Wrong result in planner for nullable constraint

[chrishajas]

Greenplum version or build

master, 6, 5

Step to reproduce the behavior

create table ta(a int check(a = 1));
insert into ta values(null);
select * from ta where a is null;

In Greenplum planner:

test=# select * from ta where a is null;
 a
---
(0 rows)

test=# explain select * from ta where a is null;
                QUERY PLAN
------------------------------------------
 Result  (cost=0.00..0.01 rows=1 width=0)
   One-Time Filter: false
 Optimizer: Postgres query optimizer
(3 rows)

In postgres 9.3 and 9.6:

select * from ta where a is null;
 a 
---
  
(1 row)

This issue also exists in ORCA and is being fixed there.

[gfphoenix78]

Effect of Unknown in WHERE clauses. See https://en.wikipedia.org/wiki/Null_(SQL)

[kainwen]

Close this since this is fixed by https://github.com/greenplum-db/gpdb/pull/14553 in main branch. For 6X we don't want to change behavior until we hear from real complains.