Re: PreparedStatement.setDate() behavior with OVERLAPS

I'm definitely using setDate(). I don't know how to check the protocol
version, I'm using a simple JDBC URL (the only connection property I'm
using is "currentSchema") with a 9.4.4 database and the 9.4.1201 driver
with Java 8.

I got the error by running the query in JDBC (not using any framework), and
also by calling toString() on the PreparedStatement to understand the
error, which is how I noticed that despite calling "setDate", I also had a
time component in the results of toString(); I don't know if that's a side
effect of toString() or if that's what really gets sent. I'm guessing that
it's close -- if not identical -- to what is actually sent because it seems
curious otherwise that the database doesn't recognize that my date is a
date...

Thanks,
Christopher

On 11 August 2015 at 16: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
>