Bug #80226 Result type check for SET GLOBAL fails unexpectedly
Submitted: 1 Feb 2016 22:28 Modified: 24 Jan 2018 16:05
Reporter: Ceri Williams Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Errors Severity:S3 (Non-critical)
Version:5.5, 5.6, 5.7, 5.7.10, 8.0.4-rc OS:Any
Assigned to: CPU Architecture:Any
Tags: consistency, error, SET GLOBAL

[1 Feb 2016 22:28] Ceri Williams
Description:
var->check_update_type(value->result_type()) in set_var.cc evaluates the assignment of the global variable differently when a variable is used as part of the assignment value.

How to repeat:
This trivial example shows the issue:

mysql > set @t := 1;
Query OK, 0 rows affected (0.00 sec)

mysql > set global sync_binlog = @t;
Query OK, 0 rows affected (0.00 sec)

All OK and as expected... but

mysql > set @t := 1.00;
Query OK, 0 rows affected (0.00 sec)

mysql > set global sync_binlog = ceil(@t);
ERROR 1232 (42000): Incorrect argument type to variable 'sync_binlog'

mysql > set global sync_binlog = ceil(1.00);
Query OK, 0 rows affected (0.00 sec)

The assignment is treated differently. 

This does not appear to be based upon the data type of the variable though:

mysql > set @t := 134217728;
Query OK, 0 rows affected (0.00 sec)

mysql > set global innodb_buffer_pool_size = ceil(134217728 * 1);
Query OK, 0 rows affected (0.00 sec)

mysql > set global innodb_buffer_pool_size = ceil(@t * 1);
ERROR 1232 (42000): Incorrect argument type to variable 'innodb_buffer_pool_size'

mysql > select @t = (cast(@t as unsigned int) * 1);
+-------------------------------------+
| @t = (cast(@t as unsigned int) * 1) |
+-------------------------------------+
|                                   1 |
+-------------------------------------+
1 row in set (0.00 sec)

Suggested fix:
Make the evaluation consistent
[1 Feb 2016 22:32] Ceri Williams
Here is gdb output showing the code path:

mysql > set global innodb_buffer_pool_sizep = ceil(@@innodb_buffer_pool_size * 2);

Breakpoint 1, set_var::check (this=0x7f6e580065c0, thd=0x7f6e58000ae0)
    at ~/builds/mysql-5.7.10/sql/set_var.cc:717
717       var->do_deprecated_warning(thd);
(gdb) n
715     {
(gdb) n
717       var->do_deprecated_warning(thd);
(gdb) n
718       if (var->is_readonly())
(gdb) n
723       if (!var->check_scope(type))
(gdb) n
729       if ((type == OPT_GLOBAL && check_global_access(thd, SUPER_ACL)))
(gdb) n
732       if (!value)
(gdb) n
736            value->fix_fields(thd, &value)) || value->check_cols(1))
(gdb) n
735       if ((!value->fixed &&
(gdb) n
736            value->fix_fields(thd, &value)) || value->check_cols(1))
(gdb) n
738       if (var->check_update_type(value->result_type()))
(gdb) n
740         my_error(ER_WRONG_TYPE_FOR_VAR, MYF(0), var->name.str);

mysql > set global innodb_buffer_pool_size = ceil(134217728 * 2);

Breakpoint 1, set_var::check (this=0x7f6e58006418, thd=0x7f6e58000ae0) at ~/builds/mysql-5.7.10/sql/set_var.cc:717
717       var->do_deprecated_warning(thd);
(gdb) n
715     {
(gdb) n
717       var->do_deprecated_warning(thd);
(gdb) n
718       if (var->is_readonly())
(gdb) n
723       if (!var->check_scope(type))
(gdb) n
729       if ((type == OPT_GLOBAL && check_global_access(thd, SUPER_ACL)))
(gdb) n
732       if (!value)
(gdb) n
736            value->fix_fields(thd, &value)) || value->check_cols(1))
(gdb) n
735       if ((!value->fixed &&
(gdb) n
736            value->fix_fields(thd, &value)) || value->check_cols(1))
(gdb) n
738       if (var->check_update_type(value->result_type()))
(gdb) n
743       int ret= var->check(thd, this) ? -1 : 0;
(gdb) n
746       if (!ret && type == OPT_GLOBAL)
<snipped>
[2 Feb 2016 6:41] Umesh Shastry
Hello Ceri Williams,

Thank you for the report and test case.

Thanks,
Umesh
[24 Jan 2018 16:05] Ceri Williams
Still present on 8.0.4-rc