Bug #28237 SHOW VARIABLES with WHERE clause returns no results incorrectly
Submitted: 4 May 2007 12:32 Modified: 17 May 2007 11:18
Reporter: Mark Leith Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:5.0.40 OS:Any
Assigned to: Sergei Glukhov CPU Architecture:Any

[4 May 2007 12:32] Mark Leith
Description:
Using WHERE instead of LIKE within SHOW STATUS returns no results, where it should. For instance:

mysql> SHOW SESSION VARIABLES LIKE '%timeout';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| connect_timeout            | 5     | 
| delayed_insert_timeout     | 300   | 
| innodb_lock_wait_timeout   | 50    | 
| innodb_rollback_on_timeout | OFF   | 
| interactive_timeout        | 28800 | 
| net_read_timeout           | 30    | 
| net_write_timeout          | 60    | 
| slave_net_timeout          | 3600  | 
| table_lock_wait_timeout    | 50    | 
| wait_timeout               | 28800 | 
+----------------------------+-------+
10 rows in set (0.00 sec)

mysql> SHOW SESSION VARIABLES WHERE Value LIKE '%timeout';
Empty set (0.01 sec)

SHOW VARIABLES reports correctly:

mysql> SHOW SESSION STATUS LIKE 'key%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Key_blocks_not_flushed | 0     | 
| Key_blocks_unused      | 7247  | 
| Key_blocks_used        | 1     | 
| Key_read_requests      | 7     | 
| Key_reads              | 1     | 
| Key_write_requests     | 4     | 
| Key_writes             | 2     | 
+------------------------+-------+
7 rows in set (0.00 sec)

mysql> SHOW SESSION STATUS WHERE Variable_name LIKE 'key%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Key_blocks_not_flushed | 0     | 
| Key_blocks_unused      | 7247  | 
| Key_blocks_used        | 1     | 
| Key_read_requests      | 7     | 
| Key_reads              | 1     | 
| Key_write_requests     | 4     | 
| Key_writes             | 2     | 
+------------------------+-------+
7 rows in set (0.00 sec)

How to repeat:
SHOW SESSION VARIABLES LIKE '%timeout';
SHOW SESSION VARIABLES WHERE Value LIKE '%timeout';
[4 May 2007 12:34] Mark Leith
Oops, little backwards :)

SHOW VARIABLES is broken, SHOW STATUS is not.
[16 May 2007 10:16] Timour Katchaounov
Upgraded to P2 as the bug report states wrong result.