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:
None 
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
Description:
My script:
----------
--disable_abort_on_error
--disable_warnings
DROP TABLE IF EXISTS t1;
--enable_warnings

CREATE TABLE t1 ( f1 INT AUTO_INCREMENT, PRIMARY KEY (f1));

INSERT INTO t1 SET f1 = NULL ;

SET @aux = NULL ;
INSERT INTO t1 SET f1 = @aux ;

SET @aux1 = 0.123E-1;
SET @aux1 = NULL;
INSERT INTO t1 SET f1 = @aux1 ;

# Cleanup
DROP TABLE t1;

Result on 5.5.99-m3-debug-log
(mysql-next-mr revno: 2962 2010-01-18)
--------------------------------------
....
CREATE TABLE t1 ( f1 INT AUTO_INCREMENT, PRIMARY KEY (f1));
INSERT INTO t1 SET f1 = NULL ;
SET @aux = NULL ;
INSERT INTO t1 SET f1 = @aux ;
   <-- ok, this is like expected
SET @aux1 = 0.123E-1;
SET @aux1 = NULL;
INSERT INTO t1 SET f1 = @aux1 ;
ERROR 23000: Column 'f1' cannot be null
   <-- Why do I get this ?
       @aux1 IS NULL like @aux in the case above
       except that @aux1 has a bit different history.

The following releases compiled with
./BUILD/compile-pentium64-debug-max
show the same bug
- 5.1.43-debug-log 
  (mysql-5.1-bugteam revno: 3325 2010-01-19)
- 6.0.14-alpha-debug-log
  (mysql-6.0-codebase-bugfixing revno: 3835 2010-01-19)

My environment:
---------------
- Intel Core2Duo (64 Bit)
- Linux OpenSuSE 11.0 (64 Bit)

How to repeat:
See above

Suggested fix:
Please modify the server so that
   INSERT INTO t1 SET f1 = @aux1 ;
is successful but be careful when modifying the
properties of user defined variables.
There are several regression tests which rely
on the following property:
SET @aux = <some value NOT NULL >;
    The data type of the user defined variable
    is set by this statement and depends on
    <some value NOT NULL> .
A succeeding
SET @aux = NULL;
does NOT change the data type of the user defined variable.

One way to show the data type of a user defined variable is
CREATE TABLE t1 AS SELECT @aux AS f1;
SHOW CREATE TABLE t1 + inspect the data type of f1.
[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.