Repository: tobymao/sqlglot
Pull request: Move SET parsing/generating logic from MySQL to base
- Moved a subset of the logic we use for parsing / generating SET statements to the base classes, so that we can parse statements like `SET variable = value` with finer granularity.
- When the base `_parse_set` method fails, the parser will fallback to parsing the input as a command.

cc: @joocer @tobymao @barakalon 

Fixes #1225 

Problem statement:
SET commands not decomposed
Parsing a `SET` command such as 

~~~sql
SET @q = 4;
~~~

results in this:
_(note, the equals expression is just presented as a string literal)_

~~~
(COMMAND this: SET, expression: 
  (LITERAL this: @q = 4, is_string: True))
~~~

I had expected the statement to be decomposed into something like this:
_(note, the identifier and literal are different nodes in the tree)_
~~~
(COMMAND this: SET, expression: 
  ((IDENTIFIER this: @q, quoted: False)
  (LITERAL this: 4, is_string: False)))
~~~

Hints from issue discussion:
many command in SQL haven't been high priority and so are parsed in the catchall "command" node.

basic parsing of set should be easy with just doing parse_conjunction(), but i'm not sure how SET is used in all dialects.
Thanks @tobymao,

Looking at another non-SELECT example
~~~sql
SHOW FULL COLUMNS FROM $astronauts"
~~~

I get a similar, COMMAND node with the first keyword and then the rest of the statement as a single literal. I'm guessing from the name only that `parse_conjuction()` is extracting predicate expressions (which `@q = 4` looks like) but won't parse `SHOW` statements?
show is an entirely different command with it's own syntax
1. remove TokenType.SET from Tokenizer.COMMANDS
2. add it to Parser.STATEMENT_PARSERS
3. parser func create parse_set
4. create a SetStatement node in expression.py
5. return a self.expression(exp.SetStatement, this=self._parse_conjunction())
6. write unit tests

please do some research on various major dialects to see what is possible

spark, snowflake, bigquery, mysql, postgres

have an understanding of what needs to be supported in the SET command, this will dictate what step 5 actually does. 
Initial reconnaissance of documentation:

