Thread: Why lower's not accept an AS declaration ?

Hi,

May be my question is stupid ... but I'm a little suprised :

SELECT id_letter as letter from my_table;

letter
-----
B
C
a
A

SELECT id_letter as letter from my_table order by letter;

letter
-----
A
B
C
a

SELECT id_letter as letter from my_table order by lower(letter);

ERROR:  Attribute "letter" not found

You can imagine my test is simple, in practise it's not the reallity of my
original request ... but this example is just to show that lower() function
does not accept an AS declaration ... is it normal ?

Thanks for your answers ...

Regards,
--
Hervé Piedvache

Elma Ingénierie Informatique
6 rue du Faubourg Saint-Honoré
F-75008 - Paris - France
Pho. 33-144949901
Fax. 33-144949902

On Mon, Aug 18, 2003 at 10:20:13AM +0200, Hervé Piedvache wrote:
> You can imagine my test is simple, in practise it's not the reallity of my
> original request ... but this example is just to show that lower() function
> does not accept an AS declaration ... is it normal ?

Yes, that's normal. It's even required by the SQL spec AFAIK. If it's a
problem of multiple evaluation, you can use subqueries in the FROM clause.

--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> "All that is needed for the forces of evil to triumph is for enough good
> men to do nothing." - Edmond Burke
> "The penalty good people pay for not being interested in politics is to be
> governed by people worse than themselves." - Plato

On Monday 18 August 2003 10:20, Hervé Piedvache wrote:
> Hi,
>
> May be my question is stupid ... but I'm a little suprised :
>
> SELECT id_letter as letter from my_table;
>
> letter
> -----
> B
> C
> a
> A
>
> SELECT id_letter as letter from my_table order by letter;
>
> letter
> -----
> A
> B
> C
> a
>
> SELECT id_letter as letter from my_table order by lower(letter);
>
> ERROR:  Attribute "letter" not found
>


Why did you change column name to "letter" in last query, and all the other
queries have "id_letter" as column name. What is table structure exactly ?
I assume that You don't have column with "letter" at all.

Regards !

On Monday 18 August 2003 13:04, Darko Prenosil wrote:
> On Monday 18 August 2003 10:20, Hervé Piedvache wrote:
> > Hi,
> >
> > May be my question is stupid ... but I'm a little suprised :
> >
> > SELECT id_letter as letter from my_table;
> >
> > letter
> > -----
> > B
> > C
> > a
> > A
> >
> > SELECT id_letter as letter from my_table order by letter;
> >
> > letter
> > -----
> > A
> > B
> > C
> > a
> >
> > SELECT id_letter as letter from my_table order by lower(letter);
> >
> > ERROR:  Attribute "letter" not found
>
> Why did you change column name to "letter" in last query, and all the other
> queries have "id_letter" as column name. What is table structure exactly ?
> I assume that You don't have column with "letter" at all.
>
> Regards !


OK, now I see exactly the mistake You are making:

    SELECT id_letter as letter from my_table order by lower(id_letter);

would be correct query, because "letter" is only alias for result column, not
column in "my_table". Sorry I didn't see it first time.

Regards !

Hi,

An to be more precise what I exactly want to do :

select
case when 'now' between t.begin and t.end then t.login else 'None' end as log
from my_table t
order by lower(log);

Is it an example more realistic for you to make an order by lower of something
as an alias ?

Regards,

Le Lundi 18 Août 2003 13:15, Darko Prenosil a écrit :
> On Monday 18 August 2003 13:04, Darko Prenosil wrote:
> > On Monday 18 August 2003 10:20, Hervé Piedvache wrote:
> > > Hi,
> > >
> > > May be my question is stupid ... but I'm a little suprised :
> > >
> > > SELECT id_letter as letter from my_table;
> > >
> > > letter
> > > -----
> > > B
> > > C
> > > a
> > > A
> > >
> > > SELECT id_letter as letter from my_table order by letter;
> > >
> > > letter
> > > -----
> > > A
> > > B
> > > C
> > > a
> > >
> > > SELECT id_letter as letter from my_table order by lower(letter);
> > >
> > > ERROR:  Attribute "letter" not found
> >
> > Why did you change column name to "letter" in last query, and all the
> > other queries have "id_letter" as column name. What is table structure
> > exactly ? I assume that You don't have column with "letter" at all.
> >
> > Regards !
>
> OK, now I see exactly the mistake You are making:
>
>     SELECT id_letter as letter from my_table order by lower(id_letter);
>
> would be correct query, because "letter" is only alias for result column,
> not column in "my_table". Sorry I didn't see it first time.
>
> Regards !
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match

