On Thu, Nov 06, 2003 at 02:13:15PM -0500, James Robinson wrote:
> Folks who use postgresql with JBoss and use non-INT4 number primary
> keys have been plagued with all of their indexes built on those columns
> being ignored in queries as simple as:
>
> SELECT COUNT(*) FROM foo WHERE (id=1234)
>
> on table
>
> CREATE TABLE foo (
> id INT8 primary key not null
> );
[...]
> Anyway, suggestions for fixes have varied to and from fixing in the
> client application (JBoss, in this case), JDBC driver (tried once,
> broke other things), and the backend parser (generally agreed to be the
> best place to fix, but a good solution fixing more than it breaks has
> not jumped up yet).
There's a patch pending that should fix this problem .. it adds an explicit
typecast to all literals that reflects the type provided to JDBC (either
implied by the method used, or from the java.sql.Types value for
setObject()). So the above query actually turns into:
SELECT COUNT(*) FROM foo WHERE (id=1234::int8)
if you use setLong() or setObject(..., Types.BIGINT) to set the parameter.
This does break other cases (e.g. using setLong() when you actually want an
int4 value) but it's necessary to get consistency between the PREPARE and
non-PREPARE paths.. and it's almost an application error in this case
anyway.
I'm not sure what the plan is for applying the patch though -- Barry?
[...]
> This ultimately causes JBoss to make the call to
> PreparedStatement.setObject(int index, Object value, int?targetSqlType)
> method with targetSqlType set to java.sql.Types.VARCHAR, which,
> ultimately, causes the JDBC driver to wrap single quotes around the
> literal long, as in
>
> SELECT COUNT(*) FROM foo WHERE (id='1234')
>
> which causes a different parsing / type cooersion path in the backend
> which ultimately lets the indexes get used.
This is essentially the "fix" that was tried earlier at the driver level,
but it does break other cases so it's not a general solution.
-O
