Re: Confusing order by error - Mailing list pgsql-general
From | Brian Dunavant |
---|---|
Subject | Re: Confusing order by error |
Date | |
Msg-id | CAJTy2e=U4CWwAsK8FSOeSLr9Lg4P9VaFghdPSs8f6+JQj7Kz3g@mail.gmail.com Whole thread Raw |
In response to | Confusing order by error ("" <kbrannen@pwhome.com>) |
List | pgsql-general |
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@pwhome.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-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 havedifferent values (1 raw and 1 up-cased). But that also doesn't really make sense either as I'd think the first queryshould have the same issue. I'd think (obviously incorrectly :) that we'd get either both working or both failing, not1 works while the other fails. > > So what's going on here? > > Thanks, > Kevin > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
pgsql-general by date: