Re: Confusing order by error

From the docs you linked:

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

The "name" in your order by is a reference to the output column. The
following example shows the same with "foo" instead of name. Once you
use UPPER() it is now an arbitrary expression where the 'name' you are
referring to becomes ambiguous.

SELECT t1.pk, t1.name as foo, t1.ref, CONCAT( t2.id , ':', t2.name )
AS ref_display
FROM test_table as t1
LEFT JOIN test_table as t2 ON t1.ref = t2.pk
ORDER BY foo;
pk | foo | ref | ref_display
----+--------+-----+-------------
2 | barney | 1 | 1000:fred
3 | betty | 2 | 2000:barney
1 | fred | | :
4 | wilma | 1 | 1000:fred
(4 rows)

Someone may correct me if I'm wrong here, but since "name" matches an
output column, it assumes that is what you mean and doesn't bother to
consider that the output column happens to have the same name as a
column in the source tables.

On Fri, Mar 31, 2017 at 3:39 PM, <kbrannen(at)pwhome(dot)com> wrote:
> I'm hoping someone can give us a little help understanding an error in the ORDER BY clause, because when I read https://www.postgresql.org/docs/9.5/static/sql-select.html#SQL-ORDERBY I just don't see anything that explains the behavior.
>
> This is with Pg-9.5.1 on Centos (not that I think the OS matters here).
>
> Consider this table and data, stripped down example of real code:
>
> CREATE TABLE IF NOT EXISTS test_table (
> pk INTEGER PRIMARY KEY,
> id INTEGER NOT NULL,
> name TEXT NOT NULL,
> ref INTEGER REFERENCES test_table
> );
>
> INSERT INTO test_table
> ( pk, id, name, ref )
> VALUES
> ( 1, 1000, 'fred', null ),
> ( 2, 2000, 'barney', 1 ),
> ( 3, 3000, 'betty', 2 ),
> ( 4, 4000, 'wilma', 1 )
> ON CONFLICT DO NOTHING;
>
> select * from test_table;
>
> pk | id | name | ref
> ----+------+--------+-----
> 1 | 1000 | fred |
> 2 | 2000 | barney | 1
> 3 | 3000 | betty | 2
> 4 | 4000 | wilma | 1
> (4 rows)
>
> So far so good, but when we try to use the data in a more meaningful way:
>
> SELECT t1.pk, t1.name, t1.ref, CONCAT( t2.id , ':', t2.name ) AS ref_display
> FROM test_table as t1
> LEFT JOIN test_table as t2 ON t1.ref = t2.pk
> ORDER BY name;
>
> pk | name | ref | ref_display
> ----+--------+-----+-------------
> 2 | barney | 1 | 1000:fred
> 3 | betty | 2 | 2000:barney
> 1 | fred | | :
> 4 | wilma | 1 | 1000:fred
> (4 rows)
>
> That looks reasonable ... but if we change the ORDER BY clause to normalize should the name be mixed case:
>
> SELECT t1.pk, t1.name, t1.ref, CONCAT( t2.id , ':', t2.name ) AS ref_display
> FROM test_table as t1
> LEFT JOIN test_table as t2 ON t1.ref = t2.pk
> ORDER BY UPPER(name);
>
> ERROR: column reference "name" is ambiguous
> LINE 4: ORDER BY UPPER(name);
> ^
>
> Eh? The parser (or whatever phase) understood "ORDER BY name" in the first query, so why did that UPPER() string function make a difference in the second query?
>
> I can almost make sense of it in that when the result tuples are created as it works, there are 2 name fields present: t1.name & t2.name. In the first example they should have the same value but in the second they'd potentially have different values (1 raw and 1 up-cased). But that also doesn't really make sense either as I'd think the first query should have the same issue. I'd think (obviously incorrectly :) that we'd get either both working or both failing, not 1 works while the other fails.
>
> So what's going on here?
>
> Thanks,
> Kevin
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general