2491508 - Query with many joining tables returned incorrect result in parallel ORDER BY phase - SAP IQ 16.0 SP11

• A query with many joining tables returned incorrect result: it should be returned 23 rows, but 15-17 rows are returned at incorrect execution.
• The query plans shows:

#87 Group By (Hash)
        
Parent Node #185 
Child Node 1 #86 
Generated Result Rows 136 
Estimated Result Rows 103 
Optimizer est. max hash rows for these keys 353004337 
SMP fragent cost 89549146003094528.00000000 
Hash est. number of keys 103 
Hash actual number of keys 11 

• When Dml_options8 is set to 268435456, the correct result is returned:

set temporary option DML_options8=268435456;      -- Disable shadow Parallel order by retrievers

• The query plan with Dml_options8 is set to 268435456 shows:

#87 Group By (Hash)
 
Parent Node #90 
Child Node 1 #86 
Generated Result Rows 23 
Estimated Result Rows 103 
Optimizer est. max hash rows for these keys 353004337 
Hash est. number of keys 103 
Hash actual number of keys 23 

• Tried to set some other DML options. Only DML_Options8=8 had a positive effect while and others still returned incorrect results.       

set temporary option DML_Options8=8;       -- Disable shadow parallelism

• Removing a derived column from the top select-list also leads to the correct result.

SAP IQ 16.0 SP11

'SubQuery PreFilter', dfo::ValidateOutputOrdering , sybase, failure, CR806424, CR#806424, 806424, 'Group By (Sort)', CR809090, CR#809090, 809090 , KBA , group by (sort) , BC-SYB-IQ , Sybase IQ , Bug Filed