[DERBY-2370] EXISTS may return the wrong value for sub-queries involving set operations

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 10.2.2.0
• Fix Version/s: 10.3.1.4
• Component/s: SQL
• Issue & fix info: Release Note Needed
• Assignee: A B
• Reporter: Dyre Tjeldvoll
• Created: 23/Feb/07 09:14
• Updated: 30/Jun/09 16:12
• Resolved: 22/May/07 21:24

Description

It seems like EXISTS on a SELECT returning zero rows returns false (as
expected), but EXISTS on INTERSECT of two disjunct sets returns true,
e.g EXISTS (values 1 intersect values 2).

Yip Ng wrote on derby-dev:
I believe its probably got to do with the EXISTS subquery transforming
the original RCL to
a TRUE boolean value for the INTERSECT. So during row comparison at
execution time
for INTERSECT processing since true == true(thus intersects), so it
will always return 'BAD'. Likewise,

select * from ( values 'OK' ) as T where exists (values 1 except values 2);

This supposedly should return 'OK' but because of the boolean
transformation mentioned
above for EXISTS subquery, it will return no rows for EXCEPT
processing.

Issue Links

• is duplicated by: DERBY-3951 Derby SQL Result Using 'Not Exists' & 'Except' Together Seem Incorrect
• is part of: DERBY-2034 Tracking of bugs that lead to incorrect results being stored or returned to the client