Repository: tobymao/sqlglot

Issue:
Log10 vs LN
sqlglot does not transpile SQL using log() correctly. 

sqlglot.transpile("select exp(log(2))", read="mysql", write="duckdb") incorrectly outputs `select exp(log(2))` when it should be `select exp(ln(2))`

```
select exp(log(2)) 
```

Returns different results on different platforms. 

In MySQL and SQL Server, [log](https://dev.mysql.com/doc/refman/8.0/en/mathematical-functions.html) is the natural log, so the result of the statement is 2

In PostGres and DuckDB, for instance, [log](https://www.postgresql.org/docs/9.1/functions-math.html) is log base 10, so the result is 1.35.

Discussion:

Patch:
diff --git a/sqlglot/dialects/bigquery.py b/sqlglot/dialects/bigquery.py
index bd8b08ed..a3f9e6de 100644
--- a/sqlglot/dialects/bigquery.py
+++ b/sqlglot/dialects/bigquery.py
@@ -213,6 +213,9 @@ class BigQuery(Dialect):
             ),
         }
 
+        LOG_BASE_FIRST = False
+        LOG_DEFAULTS_TO_LN = True
+
     class Generator(generator.Generator):
         TRANSFORMS = {
             **generator.Generator.TRANSFORMS,  # type: ignore
diff --git a/sqlglot/dialects/clickhouse.py b/sqlglot/dialects/clickhouse.py
index b54a77d8..89e22964 100644
--- a/sqlglot/dialects/clickhouse.py
+++ b/sqlglot/dialects/clickhouse.py
@@ -68,6 +68,8 @@ class ClickHouse(Dialect):
 
         TABLE_ALIAS_TOKENS = {*parser.Parser.TABLE_ALIAS_TOKENS} - {TokenType.ANY}  # type: ignore
 
+        LOG_DEFAULTS_TO_LN = True
+
         def _parse_in(
             self, this: t.Optional[exp.Expression], is_global: bool = False
         ) -> exp.Expression:
diff --git a/sqlglot/dialects/databricks.py b/sqlglot/dialects/databricks.py
index 4268f1be..2f93ee7d 100644
--- a/sqlglot/dialects/databricks.py
+++ b/sqlglot/dialects/databricks.py
@@ -16,6 +16,8 @@ class Databricks(Spark):
             "DATEDIFF": parse_date_delta(exp.DateDiff),
         }
 
+        LOG_DEFAULTS_TO_LN = True
+
     class Generator(Spark.Generator):
         TRANSFORMS = {
             **Spark.Generator.TRANSFORMS,  # type: ignore
diff --git a/sqlglot/dialects/drill.py b/sqlglot/dialects/drill.py
index 11b073a6..a33aadc7 100644
--- a/sqlglot/dialects/drill.py
+++ b/sqlglot/dialects/drill.py
@@ -101,6 +101,8 @@ class Drill(Dialect):
             "TO_CHAR": format_time_lambda(exp.TimeToStr, "drill"),
         }
 
+        LOG_DEFAULTS_TO_LN = True
+
     class Generator(generator.Generator):
         TYPE_MAPPING = {
             **generator.Generator.TYPE_MAPPING,  # type: ignore
diff --git a/sqlglot/dialects/hive.py b/sqlglot/dialects/hive.py
index fe9d85ea..68137aef 100644
--- a/sqlglot/dialects/hive.py
+++ b/sqlglot/dialects/hive.py
@@ -253,11 +253,6 @@ class Hive(Dialect):
             "FROM_UNIXTIME": format_time_lambda(exp.UnixToStr, "hive", True),
             "GET_JSON_OBJECT": exp.JSONExtractScalar.from_arg_list,
             "LOCATE": locate_to_strposition,
-            "LOG": (
-                lambda args: exp.Log.from_arg_list(args)
-                if len(args) > 1
-                else exp.Ln.from_arg_list(args)
-            ),
             "MAP": parse_var_map,
             "MONTH": lambda args: exp.Month(this=exp.TsOrDsToDate.from_arg_list(args)),
             "PERCENTILE": exp.Quantile.from_arg_list,
@@ -277,6 +272,8 @@ class Hive(Dialect):
             ),
         }
 
