Issues with making MERGE commands

Tang8330 commented 1 year ago

The SDK errors out and outputs an error:

sql: expected 4 arguments, got 0

However, this syntax is valid in BigQuery

MERGE INTO
  mock.customers_robin c
USING
  (
  SELECT
    1001 AS id,
    'Sally' AS first_name,
    'Thomas' AS last_name,
    'sally.thomas@acme.com' AS email,
    FALSE AS __artie_delete
  UNION ALL
  SELECT
    1002,
    'George',
    'Bailey',
    'gbailey@foobar.com',
    FALSE
  UNION ALL
  SELECT
    1003,
    'Edward',
    'Walker',
    'ed@walker.com',
    FALSE
  UNION ALL
  SELECT
    1004,
    'Anne',
    'Kretchmar',
    'annek@noanswer.org',
    FALSE) AS cc
ON
  c.id = cc.id
  WHEN MATCHED AND cc.__artie_delete THEN DELETE
  WHEN MATCHED
  AND IFNULL(cc.__artie_delete, FALSE) = FALSE THEN
UPDATE
SET
  id = cc.id,
  first_name = cc.first_name,
  last_name = cc.last_name,
  email = cc.email
  WHEN NOT MATCHED
  AND IFNULL(cc.__artie_delete, FALSE) = FALSE THEN
INSERT
  ( id,
    first_name,
    last_name,
    email )
VALUES
  ( cc.id,cc.first_name,cc.last_name,cc.email );

Tang8330 commented 1 year ago

I think this lib is bugging out because it doesn't like the temporary table.

Reason why it says 4 positional args is because there's 4 rows in the temp table. I just tried to update one row and I got the error for one positional arg.

See the official BQ docs on WITH - https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#roster_table

adranwit commented 1 year ago

Thanks for raising issue, it should be patched, the issue was in checkQueryParameters (as commented we had very basic way detecting binding parameters, improved it a little bit), expected 4 arguments came from false-positive binding parameter detection as '?' or @, it has nothing to do with temp table usage.

Tang8330 commented 1 year ago

Awesome. Thanks for the quick fix.

On Sat, Jan 28, 2023, 6:48 PM adranwit @.***> wrote:

Thanks for raising issue, it should be patched,

— Reply to this email directly, view it on GitHub https://github.com/viant/bigquery/issues/5#issuecomment-1407544288, or unsubscribe https://github.com/notifications/unsubscribe-auth/ABBVGKDF6UKVWW3OK3TVFH3WUXK7HANCNFSM6AAAAAAUJY3QLM . You are receiving this because you authored the thread.Message ID: @.***>

viant / bigquery

Issues with making MERGE commands #5