MySQL Bugs: #363: SHOW SESSION VARIABLES LIKE 'x' != SELECT @@SESSION.x

Bug #363	SHOW SESSION VARIABLES LIKE 'x' != SELECT @@SESSION.x
Submitted:	3 May 2003 8:19	Modified:	19 May 19:55
Reporter:	Guilhem Bichot
Status:	Verified
Category:	Server	Severity:	S3 (Non-critical)
Version:	5.0	OS:	Any (all)
Assigned to:		Target Version:
Tags:	affects_connectors
Triage:	Triaged: D5 (Feature request)

Description:
I tested it in 5.0, but may be true for older versions.

MASTER> set global sql_log_bin=0;
ERROR 1228: Variable 'sql_log_bin' is a LOCAL variable and can't be used with SET GLOBAL
MASTER> show session variables like 'sql_log_bin';
Empty set (0.00 sec)
MASTER> select @@session.sql_log_bin;
+-----------------------+
| @@session.sql_log_bin |
+-----------------------+
|                     1 |
+-----------------------+

What I don't understand is why SHOW returned empty set while the SELECT worked.
This is at least inconsistent; I would expect SHOW to return 1.

How to repeat:
show session variables like 'sql_log_bin';
select @@session.sql_log_bin;

Some more strange things :
mysql> select @@session.max_binlog_size;
ERROR 1229: Variable 'max_binlog_size' is a GLOBAL variable and should be set with SET
GLOBAL
mysql> show session variables like 'max_binlog_size';
+-----------------+------------+
| Variable_name   | Value      |
+-----------------+------------+
| max_binlog_size | 1073741824 |
+-----------------+------------+
1 row in set (0.00 sec)

mysql> show global variables like 'max_binlog_size';
+-----------------+------------+
| Variable_name   | Value      |
+-----------------+------------+
| max_binlog_size | 1073741824 |
+-----------------+------------+
1 row in set (0.00 sec)

Apparently SHOW SESSION VARIABLES prints nothing for a session-only variable,
and prints the global value for a global-only variable. It works fine for a
session-and-global variable.
I will check mysqld_show() in sql_show.cc.

Even the current SELECT @@SESSION.xx behaviour may not be consistent.
When I write SELECT @@SESSION.xx, I probably want the value of xx that applies to me,
that is:
- if this variable has a session variant, I want the session value.
- if it does not, i.e. is only global, then the global value is what applies to me, so I
should get the global value, not an error like now (but I should still get an error when
doing SET @@SESSION.xx = ).
Here's an array to sum up the consistent way that should be:
(in [] the current way when it is different) :

                    | x is glob&sess  |   x is glob only   |    x is sess only
--------------------|-----------------|--------------------|--------------------
SELECT @@SESSION.x  |  sess val of x  | glob val of x [err]|      sess val of x
SHOW SESSION LIKE x |  sess val of x  |    glob val of x   |sess val of x[empty]  
SET @@SESSION.x     |  sess val of x  |    error           |      sess val of x         
SELECT @@GLOBAL.x   |  glob val of x  |    glob val of x   |      error
SHOW GLOBAL LIKE x  |  glob val of x  |    glob val of x   |      empty
SET @@GLOBAL.x      |  glob val of x  |    glob val of x   |      error

Note that we must always honour SHOW VARIABLES = SHOW SESSION VARIABLES.

Extension, not bug report.  Should be in worklog, not here.

A small addtion to this:

log_bin stands for 'server is logging data to the binary log'

sql_log_bin stands for 'should data updated in the current thread be logged to the binary
log'

We should probably in the future add all sql_xxx variables to 'show variables'