Date: 2020-08-31 18:26:09 +0200
From: Martin van Dinther <<martin.van.dinther>>
To: SQL devs <>
Version: 11.37.7 (Jun2020)
CC: @PedroTadim

Last updated: 2020-10-19 11:06:19 +0200

Comment 28038

Date: 2020-08-31 18:26:09 +0200
From: Martin van Dinther <<martin.van.dinther>>

User-Agent: Mozilla/5.0 (Windows NT 6.1; rv:79.0) Gecko/20100101 Firefox/79.0
Build Identifier:

According to the SQL standard 2016 (ISO_IEC_9075-2E_Foundation_2016.pdf page 321) the 2 argument LOG(base, x) function is defined as:

::=
LOG

::=

::=

However MonetDB has implemented the arguments switched so LOG(x, base) instead of LOG(base, x).

Also other RDBMS follow the SQL Standard. See:
https://www.postgresql.org/docs/12/functions-math.html
log(b numeric, x numeric)

https://dev.mysql.com/doc/refman/8.0/en/mathematical-functions.htmlfunction_log
LOG(B,X)

https://docs.oracle.com/cd/B28359_01/olap.111/b28126/dml_functions_2014.htmOLADM590
LOG([base,] expression)

https://en.wikibooks.org/wiki/SQL_Dialects_Reference/Functions_and_expressions/Math_functions/Numeric_functions
Search for Feature: Logarithm of x, base b

The implementation was added in Apr2019 release,
see also: https://www.monetdb.org/bugzilla/show_bug.cgi?id=3416

The implementation should be corrected ASAP to be in compliance with the SQL Standard and behave same as other RDBMS.

Reproducible: Always

Steps to Reproduce:

Select log(2.0, 64.0); -- 6.0
SELECT LOG(2,65536); -- 16.0
SELECT LOG(10,100); -- 2.0
SELECT LOG(2,42); -- 5.392317422778761
SELECT LOG(1,100); -- NULL

Actual Results:

sql>Select log(2.0, 64.0); -- 6.0
+--------------------------+
| %2 |
+==========================+
| 0.16666666666666669 |
+--------------------------+
1 tuple
sql>SELECT LOG(2,65536); -- 16.0
+--------------------------+
| %2 |
+==========================+
| 0.0625 |
+--------------------------+
1 tuple
sql>SELECT LOG(10,100); -- 2.0
+--------------------------+
| %2 |
+==========================+
| 0.5 |
+--------------------------+
1 tuple
sql>SELECT LOG(2,42); -- 5.392317422778761
+--------------------------+
| %2 |
+==========================+
| 0.1854490234153689 |
+--------------------------+
1 tuple
sql>SELECT LOG(1,100); -- NULL
+--------------------------+
| %2 |
+==========================+
| 0 |
+--------------------------+
1 tuple
sql>

Expected Results:

sql>Select log(2.0, 64.0); -- 6.0
+--------------------------+
| %2 |
+==========================+
| 6 |
+--------------------------+
1 tuple
sql>SELECT LOG(2,65536); -- 16.0
+--------------------------+
| %2 |
+==========================+
| 16 |
+--------------------------+
1 tuple
sql>SELECT LOG(10,100); -- 2.0
+--------------------------+
| %2 |
+==========================+
| 2 |
+--------------------------+
1 tuple
sql>SELECT LOG(2,42); -- 5.392317422778761
+--------------------------+
| %2 |
+==========================+
| 5.392317422778761 |
+--------------------------+
1 tuple
sql>SELECT LOG(1,100); -- NULL
+--------------------------+
| %2 |
+==========================+
| inf |
+--------------------------+
1 tuple
sql>