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.

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

Failes to exec merge statement UPDATE #299

Open
etrandafir93 opened this issue Apr 11, 2024 · 1 comment · May be fixed by goccy/go-zetasqlite#223
Open

Failes to exec merge statement UPDATE #299

etrandafir93 opened this issue Apr 11, 2024 · 1 comment · May be fixed by goccy/go-zetasqlite#223
Labels
bug Something isn't working

Comments

@etrandafir93
Copy link

What happened?

The emulator fails two merge tables, using the MERGE ... ON ... WHEN MATCHED THEN UPDATE SET syntax. Here is a simple example:

I have two tables test_table_3 and test_table_3_temp. I made sure they were successfully created by listing all the tables before continuing with the test. Here's the java client output:

GenericData{classInfo=[datasetId, projectId, tableId], {datasetId=test-dataset, projectId=test-project, tableId=test_table_3}}
GenericData{classInfo=[datasetId, projectId, tableId], {datasetId=test-dataset, projectId=test-project, tableId=test_table_3_temp}}

Then, I want to merge the two tables and update some of the fields for the matching rows. Here is a simple example of a query that can be run to reproduce this:

 MERGE `test-project.test-dataset.test_table_3` AS target 
 USING `test-project.test-dataset.test_table_3_temp` AS temp  
 ON target.firstName = temp.firstName  
 WHEN MATCHED 
 THEN UPDATE SET middleName = "abcd";

This query fails for a weird reason: no such table: test-project.test-dataset.test_table_3. Here's the full response:

com.google.api.client.googleapis.json.GoogleJsonResponseException: 400 Bad Request
POST http://127.0.0.1:32859/bigquery/v2/projects/test-project/queries
{
  "code": 400,
  "errors": [
    {
      "location": "",
      "message": "failed to exec merge statement UPDATE `test-project.test-dataset.test_table_3` SET `middleName`=\"eyJoZWFkZXIiOiJzdHJpbmciLCJib2R5IjoiYWJjZCJ9\" FROM zetasqlite_merged_table WHERE `firstName#4` = firstName AND `firstName#1` = firstName: no such table: test-project.test-dataset.test_table_3",
      "reason": "jobInternalError",
      "debugInfo": ""
    }
  ],
  "message": "failed to exec merge statement UPDATE `test-project.test-dataset.test_table_3` SET `middleName`=\"eyJoZWFkZXIiOiJzdHJpbmciLCJib2R5IjoiYWJjZCJ9\" FROM zetasqlite_merged_table WHERE `firstName#4` = firstName AND `firstName#1` = firstName: no such table: test-project.test-dataset.test_table_3"
}

Additionally, here is the container log:

ERROR   server/handler.go:1682  jobInternalError        {"error": "jobInternalError: failed to exec merge statement UPDATE `test-project.test-dataset.test_table_3` SET `middleName`=\"eyJoZWFkZXIiOiJzdHJpbmciLCJib2R5IjoiYWJjZCJ9\" FROM zetasqlite_merged_table WHERE `firstName#4` = firstName AND `firstName#1` = firstName: no such table: test-project.test-dataset.test_table_3"}

What did you expect to happen?

The query mentioned above works when it is executed against the Big Query service in Google Cloud, and it updates the main table for the matching records.

How can we reproduce it (as minimally and precisely as possible)?

  • create two tables: foo and foo-temp
  • execute the following SQL via a POST request:
 MERGE `test-project.test-dataset.foo` AS target 
 USING `test-project.test-dataset.foo-temp` AS temp  
 ON target.id= temp.id
 WHEN MATCHED 
 THEN UPDATE SET name = "xxxx";

Anything else we need to know?

I'm using the following image: ghcr.io/goccy/bigquery-emulator:0.4.3
and the big query java client com.google.cloud : google-cloud-bigquery

@etrandafir93 etrandafir93 added the bug Something isn't working label Apr 11, 2024
@etrandafir93 etrandafir93 changed the title Merge statement UPDATE fails Failes to exec merge statement UPDATE Apr 11, 2024
@bony2023
Copy link

Should be fixed by this PR: goccy/go-zetasqlite#223
cc - @goccy

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
bug Something isn't working
Projects
None yet
Development

Successfully merging a pull request may close this issue.

2 participants