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].
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.
