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-ORDERBYI 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
functionmake 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
differentvalues (1 raw and 1 up-cased). But that also doesn't really make sense either as I'd think the first query
shouldhave the same issue. I'd think (obviously incorrectly :) that we'd get either both working or both failing, not 1
workswhile the other fails.
So what's going on here?
Thanks,
Kevin