Affects Version/s: None
Fix Version/s: None
Component/s: Query Processor
This is from the Optimization of Nested SQl Queries Revisited paper: http://dl.acm.org/citation.cfm?id=38723
Consider Part table having:
Supply table having:
should return the row with PNum=8.
But a transformation to a semi-join would eliminate this row, as there are no rows in supply table with PNum=8.
AS shown in the paper the soln is to transform to:
The additional distinct query within the SubQuery is to handle duplicates in the outer query on the joining columns.