[PATCH] Normalize numeric/bigint scientific notation after rounding
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
=?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
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
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
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
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
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
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