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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
