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@fastcrypt.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.dobrev@gmail.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@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-jdbc
>
>