Pivotal Knowledge Base

Follow

Distinct with "not null" would Return No Value by ORCA

Environment

Pivotal Greenplum Database (GPDB) versions prior to 4.3.24.0 and 5.6.0

Symptom

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)

Cause 

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.

Resolution

This issue can be resolved using legacy planner by setting "optimizer=off". This issue has also been fixed in 4.3.24.0 and 5.6.0 permanently.

 

Comments

Powered by Zendesk