FAQ
The query below used to work with 7.0.3. If I change 'lower(title)' to
'title' in the order by clause, then the query is accepted.

area=> select code, wbc.gameid, title from games, wbc where wbc.gameid = games.gameid union select code, null, null as title from wbc where gameid is null order by code, lower(title);
ERROR: Attribute 'title' not found

Search Discussions

  • Tom Lane at Jan 11, 2001 at 7:14 pm

    Bruno Wolff III writes:
    The query below used to work with 7.0.3. If I change 'lower(title)' to
    'title' in the order by clause, then the query is accepted.
    area=> select code, wbc.gameid, title from games, wbc where wbc.gameid = games.gameid union select code, null, null as title from wbc where gameid is null order by code, lower(title);
    ERROR: Attribute 'title' not found
    It may have *appeared* to work in 7.0.3, but the only reason it avoided
    failure was that you were selecting null for title in the second part of
    the union, and thus the fact that it was computing garbage for the value
    of lower(title) in that part was masked. In fact queries of this kind
    have been broken for a long time.

    7.1 only allows the result of a union to be sorted by output-column
    names, not by expressions that do not appear in the output. If you
    think about it, anything else is not well-defined because of UNION's
    elimination of duplicates: if we do
    SELECT a FROM ... UNION SELECT b FROM ... ORDER BY c;
    then there isn't necessarily a unique value of 'c' associated with
    any particular output row --- rows with different 'c' values might
    have gotten merged because they had the same 'a'/'b' values.
    For that matter, if the FROM clauses are different then 'c' may not
    even be meaningful within both SELECTs. Pre-7.1 PG fails to cope with
    any of this, however.

    The current error message does leave something to be desired, however.
    I'll see if I can improve it.

    BTW, you could probably do this query much more simply now by using
    an outer join instead of a union ...

    regards, tom lane

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-bugs @
categoriespostgresql
postedJan 10, '01 at 7:24a
activeJan 11, '01 at 7:14p
posts2
users2
websitepostgresql.org
irc#postgresql

2 users in discussion

Tom Lane: 1 post Bruno Wolff III: 1 post

People

Translate

site design / logo © 2022 Grokbase