Received: from p1.selectacast.net (IDENT:root@p1.selectacast.net [206.41.1.251]) by hub.org (8.9.3/8.9.3) with ESMTP id OAA46974 for ; Fri, 26 May 2000 14:14:37 -0400 (EDT) (envelope-from jks@selectacast.net) Received: from selectacast.net (IDENT:jks@d1 [209.219.91.66]) by p1.selectacast.net (8.9.3/8.9.3) with ESMTP id OAA26324; Fri, 26 May 2000 14:13:36 -0400 Message-ID: <392EBECF.A8427943@selectacast.net> Date: Fri, 26 May 2000 14:13:35 -0400 From: Joseph Shraibman Organization: Xtenit X-Mailer: Mozilla 4.72 [en] (X11; U; Linux 2.2.15 i686) X-Accept-Language: en MIME-Version: 1.0 To: Tom Lane CC: "pgsql-sql@postgresql.org" Subject: Re: aliases break my query References: <392DEB70.86A115E4@selectacast.net> <5442.959315714@sss.pgh.pa.us> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200005/168 Tom Lane wrote: > > Joseph Shraibman 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 = ", > 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