--
Hervé Piedvache

Elma Ingénierie Informatique
6 rue du Faubourg Saint-Honoré
F-75008 - Paris - France
Pho. 33-144949901
Fax. 33-144949902

Hi,

Le Lundi 18 Août 2003 13:15, Darko Prenosil a écrit :
> On Monday 18 August 2003 13:04, Darko Prenosil wrote:
> > On Monday 18 August 2003 10:20, Hervé Piedvache wrote:
> > > Hi,
> > >
> > > May be my question is stupid ... but I'm a little suprised :
> > >
> > > SELECT id_letter as letter from my_table;
> > >
> > > letter
> > > -----
> > > B
> > > C
> > > a
> > > A
> > >
> > > SELECT id_letter as letter from my_table order by letter;
> > >
> > > letter
> > > -----
> > > A
> > > B
> > > C
> > > a
> > >
> > > SELECT id_letter as letter from my_table order by lower(letter);
> > >
> > > ERROR:  Attribute "letter" not found
> >
> > Why did you change column name to "letter" in last query, and all the
> > other queries have "id_letter" as column name. What is table structure
> > exactly ? I assume that You don't have column with "letter" at all.
> >
> > Regards !
>
> OK, now I see exactly the mistake You are making:
>
>     SELECT id_letter as letter from my_table order by lower(id_letter);
>
> would be correct query, because "letter" is only alias for result column,
> not column in "my_table". Sorry I didn't see it first time.

Mistake ? Why a mistake ... it's as you said an alias ... for result ... I
know that ... but why order by my alias is running ... and NOT an order by
with a LOWER() function call is not running ... that's mean LOWER() do not
accept alias ... but ORDER accept ... so sorry for me it's not so logical !
;o)

Regards,
--
Hervé Piedvache

Elma Ingénierie Informatique
6 rue du Faubourg Saint-Honoré
F-75008 - Paris - France
Pho. 33-144949901
Fax. 33-144949902

=?iso-8859-15?q?Herv=E9=20Piedvache?= <herve@elma.fr> writes:
> Is it an example more realistic for you to make an order by lower of
> something as an alias ?

