Michael Richards <miker@scifair.acadiau.ca> writes:
> I've found what I believe is another set of bugs:
I can shed some light on these.
> This may not be valid SQL, as none of my books mention it. Is it possible
> to order by an expression?
Postgres accepts expressions as ORDER BY clauses, although strict SQL92
only allows sorting by a column name or number.
> It looks like the order by is only being applied to the original select,
> not the unioned select. Some authority should check on it, but by thought
> it that a union does not necessarily maintain the order, so the entire
> select should be applied to the order.
That looks like a bug to me too --- I think the ORDER BY is supposed to
apply across the whole UNION result. Will look into it.
> I'm probably going to change the numbering scheme of the system folders so
> they will sort correctly without a kluge such as:
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:-().
> Using a column name within an expression in the order by does not seem to
> work...
> Or a much simpler example to illustrate the bug:
> fastmail=> select 1 as "test" order by (test<9);
> ERROR: attribute 'test' not found
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.
regards, tom lane