Обсуждение: PreparedStatement.setDate() behavior with OVERLAPS
Hello,
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). Given that I'm in the CEST timezone, that translates to:
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 ('2015-06-27 +02:00:00' , '2015-09-06 +02:00:00' + interval '1 day') AND ref_store = 4251 ORDER BY period_begins, ctime
I can solve this by modifying my input string, such that the first two parameters are written as ?::date, ?::date (and it seems to truncate any positive or negative timezone offset). However, I'm curious about two aspects of this, and would appreciate an explanation if that's possible.
- if I'm using "setDate" (and not "setTimestamp"), why is a timezone offset being tacked onto the date string?
- if a string such as '2015-06-27' isn't understood as a date (it seems to be automatic with a lot of other SQL databases, but then again that doesn't mean the majority are correct...), why doesn't "setDate" automatically prepend "DATE" or append "::date"? In which cases would this be a problem (I'd like to understand instead of writing bad SQL).
Thanks,
Christopher
Christopher
Christopher BROWN <brown@reflexe.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
On 11 August 2015 at 10:01, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Christopher BROWN <brown@reflexe.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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
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
Christopher
On 11 August 2015 at 16:01, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Christopher BROWN <brown@reflexe.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
Dave,
I can try building it. I've cloned git@github.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@fastcrypt.com> wrote:
On 11 August 2015 at 10:01, Tom Lane <tgl@sss.pgh.pa.us> wrote:Christopher BROWN <brown@reflexe.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 laneTom,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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
Christopher,
Yes, the latter, and the command that should work is
ant clean
ant snapshot
On 11 August 2015 at 10:20, Christopher BROWN <brown@reflexe.fr> wrote:
Dave,I can try building it. I've cloned git@github.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 taskI'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,ChristopherOn 11 August 2015 at 16:10, Dave Cramer <pg@fastcrypt.com> wrote:On 11 August 2015 at 10:01, Tom Lane <tgl@sss.pgh.pa.us> wrote:Christopher BROWN <brown@reflexe.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 laneTom,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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
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@fastcrypt.com> wrote:
Christopher,Yes, the latter, and the command that should work isant cleanant snapshotOn 11 August 2015 at 10:20, Christopher BROWN <brown@reflexe.fr> wrote:Dave,I can try building it. I've cloned git@github.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 taskI'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,ChristopherOn 11 August 2015 at 16:10, Dave Cramer <pg@fastcrypt.com> wrote:On 11 August 2015 at 10:01, Tom Lane <tgl@sss.pgh.pa.us> wrote:Christopher BROWN <brown@reflexe.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 laneTom,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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
OK,
Can you file a bug on github please ?
On 11 August 2015 at 10:36, Christopher BROWN <brown@reflexe.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,ChristopherOn 11 August 2015 at 16:23, Dave Cramer <pg@fastcrypt.com> wrote:Christopher,Yes, the latter, and the command that should work isant cleanant snapshotOn 11 August 2015 at 10:20, Christopher BROWN <brown@reflexe.fr> wrote:Dave,I can try building it. I've cloned git@github.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 taskI'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,ChristopherOn 11 August 2015 at 16:10, Dave Cramer <pg@fastcrypt.com> wrote:On 11 August 2015 at 10:01, Tom Lane <tgl@sss.pgh.pa.us> wrote:Christopher BROWN <brown@reflexe.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 laneTom,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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
It strikes me that maybe the root problem is that Christopher is doing
things in such an order that the server is asked to parse the SQL string
before the setDate() is done. Don't know enough details about JDBC to
translate that idea into code, though.
regards, tom lane
Here are the comments in the code.
// We must use UNSPECIFIED here, or inserting a Date-with-timezone into a
// timestamptz field does an unexpected rotation by the server's TimeZone:
//
// We want to interpret 2005/01/01 with calendar +0100 as
// "local midnight in +0100", but if we go via date it interprets it
// as local midnight in the server's timezone:
// template1=# select '2005-01-01+0100'::timestamptz;
// timestamptz
// ------------------------
// 2005-01-01 02:00:00+03
// (1 row)
// template1=# select '2005-01-01+0100'::date::timestamptz;
// timestamptz
// ------------------------
// 2005-01-01 00:00:00+03
// (1 row)
Apparently while dates don't store timezone information we do accept timezone info in date literals ?
On 11 August 2015 at 10:49, Tom Lane <tgl@sss.pgh.pa.us> wrote:
It strikes me that maybe the root problem is that Christopher is doing
things in such an order that the server is asked to parse the SQL string
before the setDate() is done. Don't know enough details about JDBC to
translate that idea into code, though.
regards, tom lane
Dave Cramer <pg@fastcrypt.com> writes:
> Here are the comments in the code.
> // We must use UNSPECIFIED here, or inserting a Date-with-timezone into a
> // timestamptz field does an unexpected rotation by the server's TimeZone:
Ugh. But why not send it as a timestamptz?
(I suspect that the law of conservation of astonishment applies, such that
some other corner cases would act oddly if you do that. But it might be
a net improvement anyway.)
> Apparently while dates don't store timezone information we do accept
> timezone info in date literals ?
Sure. Times, too. The same input parser is used for date, timestamp,
timestamptz, time, and timetz, and then we just throw away irrelevant
fields.
regression=# select '2015-08-11 10:55:04.509393-04'::date;
date
------------
2015-08-11
(1 row)
regards, tom lane
On 11 August 2015 at 11:00, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Dave Cramer <pg@fastcrypt.com> writes:
> Here are the comments in the code.
> // We must use UNSPECIFIED here, or inserting a Date-with-timezone into a
> // timestamptz field does an unexpected rotation by the server's TimeZone:
Ugh. But why not send it as a timestamptz?
Unfortunately we do the same thing with times and timestamps.
We don't parse the sql so we don't know if there is TZ information.
If the underlying column is a timestamp and we set the oid to timstamptz then it fails. IIRC
Dave, This exact pull request introduces notion of TZ and non-TZ classes in jdbc driver: https://github.com/pgjdbc/pgjdbc/pull/340 Well, the PR needs some cleanup, however it allows to bind variables and tell server we are binding TZ. I think it would solve the issue. Another approach is to support java8's java.time APIs, however I think it is more like a long-term solution. Vladimir