Repository: tobymao/sqlglot

Issue:
Cannot parse tsql merge statement inside parentheses
**Before you file an issue**
- dialect: `sg.parse(sql, read="tsql")`
- this problem occurs while parsing not writing

**Code snippet:**

import sqlglot as sg
lol = sg.parse("""
SELECT * FROM (MERGE Production.ProductInventory AS tgt
USING (
    SELECT ProductID,
        SUM(OrderQty)
    FROM Sales.SalesOrderDetail AS sod
    INNER JOIN Sales.SalesOrderHeader AS soh
        ON sod.SalesOrderID = soh.SalesOrderID
            AND soh.OrderDate = @OrderDate
    GROUP BY ProductID
    ) AS src(ProductID, OrderQty)
    ON (tgt.ProductID = src.ProductID)
WHEN MATCHED
    AND tgt.Quantity - src.OrderQty <= 0
    THEN
        DELETE
WHEN MATCHED
    THEN
        UPDATE
        SET tgt.Quantity = tgt.Quantity - src.OrderQty,
            tgt.ModifiedDate = GETDATE()
OUTPUT $action,
    Inserted.ProductID,
    Inserted.Quantity,
    Inserted.ModifiedDate,
    Deleted.ProductID,
    Deleted.Quantity,
    Deleted.ModifiedDate);
""", read='tsql')
print(lol)

**This is the error:**

sqlglot.errors.ParseError: Expecting ). Line 2, Col: 32.

