Thread: PostgreSQL bug in SELECT DISTINCT

When I execute the following query:

SELECT DISTINCT title FROM division ORDER BY UPPER(title);

I get:
ERROR:  For SELECT DISTINCT, ORDER BY expressions must appear in target list

If I remove DISTINCT, the query works fine.

Is this illegal or a known bug?

thanks for any help,
J.R.

PostgreSQL 7.1
Mandrake 8.0

"J.R. Onyschak" <jonyschak@nvisia.com> writes:
> When I execute the following query:
> SELECT DISTINCT title FROM division ORDER BY UPPER(title);

> I get:
> ERROR:  For SELECT DISTINCT, ORDER BY expressions must appear in target list

> If I remove DISTINCT, the query works fine.

> Is this illegal or a known bug?

This is not a bug, but an intentional restriction to prevent ill-defined
query results.  Why don't you just "ORDER BY title"?

            regards, tom lane

> When I execute the following query:
> SELECT DISTINCT title FROM division ORDER BY UPPER(title);
> I get:
> ERROR:  For SELECT DISTINCT, ORDER BY expressions must appear in target list
> If I remove DISTINCT, the query works fine.
> Is this illegal or a known bug?

Illegal. I believe that allowing any function call in the "order by"
clause is an extension to SQL9x, so feel lucky that you can do it at all
;)

However,

SELECT T FROM (SELECT DISTINCT title FROM division) AS T ORDER BY
UPPER(T);

seems to work in 7.1 (but not in earlier releases). This give PostgreSQL
a chance to hold an intermediate result to sort in a second pass.

                       - Thomas

Tom Lane wrote:

>"J.R. Onyschak" <jonyschak@nvisia.com> writes:
>
>>When I execute the following query:
>>SELECT DISTINCT title FROM division ORDER BY UPPER(title);
>>
>>I get:
>>ERROR:  For SELECT DISTINCT, ORDER BY expressions must appear in target list
>>
>>If I remove DISTINCT, the query works fine.
>>
>>Is this illegal or a known bug?
>>
>
>This is not a bug, but an intentional restriction to prevent ill-defined
>query results.  Why don't you just "ORDER BY title"?
>
>            regards, tom lane
>
I can't/don't want to "ORDER BY title" because the title might be
entered as upper case or lower case.  If we had divisions with titles
Transportation, parks, and Education.  I would like to display the
results alphabetical regardless of capitalization.  I know this example
is a little contrived because all divisions should be capitalized, but
we have a number of "objects" backed by tables that have a title column
that we order by and some of them have a high chance of having mixed
capitalization.    I can understand the prevention of ill-defined query
results, but is PostgreSql being too restrictive?  I am ordering by a
column in the select clause, I am just using a function on that column.

Thanks for the great product.  It truely has been fun using
PostgreSql.Very robust, very easy to use.

Thank you for your help,
jr

P.S. I don't mean for this to sound whiny, but I encounterd this in
porting a project from using Oracle to PostgreSql, so I know that Oracle
supports it and other people might run into this problem.
P.P.S.  Where can I locate a copy of the latest SQL spec?

Thomas Lockhart wrote:

>>When I execute the following query:
>>SELECT DISTINCT title FROM division ORDER BY UPPER(title);
>>I get:
>>ERROR:  For SELECT DISTINCT, ORDER BY expressions must appear in target list
>>If I remove DISTINCT, the query works fine.
>>Is this illegal or a known bug?
>>
>
>Illegal. I believe that allowing any function call in the "order by"
>clause is an extension to SQL9x, so feel lucky that you can do it at all
>;)
>
Where can I look at a copy of the SQL spec?  I've tried to locate it
online, but haven't been able to find it.

>
>
>However,
>
>SELECT T FROM (SELECT DISTINCT title FROM division) AS T ORDER BY
>UPPER(T);
>
>seems to work in 7.1 (but not in earlier releases). This give PostgreSQL
>a chance to hold an intermediate result to sort in a second pass.
>
>                       - Thomas
>
That's interesting.  I remember that, but I don't think I should rely on
it because I am trying to keep our SQL code portable across databases.
(We've already made once change and might have to do another)

Thanks for your time,
J.R.