Horton
Menu

Thread

  1. 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-03T03:03:05Z

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

  2. 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-03T12:23:42Z

    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

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

    ZhangChi <798604270@qq.com> — 2025-07-03T12:30:40Z

    hi,


Thank you for your reply!


I’m wondering—since the parameter has already been specified as float8&nbsp;in the PREPARE&nbsp;statement, why is it still necessary to convert it to float8&nbsp;again during EXECUTE?


         原始邮件
         
       
发件人：Laurenz Albe <laurenz.albe@cybertec.at&gt;
发件时间：2025年7月3日 20:23
收件人：798604270 <798604270@qq.com&gt;, pgsql-bugs <pgsql-bugs@lists.postgresql.org&gt;
主题：Re: BUG #18976: -0.0 with float8 will be transformed to 0 inprepare statement but not in normal execution



       On&nbsp;Thu,&nbsp;2025-07-03&nbsp;at&nbsp;03:03&nbsp;+0000,&nbsp;PG&nbsp;Bug&nbsp;reporting&nbsp;form&nbsp;wrote:
&gt;&nbsp;The&nbsp;following&nbsp;bug&nbsp;has&nbsp;been&nbsp;logged&nbsp;on&nbsp;the&nbsp;website:
&gt;&nbsp;
&gt;&nbsp;Bug&nbsp;reference:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;18976
&gt;&nbsp;Logged&nbsp;by:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Chi&nbsp;Zhang
&gt;&nbsp;Email&nbsp;address:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;798604270@qq.com
&gt;&nbsp;PostgreSQL&nbsp;version:&nbsp;18beta1
&gt;&nbsp;Operating&nbsp;system:&nbsp;&nbsp;&nbsp;Ubuntu&nbsp;24.04&nbsp;and&nbsp;docker
&gt;&nbsp;Description:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&gt;&nbsp;
&gt;&nbsp;The&nbsp;value&nbsp;-0.0&nbsp;with&nbsp;float8&nbsp;is&nbsp;transformed&nbsp;to&nbsp;0&nbsp;in&nbsp;prepared&nbsp;statements&nbsp;but
&gt;&nbsp;remains&nbsp;-0&nbsp;in&nbsp;normal&nbsp;execution.&nbsp;Although&nbsp;0&nbsp;and&nbsp;-0&nbsp;are&nbsp;numerically&nbsp;equal,
&gt;&nbsp;this&nbsp;discrepancy&nbsp;can&nbsp;lead&nbsp;to&nbsp;subtle&nbsp;bugs&nbsp;in&nbsp;certain&nbsp;cases—for&nbsp;example,&nbsp;when
&gt;&nbsp;the&nbsp;value&nbsp;is&nbsp;cast&nbsp;to&nbsp;a&nbsp;VARCHAR,&nbsp;as&nbsp;illustrated&nbsp;below.
&gt;&nbsp;
&gt; PREPARE&nbsp;prepare_query&nbsp;(float8)&nbsp;AS&nbsp;SELECT&nbsp;CAST($1&nbsp;AS&nbsp;VARCHAR)&nbsp;=
&gt;&nbsp;CAST(-0.0::float8&nbsp;AS&nbsp;VARCHAR);
&gt;&nbsp;EXECUTE&nbsp;prepare_query(-0.0);&nbsp;--&nbsp;f

That's&nbsp;not&nbsp;a&nbsp;bug,&nbsp;but&nbsp;a&nbsp;pilot&nbsp;error.&nbsp;&nbsp;If&nbsp;you&nbsp;feed&nbsp;a&nbsp;"float8",&nbsp;the&nbsp;result&nbsp;ist&nbsp;TRUE:

EXECUTE&nbsp;prepare_query(-0.0::float8);

&nbsp;?column?&nbsp;
══════════
&nbsp;t
(1&nbsp;row)

Yours,
Laurenz&nbsp;Albe

  4. 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-03T13:27:05Z

    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

  5. Re: BUG #18976: -0.0 with float8 will be transformed to 0 inpreparestatement but not in normal execution

    ZhangChi <798604270@qq.com> — 2025-07-03T13:45:20Z

    I got it, thank you very much


         原始邮件
         
       
发件人：Laurenz Albe <laurenz.albe@cybertec.at&gt;
发件时间：2025年7月3日 21:27
收件人：ZhangChi <798604270@qq.com&gt;, pgsql-bugs <pgsql-bugs@lists.postgresql.org&gt;
主题：Re: BUG #18976: -0.0 with float8 will be transformed to 0 inpreparestatement but not in normal execution



       On&nbsp;Thu,&nbsp;2025-07-03&nbsp;at&nbsp;20:30&nbsp;+0800,&nbsp;ZhangChi&nbsp;wrote:
