BUG #16615: Cannot determine type of Date for "is null" expression

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

BUG #16615: Cannot determine type of Date for "is null" expression

PG Doc comments form
The following bug has been logged on the website:

Bug reference:      16615
Logged by:          Hank
Email address:      [hidden email]
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;
                        }

Reply | Threaded
Open this post in threaded view
|

Re: BUG #16615: Cannot determine type of Date for "is null" expression

Tom Lane-2
PG Bug reporting form <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: BUG #16615: Cannot determine type of Date for "is null" expression

yuanhang
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 <[hidden email]> wrote:

PG Bug reporting form <[hidden email]> 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



Reply | Threaded
Open this post in threaded view
|

Re: BUG #16615: Cannot determine type of Date for "is null" expression

David G Johnston
On Mon, Sep 14, 2020 at 12:02 AM yuanhang <[hidden email]> 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.