CASE Expression Misuse in SQLite Trigger Logic

Incorrect Boolean Logic in CASE Expression Leading to Unexpected NULL Values

The core issue revolves around the misuse of the CASE expression within an SQLite trigger, specifically when attempting to set a value based on the new.type column. The trigger is designed to insert a row into table t2 with a val column that should be set to 100 when new.type is either 10 or 12, and to 200 when new.type is 11. However, the CASE expression is incorrectly formulated, leading to unexpected NULL values in the val column for new.type values of 10 and 12.

The CASE expression in question is written as:

CASE new.type WHEN (10 OR 12) THEN 100 WHEN 11 THEN 200 END

This formulation is problematic because the expression (10 OR 12) is evaluated as a boolean expression, not as a comparison against new.type. In SQLite, boolean expressions evaluate to 1 for TRUE and 0 for FALSE. Therefore, (10 OR 12) evaluates to 1, which means the CASE expression is effectively comparing new.type to 1 instead of 10 or 12. This results in the val column being set to NULL for new.type values of 10 and 12, because the comparison new.type = 1 is FALSE for these values.

Misunderstanding of SQLite Boolean Evaluation and CASE Syntax

The root cause of this issue lies in a misunderstanding of how SQLite evaluates boolean expressions and the correct syntax for the CASE expression. SQLite does not have a native boolean type; instead, it uses integers to represent boolean values, with 0 representing FALSE and any non-zero value representing TRUE. When the expression (10 OR 12) is evaluated, SQLite interprets it as a boolean expression, resulting in 1 because both 10 and 12 are non-zero values.

Furthermore, the CASE expression syntax used in the trigger is a shorthand form that compares the new.type value directly to the values specified in the WHEN clauses. The correct syntax for achieving the desired logic is to either list each condition separately or use the long form of the CASE expression with explicit comparisons.

The shorthand form of the CASE expression:

CASE new.type WHEN 10 THEN 100 WHEN 12 THEN 100 WHEN 11 THEN 200 END

is equivalent to:

CASE WHEN new.type = 10 THEN 100 WHEN new.type = 12 THEN 100 WHEN new.type = 11 THEN 200 END

Both forms correctly compare new.type to the specified values and produce the expected results.

Correcting the CASE Expression and Ensuring Proper Trigger Logic

To resolve this issue, the CASE expression within the trigger must be corrected to properly compare new.type to the desired values. The corrected trigger logic should be written as follows:

CREATE TRIGGER t1_trigger
AFTER INSERT ON t1
WHEN new.type IN (10, 11, 12)
BEGIN
    INSERT INTO t2 (t1_type, val)
    VALUES(new.type, 
        CASE 
            WHEN new.type = 10 THEN 100 
            WHEN new.type = 12 THEN 100 
            WHEN new.type = 11 THEN 200 
        END);
END;

This corrected trigger logic ensures that the val column in table t2 is set to 100 when new.type is 10 or 12, and to 200 when new.type is 11. The CASE expression now explicitly compares new.type to each of the specified values, avoiding the boolean evaluation issue that caused the original problem.

Additionally, it is important to understand the implications of using the WHEN clause in the trigger definition. The WHEN clause acts as a filter, ensuring that the trigger only executes for rows where new.type is 10, 11, or 12. This is a useful feature for optimizing trigger performance, as it prevents unnecessary trigger executions for rows that do not meet the specified condition.

To further illustrate the corrected behavior, consider the following example:

PRAGMA FOREIGN_KEYS=ON;

CREATE TABLE t1(
    type INT INTEGER PRIMARY KEY
);

CREATE TABLE t2(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    val INT,
    t1_type INT,
    FOREIGN KEY (t1_type) REFERENCES t1(type)
);

CREATE TRIGGER t1_trigger
AFTER INSERT ON t1
WHEN new.type IN (10, 11, 12)
BEGIN
    INSERT INTO t2 (t1_type, val)
    VALUES(new.type, 
        CASE 
            WHEN new.type = 10 THEN 100 
            WHEN new.type = 12 THEN 100 
            WHEN new.type = 11 THEN 200 
        END);
END;

INSERT INTO t1 VALUES (10);
INSERT INTO t1 VALUES (11);
INSERT INTO t1 VALUES (12);

SELECT * FROM t2;

The output of the SELECT statement will now correctly reflect the intended logic:

id | val | t1_type
--- | --- | -------
1  | 100 | 10
2  | 200 | 11
3  | 100 | 12

This demonstrates that the val column is now correctly populated based on the value of new.type.

In conclusion, the issue was caused by a misunderstanding of SQLite’s boolean evaluation and the correct syntax for the CASE expression. By correcting the CASE expression and ensuring proper trigger logic, the desired behavior can be achieved. This example highlights the importance of understanding the nuances of SQLite’s syntax and evaluation rules when writing complex SQL statements and triggers.