CODEWITHSUNDEEP
mysqlsql
Kyle
Kyle

Reputation: 5557

Unintended behavior: Subtraction between null values results in '0'

When either one of the field is NULL, I want the returned value to be NULL as well. I have also tried reversing the logic: is not null. Still the same results.

MySQL code:

(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`

Screenshot:

screenshot

Upvotes: 1

Views: 91

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

Your code should be working, but you don't need the case. Whenever one of the values is NULL, the expression should be 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`

I wonder if your problem is that the value is actually 'NULL' rather than NULL. That is, a string value rather than a real NULL. MySQL will treat the string as 0 in the arithmetic.

You can fix this by doing:

(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`

Upvotes: 1

Related Questions