dan
dan

Reputation: 73

Comparing a Null to another value in MySQL Trigger

So here is my issue I am comparing new and old values when a table row is being updated. But the new or old value will sometimes be null. So the code below doesn't work. Can can I remedy this issue?

Thank You

BEFORE UPDATE ON mytable
   FOR EACH ROW
BEGIN
        IF OLD.assignedto != NEW.assignedto
        THEN
                INSERT INTO history
                    (
                        asset    ,
                        changedfield     ,
                        oldvalue    ,
                        newvalue      
                    )
                    VALUES
                    (
                        NEW.asset,
                        'assignedto',
                        OLD.assignedto,
                        NEW.assignedto
                    );
        END IF;
    END$$

Upvotes: 7

Views: 9271

Answers (6)

AFMeirelles
AFMeirelles

Reputation: 419

I know this is old, but there's a slightly clearer way to write that condition:

IF COALESCE(OLD.assignedto, 0) <> COALESCE(NEW.assignedto, 0) THEN ...

This way you tell the engine to replace NULLs with a 0 so the condition won't fail by comparing to NULL.

Upvotes: 1

nightcoder
nightcoder

Reputation: 13519

MySql has a special null-safe equality check operator:

<=>

NULL-safe equal. This operator performs an equality comparison like the = operator, but returns 1 rather than NULL if both operands are NULL, and 0 rather than NULL if one operand is NULL.

mysql> SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;
        -> 1, 1, 0
mysql> SELECT 1 = 1, NULL = NULL, 1 = NULL;
        -> 1, NULL, NULL

You can use this operator with the NOT operator:

mysql> SELECT NOT (1 <=> 1), NOT (NULL <=> NULL), NOT (1 <=> NULL);
        -> 0, 0, 1

So, in your case you should write:

IF NOT (OLD.assignedto <=> NEW.assignedto)

Upvotes: 21

Chris Cunningham
Chris Cunningham

Reputation: 1876

Try:

IF (   (OLD.assignedto IS NOT NULL AND NEW.assignedto IS NOT NULL 
             AND OLD.assignedto != NEW.assignedto)
    OR (OLD.assignedto IS NULL AND NEW.assignedto IS NOT NULL)
    OR (OLD.assignedto IS NOT NULL AND NEW.assignedto IS NULL)
   )

The comment is very much correct.

Upvotes: 4

glglgl
glglgl

Reputation: 91119

IF COALESCE(OLD.assignedto != NEW.assignedto, OLD.assignedto IS NULL AND NEW.assignedto IS NULL)

If the OLD.assignedto != NEW.assignedto part has one operator NULL, the whole expression gets NULL, making COALESCE() return the next argument instead which returns the truth value to use when one of them is NULL - the other one must be NULLas well.

Upvotes: 1

GordonM
GordonM

Reputation: 31760

You can't compare against NULL because NULL is defined as a value that never matches in a comparison, even other NULLS. Try for yourself if you don't believe me.

SELECT NULL = NULL;

You will have to check that the column in question isn't null with IS NOT NULL. You can use the AND or OR operator to incorporate such a test into the logic you already have.

Upvotes: 1

Marc B
Marc B

Reputation: 360772

nulls have to be compared with the is null/is not null construct:

if ((old.assignedto != new.assignedto) or (old.assignedto is null) or (new.assignedto is null))

You'l have to adjust that to match your requirements as this will come back true if either is null or they're not equal to each other. Perhaps you need to handle the case where both are null.

Upvotes: 1

Related Questions