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@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