search

goccy / go-zetasqlite

A database driver library that interprets ZetaSQL queries and runs them using SQLite3
MIT License
56 stars 29 forks source link

Fixing MERGE to use named paths in table names #223

Open bony2023 opened 2 months ago

bony2023 commented 2 months ago

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

The sub-actions (INSERT, UPDATE and DELETE) inside the merge statement currently do not work when using BigQuery Emulator, as they are not able to recognize the exact table they need to work upon.

This PR fixes the logic by adding namePath to the source and target table names joined by underscore to work on sqlite tables.

n10l commented 2 months ago

Hi @bony2023, Thanks for your changes. Even though the unit tests are passing, still its not generating expected query including project id and dataset with emulator. Its sending query without project id and dataset in emulator and raising same error: no such table: . Even I tried with same query as in tests as well.

bony2023 commented 2 months ago

I believe it's because the emulator always pass the datasetID as empty when executing a Query.

I'll see if I can fix it here directly.

n10l commented 2 months ago

Thanks @bony2023 for all the help.

n10l commented 2 months ago

Thanks @bony2023 for your last message. You are right and I confirm, by hard coding namePath[1] to my dataset name in your changes in this PR, everything works as expected in local emulator 🎉🎉. I am not sure if fixing this right way is possible in this lib, but thanks for being the savior here. I am new to BQ as well as Go, and was trying to figure this out for last two days, and you saved me here.

bony2023 commented 2 months ago

I just pushed a commit to extract datasetID if it's not provided in namePath from the emulator.

n10l commented 2 months ago

@bony2023 Sorry for delay in responding. Your latest commit works very well. Thank you so much. 🎉🎉🎉🎉🎉