Re: PreparedStatement.setDate() behavior with OVERLAPS

Dave,

Done. I confirm that the problem is repeatable (as is the workaround of
adding "::date") with a build from git (origin/master).

Thanks,
Christopher

On 11 August 2015 at 16:23, Dave Cramer <pg(at)fastcrypt(dot)com> wrote:

> Christopher,
>
> Yes, the latter, and the command that should work is
>
> ant clean
> ant snapshot
>
>
> Dave Cramer
>
> dave.cramer(at)credativ(dot)ca
> http://www.credativ.ca
>
> On 11 August 2015 at 10:20, Christopher BROWN <brown(at)reflexe(dot)fr> wrote:
>
>> Dave,
>>
>> I can try building it. I've cloned git(at)github(dot)com:pgjdbc/pgjdbc.git but
>> running "ant -projecthelp" just lists a lot of Maven Central-related tasks,
>> and running "ant jar" yielded the following message:
>> build.xml:107: snapshot-version or release-version target must run as the
>> first task
>>
>> I'm using Ant 1.9.6 and Java 8 by default. What command should I use to
>> build it? (and what command should I use to clean up generated stuff?)
>>
>> Is it already fixed, or do you want me to check before you try fixing it
>> (I'm guessing the second option)?
>>
>> Thanks,
>> Christopher
>>
>>
>>
>> On 11 August 2015 at 16:10, Dave Cramer <pg(at)fastcrypt(dot)com> wrote:
>>
>>>
>>> Dave Cramer
>>>
>>> dave.cramer(at)credativ(dot)ca
>>> http://www.credativ.ca
>>>
>>> On 11 August 2015 at 10:01, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>>
>>>> Christopher BROWN <brown(at)reflexe(dot)fr> writes:
>>>> > When I use the following query as a PreparedStatement with the
>>>> 9.4.1201
>>>> > JDBC driver, using a 9.4.4 database, the SQL is rejected:
>>>>
>>>> > SELECT id, ctime, mtime, is_archived, ref_store, ref_supplier,
>>>> > period_begins, period_ends, received_by, received_on, received_qty,
>>>> > disposed_qty FROM store_delivery WHERE (period_begins, period_ends +
>>>> > interval '1 day') OVERLAPS (?, ? + interval '1 day') AND ref_store = ?
>>>> > ORDER BY period_begins, ctime
>>>>
>>>> > Specifically, with "invalid input syntax for type interval". I'm
>>>> setting
>>>> > the first two parameters to java.sql.Date values, using "setDate"
>>>> method of
>>>> > PreparedStatement (the third parameter is an integer, ex 4251).
>>>>
>>>> FWIW, the same would happen if you just did this in psql:
>>>>
>>>> regression=# select '2015-09-06' + interval '1 day';
>>>> ERROR: invalid input syntax for type interval: "2015-09-06"
>>>>
>>>> The server uses various heuristics to determine the type of an unmarked
>>>> literal or parameter symbol, and the first one that applies in this
>>>> context is "assume it's the same type as the other input to the binary
>>>> operator". So the only way to make this work is to explicitly tell the
>>>> server that the parameter is of type date or timestamp. You could do
>>>> that
>>>> within the SQL string with "::date", which as you mentioned fixes the
>>>> problem. However, I'd have expected that if you set the parameter with
>>>> setDate or equivalent, the JDBC driver would pass along the information
>>>> that the value is of type date. I'm not sure what the restrictions are
>>>> on
>>>> making that happen, but that's the area to sniff around in. Maybe
>>>> you're
>>>> actually using setString, for example? Or using protocol version 2,
>>>> which
>>>> doesn't have a provision for passing parameter type data?
>>>>
>>>> regards, tom lane
>>>>
>>>
>>> Tom,
>>>
>>>
>>> I suspect it is getting lumped into the time/timestamp and we send it
>>> over as unknown... If so we can fix setDate.
>>>
>>> Christopher, can you try this on HEAD. If you can build it ?
>>>
>>>>
>>>>
>>>> --
>>>> 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
>>>>
>>>
>>>
>>
>