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 EDA759FB278
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue, 11 Jul 2006 01:11:22 -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 62977-07 for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue, 11 Jul 2006 01:11:18 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-
Received: from nf-out-0910.google.com (nf-out-0910.google.com
 [64.233.182.187])
	by postgresql.org (Postfix) with ESMTP id 972679FB26C
	for <pgsql-sql@postgresql.org>; Tue, 11 Jul 2006 01:11:18 -0300 (ADT)
Received: by nf-out-0910.google.com with SMTP id x30so103863nfb
	for <pgsql-sql@postgresql.org>; Mon, 10 Jul 2006 21:11:17 -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=iwp7moa8+JIFvjEiwRVO2+qVEF2Exyk83SAuAp2UyQMgtA+9Y46JKFkHUCqWhFSA4drn0XYu/x7i5hkT7S+m9utIZ2ZFeNk5j+h5DK6FoDn9uZv1uzxhJ+wuJYrEnyqOQ6ZOeJfPO0FWsWK20YSh5ZEnI6eX/Ni27eyX88+ZBu4=
Received: by 10.78.159.7 with SMTP id h7mr1983273hue;
	Mon, 10 Jul 2006 21:11:17 -0700 (PDT)
Received: by 10.78.29.2 with HTTP; Mon, 10 Jul 2006 21:11:16 -0700 (PDT)
Message-ID: <bf05e51c0607102111u63847a04i4d0b446dc7c91ebf@mail.gmail.com>
Date: Mon, 10 Jul 2006 23:11:16 -0500
From: "Aaron Bono" <postgresql@aranya.com>
To: "Tom Lane" <tgl@sss.pgh.pa.us>
Subject: Re: Can function results be used in WHERE?
Cc: "Bryce Nesbitt" <bryce1@obviously.com>, pgsql-sql@postgresql.org
In-Reply-To: <8088.1152589020@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_44410_16766210.1152591076959"
References: <44B29376.2030107@obviously.com>
	<bf05e51c0607101302w271c7773tdec47e76b192f2c9@mail.gmail.com>
	<44B2CFC7.7060101@obviously.com> <8088.1152589020@sss.pgh.pa.us>
X-Google-Sender-Auth: 4fb31a23bd261acc
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200607/171
X-Sequence-Number: 25745

------=_Part_44410_16766210.1152591076959
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On 7/10/06, Tom Lane <tgl@sss.pgh.pa.us> 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, but it's somewhat defensible on the grounds that
> neither ordering nor grouping by a simple constant has any possible
> real-world use; so the special case doesn't break anything of interest.
> This would certainly not be so if we were to randomly replace integer
> constants in general WHERE conditions with non-constant values.


I agree whole heartedly with Tom, using the number in the ORDER BY is ugly
and not recommended.  Using column names is much easier to read and is much
more maintainable by team members.  I have to admit the 4 < 1 did confuse me
at first.

-Aaron Bono

------=_Part_44410_16766210.1152591076959
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On 7/10/06, <b class="gmail_sendername">Tom Lane</b> &lt;<a href="mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>&gt; wrote:<div><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<br>But as far as the underlying misconception goes, you seem to think that<br>&quot;4&quot; in the WHERE clause might somehow be taken as referring to the<br>fourth SELECT result column (why you don't think that the &quot;1&quot; would
<br>likewise refer to the first result column isn't clear).&nbsp;&nbsp;This is not so.<br>&quot;4&quot; means the numeric value four.&nbsp;&nbsp;There is a special case in ORDER BY<br>and GROUP BY that an argument consisting of a simple integer literal
<br>constant will be taken as a reference to an output column.&nbsp;&nbsp;This is an<br>ugly kluge IMHO, but it's somewhat defensible on the grounds that<br>neither ordering nor grouping by a simple constant has any possible<br>real-world use; so the special case doesn't break anything of interest.
<br>This would certainly not be so if we were to randomly replace integer<br>constants in general WHERE conditions with non-constant values.</blockquote><div><br>I agree whole heartedly with Tom, using the number in the ORDER BY is ugly and not recommended.&nbsp; Using column names is much easier to read and is much more maintainable by team members.&nbsp; I have to admit the 4 &lt; 1 did confuse me at first.
<br></div><br>-Aaron Bono<br></div>

------=_Part_44410_16766210.1152591076959--