Re: "Fix" for INT8 literals being parsed as INT4 disqualifying

Oliver,

That patch will be applied soon. But it will *not* be in the 7.4 stable
branch. It will be fixed in head of tree. Since it will break existing
code I didn't want to add it to 7.4 in the middle of beta, or now that
7.4 is RC1.

This argues though for a short release cycle for the jdbc driver that is
off cycle from the rest of the the database to get this out in a
production version sooner rather than later.

thanks,
--Barry

Oliver Jowett wrote:
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>