Aliases attached to SELECT output columns are visible outside the
SELECT, not inside it.  The special case for ORDER BY simple-column-name
is a kluge for compatibility with a now-obsolete version of the SQL spec
(SQL92 expects this to work, SQL99 doesn't) and we aren't going to
extend it.  See past discussions in the archives (I seem to recall
answering this same question within the past week...)

            regards, tom lane

On Mon, 18 Aug 2003, [iso-8859-15] Herv� Piedvache wrote:

> Mistake ? Why a mistake ... it's as you said an alias ... for result ... I
> know that ... but why order by my alias is running ... and NOT an order by
> with a LOWER() function call is not running ... that's mean LOWER() do not
> accept alias ... but ORDER accept ... so sorry for me it's not so logical !

SQL92 doesn't allow expressions in order by, but you can name output
columns of the select list.  We extend the specification in a way similar
to SQL99 to allow expressions on the input columns of the select list.  We
do not extend it to expressions upon the output columns of the select list
(although re-reading SQL99 makes me believe that it would allow such).

On Mon, Aug 18, 2003 at 18:59:44 +0200,
  Hervé Piedvache <herve@elma.fr> wrote:
> Hi,
>
> An to be more precise what I exactly want to do :
>
> select
> case when 'now' between t.begin and t.end then t.login else 'None' end as log
> from my_table t
> order by lower(log);
>
> Is it an example more realistic for you to make an order by lower of something
> as an alias ?

You can repeat the case statement inside the lower function to do what you
want. (Assuming you didn't want 'None' to sort specially.)

Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> Actually, rereading SQL99, I wonder if it would expect this to work.
> Using 14.1's wording on order by clauses, syntax rule 18, h

Hmm ... that section is not exactly crystal-clear, is it?  I had been
thinking of the part about deliberate incompatibilities with SQL92,
but rereading that, I see it only says they've eliminated the ability
to reference output columns by *number*, not by name.

Yet if they merely want to say "we allow expressions in the output
column names", why wouldn't they say that?  This section is about ten
times longer than it would need to be to express that concept.  I get
the impression that they're trying to compromise between allowing output
column names and input column names, but I sure don't follow exactly how
the compromise is supposed to work.  And there are a ton of apparently-
unnecessary restrictions (no grouping, no subqueries in the sort keys)
that make me wonder what's going on.

Can anyone translate this part of the spec into plain English?

            regards, tom lane

----- Original Message -----
From: "Darko Prenosil" <Darko.Prenosil@finteh.hr>
To: "Hervé Piedvache" <herve@elma.fr>; "Postgresql General"
<pgsql-general@postgresql.org>
Sent: Monday, August 18, 2003 10:09 PM
Subject: Re: [GENERAL] Why lower's not accept an AS declaration ?


>
> ----- Original Message -----
> From: "Hervé Piedvache" <herve@elma.fr>
> To: "Darko Prenosil" <darko.prenosil@finteh.hr>; "Postgresql General"
> <pgsql-general@postgresql.org>
> Sent: Monday, August 18, 2003 6:59 PM
> Subject: Re: [GENERAL] Why lower's not accept an AS declaration ?
>
>
> > Hi,
> >
> > An to be more precise what I exactly want to do :
> >
> > select
> > case when 'now' between t.begin and t.end then t.login else 'None' end
as
> log
> > from my_table t
> > order by lower(log);
> >
>
> Here is the rewired query that works :
>
> CREATE TABLE my_table ("begin" timestamp, "end" timestamp, login
> varchar(100));
>
> select case
>     when now() between "t"."begin" and "t"."end"  then t.login
>     else 'None'
>     end
>     as log
> from my_table t
> order by lower(1);
>
>
> where number 1 is the number of result column. I'm puzzled too now,
because
> according to docs, it should work.
> Here is the part from docs that even explains what happens if the real
table
> column name and result alias are the same:
>
> If an ORDER BY expression is a simple name that matches both a result
column
> name and an input column name, ORDER BY will interpret it as the result
> column name. This is the opposite of the choice that GROUP BY will make in
> the same situation. This inconsistency is made to be compatible with the
SQL
> standard.
>
> I must confess that I wasn't reading Your mail carefully. Sorry ! You were
> right !
> Regards !
>
Wrong again ! This works, but it does not sorting anything. We can say that
ORDER BY accepts both column numbers and column aliases, but not column
numbers and aliases as arguments in functions. I can say this because this
works :

select case
    when now() between "t"."begin" and "t"."end"  then lower(t.login)
    else 'none'
    end
    as log
from my_table t
order by 1 ASC;

same as:

select case
 when now() between "t"."begin" and "t"."end"  then lower(t.login)
 else 'none'
 end
 as log
from my_table t
order by log ASC;

Sorry for the mess !
Regards !

On Monday 18 August 2003 18:59, Hervé Piedvache wrote:
> Hi,
>
> An to be more precise what I exactly want to do :
>
> select
> case when 'now' between t.begin and t.end then t.login else 'None' end as
> log from my_table t
> order by lower(log);

How about something like:

select
  case when 'now' between t.begin and t.end then t.login else 'None' end
    as log,
  LOWER(case when 'now' between t.begin and t.end then t.login else 'None'
    end)
    as log_lower
  from my_table t
  order by 2;

Ian Barwick
barwick@gmx.net

----- Original Message -----
From: "Hervé Piedvache" <herve@elma.fr>
To: "Darko Prenosil" <darko.prenosil@finteh.hr>; "Postgresql General"
<pgsql-general@postgresql.org>
Sent: Monday, August 18, 2003 6:59 PM
Subject: Re: [GENERAL] Why lower's not accept an AS declaration ?


> Hi,
>
> An to be more precise what I exactly want to do :
>
> select
> case when 'now' between t.begin and t.end then t.login else 'None' end as
log
> from my_table t
> order by lower(log);
>

Here is the rewired query that works :

CREATE TABLE my_table ("begin" timestamp, "end" timestamp, login
varchar(100));

select case
    when now() between "t"."begin" and "t"."end"  then t.login
    else 'None'
    end
    as log
from my_table t
order by lower(1);


where number 1 is the number of result column. I'm puzzled too now, because
according to docs, it should work.
Here is the part from docs that even explains what happens if the real table
column name and result alias are the same:

If an ORDER BY expression is a simple name that matches both a result column
name and an input column name, ORDER BY will interpret it as the result
column name. This is the opposite of the choice that GROUP BY will make in
the same situation. This inconsistency is made to be compatible with the SQL
standard.

I must confess that I wasn't reading Your mail carefully. Sorry ! You were
right !
Regards !

On Mon, 18 Aug 2003, Tom Lane wrote:

> =?iso-8859-15?q?Herv=E9=20Piedvache?= <herve@elma.fr> writes:
> > Is it an example more realistic for you to make an order by lower of
> > something as an alias ?
>
> Aliases attached to SELECT output columns are visible outside the
> SELECT, not inside it.  The special case for ORDER BY simple-column-name
> is a kluge for compatibility with a now-obsolete version of the SQL spec
> (SQL92 expects this to work, SQL99 doesn't) and we aren't going to
> extend it.  See past discussions in the archives (I seem to recall
> answering this same question within the past week...)

Actually, rereading SQL99, I wonder if it would expect this to work.

Using 14.1's wording on order by clauses, syntax rule 18, h
"Ki is a <value expression>... shall contain a <column reference>.
 i) Let X be any column reference directly contained in Ki.
 ii) If X does not contain an explicit <table or query name> or
  <correlation name>, then Ki shall be a <column name> that shall
  be equivalent to the name of exactly one column of ST."

T is the result of evaluating the query expression. If no sort key refers
to a column that isn't a column of T then ST is the same as T.

If the result of evaluating the query expression (T) is the output of the
query expression with the output column names then it should allow output
column names in the value expressions of the order by clause I believe.

Hervé Piedvache wrote:
> An to be more precise what I exactly want to do :
>
> select
> case when 'now' between t.begin and t.end then t.login else 'None' end as log
> from my_table t
> order by lower(log);
>

Try:

select log from (select case when 'now' between t.begin and t.end then
t.login else 'None' end as log from my_table t) as ss order by lower(log);

HTH,

Joe

On Mon, 18 Aug 2003, Tom Lane wrote:

> Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> > Actually, rereading SQL99, I wonder if it would expect this to work.
> > Using 14.1's wording on order by clauses, syntax rule 18, h
>
> Hmm ... that section is not exactly crystal-clear, is it?  I had been
> thinking of the part about deliberate incompatibilities with SQL92,
> but rereading that, I see it only says they've eliminated the ability
> to reference output columns by *number*, not by name.
>
> Yet if they merely want to say "we allow expressions in the output
> column names", why wouldn't they say that?  This section is about ten
> times longer than it would need to be to express that concept.  I get
> the impression that they're trying to compromise between allowing output
> column names and input column names, but I sure don't follow exactly how
> the compromise is supposed to work.  And there are a ton of apparently-
> unnecessary restrictions (no grouping, no subqueries in the sort keys)
> that make me wonder what's going on.

My reading is basically:

You can make column references to output columns.

If you make column references to things that aren't output columns, then
the query must be a "simple table query" (as per the definition in e).
 If the expression is not equivalent to one of the output value
expressions, the restrictions listed (no grouping, etc...) apply and treat
it as if you added the appropriate columns to the output select
list. ** This bit is very unclear, but it seems reasonable given the
mention of removing extended sort key columns from the output later in
the general rules. **
 If it is equivalent to one of the output value expressions then act
as if the output column name was used instead of the expression.

You cannot use subqueries or set function in the order by.

-- This seems really different from our previous standard reading of SQL92
though.  It implies that you can't really do stuff on input columns
except in very limited cases and that'd be really bad.

Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> -- This seems really different from our previous standard reading of SQL92
> though.  It implies that you can't really do stuff on input columns
> except in very limited cases and that'd be really bad.

Yes, it seems fraught with bogus restrictions, which makes me wonder if
we're interpreting it correctly.

I could understand a definition that says "unqualified names are first
sought as output column names, and if no match then treated as input
column names; qualified names are always input column names".  Perhaps
that's what they're really trying to do, but why all the strange
verbiage?

            regards, tom lane

because the people who created it had doctorate degrees? kidding:-)

