pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18977: Unexpected result of function to_char
4+ messages / 4 participants
[nested] [flat]
* BUG #18977: Unexpected result of function to_char
@ 2025-07-03 13:23 PG Bug reporting form <noreply@postgresql.org>
2025-07-03 14:40 ` Re: BUG #18977: Unexpected result of function to_char Laurenz Albe <laurenz.albe@cybertec.at>
2025-07-03 15:06 ` Re: BUG #18977: Unexpected result of function to_char Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 4+ messages in thread
From: PG Bug reporting form @ 2025-07-03 13:23 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: 798604270@qq.com
The following bug has been logged on the website:
Bug reference: 18977
Logged by: Chi Zhang
Email address: 798604270@qq.com
PostgreSQL version: 18beta1
Operating system: ubuntu 24.04 with docker
Description:
hi,
the following two queries are equivalent but return different results:
```
SELECT ((to_char(-1E30, '0.9930824')));
to_char
------------
-#.##3#824
(1 row)
PREPARE prepare_query (float8) AS SELECT ((to_char($1, '0.9930824')));
EXECUTE prepare_query(-1E30::float8);
to_char
---------
-#.
```
furthermore, it seems the second argument of to_chat is formatted, but
according to the document in
https://www.postgresql.org/docs/current/functions-formatting.html, it should
be the first argument to be formatted
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #18977: Unexpected result of function to_char
2025-07-03 13:23 BUG #18977: Unexpected result of function to_char PG Bug reporting form <noreply@postgresql.org>
@ 2025-07-03 14:40 ` Laurenz Albe <laurenz.albe@cybertec.at>
1 sibling, 0 replies; 4+ messages in thread
From: Laurenz Albe @ 2025-07-03 14:40 UTC (permalink / raw)
To: 798604270@qq.com; pgsql-bugs@lists.postgresql.org
On Thu, 2025-07-03 at 13:23 +0000, PG Bug reporting form wrote:
> the following two queries are equivalent but return different results:
> ```
> SELECT ((to_char(-1E30, '0.9930824')));
> to_char
> ------------
> -#.##3#824
> (1 row)
> PREPARE prepare_query (float8) AS SELECT ((to_char($1, '0.9930824')));
> EXECUTE prepare_query(-1E30::float8);
> to_char
> ---------
> -#.
> ```
> furthermore, it seems the second argument of to_chat is formatted, but
> according to the document in
> https://www.postgresql.org/docs/current/functions-formatting.html, it should
> be the first argument to be formatted
This is the same as bug report #18976.
For user guidance, please write to the pgsql-general mailing list.
This list is for bug reports.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #18977: Unexpected result of function to_char
2025-07-03 13:23 BUG #18977: Unexpected result of function to_char PG Bug reporting form <noreply@postgresql.org>
@ 2025-07-03 15:06 ` Tom Lane <tgl@sss.pgh.pa.us>
2025-07-03 15:49 ` Re: BUG #18977: Unexpected result of function to_char =?utf-8?B?WmhhbmdDaGk=?= <798604270@qq.com>
1 sibling, 1 reply; 4+ messages in thread
From: Tom Lane @ 2025-07-03 15:06 UTC (permalink / raw)
To: 798604270@qq.com; +Cc: pgsql-bugs@lists.postgresql.org
PG Bug reporting form <noreply@postgresql.org> writes:
> the following two queries are equivalent but return different results:
> ```
> SELECT ((to_char(-1E30, '0.9930824')));
> to_char
> ------------
> -#.##3#824
> (1 row)
> PREPARE prepare_query (float8) AS SELECT ((to_char($1, '0.9930824')));
> EXECUTE prepare_query(-1E30::float8);
> to_char
> ---------
> -#.
> ```
They are not equivalent: the float8 and numeric variants of to_char
behave somewhat differently, because of the need to round off float8
values to no more than about 15 decimal digits. (If we failed to do
so, we'd print useless noise digits.) In this case float8_to_char
decides that it can't print any digits beyond the decimal point.
> furthermore, it seems the second argument of to_chat is formatted, but
> according to the document in
> https://www.postgresql.org/docs/current/functions-formatting.html, it should
> be the first argument to be formatted
It is the first argument that is formatted. You are passing a garbage
value of the format string, and unsurprisingly getting a garbage
result. (Only the 0's, 9's, and decimal point act as format characters.)
You'd get better results with a format that is wide enough to hold
the value, say
regression=# SELECT to_char(-1E30::numeric, '0.9999999eeee');
to_char
----------------
-1.0000000e+30
(1 row)
regression=# SELECT to_char(-1E30::float8, '0.9999999eeee');
to_char
----------------
-1.0000000e+30
(1 row)
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #18977: Unexpected result of function to_char
2025-07-03 13:23 BUG #18977: Unexpected result of function to_char PG Bug reporting form <noreply@postgresql.org>
2025-07-03 15:06 ` Re: BUG #18977: Unexpected result of function to_char Tom Lane <tgl@sss.pgh.pa.us>
@ 2025-07-03 15:49 ` =?utf-8?B?WmhhbmdDaGk=?= <798604270@qq.com>
0 siblings, 0 replies; 4+ messages in thread
From: ZhangChi @ 2025-07-03 15:49 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs <pgsql-bugs@lists.postgresql.org>
Hi tom 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>
发件时间：2025年7月3日 23:06
收件人：798604270 <798604270@qq.com>
抄送：pgsql-bugs <pgsql-bugs@lists.postgresql.org>
主题：Re: BUG #18977: Unexpected result of function to_char
PG Bug reporting form <noreply@postgresql.org> writes:
> the following two queries are equivalent but return different results:
> ```
> SELECT ((to_char(-1E30, '0.9930824')));
> to_char
> ------------
> -#.##3#824
> (1 row)
> PREPARE prepare_query (float8) AS SELECT ((to_char($1, '0.9930824')));
> EXECUTE prepare_query(-1E30::float8);
> to_char
> ---------
> -#.
> ```
They are not equivalent: the float8 and numeric variants of to_char
behave somewhat differently, because of the need to round off float8
values to no more than about 15 decimal digits. (If we failed to do
so, we'd print useless noise digits.) In this case float8_to_char
decides that it can't print any digits beyond the decimal point.
> furthermore, it seems the second argument of to_chat is formatted, but
> according to the document in
> https://www.postgresql.org/docs/current/functions-formatting.html, it should
> be the first argument to be formatted
It is the first argument that is formatted. You are passing a garbage
value of the format string, and unsurprisingly getting a garbage
result. (Only the 0's, 9's, and decimal point act as format characters.)
You'd get better results with a format that is wide enough to hold
the value, say
regression=# SELECT to_char(-1E30::numeric, '0.9999999eeee');
to_char
----------------
-1.0000000e+30
(1 row)
regression=# SELECT to_char(-1E30::float8, '0.9999999eeee');
to_char
----------------
-1.0000000e+30
(1 row)
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2025-07-03 15:49 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-07-03 13:23 BUG #18977: Unexpected result of function to_char PG Bug reporting form <noreply@postgresql.org>
2025-07-03 14:40 ` Laurenz Albe <laurenz.albe@cybertec.at>
2025-07-03 15:06 ` Tom Lane <tgl@sss.pgh.pa.us>
2025-07-03 15:49 ` =?utf-8?B?WmhhbmdDaGk=?= <798604270@qq.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox