Why lower's not accept an AS declaration ?

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/

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,

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

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

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.)

=?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

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:

=?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.

----- 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 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: "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 !

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

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:

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