Fix numeric log() with an infinite value and 0<base<=1
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
This thread has been committed, so CI has stopped here. Anything below is the last result it produced.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t254195psql -h localhost -U postgresBuilt from patchset v3 (message #3), October 15, 2026 at 02:28 AM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t254195_3 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t254195_3 && git checkout t254195_3Patchset v3 (message #3) is on t254195_3
Hi,
The SQL function log() returns incorrect results in these cases:
```
evantest=# SELECT log(0.5::numeric, 'Infinity'::numeric);
log
----------
Infinity
(1 row)
evantest=# SELECT log(1::numeric, 'Infinity'::numeric);
log
----------
Infinity
(1 row)
```
log(base, value) = ln(value) / ln(base)
ln(0.5) is negative, so the first should return negative infinity; ln(1) is zero, so the second should fail with division by zero.
Verifying in Python:
```
print(math.log(math.inf, 0.5))
-inf
print(math.log(math.inf, 1));
Traceback (most recent call last):
File "<python-input-3>", line 1, in <module>
print(math.log(math.inf, 1));
~~~~~~~~^^^^^^^^^^^^^
ZeroDivisionError: division by zero
```
Also, verifying with PG itself using division:
```
evantest=# select ln('Infinity'::numeric)/ln(0.5::numeric);
?column?
-----------
-Infinity
(1 row)
evantest=# select ln('Infinity'::numeric)/ln(1::numeric);
ERROR: division by zero
```
This issue was introduced in v14.
Please see the attached patch for the fix.
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
On Wed, 14 Oct 2026 at 13:17, Chao Li <li.evan.chao@gmail.com> wrote:
Hi,
The SQL function log() returns incorrect results in these cases:
```
evantest=# SELECT log(0.5::numeric, 'Infinity'::numeric);
log
----------
Infinity
(1 row)evantest=# SELECT log(1::numeric, 'Infinity'::numeric);
log
----------
Infinity
(1 row)
```log(base, value) = ln(value) / ln(base)
ln(0.5) is negative, so the first should return negative infinity; ln(1) is zero, so the second should fail with division by zero.
Verifying in Python:
```print(math.log(math.inf, 0.5))
-inf
print(math.log(math.inf, 1));
Traceback (most recent call last):
File "<python-input-3>", line 1, in <module>
print(math.log(math.inf, 1));
~~~~~~~~^^^^^^^^^^^^^
ZeroDivisionError: division by zero
```
I don't think that we should match Python on this, because
log(non-unit-val, 1) should return a descriptive error here, and the
"division by zero" exposes implementation details that are not
relevant to the user, and indeed are quite confusing given the lack of
division operation in the user's query input. Note also that we don't
need a division by zero to determine that there is no power of 1 that
doesn't equal 1, so we shouldn't need to rely on that division.
So, I'd suggest something like
+ if (cmp_var(, const_one) == 0)
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_ARGUMENT_FOR_LOG),
+ errmsg("cannot take logarithm using base 1"));when the base is 1; which I think is sufficiently comparable to the
checks and errors we have for non-positive value inputs.
Kind regards,
Matthias van de Meent
Databricks (https://www.databricks.com)
On Oct 14, 2026, at 23:07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Matthias van de Meent <boekewurm+postgres@gmail.com> writes:
On Wed, 14 Oct 2026 at 13:17, Chao Li <li.evan.chao@gmail.com> wrote:
The SQL function log() returns incorrect results in these cases:
evantest=# SELECT log(0.5::numeric, 'Infinity'::numeric);
log
----------
Infinity
(1 row)
evantest=# SELECT log(1::numeric, 'Infinity'::numeric);
log
----------
Infinity
(1 row)I don't think that we should match Python on this,
Indeed; Python is not our reference implementation. But the SQL spec
does say that log(1, anything) is an error.
I didn’t intend to follow Python’s error message. I was just verifying that this case should raise an error.
So, I'd suggest something like
+ if (cmp_var(, const_one) == 0) + ereport(ERROR, + errcode(ERRCODE_INVALID_ARGUMENT_FOR_LOG), + errmsg("cannot take logarithm using base 1"));Agreed, but if we want that, we must do it for a finite second input
too. Right now you do getregression=# select log(1, 33);
ERROR: division by zeroChao proposes matching that, which isn't insane, but I like a
specialized error message better.
That’s why I chose that error message, because the existing test has this:
```
select log(1.0, 12.34);
ERROR: division by zero
```
After thinking it over, I think we can add a check for base 1 in log_var(), and numeric_log() can just defer base 1 handling to log_var(). Please see v2, which implements this approach.
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
Import Notes
Reply to msg id not found: 1174673.1791990459@sss.pgh.pa.us
On Thu, 15 Oct 2026 at 04:16, Chao Li <li.evan.chao@gmail.com> wrote:
On Oct 14, 2026, at 23:07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Matthias van de Meent <boekewurm+postgres@gmail.com> writes:
So, I'd suggest something like
+ if (cmp_var(, const_one) == 0) + ereport(ERROR, + errcode(ERRCODE_INVALID_ARGUMENT_FOR_LOG), + errmsg("cannot take logarithm using base 1"));Agreed, but if we want that, we must do it for a finite second input
too. Right now you do getregression=# select log(1, 33);
ERROR: division by zeroChao proposes matching that, which isn't insane, but I like a
specialized error message better.That’s why I chose that error message, because the existing test has this:
```
select log(1.0, 12.34);
ERROR: division by zero
```
Ah, I see, thanks for clarifying. This wasn't quite clear from the
initial post.
After thinking it over, I think we can add a check for base 1 in log_var(), and numeric_log() can just defer base 1 handling to log_var(). Please see v2, which implements this approach.
LGTM.
Kind regards,
Matthias van de Meent
Databricks (https://www.databricks.com)
Matthias van de Meent <boekewurm+postgres@gmail.com> writes:
On Thu, 15 Oct 2026 at 04:16, Chao Li <li.evan.chao@gmail.com> wrote:
After thinking it over, I think we can add a check for base 1 in log_var(), and numeric_log() can just defer base 1 handling to log_var(). Please see v2, which implements this approach.
LGTM.
Works for me too. Pushed after a bit of comment-smithing.
I did not back-patch as you suggested, though. This behavior for
infinite input has been there for as long as we've had numeric
infinity, so I think the argument for changing it in stable branches
is pretty weak.
regards, tom lane
On Oct 15, 2026, at 23:28, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Matthias van de Meent <boekewurm+postgres@gmail.com> writes:
On Thu, 15 Oct 2026 at 04:16, Chao Li <li.evan.chao@gmail.com> wrote:
After thinking it over, I think we can add a check for base 1 in log_var(), and numeric_log() can just defer base 1 handling to log_var(). Please see v2, which implements this approach.
LGTM.
Works for me too. Pushed after a bit of comment-smithing.
Thanks a lot for pushing.
I did not back-patch as you suggested, though. This behavior for
infinite input has been there for as long as we've had numeric
infinity, so I think the argument for changing it in stable branches
is pretty weak.
Understood. Thanks for the explanation.
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/