Re: PreparedStatement.setDate() behavior with OVERLAPS

OK,

Can you file a bug on github please ?

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On 11 August 2015 at 10:36, Christopher BROWN <brown(at)reflexe(dot)fr> wrote:

> 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
>>>>>
>>>>
>>>>
>>>
>>
>