Thread: Forward referencing of table aliases in subqueries does not work in 9.1 beta2 ( works in 9.0 and 8.4.2 )
Forward referencing of table aliases in subqueries does not work in 9.1 beta2 ( works in 9.0 and 8.4.2 )
Hi Everybody,
We have a view defined like below.
CREATE OR REPLACE VIEW dim_agg_instr_actuals_view AS 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)))) AND NOT (EXISTS ( SELECT 1
FROM dim_base_agg_col dbac
WHERE dba.dim_base_agg_key = dbac.dim_base_agg_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 dbac.dim_col_key = dbaid.dim_col_key))));
ALTER TABLE dim_agg_instr_actuals_view OWNER TO postgres;
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.For example dbai alias in the query below...
SELECT 1
FROM dim_base_agg_instr_def dbaid
WHERE dbai.dim_base_agg_instr_key = dbaid.dim_base_agg_instr_key
Any reason why this functionality is disabled in 9.1 beta 2 ?
Thanks,
Bangar Raju
Re: 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.Vadapalli@infor.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
Re: Forward referencing of table aliases in subqueries does not work in 9.1 beta2 ( works in 9.0 and 8.4.2 )
On 20/06/11 13:59, Tom Lane wrote: > BangarRaju Vadapalli <BangarRaju.Vadapalli@infor.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. In other words: Please provide details, including at least the exact text of the error message you get when you run the query on 9.1 beta 2. I suspect from earlier discussion off-list (while we were trying to get any kind of info on the crash) that he's getting the error: "Error: JOIN qualification cannot refer to other relations." when executing that query, and did not get that error in earlier versions of PostgreSQL. Bangar Raju: If at all possible, please put together a small self-contained .SQL file that: - Defines a table - Defines a view of that table - Runs a query against that view that works on 8.4 and 9.0 but fails on 9.1 beta 2. As your post stands, it's very incomplete and hard to answer because the view is complex and refers to lots of tables you haven't provided definitions for. If you just post all your table definitions it'll probably be too complex for anyone to be willing to investigate - for free in their spare time, anyway. You will get the best help if you write a better question and try to cut things out of your test view and query until it is as simple and self-contained as possible. When I suggested that you ask about this on-list, I didn't expect you to re-post your question to me word-for-word. Remember, those on the list don't have the context of our prior conversation, and even with that (which would only tell the the missing error message) they wouldn't know enough to be able to help you. Whenver asking for help on any mailing list, try to read your post before clicking "send" as if you were someone who didn't know anything about your tables, business, app, environment, etc and only knew what you actually wrote in the email. See if it would make sense to them. Imagine you were trying to answer the question, as if it came from somebody else in another situation you knew nothing about. You will quickly notice pieces of information that you left out, like your operating system, exact database version, table definitions, error messages, etc etc etc. (All this is, of course, in the guide to reporting problems that I linked to before). -- Craig Ringer
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@sss.pgh.pa.us]
Sent: Monday, June 20, 2011 11:30 AM
To: BangarRaju Vadapalli
Cc: pgsql-general@postgresql.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.Vadapalli@infor.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
Re: 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.Vadapalli@infor.com> writes:
> 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.xand not exists ( select 1 from c where b.y = c.y and not exists ( select 1 from c where a.x = c.x))
Thanks, I've applied a patch for this.
regards, tom lane