pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18976: -0.0 with float8 will be transformed to 0 in prepare statement but not in normal execution
5+ messages / 3 participants
[nested] [flat]
* BUG #18976: -0.0 with float8 will be transformed to 0 in prepare statement but not in normal execution
@ 2025-07-03 03:03 PG Bug reporting form <noreply@postgresql.org>
2025-07-03 12:23 ` Re: BUG #18976: -0.0 with float8 will be transformed to 0 in prepare statement but not in normal execution Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 5+ messages in thread
From: PG Bug reporting form @ 2025-07-03 03:03 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: 798604270@qq.com
The following bug has been logged on the website:
Bug reference: 18976
Logged by: Chi Zhang
Email address: 798604270@qq.com
PostgreSQL version: 18beta1
Operating system: Ubuntu 24.04 and docker
Description:
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
```
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #18976: -0.0 with float8 will be transformed to 0 in prepare statement but not in normal execution
2025-07-03 03:03 BUG #18976: -0.0 with float8 will be transformed to 0 in prepare statement but not in normal execution PG Bug reporting form <noreply@postgresql.org>
@ 2025-07-03 12:23 ` Laurenz Albe <laurenz.albe@cybertec.at>
2025-07-03 12:30 ` Re: BUG #18976: -0.0 with float8 will be transformed to 0 inprepare statement but not in normal execution =?utf-8?B?WmhhbmdDaGk=?= <798604270@qq.com>
0 siblings, 1 reply; 5+ messages in thread
From: Laurenz Albe @ 2025-07-03 12:23 UTC (permalink / raw)
To: 798604270@qq.com; pgsql-bugs@lists.postgresql.org
On Thu, 2025-07-03 at 03:03 +0000, PG Bug reporting form wrote:
> The following bug has been logged on the website:
>
> Bug reference: 18976
> Logged by: Chi Zhang
> Email address: 798604270@qq.com
> PostgreSQL version: 18beta1
> Operating system: Ubuntu 24.04 and docker
> Description:
>
> 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)
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #18976: -0.0 with float8 will be transformed to 0 inprepare statement but not in normal execution
2025-07-03 03:03 BUG #18976: -0.0 with float8 will be transformed to 0 in prepare statement but not in normal execution PG Bug reporting form <noreply@postgresql.org>
2025-07-03 12:23 ` Re: BUG #18976: -0.0 with float8 will be transformed to 0 in prepare statement but not in normal execution Laurenz Albe <laurenz.albe@cybertec.at>
@ 2025-07-03 12:30 ` =?utf-8?B?WmhhbmdDaGk=?= <798604270@qq.com>
2025-07-03 13:27 ` Re: BUG #18976: -0.0 with float8 will be transformed to 0 inprepare statement but not in normal execution Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 5+ messages in thread
From: ZhangChi @ 2025-07-03 12:30 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-bugs <pgsql-bugs@lists.postgresql.org>
hi,
Thank you for your reply!
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?
原始邮件
发件人：Laurenz Albe <laurenz.albe@cybertec.at>
发件时间：2025年7月3日 20:23
收件人：798604270 <798604270@qq.com>, pgsql-bugs <pgsql-bugs@lists.postgresql.org>
主题：Re: BUG #18976: -0.0 with float8 will be transformed to 0 inprepare statement but not in normal execution
On Thu, 2025-07-03 at 03:03 +0000, PG Bug reporting form wrote:
> The following bug has been logged on the website:
>
> Bug reference: 18976
> Logged by: Chi Zhang
> Email address: 798604270@qq.com
> PostgreSQL version: 18beta1
> Operating system: Ubuntu 24.04 and docker
> Description:
>
> 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)
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #18976: -0.0 with float8 will be transformed to 0 inprepare statement but not in normal execution
2025-07-03 03:03 BUG #18976: -0.0 with float8 will be transformed to 0 in prepare statement but not in normal execution PG Bug reporting form <noreply@postgresql.org>
2025-07-03 12:23 ` Re: BUG #18976: -0.0 with float8 will be transformed to 0 in prepare statement but not in normal execution Laurenz Albe <laurenz.albe@cybertec.at>
2025-07-03 12:30 ` Re: BUG #18976: -0.0 with float8 will be transformed to 0 inprepare statement but not in normal execution =?utf-8?B?WmhhbmdDaGk=?= <798604270@qq.com>
@ 2025-07-03 13:27 ` Laurenz Albe <laurenz.albe@cybertec.at>
2025-07-03 13:45 ` Re: BUG #18976: -0.0 with float8 will be transformed to 0 inpreparestatement but not in normal execution =?utf-8?B?WmhhbmdDaGk=?= <798604270@qq.com>
0 siblings, 1 reply; 5+ messages in thread
From: Laurenz Albe @ 2025-07-03 13:27 UTC (permalink / raw)
To: ZhangChi <798604270@qq.com>; pgsql-bugs <pgsql-bugs@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #18976: -0.0 with float8 will be transformed to 0 inpreparestatement but not in normal execution
2025-07-03 03:03 BUG #18976: -0.0 with float8 will be transformed to 0 in prepare statement but not in normal execution PG Bug reporting form <noreply@postgresql.org>
2025-07-03 12:23 ` Re: BUG #18976: -0.0 with float8 will be transformed to 0 in prepare statement but not in normal execution Laurenz Albe <laurenz.albe@cybertec.at>
2025-07-03 12:30 ` Re: BUG #18976: -0.0 with float8 will be transformed to 0 inprepare statement but not in normal execution =?utf-8?B?WmhhbmdDaGk=?= <798604270@qq.com>
2025-07-03 13:27 ` Re: BUG #18976: -0.0 with float8 will be transformed to 0 inprepare statement but not in normal execution Laurenz Albe <laurenz.albe@cybertec.at>
@ 2025-07-03 13:45 ` =?utf-8?B?WmhhbmdDaGk=?= <798604270@qq.com>
0 siblings, 0 replies; 5+ messages in thread
From: ZhangChi @ 2025-07-03 13:45 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-bugs <pgsql-bugs@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2025-07-03 13:45 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-07-03 03:03 BUG #18976: -0.0 with float8 will be transformed to 0 in prepare statement but not in normal execution PG Bug reporting form <noreply@postgresql.org>
2025-07-03 12:23 ` Laurenz Albe <laurenz.albe@cybertec.at>
2025-07-03 12:30 ` Re: BUG #18976: -0.0 with float8 will be transformed to 0 inprepare statement but not in normal execution =?utf-8?B?WmhhbmdDaGk=?= <798604270@qq.com>
2025-07-03 13:27 ` Re: BUG #18976: -0.0 with float8 will be transformed to 0 inprepare statement but not in normal execution Laurenz Albe <laurenz.albe@cybertec.at>
2025-07-03 13:45 ` Re: BUG #18976: -0.0 with float8 will be transformed to 0 inpreparestatement but not in normal execution =?utf-8?B?WmhhbmdDaGk=?= <798604270@qq.com>
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