&gt;&nbsp;&gt;&nbsp;&gt;&nbsp;&nbsp;The&nbsp;value&nbsp;-0.0&nbsp;with&nbsp;float8&nbsp;is&nbsp;transformed&nbsp;to&nbsp;0&nbsp;in&nbsp;prepared&nbsp;statements&nbsp;but
&gt;&nbsp;&gt;&nbsp;&gt;&nbsp;&nbsp;remains&nbsp;-0&nbsp;in&nbsp;normal&nbsp;execution.&nbsp;Although&nbsp;0&nbsp;and&nbsp;-0&nbsp;are&nbsp;numerically&nbsp;equal,
&gt;&nbsp;&gt;&nbsp;&gt;&nbsp;&nbsp;this&nbsp;discrepancy&nbsp;can&nbsp;lead&nbsp;to&nbsp;subtle&nbsp;bugs&nbsp;in&nbsp;certain&nbsp;cases—for&nbsp;example,&nbsp;when
&gt;&nbsp;&gt;&nbsp;&gt;&nbsp;&nbsp;the&nbsp;value&nbsp;is&nbsp;cast&nbsp;to&nbsp;a&nbsp;VARCHAR,&nbsp;as&nbsp;illustrated&nbsp;below.
&gt;&nbsp;&gt;&nbsp;&gt;&nbsp;
&gt;&nbsp;&gt;&nbsp;&gt;&nbsp;PREPARE&nbsp;prepare_query&nbsp;(float8)&nbsp;AS&nbsp;SELECT&nbsp;CAST($1&nbsp;AS&nbsp;VARCHAR)&nbsp;=
&gt;&nbsp;&gt;&nbsp;&gt;&nbsp;&nbsp;CAST(-0.0::float8&nbsp;AS&nbsp;VARCHAR);
&gt;&nbsp;&gt;&nbsp;&gt;&nbsp;&nbsp;EXECUTE&nbsp;prepare_query(-0.0);&nbsp;--&nbsp;f
&gt;&nbsp;&gt;
&gt;&nbsp;&gt;&nbsp;That's&nbsp;not&nbsp;a&nbsp;bug,&nbsp;but&nbsp;a&nbsp;pilot&nbsp;error.&nbsp;&nbsp;If&nbsp;you&nbsp;feed&nbsp;a&nbsp;"float8",&nbsp;the&nbsp;result&nbsp;ist&nbsp;TRUE:
&gt;&nbsp;&gt;
&gt;&nbsp;&gt;&nbsp;EXECUTE&nbsp;prepare_query(-0.0::float8);
&gt;&nbsp;&gt;
&gt;&nbsp;&gt;&nbsp;&nbsp;?column?&nbsp;
&gt;&nbsp;&gt;&nbsp;══════════
&gt;&nbsp;&gt;&nbsp;&nbsp;t
&gt;&nbsp;&gt;&nbsp;(1&nbsp;row)
&gt;
&gt;&nbsp;I’m&nbsp;wondering—since&nbsp;the&nbsp;parameter&nbsp;has&nbsp;already&nbsp;been&nbsp;specified&nbsp;as&nbsp;float8&nbsp;in&nbsp;the
&gt;&nbsp;PREPARE&nbsp;statement,&nbsp;why&nbsp;is&nbsp;it&nbsp;still&nbsp;necessary&nbsp;to&nbsp;convert&nbsp;it&nbsp;to&nbsp;float8&nbsp;again
&gt;&nbsp;during&nbsp;EXECUTE?

I&nbsp;didn't&nbsp;debug&nbsp;through&nbsp;the&nbsp;code,&nbsp;but&nbsp;a&nbsp;numeric&nbsp;literal&nbsp;is&nbsp;considered&nbsp;to&nbsp;be
of&nbsp;type&nbsp;"numeric"&nbsp;in&nbsp;PostgreSQL:

&nbsp;&nbsp;SELECT&nbsp;pg_typeof(-0.0);

&nbsp;&nbsp;&nbsp;pg_typeof&nbsp;
&nbsp;&nbsp;═══════════
&nbsp;&nbsp;&nbsp;numeric
&nbsp;&nbsp;(1&nbsp;row)

And&nbsp;"numeric"&nbsp;doesn't&nbsp;know&nbsp;negative&nbsp;zeros.&nbsp;&nbsp;So&nbsp;I&nbsp;guess&nbsp;what&nbsp;happens&nbsp;is&nbsp;about
the&nbsp;following:

&nbsp;&nbsp;SELECT&nbsp;'-0.0'::numeric::float8;

&nbsp;&nbsp;&nbsp;float8&nbsp;
&nbsp;&nbsp;════════
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0
&nbsp;&nbsp;(1&nbsp;row)

The&nbsp;canonical&nbsp;way&nbsp;to&nbsp;write&nbsp;a&nbsp;literal&nbsp;(constant)&nbsp;of&nbsp;a&nbsp;specific&nbsp;data&nbsp;type&nbsp;is

&nbsp;&nbsp;DOUBLE&nbsp;PRECISION&nbsp;'-0.0'

Yours,
Laurenz&nbsp;Albe