pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #16615: Cannot determine type of Date for "is null" expression
4+ messages / 4 participants
[nested] [flat]
* BUG #16615: Cannot determine type of Date for "is null" expression
@ 2020-09-13 00:28 PG Bug reporting form <noreply@postgresql.org>
2020-09-13 14:42 ` Re: BUG #16615: Cannot determine type of Date for "is null" expression Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: PG Bug reporting form @ 2020-09-13 00:28 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: yuanhang.zheng@qq.com
The following bug has been logged on the website:
Bug reference: 16615
Logged by: Hank
Email address: yuanhang.zheng@qq.com
PostgreSQL version: 13beta3
Operating system: Mac OS X
Description:
Steps to reproduce:
----------------------------
I am using Postgres JDBC driver 42.2.16.
Date date = new
SimpleDateFormat("yyyy-MM-dd").parse("2020-09-08");
String sql = "select * from tb_user where (? is null or
createdat > ?)";
PreparedStatement statement = conn.prepareStatement(sql);
statement.setDate(1, new java.sql.Date(date.getTime()));
statement.setDate(2, new java.sql.Date(date.getTime()));
ResultSet resultSet = statement.executeQuery();
Expected result:
------------------------
Can get result set successfully
Actual result:
ERROR: could not determine data type of parameter $1
After reading some codes of Postgres JDBC driver and Postgres server, the
following is my finding.
1. Postgres JDBC driver will always use Oid UNSPECIFIED(0) for Date type.
See PgPreparedStatement.java, in setDate function.
2. In Postgres server, it won't coerce the type to Date even if we provide
the type. See parse_expr.c in transformExprRecurse function.
case T_NullTest:
{
NullTest *n = (NullTest *) expr;
n->arg = (Expr *) transformExprRecurse(pstate, (Node *) n->arg);
/* the argument can be any type, so don't coerce it */
n->argisrow = type_is_rowtype(exprType((Node *) n->arg));
result = expr;
break;
}
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #16615: Cannot determine type of Date for "is null" expression
2020-09-13 00:28 BUG #16615: Cannot determine type of Date for "is null" expression PG Bug reporting form <noreply@postgresql.org>
@ 2020-09-13 14:42 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-09-14 03:23 ` Re: BUG #16615: Cannot determine type of Date for "is null" expression yuanhang <yuanhang.zheng@qq.com>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2020-09-13 14:42 UTC (permalink / raw)
To: yuanhang.zheng@qq.com; +Cc: pgsql-bugs@lists.postgresql.org
PG Bug reporting form <noreply@postgresql.org> writes:
> 1. Postgres JDBC driver will always use Oid UNSPECIFIED(0) for Date type.
> See PgPreparedStatement.java, in setDate function.
You could ask the JDBC crew why they do that, although I suspect they
have reasons.
> 2. In Postgres server, it won't coerce the type to Date even if we provide
> the type. See parse_expr.c in transformExprRecurse function.
There's nothing to coerce it *to*.
I'd suggest working around this with something like
select * from tb_user where (?::date is null or createdat > ?)
Although TBH that query looks pretty fishy to start with. Do you
really want the entire table when the argument is null? Is it even
possible for the argument to be null --- I doubt Java has such a
thing as a null Date?
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #16615: Cannot determine type of Date for "is null" expression
2020-09-13 00:28 BUG #16615: Cannot determine type of Date for "is null" expression PG Bug reporting form <noreply@postgresql.org>
2020-09-13 14:42 ` Re: BUG #16615: Cannot determine type of Date for "is null" expression Tom Lane <tgl@sss.pgh.pa.us>
@ 2020-09-14 03:23 ` yuanhang <yuanhang.zheng@qq.com>
2020-09-14 18:29 ` Re: BUG #16615: Cannot determine type of Date for "is null" expression David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: yuanhang @ 2020-09-14 03:23 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org
Hi Tom,
Thank you for your reply.
1. Here is the comments in JDBC Postgres driver.
// 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)
2. I don’t understand why the type cannot be parsed in T_NullTest. It can be parsed when using the SQL below.
String sql = "select * from tb_user where (createdat = ?)”;
PreparedStatement statement = conn.prepareStatement(sql);
statement.setDate(1, new java.sql.Date(date.getTime()));
3. Thank you for your working around solution. It’s good! I really want the entire table but I will implement pagination when the argument is null. Java actually has Date as null.
> On Sep 13, 2020, at 22:42, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> PG Bug reporting form <noreply@postgresql.org> writes:
>> 1. Postgres JDBC driver will always use Oid UNSPECIFIED(0) for Date type.
>> See PgPreparedStatement.java, in setDate function.
>
> You could ask the JDBC crew why they do that, although I suspect they
> have reasons.
>
>> 2. In Postgres server, it won't coerce the type to Date even if we provide
>> the type. See parse_expr.c in transformExprRecurse function.
>
> There's nothing to coerce it *to*.
>
> I'd suggest working around this with something like
>
> select * from tb_user where (?::date is null or createdat > ?)
>
> Although TBH that query looks pretty fishy to start with. Do you
> really want the entire table when the argument is null? Is it even
> possible for the argument to be null --- I doubt Java has such a
> thing as a null Date?
>
> regards, tom lane
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #16615: Cannot determine type of Date for "is null" expression
2020-09-13 00:28 BUG #16615: Cannot determine type of Date for "is null" expression PG Bug reporting form <noreply@postgresql.org>
2020-09-13 14:42 ` Re: BUG #16615: Cannot determine type of Date for "is null" expression Tom Lane <tgl@sss.pgh.pa.us>
2020-09-14 03:23 ` Re: BUG #16615: Cannot determine type of Date for "is null" expression yuanhang <yuanhang.zheng@qq.com>
@ 2020-09-14 18:29 ` David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: David G. Johnston @ 2020-09-14 18:29 UTC (permalink / raw)
To: yuanhang <yuanhang.zheng@qq.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Mon, Sep 14, 2020 at 12:02 AM yuanhang <yuanhang.zheng@qq.com> wrote:
> 2. I don’t understand why the type cannot be parsed in T_NullTest. It can
> be parsed when using the SQL below.
>
Equality is a binary operator so the unknown argument can be inferred from
the known one.
Is Null is a unary operator so there is nothing available with which to
infer a type.
David J.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2020-09-14 18:29 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-09-13 00:28 BUG #16615: Cannot determine type of Date for "is null" expression PG Bug reporting form <noreply@postgresql.org>
2020-09-13 14:42 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-09-14 03:23 ` yuanhang <yuanhang.zheng@qq.com>
2020-09-14 18:29 ` David G. Johnston <david.g.johnston@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox