SELECT INTO Returning NULL in MySQL Trigger

Question

I'm having an issue where a SELECT INTO query in a MySQL trigger is returning NULL for values that should be fetched correctly.

Here is my trigger code:

BEGIN
    DECLARE program_price DECIMAL(10, 2);
    DECLARE payment_type enum('Full', 'Installment');
    DECLARE program_duration_months INT;
    DECLARE amount DECIMAL(10, 2);
    DECLARE details VARCHAR(255);
    DECLARE payment_due DATE;

 -- Log start of trigger execution
    INSERT INTO debug_table (debug_message) VALUES ('Trigger execution started.');
    -- Fetch program details
    INSERT INTO debug_table (debug_message)
    VALUES (CONCAT('Fetching program details for program_code: ', NEW.program_code));

    SELECT price, payment_type
    INTO program_price, payment_type
    FROM Programs
    WHERE program_code = NEW.program_code;
    
       INSERT INTO debug_table (debug_message)
    VALUES (CONCAT('Fetched program details for program_code: ', NEW.program_code));

    -- Log fetched values
    INSERT INTO debug_table (debug_message)
    VALUES (CONCAT('Fetched values - program_price: ', program_price, ', payment_type: ', payment_type, ', program_duration_months: ', program_duration_months));
    INSERT INTO debug_table (debug_message) VALUES ('Trigger execution completed.');
    

END

The trigger executes successfully, but when the SELECT INTO is run, the values for program_price, payment_type, and program_duration_months are always returned as NULL. I have checked the program_code in the Programs table, and it exists, and there is no issue with the case sensitivity of the values (I’ve already tested this).

Things I've Tried:

• Checked the program_code value (D632 in this case) in the Programs table, and it matches.
• Ensured there is no issue with case sensitivity (the collation is not utf8_bin).
• Added debug statements to log the SELECT INTO output.
• Tried using a SELECT query without the INTO clause and confirmed that the values are returned correctly when executed directly (not in the trigger).

The Programs table has the following data that is expected:

program_price = 499.99 payment_type = 'Installment'

Answer 1

Your trigger has two problems.

First, the program_duration_months local variable has not been assigned any value, nor does it have a DEFAULT. So it defaults to NULL. CONCAT() returns NULL if any of its arguments are NULL.

Second, you named the payment_type variable the same as a column of the table. In cases where both a column and a variable of the same name exist in a stored routine, MySQL chooses to reference the variable.

https://dev.mysql.com/doc/refman/8.4/en/local-variable-scope.html says:

A local variable should not have the same name as a table column. If an SQL statement, such as a SELECT ... INTO statement, contains a reference to a column and a declared local variable with the same name, MySQL currently interprets the reference as the name of a variable.

To fix the latter problem, name your local variable something distinct from the column names in any tables you reference in the routine.

For example, I tested the trigger with these changes:

BEGIN
    ...
    DECLARE v_payment_type enum('Full', 'Installment'); -- change variable name
    DECLARE program_duration_months INT DEFAULT 0; -- give variable a default value

    ...

    SELECT price, payment_type
    INTO program_price, v_payment_type
    FROM Programs
    WHERE program_code = NEW.program_code;

    ...

    INSERT INTO debug_table (debug_message)
    VALUES (CONCAT('Fetched values - program_price: ', program_price,
      ', payment_type: ', v_payment_type, 
      ', program_duration_months: ', program_duration_months));

    ...
END

Testing it has this result:

mysql> select * from debug_table;
+---------------------------------------------------------------------------------------+
| debug_message                                                                         |
+---------------------------------------------------------------------------------------+
| Trigger execution started.                                                            |
| Fetching program details for program_code: 123                                        |
| Fetched program details for program_code: 123                                         |
| Fetched values - program_price: 19.95, payment_type: Full, program_duration_months: 0 |
| Trigger execution completed.                                                          |
+---------------------------------------------------------------------------------------+