Bug #74244 Wrong results with stored programs and nullable system variables
Submitted: 7 Oct 2014 6:52 Modified: 5 Nov 2014 16:02
Reporter: Davi Arnaut (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S2 (Serious)
Version:5.6, 5.6.22 OS:Any
Assigned to: CPU Architecture:Any

[7 Oct 2014 6:52] Davi Arnaut
Description:
Reading a system variable with a null value inside a stored program
will cause any subsequent reads to return a null value even though the
variable value might change across invocations of the stored program.

How to repeat:
mysql> use test;
Database changed

mysql> CREATE TABLE t (a INT PRIMARY KEY, b TEXT, FULLTEXT(b)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.14 sec)

mysql> CREATE FUNCTION f() RETURNS TEXT RETURN @@GLOBAL.innodb_ft_aux_table;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@GLOBAL.innodb_ft_aux_table, f();
+------------------------------+------+
| @@GLOBAL.innodb_ft_aux_table | f()  |
+------------------------------+------+
| NULL                         | NULL |
+------------------------------+------+
1 row in set (0.00 sec)

mysql> SET GLOBAL innodb_ft_aux_table="test/t";
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@GLOBAL.innodb_ft_aux_table, f();
+------------------------------+------+
| @@GLOBAL.innodb_ft_aux_table | f()  |
+------------------------------+------+
| test/t                       | NULL |
+------------------------------+------+
1 row in set (0.00 sec)

Suggested fix:
Set null_value to 0 in Item_func_get_system_var::val_str if the value is not null.
[7 Oct 2014 7:10] MySQL Verification Team
Hello Davi,

Thank you for the report and test case.

Thanks,
Umesh
[5 Nov 2014 16:02] Paul DuBois
Noted in 5.7.6 changelog.

Reading a system variable with a NULL value inside a stored program
caused any subsequent reads to return a NULL value even though the
variable value might change across invocations of the stored program.