Hi,
I simplified the test case a bit:
create table test (id int);
select id from test t1 where exists (select * from
(select (select count(*) from test where t1.id=1) as x) as y);
This works for PostgreSQL, but fails for the other database I tried. HSQLDB
and Derby don't like one of the ")", and MySQL also says "Unknown column '
t1.id' in 'where clause'".
I think the level of indirection is too high for H2 and MySQL. I'm afraid
you would need to re-write the query; not quite sure how to do that best
I'm sorry. Or use PostgreSQL or another database where it is supported.
Regards,
Thomas
On Thursday, January 2, 2014, Emmeran Seehuber wrote:
> Hi,
>
> The following script works fine in Postgresql (9.3):
>
> create table test1 (id int8);
> create table test2 (id int8, test1_id int8, test3_id int8);
> create table test3 (id int8, testflag boolean);
>
>
> select id from test1 t1 where exists (select * from (select
> (select count(*) from test2 t2 join test3 t3 on t3.id = t2.test3_id where
> t2.test1_id = t1.id) as count1,
> (select count(*) from test2 t2 join test3 t3 on t3.id = t2.test3_id where
> t2.test1_id = t1.id and testflag = true) as count2
> ) as sq1
> where sq1.count1 = sq1.count2 and sq1.count2 > 0)
>
>
> But when i try to execute this with H2 (1.3.174) get the following error:
>
> Feld "T1.ID" nicht gefunden
> Column "T1.ID" not found; SQL statement:
> CREATE FORCE VIEW PUBLIC._2 AS
> SELECT
> (SELECT
> COUNT(*)
> FROM PUBLIC.TEST2 T2
> INNER JOIN PUBLIC.TEST3 T3
> ON 1=1
> WHERE (T2.TEST1_ID = T1.ID)
> AND (T3.ID = T2.TEST3_ID)) AS COUNT1,
> (SELECT
> COUNT(*)
> FROM PUBLIC.TEST2 T2
> INNER JOIN PUBLIC.TEST3 T3
> ON 1=1
> WHERE ((T2.TEST1_ID = T1.ID)
> AND (TESTFLAG = TRUE))
> AND (T3.ID = T2.TEST3_ID)) AS COUNT2
> FROM SYSTEM_RANGE(1, 1)
> [42122-174]<http://10.50.5.84:60676/query.do?jsessionid=b7b0ff1b0bd4f2b7fa1f72bf183a8a7e#>
> 42S22/42122
>
> It seems H2 is creating a (temporary?) view for the subselect. And of
> course the view statement cant reference the outer table field. I think
> this is a bug in H2, but i am not sure if this is easy to fix, as i think
> h2 shouldn't create a view here ...
>
> Any idea on a workaround for this?
>
> I use H2 as the db for my testdrivers, so i can skip the test on this sql
> statement for now.
>
> Thanks.
>
> Regards,
> Emmeran
>
> --
> 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] <javascript:_e({},
> 'cvml', 'h2-database%[email protected]');>.
> To post to this group, send email to
> [email protected]<javascript:_e({}, 'cvml',
> '[email protected]');>
> .
> Visit this group at http://groups.google.com/group/h2-database.
> 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.