Tom Lane wrote:

> Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
>
>>-- This seems really different from our previous standard reading of SQL92
>>though.  It implies that you can't really do stuff on input columns
>>except in very limited cases and that'd be really bad.
>
>
> Yes, it seems fraught with bogus restrictions, which makes me wonder if
> we're interpreting it correctly.
>
> I could understand a definition that says "unqualified names are first
> sought as output column names, and if no match then treated as input
> column names; qualified names are always input column names".  Perhaps
> that's what they're really trying to do, but why all the strange
> verbiage?
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html
>

On Mon, 18 Aug 2003, Tom Lane wrote:

> Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> > -- This seems really different from our previous standard reading of SQL92
> > though.  It implies that you can't really do stuff on input columns
> > except in very limited cases and that'd be really bad.
>
> Yes, it seems fraught with bogus restrictions, which makes me wonder if
> we're interpreting it correctly.
>
> I could understand a definition that says "unqualified names are first
> sought as output column names, and if no match then treated as input
> column names; qualified names are always input column names".  Perhaps
> that's what they're really trying to do, but why all the strange
> verbiage?

Okay, I think many of the random restrictions (in 2a, the grouping,
distinct, set function spec) are to stop you from doing things like:

 select distinct a from table order by b;
 select a,min(b) from table group by a order by c;
 select count(*) from table order by a;

