Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

INSERT ROW part of MERGE statement dont seem to be supported #163

Open
sbv-csis opened this issue Mar 8, 2023 · 2 comments · May be fixed by goccy/go-zetasqlite#222
Open

INSERT ROW part of MERGE statement dont seem to be supported #163

sbv-csis opened this issue Mar 8, 2023 · 2 comments · May be fixed by goccy/go-zetasqlite#222

Comments

@sbv-csis
Copy link

sbv-csis commented Mar 8, 2023

When using the INSERT ROW part of merge it seems to fail - when trying to use https://cloud.google.com/bigquery/docs/reference/standard-sql/dml-syntax#omitting_column_names_2

bq --api http://127.0.0.1:9050 query "MERGE test-dataset-bla.t1 t using (select * from test-dataset-bla.t2) s on s.name = t.name when not matched by target then insert row when not matched by source then delete"
BigQuery error in query operation: Error processing job 'test-project-bla:bqjob_r554b87e1dbc3244d_00000186c0a89d32_1': failed to analyze:
INVALID_ARGUMENT: Missing insert column list [at 1:124]

as far as I can read zetasql should support it - though with some kind of feature flag FEATURE_V_1_3_OMIT_INSERT_COLUMN_LIST

And when trying with explict select and values lists it still fails:

E0308 11:06:03.384493 140062286534464 bq_utils.py:245] BigQuery error in query operation: Error processing job 'test-project-bla:bqjob_r33ab615fd2153cbd_00000186c0af5c11_1': failed to exec merge
statement CREATE TABLE zetasqlite_merged_table AS SELECT DISTINCT * FROM (SELECT * FROM SELECT `name#2` FROM (SELECT `name` AS `name#2` FROM
`test-project-bla_test-dataset-bla_t2`) LEFT JOIN (SELECT `name` AS `name#1` FROM `test-project-bla_test-dataset-bla_t1`) ON
zetasqlite_equal(`name#2`,`name#1`) UNION ALL SELECT * FROM (SELECT `name` AS `name#1` FROM `test-project-bla_test-dataset-bla_t1`) LEFT
JOIN SELECT `name#2` FROM (SELECT `name` AS `name#2` FROM `test-project-bla_test-dataset-bla_t2`) ON zetasqlite_equal(`name#2`,`name#1`)):
near "SELECT": syntax error
Traceback (most recent call last):
  File "/home/sbv/google-cloud-sdk/platform/bq/bq.py", line 856, in RunSafely
    return_value = self.RunWithArgs(*args, **kwds)
  File "/home/sbv/google-cloud-sdk/platform/bq/bq.py", line 2144, in RunWithArgs
    job = client.Query(query, **kwds)
  File "/home/sbv/google-cloud-sdk/platform/bq/bigquery_client.py", line 6563, in Query
    return self.ExecuteJob(request, **kwds)
  File "/home/sbv/google-cloud-sdk/platform/bq/bigquery_client.py", line 6046, in ExecuteJob
    job = self.RunJobSynchronously(
  File "/home/sbv/google-cloud-sdk/platform/bq/bigquery_client.py", line 6032, in RunJobSynchronously
    return self.RaiseIfJobError(result)
  File "/home/sbv/google-cloud-sdk/platform/bq/bigquery_client.py", line 3286, in RaiseIfJobError
    raise BigqueryError.Create(
bigquery_client.BigqueryServiceError: Error processing job 'test-project-bla:bqjob_r33ab615fd2153cbd_00000186c0af5c11_1': failed to exec merge statement CREATE TABLE zetasqlite_merged_table AS SELECT DISTINCT * FROM (SELECT * FROM SELECT `name#2` FROM (SELECT `name` AS `name#2` FROM `test-project-bla_test-dataset-bla_t2`) LEFT JOIN (SELECT `name` AS `name#1` FROM `test-project-bla_test-dataset-bla_t1`) ON zetasqlite_equal(`name#2`,`name#1`) UNION ALL SELECT * FROM (SELECT `name` AS `name#1` FROM `test-project-bla_test-dataset-bla_t1`) LEFT JOIN SELECT `name#2` FROM (SELECT `name` AS `name#2` FROM `test-project-bla_test-dataset-bla_t2`) ON zetasqlite_equal(`name#2`,`name#1`)): near "SELECT": syntax error
BigQuery error in query operation: Error processing job 'test-project-bla:bqjob_r33ab615fd2153cbd_00000186c0af5c11_1': failed to exec merge
statement CREATE TABLE zetasqlite_merged_table AS SELECT DISTINCT * FROM (SELECT * FROM SELECT `name#2` FROM (SELECT `name` AS `name#2` FROM
`test-project-bla_test-dataset-bla_t2`) LEFT JOIN (SELECT `name` AS `name#1` FROM `test-project-bla_test-dataset-bla_t1`) ON
zetasqlite_equal(`name#2`,`name#1`) UNION ALL SELECT * FROM (SELECT `name` AS `name#1` FROM `test-project-bla_test-dataset-bla_t1`) LEFT
JOIN SELECT `name#2` FROM (SELECT `name` AS `name#2` FROM `test-project-bla_test-dataset-bla_t2`) ON zetasqlite_equal(`name#2`,`name#1`)):
near "SELECT": syntax error
@sbv-csis
Copy link
Author

sbv-csis commented Mar 8, 2023 •
edited
Loading

And t1 and t2 are simple one column tables:

$ bq --api http://127.0.0.1:9050 --project_id=test-project-bla query "select * from test-dataset-bla.t2";
+------+
| name |
+------+
| n1   |
+------+
$  bq --api http://127.0.0.1:9050 --project_id=test-project-bla query "select * from test-dataset-bla.t1";

$

@bony2023 bony2023 linked a pull request Sep 15, 2024 that will close this issue
Open
@bony2023
Copy link

This should be fixed by this PR: goccy/go-zetasqlite#222
cc - @goccy

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

Making MERGE more robust in handling edge-cases bony2023/go-zetasqlite
2 participants
@bony2023 @sbv-csis