PostgreSQL中仅最后一行1和1.0不同的两个查询结果为何不同？

问题根本原因 #

你碰到的报错本质是PostgreSQL优化器的执行计划调整 + 不安全的强制类型转换 + 隐式类型转换触发的执行顺序变化共同导致的，具体拆解如下：

• 先明确几个字段的类型前提：
  • xm_atributo.VALOR是字符串类型，只有当id_tipo_atributo对应"AGENTE COMERCIAL QUE EXPORTA"的时候，这个字段存的是可转换为数字的ID，其他类型的属性对应的VALOR可能是普通文本（比如报错里的企业名称）
  • xe.id_tipo_entidad是整数类型
• 第一个查询（xe.id_tipo_entidad=1）能正常执行的原因：
  1是整数，和id_tipo_entidad的类型完全匹配，优化器生成的执行计划会先执行过滤条件：先筛出xm_tipo_atributo中符合名称的ID，再筛出xm_entidad中id_tipo_entidad=1的行，最后再做两表关联，此时参与VALOR::double precision::integer转换的行都是属性类型匹配的，VALOR都是可转数字的字符串，所以不会报错。
• 第二个查询（xe.id_tipo_entidad=1.0）报错的原因：
  1.0是双精度浮点数（double precision），和整数类型的id_tipo_entidad比较时会触发隐式类型转换，优化器会判断类型转换后的成本，调整执行计划的顺序：提前执行JOIN条件中的VALOR类型转换，还没等到过滤属性类型、实体类型的条件生效，就已经处理到了VALOR为普通文本的行，尝试把"BARBACOAL BARBOSA BALLESTEROS "这种文本转双精度浮点数时就触发了报错，对应的SQL状态码22P02就是典型的无效输入类型转换错误。

修复方案 #

1. 优先修改不安全的强制类型转换：PostgreSQL 12及以上版本可以用TRY_CAST替代强制转换，非数字内容会返回NULL而非直接报错，把JOIN条件改成：

   ON TRY_CAST(TRY_CAST(VALOR AS double precision) AS integer) = xe.id_entidad
   

2. 也可以把VALOR的转换逻辑放到子查询里，先过滤属性类型再做转换，强制执行顺序：

select xa.ID_ENTIDAD, xe.nombre_entidad AGENTE_COMERCIAL_QUE_EXPORTA,
GREATEST (fecha_ini_validez,'2021-07-01 00:00' ) FECHA_INI_VALIDEZ,
LEAST (FECHA_FIN_VALIDEZ,'2021-07-31 23:59' ) FECHA_FIN_VALIDEZ,
ROW_NUMBER() over (partition by xa.id_entidad order by fecha_ini_validez) orden
from (
    select * from xm_atributo
    where id_tipo_atributo in (
        select id_tipo_atributo from xm_tipo_atributo where nombre='AGENTE COMERCIAL QUE EXPORTA'
    )
    and fecha_ini_validez<='2021-07-31 23:59'::timestamp and FECHA_FIN_VALIDEZ >= '2021-07-01 00:00'::timestamp
) xa
join xm_entidad xe on VALOR::double precision::integer = xe.id_entidad
where xe.id_tipo_entidad=1.0

3. 最稳妥的方案还是保持整数类型的比较，不要用1.0这种浮点数和整数字段做等值判断，既可以避免浮点数精度问题，也能避免不必要的隐式转换。

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