[spark](https://spark.apache.org/docs/latest/sql-ref-syntax-aux-conf-mgmt-set.html)
[snowflake](https://docs.snowflake.com/en/sql-reference/sql/set)
[bigquery](https://cloud.google.com/bigquery/docs/reference/standard-sql/procedural-language#set)
[MySQL](https://dev.mysql.com/doc/refman/8.0/en/set-statement.html)
[postgres](https://www.postgresql.org/docs/current/sql-set.html)
[trino](https://trino.io/docs/current/sql.html)

Initial Observations:
- Spark supports `SET;` as a valid command which returns all values
- BigQuery appears to require a `DECLARE` statement creating the variable before it can be set
- Snowflake and BigQuery appear to support multiple variable assignments in one statement
- Postgres appears to have the most modifiers in its syntax
- Trino only accepts `SET` in combination with one of a small set of following keywords
- DuckDB appears to have no `SET` command
Identified variations

**Spark**
~~~
SET variable = value;
SET -v;
SET;
SET variable;
~~~

**Snowflake**
~~~
SET variable = value;
SET ( variable [, variable ... ]) = ( value [, value ... ]);
~~~

**BigQuery**
~~~
SET variable = value;
SET ( variable [, variable ... ]) = ( value [, value ... ]);
~~~

**MySQL**
~~~
SET [[@@]GLOBAL|[@@]SESSION|[@@]PERSIST|[@@]PERSIST_ONLY] variable = value;
SET [@@]variable = value;
SET [[@@]GLOBAL|[@@]SESSION|[@@]PERSIST|[@@]PERSIST_ONLY].variable = value;
SET <CHARACTER SET | CHARSET> <name | DEFAULT>;
SET NAMES <charset_name> < COLLATE 'collation_name' | DEFAULT >;
SET DEFAULT ROLE <NONE | ALL | role [, role ... ]> TO user [, user ...];
SET ROLE <DEFAULT | NONE | ALL | ALL EXCEPT role [, role ...] | role [, role ...]>;
SET PASSWORD [FOR user] auth_option [REPLACE 'current_auth_string'] [RETAIN CURRENT PASSWORD];
SET RESOURCE GROUP group_name [FOR thread_id [, thread_id ...] ];
SET [GLOBAL | SESSION] TRANSACTION <ISOLATION LEVEL < REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED | SERIALIZABLE > | < READ WRITE | READ ONLY >, ... >
~~~

**Postgres**
~~~
SET [ SESSION | LOCAL ] configuration_parameter < TO | = > < value | 'value' | DEFAULT >
SET [ SESSION | LOCAL ] TIME ZONE < value | 'value' | LOCAL | DEFAULT >
~~~
_postgres documentation refers to other variations as 'special case'_

**Trino**
~~~
SET ROLE ( role | ALL | NONE ) [ IN catalog ];
SET SESSION variable = value;
SET TIME ZONE [ LOCAL | expression ];
~~~

**Oracle & TSQL**
~~~
SET variable value [modifiers]
~~~
Please don't forget Oracle and Microsoft SQL Server when talking about major dialects. Even though the Open Source community rarely uses them, they are still very big players in productive environments.

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqpug/SET-system-variable-summary.html

https://learn.microsoft.com/en-us/sql/t-sql/statements/set-statements-transact-sql?view=sql-server-ver16

Most commonly used SQL dialects, according to the StackOverflow survey 2020: https://insights.stackoverflow.com/survey/2020#technology-databases-all-respondents4
The most universal format appears to be:
~~~
SET [ SESSION | GLOBAL | LOCAL ] variable [=|TO] value;
~~~

This is a mixed syntax that covers the syntax supported by Spark, Snowflake, BigQuery, MySQL, Trino, Postgres, Oracle and TSQL.

An outline of logic to parse this would be something like this (assuming we're working with a queue of tokens):

~~~python
pop the first token
if the first token in ("SESSION", "GLOBAL", "LOCAL"):
    it's a modifier, save it and pop the next token
this token is our variable name, save it
pop the next token
if this token is in ("TO", "="):
   it's punctuation, discard it and pop the next token
this token is our value, save it
if we have more tokens, revert to the command response and return
build the node, with the modifier, variable and value as the attributes
~~~

FYI duckdb has a SET command, see [Configuration](https://duckdb.org/docs/sql/configuration.html), eg:
```
-- set the memory limit of the system to 10GB
SET memory_limit='10GB';
-- configure the system to use 1 thread
SET threads TO 1;
```


Thanks @tekumara, it looks like the format for DuckDB would be covered by the proposed format above.

Commit message: Move SET parsing logic from MySQL to base parser

Patch to review:
diff --git a/sqlglot/dialects/mysql.py b/sqlglot/dialects/mysql.py
index a8312358c5..1e2cfa3287 100644
--- a/sqlglot/dialects/mysql.py
+++ b/sqlglot/dialects/mysql.py
@@ -177,7 +177,7 @@ class Tokenizer(tokens.Tokenizer):
             "@@": TokenType.SESSION_PARAMETER,
         }
 
-        COMMANDS = tokens.Tokenizer.COMMANDS - {TokenType.SET, TokenType.SHOW}
+        COMMANDS = tokens.Tokenizer.COMMANDS - {TokenType.SHOW}
 
     class Parser(parser.Parser):
         FUNC_TOKENS = {*parser.Parser.FUNC_TOKENS, TokenType.SCHEMA, TokenType.DATABASE}  # type: ignore
@@ -211,7 +211,6 @@ class Parser(parser.Parser):
         STATEMENT_PARSERS = {
             **parser.Parser.STATEMENT_PARSERS,  # type: ignore
             TokenType.SHOW: lambda self: self._parse_show(),
-            TokenType.SET: lambda self: self._parse_set(),
         }
 
         SHOW_PARSERS = {
@@ -269,15 +268,12 @@ class Parser(parser.Parser):
         }
 
         SET_PARSERS = {
-            "GLOBAL": lambda self: self._parse_set_item_assignment("GLOBAL"),
+            **parser.Parser.SET_PARSERS,
             "PERSIST": lambda self: self._parse_set_item_assignment("PERSIST"),
             "PERSIST_ONLY": lambda self: self._parse_set_item_assignment("PERSIST_ONLY"),
-            "SESSION": lambda self: self._parse_set_item_assignment("SESSION"),
-            "LOCAL": lambda self: self._parse_set_item_assignment("LOCAL"),
             "CHARACTER SET": lambda self: self._parse_set_item_charset("CHARACTER SET"),
             "CHARSET": lambda self: self._parse_set_item_charset("CHARACTER SET"),
             "NAMES": lambda self: self._parse_set_item_names(),
-            "TRANSACTION": lambda self: self._parse_set_transaction(),
         }
 
         PROFILE_TYPES = {
@@ -292,15 +288,6 @@ class Parser(parser.Parser):
             "SWAPS",
         }
 
-        TRANSACTION_CHARACTERISTICS = {
-            "ISOLATION LEVEL REPEATABLE READ",
-            "ISOLATION LEVEL READ COMMITTED",
-            "ISOLATION LEVEL READ UNCOMMITTED",
-            "ISOLATION LEVEL SERIALIZABLE",
-            "READ WRITE",
-            "READ ONLY",
-        }
-
         def _parse_show_mysql(self, this, target=False, full=None, global_=None):
             if target:
                 if isinstance(target, str):
@@ -354,12 +341,6 @@ def _parse_show_mysql(self, this, target=False, full=None, global_=None):
                 **{"global": global_},
             )
 
-        def _parse_var_from_options(self, options):
-            for option in options:
-                if self._match_text_seq(*option.split(" ")):
-                    return exp.Var(this=option)
-            return None
-
         def _parse_oldstyle_limit(self):
             limit = None
             offset = None
@@ -372,30 +353,6 @@ def _parse_oldstyle_limit(self):
                     offset = parts[0]
             return offset, limit
 
-        def _default_parse_set_item(self):
-            return self._parse_set_item_assignment(kind=None)
-
-        def _parse_set_item_assignment(self, kind):
-            if kind in {"GLOBAL", "SESSION"} and self._match_text_seq("TRANSACTION"):
-                return self._parse_set_transaction(global_=kind == "GLOBAL")
-
-            left = self._parse_primary() or self._parse_id_var()
-            if not self._match(TokenType.EQ):
-                self.raise_error("Expected =")
-            right = self._parse_statement() or self._parse_id_var()
-
-            this = self.expression(
-                exp.EQ,
-                this=left,
-                expression=right,
-            )
-
-            return self.expression(
-                exp.SetItem,
-                this=this,
-                kind=kind,
-            )
-
         def _parse_set_item_charset(self, kind):
             this = self._parse_string() or self._parse_id_var()
 
@@ -418,18 +375,6 @@ def _parse_set_item_names(self):
                 kind="NAMES",
             )
 
-        def _parse_set_transaction(self, global_=False):
-            self._match_text_seq("TRANSACTION")
-            characteristics = self._parse_csv(
-                lambda: self._parse_var_from_options(self.TRANSACTION_CHARACTERISTICS)
-            )
-            return self.expression(
-                exp.SetItem,
-                expressions=characteristics,
-                kind="TRANSACTION",
-                **{"global": global_},
-            )
-
     class Generator(generator.Generator):
         LOCKING_READS_SUPPORTED = True
         NULL_ORDERING_SUPPORTED = False
@@ -523,16 +468,3 @@ def _oldstyle_limit_sql(self, expression):
                 limit_offset = f"{offset}, {limit}" if offset else limit
                 return f" LIMIT {limit_offset}"
             return ""
-
-        def setitem_sql(self, expression):
-            kind = self.sql(expression, "kind")
-            kind = f"{kind} " if kind else ""
-            this = self.sql(expression, "this")
-            expressions = self.expressions(expression)
-            collate = self.sql(expression, "collate")
-            collate = f" COLLATE {collate}" if collate else ""
-            global_ = "GLOBAL " if expression.args.get("global") else ""
-            return f"{global_}{kind}{this}{expressions}{collate}"
-
-        def set_sql(self, expression):
-            return f"SET {self.expressions(expression)}"
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index e08505c456..78f270cf37 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -1176,6 +1176,19 @@ def offset_sql(self, expression: exp.Offset) -> str:
         this = self.sql(expression, "this")
         return f"{this}{self.seg('OFFSET')} {self.sql(expression, 'expression')}"
 
+    def setitem_sql(self, expression: exp.SetItem) -> str:
+        kind = self.sql(expression, "kind")
+        kind = f"{kind} " if kind else ""
+        this = self.sql(expression, "this")
+        expressions = self.expressions(expression)
+        collate = self.sql(expression, "collate")
+        collate = f" COLLATE {collate}" if collate else ""
+        global_ = "GLOBAL " if expression.args.get("global") else ""
+        return f"{global_}{kind}{this}{expressions}{collate}"
+
+    def set_sql(self, expression: exp.Set) -> str:
+        return f"SET {self.expressions(expression)}"
+
     def lock_sql(self, expression: exp.Lock) -> str:
         if self.LOCKING_READS_SUPPORTED:
             lock_type = "UPDATE" if expression.args["update"] else "SHARE"
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index 8725b90b7d..b2cbea08fd 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -460,6 +460,7 @@ class Parser(metaclass=_Parser):
         TokenType.LOAD_DATA: lambda self: self._parse_load_data(),
         TokenType.MERGE: lambda self: self._parse_merge(),
         TokenType.ROLLBACK: lambda self: self._parse_commit_or_rollback(),
+        TokenType.SET: lambda self: self._parse_set(),
         TokenType.UNCACHE: lambda self: self._parse_uncache(),
         TokenType.UPDATE: lambda self: self._parse_update(),
         TokenType.USE: lambda self: self.expression(
@@ -684,13 +685,28 @@ class Parser(metaclass=_Parser):
         "sample": lambda self: self._parse_table_sample(as_modifier=True),
     }
 
+    SET_PARSERS = {
+        "GLOBAL": lambda self: self._parse_set_item_assignment("GLOBAL"),
+        "LOCAL": lambda self: self._parse_set_item_assignment("LOCAL"),
+        "SESSION": lambda self: self._parse_set_item_assignment("SESSION"),
+        "TRANSACTION": lambda self: self._parse_set_transaction(),
+    }
+
     SHOW_PARSERS: t.Dict[str, t.Callable] = {}
-    SET_PARSERS: t.Dict[str, t.Callable] = {}
 
     MODIFIABLES = (exp.Subquery, exp.Subqueryable, exp.Table)
 
     TRANSACTION_KIND = {"DEFERRED", "IMMEDIATE", "EXCLUSIVE"}
 
+    TRANSACTION_CHARACTERISTICS = {
+        "ISOLATION LEVEL REPEATABLE READ",
+        "ISOLATION LEVEL READ COMMITTED",
+        "ISOLATION LEVEL READ UNCOMMITTED",
+        "ISOLATION LEVEL SERIALIZABLE",
+        "READ WRITE",
+        "READ ONLY",
+    }
+
     INSERT_ALTERNATIVES = {"ABORT", "FAIL", "IGNORE", "REPLACE", "ROLLBACK"}
 
     WINDOW_ALIAS_TOKENS = ID_VAR_TOKENS - {TokenType.ROWS}
@@ -3788,9 +3804,40 @@ def _parse_show(self) -> t.Optional[exp.Expression]:
         return self.expression(exp.Show, this=self._prev.text.upper())
 
     def _default_parse_set_item(self) -> exp.Expression:
+        return self._parse_set_item_assignment(kind=None)
+
+    def _parse_set_item_assignment(self, kind: t.Optional[str] = None) -> exp.Expression:
+        if kind in {"GLOBAL", "SESSION"} and self._match_text_seq("TRANSACTION"):
+            return self._parse_set_transaction(global_=kind == "GLOBAL")
+
+        left = self._parse_primary() or self._parse_id_var()
+
+        if not self._match(TokenType.EQ):
+            self.raise_error("Expected =")
+
+        right = self._parse_statement() or self._parse_id_var()
+        this = self.expression(
+            exp.EQ,
+            this=left,
+            expression=right,
+        )
+
         return self.expression(
             exp.SetItem,
-            this=self._parse_statement(),
+            this=this,
+            kind=kind,
+        )
+
+    def _parse_set_transaction(self, global_: bool = False) -> exp.Expression:
+        self._match_text_seq("TRANSACTION")
+        characteristics = self._parse_csv(
+            lambda: self._parse_var_from_options(self.TRANSACTION_CHARACTERISTICS)
+        )
+        return self.expression(
+            exp.SetItem,
+            expressions=characteristics,
+            kind="TRANSACTION",
+            **{"global": global_},  # type: ignore
         )
 
     def _parse_set_item(self) -> t.Optional[exp.Expression]:
@@ -3864,7 +3911,18 @@ def _parse_merge(self) -> exp.Expression:
         )
 
     def _parse_set(self) -> exp.Expression:
-        return self.expression(exp.Set, expressions=self._parse_csv(self._parse_set_item))
+        index = self._index
+        try:
+            return self.expression(exp.Set, expressions=self._parse_csv(self._parse_set_item))
+        except ParseError:
+            self._retreat(index)
+            return self._parse_as_command(self._prev)
+
+    def _parse_var_from_options(self, options: t.Collection[str]) -> t.Optional[exp.Expression]:
+        for option in options:
+            if self._match_text_seq(*option.split(" ")):
+                return exp.Var(this=option)
+        return None
 
     def _parse_as_command(self, start: Token) -> exp.Command:
         while self._curr:
diff --git a/sqlglot/tokens.py b/sqlglot/tokens.py
index a513fc26f8..ee0f5853c7 100644
--- a/sqlglot/tokens.py
+++ b/sqlglot/tokens.py
@@ -726,7 +726,6 @@ class Tokenizer(metaclass=_Tokenizer):
         TokenType.COMMAND,
         TokenType.EXECUTE,
         TokenType.FETCH,
-        TokenType.SET,
         TokenType.SHOW,
     }
 
diff --git a/tests/fixtures/identity.sql b/tests/fixtures/identity.sql
index 380d945660..41fc020b96 100644
--- a/tests/fixtures/identity.sql
+++ b/tests/fixtures/identity.sql
@@ -161,6 +161,10 @@ CAST('2025-11-20 00:00:00+00' AS TIMESTAMP) AT TIME ZONE 'Africa/Cairo'
 SET x = 1
 SET -v
 SET x = ';'
+SET variable = value
+SET GLOBAL variable = value
+SET LOCAL variable = value
+SET @user OFF
 COMMIT
 USE db
 USE role x
diff --git a/tests/test_parser.py b/tests/test_parser.py
index dbde437fa9..93b80e3a16 100644
--- a/tests/test_parser.py
+++ b/tests/test_parser.py
@@ -280,6 +280,29 @@ def test_type_literals(self):
         self.assertIsInstance(parse_one("TIMESTAMP()"), exp.Func)
         self.assertIsInstance(parse_one("map.x"), exp.Column)
 
+    def test_set_expression(self):
+        set_session = parse_one("SET SESSION x = 1")
+
+        self.assertEqual(set_session.sql(), "SET SESSION x = 1")
+        self.assertIsInstance(set_session, exp.Set)
+
+        set_item = set_session.expressions[0]
+
+        self.assertIsInstance(set_item, exp.SetItem)
+        self.assertIsInstance(set_item.this, exp.EQ)
+        self.assertIsInstance(set_item.this.this, exp.Identifier)
+        self.assertIsInstance(set_item.this.expression, exp.Literal)
+
+        self.assertEqual(set_item.args.get("kind"), "SESSION")
+
+        set_as_command = parse_one("SET DEFAULT ROLE ALL TO USER")
+
+        self.assertEqual(set_as_command.sql(), "SET DEFAULT ROLE ALL TO USER")
+
+        self.assertIsInstance(set_as_command, exp.Command)
+        self.assertEqual(set_as_command.this, "SET")
+        self.assertEqual(set_as_command.expression, " DEFAULT ROLE ALL TO USER")
+
     def test_pretty_config_override(self):
         self.assertEqual(parse_one("SELECT col FROM x").sql(), "SELECT col FROM x")
         with patch("sqlglot.pretty", True):


Code review comments:
diff_hunk: @@ -3864,7 +3911,18 @@ def _parse_merge(self) -> exp.Expression:
         )
 
     def _parse_set(self) -> exp.Expression:
-        return self.expression(exp.Set, expressions=self._parse_csv(self._parse_set_item))
+        index = self._index
+        try:
+            return self.expression(exp.Set, expressions=self._parse_csv(self._parse_set_item))
+        except ParseError:
path: sqlglot/parser.py
text: @user1:
This could lead to bugs that are hard to debug, as we silence the error.

@author:
Agreed, I don't feel very good about this too. Maybe we should get rid of `_parse_set_item` and `_default_parse_set_Item` in order to inline their logic in `_parse_set`. Also, `_parse_set_item_assignment` can return `None` on failure, so we can gracefully handle it, e.g. if `TokenType.EQ` is not matched. How does that sound?

@user2:
you should definitely not use try catch . if there are any tokens left after a set, it’s failed

@author:
> if there are any tokens left after a set, it’s failed

Not necessarily, since spark [supports](https://spark.apache.org/docs/3.0.0-preview/sql-ref-syntax-aux-conf-mgmt-set.html#examples) `SET;`. Agreed with the comments regarding try-catch though, I will try to remove that.

@user2:
how is set; different?

@user2:
if you parse set; there are no tokens left, just check for self._curr and there are none so you don’t raise 

@author:
Ignore me, I misread your comment and got confused, you're right.

@author:
Yep, I realized what you mean now -- thanks for the suggestion

@author:
Got rid of try / catch.