pg.ddx.io pgsql-bugs@postgresql.org mailing list archive help / color / mirror / Atom feed From: ZhangChi <798604270@qq.com>
To: Laurenz Albe <laurenz.albe@cybertec.at>
To: pgsql-bugs <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #18976: -0.0 with float8 will be transformed to 0 inpreparestatement but not in normal execution
Date: Thu, 3 Jul 2025 21:45:20 +0800
Message-ID: <tencent_CB6BB2791E324F138638B7D0FDA5D25DAC07@qq.com> (raw )
In-Reply-To: <0736526ae323d07241e2438be1d40ed26c45d833.camel@cybertec.at >
References: <18976-c1bc92542b90e5e6@postgresql.org >
<c8c5090a0d720db8a5c4c382cce59afde3dc65d2.camel@cybertec.at >
<tencent_962FF1EEB65D67CD8B93099B1CC499851E0A@qq.com >
<0736526ae323d07241e2438be1d40ed26c45d833.camel@cybertec.at >
I got it, thank you very much
原始邮件
发件人：Laurenz Albe <laurenz.albe@cybertec.at>
发件时间：2025年7月3日 21:27
收件人：ZhangChi <798604270@qq.com>, pgsql-bugs <pgsql-bugs@lists.postgresql.org>
主题：Re: BUG #18976: -0.0 with float8 will be transformed to 0 inpreparestatement but not in normal execution
On Thu, 2025-07-03 at 20:30 +0800, ZhangChi wrote:
> > > The value -0.0 with float8 is transformed to 0 in prepared statements but
> > > remains -0 in normal execution. Although 0 and -0 are numerically equal,
> > > this discrepancy can lead to subtle bugs in certain cases—for example, when
> > > the value is cast to a VARCHAR, as illustrated below.
> > >
> > > PREPARE prepare_query (float8) AS SELECT CAST($1 AS VARCHAR) =
> > > CAST(-0.0::float8 AS VARCHAR);
> > > EXECUTE prepare_query(-0.0); -- f
> >
> > That's not a bug, but a pilot error. If you feed a "float8", the result ist TRUE:
> >
> > EXECUTE prepare_query(-0.0::float8);
> >
> > ?column?
> > ══════════
> > t
> > (1 row)
>
> I’m wondering—since the parameter has already been specified as float8 in the
> PREPARE statement, why is it still necessary to convert it to float8 again
> during EXECUTE?
I didn't debug through the code, but a numeric literal is considered to be
of type "numeric" in PostgreSQL:
SELECT pg_typeof(-0.0);
pg_typeof
═══════════
numeric
(1 row)
And "numeric" doesn't know negative zeros. So I guess what happens is about
the following:
SELECT '-0.0'::numeric::float8;
float8
════════
0
(1 row)
The canonical way to write a literal (constant) of a specific data type is
DOUBLE PRECISION '-0.0'
Yours,
Laurenz Albe
view thread (5+ messages)
Message-ID: <tencent_CB6BB2791E324F138638B7D0FDA5D25DAC07@qq.com>
Permalink: ../tencent_CB6BB2791E324F138638B7D0FDA5D25DAC07@qq.com/
Also on: postgresql.org/message-id/tencent_CB6BB2791E324F138638B7D0FDA5D25DAC07@qq.com
copy link · copy postgr.es
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: 798604270@qq.com, laurenz.albe@cybertec.at, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18976: -0.0 with float8 will be transformed to 0 inpreparestatement but not in normal execution
In-Reply-To: <tencent_CB6BB2791E324F138638B7D0FDA5D25DAC07@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