PostgreSQL - why can't I ORDER BY a function of a column?-CodePudding

I'm trying to get something I thought should be relatively simple (it works in Oracle and MySQL). The PostgreSQL fiddle for the code below is available here - just change the server to check out the others.

Very simple test case:

CREATE TABLE x
(
  y CHAR(1)
);

populate:

INSERT INTO x VALUES ('x');

and

INSERT INTO x VALUES('y');

then (works - as one would expect):

SELECT
  y AS the_char
FROM 
  x
ORDER BY the_char;

Result:

the_char
       x
       y

But then, if I try the following:

SELECT
  y AS the_char
FROM 
  x
ORDER BY ASCII(the_char);

I receive an error:

ERROR:  column "the_char" does not exist
LINE 5: ORDER BY ASCII(the_char);

As mentioned, this works with Oracle and MySQL, but not on PostgreSQL, Firebird and SQL Server.

Can anyone explain why? What is it about a simple function of the column that causes the ORDER BY to fail? This seems to conflict with the manual here which says:

The sort expression(s) can be any expression that would be valid in the query's select list. An example is:

SELECT a, b FROM table1 ORDER BY a b, c;

CodePudding user response：

Only input columns can be used in expressions other than simple column names, as specified in the documentation:

Each expression can be the name or ordinal number of an output column (SELECT list item), or it can be an arbitrary expression formed from input-column values.

Read also this note:

In the SQL-92 standard, an ORDER BY clause can only use output column names or numbers, while a GROUP BY clause can only use expressions based on input column names. PostgreSQL extends each of these clauses to allow the other choice as well (but it uses the standard's interpretation if there is ambiguity). PostgreSQL also allows both clauses to specify arbitrary expressions. Note that names appearing in an expression will always be taken as input-column names, not as output-column names.

CodePudding user response：

If you read the documentation more closely, it states the aliases or column output names cannot be used in an expression in order by clause.

Note that an output column name has to stand alone, that is, it cannot be used in an expression

SELECT a b AS sum, c FROM table1 ORDER BY sum c; -- wrong

So, you're going to need

SELECT
  y AS the_char
FROM 
  x
ORDER BY ASCII(y);

CodePudding user response：

An output column name (as the_char) can only be used as a plain value in the ORDER BY clause, but not as part of an expression; y ,on the other hand, can be freely used in an expression for ordering. You can do:

select y as the_char
from x
order by ascii(y)

Alternatively, you can use a subquery to change the scope of the output column the_char so it can be managed and processed as a bona fide column. For example:

select *
from (
  select y as the_char from x
) z
order by ascii(the_char)