BUG #18976: -0.0 with float8 will be transformed to 0 in prepare 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

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

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

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