IssueTracker

The customer is trying to use parameterized queries with Python Client Library for BigQuery and is receiving invalid results when the parameter is an array and its value is an empty array.

An unexpected behavior occurs and it may result in subtle bugs trivially introduced without being noticed, which can result in multiple issues downstream, up to and including data corruption and/or data loss during processing.

The issue also occurs with a small repro in Go language, it is likely language-independent. Here is a minimal reproduction in Python:

from google.cloud import bigquery

client = bigquery.Client(project='myproject')
query = 'SELECT ARRAY_LENGTH(@values_array) as array_len'
query_params = [bigquery.ArrayQueryParameter('values_array', 'STRING', [])]
job_config = bigquery.QueryJobConfig(query_parameters=query_params)
query_job = client.query(query, job_config=job_config)
results = list(query_job.result())
print('array_len: ' + str(results[0]['array_len']))

Actual output: array_len: None

Expected output: array_len: 0

Passing non-parameterized query works as expected: SELECT ARRAY_LENGTH([]) as array_len - the result is 0.

It behaves as if the parameter was passed as NULL (in which case ARRAY_LENGTH returns NULL).

The workaround is to use the COALESCE function - although it may be difficult for customers to come up with and remember to use it every time. The above query can be written as:

"SELECT ARRAY_LENGTH(COALESCE(@values_array, ARRAY<STRING>[])) as array_len"

The workaround is insufficient for the customer for reasons given above.