Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kHiZF-0008W6-Iz for pgsql-bugs@arkaria.postgresql.org; Mon, 14 Sep 2020 07:06:27 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kHiZE-0001V1-EV for pgsql-bugs@arkaria.postgresql.org; Mon, 14 Sep 2020 07:06:24 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kHf6n-0005gl-7f for pgsql-bugs@lists.postgresql.org; Mon, 14 Sep 2020 03:24:50 +0000 Received: from out203-205-221-242.mail.qq.com ([203.205.221.242] helo=qq.com) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kHf6b-00087t-N6 for pgsql-bugs@lists.postgresql.org; Mon, 14 Sep 2020 03:24:48 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=qq.com; s=s201512; t=1600053838; bh=57eqdDw5kQd91zxJ6eY+FdpDjwTUj/htHBxCb1bc1fo=; h=From:Subject:Date:In-Reply-To:Cc:To:References; b=K/2IIonJghvQsTXgMOeM6kdU5cFaBhdNeyUmUtiwRCG11DmgP6QgUXPGl/D+gbzH+ 7nCBQ4KrGTxp5agVwkQzGqK40X00P9fHUtWG3GpOXCrkCoWVX6Enz5pZexLDWpSeCV G/HiS3CjKiKxeXTjNyFx7rUxc3+nl3twSQYS62Hs= Received: from [192.168.199.201] ([120.235.19.1]) by newxmesmtplogicsvrsza7.qq.com (NewEsmtp) with SMTP id 5F93024C; Mon, 14 Sep 2020 11:23:57 +0800 X-QQ-mid: xmsmtpt1600053837tvj4sd4z5 Message-ID: X-QQ-XMAILINFO: NJfaUkwOZVbm7Or3qWrEtiIXz3I8F30VWphR4He+gUgRp1mMvj/sj0cIHT1dIa HiEZPuD4MItfuojnYAVphK+i2Oz3zZ1S2OQYWzLbA3yoiiXguqz4wNNPAMxNB/Bzi8kzcoQglo2C Jh23YcnzUDUZmhEe2gPIDm1kr6wwFz/1s00zVr3TwxsKEw6jv9HnssTo64iy0OH1wYxIHNcK5Rlq 1zPgHxktLO+7+TV7JMhLwvQCzjIn/kylWlSMVKgLwhqNgTYKtycKAEKzyUqntQKK+asYJ6R4lUuH oq30o+Zzh8adN8jNL5WcEru5YzWAx6to55SKtUYrIAuI5Qu0cc13COhQ60WxfdWon+588xkLCljP FBzMPBuzFU7ZFadKbw9b1SckTsRB3UMdtrm3JRuLptg5hQK5XB+6iHbCbm8jwvVkf62TGgQbN4gK r/PSeP6XQKu1h8d1aflB6FObtuowTStWLQYi4Vh8lcDEJiy/cwVnaYEt+ghoZ1CU2tvl/5XKIu4R rDDfC4UyHCIqdV0/pAVq2soeD6J3odkhzs4Gq68T2A2phf+lI3LeztrgVTZ4pKSOX8loPiXBvfcw bUZ4qbPyTXgpzy/Q3vX34agYn7eirLrO+bvBBhbMJgMh2va9jTBPsRHq7Qzg7KsRVszo6j1QIOMs +Xr/3zpUZ6yA0ly9sLz/bDd/bnkEFCvnOXs3VTPqT7K1ppIMa/4a8AMVPP9Vw+ta1Hya+KsurAmL TpvbGcWukxvpu8c6yfLpjp4628JDX7UYaL From: yuanhang X-OQ-MSGID: <00F58B3B-23A2-4659-8D06-4D02D8714B0C@qq.com> Content-Type: multipart/alternative; boundary="Apple-Mail=_9A69D85E-4043-45AF-9E3C-64DA9670AFF2" Mime-Version: 1.0 (Mac OS X Mail 13.4 \(3608.80.23.2.2\)) Subject: Re: BUG #16615: Cannot determine type of Date for "is null" expression Date: Mon, 14 Sep 2020 11:23:57 +0800 In-Reply-To: <749016.1600008172@sss.pgh.pa.us> Cc: pgsql-bugs@lists.postgresql.org To: Tom Lane References: <16615-1f85b569d6a34277@postgresql.org> <749016.1600008172@sss.pgh.pa.us> X-Mailer: Apple Mail (2.3608.80.23.2.2) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --Apple-Mail=_9A69D85E-4043-45AF-9E3C-64DA9670AFF2 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 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=3D# select '2005-01-01+0100'::timestamptz; // timestamptz // ------------------------ // 2005-01-01 02:00:00+03 // (1 row) // template1=3D# select '2005-01-01+0100'::date::timestamptz; // timestamptz // ------------------------ // 2005-01-01 00:00:00+03 // (1 row) 2. I don=E2=80=99t understand why the type cannot be parsed in = T_NullTest. It can be parsed when using the SQL below. String sql =3D "select * from tb_user where (createdat =3D ?)=E2=80=9D; PreparedStatement statement =3D conn.prepareStatement(sql); statement.setDate(1, new java.sql.Date(date.getTime())); 3. Thank you for your working around solution. It=E2=80=99s 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 wrote: >=20 > PG Bug reporting form writes: >> 1. Postgres JDBC driver will always use Oid UNSPECIFIED(0) for Date = type. >> See PgPreparedStatement.java, in setDate function. >=20 > You could ask the JDBC crew why they do that, although I suspect they > have reasons. >=20 >> 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. >=20 > There's nothing to coerce it *to*. >=20 > I'd suggest working around this with something like >=20 > select * from tb_user where (?::date is null or createdat > ?) >=20 > 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? >=20 > regards, tom lane >=20 >=20 --Apple-Mail=_9A69D85E-4043-45AF-9E3C-64DA9670AFF2 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8 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=3D# select '2005-01-01+0100'::timestamptz;
// = timestamptz
// ------------------------
// 2005-01-01 02:00:00+03
// (1 = row)

// = template1=3D# select '2005-01-01+0100'::date::timestamptz;
// = timestamptz
// ------------------------
// 2005-01-01 00:00:00+03
// (1 = row)
2. I don=E2=80=99t understand why the = type cannot be parsed in T_NullTest. It can be parsed when using the SQL = below.
String sql =3D =
"select * from tb_user where (createdat =3D ?)=E2=80=9D;
PreparedStatement statement =
=3D conn.prepareStatement(sql);
statement.setDate(1, =
new =
java.sql.Date(date.getTime()));
3. Thank you for your working = around solution. It=E2=80=99s 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


= --Apple-Mail=_9A69D85E-4043-45AF-9E3C-64DA9670AFF2--