mysql null value case when is null does not work

Question

I have following query to handle null value and flag those based on two table join
however c.pure_proc_flag is null then 0 else 1 does not provide the result I expect.

query as following

select 
b.procedure_Type as procedure_type,b.procedure_Episode_ID as episode_id,b.claim_claim_number as claim_number,1.0 as card_procedure_ind,
c.pure_proc_flag as pure_proc_flag_orig,
ifnull(c.pure_proc_flag,0) as pure_proc_flag2,
case when c.pure_proc_flag is null then 0 else 1 end as pure_proc_flag3
from
(select procedure_Type,procedure_Episode_ID,claim_Claim_Number FROM HEALTHPLAN_SANDBOX.JX_INS_CLAIMS_PROCEDURE
where procedure_Type='Cardiology - CABG' and procedure_Episode_ID='1029877775') b
left join
(select procedure_Type,procedure_Episode_ID,claim_Claim_Number,1 as pure_proc_flag FROM HEALTHPLAN_SANDBOX.JX_INS_CLAIMS_PROCEDURE_SOURCE
where procedure_Type='Cardiology - CABG' and procedure_Episode_ID='1029877775') c
on b.procedure_Type=c.procedure_Type and b.procedure_Episode_ID=c.procedure_Episode_ID and b.claim_Claim_Number=c.claim_Claim_Number

the query is pretty straightforward.
I use b table left join c table
whereever the raw in b table not find the match in c table, It return null value, rest should be 1 show as pure_proc_flag_orig column
based on the condition of pure_proc_flag in c table, I want to flag those into 0 or 1

For understanding. pure_proc_flag2 and pure_proc_flag3 logic are the same.

however the result is different.

as you can see ifnull(c.pure_proc_flag,0) as pure_proc_flag2 work as expect, however, case when c.pure_proc_flag is null then 0 else 1 end as pure_proc_flag3 does not work. (it indicated no blank row in c table. however, as we can see in pure_proc_flag_orig column, c table have blank row due to left join.

do I miss any important concept in mysql?

system version: aurora_version 2.02.5
innodb_version 5.7.12

Answer 1

Your query could actually be simplified without doubling the select queries. Both "b" and "c" queries were filtering on the same criteria of procedure type and episode. The only difference was the left-join to the "c" additionally included the CLAIM number which is where you would possibly find your null matches, such as when a given procedure has multiple claims against it.

Procedure_type  Procedure_Episode_id  Claim_Claim_Number
A               1                     X
A               1                     Y

The first entry same episode wont find the match against the second claim (and vice-versa) so those would show as null/0

Revised query without explicit select subqueries.

select
        cp.procedure_Type,
        cp.procedure_Episode_ID episode_id,
        cp.claim_claim_number claim_number,
        1.0 as card_procedure_ind,
        cp2.pure_proc_flag pure_proc_flag_orig,
        COALESCE( cp2.pure_proc_flag, 0 ) as pure_proc_flag2UsingCoalesce,
        ifnull( cp2.pure_proc_flag, 0 ) as pure_proc_flag2,
        case when cp2.pure_proc_flag is null 
            then 0 else 1 end as pure_proc_flag3
    from
        HEALTHPLAN_SANDBOX.JX_INS_CLAIMS_PROCEDURE cp
            LEFT JOIN HEALTHPLAN_SANDBOX.JX_INS_CLAIMS_PROCEDURE_SOURCE cp2
                on cp.procedure_Type = cp2.procedure_Type 
                and cp.procedure_Episode_ID = cp2.procedure_Episode_ID 
                and cp.claim_Claim_Number = cp2.claim_Claim_Number
    where
            cp.procedure_Type = 'Cardiology - CABG' 
        and cp.procedure_Episode_ID = '1029877775'
     

As to the issue, I think it might be a bug in the IFNULL() function. I changed (actually added) another copy but used COALESCE() instead as you can see in my example, and it properly shows the 0 or 1 respectively where the IFNULL() does not.

Now, to POSSIBLY explain the reasoning behind the failure is only speculation. From your original secondary query "c" alias for the 1 as Pure_Proc_Flag, that query is being processed in full before the JOIN is done. By having a forced value all the time, does the MySQL actually force this column as a non-null and thus is always (by its returned structure), will never be a null value? And that is why it is returning 1 all the time.

You may instead want to check for NULL of any of the columns you are attempting to JOIN on that SHOULD exist, but may not. Such as I added in my sample.

Answer 2

Kidnly answer the below 3 questions for a better understanding:

1. Try to use ifnull for pure_proc_flag3, and check if it will give the same result when using case function or not.

2. Try to use length(pure_proc_flag3), to check what is the number of characters when the value is NULL.

3. Try to run this replace(pure_proc_flag3, null, 'Ops'), and check if it will print to you Ops or not.