go-zetasqlite icon indicating copy to clipboard operation
go-zetasqlite copied to clipboard

Making MERGE more robust in handling edge-cases

Open bony2023 opened this issue 5 months ago • 10 comments

Closes: https://github.com/goccy/bigquery-emulator/issues/163

The MERGE statement currently works fine but on a few edge cases:

  1. When the INSERT action inside MERGE uses ROW instead of specifying a column list, it raises the error. We can simply add the feature FeatureV13OmitInsertColumnList 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;
  1. 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);
  1. 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.

bony2023 avatar Sep 15 '24 17:09 bony2023