F1 is a String(MAX) column. I use this F1 column to store any data type and then parse the value based on the Type column's value. The following query executes fine.
select cast(F1 as FLOAT64) from DATA where Type = 'DON' and cast(F1 as FLOAT64) > 20
But when I force the use of index on F1(secondary index on <Type, F1>) as shown below, the query errors out.
select cast(F1 as FLOAT64) from DATA@{force_index = 'data_f1'} where Type = 'DON' and cast(F1 as FLOAT64) > 20
In this case it errors with: "Statement failed: Bad double value: 00e2e513-019a-44c7-88e9-8970e6c7..."
I have verified that when Type = 'DON' then all the values in F1 are always Floats stored as String values.
It seems like the where condition in the second query is evaluating "cast(F1 as FLOAT64) > 20" even for rows where Type != 'DON'.
Seems like an issue in using indexes and how the where conditions execute.
I tried to make sure all the values for F1 when Type = 'DON' are cast-able to FLOAT64 and my first query proves that it's true.
I was expecting same results as query 1 but a faster scan because of the index. But the query just errors out.
There is no guarantee of ordering between these AND conditions.
For your use case, consider using SAFE_CAST instead of CAST, which defaults to NULL if the cast fails.