Horton

Thread

  1. 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