MySQL Bugs: #34243: SET var_name = DEFAULT: nonsensical result

Bug #34243	SET var_name = DEFAULT: nonsensical result
Submitted:	1 Feb 2008 23:43	Modified:	19 May 2010 0:10
Reporter:	Paul DuBois	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	any	OS:	Any
Assigned to:	Gleb Shchepa	CPU Architecture:	Any

Description:
Many session system variables can be set to DEFAULT to reset them to the value of the corresponding global variable.

But assigning DEFAULT to a session variable for which there is no global variable results in nonsense.

mysql> SET autocommit = 0;
mysql> SELECT @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+

mysql> SET autocommit = DEFAULT;
mysql> SELECT @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |               
+--------------+

mysql> SET autocommit = 1;
mysql> SELECT @@autocommit;
+--------------+
| @@autocommit | 
+--------------+ 
|            1 | 
+--------------+

mysql> SET autocommit = DEFAULT;
mysql> SELECT @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+

Note that assigning DEFAULT has two different results.

I'm not sure what the semantics *should* be (perhaps it is time to specify what it should be), but it's weird that assigning DEFAULT can leave the value set to different values at different times.

How to repeat:
SET autocommit = 0;
SELECT @@autocommit;
SET autocommit = DEFAULT;
SELECT @@autocommit;
SET autocommit = 1;
SELECT @@autocommit;
SET autocommit = DEFAULT;
SELECT @@autocommit;

If a decision is made that assigning DEFAULT to a session-only variable should indeed have no effect, I suggest that the server should produce an error for SET var_name = DEFAULT rather than silently doing nothing.

Thank you for a bug report. Verified just as described.

Fixed in 5.5.3/6.0.14 by WL#4738.

See 5.5.3/6.0.14 changelog entry on handling of system variables and command-line
options.