Repository: tobymao/sqlglot

Issue:
invalid transpilation of clickhouse `isNaN` function
**Fully reproducible code snippet**

```
In [9]: import sqlglot as sg

In [10]: sg.__version__
Out[10]: '17.8.6'

In [11]: sg.parse_one("select isNaN(1.0)", read="clickhouse").sql(dialect="clickhouse")
Out[11]: 'SELECT IS_NAN(1.0)'
```

Expected output is:

```
Out[11]: 'SELECT isNaN(1.0)'
```

**Official Documentation**

https://clickhouse.com/docs/en/sql-reference/functions/other-functions#isnanx

Is there a way I can disable function normalization for any and all function calls for clickhouse?

These incorrect translation bugs keep popping up and I'd like for function calls to pass through unchanged if possible.


Discussion:

Patch:
diff --git a/sqlglot/dialects/bigquery.py b/sqlglot/dialects/bigquery.py
index fd9965c6..df9065f1 100644
--- a/sqlglot/dialects/bigquery.py
+++ b/sqlglot/dialects/bigquery.py
@@ -211,6 +211,10 @@ class BigQuery(Dialect):
         "TZH": "%z",
     }
 
+    # The _PARTITIONTIME and _PARTITIONDATE pseudo-columns are not returned by a SELECT * statement
+    # https://cloud.google.com/bigquery/docs/querying-partitioned-tables#query_an_ingestion-time_partitioned_table
+    PSEUDOCOLUMNS = {"_PARTITIONTIME", "_PARTITIONDATE"}
+
     @classmethod
     def normalize_identifier(cls, expression: E) -> E:
         # In BigQuery, CTEs aren't case-sensitive, but table names are (by default, at least).
diff --git a/sqlglot/dialects/dialect.py b/sqlglot/dialects/dialect.py
index 8b8ebd32..9ad2393c 100644
--- a/sqlglot/dialects/dialect.py
+++ b/sqlglot/dialects/dialect.py
@@ -168,6 +168,10 @@ class Dialect(metaclass=_Dialect):
     # special syntax cast(x as date format 'yyyy') defaults to time_mapping
     FORMAT_MAPPING: t.Dict[str, str] = {}
 
+    # Columns that are auto-generated by the engine corresponding to this dialect
+    # Such columns may be excluded from SELECT * queries, for example
+    PSEUDOCOLUMNS: t.Set[str] = set()
+
     # Autofilled
     tokenizer_class = Tokenizer
     parser_class = Parser
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index 28d7fd50..ed0a6819 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -2379,7 +2379,12 @@ class Generator:
             elif arg_value is not None:
                 args.append(arg_value)
 
-        return self.func(expression.sql_name(), *args)
+        if self.normalize_functions:
+            name = expression.sql_name()
+        else:
+            name = (expression._meta and expression.meta.get("name")) or expression.sql_name()
+
+        return self.func(name, *args)
 
     def func(
         self,
diff --git a/sqlglot/optimizer/qualify_columns.py b/sqlglot/optimizer/qualify_columns.py
index 26571882..9c34cef2 100644
--- a/sqlglot/optimizer/qualify_columns.py
+++ b/sqlglot/optimizer/qualify_columns.py
@@ -39,6 +39,7 @@ def qualify_columns(
     """
     schema = ensure_schema(schema)
     infer_schema = schema.empty if infer_schema is None else infer_schema
+    pseudocolumns = Dialect.get_or_raise(schema.dialect).PSEUDOCOLUMNS
 
     for scope in traverse_scope(expression):
         resolver = Resolver(scope, schema, infer_schema=infer_schema)
@@ -55,7 +56,7 @@ def qualify_columns(
             _expand_alias_refs(scope, resolver)
 
         if not isinstance(scope.expression, exp.UDTF):
-            _expand_stars(scope, resolver, using_column_tables)
+            _expand_stars(scope, resolver, using_column_tables, pseudocolumns)
             _qualify_outputs(scope)
         _expand_group_by(scope)
         _expand_order_by(scope, resolver)
@@ -326,7 +327,10 @@ def _qualify_columns(scope: Scope, resolver: Resolver) -> None:
 
 
 def _expand_stars(
-    scope: Scope, resolver: Resolver, using_column_tables: t.Dict[str, t.Any]
+    scope: Scope,
+    resolver: Resolver,
+    using_column_tables: t.Dict[str, t.Any],
+    pseudocolumns: t.Set[str],
 ) -> None:
     """Expand stars to lists of column selections"""
 
@@ -367,14 +371,8 @@ def _expand_stars(
 
             columns = resolver.get_source_columns(table, only_visible=True)
 
-            # The _PARTITIONTIME and _PARTITIONDATE pseudo-columns are not returned by a SELECT * statement
-            # https://cloud.google.com/bigquery/docs/querying-partitioned-tables#query_an_ingestion-time_partitioned_table
-            if resolver.schema.dialect == "bigquery":
-                columns = [
-                    name
-                    for name in columns
-                    if name.upper() not in ("_PARTITIONTIME", "_PARTITIONDATE")
-                ]
+            if pseudocolumns:
+                columns = [name for name in columns if name.upper() not in pseudocolumns]
 
             if columns and "*" not in columns:
                 if pivot and has_pivoted_source and pivot_columns and pivot_output_columns:
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index 2a6f551c..f714c8df 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -835,6 +835,7 @@ class Parser(metaclass=_Parser):
     UNNEST_COLUMN_ONLY: bool = False
     ALIAS_POST_TABLESAMPLE: bool = False
     STRICT_STRING_CONCAT = False
+    NORMALIZE_FUNCTIONS = "upper"
     NULL_ORDERING: str = "nulls_are_small"
     SHOW_TRIE: t.Dict = {}
     SET_TRIE: t.Dict = {}
@@ -3360,7 +3361,10 @@ class Parser(metaclass=_Parser):
             args = self._parse_csv(lambda: self._parse_lambda(alias=alias))
 
             if function and not anonymous:
-                this = self.validate_expression(function(args), args)
+                func = self.validate_expression(function(args), args)
+                if not self.NORMALIZE_FUNCTIONS:
+                    func.meta["name"] = this
+                this = func
             else:
                 this = self.expression(exp.Anonymous, this=this, expressions=args)
 


Test patch:
diff --git a/tests/dialects/test_bigquery.py b/tests/dialects/test_bigquery.py
index abdcb4db..29b68bce 100644
--- a/tests/dialects/test_bigquery.py
+++ b/tests/dialects/test_bigquery.py
@@ -390,7 +390,7 @@ class TestBigQuery(Validator):
             },
         )
         self.validate_all(
-            "current_timestamp",
+            "CURRENT_TIMESTAMP",
             write={
                 "bigquery": "CURRENT_TIMESTAMP()",
                 "duckdb": "CURRENT_TIMESTAMP",
@@ -401,7 +401,7 @@ class TestBigQuery(Validator):
             },
         )
         self.validate_all(
-            "current_timestamp()",
+            "CURRENT_TIMESTAMP()",
             write={
                 "bigquery": "CURRENT_TIMESTAMP()",
                 "duckdb": "CURRENT_TIMESTAMP",
diff --git a/tests/dialects/test_clickhouse.py b/tests/dialects/test_clickhouse.py
index bc826453..16c10feb 100644
--- a/tests/dialects/test_clickhouse.py
+++ b/tests/dialects/test_clickhouse.py
@@ -6,6 +6,12 @@ class TestClickhouse(Validator):
     dialect = "clickhouse"
 
     def test_clickhouse(self):
+        expr = parse_one("count(x)")
+        self.assertEqual(expr.sql(dialect="clickhouse"), "COUNT(x)")
+        self.assertIsNone(expr._meta)
+
+        self.validate_identity("SELECT isNaN(1.0)")
+        self.validate_identity("SELECT startsWith('Spider-Man', 'Spi')")
         self.validate_identity("SELECT xor(TRUE, FALSE)")
         self.validate_identity("ATTACH DATABASE DEFAULT ENGINE = ORDINARY")
         self.validate_identity("CAST(['hello'], 'Array(Enum8(''hello'' = 1))')")
@@ -162,7 +168,7 @@ class TestClickhouse(Validator):
             ORDER BY loyalty ASC
             """,
             write={
-                "clickhouse": "SELECT loyalty, COUNT() FROM hits LEFT SEMI JOIN users USING (UserID)"
+                "clickhouse": "SELECT loyalty, count() FROM hits LEFT SEMI JOIN users USING (UserID)"
                 + " GROUP BY loyalty ORDER BY loyalty"
             },
         )
@@ -247,7 +253,7 @@ class TestClickhouse(Validator):
         for data_type in data_types:
             self.validate_all(
                 f"pow(2, 32)::{data_type}",
-                write={"clickhouse": f"CAST(POWER(2, 32) AS {data_type})"},
+                write={"clickhouse": f"CAST(pow(2, 32) AS {data_type})"},
             )
 
     def test_ddl(self):
@@ -304,8 +310,8 @@ GROUP BY
   id,
   toStartOfDay(timestamp)
 SET
-  max_hits = MAX(max_hits),
-  sum_hits = SUM(sum_hits)""",
+  max_hits = max(max_hits),
+  sum_hits = sum(sum_hits)""",
             },
             pretty=True,
         )
@@ -447,8 +453,8 @@ GROUP BY
   k1,
   k2
 SET
-  x = MAX(x),
-  y = MIN(y)""",
+  x = max(x),
+  y = min(y)""",
             },
             pretty=True,
         )
diff --git a/tests/dialects/test_drill.py b/tests/dialects/test_drill.py
index a7f609aa..41c02fbd 100644
--- a/tests/dialects/test_drill.py
+++ b/tests/dialects/test_drill.py
@@ -66,7 +66,7 @@ class TestDrill(Validator):
             write={
                 "drill": "SELECT * FROM (SELECT education_level, salary, marital_status, "
                 "EXTRACT(year FROM age(birth_date)) AS age FROM cp.`employee.json`) "
-                "PIVOT(AVG(salary) AS avg_salary, AVG(age) AS avg_age FOR marital_status "
+                "PIVOT(avg(salary) AS avg_salary, avg(age) AS avg_age FOR marital_status "
                 "IN ('M' AS married, 'S' AS single))"
             },
         )