MySQL Bugs: #51821: Setting user var in trigger may lead to strange results

Bug #51821	Setting user var in trigger may lead to strange results
Submitted:	8 Mar 2010 9:22	Modified:	13 Aug 2012 13:32
Reporter:	Luis Soares	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.1, 5.5	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Picture a table that has a PK constraint and a trigger that for
each row inserted it changes the value of a user variable named
@aux, and @aux is also used in the insert as the PK field value.

This can lead to different results whether the value set in the
trigger is negative or positive (in the case I tested, 1 or -1
respectively).

In my test case, if trigger sets value to 1, the test succeeds,
if set to -1 the test fails with DUPLICATE entry. See how to
repeat section for details.

If removing the PK constraint, we will not have a failure, but
instead, we will get different contents on the table. This is
also wrong and inline with the defective behavior when a PK is
declared.

This was found in:

 - 5.1
   revid:build@mysql.com-20100115170348-qyaxw83mnms3gx8y
 - 5.5
   revid:alik@sun.com-20100222132231-0tt57bbj0k1b3woz

I have not tested against 6.0-codebase, but this may be there as well.

How to repeat:
1. bzr clone -r revid:build@mysql.com-20100115170348-qyaxw83mnms3gx8y $BZR_REPO/mysql-5.1

2. cd mysql-5.1
  
   - build it

3. cd mysql-test

4. create test file (t/user_var_trigger.test):

CREATE TABLE t1 ( c INT, PRIMARY KEY (c)) Engine=MyISAM;
CREATE TRIGGER tr1 AFTER INSERT ON t1 FOR EACH ROW SET @aux = -1 ; 

SET @aux = 10294947273192243200;
INSERT INTO t1 VALUES (@aux) , (@aux);

SELECT * FROM t1; 

DROP TABLE t1; 

-- exit

5. perl mysql-test-run.pl user_var_trigger

   Observe it failing

6. Change trigger definition, so that @aux is set to 1 instead of -1:

-CREATE TRIGGER tr1 AFTER INSERT ON t1 FOR EACH ROW SET @aux = -1 ;
+CREATE TRIGGER tr1 AFTER INSERT ON t1 FOR EACH ROW SET @aux = 1 ;

7. perl mysql-test-run.pl user_var_trigger

   Observe it succeeding.

Suggested fix:
n/a

Verified just as described:

77-52-24-143:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.45-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE t1 ( c INT, PRIMARY KEY (c)) Engine=MyISAM;
Query OK, 0 rows affected (0.06 sec)

mysql> CREATE TRIGGER tr1 AFTER INSERT ON t1 FOR EACH ROW SET @aux = -1 ; 
Query OK, 0 rows affected (0.24 sec)

mysql> SET @aux = 10294947273192243200;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t1 VALUES (@aux) , (@aux);
ERROR 1062 (23000): Duplicate entry '2147483647' for key 'PRIMARY'
mysql> 
mysql> SELECT * FROM t1; 
+------------+
| c          |
+------------+
| 2147483647 |
+------------+
1 row in set (0.00 sec)

mysql> drop trigger tr1;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TRIGGER tr1 AFTER INSERT ON t1 FOR EACH ROW SET @aux = 1 ; 
Query OK, 0 rows affected (0.15 sec)

mysql> delete from t1;
Query OK, 2 rows affected (0.00 sec)

mysql> SET @aux = 10294947273192243200;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t1 VALUES (@aux) , (@aux);
Query OK, 2 rows affected, 1 warning (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 1

mysql> SELECT * FROM t1; 
+------------+
| c          |
+------------+
|          1 |
| 2147483647 |
+------------+
2 rows in set (0.00 sec)

to highlight the issue, consider

INSERT INTO t1 VALUES (@aux), (-1); 

works out perfectly. But if @aux becomes -1 in the trigger
 
INSERT INTO t1 VALUES (@aux),(@aux /* == -1 */);

gains the dup key error.

SET @aux = 10294947273192243200;
is such a big number that @aux is created as bigint unsigned.
INSERT reads @aux, when the type of @aux is still bigint unsigned.
Type is constant in a query, see
http://dev.mysql.com/doc/refman/5.6/en/user-variables.html :

"Another issue with assigning a value to a variable and reading the value
within the same statement is that the default result type of a variable is
based on its type at the start of the statement. "

So trigger tries to store -1 in @aux which is bigint *unsigned*, which fails
(out of range), so @aux does not change.
So we store twice 10294947273192243200 in the column, twice truncated to max
of INT column, so dup key error.
Works as documented. Not a bug.