From: Joseph Shraibman <jks@selectacast.net>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Re: aliases break my query
Date: Fri, 26 May 2000 14:13:35 -0400
Message-ID: <392EBECF.A8427943@selectacast.net> (raw)
References: <392DEB70.86A115E4@selectacast.net>
<5442.959315714@sss.pgh.pa.us>
Tom Lane wrote:
>
> 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?
>
Well I assumed that the aliases would be inerited from the outer query.
> > 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.
I guess I made the mistake of assuming that SQL is logical. I don't know
what I was thinking. ;)
>
> 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.
OK that sorta makes sense to be. What I want is the behavior I got with
the third query (below). I want the values in table a, and then a count
of how many entries in tableb have the yy field of tableb that matches
that entry in tablea's a field.
playpen=> select ta.a,ta.b,ta.c, (select count (tb.zz) from tableb tb
where tb.yy = ta.a) from tablea ta, tableb tb group by ta.a, ta.b, ta.c
order by ta.a;
a|b|c|?column?
-+-+-+--------
1|2| | 0
2|3|4| 2
3|4|5| 1
4|5|4| 1
(4 rows)
... which is what I want. Thanks.
>
> > 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: jks@selectacast.net, tgl@sss.pgh.pa.us
Subject: Re: aliases break my query
In-Reply-To: <392EBECF.A8427943@selectacast.net>
References: <392DEB70.86A115E4@selectacast.net> <5442.959315714@sss.pgh.pa.us> <392EBECF.A8427943@selectacast.net>
* 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