mysql - 意外行为 : Subtraction between null values results in '0'

Question

当任一字段为NULL 时，我希望返回值也为NULL。我也尝试过反转逻辑:is not null。结果还是一样。

MySQL代码:

(case
            when
                ((`creative_stg_sample_tracking_raw`.`total_samples_received` is not null)
                    and (`creative_stg_sample_tracking_raw`.`total_samples_forecasted` is not null))
            then
                (cast(`creative_stg_sample_tracking_raw`.`total_samples_received`
                    as signed) - cast(`creative_stg_sample_tracking_raw`.`total_samples_forecasted`
                    as signed))
            else NULL
        end) AS `received_forecasted_dif`

截图:

Answer 1

您的代码应该可以正常工作，但您不需要case。只要其中一个值是 NULL，表达式就应该是 NULL:

(cast(`creative_stg_sample_tracking_raw`.`total_samples_received` as signed) -
 cast(`creative_stg_sample_tracking_raw`.`total_samples_forecasted` as signed))
) AS `received_forecasted_dif`

我想知道您的问题是否是该值实际上是 'NULL' 而不是 NULL。也就是说，一个字符串值而不是一个真正的 NULL。 MySQL 在算法中将字符串视为 0。

您可以通过以下方式解决此问题:

(case when `creative_stg_sample_tracking_raw`.`total_samples_received` <> 'NULL' and
           `creative_stg_sample_tracking_raw`.`total_samples_forecasted` <> 'NULL'
      then (cast(`creative_stg_sample_tracking_raw`.`total_samples_received` as signed) -
            cast(`creative_stg_sample_tracking_raw`.`total_samples_forecasted` as signed))
           )
      else NULL
end) AS `received_forecasted_dif`