Redshift中Between/范围过滤报错：单独>或<可用，组合失效

问题根源 #

Redshift的查询优化器会执行谓词下推优化：当你在外部WHERE子句中使用组合范围条件（比如sqft>200 AND sqft<100000或BETWEEN）时，优化器可能跳过CTE里的清洗逻辑，直接在原表mv_prop_attributes上尝试对value字段做数值转换和过滤。这就会碰到那些本来会被CTE排除的无效值（比如单独的.），触发Invalid digit错误。而单独使用单个范围条件时，优化器的下推逻辑不同，所以没触发报错。

另外注意：原CTE的WHERE子句里引用sqft is not null and length(sqft)>0是无效的——因为sqft是当前SELECT语句生成的字段，不能在同一个SELECT的WHERE中直接引用，这部分逻辑完全没起到作用，也是导致无效值漏网的原因之一。

可行解决方案 #

方案1：用NO_PUSHED_PREDICATES提示阻止下推 #

在CTE的SELECT语句前添加Redshift专属提示，强制优化器先执行完CTE内的所有清洗逻辑，再应用外部过滤条件：

WITH attributes AS (
    SELECT /*+ NO_PUSHED_PREDICATES */
        property_id,
        CASE 
            WHEN regexp_replace(value, '[^0-9.]', '') = '' THEN NULL
            WHEN regexp_replace(value, '[^0-9.]', '') = '.' THEN NULL
            ELSE regexp_replace(value, '[^0-9.]', '')::float
        END AS sqft, 
        'platform' AS source
    FROM mv_prop_attributes
    WHERE display_name = 'Livable Area'
)
SELECT *
FROM attributes
WHERE sqft IS NOT NULL
  AND sqft > 200
  AND sqft < 100000;

方案2：子查询加LIMIT ALL阻止下推 #

给子查询末尾加上LIMIT ALL（不影响结果，但会让优化器放弃谓词下推），同样能确保先完成数据清洗再过滤：

SELECT *
FROM (
    SELECT
        property_id,
        CASE 
            WHEN regexp_replace(value, '[^0-9.]', '') = '' THEN NULL
            WHEN regexp_replace(value, '[^0-9.]', '') = '.' THEN NULL
            ELSE regexp_replace(value, '[^0-9.]', '')::float
        END AS sqft, 
        'platform' AS source
    FROM mv_prop_attributes
    WHERE display_name = 'Livable Area'
    LIMIT ALL
) AS attributes
WHERE sqft IS NOT NULL
  AND sqft > 200
  AND sqft < 100000;

额外优化建议 #

如果这个查询是高频使用的，建议把清洗后的sqft字段持久化到物理表中（比如创建一个物化视图或定期ETL同步），避免每次查询都重复执行正则替换和类型转换，能大幅提升查询性能。

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