go-zetasqlite
go-zetasqlite copied to clipboard
Making MERGE more robust in handling edge-cases
Closes: https://github.com/goccy/bigquery-emulator/issues/163
The MERGE statement currently works fine but on a few edge cases:
- When the INSERT action inside MERGE uses
ROW
instead of specifying a column list, it raises the error. We can simply add the featureFeatureV13OmitInsertColumnList
to fix that.
/* ❌ Doesn't work currently because there's no column values given in INSERT */
MERGE target T USING source S ON T.id = S.id
WHEN NOT MATCHED THEN INSERT ROW;
- When the source table is evaluated using a SELECT expression rather than just a name, it raises an error. This is fixed by adding additional parenthesis around the table joins.
/* ❌ Doesn't work currently as source table is evaluated using a SELECT */
MERGE target T USING (SELECT * FROM source) S ON T.id = S.id
WHEN NOT MATCHED THEN INSERT (id, name) VALUES (id, name);
- When the target table name is a substring of source table name, it doesn't properly merge as it switches the target and source tables, because of this check.
/* ❌ Doesn't work currently as target table name is a substring of source table name */
MERGE target T USING target_tmp S ON T.id = S.id
WHEN NOT MATCHED THEN INSERT (id, name) VALUES (id, name);
This PR aims to fix all these issues while adding some test cases for basic merge statements.