[BUG]: Snowflake | Incorrect conversion of Update FROM statement

[bishwajit-db]

Incorrect conversion of the UPDATE FROM statement. Subqueries are included in the search condition of the MERGE operation. But sub-queries are not supported in the MERGE search condition.

The converted query throws DELTA_UNSUPPORTED_SUBQUERY error.

Source: Snowflake

Sample Query

UPDATE SCHEMA_A.TABLE_X TT
    SET TT.MAT_COD = NULL
    FROM SCHEMA_B.VW_TABLE_Y R_MAT
    WHERE R_MAT.MAT_COD = TT.MAT_COD
      AND R_MAT.MAT_TYP_COD NOT IN ('STR_A','STR_B','STR_C')
      AND R_MAT.SRC_SYS_COD = TT.SRC_SYS_COD
      AND TT.CTL_ARE_COD IN (
        SELECT CTL_ARE_COD
        FROM SCHEMA_C.TABLE_Z
        WHERE MAP_cODE = 'STR_D'
      )
      AND (pCTLARECOD IS NULL OR TT.CTL_ARE_COD = pCTLARECOD);

Current Output:

MERGE INTO
  SCHEMA_A.TABLE_X AS TT
USING
  SCHEMA_B.VW_TABLE_Y AS R_MAT
ON
  R_MAT.MAT_COD = TT.MAT_COD
  AND R_MAT.MAT_TYP_COD NOT IN ('STR_A','STR_B','STR_C')
  AND R_MAT.SRC_SYS_COD = TT.SRC_SYS_COD
  AND TT.CTL_ARE_COD IN (
    SELECT CTL_ARE_COD
    FROM SCHEMA_C.TABLE_Z
    WHERE MAP_cODE = 'STR_D'
  )
  AND (pCTLARECOD IS NULL OR TT.CTL_ARE_COD = pCTLARECOD)
WHEN MATCHED THEN UPDATE SET TT.MAT_COD = NULL;

Version

latest via Databricks CLI

[vil1]

These need to be flagged as unsupported, with a FIXME comment.