While trying to get Derby to create all sorts of strange ResultSets
(see DERBY-827), I have come across some strange behavior with the
'EXISTS' predicate.
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).
It looks like this could be related to the use of an AnyResultSet for
the predicate. When EXISTS is applied to a SELECT it seems like the
query is re-written as a join... (lots of guessing and hand-waving
here...).
Repro:
Running the following ij-script:
connect 'jdbc:derby:repro;create=true';
autocommit off;
values 1 intersect values 1;
select * from ( values 'OK' ) as T where exists (values 1 intersect values 1);
values 1 intersect values 2;
select * from ( values 'BAD' ) as T where exists (values 1 intersect values 2);
select * from ( values 'OK' ) as T where exists (select * from SYS.SYSTABLES);
select * from SYS.SYSTABLES where TABLENAME is null;
select * from ( values 'BAD' ) as T where exists (select * from SYS.SYSTABLES
where TABLENAME is null);
produces the following:
+ java org.apache.derby.tools.ij
ij version 10.3
ij> ij> ij> 1
-----------
1
1 row selected
ij> 1
--
OK
1 row selected
ij> 1
-----------
0 rows selected
ij> 1
---
BAD
1 row selected
ij> 1
--
OK
1 row selected
ij> TABLEID |TABLENAME
|&|SCHEMAID |&
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0 rows selected
ij> 1
---
0 rows selected
--
dt
