Not possible to pass NULL argument value via ArrayQueryParameter

[xtrmstep]

Hi,

When attempting to pass None as a value to represent a NULL in a BigQuery stored procedure, the process fails with a NoneType object is not iterable error. The issue is observed while using google-cloud-bigquery==3.25.0.

Steps to Reproduce:

1. Initialize a BigQuery stored procedure that accepts NULL values in its arguments.
2. Use the following code to attempt passing a NULL value to the procedure:

   arg_values = None  # Representing NULL
   bigquery.ArrayQueryParameter("arg_name", "STRING", arg_values)

3. Execute the stored procedure from Python code.

Actual Behavior:

The procedure fails with the following error message:

'NoneType' object is not iterable
Error observed in the file: venv/Lib/site-packages/google/cloud/bigquery/query.py [line: 754]

Expected Behavior:

There should be a way to pass NULL to arguments. For example, None could be accepted as a valid input to represent NULL in the BigQuery procedure, allowing for proper execution without raising any exceptions.

Regards,

[suzmue]

@xtrmstep Thanks for filing an issue. It is possible to pass None as a value by passing it as an element of a list or an empty list. Does this not work for your use case? If not, could you please describe why you need to be able to pass NULL as argument itself?

arg_values = [None]
# arg_values = []
bigquery.ArrayQueryParameter("arg_name", "STRING", arg_values)

[xtrmstep]

Hi @suzmue,

I would like the column to have a NULL value, not just individual elements within the array. An empty array or a null element inside the array isn't the same as having the column itself set to NULL. This is necessary to differentiate between three states:

1. The value is not set.
2. The value is set but empty (an empty array also fits here).
3. The value is set and contains actual data.

It's possible to achieve this with SQL but not through the current parameters, which leads me to believe the implementation is incomplete.

Thank you!