From: Tom Lane <tgl@sss.pgh.pa.us>
To: Joseph Shraibman <jks@selectacast.net>
Cc: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Re: aliases break my query
Date: Fri, 26 May 2000 00:35:14 -0400
Message-ID: <5442.959315714@sss.pgh.pa.us> (raw)
In-Reply-To: <392DEB70.86A115E4@selectacast.net>
References: <392DEB70.86A115E4@selectacast.net>
Joseph Shraibman <jks@selectacast.net> writes:
> These two queries are exactly alike. The first one uses aliases except
> for the order by. The second uses aliases also for the order by. The
> third uses whole names. The third has the behavior I want.
I think you are confusing yourself by leaving out FROM clauses.
In particular, with no FROM for the inner SELECT it's not real clear
what should happen there. I can tell you what *is* happening, but
who's to say if it's right or wrong?
> playpen=> select ta.a,ta.b,ta.c, (select count (tb.zz) where tb.yy =
> ta.a) from tablea ta, tableb tb order by tablea.a;
[ produces 80 rows ]
> playpen=> select ta.a,ta.b,ta.c, (select count (tb.zz) where tb.yy =
> ta.a) from tablea ta, tableb tb order by ta.a;
[ produces 20 rows ]
The difference between these two is that by explicitly specifying
"tablea" in the order-by clause, you've created a three-way join,
as if you had written "from tablea ta, tableb tb, tablea tablea".
Once you write an alias in a from-clause entry, you must refer to
that from-clause entry by its alias, not by its true table name.
Meanwhile, what of the inner select? It has no FROM clause *and*
no valid table names. The only way to interpret the names in it
is as references to the outer select. So, on any given iteration
of the outer select, the inner select collapses to constants.
It looks like "SELECT count(constant1) WHERE constant2 = constant3"
and so you get either 0 or 1 depending on whether tb.yy and ta.a
from the outer scan are different or equal.
> playpen=> select tablea.a,tablea.b,tablea.c, (select count (tableb.zz)
> where tableb.yy = tablea.a) order by tablea.a;
[ produces 4 rows ]
Here the outer select is not a join at all --- it mentions only tablea,
so you are going to get one output for each tablea row. The inner
select looks like "select count (zz) FROM tableb WHERE yy = <constant>",
so you get an actual scan of tableb for each iteration of the outer
scan.
It's not very clear from these examples what you actually wanted to have
happen, but I suggest that you will have better luck if you specify
explicit FROM lists in both the inner and outer selects, and be careful
that each variable you use clearly refers to exactly one of the
FROM-list entries.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, jks@selectacast.net
Subject: Re: aliases break my query
In-Reply-To: <5442.959315714@sss.pgh.pa.us>
References: <392DEB70.86A115E4@selectacast.net> <5442.959315714@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox