Repository: tobymao/sqlglot
Pull request: fix: Fix COLLATE's RHS parsing
Fixes #3880

`COLLATE` should have a higher precedence than constructs such as `LIKE` but it's not possible to override it's parsing. For this reason, this PR unrolls `_parse_term` and transforms `COLLATE`'s RHS into an `exp.Var` (instead of `exp.Column`) if it's an unquoted identifier.

Problem statement:
T-SQL COLLATE argument being interpreted as a column
SQLGlot versions: tested with 25.9.0 and 25.8.1


When parsing a code block, I'm finding that the parsed query is returning the COLLATE collation argument as a column.

Example SQL:
```
select
    sql_text
    , some_id
from
    my_catalog.my_schema.my_table a
    inner join my_catalog.my_schema.my_other_table b
        on a.id = b.id
WHERE
    MySpecialColumn LIKE '%test%' COLLATE LATIN1_GENERAL_BIN

```

Given the above sql as variable "sql" - 

```
parsed_query = parse_one(sql=sql, dialect="tsql")
x = list(parsed_query.find_all(exp.Column))
for c in x:
    print(c)
```

I'm seeing "LATIN1_GENERAL_BIN" being emitted as a column, which surprised me. I wasn't sure if this was the desired behavior or not.

Hints from issue discussion:

Commit message: fix: Fix COLLATE's RHS parsing

Patch to review:
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index d43a916e33..56fda369e3 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -4360,7 +4360,25 @@ def _parse_bitwise(self) -> t.Optional[exp.Expression]:
         return this
 
     def _parse_term(self) -> t.Optional[exp.Expression]:
-        return self._parse_tokens(self._parse_factor, self.TERM)
+        this = self._parse_factor()
+
+        while self._match_set(self.TERM):
+            klass = self.TERM[self._prev.token_type]
+            comments = self._prev_comments
+            expression = self._parse_factor()
+
+            this = self.expression(klass, this=this, comments=comments, expression=expression)
+
+            if isinstance(this, exp.Collate):
+                expr = this.expression
+
+                # Preserve collations such as pg_catalog."default" (Postgres) as columns, otherwise
+                # fallback to Identifier / Var
+                if isinstance(expr, exp.Column) and len(expr.parts) == 1:
+                    ident = expr.this
+                    this.set("expression", ident if ident.quoted else exp.var(ident.name))
+
+        return this
 
     def _parse_factor(self) -> t.Optional[exp.Expression]:
         parse_method = self._parse_exponent if self.EXPONENT else self._parse_unary
diff --git a/tests/test_parser.py b/tests/test_parser.py
index f360b4311b..039631aed2 100644
--- a/tests/test_parser.py
+++ b/tests/test_parser.py
@@ -903,3 +903,18 @@ def test_trailing_comments(self):
 
     def test_parse_prop_eq(self):
         self.assertIsInstance(parse_one("x(a := b and c)").expressions[0], exp.PropertyEQ)
+
+    def test_collate(self):
+        collates = [
+            ('pg_catalog."default"', exp.Column),
+            ('"en_DE"', exp.Identifier),
+            ("LATIN1_GENERAL_BIN", exp.Var),
+            ("'en'", exp.Literal),
+        ]
+
+        for collate_pair in collates:
+            collate_node = parse_one(
+                f"""SELECT * FROM t WHERE foo LIKE '%bar%' COLLATE {collate_pair[0]}"""
+            ).find(exp.Collate)
+            self.assertIsInstance(collate_node, exp.Collate)
+            self.assertIsInstance(collate_node.expression, collate_pair[1])


Code review comments:
diff_hunk: @@ -4360,7 +4360,25 @@ def _parse_bitwise(self) -> t.Optional[exp.Expression]:
         return this
 
     def _parse_term(self) -> t.Optional[exp.Expression]:
-        return self._parse_tokens(self._parse_factor, self.TERM)
+        this = self._parse_factor()
+
+        while self._match_set(self.TERM):
+            klass = self.TERM[self._prev.token_type]
+            comments = self._prev_comments
+            expression = self._parse_factor()
+
+            this = self.expression(klass, this=this, comments=comments, expression=expression)
+
+            if isinstance(this, exp.Collate):
+                expr = this.expression
+
+                # Preserve collations such as pg_catalog."default" (Postgres) as columns, otherwise
+                # fallback to Identifier / Var
+                if isinstance(expr, exp.Column) and len(expr.parts) == 1:
+                    ident = expr.this
+                    this.set("expression", ident if ident.quoted else exp.var(ident.name))
path: sqlglot/parser.py
text: @user1:
Are you sure `Column.this` is always gonna be an identifier here? `ident.quoted` will fail if not.

@author:
Hmm, can we a parent `Column` without an `Identifier` child? Currently the RHS is always parsed either into a (1) `exp.Column(this=exp.Identifier())` or (2) a string `exp.Literal`, depending on the quotes.

@user1:
> Hmm, can we a parent Column without an Identifier child?

I'm not sure, I'd need to analyze the code paths in the parser to tell. Probably not, but if it's easy to be a bit more defensive here it'd be good. Your call.

@author:
I think we'll only create an `exp.Column` at `_parse_column_reference()`, and that's only if `this` is an `exp.Identifier`.

However, I'll go ahead and add one more branch just in case