Re: Forward referencing of table aliases in subqueries does not work in 9.1 beta2 ( works in 9.0 and 8.4.2 )

Hi Tom,

Self contained test case attached... When executing the query below in 9.1 beta 2 we are getting the error ERROR: JOIN qualification cannot refer to other relations. The same query is executing fine in 8.4.2 and 9.0.

SELECT dbai.dim_base_agg_instr_key AS instr_key, dba.dim_base_key AS basekey, dl.datamart_letter, dba.agg_number AS aggnum, dl.enabled, dba.dim_base_agg_key, dbai.dim_base_name, dbai.dim_base_agg_name, dl.dim_base_agg_letter_key, dl.agg_size

FROM dim_base_agg_instr_view dbai, dim_base_agg dba, dim_base_agg_letter dl

WHERE dba.dim_base_agg_key = dl.dim_base_agg_key AND dbai.dim_base_key = dba.dim_base_key AND NOT (EXISTS ( SELECT 1

FROM dim_base_agg_instr_def dbaid

WHERE dbai.dim_base_agg_instr_key = dbaid.dim_base_agg_instr_key AND NOT (EXISTS ( SELECT 1

FROM dim_base_agg_col dbac

WHERE dba.dim_base_agg_key = dbac.dim_base_agg_key AND dbac.dim_col_key = dbaid.dim_col_key))));

I have tried to find the cause and found out that the dba table alias ( the condition dba.dim_base_agg_key = dbac.dim_base_agg_key in bold and italic above which is nested 2 levels ) in the subquery is not visible from the outer query.

As already mentioned we are experiencing a backend crashes in 8.4.2, 9.0 and are trying to use crash dump handler to generate the trace in 9.1 beta2. We have so many views in our application which use select queries like above, so we are looking for any workarounds...

Given below the self contained test case...

create table a(x int);

create table b(x int, y int);

create table c(x int, y int, z int);

This query fails with ERROR: JOIN qualification cannot refer to other relations - select a.x from a, b where a.x = b.x and not exists ( select 1 from c where b.y = c.y and not exists ( select 1 from c where a.x = c.x))

I suppose other environmental information is not needed here...

Thanks,

Bangar Raju

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Monday, June 20, 2011 11:30 AM
To: BangarRaju Vadapalli
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Forward referencing of table aliases in subqueries does not work in 9.1 beta2 ( works in 9.0 and 8.4.2 )

BangarRaju Vadapalli <BangarRaju(dot)Vadapalli(at)infor(dot)com> writes:

> The select query for creating the view is executing fine in 8.4.2. But

> not in 9.1 beta2. The issue is forward referencing of table aliases in

> subqueries is not working fine in 9.1 beta2.

This complaint is unintelligible. Please provide a self-contained

test case.

regards, tom lane