Bug #80949 When setting a boolean variable values less than 0 are allowed
Submitted: 4 Apr 2016 20:41 Modified: 5 Apr 2016 8:17
Reporter: Jay Edgar Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Options Severity:S3 (Non-critical)
Version:5.7. 5.7.11, 5.6.29 OS:Any
Assigned to: CPU Architecture:Any
Tags: boolean, variables

[4 Apr 2016 20:41] Jay Edgar
Description:
MySQL allows the the following values when setting a boolean variable:  on/off, true/false, and 1/0.  Any integer value larger than 1 is rejected, but oddly any value less than 0 is not rejected and is interpreted as 1 (on).

I would argue that since all positive numbers greater than 1 are not allowed then all negative numbers should also not be allowed.

Note that the problem doesn't affect bit variables (like 'sql_warnings').

How to repeat:
SET innodb_strict_mode=0;  // succeeds, innodb_strict_mode is set to OFF
SET innodb_strict_mode=1;  // succeeds, innodb_strict_mode is set to ON
SET innodb_strict_mode=2;  // fails, innodb_strict_mode does not change
SET innodb_strict_mode=-1; // succeeds, and innodb_strict_mode is set to ON

Suggested fix:
The following code in sql/sql_plugin.cc in function check_func_bool() should be

  if (value->value_type(value) == MYSQL_VALUE_TYPE_STRING)
  {
    ...
  }
  else
  {
    if (value->val_int(value, &tmp) < 0)
      goto err;
    if (tmp > 1 || tmp < 0)    // <----  New - check for less than 0.
      goto err;
    result= (int) tmp;
  }
[5 Apr 2016 8:17] MySQL Verification Team
Hello Jay Edgar,

Thank you for the report and feedback.

Thanks,
Umesh