pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: yuanhang.zheng@qq.com
Subject: BUG #16615: Cannot determine type of Date for "is null" expression
Date: Sun, 13 Sep 2020 00:28:45 +0000
Message-ID: <16615-1f85b569d6a34277@postgresql.org> (raw)

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



view thread (4+ messages)  latest in thread

Message-ID: <16615-1f85b569d6a34277@postgresql.org>
Permalink:  ../16615-1f85b569d6a34277@postgresql.org/
Also on:    postgresql.org/message-id/16615-1f85b569d6a34277@postgresql.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-bugs@postgresql.org
  Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, yuanhang.zheng@qq.com
  Subject: Re: BUG #16615: Cannot determine type of Date for "is null" expression
  In-Reply-To: <16615-1f85b569d6a34277@postgresql.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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