Bug #50511 | Sometimes wrong handling of user variables containing NULL | ||
---|---|---|---|
Submitted: | 21 Jan 2010 15:27 | Modified: | 7 May 2010 20:01 |
Reporter: | Matthias Leich | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server | Severity: | S3 (Non-critical) |
Version: | 5.1.43,5.5.99-m3,6.0.14-alpha | OS: | Any |
Assigned to: | Alexander Nozdrin | CPU Architecture: | Any |
Tags: | replication |
[21 Jan 2010 15:27]
Matthias Leich
[21 Jan 2010 16:04]
Matthias Leich
This bug is on the first view rather harmless because no wrong - (SELECT) results are reported - values are inserted. But this bug also breaks the statement based replication. --source include/master-slave.inc --disable_warnings DROP TABLE IF EXISTS t1; DROP TABLE IF EXISTS t2; --enable_warnings CREATE TABLE t1 ( f2 VARCHAR(10), f1 INT AUTO_INCREMENT, PRIMARY KEY (f1)); SET SESSION BINLOG_FORMAT = MIXED; SET @aux = 1.23E-1; CREATE TABLE t2 AS SELECT @aux; SET @aux = CAST(NULL AS SIGNED); SELECT @aux; INSERT INTO t1 ( f1 , f2 ) VALUES ( NULL , 'NULL' ) ; INSERT INTO t1 ( f1 , f2 ) VALUES ( @aux , '@aux' ) ; SELECT f1,f2 FROM t1 ORDER BY f1; --sync_slave_with_master connection master; DROP TABLE t1; DROP TABLE t2; Result on 5.5.99-m3-debug-log (mysql-next-mr revno: 2962 2010-01-18) -------------------------------------- ... main.ml38x [ fail ] ... === SHOW SLAVE STATUS === ---- 1. ---- ... Last_Errno 1048 Last_Error Error 'Column 'f1' cannot be null' on query. Default database: 'test'. Query: 'INSERT INTO t1 ( f1 , f2 ) VALUES ( @aux , '@aux' )' .... SHOW BINLOG EVENTS IN 'master-bin.000001'; (edited) ... Info Server ver: 5.5.99-m3-debug-log, Binlog ver: 4 use `test`; DROP TABLE IF EXISTS t1 use `test`; DROP TABLE IF EXISTS t2 use `test`; CREATE TABLE t1 ( f2 VARCHAR(10), f1 INT AUTO_INCREMENT, PRIMARY KEY (f1)) @`aux`=0.123 use `test`; CREATE TABLE t2 AS SELECT @aux BEGIN INSERT_ID=1 use `test`; INSERT INTO t1 ( f1 , f2 ) VALUES ( NULL , 'NULL' ) COMMIT BEGIN INSERT_ID=2 @`aux`=NULL use `test`; INSERT INTO t1 ( f1 , f2 ) VALUES ( @aux , '@aux' ) COMMIT
[22 Jan 2010 11:36]
Matthias Leich
SET @aux1 = 0.123E-1; influences the data type of @aux1 in a way that a table create from @aux1 has a column of data type double. Here the data type of @aux1 is problematic. SET @aux1 = 1 ; influences the data type of @aux1 in a way that a table create from @aux1 has a column of data type bigint(20). Here the data type of @aux1 is harmless. Workaround (I am aware that it is uncomfortable): ------------------------------------------------- Replace SET @aux = <whatever value and data type including NULL>; by SET @aux = 1 ; SET @aux = <whatever value and data type including NULL>;
[25 Jan 2010 13:47]
Matthias Leich
Unfortunately the workaround does not help everywhere in my RQG tests for replication: --source include/master-slave.inc CREATE TABLE t1 ( pk INT AUTO_INCREMENT, PRIMARY KEY (pk)); CREATE TABLE t2 ( f2 DOUBLE); INSERT INTO t2 SET f2 = 1.1e+2; SELECT f2 FROM t2 INTO @aux ; SET SESSION BINLOG_FORMAT = MIXED; UPDATE t2 SET f2 = @aux ; SET @aux = 1 ; SET @aux = NULL; INSERT INTO test . t1 ( `pk` ) VALUES ( @aux ) ; --sync_slave_with_master exit; Result: ------- ... Last_Error Error 'Column 'pk' cannot be null' on query. Default database: 'test'. Query: 'INSERT INTO test.t1 ( `pk` ) VALUES ( @aux )' This means I have to disable any use of columns and values of data type FLOAT and DOUBLE.
[29 Jan 2010 22:39]
Omer Barnir
triage: - setting to w2 I3 because of test impact and changing to SR55RC, SRMR03 (to risky to be put in 5.1)
[4 Feb 2010 18:06]
Matthias Leich
I found another example of this bug when running replication tests on mysql-next-mr (5.5.99-m3). In this case a TRIGGER is involved but AUTO_INCREMENT is no more needed. DROP TABLE IF EXISTS t1; DROP TRIGGER IF EXISTS tr1; CREATE TABLE t1 ( col1 varchar(257) , pk int , primary key (pk)); CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW SET @aux = 1 ; SET @aux = NULL; INSERT INTO test.t1 ( col1,pk ) VALUES ( 1,1 ) , ( @aux,2 ) ; SET @aux = CONVERT( 'nl' USING UCS2 ); SET @aux = NULL; INSERT INTO test.t1 ( col1,pk ) VALUES ( 1,3 ) , ( @aux,4 ) ; Warnings: Warning 1265 Data truncated for column 'col1' at row 2 <-- This is wrong. We have NULL and there is nothing to truncate. SELECT col1,pk FROM t1 ORDER BY pk; col1 pk 1 1 1 2 1 3 4 <-- The value for col1 is wrong. DROP TABLE t1;
[14 Apr 2010 16:19]
Alexander Nozdrin
Matthias, I agree with the 1st test case, but I think the 2nd case is not valid. The thing is that user variables are fixed for the whole INSERT statement at once. Thus, changes to the user variable value made inside the trigger are not visible for INSERT execution.
[14 Apr 2010 18:16]
Matthias Leich
Hi Alexander, my second testcase might be a bad effect caused by other reasons than in testcase 1. But it is also a bug because we have here inconsistent behaviour. Both subtests follow the "use pattern" 1. Do some the data type of @aux affecting manipulations 2. Set @aux to NULL 3. INSERT INTO test.t1 VALUES (<val1>,<val2>), (@aux,<val4>); but the outcome for the content of col1 differs. Case 1: ------- no @aux related history before SET @aux = NULL; INSERT INTO test.t1 ( col1,pk ) VALUES (1,1), ( @aux,2 ) ; SELECT * FROM test.t1 WHERE pk = 2; col1 pk 1 2 Case 2: ------- SET @aux = CONVERT( 'nl' USING UCS2 ); SET @aux = NULL; INSERT INTO test.t1 ( col1,pk ) VALUES (1,3), ( @aux,4 ) ; Warnings: Warning 1265 Data truncated for column 'col1' at row 2 SELECT * FROM test.t1 WHERE pk = 4; col1 pk 4 Or do I misunderstand something? Regards Matthias
[16 Apr 2010 9:34]
Alexander Nozdrin
Hi Matthias, > my second testcase might be a bad effect caused by other > reasons than in testcase 1. But it is also a bug because > we have here inconsistent behaviour. No, it is not a bug. According the The Manual, the result of setting and getting user variable within one statement is not determined: http://dev.mysql.com/doc/refman/5.4/en/user-variables.html <quote> As a general rule, you should never assign a value to a user variable and read the value within the same statement. You might get the results you expect, but this is not guaranteed. The order of evaluation for expressions involving user variables is undefined and may change based on the elements contained within a given statement. In SELECT @a, @a:=@a+1, ..., you might think that MySQL will evaluate @a first and then do an assignment second. However, changing the statement (for example, by adding a GROUP BY, HAVING, or ORDER BY clause) may cause MySQL to select an execution plan with a different order of evaluation. 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. The following example illustrates this: </quote> > Case 2: > ------- > SET @aux = CONVERT( 'nl' USING UCS2 ); > SET @aux = NULL; > INSERT INTO test.t1 ( col1,pk ) VALUES (1,3), ( @aux,4 ) ; > Warnings: > Warning 1265 Data truncated for column 'col1' at row 2 > SELECT * FROM test.t1 WHERE pk = 4; > col1 pk > 4 This happens because @aux has the STRING type and the UCS2 character set fixed (cached) at the beginning of INSERT statment. Setting value in trigger does not affect those cached values, but it does change the value of user variable. So, after execution of the trigger we have user variable of type STRING, character set UCS2, with value 0x01. Byte-sequence 0x01 is not a valid UCS2-string, that's why we get this warning and NULL value in t1.
[16 Apr 2010 11:43]
Matthias Leich
Hi Alexander, it looks like you are right.
[5 May 2010 11:01]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/107485 3035 Alexander Nozdrin 2010-05-05 Patch for Bug#50511 (Sometimes wrong handling of user variables containing NULL). The bug happened under the following condition: - there was a user variable of type REAL, containing NULL value - there was a table with a NOT_NULL column of any type but REAL, having default value (or auto increment); - a row was inserted into the table with the user variable as value. A warning was emitted here. The problem was that handling of NULL values of REAL type was not properly implemented: it didn't expect that REAL NULL value can be assigned to other data type. Basically, the problem was that set_field_to_null() was used instead of set_field_to_null_with_conversions(). The fix is to use the right function, or more generally, to allow conversion of REAL NULL values to other data types.
[5 May 2010 13:58]
Alexander Nozdrin
Queued into trunk-bugfixing.
[7 May 2010 9:22]
Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100507091908-vqyhpwf2km0aokno) (version source revid:alik@sun.com-20100507091737-12vceffs11elb25g) (merge vers: 6.0.14-alpha) (pib:16)
[7 May 2010 9:22]
Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100507091655-349gwq21ursz8y4p) (version source revid:alik@sun.com-20100507091655-349gwq21ursz8y4p) (merge vers: 5.5.5-m3) (pib:16)
[7 May 2010 9:23]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100507091823-nzao4h3qosau4tin) (version source revid:alik@sun.com-20100507091720-ib9r8uny2aeazvas) (pib:16)
[7 May 2010 20:01]
Paul DuBois
Noted in 5.5.5, 6.0.14 changelogs. User-defined variables of type REAL that contained NULL were handled improperly when assigned to a column of another type.