Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2] (may be forged)) by hub.org (8.9.3/8.9.3) with ESMTP id KAA56301; Fri, 16 Jul 1999 10:39:01 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id KAA09543; Fri, 16 Jul 1999 10:35:32 -0400 (EDT) To: Michael Richards cc: pgsql-sql@postgreSQL.org, hackers@postgreSQL.org Subject: Re: [HACKERS] Counting bool flags in a complex query In-reply-to: Your message of Fri, 16 Jul 1999 05:37:20 -0300 (ADT) Date: Fri, 16 Jul 1999 10:35:32 -0400 Message-ID: <9541.932135732@sss.pgh.pa.us> From: Tom Lane Michael Richards 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