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