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