Thread

1. PostgreSQL bug in SELECT DISTINCT

   J.R. Onyschak <jonyschak@nvisia.com> — 2001-04-30T19:31:10Z

   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
   
   
   

2. Re: PostgreSQL bug in SELECT DISTINCT

   Thomas Lockhart <lockhart@alumni.caltech.edu> — 2001-05-03T15:19:32Z

   > 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
   
   

3. Re: PostgreSQL bug in SELECT DISTINCT

   Tom Lane <tgl@sss.pgh.pa.us> — 2001-05-03T15:26:10Z

   "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
   
   

4. Re: PostgreSQL bug in SELECT DISTINCT

   J.R. Onyschak <jonyschak@nvisia.com> — 2001-05-03T18:07:44Z

   
   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?
   
   
   

5. Re: PostgreSQL bug in SELECT DISTINCT

   J.R. Onyschak <jonyschak@nvisia.com> — 2001-05-03T18:13:50Z

   
   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.