Greenplum 6.8.1过滤含“万”数据后类型转换仍报“1.1万”错误咨询

根本触发逻辑 #

Greenplum基于PostgreSQL内核，默认会对视图查询执行谓词下推优化：当你对包含类型转换的视图添加数值过滤条件时，优化器会优先把类型转换、数值过滤操作下推到最底层的原始表执行，早于你第二步定义的「排除含万字符行」的过滤条件。所以会先遇到1.1万这类字符串执行类型转换，直接抛出报错。
你直接查询第二步视图时没有额外过滤条件，不会触发下推，所以能按你定义的顺序先过滤再返回结果，看不到含万字符的行，形成了「已经过滤干净」的错觉。

两个异常现象的解释 #

• 基于bigint视图二次转decimal不报错：多层嵌套的类型转换阻断了优化器的谓词下推路径，优化器无法将最外层的数值过滤直接推到原始表，会按你定义的视图顺序先执行第二步的过滤逻辑，去掉含万字符的行之后再做类型转换，因此不会报错。
• 直接基于第二步视图转decimal报错：单一层级的类型转换不会阻断谓词下推，优化器还是会把类型转换和数值过滤下推到原始表执行，触发转换报错。

• 方案1：阻断谓词下推，固定过滤执行顺序
  在第二步过滤视图的子查询中添加OFFSET 0语法，这是PG系数据库通用的阻止谓词下推的手段，可以强制优化器先执行过滤逻辑再处理后续的类型转换操作，修改后的第二步建视图语句如下：

CREATE VIEW pipeline.view_tclean_19280_1627009588402 AS 
SELECT "评分人数_拆分1","pub_info","评分","name","播放弹幕追番","url","评分人数","_record_id_" 
FROM (
    SELECT * FROM pipeline.view_tclean_19280_1627009588360 
    WHERE ("评分人数_拆分1" !~ E'万' OR "评分人数_拆分1" IS NULL) OR "评分人数_拆分1" = ''
    OFFSET 0
) t

• 方案2：使用安全类型转换函数规避报错
  Greenplum 6.8+支持TRY_CAST函数，转换失败时会返回NULL而非抛出报错，适合你这种脏数据场景，类型转换语句修改如下：

-- 转bigint写法
SELECT TRY_CAST("评分人数_拆分1" as bigint) as "评分人数_拆分1", ...
-- 转decimal写法
SELECT TRY_CAST("评分人数_拆分1" as decimal) as "评分人数_拆分1", ...

• 方案3：从根源统一字段格式
  建议在拆分「评分人数」字段后，先统一处理含「万」的数值（比如把1.1万转换为11000），再做后续的类型转换和过滤，从根源避免字符串转数值的异常。

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