Confusing order by error

["" <kbrannen@xxxxxxxxxx>]

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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general