Pivotal Knowledge Base

Follow

Queries Return Values in Wrong Columns when Optimizer is On

Environment

Pivotal Greenplum Database (GPDB) 4.3.16.x and above

Symptom

When running some queries that contain a "WITH" clause and a projection list with more than 1 column, GPORCA returned column data in the incorrect order. 

Example

WITH sel1 AS ( SELECT * FROM (VALUES ('rulename1', 'action1'), ('rulename2', 'action2')) AS t (rulename, act) ), sel2 AS ( SELECT coalesce(sel1.rulename, '0') AS rulename, 0 AS dfsdfs, sel1.act AS act FROM sel1) SELECT * FROM sel2;

 

With optimizer OFF:

rulename | dfsdfs | act 
-----------+--------+--------- 
rulename1 | 0 | action1 
rulename2 | 0 | action2

 

With optimizer ON:

rulename | dfsdfs | act 
----------+--------+----------- 
action1 | 0 | rulename1 
action2 | 0 | rulename2

Cause 

The incorrect column order occurred when GPORCA did not correctly translate a ValueScan operation.

Resolution

This issue has been fixed. GPORCA handles ValueScan operations for the specified type of query correctly.

Fix is included in GPDB 4.3.20.0

 

Comments

Powered by Zendesk