Why use COALESCE() in sql statement

Question

Hi,

I'm refactoring some old code and stumbled upon this named query (It's using hibernate on top of mysql):

delete F from
    foo F
inner join user DU on F.user_id = DU.id
where
(COALESCE(:userAlternateId,null) is null or DU.alternate_id like :userAlternateId )
    and ( COALESCE(:fooId,null) is null or F.foo_id like :fooId )
    and (
        ( COALESCE(:fromUpdated,null) is null or F.updated_at>=:fromUpdated )
        and ( COALESCE(:toUpdated,null) is null or F.updated_at<=:toUpdated )
)

I don't understand why this COALESCE is being used in this fashion: COALESCE(:userAlternateId,null) is null

is this a performance hack or does it make the query database independent or ...?

btw userAlternateId is a string/varchar, other id's are longs and from-to are dates

Answer 1

I can't think of any reason for COALESCE being used this way.

Following statement is equivalent

DELETE  F 
FROM    foo F
        INNER JOIN User DU on F.user_id = DU.id
WHERE   (:userAlternateId IS NULL OR DU.alternate_id LIKE :userAlternateId)
        AND (:fooId IS NULL OR F.foo_id LIKE :fooId)
        AND (:fromUpdated IS NULL OR F.updated_at >= :fromUpdated)
        AND (:toUpdated IS NULL OR F.updated_at <= :toUpdated)

Answer 2

Yep, thinking about this more, I'm betting on what I suggested in the comment on the question. Either it's auto-generated code, and its an artefact of the way the code generating that code has to handle more general issues than the particular case it's dealing with here, or it's an artefact of someone moving from something more reasonable like COALESCE(:userAlternateId, "not set") = "not set" to COALESCE(:userAlternateId,null) is null, which while not very sensible, is something that you can see how someone could get from A to B.