[PATCH] Normalize numeric/bigint scientific notation after rounding

Started by 韩建桥9 days ago8 messageshackers
Jump to latest
#1韩建桥
hanjianqiao@gmail.com

Hi hackers,

While working with PG, I found that to_char returns value that is
right but the format is wrong:
SELECT to_char(999::numeric, '9.9EEEE');
Result is: 10.0e+02, and I think the expected output should be: 1.0e+03.
With help from ChatGPT, I figured that get_str_from_var_sci()
determines the exponent
before rounding the significand. If rounding increases the
significand's absolute value to
10, the original exponent is still used. The attached patch adds a
check for this,and adjusts
the significand and the exponent.

Regards,
hanjianqiao

Attachments:

t259214_1
v1-0001-Normalize-numeric-bigint-scientific-notation-a.patchapplication/octet-stream; name=v1-0001-Normalize-numeric-bigint-scientific-notation-a.patchDownload+83-1
#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: 韩建桥 (#1)
Re: [PATCH] Normalize numeric/bigint scientific notation after rounding

=?UTF-8?B?6Z+p5bu65qGl?= <hanjianqiao@gmail.com> writes:

While working with PG, I found that to_char returns value that is
right but the format is wrong:
SELECT to_char(999::numeric, '9.9EEEE');
Result is: 10.0e+02, and I think the expected output should be: 1.0e+03.
With help from ChatGPT, I figured that get_str_from_var_sci()
determines the exponent
before rounding the significand. If rounding increases the
significand's absolute value to
10, the original exponent is still used. The attached patch adds a
check for this,and adjusts
the significand and the exponent.

Good point. The patch feels a little brute-force, but I don't see
a simple way to do better, and it's probably not worth a huge
amount of trouble to make this edge case faster.

Is there any case in which we have the reverse problem, ie rounding
down causes the leading digit to become 0?

regards, tom lane

