Re: Regression: Problems with Timestamp arguments

This works:
PREPARE is_date_reached AS SELECT NOW() < $1 OR $1 IS NULL;

However JDBC can not use the same parameter more than once…
SELECT NOW() < ? OR ? IS NULL;

Hmm. That's not good.

2013/9/10 Dave Cramer <pg(at)fastcrypt(dot)com>:
> Hi,
>
> if you try this: prepare foo as select $1 is null;
> in psql you will get
> ERROR: could not determine data type of parameter $1
>
> Why it works with integers I don't know yet, but thought I would pass that
> along
>
> Dave Cramer
>
> dave.cramer(at)credativ(dot)ca
> http://www.credativ.ca
>
>
> On Tue, Sep 10, 2013 at 5:17 AM, Lachezar Dobrev <l(dot)dobrev(at)gmail(dot)com> wrote:
>>
>> Hello colleagues,
>>
>> There seems to be a problem with the latest driver and Timestamp
>> arguments uses in IS NULL comparisons:
>> QUERY: SELECT ? IS NULL
>> ARGUMENTS: statement.setTimestamp(1, new
>> Timestamp(System.currentTimeMillis()))
>>
>> Result:
>> Exception in thread "main" org.postgresql.util.PSQLException: ERROR:
>> could not determine data type of parameter $1
>> at
>> org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157)
>> at
>> org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886)
>> at
>> org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
>> at
>> org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555)
>> at
>> org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)
>> at
>> org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:302)
>>
>> The problem arises on:
>> protocolVersion=3, or not specifying protocol version
>> argument type: Timestamp
>>
>> The problem does not arise on other types (tested on BigDecimal and
>> String)
>> The problem does not arise when using protocolVersion=2
>> The problem does not arise if the expression is not ? IS NULL
>>
>> List of work-around methods:
>> - Degrade the protocol:
>> jdbc:postgresql://host:port/database?protocolVersion=2
>> * does not work with PgPool-2
>>
>> - Explicitly cast the argument
>> SELECT ?::timestamp IS NULL
>> SELECT CAST(? AS timestamp) IS NULL
>> * requires rewriting of currently working code
>>
>> I believe this to be a bug.
>>
>>
>> --
>> Sent via pgsql-jdbc mailing list (pgsql-jdbc(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-jdbc
>
>