db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Dyre.Tjeldv...@Sun.COM
Subject Is this a bug in EXISTS?
Date Thu, 22 Feb 2007 17:36:11 GMT
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


Mime
View raw message