Topic
1 reply Latest Post - ‏2012-10-19T09:58:59Z by SystemAdmin
SystemAdmin
SystemAdmin
17917 Posts
ACCEPTED ANSWER

Pinned topic WHERE clause COLUMN_A = COLUMN_A

‏2012-10-18T19:45:27Z |
SELECT created_by_branch_id from ur.airreservation_bookingtraveler
where created_by_branch_id = 1214
and air_reservation_id = coalesce(null, air_reservation_id)
and booking_traveler_id = coalesce(null, booking_traveler_id)
and bt_first_name_up = coalesce(null, bt_first_name_up)
and bt_last_name_up = coalesce(null, bt_last_name_up)
and airres_urid = coalesce(null, airres_urid)
and firstname = coalesce(null, firstname)
--and middlename = coalesce(NULL, middlename)
--and middlename = middlename

When I run this query with just the top part I get 40 rows:

SELECT created_by_branch_id from ur.airreservation_bookingtraveler
where created_by_branch_id = 1214

When I add in the next lines, I still get 40 rows:

SELECT created_by_branch_id from ur.airreservation_bookingtraveler
where created_by_branch_id = 1214
and air_reservation_id = coalesce(null, air_reservation_id)
and booking_traveler_id = coalesce(null, booking_traveler_id)
and bt_first_name_up = coalesce(null, bt_first_name_up)
and bt_last_name_up = coalesce(null, bt_last_name_up)
and airres_urid = coalesce(null, airres_urid)
and firstname = coalesce(null, firstname)
When I add in the following line, I get ZERO rows:

and middlename = coalesce(NULL, middlename)

Or if I add the following line, I also get ZERO rows:

and middlename = middlename

All middlenames for this set of rows are defined as NULL. I thought that when you did an EXACT column to column predicate in a where clause, you should get ALL rows and nothing should be filtered. Is there something about DB2 to cause NULLs to be filtered in this case? This isnt the behavior I would expert. Thanks in advance.
Updated on 2012-10-19T09:58:59Z at 2012-10-19T09:58:59Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    17917 Posts
    ACCEPTED ANSWER

    Re: WHERE clause COLUMN_A = COLUMN_A

    ‏2012-10-19T09:58:59Z  in response to SystemAdmin
    Hi there,

    I think there is nothing wrong here. The documentation says:

    Two strings are equal if they are both empty or if all corresponding bytes are equal. If either operand is null, the result is unknown.

    Also:

    If the search condition evaluates to unknown, the value of NULL is assigned.

    And as you say that all the rows for your set has NULL values for the middle name then neither of your conditions on middle name can be properly evaluated and therefore will filtered as the result of that comparison is unknown and therefore not true.

    
    select 1 from sysibm.sysdummy1 where null=
    
    null   1 -----------   0 record(s) selected.
    


    The above code does not return any rows despite using null at both sides of the where clause. But the next code will return 1 row as '' is equal to ''

    
    select 1 from sysibm.sysdummy1 where 
    ''=
    ''   1 ----------- 1   1 record(s) selected.
    


    I think that your confusion comes from the Oracle side. Oracle does not make any distinction between null and '' (the empty string). That is why you can compare nulls and obtain a result. DB2 makes a distinction between both and therefore they are not the same and any comparison with NULL at any side will result in an unknown result and therefore will not produce your expected results.

    Also I do not understand the use you are making of the COALESCE function in your query in any of the cases.

    
    coalesce(null, air_reservation_id)
    
    will always return air_reservation_id as the first element in the list of parameters is always null

    So the only effective part of your query is

    
    SELECT created_by_branch_id from ur.airreservation_bookingtraveler where created_by_branch_id = 1214
    


    The rest does not make any sense here.

    Hope this helps,

    LooW