Обсуждение: BUG #18976: -0.0 with float8 will be transformed to 0 in prepare statement but not in normal execution

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

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

发件人：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

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