All of which seem badly defined to me since in none of those cases does
the ordering really make sense because you can't necessarily distinctly
choose a value for sorting for each output row (or the output row in the
last case).

The whole definition of simple table query seems to boil down to the fact
that the query expression must be a query specification (which would
appear to kill UNION/INTERSECT/EXCEPT, which makes sense since input
column names aren't necessarily meaningful in that case). I believe that
the grammar for query expression seems to allow something like FOO INNER
JOIN BAR ON (FOO.A=BAR.B) as an entire query expression without a SELECT
or select list -- and that would be disallowed as well --, but AFAIK we
don't support that anyway.

So the rules for the input column references are:
 You cannot do it through distinct, group by, set functions or
UNION/INTERSECT/EXCEPT.  You can also not do it through some wierd SQL99
constructs we don't support. :)

Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> Okay, I think many of the random restrictions (in 2a, the grouping,
> distinct, set function spec) are to stop you from doing things like:

>  select distinct a from table order by b;
>  select a,min(b) from table group by a order by c;
>  select count(*) from table order by a;

> All of which seem badly defined to me

Agreed, but restrictions on those grounds should be identical to the
restrictions on what you can write in a SELECT-list item.  AFAICT the
restrictions actually cited here are quite different.

> The whole definition of simple table query seems to boil down to the fact
> that the query expression must be a query specification (which would
> appear to kill UNION/INTERSECT/EXCEPT, which makes sense since input
> column names aren't necessarily meaningful in that case).

Right, you could only use output column names for an ORDER BY on a
UNION/etc.  We have that restriction already.  But is that really all
they're saying here?

            regards, tom lane

On Mon, 18 Aug 2003, Tom Lane wrote:

> Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> > Okay, I think many of the random restrictions (in 2a, the grouping,
> > distinct, set function spec) are to stop you from doing things like:
>
> >  select distinct a from table order by b;
> >  select a,min(b) from table group by a order by c;
> >  select count(*) from table order by a;
>
> > All of which seem badly defined to me
>
> Agreed, but restrictions on those grounds should be identical to the
> restrictions on what you can write in a SELECT-list item.  AFAICT the
> restrictions actually cited here are quite different.

I see that it's different for grouping for example because it doesn't
mention the grouping columns as being okay, although simple column
references to input names of grouping columns that are directly mentioned
in the select list are okay because of the equivalence.

I can't really think of any other way to interpret that section
particularly differently.  If it's a simple table query and the expression
is not equivalent to a select list item then it can't use distinct or
group by or a set function.  We might argue about the meaning of simple
table query or equivalent but 2.A.I and II seem pretty straightforward.
I'm a little vague on why they worded IV the way they did, but VI seems to
imply that you're adding items to the sort table (that you take out later)
in order to make the column references match up.

> > The whole definition of simple table query seems to boil down to the fact
> > that the query expression must be a query specification (which would
> > appear to kill UNION/INTERSECT/EXCEPT, which makes sense since input
> > column names aren't necessarily meaningful in that case).
>
> Right, you could only use output column names for an ORDER BY on a
> UNION/etc.  We have that restriction already.  But is that really all
> they're saying here?

The two cases I mentioned, union and its ilk and the loose joins without a
select list are the cases I could see going through the section on query
expression definition.  There could be more, but AFAICS
SELECT <selectlist> FROM <table expression> seems to meet the
requirements mentioned by simple table query.

Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> I can't really think of any other way to interpret that section
> particularly differently.  If it's a simple table query and the expression
> is not equivalent to a select list item then it can't use distinct or
> group by or a set function.

But this is bogus.  What is wrong with

    SELECT a, max(b) FROM t GROUP BY a ORDER BY min(c)

It would certainly be legal as

    SELECT a, max(b), min(c) AS ocol FROM t GROUP BY a ORDER BY ocol

but SQL99 seems to be written so that you can't write the former ---
which leaves me wondering exactly what they mean by features E121-02
and E121-03 ...

After reading over the spec again I finally realized the significance of
this bit:

              i) Let X be any <column reference> directly contained in K(i).
             ii) If X does not contain an explicit <table or query name> or
                 <correlation name>, then K(i) shall be a <column name> that
                 shall be equivalent to the name of exactly one column of
                 ST.

Although they manage not to say so in so many words, it seems their
solution to the output-column-name vs input-column-name ambiguity is
that unqualified names in ORDER BY are output names, and qualified names
are input names.  Period, no alternatives.

I think we'd create too much of a backwards compatibility problem for
ourselves if we adopt this verbatim.  I could go for (a) qualified names
are input columns, (b) unqualified names are sought first as output
columns and second as input columns.  This would accept every SQL99-
or SQL92-compatible query correctly.  It would also accept most queries
that we've historically accepted -- the gotchas would come if you rename
an output column with a name that conflicts with an input column, and
then refer to that (unqualified) name in an ORDER BY expression.  That
seems like a pretty small population of problems.

As for the other restrictions in the spec, I say lose 'em.  If an
expression would be valid as a SELECT-list entry, it should be valid in
ORDER BY.

(I have no idea exactly how hard this would be to implement, btw.  I
think the existing infrastructure for unnamed joins might help, but
I'm not sure.)

            regards, tom lane

I said:
> After reading over the spec again I finally realized the significance of
> this bit:

>               i) Let X be any <column reference> directly contained in K(i).
>              ii) If X does not contain an explicit <table or query name> or
>                  <correlation name>, then K(i) shall be a <column name> that
>                  shall be equivalent to the name of exactly one column of
>                  ST.

