pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: yuanhang <yuanhang.zheng@qq.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #16615: Cannot determine type of Date for "is null" expression
Date: Mon, 14 Sep 2020 11:23:57 +0800
Message-ID: <tencent_D3DFF8BEF20146DCFBCEB1108A164C741607@qq.com> (raw)
In-Reply-To: <749016.1600008172@sss.pgh.pa.us>
References: <16615-1f85b569d6a34277@postgresql.org>
	<749016.1600008172@sss.pgh.pa.us>

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

view thread (4+ messages)  latest in thread

Message-ID: <tencent_D3DFF8BEF20146DCFBCEB1108A164C741607@qq.com>
Permalink:  ../tencent_D3DFF8BEF20146DCFBCEB1108A164C741607@qq.com/
Also on:    postgresql.org/message-id/tencent_D3DFF8BEF20146DCFBCEB1108A164C741607@qq.com

 · 

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: yuanhang.zheng@qq.com, tgl@sss.pgh.pa.us, pgsql-bugs@lists.postgresql.org
  Subject: Re: BUG #16615: Cannot determine type of Date for "is null" expression
  In-Reply-To: <tencent_D3DFF8BEF20146DCFBCEB1108A164C741607@qq.com>

* 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