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:51:42 GMT
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