failed to analyze: INVALID_ARGUMENT: Type not found: INTEGER

[DavitSo]

Tables are created successfully but query gives Error for the following script
MERGE transaction_raw_dev.tradeTable USING UNNEST([struct<orderChainUuid STRING, userId INTEGER, ...

[goccy]

It's difficult to investigate with this information, so please provide a repro code

[goccy]

Closed due to no response

[DavitSo]

Sorry for the late response, here is the example of SQL script. I just run the bigquery.dataset(datasetId).query(query)

query = MERGE testDataset.testTable
USING UNNEST([struct<orderUuid STRING, userId INTEGER> ('260e3d0e-0a29-40a2-02ab-111111110a19', 10)]) TempTable ON testTable .orderUuid = TempTable.orderUuid
WHEN NOT MATCHED THEN INSERT (orderUuid,userId) VALUES (TempTable.orderUuid, TempTable.userId)
WHEN MATCHED THEN UPDATE SET testTable.orderUuid = TempTable.orderUuid, testTable .userId = TempTable.userId;

I just run the above query
bigquery.dataset(datasetId).query(query)

[DavitSo]

After renaming INTEGER to INT64 in the schema I'm getting the following error
MERGE expression is supported equal expression only
for the same script

[DavitSo]

Hi @goccy any idea ?
Thanks

[DavitSo]

here is code for reprodusing
const {BigQuery} = require('@google-cloud/bigquery');
const orderSchemaForEmulator = [
{name: 'orderUuid', type: 'STRING', mode: 'REQUIRED'},
{name: 'userId', type: 'INT64', mode: 'REQUIRED'},
];

const bigQueryConfig = {
projectId: 'test',
datasetId: 'testDataset',
orderTableId: 'testTable',
};

const bigquery = new BigQuery({projectId: bigQueryConfig.projectId, apiEndpoint: 'http://localhost:9050'});
const dataset = bigquery.dataset(bigQueryConfig.datasetId);

let query = MERGE ${bigQueryConfig.datasetId}.${bigQueryConfig.orderTableId} ;
query += USING UNNEST([struct<;
query += orderUuid STRING, userId INT64> ( +
'260e3d0e-0a29-40a2-02ab-111111020a19', 10)]) TempTable ON testTable .orderUuid = TempTable.orderUuid +
WHEN NOT MATCHED THEN INSERT (orderUuid,userId) VALUES (TempTable.orderUuid, TempTable.userId) WHEN MATCHED +
THEN UPDATE SET testTable.orderUuid = TempTable.orderUuid, testTable .userId = TempTable.userId;

/**
*

• @return {Promise}
  */
  async function test() {
  try {
  await dataset.table(bigQueryConfig.orderTableId).delete();
  } catch (error) {
  console.log(Error: ${error.message});
  }
  await dataset.createTable(bigQueryConfig.orderTableId, {schema: orderSchemaForEmulator});
  await dataset.query(query);
  }
  test();

node version - 16
emulator version is 0.2.10

[nealrushforthlbg]

@goccy any reason this is closed as I am getting the same error on a MERGE statement

[nealrushforthlbg]

@goccy any update as we are having similar issue executing a MERGE statement. The error we get is - "500 currently MERGE expression is supported equal expression only".

Any pointers on what this is trying to tell me?

Our setup is that we are trying to use BQEmulator with dbt. We can connect to BQ Emulator ok, but when the MERGE command is issued, we get the error mentioned above, however when it runs against BQ on GCP it works fine.