Pivotal Greenplum Database (GPDB) versions prior to 188.8.131.52 and 5.6.0
Distinct with "not null" would return no value by ORCA. Please check the following reproduce steps.
gpadmin=# create table sa (id bigint , id1 bigint) ; NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' as the Greenplum Database data distribution key for this table. HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. CREATE TABLE Time: 12.429 ms gpadmin=# insert into sa values ( 1070603463663268 , 810348137470364) ; INSERT 0 1 Time: 129.622 ms gpadmin=# show optimizer ; optimizer ----------- on (1 row) Time: 1.035 ms
"Not null" works fine without distinct
gpadmin=# select id,id1 from sa where not id = id1 and id is not null and id1 is not null ; id | id1 ------------------+----------------- 1070603463663268 | 810348137470364 (1 row)
While not null returns no value with distinct
gpadmin=# select distinct id,id1 from sa where not id = id1 and id is not null; id | id1 ----+----- (0 rows)
GPORCA produced wrong results for some queries when the query contains multiple predicates combined by AND or OR operators and some of the predicates included a NOT operator (NOT <condition>). GPORCA did not process the NOT operator correctly.
This issue can be resolved using legacy planner by setting "optimizer=off". This issue has also been fixed in 184.108.40.206 and 5.6.0 permanently.