WHERE condition getting pushed into sub-query with LIMIT

[GoogleCodeExporter]

What steps will reproduce the problem?

DROP TABLE COFFEES if exists;
CREATE TABLE COFFEES (COF_NAME VARCHAR,PRICE INT);

INSERT INTO COFFEES (COF_NAME,PRICE) VALUES ('Colombian',       5);
INSERT INTO COFFEES (COF_NAME,PRICE) VALUES ('French_Roast',    5);
INSERT INTO COFFEES (COF_NAME,PRICE) VALUES ('Colombian_Decaf', 20);

select * from (
  select COF_NAME, PRICE from COFFEES order by COF_NAME LIMIT 2
);

select * from (
  select COF_NAME, PRICE from COFFEES order by COF_NAME LIMIT 2
) where PRICE < 10;

What is the expected output? What do you see instead?

The first select finds Colombian and Colombian_Decaf.
The second one should find only Colombian but also finds French_Roast.

Tested with 1.3.162 and 1.3.164 on Win7/x64, in-memory DB. Code like this is 
generated as part of the ScalaQuery test suite 
(https://github.com/szeiger/scala-query/blob/new-ast/src/test/scala/org/scalaque
ry/test/NewQuerySemanticsTest.scala#L179). A work-around in addition to a fix 
would be very useful so we can support older H2 versions properly.

Original issue reported on code.google.com by szeig...@gmail.com on 22 Mar 2012 at 9:59

[GoogleCodeExporter]

You are right, I will fix this in the next release. All other databases I 
tested don't push the condition into the subquery.

Unfortunately, I don't currently know of a workaround for older versions of H2.

Original comment by thomas.t...@gmail.com on 23 Mar 2012 at 3:37

• Changed state: Accepted

[GoogleCodeExporter]

Fixed in version 1.3.166

Original comment by thomas.t...@gmail.com on 9 Apr 2012 at 10:24

• Changed state: Fixed

[GoogleCodeExporter]

Here's a more complicated query which still exhibits the same problem:

select s2.c3, s2.c5 from (select s3.c3 as c3, s3.c4 as c4, s3.c5 as c5 from 
(select s4.c3 as c3, s4.c4 as c4, s4.c5 as c5 from (select e1."COF_NAME" as c3, 
e1."PRICE" as c4, 42 as c5 from "COFFEES" e1) s4 order by s4.c3) s3 LIMIT 2) s2 
where s2.c4 < 10

Original comment by szeig...@gmail.com on 12 Jun 2012 at 2:37

[GoogleCodeExporter]

Never mind, this version separates the ORDER BY from the LIMIT, so the ordering 
does not apply (thanks to SQL's stupid non-compositional semantics).

Original comment by szeig...@gmail.com on 12 Jun 2012 at 2:55