Repository: tobymao/sqlglot

Issue:
`MATCH_CONDITION` not recognised in `ASOF JOIN` for Snowflake
**Is your feature request related to a problem? Please describe.**
Snowflake recently added [the `ASOF` join type](https://docs.snowflake.com/en/sql-reference/constructs/asof-join) which introduces [the `MATCH_CONDITION` keyword](https://docs.snowflake.com/en/sql-reference/constructs/asof-join#syntax) in the join:

```sql
SELECT ...
FROM <left_table> ASOF JOIN <right_table>
  MATCH_CONDITION (<left_table.timecol> <comparison_operator> <right_table.timecol>)
  [ ON <table.col> = <table.col> [ AND ... ] ]
```

However, this doesn't appear to be supported in SQLGlot yet (details below).

**Describe the solution you'd like**
SQLGlot to understand and parse the `MATCH_CONDITION` keyword when used as part of an `ASOF` join with the Snowflake dialect.

**Describe alternatives you've considered**
I don't think there are any alternatives, other than simply commenting out the `MATCH_CONDITION` line so that SQLGlot ignores it in the parsing 😄 

**Additional context**
I'll add an MWE here with corresponding screenshots -- I'm using `sqlglot==23.3.0` (with `sqlglotrs==0.1.3`).

Here's an example of a SQL query that runs fine in Snowflake:

```sql
WITH DATA(VAL) AS (
    SELECT *
    FROM VALUES (1), (2), (3)
)

SELECT *
FROM DATA AS DATA_L
    ASOF JOIN DATA AS DATA_R
        MATCH_CONDITION (DATA_L.VAL > DATA_R.VAL)
ORDER BY DATA_L.VAL
```

...and a screenshot to show that the result is printed as expected:

![image](https://github.com/tobymao/sqlglot/assets/25640332/997aa2a2-d004-4257-ad43-6c75315e74d5)

Here's a corresponding example of a Python module to parse this query:

```python
import sqlglot


SQL = """
WITH DATA(VAL) AS (
    SELECT *
    FROM VALUES (1), (2), (3)
)

SELECT *
FROM DATA AS DATA_L
    ASOF JOIN DATA AS DATA_R
        MATCH_CONDITION (DATA_L.VAL > DATA_R.VAL)
ORDER BY DATA_L.VAL
"""

print(sqlglot.parse_one(SQL, dialect="snowflake"))
```

...and a screenshot of the error I get when I run this:

![image](https://github.com/tobymao/sqlglot/assets/25640332/6c0ebc82-a651-4863-b03d-1c7b9febf18f)


Discussion:

Patch:
diff --git a/sqlglot/dialects/snowflake.py b/sqlglot/dialects/snowflake.py
index 1e94c05a..73a9166e 100644
--- a/sqlglot/dialects/snowflake.py
+++ b/sqlglot/dialects/snowflake.py
@@ -335,7 +335,13 @@ class Snowflake(Dialect):
     class Parser(parser.Parser):
         IDENTIFY_PIVOT_STRINGS = True
 
+        ID_VAR_TOKENS = {
+            *parser.Parser.ID_VAR_TOKENS,
+            TokenType.MATCH_CONDITION,
+        }
+
         TABLE_ALIAS_TOKENS = parser.Parser.TABLE_ALIAS_TOKENS | {TokenType.WINDOW}
+        TABLE_ALIAS_TOKENS.discard(TokenType.MATCH_CONDITION)
 
         FUNCTIONS = {
             **parser.Parser.FUNCTIONS,
@@ -698,6 +704,7 @@ class Snowflake(Dialect):
             "EXCLUDE": TokenType.EXCEPT,
             "ILIKE ANY": TokenType.ILIKE_ANY,
             "LIKE ANY": TokenType.LIKE_ANY,
+            "MATCH_CONDITION": TokenType.MATCH_CONDITION,
             "MATCH_RECOGNIZE": TokenType.MATCH_RECOGNIZE,
             "MINUS": TokenType.EXCEPT,
             "NCHAR VARYING": TokenType.VARCHAR,
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index 9f72c290..78cc9d50 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -2100,6 +2100,7 @@ class Join(Expression):
         "method": False,
         "global": False,
         "hint": False,
+        "match_condition": False,  # Snowflake
     }
 
     @property
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index 470fa422..ae4b1ccd 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -1873,6 +1873,8 @@ class Generator(metaclass=_Generator):
             )
             if op
         )
+        match_cond = self.sql(expression, "match_condition")
+        match_cond = f" MATCH_CONDITION ({match_cond})" if match_cond else ""
         on_sql = self.sql(expression, "on")
         using = expression.args.get("using")
 
@@ -1896,7 +1898,7 @@ class Generator(metaclass=_Generator):
             return f", {this_sql}"
 
         op_sql = f"{op_sql} JOIN" if op_sql else "JOIN"
-        return f"{self.seg(op_sql)} {this_sql}{on_sql}"
+        return f"{self.seg(op_sql)} {this_sql}{match_cond}{on_sql}"
 
     def lambda_sql(self, expression: exp.Lambda, arrow_sep: str = "->") -> str:
         args = self.expressions(expression, flat=True)
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index 474ba050..c8077ad3 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -2907,6 +2907,9 @@ class Parser(metaclass=_Parser):
         if hint:
             kwargs["hint"] = hint
 
+        if self._match(TokenType.MATCH_CONDITION):
+            kwargs["match_condition"] = self._parse_wrapped(self._parse_comparison)
+
         if self._match(TokenType.ON):
             kwargs["on"] = self._parse_conjunction()
         elif self._match(TokenType.USING):
diff --git a/sqlglot/tokens.py b/sqlglot/tokens.py
index f26511c2..1c5f8bc9 100644
--- a/sqlglot/tokens.py
+++ b/sqlglot/tokens.py
@@ -291,6 +291,7 @@ class TokenType(AutoName):
     LOAD = auto()
     LOCK = auto()
     MAP = auto()
+    MATCH_CONDITION = auto()
     MATCH_RECOGNIZE = auto()
     MEMBER_OF = auto()
     MERGE = auto()


Test patch:
diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py
index ad169e98..69c03bac 100644
--- a/tests/dialects/test_snowflake.py
+++ b/tests/dialects/test_snowflake.py
@@ -97,6 +97,10 @@ WHERE
         self.validate_identity(
             "SELECT a FROM test PIVOT(SUM(x) FOR y IN ('z', 'q')) AS x TABLESAMPLE (0.1)"
         )
+        self.validate_identity(
+            "SELECT * FROM DATA AS DATA_L ASOF JOIN DATA AS DATA_R MATCH_CONDITION (DATA_L.VAL > DATA_R.VAL) ON DATA_L.ID = DATA_R.ID"
+        )
+        self.validate_identity("SELECT MATCH_CONDITION")
         self.validate_identity(
             "SELECT a:from::STRING, a:from || ' test' ",
             "SELECT CAST(GET_PATH(a, 'from') AS TEXT), GET_PATH(a, 'from') || ' test'",