Thread: ERROR in 8.0 driver, but not 7.4: SELECT DISTINCT, ORDER BY expressions must appear in select list

ERROR in 8.0 driver, but not 7.4: SELECT DISTINCT, ORDER BY expressions must appear in select list

From

sumit shah

Date:

12 May 2005, 16:46:55

Dear Postgres JDBC hackers:

I've just recently started testing the postgres JDBC 3 driver
(8.0-311) after using JDBC 3 (pg74.216.jdbc).  I now get an error on
a query of the type:

SELECT DISTINCT column1, column2 FROM table1 ORDER BY 1

where the "1" in the ORDER BY is set by PreparedStatement.setInt method

The error is: "SELECT DISTINCT, ORDER BY expressions must appear in
select list"

The error disappears when I change the query to:

SELECT DISTINCT column1, column2 FROM table1 ORDER BY  column1

or when I hard code the ORDER BY 1 in the query instead of setting it
via setInt.

This error occurs only with the 8.0-311 driver or the 8.1dev-400 jdbc
drivers, it does not occur with the pg74-216 driver.  This occurs
with both the 7.4 and 8.0 backends.

Is this change in behavior expected and our code was simply relying
on old semantics or misinterpretation of the JDBC standard?

Thanks,
Sumit

Re: ERROR in 8.0 driver, but not 7.4: SELECT DISTINCT, ORDER

From

Dave Cramer

Date:

12 May 2005, 16:58:05

Yes, it is expected behaviour

see

http://archives.postgresql.org/pgsql-jdbc/2005-05/msg00013.php

for details.

sumit shah wrote:

> Dear Postgres JDBC hackers:
>
> I've just recently started testing the postgres JDBC 3 driver
> (8.0-311) after using JDBC 3 (pg74.216.jdbc).  I now get an error on
> a query of the type:
>
> SELECT DISTINCT column1, column2 FROM table1 ORDER BY 1
>
> where the "1" in the ORDER BY is set by PreparedStatement.setInt method
>
> The error is: "SELECT DISTINCT, ORDER BY expressions must appear in
> select list"
>
> The error disappears when I change the query to:
>
> SELECT DISTINCT column1, column2 FROM table1 ORDER BY  column1
>
> or when I hard code the ORDER BY 1 in the query instead of setting it
> via setInt.
>
> This error occurs only with the 8.0-311 driver or the 8.1dev-400 jdbc
> drivers, it does not occur with the pg74-216 driver.  This occurs
> with both the 7.4 and 8.0 backends.
>
> Is this change in behavior expected and our code was simply relying
> on old semantics or misinterpretation of the JDBC standard?
>
> Thanks,
> Sumit
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
>

--
Dave Cramer
http://www.postgresintl.com
519 939 0336
ICQ#14675561

Arrays Was (Re: ERROR in 8.0 driver, but not 7.4: SELECT DISTINCT, ORDER BY expressions must appear in select list)

From

sumit shah

Date:

13 May 2005, 15:20:56

Thanks, Dave for the pointer.  I guess I didn't have the right key
words in my googling.

Also I had one more question, concerning sql Arrays.  I didn't find
too much documentation on this other than mailing list discussions.
I've created some minimal utility wrapper classes to create
java.sql.Array objects for inserting/updating (i.e. using
PreparedStatement.setArray(x, x)) int, float, double, and String
arrays.  The functions for int and String are at the bottom of this
message and the float and double versions are similar.

Now my question.  I think the int/float/double are reasonably ok, but
the String version needs some help.  Is there a way I can escape the
individual elements?  Also any other comments on the general
implementation?  I believe this should be ok but if future versions
need other methods implemented besides Array.toString() and Array.
than this needs to be modified.  Also can this example be placed in
the documentation for the JDBC driver?  I don't mind adding some more
pointers and explanation if people would find it useful.  I spent
quite bit of time googling on how to do this, but I ended up having
to dig around the code to see what methods in java.sql.Array required
more than a stub implementation.

