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)
• Status: Closed
• Category: MySQL Server: Stored Routines
• Severity: S2 (Serious)
• Version: 5.6, 5.6.22
• OS: Any
• 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.