#3韩建桥
hanjianqiao@gmail.com
In reply to: Tom Lane (#2)
Re: [PATCH] Normalize numeric/bigint scientific notation after rounding

On Sat, 14 Nov 2026 at 02:28, Tom Lane <tgl@sss.pgh.pa.us> wrote:

=?UTF-8?B?6Z+p5bu65qGl?= <hanjianqiao@gmail.com> writes:

While working with PG, I found that to_char returns value that is
right but the format is wrong:
SELECT to_char(999::numeric, '9.9EEEE');
Result is: 10.0e+02, and I think the expected output should be: 1.0e+03.
With help from ChatGPT, I figured that get_str_from_var_sci()
determines the exponent
before rounding the significand. If rounding increases the
significand's absolute value to
10, the original exponent is still used. The attached patch adds a
check for this,and adjusts
the significand and the exponent.

Good point. The patch feels a little brute-force, but I don't see
a simple way to do better, and it's probably not worth a huge
amount of trouble to make this edge case faster.

Is there any case in which we have the reverse problem, ie rounding
down causes the leading digit to become 0?

regards, tom lane

Thanks for the review. The patch handles the tmp_var calculated by
following two function calls:

power_ten_int(exponent, &tmp_var);
// rscale is always >= 0
// for a non-zero var: var / 10^exponent is in [1, 10), with round=true the
// range is [1, 10] ie. 9.99 will be rounded to 10.0
div_var(var, &tmp_var, &tmp_var, rscale, true, true);

And the patch increases exponent only when tmp_var.digits[0] >= 10.
So for a non-zero input the tmp_var.digits[0] within range [1, 10] will
not be rounded to zero, and I cannot think any other type of reverse
problems will occur here.

Regards,
Jianqiao Han

#4Dean Rasheed
dean.a.rasheed@gmail.com
In reply to: 韩建桥 (#3)
Re: [PATCH] Normalize numeric/bigint scientific notation after rounding

On Mon, 16 Nov 2026 at 00:02, Jianqiao Han <hanjianqiao@gmail.com> wrote:

On Sat, 14 Nov 2026 at 02:28, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Good point. The patch feels a little brute-force, but I don't see
a simple way to do better, and it's probably not worth a huge
amount of trouble to make this edge case faster.

Is there any case in which we have the reverse problem, ie rounding
down causes the leading digit to become 0?

Thanks for the review. The patch handles the tmp_var calculated by
following two function calls:

power_ten_int(exponent, &tmp_var);
// rscale is always >= 0
// for a non-zero var: var / 10^exponent is in [1, 10), with round=true the
// range is [1, 10] ie. 9.99 will be rounded to 10.0
div_var(var, &tmp_var, &tmp_var, rscale, true, true);

And the patch increases exponent only when tmp_var.digits[0] >= 10.
So for a non-zero input the tmp_var.digits[0] within range [1, 10] will
not be rounded to zero, and I cannot think any other type of reverse
problems will occur here.

Agreed. If it doesn't round up, it truncates, which doesn't change the
leading digits.

The patch looks correct to me, though the same result can be achieved
without a second div_var() call, and I think it would be worth adding
Assert()s to confirm the current division result is as expected.

After the current call to div_var(), if the input is non-zero
(var->ndigits > 0), tmp_var should have a magnitude in the range [1,
10], with rscale digits after the decimal point, so one should be able
to Assert the following for tmp_var:

ndigits > 0
weight == 0
dscale == rscale
digits[0] >= 1
digits[0] <= 10

In addition, if digits[0] == 10, the magnitude of tmp_var should
exactly equal 10, and any trailing zeros should have been stripped
from the digit array, so one could further Assert that ndigits == 1 in
that case.

Having confirmed all that, to compensate for it having 1 significant
digit too many when digits[0] == 10, all you'd have to do is set
digits[0] to 1 and increase exponent by 1.

The argument for doing it that way isn't so much about performance,
but more about confirming/documenting the algorithm.

Regards,
Dean

#5Dean Rasheed
dean.a.rasheed@gmail.com
In reply to: Dean Rasheed (#4)
Re: [PATCH] Normalize numeric/bigint scientific notation after rounding

On Mon, 16 Nov 2026 at 10:00, Dean Rasheed <dean.a.rasheed@gmail.com> wrote:

After the current call to div_var(), if the input is non-zero
(var->ndigits > 0), tmp_var should have a magnitude in the range [1,
10], with rscale digits after the decimal point, so one should be able
to Assert the following for tmp_var:

ndigits > 0
weight == 0
dscale == rscale
digits[0] >= 1
digits[0] <= 10

Having said that, do we still care about supporting other NBASE
values? If NBASE == 10, the overflow case would have weight == 1 and
digits[0] == 1, which would need to be treated differently.

Regards,
Dean

#6Tom Lane
tgl@sss.pgh.pa.us
In reply to: Dean Rasheed (#5)
Re: [PATCH] Normalize numeric/bigint scientific notation after rounding

Dean Rasheed <dean.a.rasheed@gmail.com> writes:

Having said that, do we still care about supporting other NBASE
values? If NBASE == 10, the overflow case would have weight == 1 and
digits[0] == 1, which would need to be treated differently.

There are already places that assume NBASE==10000. Still, I feel
that this is overthinking the problem. Jianqiao's solution is
obviously correct, and if you think maybe it isn't, changing the
"if" into a "while" would take care of any strange roundoff effects
in the second division.

regards, tom lane

#7Dean Rasheed
dean.a.rasheed@gmail.com
In reply to: Tom Lane (#6)
Re: [PATCH] Normalize numeric/bigint scientific notation after rounding

On Mon, 16 Nov 2026 at 15:20, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Dean Rasheed <dean.a.rasheed@gmail.com> writes:

Having said that, do we still care about supporting other NBASE
values? If NBASE == 10, the overflow case would have weight == 1 and
digits[0] == 1, which would need to be treated differently.

There are already places that assume NBASE==10000. Still, I feel
that this is overthinking the problem. Jianqiao's solution is
obviously correct, and if you think maybe it isn't, changing the
"if" into a "while" would take care of any strange roundoff effects
in the second division.

OK, I guess it has the advantage of being nice and simple. Changing
the "if" to a "while" doesn't make sense.

Regards,
Dean

#8Tom Lane
tgl@sss.pgh.pa.us
In reply to: Dean Rasheed (#7)
Re: [PATCH] Normalize numeric/bigint scientific notation after rounding

Dean Rasheed <dean.a.rasheed@gmail.com> writes:

On Mon, 16 Nov 2026 at 15:20, Tom Lane <tgl@sss.pgh.pa.us> wrote:

There are already places that assume NBASE==10000. Still, I feel
that this is overthinking the problem. Jianqiao's solution is
obviously correct, and if you think maybe it isn't, changing the
"if" into a "while" would take care of any strange roundoff effects
in the second division.

OK, I guess it has the advantage of being nice and simple. Changing
the "if" to a "while" doesn't make sense.

OK, pushed as-submitted then. I worked on the documentation
a bit while at it, because it really didn't say what we expect
the output to look like.

regards, tom lane