+        LOG_DEFAULTS_TO_LN = True
+
     class Generator(generator.Generator):
         TYPE_MAPPING = {
             **generator.Generator.TYPE_MAPPING,  # type: ignore
diff --git a/sqlglot/dialects/mysql.py b/sqlglot/dialects/mysql.py
index 7c31facc..5dfa811d 100644
--- a/sqlglot/dialects/mysql.py
+++ b/sqlglot/dialects/mysql.py
@@ -290,6 +290,8 @@ class MySQL(Dialect):
             "SWAPS",
         }
 
+        LOG_DEFAULTS_TO_LN = True
+
         def _parse_show_mysql(self, this, target=False, full=None, global_=None):
             if target:
                 if isinstance(target, str):
diff --git a/sqlglot/dialects/tsql.py b/sqlglot/dialects/tsql.py
index acf5251e..8e9b6c3c 100644
--- a/sqlglot/dialects/tsql.py
+++ b/sqlglot/dialects/tsql.py
@@ -313,6 +313,9 @@ class TSQL(Dialect):
             TokenType.END: lambda self: self._parse_command(),
         }
 
+        LOG_BASE_FIRST = False
+        LOG_DEFAULTS_TO_LN = True
+
         def _parse_system_time(self) -> t.Optional[exp.Expression]:
             if not self._match_text_seq("FOR", "SYSTEM_TIME"):
                 return None
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index 91c2b035..82695253 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -676,6 +676,7 @@ class Parser(metaclass=_Parser):
         "CONVERT": lambda self: self._parse_convert(self.STRICT_CAST),
         "EXTRACT": lambda self: self._parse_extract(),
         "JSON_OBJECT": lambda self: self._parse_json_object(),
+        "LOG": lambda self: self._parse_logarithm(),
         "POSITION": lambda self: self._parse_position(),
         "STRING_AGG": lambda self: self._parse_string_agg(),
         "SUBSTRING": lambda self: self._parse_substring(),
@@ -733,6 +734,9 @@ class Parser(metaclass=_Parser):
 
     CONVERT_TYPE_FIRST = False
 
+    LOG_BASE_FIRST = True
+    LOG_DEFAULTS_TO_LN = False
+
     __slots__ = (
         "error_level",
         "error_message_context",
@@ -3381,6 +3385,19 @@ class Parser(metaclass=_Parser):
             encoding=encoding,
         )
 
+    def _parse_logarithm(self) -> exp.Expression:
+        # Default argument order is base, expression
+        args = self._parse_csv(self._parse_range)
+
+        if len(args) > 1:
+            if not self.LOG_BASE_FIRST:
+                args.reverse()
+            return exp.Log.from_arg_list(args)
+
+        return self.expression(
+            exp.Ln if self.LOG_DEFAULTS_TO_LN else exp.Log, this=seq_get(args, 0)
+        )
+
     def _parse_position(self, haystack_first: bool = False) -> exp.Expression:
         args = self._parse_csv(self._parse_bitwise)
 


Test patch:
diff --git a/tests/dialects/test_dialect.py b/tests/dialects/test_dialect.py
index 1f551c01..0805e9c9 100644
--- a/tests/dialects/test_dialect.py
+++ b/tests/dialects/test_dialect.py
@@ -1511,6 +1511,46 @@ SELECT
             },
         )
 
+    def test_logarithm(self):
+        self.validate_all(
+            "LOG(x)",
+            read={
+                "duckdb": "LOG(x)",
+                "postgres": "LOG(x)",
+                "redshift": "LOG(x)",
+                "sqlite": "LOG(x)",
+                "teradata": "LOG(x)",
+            },
+        )
+        self.validate_all(
+            "LN(x)",
+            read={
+                "bigquery": "LOG(x)",
+                "clickhouse": "LOG(x)",
+                "databricks": "LOG(x)",
+                "drill": "LOG(x)",
+                "hive": "LOG(x)",
+                "mysql": "LOG(x)",
+                "tsql": "LOG(x)",
+            },
+        )
+        self.validate_all(
+            "LOG(b, n)",
+            read={
+                "bigquery": "LOG(n, b)",
+                "databricks": "LOG(b, n)",
+                "drill": "LOG(b, n)",
+                "hive": "LOG(b, n)",
+                "mysql": "LOG(b, n)",
+                "oracle": "LOG(b, n)",
+                "postgres": "LOG(b, n)",
+                "snowflake": "LOG(b, n)",
+                "spark": "LOG(b, n)",
+                "sqlite": "LOG(b, n)",
+                "tsql": "LOG(n, b)",
+            },
+        )
+
     def test_count_if(self):
         self.validate_identity("COUNT_IF(DISTINCT cond)")