Re: BUG #18976: -0.0 with float8 will be transformed to 0 inprepare statement but not in normal execution

From: Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
To: ZhangChi <798604270(at)qq(dot)com>, pgsql-bugs <pgsql-bugs(at)lists(dot)postgresql(dot)org>
Subject: Re: BUG #18976: -0.0 with float8 will be transformed to 0 inprepare statement but not in normal execution
Date: 2025-07-03 13:27:05
Message-ID: 0736526ae323d07241e2438be1d40ed26c45d833.camel@cybertec.at
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Andy Fan 2025-07-03 13:33:27 Re: BUG #16931: source code problem about commit_ts
Previous Message PG Bug reporting form 2025-07-03 13:23:12 BUG #18977: Unexpected result of function to_char