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.
