X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 122CD9FB3E0 for ; Wed, 12 Jul 2006 16:22:12 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 40909-08 for ; Wed, 12 Jul 2006 16:22:05 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.175]) by postgresql.org (Postfix) with ESMTP id 491F39FB358 for ; Wed, 12 Jul 2006 16:22:05 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id u2so345962uge for ; Wed, 12 Jul 2006 12:22:03 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=UJrC7UOV5rlvxYtzQeWcJe5uOYupjtoYssrOfx5gvLAuagzgzLqhEBuFVrGzCJNWX1hj5BkDHhLrZSmDHj5nk6UODlY5kmuC+2SXdM1l0gz3osGQmaC37mAJFXbEDUglUTRaM9srWGc27+xN+HtZNoKQRAxz173Oe0S+ST+Khpg= Received: by 10.78.151.15 with SMTP id y15mr28870hud; Wed, 12 Jul 2006 12:22:03 -0700 (PDT) Received: by 10.78.29.15 with HTTP; Wed, 12 Jul 2006 12:22:03 -0700 (PDT) Message-ID: Date: Wed, 12 Jul 2006 14:22:03 -0500 From: "Aaron Bono" To: "Bryce Nesbitt" Subject: Re: Can function results be used in WHERE? Cc: pgsql-sql@postgresql.org In-Reply-To: <44B3C761.3060903@obviously.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_7794_2725275.1152732123563" References: <44B29376.2030107@obviously.com> <44B2CFC7.7060101@obviously.com> <8088.1152589020@sss.pgh.pa.us> <44B3C761.3060903@obviously.com> X-Google-Sender-Auth: a030cacf358ce942 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200607/196 X-Sequence-Number: 25770 ------=_Part_7794_2725275.1152732123563 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline On 7/11/06, Bryce Nesbitt wrote: > > Tom Lane wrote: > > But as far as the underlying misconception goes, you seem to think that > > "4" in the WHERE clause might somehow be taken as referring to the > > fourth SELECT result column (why you don't think that the "1" would > > likewise refer to the first result column isn't clear). This is not so. > > "4" means the numeric value four. There is a special case in ORDER BY > > and GROUP BY that an argument consisting of a simple integer literal > > constant will be taken as a reference to an output column. This is an > > ugly kluge IMHO... > Yeah, it was a longshot. I only tried it because the column label did > NOT work, and I had some gut reaction to repeating the same function > twice: As I mentioned before: the only difference between HAVING and WHERE is that WHERE occurs before a GROUP BY and HAVING occurs after. stage=# SELECT pod_code, lat, lon, > calculate_distance(lat,lon,37.789629,-122.422082) as dist FROM eg_pod > WHERE dist < 1 ORDER BY dist desc limit 10; > ERROR: column "dist" does not exist You cannot use an alias from the select column list in your WHERE clause because the where is the criteria done BEFORE your columns are pulled out. This is especially noticable when doing a GROUP BY since the WHERE is done before the GROUP BY and the returned column values are gathered after the GROUP BY. If you want to use an alias, do a subquery and then put your where in the outer query. I believe the ORDER BY is done last but that may be dependent on the database implementation. It does make sense to think of ORDER BY to be done last though. For that reason it can use the alias. stage=# SELECT pod_code, lat, lon, > calculate_distance(lat,lon,37.789629,-122.422082) as dist FROM eg_pod > WHERE calculate_distance(lat,lon, 37.789629,-122.422082) < 1 ORDER BY > dist desc limit 5; > pod_code | lat | lon | dist > ----------+-----------+-------------+------------------- > 5 | 37.792022 | -122.404247 | 0.988808031847045 > 62 | 37.780166 | -122.409615 | 0.944907273102541 > 4 | 37.798528 | -122.409582 | 0.919592583879426 > 86 | 37.777529 | -122.417982 | 0.866416010967029 > 68 | 37.789915 | -122.406926 | 0.82867104307647 > (5 rows) ================================================================== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com ================================================================== ------=_Part_7794_2725275.1152732123563 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline On 7/11/06, Bryce Nesbitt <bryce1@obviously.com> wrote:
Tom Lane wrote:
> But as far as the underlying misconception goes, you seem to think that
> "4" in the WHERE clause might somehow be taken as referring to the
> fourth SELECT result column (why you don't think that the "1" would
> likewise refer to the first result column isn't clear).  This is not so.
> "4" means the numeric value four.  There is a special case in ORDER BY
> and GROUP BY that an argument consisting of a simple integer literal
> constant will be taken as a reference to an output column.  This is an
> ugly kluge IMHO...
Yeah, it was a longshot.  I only tried it because the column label did
NOT work, and I had some gut reaction to repeating the same function twice:

As I mentioned before: the only difference between HAVING and WHERE is that WHERE occurs before a GROUP BY and HAVING occurs after. 

stage=# SELECT pod_code, lat, lon,
calculate_distance(lat,lon,37.789629,-122.422082) as dist FROM eg_pod
WHERE dist < 1 ORDER BY dist desc limit 10;
ERROR:  column "dist" does not exist

You cannot use an alias from the select column list in your WHERE clause because the where is the criteria done BEFORE your columns are pulled out.  This is especially noticable when doing a GROUP BY since the WHERE is done before the GROUP BY and the returned column values are gathered after the GROUP BY.  If you want to use an alias, do a subquery and then put your where in the outer query.

I believe the ORDER BY is done last but that may be dependent on the database implementation.  It does make sense to think of ORDER BY to be done last though.  For that reason it can use the alias.

stage=# SELECT pod_code, lat, lon,
calculate_distance(lat,lon,37.789629,-122.422082) as dist FROM eg_pod
WHERE calculate_distance(lat,lon, 37.789629,-122.422082) < 1 ORDER BY
dist desc limit 5;
pod_code |    lat    |     lon     |       dist
----------+-----------+-------------+-------------------
        5 | 37.792022 | -122.404247 | 0.988808031847045
       62 | 37.780166 | -122.409615 | 0.944907273102541
        4 | 37.798528 | -122.409582 | 0.919592583879426
       86 | 37.777529 | -122.417982 | 0.866416010967029
       68 | 37.789915 | -122.406926 |  0.82867104307647
(5 rows)

==================================================================
   Aaron Bono
   Aranya Software Technologies, Inc.
   http://www.aranya.com
================================================================== ------=_Part_7794_2725275.1152732123563--