SELECT * FROM (MERGE Production.ProductInventory AS tgt
USING (
    SELECT ProductID,
        SUM(OrderQty)
    FROM Sales.SalesOrde

**Expected output:**

AST like object

e.g.:

[Merge(
  this=Table(
    this=Identifier(this=ProductInventory, quoted=False),
    db=Identifier(this=Production, quoted=False),
    alias=TableAlias(
      this=Identifier(this=tgt, quoted=False))),
  using=Subquery(
    this=Select(
      expressions=[
        Column(
          this=Identifier(this=ProductID, quoted=False)),
        Sum(
          this=Column(
            this=Identifier(this=OrderQty, quoted=False)))],
      from=From(
        this=Table(
          this=Identifier(this=SalesOrderDetail, quoted=False),
          db=Identifier(this=Sales, quoted=False),
          alias=TableAlias(
            this=Identifier(this=sod, quoted=False)))),
      joins=[
        Join(
          this=Table(
            this=Identifier(this=SalesOrderHeader, quoted=False),
            db=Identifier(this=Sales, quoted=False),
            alias=TableAlias(
              this=Identifier(this=soh, quoted=False))),
          kind=INNER,
          on=And(
            this=EQ(
              this=Column(
                this=Identifier(this=SalesOrderID, quoted=False),
                table=Identifier(this=sod, quoted=False)),
              expression=Column(
                this=Identifier(this=SalesOrderID, quoted=False),
                table=Identifier(this=soh, quoted=False))),
            expression=EQ(
              this=Column(
                this=Identifier(this=OrderDate, quoted=False),
                table=Identifier(this=soh, quoted=False)),
              expression=Parameter(
                this=Var(this=OrderDate)))))],
      group=Group(
        expressions=[
          Column(
            this=Identifier(this=ProductID, quoted=False))])),
    alias=TableAlias(
      this=Identifier(this=src, quoted=False),
      columns=[
        Identifier(this=ProductID, quoted=False),
        Identifier(this=OrderQty, quoted=False)])),
  on=Paren(
    this=EQ(
      this=Column(
        this=Identifier(this=ProductID, quoted=False),
        table=Identifier(this=tgt, quoted=False)),
      expression=Column(
        this=Identifier(this=ProductID, quoted=False),
        table=Identifier(this=src, quoted=False)))),
  whens=Whens(
    expressions=[
      When(
        matched=True,
        condition=LTE(
          this=Sub(
            this=Column(
              this=Identifier(this=Quantity, quoted=False),
              table=Identifier(this=tgt, quoted=False)),
            expression=Column(
              this=Identifier(this=OrderQty, quoted=False),
              table=Identifier(this=src, quoted=False))),
          expression=Literal(this=0, is_string=False)),
        then=Var(this=DELETE)),
      When(
        matched=True,
        then=Update(
          expressions=[
            EQ(
              this=Column(
                this=Identifier(this=Quantity, quoted=False),
                table=Identifier(this=tgt, quoted=False)),
              expression=Sub(
                this=Column(
                  this=Identifier(this=Quantity, quoted=False),
                  table=Identifier(this=tgt, quoted=False)),
                expression=Column(
                  this=Identifier(this=OrderQty, quoted=False),
                  table=Identifier(this=src, quoted=False)))),
            EQ(
              this=Column(
                this=Identifier(this=ModifiedDate, quoted=False),
                table=Identifier(this=tgt, quoted=False)),
              expression=CurrentTimestamp())]))]),
  returning=Returning(
    expressions=[
      Column(
        this=Identifier(this=$action, quoted=False)),
      Column(
        this=Identifier(this=ProductID, quoted=False),
        table=Identifier(this=Inserted, quoted=False)),
      Column(
        this=Identifier(this=Quantity, quoted=False),
        table=Identifier(this=Inserted, quoted=False)),
      Column(
        this=Identifier(this=ModifiedDate, quoted=False),
        table=Identifier(this=Inserted, quoted=False)),
      Column(
        this=Identifier(this=ProductID, quoted=False),
        table=Identifier(this=Deleted, quoted=False)),
      Column(
        this=Identifier(this=Quantity, quoted=False),
        table=Identifier(this=Deleted, quoted=False)),
      Column(
        this=Identifier(this=ModifiedDate, quoted=False),
        table=Identifier(this=Deleted, quoted=False))]))]

Discussion:
@georgesittas: Can't find documentation that explains this use of `MERGE`, and a couple of tests I did in SQL Server resulted in syntax failures.

Closing as this doesn't seem valid & not enough details have been provided.@AndysonEjvind: @georgesittas

This is indeed valid tsql. You can see an example of inserting data into a table from the result of a merge statement:

https://learn.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-ver16#d-insert-the-results-of-the-merge-statement-into-another-table

```tsql
CREATE TABLE Production.UpdatedInventory (
    ProductID INT NOT NULL,
    LocationID INT,
    NewQty INT,
    PreviousQty INT,
    CONSTRAINT PK_Inventory PRIMARY KEY CLUSTERED (
        ProductID,
        LocationID
        )
    );
GO

INSERT INTO Production.UpdatedInventory
SELECT ProductID, LocationID, NewQty, PreviousQty
FROM (
    MERGE Production.ProductInventory AS pi
    USING (
        SELECT ProductID, SUM(OrderQty)
        FROM Sales.SalesOrderDetail AS sod
        INNER JOIN Sales.SalesOrderHeader AS soh
            ON sod.SalesOrderID = soh.SalesOrderID
                AND soh.OrderDate BETWEEN '20030701'
                    AND '20030731'
        GROUP BY ProductID
        ) AS src(ProductID, OrderQty)
        ON pi.ProductID = src.ProductID
    WHEN MATCHED
        AND pi.Quantity - src.OrderQty >= 0
        THEN
            UPDATE SET pi.Quantity = pi.Quantity - src.OrderQty
    WHEN MATCHED
        AND pi.Quantity - src.OrderQty <= 0
        THEN
            DELETE
    OUTPUT $action,
        Inserted.ProductID,
        Inserted.LocationID,
        Inserted.Quantity AS NewQty,
        Deleted.Quantity AS PreviousQty
    ) AS Changes(Action, ProductID, LocationID, NewQty, PreviousQty)
WHERE Action = 'UPDATE';
GO
```

this will fail in sqlglot@georgesittas: Hm ok, reopening and going to take another look.

Solution patch:
diff --git a/sqlglot/dialects/tsql.py b/sqlglot/dialects/tsql.py
index 63747bf246..b832471baf 100644
--- a/sqlglot/dialects/tsql.py
+++ b/sqlglot/dialects/tsql.py
@@ -633,6 +633,15 @@ class Parser(parser.Parser):
             else self.expression(exp.ScopeResolution, this=this, expression=to),
         }
 
+        def _parse_wrapped_select(self, table: bool = False) -> t.Optional[exp.Expression]:
+            if self._match(TokenType.MERGE):
+                comments = self._prev_comments
+                merge = self._parse_merge()
+                merge.add_comments(comments, prepend=True)
+                return merge
+
+            return super()._parse_wrapped_select(table=table)
+
         def _parse_dcolon(self) -> t.Optional[exp.Expression]:
             # We want to use _parse_types() if the first token after :: is a known type,
             # otherwise we could parse something like x::varchar(max) into a function
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index 199bf72a92..248a1287b0 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -3057,6 +3057,37 @@ def _parse_value_expression() -> t.Optional[exp.Expression]:
     def _parse_projections(self) -> t.List[exp.Expression]:
         return self._parse_expressions()
 
+    def _parse_wrapped_select(self, table: bool = False) -> t.Optional[exp.Expression]:
+        if self._match_set((TokenType.PIVOT, TokenType.UNPIVOT)):
+            this: t.Optional[exp.Expression] = self._parse_simplified_pivot(
+                is_unpivot=self._prev.token_type == TokenType.UNPIVOT
+            )
+        elif self._match(TokenType.FROM):
+            from_ = self._parse_from(skip_from_token=True)
+            # Support parentheses for duckdb FROM-first syntax
+            select = self._parse_select()
+            if select:
+                select.set("from", from_)
+                this = select
+            else:
+                this = exp.select("*").from_(t.cast(exp.From, from_))
+        else:
+            this = (
+                self._parse_table()
+                if table
+                else self._parse_select(nested=True, parse_set_operation=False)
+            )
+
+            # Transform exp.Values into a exp.Table to pass through parse_query_modifiers
+            # in case a modifier (e.g. join) is following
+            if table and isinstance(this, exp.Values) and this.alias:
+                alias = this.args["alias"].pop()
+                this = exp.Table(this=this, alias=alias)
+
+            this = self._parse_query_modifiers(self._parse_set_operations(this))
+
+        return this
+
     def _parse_select(
         self,
         nested: bool = False,
@@ -3140,38 +3171,11 @@ def _parse_select(
 
             this = self._parse_query_modifiers(this)
         elif (table or nested) and self._match(TokenType.L_PAREN):
-            if self._match_set((TokenType.PIVOT, TokenType.UNPIVOT)):
-                this = self._parse_simplified_pivot(
-                    is_unpivot=self._prev.token_type == TokenType.UNPIVOT
-                )
-            elif self._match(TokenType.FROM):
-                from_ = self._parse_from(skip_from_token=True)
-                # Support parentheses for duckdb FROM-first syntax
-                select = self._parse_select()
-                if select:
-                    select.set("from", from_)
-                    this = select
-                else:
-                    this = exp.select("*").from_(t.cast(exp.From, from_))
-            else:
-                this = (
-                    self._parse_table()
-                    if table
-                    else self._parse_select(nested=True, parse_set_operation=False)
-                )
-
-                # Transform exp.Values into a exp.Table to pass through parse_query_modifiers
-                # in case a modifier (e.g. join) is following
-                if table and isinstance(this, exp.Values) and this.alias:
-                    alias = this.args["alias"].pop()
-                    this = exp.Table(this=this, alias=alias)
-
-                this = self._parse_query_modifiers(self._parse_set_operations(this))
-
-            self._match_r_paren()
+            this = self._parse_wrapped_select(table=table)
 
             # We return early here so that the UNION isn't attached to the subquery by the
             # following call to _parse_set_operations, but instead becomes the parent node
+            self._match_r_paren()
             return self._parse_subquery(this, parse_alias=parse_subquery_alias)
         elif self._match(TokenType.VALUES, advance=False):
             this = self._parse_derived_table_values()


Test patch:
diff --git a/tests/dialects/test_tsql.py b/tests/dialects/test_tsql.py
index bafd4eca4f..55098d0680 100644
--- a/tests/dialects/test_tsql.py
+++ b/tests/dialects/test_tsql.py
@@ -850,6 +850,9 @@ def test_types_bin(self):
         )
 
     def test_ddl(self):
+        for colstore in ("NONCLUSTERED COLUMNSTORE", "CLUSTERED COLUMNSTORE"):
+            self.validate_identity(f"CREATE {colstore} INDEX index_name ON foo.bar")
+
         for view_attr in ("ENCRYPTION", "SCHEMABINDING", "VIEW_METADATA"):
             self.validate_identity(f"CREATE VIEW a.b WITH {view_attr} AS SELECT * FROM x")
 
@@ -871,19 +874,19 @@ def test_ddl(self):
 
         self.validate_identity("CREATE SCHEMA testSchema")
         self.validate_identity("CREATE VIEW t AS WITH cte AS (SELECT 1 AS c) SELECT c FROM cte")
+        self.validate_identity("ALTER TABLE tbl SET SYSTEM_VERSIONING=OFF")
+        self.validate_identity("ALTER TABLE tbl SET FILESTREAM_ON = 'test'")
+        self.validate_identity("ALTER TABLE tbl SET DATA_DELETION=ON")
+        self.validate_identity("ALTER TABLE tbl SET DATA_DELETION=OFF")
         self.validate_identity(
             "ALTER TABLE tbl SET SYSTEM_VERSIONING=ON(HISTORY_TABLE=db.tbl, DATA_CONSISTENCY_CHECK=OFF, HISTORY_RETENTION_PERIOD=5 DAYS)"
         )
         self.validate_identity(
             "ALTER TABLE tbl SET SYSTEM_VERSIONING=ON(HISTORY_TABLE=db.tbl, HISTORY_RETENTION_PERIOD=INFINITE)"
         )
-        self.validate_identity("ALTER TABLE tbl SET SYSTEM_VERSIONING=OFF")
-        self.validate_identity("ALTER TABLE tbl SET FILESTREAM_ON = 'test'")
         self.validate_identity(
             "ALTER TABLE tbl SET DATA_DELETION=ON(FILTER_COLUMN=col, RETENTION_PERIOD=5 MONTHS)"
         )
-        self.validate_identity("ALTER TABLE tbl SET DATA_DELETION=ON")
-        self.validate_identity("ALTER TABLE tbl SET DATA_DELETION=OFF")
 
         self.validate_identity("ALTER VIEW v AS SELECT a, b, c, d FROM foo")
         self.validate_identity("ALTER VIEW v AS SELECT * FROM foo WHERE c > 100")
@@ -899,10 +902,44 @@ def test_ddl(self):
             "ALTER VIEW v WITH VIEW_METADATA AS SELECT * FROM foo WHERE c > 100",
             check_command_warning=True,
         )
+        self.validate_identity(
+            "CREATE COLUMNSTORE INDEX index_name ON foo.bar",
+            "CREATE NONCLUSTERED COLUMNSTORE INDEX index_name ON foo.bar",
+        )
         self.validate_identity(
             "CREATE PROCEDURE foo AS BEGIN DELETE FROM bla WHERE foo < CURRENT_TIMESTAMP - 7 END",
             "CREATE PROCEDURE foo AS BEGIN DELETE FROM bla WHERE foo < GETDATE() - 7 END",
         )
+        self.validate_identity(
+            "INSERT INTO Production.UpdatedInventory SELECT ProductID, LocationID, NewQty, PreviousQty FROM (MERGE INTO Production.ProductInventory AS pi USING (SELECT ProductID, SUM(OrderQty) FROM Sales.SalesOrderDetail AS sod INNER JOIN Sales.SalesOrderHeader AS soh ON sod.SalesOrderID = soh.SalesOrderID AND soh.OrderDate BETWEEN '20030701' AND '20030731' GROUP BY ProductID) AS src(ProductID, OrderQty) ON pi.ProductID = src.ProductID WHEN MATCHED AND pi.Quantity - src.OrderQty >= 0 THEN UPDATE SET pi.Quantity = pi.Quantity - src.OrderQty WHEN MATCHED AND pi.Quantity - src.OrderQty <= 0 THEN DELETE OUTPUT $action, Inserted.ProductID, Inserted.LocationID, Inserted.Quantity AS NewQty, Deleted.Quantity AS PreviousQty) AS Changes(Action, ProductID, LocationID, NewQty, PreviousQty) WHERE Action = 'UPDATE'",
+            """INSERT INTO Production.UpdatedInventory
+SELECT
+  ProductID,
+  LocationID,
+  NewQty,
+  PreviousQty
+FROM (
+  MERGE INTO Production.ProductInventory AS pi
+  USING (
+    SELECT
+      ProductID,
+      SUM(OrderQty)
+    FROM Sales.SalesOrderDetail AS sod
+    INNER JOIN Sales.SalesOrderHeader AS soh
+      ON sod.SalesOrderID = soh.SalesOrderID
+      AND soh.OrderDate BETWEEN '20030701' AND '20030731'
+    GROUP BY
+      ProductID
+  ) AS src(ProductID, OrderQty)
+  ON pi.ProductID = src.ProductID
+  WHEN MATCHED AND pi.Quantity - src.OrderQty >= 0 THEN UPDATE SET pi.Quantity = pi.Quantity - src.OrderQty
+  WHEN MATCHED AND pi.Quantity - src.OrderQty <= 0 THEN DELETE
+  OUTPUT $action, Inserted.ProductID, Inserted.LocationID, Inserted.Quantity AS NewQty, Deleted.Quantity AS PreviousQty
+) AS Changes(Action, ProductID, LocationID, NewQty, PreviousQty)
+WHERE
+  Action = 'UPDATE'""",
+            pretty=True,
+        )
 
         self.validate_all(
             "CREATE TABLE [#temptest] (name INTEGER)",
@@ -1003,14 +1040,6 @@ def test_ddl(self):
             },
         )
 
-        for colstore in ("NONCLUSTERED COLUMNSTORE", "CLUSTERED COLUMNSTORE"):
-            self.validate_identity(f"CREATE {colstore} INDEX index_name ON foo.bar")
-
-        self.validate_identity(
-            "CREATE COLUMNSTORE INDEX index_name ON foo.bar",
-            "CREATE NONCLUSTERED COLUMNSTORE INDEX index_name ON foo.bar",
-        )
-
     def test_insert_cte(self):
         self.validate_all(
             "INSERT INTO foo.bar WITH cte AS (SELECT 1 AS one) SELECT * FROM cte",