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 NAA97677; Fri, 26 May 2000 13:48:08 -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 NAA26131; Fri, 26 May 2000 13:47:04 -0400 Message-ID: <392EB897.203DE4AE@selectacast.net> Date: Fri, 26 May 2000 13:47:03 -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: Peter Eisentraut CC: Tom Lane , "pgsql-sql@postgresql.org" , pgsql-hackers@postgresql.org Subject: Re: aliases break my query References: Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Archive-Number: 200005/167 Peter Eisentraut wrote: > > > > 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 ] > > > > playpen=> select tablea.a,tablea.b,tablea.c, (select count (tableb.zz) > > > where tableb.yy = tablea.a) order by tablea.a; > > [ produces 4 rows ] > > Once again, I think that we *really* need to discuss whether implicit > range table entries in SELECT are a good idea. What is an "implicit range table entry"? We invariably get a > question like this every week and invariably the answer is "if you give a > table an alias you *must* refer to it by that alias". Hey, I *did* do that in the second query, and that still produced extra results. I tried putting the aliases in the inner select too but that didn't help. In fact the inner select always is 4 in that case. Unless I only alias tableb in the inner query, and let it get the definition of tablea from the outer query. (I'm sure Tom has > this reply automated by now.) I claim the only thing that buys is > confusion for very little convenience at the other end. > > Stop the madness! :) > > -- > Peter Eisentraut Sernanders väg 10:115 > peter_e@gmx.net 75262 Uppsala > http://yi.org/peter-e/ Sweden