Thanks,
Sumit


   public static Array convertStringToPgSqlArray(final String[] s)
throws Exception {
     if(s == null || s.length < 1) return null;
     Array a = new Array() {
       public String getBaseTypeName() {return "text";}
       public int getBaseType()  {return 0;}
       public Object getArray()  {return null;}
       public Object getArray(Map<String, Class<?>> map)  {return null;}
       public Object getArray(long index, int count)  {return null;}
       public Object getArray(long index, int count, Map<String,
Class<?>> map)  {return null;}
       public ResultSet getResultSet()  {return null;}
       public ResultSet getResultSet(Map<String, Class<?>> map)
{return null;}
       public ResultSet getResultSet(long index, int count)  {return
null;}
       public ResultSet getResultSet(long index, int count,
Map<String, Class<?>> map) {return null;}

       public String toString() {
         String p = "{";
         if(s.length == 0) {
         } else {
           for(int i = 0; i < s.length - 1; i++) p += s[i] + ",";
           p += "'" + s[s.length - 1] + "'";
         }
         p+="}";
         return p;
       }
     };
     return a;
   }



public static Array convertIntegerToPgSqlArray(final int[] p) {
     if(p == null || p.length < 1) return null;
     Array a = new Array() {
       public String getBaseTypeName() {return "int4";}
       public int getBaseType()  {return 0;}
       public Object getArray()  {return null;}
       public Object getArray(Map<String, Class<?>> map)  {return null;}
       public Object getArray(long index, int count)  {return null;}
       public Object getArray(long index, int count, Map<String,
Class<?>> map)  {return null;}
       public ResultSet getResultSet()  {return null;}
       public ResultSet getResultSet(Map<String, Class<?>> map)
{return null;}
       public ResultSet getResultSet(long index, int count)  {return
null;}
       public ResultSet getResultSet(long index, int count,
Map<String, Class<?>> map) {return null;}

       public String toString() {
         String fp = "{";
         if(p.length == 0) {
         } else {
           for(int i = 0; i < p.length - 1; i++) fp += p[i] + ",";
           fp += p[p.length - 1];
         }
         fp+="}";
         return fp;
       }
     };
     return a;
   }


On May 12, 2005, at 9:57 AM, Dave Cramer wrote:

> Yes, it is expected behaviour
>
> see
>
> http://archives.postgresql.org/pgsql-jdbc/2005-05/msg00013.php
>
> for details.
>
> sumit shah wrote:
>
>
>> Dear Postgres JDBC hackers:
>>
>> I've just recently started testing the postgres JDBC 3 driver
>> (8.0-311) after using JDBC 3 (pg74.216.jdbc).  I now get an error
>> on  a query of the type:
>>
>> SELECT DISTINCT column1, column2 FROM table1 ORDER BY 1
>>
>> where the "1" in the ORDER BY is set by PreparedStatement.setInt
>> method
>>
>> The error is: "SELECT DISTINCT, ORDER BY expressions must appear
>> in  select list"
>>
>> The error disappears when I change the query to:
>>
>> SELECT DISTINCT column1, column2 FROM table1 ORDER BY  column1
>>
>> or when I hard code the ORDER BY 1 in the query instead of setting
>> it  via setInt.
>>
>> This error occurs only with the 8.0-311 driver or the 8.1dev-400
>> jdbc  drivers, it does not occur with the pg74-216 driver.  This
>> occurs  with both the 7.4 and 8.0 backends.
>>
>> Is this change in behavior expected and our code was simply
>> relying  on old semantics or misinterpretation of the JDBC standard?
>>
>> Thanks,
>> Sumit
>>
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 4: Don't 'kill -9' the postmaster
>>
>>
>>
>
> --
> Dave Cramer
> http://www.postgresintl.com
> 519 939 0336
> ICQ#14675561
>
>