Name
DAT-05: Don’t assume that the result of an expression is TRUE or FALSE; it could be NULL
Synopsis
Three-valued logic—the logic that includes NULLs—is an essential part of the relational database model. However, the tendency of humans to think in terms of two-valued logic—an expression is either TRUE or FALSE—can lead to serious logic bugs.
For instance, consider the following logic, which is intended to retire employees older than 65 years and older, and keep those younger than 65 years:
IF v_date_of_birth > DATE_SUB(NOW( ), INTERVAL 65 YEAR) THEN CALL keep_employee( v_employee_id); ELSE CALL retire_employee( v_employee_id); END IF;
This logic seems valid from a two-valued logic perspective,
but what if v_date_of_birth
is
NULL? If the date of birth is NULL, then the date comparison will
return NULL, rather than TRUE or FALSE. Consequently, the ELSE
condition will be executed and we
will retire an employee, although in fact we have no idea how old
the employee is.
NULL values can be handled in a couple of ways:
Explicitly check that a value is NOT NULL before attempting a comparison.
Explicitly check each condition: don’t assume that an expression that is not TRUE, is necessarily FALSE.
If we are worried about the date of birth being NULL in the above example, we might recode it as follows:
SET v_age_in_years=DATEDIFF(NOW( ), v_date_of_birth)/365.25; IF v_age_in_years > 65 THEN CALL retire_employee( v_employee_id); ELSEIF v_age_in_years <= 65 THEN CALL keep_employee( v_employee_id); ...
Get MySQL Stored Procedure Programming now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.