Wait a second ... this is a classic case of reading what you expected
rather than what's there.  I thought that (ii) said "If X does not ...
then X shall be ..." but that's not what it says --- the "then" says
that the whole sort-key K(i) must be an output-column name.

In other words, SQL99 does not allow expressions over output-column
names.  An output-column reference can only appear as a simple name
(same as SQL92, and same as what we allow).  SQL99 allows expressions
over input-column names ... but only if the expressions use
fully-qualified input-column names.

This last is such a stupid restriction that I can't believe I'm reading
it right; is it just too late at night for me?

If I am reading it right, then we already support every case that is
legal per SQL99, and more besides.

            regards, tom lane

On Tue, 19 Aug 2003, Tom Lane wrote:

> I said:
> > After reading over the spec again I finally realized the significance of
> > this bit:
>
> >               i) Let X be any <column reference> directly contained in K(i).
> >              ii) If X does not contain an explicit <table or query name> or
> >                  <correlation name>, then K(i) shall be a <column name> that
> >                  shall be equivalent to the name of exactly one column of
> >                  ST.
>
> Wait a second ... this is a classic case of reading what you expected
> rather than what's there.  I thought that (ii) said "If X does not ...
> then X shall be ..." but that's not what it says --- the "then" says
> that the whole sort-key K(i) must be an output-column name.

