Forgot to include the link to the Reference Manual page. Here it is http://db.apache.org/derby/docs/10.2/ref/rrefsqlj23075.html
Mamta On 2/22/07, Mamta Satoor <[EMAIL PROTECTED]> wrote:
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 > >
