Re: [HACKERS] Counting bool flags in a complex query

On Fri, 16 Jul 1999, Tom Lane wrote:

> Good plan. Although you could sort by a user-defined function result,
> it's likely to be horribly slow (because user-defined functions are
> slow:-().
Yes, but I did include my horrible design ideas so you could see why in
"god's name" I was trying to do what I was trying to do when I found what
looked to be a "bug"

> This is not so much a bug as a definitional issue. For SQL92
> compatibility, we accept ORDER BY a column label so long as it's
> a bare column label, but column labels are NOT part of the namespace
> for full expression evaluation. You can't do this either:
>
> select 1 as "test" , test<9 ;
> ERROR: attribute 'test' not found
>
> There are all sorts of squirrely questions about this feature IMHO.
> For example,
>
> create table z1 (f1 int4, f2 int4);
> CREATE
> select f1 as f2, f2 from z1 order by f2;
> f2|f2
> --+--
> (0 rows)
>
> Which column do you think it's ordering by? Which column *should* it
> order by? I think this ought to draw an "ambiguous column label" error
> ... there is code in there that claims to be looking for such a thing,
> in fact, so I am not quite sure why it doesn't trigger on this example.

Good point. Is there anything in the SQL standard that defined how this
"is supposed" to work? I suppose with no expression support it isn't
really necessary. How about requiring quotes when we're to look at it was
"named" columns? If
select f1 as f2, f2 from z1 order by "f2";

Of course I have no idea how this would conflicy with SQL-92. It's more of
an idea...

-Michael