db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mamta Satoor" <msat...@gmail.com>
Subject Re: Is this a bug in EXISTS?
Date Thu, 22 Feb 2007 17:53:25 GMT
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 <msatoor@gmail.com> 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, Dyre.Tjeldvoll@sun.com <Dyre.Tjeldvoll@sun.com> 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
> >
> >
>

Mime
View raw message