Hi, This should be fixed now. In the trunk currently, tomorrow it should be in the "Latest Automated Build (not released)<http://www.h2database.com/automated/h2-latest.jar> ".
Regards, Thomas On Wed, May 29, 2013 at 7:36 PM, Thomas Mueller < [email protected]> wrote: > Hi, > > Yes, it's a bug. I'm not sure what the problem is, but it seems to be > related to the "group sort" optimization. A workaround is: > > select ref , (select count(*) from t2 where t2.ref = t.ref) CC > from (select ref from t1 group by ref) t; > > But of course it needs to be fixed. > > Regards, > Thomas > > > On Wed, May 29, 2013 at 6:27 PM, <[email protected]> wrote: > >> Or at least that's how I interpret it. >> >> If you run the SQL below in H2 console (Version 1.3.170). You will see >> that the last query do not provide the same result before and after >> dropping an index. >> I don't have that kind of problem when performing that kind of querries >> in Oracle. >> Note that there are no error message. >> >> >> drop table T1 if exists; >> drop table T2 if exists; >> drop index T1REF_INDEX if exists; >> >> create temp table IF NOT EXISTS T1 ( ref int, NAME VARCHAR(255) ) ; >> create temp table IF NOT EXISTS T2 ( ref int) ; >> INSERT INTO T1 VALUES(1, 'Albert' ), (2, 'Bertrand' ) , (2, 'Berenice'), >> ( 3, 'Camille' ) , ( 4 , 'Denis' ); >> INSERT INTO T2 VALUES(1), (1), (2) , (3) , (4) , (4) , (4); >> CREATE INDEX T1REF_INDEX ON T1(ref); >> >> >> -- Q1) Gives the expected result but I cannot add the result column >> max(name) if I want too and I get multiple rows with ref=2 which is not >> what I want here. >> select ref, (select count(*) from t2 where t2.ref = t1.ref) CC from t1; >> >> >> -- Q2) Works and allow me get the max(name) if I want to, but it is so >> cumbersome... >> select XX.* , YY.CC from ( select ref , max(name) from T1 group by ref >> ) XX inner join (select ref , count(*) CC from T2 group by ref) YY ON >> XX.ref = YY.ref ; >> >> >> -- Q3a) Doesn't work. Gives a strange result for me (At least Oracle >> doesn't give the same result) >> select ref , (select count(*) from t2 where t2.ref = t1.ref) CC >> from t1 group by ref ; >> >> >> drop index T1REF_INDEX if exists; >> >> -- Q3b) Same as Q3 but now it works... >> select ref , (select count(*) from t2 where t2.ref = t1.ref) CC >> from t1 group by ref ; >> >> -- Why does the presence of an index on T1.ref modify the result of the >> query Q3a ? Why doesn't it give me the same result as Q2 ? >> -- Note: I have the same problem with indexes created implicitly for >> primary keys. >> >> -- >> You received this message because you are subscribed to the Google Groups >> "H2 Database" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to [email protected]. >> To post to this group, send email to [email protected]. >> Visit this group at http://groups.google.com/group/h2-database?hl=en. >> For more options, visit https://groups.google.com/groups/opt_out. >> >> >> > > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/groups/opt_out.
