Обсуждение: BUG #16615: Cannot determine type of Date for "is null" expression
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;
}
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
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
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.