Dyre, this appears to be a bug. The Refernce Manual says that EXISTS will return TRUE if the table subquery returns ANY rows. Here is the text from the Reference Manual ******************** Operates on a table subquery. Returns TRUE if the table subquery returns any rows, and FALSE if it returns no rows. Table subquery can return multiple columns (only if you use * to denote multiple columns) and rows.
*WHERE EXISTS (SELECT * FROM Flights WHERE dest_airport = 'SFO' AND orig_airport = 'GRU')* ******************** Mamta On 2/22/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
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
