使用二级索引时的CAST函数转换异常问题

当强制使用<Type, F1>联合索引data_f1时，查询优化器的执行逻辑发生了变化：原本无索引时，会先过滤Type = 'DON'的行，再对这些行的F1执行CAST转换；但走索引时，优化器可能先对索引中所有行的F1执行CAST操作，再过滤Type条件，导致Type != 'DON'的行中那些无法转换为FLOAT64的字符串触发转换错误。

• 子查询过滤先行：先通过子查询筛选出Type = 'DON'的行，再在外层处理CAST和数值判断，确保只有符合类型条件的行才会执行转换：

SELECT cast(F1 as FLOAT64)
FROM (
    SELECT F1
    FROM DATA FORCE INDEX(data_f1)
    WHERE Type = 'DON'
) t
WHERE cast(F1 as FLOAT64) > 20

• CASE表达式限制转换范围：用CASE表达式仅在Type = 'DON'时执行CAST，其他情况返回NULL，避免无效转换：

SELECT cast(F1 as FLOAT64)
FROM DATA FORCE INDEX(data_f1)
WHERE Type = 'DON'
AND CASE WHEN Type = 'DON' THEN cast(F1 as FLOAT64) ELSE NULL END > 20

• 创建过滤型函数索引：针对Type = 'DON'的行，单独创建F1转换为FLOAT64后的索引，这样索引中仅包含有效转换的行，查询时不会触发错误（以SQL Server为例）：

CREATE INDEX idx_don_f1_float ON DATA (cast(F1 as FLOAT64))
WHERE Type = 'DON'

之后查询时强制使用该索引即可。

内容的提问来源于stack exchange，提问作者chang2023