From: Tom Lane <tgl@sss.pgh.pa.us>
To: Bryce Nesbitt <bryce1@obviously.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Can function results be used in WHERE?
Date: Mon, 10 Jul 2006 23:37:00 -0400
Message-ID: <8088.1152589020@sss.pgh.pa.us> (raw)
In-Reply-To: <44B2CFC7.7060101@obviously.com>
References: <44B29376.2030107@obviously.com>
<bf05e51c0607101302w271c7773tdec47e76b192f2c9@mail.gmail.com>
<44B2CFC7.7060101@obviously.com>
Bryce Nesbitt <bryce1@obviously.com> writes:
> stage=# select
> pod_code,lat,lon,calculate_distance(lat,lon,37.789629,-122.422082) as
> dist from eg_pod where 4 < 1 order by dist desc limit 10;
> [ allegedly returns 10 rows ]
I'm having a real hard time believing any of this: "WHERE 4 < 1" is
a constant FALSE condition and cannot possibly return any rows ...
unless basic integer arithmetic is broken on your platform? 4 is
not less than 1. I think you're showing us a heavily edited version
of your query rather than what you actually typed.
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.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, bryce1@obviously.com
Subject: Re: Can function results be used in WHERE?
In-Reply-To: <8088.1152589020@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox