Repository: tobymao/sqlglot

Issue:
ClickHouse Map literal not serialized correctly
I'm using ClickHouse v24.12.3 and `sqlglot` 26.3.8.

Certain `SETTINGS` in ClickHouse require a Map data type ([see for example,`additional_table_filters`](https://clickhouse.com/docs/en/operations/settings/settings#additional_table_filters)), but written using the syntax `{'key1': value1, 'key2': value2}`. The `map(...)` function cannot be used in this context, see https://github.com/ClickHouse/ClickHouse/issues/75065

`sqlglot` parses the `{...}` syntax into a `exp.VarMap`, but when calling `sql()`, it's converted into a `map(...)` function call. For example:

Parsing:
```
>>> e = sqlglot.parse_one("SELECT COUNT(1) FROM table SETTINGS additional_table_filters = map('table', 'tenant_id = 123')", dialect="clickhouse")
>>> e
Select(
  expressions=[
    Count(
      this=Literal(this=1, is_string=False),
      big_int=True)],
  from=From(
    this=Table(
      this=Identifier(this=table, quoted=False))),
  settings=[
    EQ(
      this=Column(
        this=Identifier(this=additional_table_filters, quoted=False)),
      expression=VarMap(
        keys=Array(
          expressions=[
            Literal(this=table, is_string=True)]),
        values=Array(
          expressions=[
            Literal(this=tenant_id = 123, is_string=True)])))])
```

Serializing:
```
>>> e.sql(dialect="clickhouse")
"SELECT COUNT(1) FROM table SETTINGS additional_table_filters = map('table', 'tenant_id = 123')"
```

ClickHouse fails to execute the output SQL, while the input SQL works just fine:
```
7fe4f271034f :) SELECT COUNT(1) FROM table SETTINGS additional_table_filters = map('table', 'tenant_id = 123')

Syntax error: failed at position 95 (end of query):

SELECT COUNT(1) FROM table SETTINGS additional_table_filters = map('table', 'tenant_id = 123')

Expected one of: token, OpeningRoundBracket, FILTER, RESPECT NULLS, IGNORE NULLS, OVER, literal or map, literal, NULL, NULL, number, Bool, TRUE, FALSE, string literal, OpeningCurlyBrace
```

JFYI, support for parsing `{...}` had been added [here](https://github.com/tobymao/sqlglot/commit/d18a8712e088f87c969214b61a05944d279e5703).

I would expect that `{...}` is converted back to `{...}` and not to `map(...)`.

Discussion:
@VaggelisD: Hey @martijnthe, thank you for reporting this issue, I'll take a look at applying a bandaid solution.

As far as I understand, both the bracket notation and the function-based representation should yield the same `Map` object in Clickhouse, but due to an upstream bug they're interpreted differently during execution, right?

Would you also happen to know if there are cases other than `SETTINGS` that have similar behavior? Or put differently, could we generally consider the bracket notation more expressive than the function-based representation?

> I would expect that {...} is converted back to {...} and not to map(...).

This goes back to the equivalent constructors i.e if `{'key': value} == map('key', value)` then converting to one or another shouldn't make a difference; This happens for other cases too e.g there's different to construct `STRUCT`s on dialects such as DuckDB, but biasing one or another should yield the same result (semantically).@martijnthe: Thanks for the quick fix!

I’m not sure under what circumstances the bracket notation works and doesn’t work.

I did try a basic `SELECT {'key': 123}` and this actually fails with a syntax error…

So far I’m only aware of `map()` not working in `SETTINGS`, but imho that sounds more like a bug (hence the issue I opened with ClickHouse).@VaggelisD: Yeah, not the proudest moment for Clickhouse:

```SQL
:) SELECT map('key', 123);

   ┌─map('key', 123)─┐
1. │ {'key':123}     │
   └─────────────────┘
```

```SQL
:) SELECT {'key': 123};

Syntax error: failed at position 9 (''key''):

SELECT {'key': 123};

Expected substitution name (identifier)
```

I'll stop investigating this further for our sanity. 

Solution patch:
diff --git a/sqlglot/dialects/clickhouse.py b/sqlglot/dialects/clickhouse.py
index 9f77cd2913..b8fc0b989e 100644
--- a/sqlglot/dialects/clickhouse.py
+++ b/sqlglot/dialects/clickhouse.py
@@ -160,6 +160,26 @@ def _timestrtotime_sql(self: ClickHouse.Generator, expression: exp.TimeStrToTime
     return self.sql(exp.cast(ts, datatype, dialect=self.dialect))
 
 
+def _map_sql(self: ClickHouse.Generator, expression: exp.Map | exp.VarMap) -> str:
+    if not (expression.parent and expression.parent.arg_key == "settings"):
+        return _lower_func(var_map_sql(self, expression))
+
+    keys = expression.args.get("keys")
+    values = expression.args.get("values")
+
+    if not isinstance(keys, exp.Array) or not isinstance(values, exp.Array):
+        self.unsupported("Cannot convert array columns into map.")
+        return ""
+
+    args = []
+    for key, value in zip(keys.expressions, values.expressions):
+        args.append(f"{self.sql(key)}: {self.sql(value)}")
+
+    csv_args = ", ".join(args)
+
+    return f"{{{csv_args}}}"
+
+
 class ClickHouse(Dialect):
     NORMALIZE_FUNCTIONS: bool | str = False
     NULL_ORDERING = "nulls_are_last"
@@ -989,7 +1009,7 @@ class Generator(generator.Generator):
             exp.JSONPathKey: json_path_key_only_name,
             exp.JSONPathRoot: lambda *_: "",
             exp.Length: length_or_char_length_sql,
-            exp.Map: lambda self, e: _lower_func(var_map_sql(self, e)),
+            exp.Map: _map_sql,
             exp.Median: rename_func("median"),
             exp.Nullif: rename_func("nullIf"),
             exp.PartitionedByProperty: lambda self, e: f"PARTITION BY {self.sql(e, 'this')}",
@@ -1011,7 +1031,7 @@ class Generator(generator.Generator):
             exp.TimeStrToTime: _timestrtotime_sql,
             exp.TimestampAdd: _datetime_delta_sql("TIMESTAMP_ADD"),
             exp.TimestampSub: _datetime_delta_sql("TIMESTAMP_SUB"),
-            exp.VarMap: lambda self, e: _lower_func(var_map_sql(self, e)),
+            exp.VarMap: _map_sql,
             exp.Xor: lambda self, e: self.func("xor", e.this, e.expression, *e.expressions),
             exp.MD5Digest: rename_func("MD5"),
             exp.MD5: lambda self, e: self.func("LOWER", self.func("HEX", self.func("MD5", e.this))),
diff --git a/sqlglot/dialects/dialect.py b/sqlglot/dialects/dialect.py
index bd55eaefea..7b150f6d43 100644
--- a/sqlglot/dialects/dialect.py
+++ b/sqlglot/dialects/dialect.py
@@ -1119,8 +1119,8 @@ def struct_extract_sql(self: Generator, expression: exp.StructExtract) -> str:
 def var_map_sql(
     self: Generator, expression: exp.Map | exp.VarMap, map_func_name: str = "MAP"
 ) -> str:
-    keys = expression.args["keys"]
-    values = expression.args["values"]
+    keys = expression.args.get("keys")
+    values = expression.args.get("values")
 
     if not isinstance(keys, exp.Array) or not isinstance(values, exp.Array):
         self.unsupported("Cannot convert array columns into map.")
diff --git a/sqlglot/dialects/mysql.py b/sqlglot/dialects/mysql.py
index 6a5ed40bf7..d064dd5a8a 100644
--- a/sqlglot/dialects/mysql.py
+++ b/sqlglot/dialects/mysql.py
@@ -27,6 +27,7 @@
     trim_sql,
     timestrtotime_sql,
 )
+from sqlglot.generator import unsupported_args
 from sqlglot.helper import seq_get
 from sqlglot.tokens import TokenType
 
@@ -316,6 +317,8 @@ class Parser(parser.Parser):
                 this=exp.TsOrDsToDate(this=seq_get(args, 0)),
                 format=exp.Literal.string("%B"),
             ),
+            "SCHEMA": exp.CurrentSchema.from_arg_list,
+            "DATABASE": exp.CurrentSchema.from_arg_list,
             "STR_TO_DATE": _str_to_date,
             "TIMESTAMPDIFF": build_date_delta(exp.TimestampDiff),
             "TO_DAYS": lambda args: exp.paren(
@@ -1265,3 +1268,7 @@ def attimezone_sql(self, expression: exp.AtTimeZone) -> str:
 
         def isascii_sql(self, expression: exp.IsAscii) -> str:
             return f"REGEXP_LIKE({self.sql(expression.this)}, '^[[:ascii:]]*$')"
+
+        @unsupported_args("this")
+        def currentschema_sql(self, expression: exp.CurrentSchema) -> str:
+            return self.func("SCHEMA")
diff --git a/sqlglot/dialects/postgres.py b/sqlglot/dialects/postgres.py
index d358ebd461..1d0c862c5e 100644
--- a/sqlglot/dialects/postgres.py
+++ b/sqlglot/dialects/postgres.py
@@ -34,10 +34,12 @@
     ts_or_ds_add_cast,
     strposition_sql,
 )
+from sqlglot.generator import unsupported_args
 from sqlglot.helper import is_int, seq_get
 from sqlglot.parser import binary_range_parser
 from sqlglot.tokens import TokenType
 
+
 DATE_DIFF_FACTOR = {
     "MICROSECOND": " * 1000000",
     "MILLISECOND": " * 1000",
@@ -393,6 +395,11 @@ class Parser(parser.Parser):
             "LEVENSHTEIN_LESS_EQUAL": _build_levenshtein_less_equal,
         }
 
+        NO_PAREN_FUNCTIONS = {
+            **parser.Parser.NO_PAREN_FUNCTIONS,
+            TokenType.CURRENT_SCHEMA: exp.CurrentSchema,
+        }
+
         FUNCTION_PARSERS = {
             **parser.Parser.FUNCTION_PARSERS,
             "DATE_PART": lambda self: self._parse_date_part(),
@@ -723,3 +730,7 @@ def computedcolumnconstraint_sql(self, expression: exp.ComputedColumnConstraint)
 
         def isascii_sql(self, expression: exp.IsAscii) -> str:
             return f"({self.sql(expression.this)} ~ '^[[:ascii:]]*$')"
+
+        @unsupported_args("this")
+        def currentschema_sql(self, expression: exp.CurrentSchema) -> str:
+            return "CURRENT_SCHEMA"
diff --git a/sqlglot/dialects/sqlite.py b/sqlglot/dialects/sqlite.py
index d7d9fb66db..721e23689c 100644
--- a/sqlglot/dialects/sqlite.py
+++ b/sqlglot/dialects/sqlite.py
@@ -17,8 +17,8 @@
     rename_func,
     strposition_sql,
 )
-from sqlglot.tokens import TokenType
 from sqlglot.generator import unsupported_args
+from sqlglot.tokens import TokenType
 
 
 def _date_add_sql(self: SQLite.Generator, expression: exp.DateAdd) -> str:
@@ -302,3 +302,7 @@ def transaction_sql(self, expression: exp.Transaction) -> str:
 
         def isascii_sql(self, expression: exp.IsAscii) -> str:
             return f"(NOT {self.sql(expression.this)} GLOB CAST(x'2a5b5e012d7f5d2a' AS TEXT))"
+
+        @unsupported_args("this")
+        def currentschema_sql(self, expression: exp.CurrentSchema) -> str:
+            return "'main'"
diff --git a/sqlglot/dialects/tsql.py b/sqlglot/dialects/tsql.py
index 8c585cfffc..b30dc6b84e 100644
--- a/sqlglot/dialects/tsql.py
+++ b/sqlglot/dialects/tsql.py
@@ -575,6 +575,7 @@ class Parser(parser.Parser):
             "RIGHT": _build_with_arg_as_text(exp.Right),
             "PARSENAME": _build_parsename,
             "REPLICATE": exp.Repeat.from_arg_list,
+            "SCHEMA_NAME": exp.CurrentSchema.from_arg_list,
             "SQUARE": lambda args: exp.Pow(this=seq_get(args, 0), expression=exp.Literal.number(2)),
             "SYSDATETIME": exp.CurrentTimestamp.from_arg_list,
             "SUSER_NAME": exp.CurrentUser.from_arg_list,
@@ -927,6 +928,7 @@ class Generator(generator.Generator):
             exp.Min: min_or_least,
             exp.NumberToStr: _format_sql,
             exp.Repeat: rename_func("REPLICATE"),
+            exp.CurrentSchema: rename_func("SCHEMA_NAME"),
             exp.Select: transforms.preprocess(
                 [
                     transforms.eliminate_distinct_on,
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index e3cc94e158..ac6ceede67 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -5628,6 +5628,10 @@ class CurrentTimestamp(Func):
     arg_types = {"this": False, "sysdate": False}
 
 
+class CurrentSchema(Func):
+    arg_types = {"this": False}
+
+
 class CurrentUser(Func):
     arg_types = {"this": False}
 
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index 5a803e52c9..25660e1e1a 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -487,6 +487,7 @@ class Parser(metaclass=_Parser):
         TokenType.CONSTRAINT,
         TokenType.COPY,
         TokenType.CUBE,
+        TokenType.CURRENT_SCHEMA,
         TokenType.DEFAULT,
         TokenType.DELETE,
         TokenType.DESC,
@@ -589,6 +590,7 @@ class Parser(metaclass=_Parser):
         TokenType.COMMAND,
         TokenType.CURRENT_DATE,
         TokenType.CURRENT_DATETIME,
+        TokenType.CURRENT_SCHEMA,
         TokenType.CURRENT_TIMESTAMP,
         TokenType.CURRENT_TIME,
         TokenType.CURRENT_USER,
diff --git a/sqlglot/tokens.py b/sqlglot/tokens.py
index 0d2704bfaf..06afaa0c6f 100644
--- a/sqlglot/tokens.py
+++ b/sqlglot/tokens.py
@@ -250,6 +250,7 @@ class TokenType(AutoName):
     CUBE = auto()
     CURRENT_DATE = auto()
     CURRENT_DATETIME = auto()
+    CURRENT_SCHEMA = auto()
     CURRENT_TIME = auto()
     CURRENT_TIMESTAMP = auto()
     CURRENT_USER = auto()
@@ -713,6 +714,7 @@ class Tokenizer(metaclass=_Tokenizer):
         "CROSS": TokenType.CROSS,
         "CUBE": TokenType.CUBE,
         "CURRENT_DATE": TokenType.CURRENT_DATE,
+        "CURRENT_SCHEMA": TokenType.CURRENT_SCHEMA,
         "CURRENT_TIME": TokenType.CURRENT_TIME,
         "CURRENT_TIMESTAMP": TokenType.CURRENT_TIMESTAMP,
         "CURRENT_USER": TokenType.CURRENT_USER,


Test patch:
diff --git a/tests/dialects/test_clickhouse.py b/tests/dialects/test_clickhouse.py
index a4ee23c923..c5cdd1958d 100644
--- a/tests/dialects/test_clickhouse.py
+++ b/tests/dialects/test_clickhouse.py
@@ -551,6 +551,9 @@ def test_clickhouse(self):
 
         self.validate_identity("SELECT 1_2_3_4_5", "SELECT 12345")
         self.validate_identity("SELECT 1_b", "SELECT 1_b")
+        self.validate_identity(
+            "SELECT COUNT(1) FROM table SETTINGS additional_table_filters = {'a': 'b', 'c': 'd'}"
+        )
 
     def test_clickhouse_values(self):
         values = exp.select("*").from_(
diff --git a/tests/dialects/test_dialect.py b/tests/dialects/test_dialect.py
index d408f90e1b..6a8b1ca77a 100644
--- a/tests/dialects/test_dialect.py
+++ b/tests/dialects/test_dialect.py
@@ -3306,3 +3306,19 @@ def test_median(self):
                     "postgres": f"PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x){suffix}",
                 },
             )
+
+    def test_current_schema(self):
+        self.validate_all(
+            "CURRENT_SCHEMA()",
+            read={
+                "mysql": "SCHEMA()",
+                "postgres": "CURRENT_SCHEMA()",
+                "tsql": "SCHEMA_NAME()",
+            },
+            write={
+                "sqlite": "'main'",
+                "mysql": "SCHEMA()",
+                "postgres": "CURRENT_SCHEMA",
+                "tsql": "SCHEMA_NAME()",
+            },
+        )
diff --git a/tests/dialects/test_mysql.py b/tests/dialects/test_mysql.py
index 34ee063364..4a9e2b4df2 100644
--- a/tests/dialects/test_mysql.py
+++ b/tests/dialects/test_mysql.py
@@ -253,8 +253,7 @@ def test_identity(self):
         self.validate_identity("SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE")
         self.validate_identity("SET TRANSACTION READ ONLY")
         self.validate_identity("SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ WRITE")
-        self.validate_identity("SELECT SCHEMA()")
-        self.validate_identity("SELECT DATABASE()")
+        self.validate_identity("DATABASE()", "SCHEMA()")
         self.validate_identity(
             "SET GLOBAL sort_buffer_size = 1000000, SESSION sort_buffer_size = 1000000"
         )
diff --git a/tests/dialects/test_postgres.py b/tests/dialects/test_postgres.py
index 6714de2b30..b02cc8cf37 100644
--- a/tests/dialects/test_postgres.py
+++ b/tests/dialects/test_postgres.py
@@ -73,6 +73,7 @@ def test_postgres(self):
         self.validate_identity("SELECT * FROM r CROSS JOIN LATERAL UNNEST(ARRAY[1]) AS s(location)")
         self.validate_identity("CAST(1 AS DECIMAL) / CAST(2 AS DECIMAL) * -100")
         self.validate_identity("EXEC AS myfunc @id = 123", check_command_warning=True)
+        self.validate_identity("SELECT CURRENT_SCHEMA")
         self.validate_identity("SELECT CURRENT_USER")
         self.validate_identity("SELECT * FROM ONLY t1")
         self.validate_identity(
diff --git a/tests/dialects/test_tsql.py b/tests/dialects/test_tsql.py
index dc35610b7a..8960757eef 100644
--- a/tests/dialects/test_tsql.py
+++ b/tests/dialects/test_tsql.py
@@ -443,6 +443,16 @@ def test_tsql(self):
             "CREATE TABLE db.t1 (a INTEGER, b INTEGER, CONSTRAINT c PRIMARY KEY (a DESC, b))"
         )
 
+        self.validate_all(
+            "SCHEMA_NAME(id)",
+            write={
+                "sqlite": "'main'",
+                "mysql": "SCHEMA()",
+                "postgres": "CURRENT_SCHEMA",
+                "tsql": "SCHEMA_NAME(id)",
+            },
+        )
+
     def test_option(self):
         possible_options = [
             "HASH GROUP",