From: Tom Lane <tgl@sss.pgh.pa.us>
To: Michael Richards <miker@scifair.acadiau.ca>
Cc: pgsql-sql@postgreSQL.org
Cc: hackers@postgreSQL.org
Subject: Re: [HACKERS] Counting bool flags in a complex query
Date: Fri, 16 Jul 1999 10:35:32 -0400
Message-ID: <9541.932135732@sss.pgh.pa.us> (raw)
In-Reply-To: <Pine.BSF.4.10.9907160447220.38362-100000@scifair.acadiau.ca>
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
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, miker@scifair.acadiau.ca, hackers@postgreSQL.org
Subject: Re: [HACKERS] Counting bool flags in a complex query
In-Reply-To: <9541.932135732@sss.pgh.pa.us>
References: <Pine.BSF.4.10.9907160447220.38362-100000@scifair.acadiau.ca> <9541.932135732@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