Unexpected Results when Inserting NULL to PK column

[suyZhong]

Describe the bug

Consider the following test case. It is unexpected that both of queries return the row, since the expression (c1) > 1 cannot be evaluated to TRUE at the same time. I believe there's an issue concerning the INSERT statement since it inserted NULL into c1, which violates its primary key constraint.

Error message/log

No response

To Reproduce

CREATE TABLE t0(c0 INT, c1 INT, PRIMARY KEY(c1, c0));
INSERT INTO t0(c0) VALUES (1);

SELECT * FROM t0 WHERE (c1 > 1) IS NULL; -- 1 NULL
SELECT * FROM t0 WHERE (c1 > 1);  -- 1 NULL

Expected behavior

I expected that the INSERT statement would trigger an error (as in PostgreSQL), or at least one of the two queries return an empty table.

How did you deploy RisingWave?

via Docker image and online playground

The version of RisingWave

dev=> SELECT version();
                                     version                                      
----------------------------------------------------------------------------------
 PostgreSQL 9.5-RisingWave-1.3.0-alpha (0c1f558ca104d1ba3e95d33ddc198111aa021537)

docker image is 

risingwavelabs/risingwave                     latest                      1d2602412e28   3 days ago

Additional context

No response

[BugenZhao]

The direct cause of this issue is the predicate push-down on Scan.

dev=> explain SELECT * FROM t0 WHERE (c1 > 1);
                                 QUERY PLAN                                 
----------------------------------------------------------------------------
 BatchExchange { order: [], dist: Single }
 └─BatchScan { table: t0, columns: [c0, c1], scan_ranges: [c1 > Int32(1)] }
(2 rows)

By default, NULL is the largest value. That's why NULL is selected.

---

Possible solutions:

1. Keep the Filter node after pushing down the predicate.
2. Correctly and carefully handle NULL when converting predicates to scan ranges.
3. Reject NULL values on INSERTs and UPDATEs. (feat: not null constraint #8133)

It's worth noting that it might be impossible to enforce non-NULL constraints in RisingWave even though 3 is possible. This is because NULL primary keys can also come from external sources or in internal tables, where we cannot "reject" them like user-issued INSERTs or UPDATEs but only fail the entire job.

cc @xxchan @chenzl25