Обсуждение: postgresql-8.0.jar and failure of ORDER BY parameters

Hi,

Trying postgresql-8.0-310.jdbc3.jar and
postgresql-8.0-311.jdbc3.jar with a 7.4.5 server, ORDER BY
parameters in a preparedStatement are not taken in account
anymore (were ok when using 7.4.x JAR driver).

E.g.

        SELECT .... ORDER BY ?, ?

..

    actionSt = conn.prepareStatement( getQuery(),
                                          ResultSet.TYPE_SCROLL_INSENSITIVE,
                                          ResultSet.CONCUR_READ_ONLY );

..

    actionSt.setInt( 1, 4 );
    actionSt.setInt( 2, 5 );


the last part setting ORDER BY parameters, which have no effect
on the resultset we obtain.

Is there something I can do beside changing the version of the
server used?

Thanks.

--
Guillaume Cottenceau

Guillaume Cottenceau <gc@mnc.ch> writes:
>         SELECT .... ORDER BY ?, ?

>     actionSt.setInt( 1, 4 );
>     actionSt.setInt( 2, 5 );

This would appear to be asking for a sort by two constant values,
which of course is not going to order the data usefully at all
--- all the rows will have the same sort keys.

I suppose that it behaved differently in older versions of the JDBC
driver that didn't implement real server-side prepared statements.
If the parameters are substituted textually before the server sees
the command, then you have "ORDER BY 4,5" which is interpreted according
to the old SQL92 syntax --- ie, sort by fourth and fifth columns of the
output.  But anything beyond a simple integer literal --- in particular,
a parameter symbol --- is treated as an expression value per SQL99.

So no, you can't do it like that.  The fact that it happened to "work"
before was an artifact of the implementation.

            regards, tom lane

Tom Lane <tgl 'at' sss.pgh.pa.us> writes:

> Guillaume Cottenceau <gc@mnc.ch> writes:
> >         SELECT .... ORDER BY ?, ?
>
> >     actionSt.setInt( 1, 4 );
> >     actionSt.setInt( 2, 5 );
>
> This would appear to be asking for a sort by two constant values,
> which of course is not going to order the data usefully at all
> --- all the rows will have the same sort keys.
>
> I suppose that it behaved differently in older versions of the JDBC
> driver that didn't implement real server-side prepared statements.
> If the parameters are substituted textually before the server sees
> the command, then you have "ORDER BY 4,5" which is interpreted according
> to the old SQL92 syntax --- ie, sort by fourth and fifth columns of the
> output.  But anything beyond a simple integer literal --- in particular,

Yes, that's what it did. I thought this was "normal" behaviour
(even if it's cumbersome IMHO) so I have not explained it.

I'd better write for example:

        actionSt.setString( 1, "surname" )

in order to sort by the value of the column named "surname", but
this has never "worked". Is it supposed to work now? I just tried
that but it didn't change the resultset, btw.

> a parameter symbol --- is treated as an expression value per SQL99.
>
> So no, you can't do it like that.  The fact that it happened to "work"
> before was an artifact of the implementation.

Is there any way to give parameters to ORDER BY in a
preparedStatement then?

Thanks.

--
Guillaume Cottenceau

Guillaume Cottenceau wrote:
> Is there any way to give parameters to ORDER BY in a
> preparedStatement then?

This seems impossible since the aim of the preparedStatement is the
caching of the execution plan which is affected by the ORDER BY clause.
The same reason applies for table name or columns names.
The parameters should be real parameters, not part of the query structure.

Guillaume Cottenceau <gc@mnc.ch> writes:
> I'd better write for example:
>         actionSt.setString( 1, "surname" )
> in order to sort by the value of the column named "surname", but
> this has never "worked". Is it supposed to work now?

No, that's just a different way of sorting by a constant.

Parameters are *values*, they are not references to columns, and so
there is no way to do what you want.  The fact that it happened to
work like that before was an implementation artifact that has now
gone away.

AFAICS you'll have to set up multiple prepared statements with all the
different orderings you want.  This is not different from having to set
up different statements depending on which columns you want displayed
--- would you expect "SELECT ? FROM mytab" to allow run-time
selection of a column?

            regards, tom lane

Tom Lane <tgl 'at' sss.pgh.pa.us> writes:

> Guillaume Cottenceau <gc@mnc.ch> writes:
> > I'd better write for example:
> >         actionSt.setString( 1, "surname" )
> > in order to sort by the value of the column named "surname", but
> > this has never "worked". Is it supposed to work now?
>
> No, that's just a different way of sorting by a constant.
>
> Parameters are *values*, they are not references to columns, and so
> there is no way to do what you want.  The fact that it happened to
> work like that before was an implementation artifact that has now
> gone away.

Ok.

> AFAICS you'll have to set up multiple prepared statements with all the
> different orderings you want.  This is not different from having to set
> up different statements depending on which columns you want displayed
> --- would you expect "SELECT ? FROM mytab" to allow run-time
> selection of a column?

True enough.

Thanks for the light.

--
Guillaume Cottenceau