IQ 16.0 server returns an incorrect result against a simple query below.
SELECT b.COL001 AS COL001,
FROM TAB001 A
INNER JOIN TAB002 B
ON A.COL002 = B.COL002
AND A.COL003 = '01'
INNER JOIN TAB003 f
ON B.COL002 = f.COL002
AND f.COL003 = '01'
left outer JOIN TAB004 M
ON F.COL004 = M.COL005
AND m.COL006 = '2015'
AND substr(B.COL007,1,4) = '2015'
The first(left) table TAB001 has a primary key on the join column(COL002) and the IQ server returns correct result in case of not creaCOL001g the primary key on the table.
According to the query plans on both the incorrect and correct result, there commonly is a note regarding to the elimination of redundant sort(Order By) nodes on a Join node as below.
*** #08 Join (Sort-Merge)
Eliminated redundant condition (A.COL002 = F.COL002)
A notable difference between two query plans, there is no "ORDER BY" node as a right child node of the join node above in case of the incorrect result, while it can be seen from the correct result.
- Join Node in case of the correct result
- Join Node in case of the incorrect result
Regarding to the note above, the IQ server returns the correct result when setting a temporary option Dml_options2 to 8388608.
It is to disable elimination of redundant sort(Order By) nodes.
SAP IQ16 SP11 PL06
"wrong result", Dml_options2, 8388608, DML_Options8, CR 809166, CR#809166, CR809166 , KBA , BC-SYB-IQ , Sybase IQ , Problem
About this pageThis is a preview of a SAP Knowledge Base Article. Click more to access the full version on SAP for Me (Login required).
Search for additional results
Visit SAP Support Portal's SAP Notes and KBA Search.