2493710 - In IQ 16.0, an incorrect result returned against a simple join query | SAP Knowledge Base Article

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.TIN AS TIN,
............
FROM TTIRNDM001 A
INNER JOIN TTIRNAM101 B
ON A.RTN_CVA_ID = B.RTN_CVA_ID
AND A.MATE_STAT_CL_CD = '01'
INNER JOIN TTIRNDL008 f
ON B.RTN_CVA_ID = f.RTN_CVA_ID
AND f.MATE_STAT_CL_CD = '01'
left outer JOIN TTIRNAC805 M
ON F.MTFB_CD = M.TFB_CD
AND m.ATTR_YR = '2015'
WHERE 1=1
AND substr(B.TXNRM_YM,1,4) = '2015'
.................

The first(left) table TTIRNDM001 has a primary key on the join column(RTN_CVA_ID) and the IQ server returns correct result in case of not creating 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.RTN_CVA_ID = F.RTN_CVA_ID)

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.png

  • Join Node in case of the incorrect result

IncorrectQueryPlanQueryTree.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.