When I add a condition to the WHERE of a query in which I pass a string parameter with a null value as follows:
@parameter IS NULL OR @parameter = 'target text'
Throws the following error:
failed to analyze: INVALID_ARGUMENT: Undeclared parameter 'parameter' is used assuming different types (STRING vs INT64)
What did you expect to happen?
The clause must be satisfied since the parameter is null and meets the condition @parameter IS NULL and query must return results without throwing any error.
How can we reproduce it (as minimally and precisely as possible)?
Instantiate a bigquery client, add the parameter and run the query:
final String query= "SELECT * FROM `my-table` WHERE @parameter IS NULL OR 'target text' = @parameter";
QueryJobConfiguration.Builder queryConfigBuilder = QueryJobConfiguration.newBuilder(query);
QueryJobConfiguration queryConfig = queryConfigBuilder.build();
...
QueryParameterValue parameter = QueryParameterValue.of(null, StandardSQLTypeName.STRING);
queryConfigBuilder.addNamedParameter("parameter", parameter);
...
bigQuery.query(queryConfig);
ohaibbq
commented
6 months ago
What happened?
When I add a condition to the WHERE of a query in which I pass a string parameter with a null value as follows:
@parameter IS NULL OR @parameter = 'target text'Throws the following error:
failed to analyze: INVALID_ARGUMENT: Undeclared parameter 'parameter' is used assuming different types (STRING vs INT64)What did you expect to happen?
The clause must be satisfied since the parameter is null and meets the condition
@parameter IS NULLand query must return results without throwing any error.How can we reproduce it (as minimally and precisely as possible)?
Instantiate a bigquery client, add the parameter and run the query:
Anything else we need to know?
No response