Cannot parse tsql merge statement inside parentheses

[AndysonEjvind]

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))]))]

[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

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.