Err, yeah.  That's certainly different than reading that as then X shall
be. :(

> In other words, SQL99 does not allow expressions over output-column
> names.  An output-column reference can only appear as a simple name
> (same as SQL92, and same as what we allow).  SQL99 allows expressions
> over input-column names ... but only if the expressions use
> fully-qualified input-column names.
>
> This last is such a stupid restriction that I can't believe I'm reading
> it right; is it just too late at night for me?

Yeah.  These rules don't seem to make much sense, why bother making
it a general value expression if you're going to limit it like that?
It almost makes me wonder what SQL200x is going to do to the clause.

How about we forget that I ever brought it up. ;)

Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> Yeah.  These rules don't seem to make much sense, why bother making
> it a general value expression if you're going to limit it like that?
> It almost makes me wonder what SQL200x is going to do to the clause.

Maybe it's a typo ... could someone check the SQL200x wording?

            regards, tom lane

On Tue, 19 Aug 2003, Tom Lane wrote:

> Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> > Yeah.  These rules don't seem to make much sense, why bother making
> > it a general value expression if you're going to limit it like that?
> > It almost makes me wonder what SQL200x is going to do to the clause.
>
> Maybe it's a typo ... could someone check the SQL200x wording?

It didn't appear to be too recent, but I found a link to what appears to
be an older draft, and the wording in that section seemed to have changed
again.  Maybe some of the other rules moved out to other sections, I
didn't go through it for that.

--------------------------------------------------------------

18) If an <order by clause> is specified, then:

a) Let OBC be the <order by clause>. Let NSK be the number of <sort
specification>s in OBC. For each i between 1 (one) and NSK, let K i be the
<sort key> contained in the i-th <sort specification> in OBC.

b) Let DT be the declared type of K i .

c) Each K i shall contain a <column reference> and shall not contain a
<subquery> or a <set function specification>.

d) If QE is a <query expression body> that is a <non-join query
expression> that is a <non-join query term> that is a <non-join query
primary> that is a <simple table> that is a <query specification>, then
the <cursor specification> is said to be a simple table query.

e) Case:

 i) If <sort specification list> contains any <sort key> K i that contains
a column reference to a column that is not a column of T, then:

  1) The <cursor specification> shall be a simple table query.

  2) Let TE be the <table expression> immediately contained in the <query
specification> QS contained in QE.

  3) Let SL be the <select list> of QS. Let SLT be obtained from SL by
replacing each <column reference> with its fully qualified equivalent
(that is, including either an explicit <correlation name> or an explicit
<table name> including either the keyword MODULE or an explicit <catalog
name> and an explicit <unqualified schema name>).

  4) Let OBCT be obtained from OBC by replacing each <column reference>
that references a column of TE with its fully qualified equivalent (that
is, including either an explicit <corrrelation name> or an explicit <table
name> including either the key-word MODULE or an explicit <catalog name>
and an explicit <unqualified schema name>).

  5) For each i between 1 (one) and NSK, let KT i be the <sort key>
contained in the i-th <sort specification> contained in OBCT.

  6) For each i between 1 (one) and NSK,if KT i has the same left normal
form derivation as the <value expression> immediately contained in some
<derived column> DC of SLT, then:

NOTE 350 � ��Left normal form derivation�� is defined in Subclause 6.1,
"Notation", in ISO/IEC 9075-1.

   A) Case:

    I) If DC simply contains an <as clause>, then let CN be the <column
name> contained in the <as clause>.

    II) Otherwise, let CN be an implementation-dependent <column name>
that is not equivalent to the explicit or implicit <column name> of any
other <derived column> contained in SLT. Let VE be the <value expression>
simply contained in DC. DC is replaced in SLT by VE AS CN

   B) KT i is replaced in OBCT by CN

  7) Let SCR be the set of <column reference>s to columns of TE that
remain in OBCT after the preceding transformation.

  8) Let NSCR be the number of <column reference>s contained in SCR. For
each j between 1 (one) and NCR, let C j be an enumeration of these <column
reference>s.

  9) Case:

   A) If NSCR is 0 (zero), then let SKL be the zero-length string.

   B) Otherwise, Let SKL be the comma-separated list of <derived column>s:
, C 1 , C 2 , ..., C NCR The columns C j are said to be extended sort key
columns.

  10) Let ST be the result of evaluating the <query specification>:
SELECT SLT SKL TE

  11) Let EOBC be OBCT.

 ii) Otherwise, let ST be T and let EOBC be OBC.

f) ST is said to be a sort table.
----------------------------------------------------------