Horton

Thread

  1. Re: BUG #18977: Unexpected result of function to_char

    ZhangChi <798604270@qq.com> — 2025-07-03T15:49:27Z

    Hi tom&nbsp;lane,


Thank you very much for your explanation.


I am trying to detecting issues in prepare statement and I can understant this now.


I sincerely apologize for reporting a false positive.


         原始邮件
         
       
发件人：Tom Lane <tgl@sss.pgh.pa.us&gt;
发件时间：2025年7月3日 23:06
收件人：798604270 <798604270@qq.com&gt;
抄送：pgsql-bugs <pgsql-bugs@lists.postgresql.org&gt;
主题：Re: BUG #18977: Unexpected result of function to_char



       PG&nbsp;Bug&nbsp;reporting&nbsp;form&nbsp;<noreply@postgresql.org&gt;&nbsp;writes:
&gt;&nbsp;the&nbsp;following&nbsp;two&nbsp;queries&nbsp;are&nbsp;equivalent&nbsp;but&nbsp;return&nbsp;different&nbsp;results:
&gt;&nbsp;```
&gt;&nbsp;SELECT&nbsp;((to_char(-1E30,&nbsp;'0.9930824')));
&gt;&nbsp;&nbsp;&nbsp;to_char
&gt;&nbsp;------------
&gt;&nbsp;&nbsp;-#.##3#824
&gt;&nbsp;(1&nbsp;row)
&gt;&nbsp;PREPARE&nbsp;prepare_query&nbsp;(float8)&nbsp;AS&nbsp;SELECT&nbsp;((to_char($1,&nbsp;'0.9930824')));
&gt;&nbsp;EXECUTE&nbsp;prepare_query(-1E30::float8);
&gt;&nbsp;&nbsp;to_char
&gt;&nbsp;---------
&gt;&nbsp;&nbsp;-#.
&gt;&nbsp;```

They&nbsp;are&nbsp;not&nbsp;equivalent:&nbsp;the&nbsp;float8&nbsp;and&nbsp;numeric&nbsp;variants&nbsp;of&nbsp;to_char
behave&nbsp;somewhat&nbsp;differently,&nbsp;because&nbsp;of&nbsp;the&nbsp;need&nbsp;to&nbsp;round&nbsp;off&nbsp;float8
values&nbsp;to&nbsp;no&nbsp;more&nbsp;than&nbsp;about&nbsp;15&nbsp;decimal&nbsp;digits.&nbsp;&nbsp;(If&nbsp;we&nbsp;failed&nbsp;to&nbsp;do
so,&nbsp;we'd&nbsp;print&nbsp;useless&nbsp;noise&nbsp;digits.)&nbsp;&nbsp;In&nbsp;this&nbsp;case&nbsp;float8_to_char
decides&nbsp;that&nbsp;it&nbsp;can't&nbsp;print&nbsp;any&nbsp;digits&nbsp;beyond&nbsp;the&nbsp;decimal&nbsp;point.

&gt;&nbsp;furthermore,&nbsp;it&nbsp;seems&nbsp;the&nbsp;second&nbsp;argument&nbsp;of&nbsp;to_chat&nbsp;is&nbsp;formatted,&nbsp;but
&gt;&nbsp;according&nbsp;to&nbsp;the&nbsp;document&nbsp;in
&gt;&nbsp;https://www.postgresql.org/docs/current/functions-formatting.html,&nbsp;it&nbsp;should
&gt;&nbsp;be&nbsp;the&nbsp;first&nbsp;argument&nbsp;to&nbsp;be&nbsp;formatted

It&nbsp;is&nbsp;the&nbsp;first&nbsp;argument&nbsp;that&nbsp;is&nbsp;formatted.&nbsp;&nbsp;You&nbsp;are&nbsp;passing&nbsp;a&nbsp;garbage
value&nbsp;of&nbsp;the&nbsp;format&nbsp;string,&nbsp;and&nbsp;unsurprisingly&nbsp;getting&nbsp;a&nbsp;garbage
result.&nbsp;&nbsp;(Only&nbsp;the&nbsp;0's,&nbsp;9's,&nbsp;and&nbsp;decimal&nbsp;point&nbsp;act&nbsp;as&nbsp;format&nbsp;characters.)
You'd&nbsp;get&nbsp;better&nbsp;results&nbsp;with&nbsp;a&nbsp;format&nbsp;that&nbsp;is&nbsp;wide&nbsp;enough&nbsp;to&nbsp;hold
the&nbsp;value,&nbsp;say

regression=#&nbsp;SELECT&nbsp;to_char(-1E30::numeric,&nbsp;'0.9999999eeee');
&nbsp;&nbsp;&nbsp;&nbsp;to_char&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
----------------
&nbsp;-1.0000000e+30
(1&nbsp;row)

regression=#&nbsp;SELECT&nbsp;to_char(-1E30::float8,&nbsp;'0.9999999eeee');
&nbsp;&nbsp;&nbsp;&nbsp;to_char&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
----------------
&nbsp;-1.0000000e+30
(1&nbsp;row)

			regards,&nbsp;tom&nbsp;lane