Re: Can function results be used in WHERE?

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