MySQL Bugs: #10411: A trigger behaves incorrectly if a column that it alters is not available.

Bug #10411	A trigger behaves incorrectly if a column that it alters is not available.
Submitted:	6 May 2005 11:54	Modified:	19 May 2005 18:29
Reporter:	Disha	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.4 Beta	OS:	Windows (Windows 2003)
Assigned to:	Dmitry Lenev	CPU Architecture:	Any

Description:
If a trigger uses a column to alter and a value is not available for that column, when the trigger executes, then the trigger behaves incorrectly.

How to repeat:
1. Connect to MySQL server with valid user name and password.
2. Select the TEST database with the USE command and set the delimiter to // as follows:
   USE TEST
   DELIMITER //

3. Create a table with name T1 having a VARCHAR field of length 0 having the InnoDB engine as follows:
   CREATE TABLE T1 (F1 CHAR(20), F2 CHAR(20))//

4. After the table is created, add values in the table as follows:
   INSERT INTO T1 VALUES('AA','BB')//

5. Create a trigger on table T1 as follows:
   CREATE TRIGGER TRG1 BEFORE UPDATE ON T1 FOR EACH ROW
   BEGIN
      SET NEW.F1 = CONCAT(NEW.F1,'ADDED TEXT');
      SET NEW.F2 = CONCAT(NEW.F2,'ADDED TEXT');
   END;//

6. Now update the value of F1 that is ‘AA’ to ‘NEW’ using the following command:
   UPDATE T1 SET F1='NEW' WHERE F2='BB'//

Expected Results: 
1. The value of column F1 should be change to ‘NEWADDED TEXT’ from ‘AA’
2. The value of column F2 should be change to ‘ADDED TEXT’ from ‘BB’

Actual Results: 
1. The value of F1 is changed to 'NEWADDED TEXT’ (as expected)
2. But the value of F2 is changed to ‘BBADDED TEXT’

Additional Info: 
1. If we execute the update command once again as follows:
UPDATE T1 SET F1='NEW' WHERE F2='BBADDED TEXT'//

Then the value of F2 is again appended with test ‘ADDED TEXT’ and the value of F2 becomes as ‘BBADDED TEXTADDED TEXT’

Hi!

What you are observing is not a bug. This is expected behavior.

NEW and OLD transition variables accessible in triggers represent new and old versions of rows being updated (inserted or deleted). This behavior documented in our manual at in chapter "20.3. Using Triggers" http://dev.mysql.com/doc/mysql/en/using-triggers.html) and is consistenet with SQL standard.

In your case when update statement will invoke before trigger initial value of NEW transition variable will be ('NEW', 'BB') (since NEW represent new full version of row being updated, and not array of fields explicitly set in UPDATE statement). So naturally the final version of row which will be stored in table is ('NEWADDED TEXT', 'BBADDED TEXT'). And this is exactly what you observe.