SAP Knowledge Base Article - Preview

2493710 - In IQ 16.0, an incorrect result returned against a simple join query

Symptom

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'
WHERE 1=1
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

CorrectQueryPlanJoinNode#8_masking.png

  • Join Node in case of the incorrect result

IncorrectQueryPlanQueryTree_masking.png

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.


Read more...

Environment

SAP IQ16 SP11 PL06

Product

SAP IQ 16.0

Keywords

"wrong result", Dml_options2, 8388608, DML_Options8, CR 809166, CR#809166, CR809166 , KBA , BC-SYB-IQ , Sybase IQ , Problem

About this page

This is a preview of a SAP Knowledge Base Article. Click more to access the full version on SAP ONE Support launchpad (Login required).

Search for additional results

Visit SAP Support Portal's SAP Notes and KBA Search.