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
