Also, the ANSI SQL-92 spec requires that the subquery appears TRUE. In particular, section 8.7, general rule (2), case (a) says:
> If T is empty or if the implied <comparison predicate> is true for every row > RT in T, then "R <comp op> <all> T" is true. So H2's behaviour does not concur with the standard; the behaviour of Oracle (and the others listed by Lukas) does concur. Please, can this be fixed? I'm working in a team that uses both H2 and Oracle extensively, and we need the behaviour to be as consistent as possible between the two platforms. Do I need to add this issue to the bug database? I'm not really sure whether it's related to the existing issue 340. Thank you, David. On Sep 2, 6:04 pm, Lukas Eder <[email protected]> wrote: > I can confirm this. I can also confirm that Oracle's behaviour for >= > ALL (... empty set ...) can be observed in > > - DB2 > - Derby > - HSQLDB > - Ingres > - MySQL > - Postgres > - SQL Server > - Sybase SQL Anywhere > > On 2 Sep., 01:19, David Wallace <[email protected]> wrote: > > > > > Hello H2 community, > > I have identified the following bug in version 1.3.158. Using an ALL > > clause against a subquery appears FALSE if the subquery is empty, > > whereas it should appear TRUE (like it does in Oracle). For example, > > the SQL below should return one row from the SELECT, but it returns no > > rows. > > > create table test1( testcol1 int ); > > create table test2( testcol2 int ); > > insert into test1( testcol1 ) values( 1 ); > > select * from test1 where testcol1 >=all ( select testcol2 from > > test2 ); > > > Can this be fixed please? > > > Thank you, > > David.- Hide quoted text - > > - Show quoted text - -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
