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

when either 1 of field null, want returned value null well. have tried reversing logic: is not null. still same results.

mysql code:

(case             when                 ((`creative_stg_sample_tracking_raw`.`total_samples_received` not null)                     , (`creative_stg_sample_tracking_raw`.`total_samples_forecasted` not null))                             (cast(`creative_stg_sample_tracking_raw`.`total_samples_received`                     signed) - cast(`creative_stg_sample_tracking_raw`.`total_samples_forecasted`                     signed))             else null         end) `received_forecasted_dif` 

screenshot:

your code should working, don't need case. whenever 1 of values null, expression should null:

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

i wonder if problem value 'null' rather null. is, string value rather real null. mysql treat string 0 in arithmetic.

you can fix doing:

(case when `creative_stg_sample_tracking_raw`.`total_samples_received` <> 'null' ,            `creative_stg_sample_tracking_raw`.`total_samples_forecasted` <> 'null'       (cast(`creative_stg_sample_tracking_raw`.`total_samples_received` signed) -             cast(`creative_stg_sample_tracking_raw`.`total_samples_forecasted` signed))            )       else null end) `received_forecasted_dif`