pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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



view thread (12+ messages)  latest in thread

Message-ID: <9541.932135732@sss.pgh.pa.us>
Permalink:  ../9541.932135732@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/9541.932135732@sss.pgh.pa.us

 · 

reply

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