Failes to exec merge statement UPDATE

[etrandafir93]

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