Re: aliases break my query - Mailing list pgsql-sql

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