MySQL Stored Procedure Programming

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); ...