Subselect from a function in a select clause does not see aliases from the outer select

[GoogleCodeExporter]

As in summary.

Consider following database:

create table a_table(id integer primary key, a_column varchar(32) not null, 
another_column varchar(32));
create alias a_function for "H2SubselectTest.aFunction"; -- function definition 
in attachment
insert into a_table(id, a_column, another_column) values (1,'1','1'), 
(2,'2','2'), (3,'1',null), (4,'3',null);

a select tat succeeds:
select t.id, t.a_column, (select coalesce(max(t2.another_column), 
t.another_column) from a_table t2 where t2.a_column=t.a_column) as subselect 
from (select a_column as a_column, id as id, another_column as another_column 
from a_table) as t;

the select that fails:
select t.id, t.a_column, (select coalesce(max(t2.another_column), 
t.another_column) from a_table t2 where t2.a_column=t.a_column) as subselect, 
(select coalesce(max(t3.another_column), t.another_column) as 
subselect_from_function from a_function(t.a_column) as t3 ) from (select 
a_column as a_column, id as id, another_column as another_column from a_table) 
as t;

with:
Column "T.A_COLUMN" not found; SQL statement:
select t.id, t.a_column, (select coalesce(max(t2.another_column), 
t.another_column) from a_table t2 where t2.a_column=t.a_column) as subselect, 
(select coalesce(max(t3.another_column), t.another_column) as 
subselect_from_function from a_function(t.a_column) as t3 ) from (select 
a_column as a_column, id as id, another_column as another_column from a_table) 
as t; [42122-185]

Please not that analogous sql runs properly on postgresql:

drop table if exists a_table cascade;
create table if not exists a_table(id integer primary key, a_column varchar(32) 
not null, another_column varchar(32)); 
delete from a_table;
insert into a_table(id, a_column, another_column) values (1, '1', '1'), (2, 
'2', '2'), (3, '1', null), (4, '3', null);
create or replace FUNCTION a_function(a_value varchar(32)) returns setof  
a_table as $BODY$select * from a_table where a_column=a_value$BODY$ language 
sql volatile;
select t.id, t.a_column, (select coalesce(max(t2.another_column), 
t.another_column) from a_table t2 where t2.a_column=t.a_column) as subselect 
from (select a_column as a_column, id as id, another_column as another_column 
from a_table) as t;
select t.id, t.a_column, (select coalesce(max(t2.another_column), 
t.another_column) from a_table t2 where t2.a_column=t.a_column) as subselect, 
(select coalesce(max(t3.another_column), t.another_column) as 
subselect_from_function from a_function(t.a_column) as t3 ) from (select 
a_column as a_column, id as id, another_column as another_column from a_table) 
as t;

junit test attached.

Original issue reported on code.google.com by michalbi...@gmail.com on 3 Feb 2015 at 2:05

Attachments:

• H2SubselectTest.java

[GoogleCodeExporter]

We use h2 database to test sql queries that run on postrgesql on production. 
This bug blocks some of our tests.

Original comment by michalbi...@gmail.com on 3 Feb 2015 at 2:07

[GoogleCodeExporter]

We are using version 1.4.178 but can be reproduced on latest 1.4.185 release as 
well

Original comment by michalbi...@gmail.com on 3 Feb 2015 at 2:09

[GoogleCodeExporter]

Hi,

Yes, this is not supported, and there are currently no plans to support it, 
sorry. Patches are welcome.

Regards,
Thomas

Original comment by thomas.t...@gmail.com on 12 Mar 2015 at 6:51

[GoogleCodeExporter]

Original comment by thomas.t...@gmail.com on 12 Mar 2015 at 6:51

• Changed state: PatchesWelcome