Bug #54422 query with = 'variables'
Submitted: 11 Jun 2010 9:21 Modified: 15 Oct 2010 10:44
Reporter: frederic Georges Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Information schema Severity:S3 (Non-critical)
Version:5.1.46, 5.1.47-community OS:Any
Assigned to: Sergei Glukhov CPU Architecture:Any
Tags: regression, variables;query

[11 Jun 2010 9:21] frederic Georges
Description:
I have a table who is name 'variables'
when i run query :

mysql> SELECT COLUMN_DEFAULT, TABLE_NAME
    -> FROM INFORMATION_SCHEMA.COLUMNS
    -> WHERE INFORMATION_SCHEMA.COLUMNS.TABLE_NAME like 'variables';
+----------------+------------+
| COLUMN_DEFAULT | TABLE_NAME |
+----------------+------------+
| NULL           | variables  |
| NULL           | variables  |
| NULL           | variables  |
| NULL           | variables  |
| NULL           | variables  |
| NULL           | variables  |
| NULL           | variables  |
+----------------+------------+
7 rows in set (0.00 sec)

mysql> SELECT COLUMN_DEFAULT, TABLE_NAME
    -> FROM INFORMATION_SCHEMA.COLUMNS
    -> WHERE INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = 'variables';
ERROR 1109 (42S02): Unknown table 'variables' in information_schema

why this error ?

How to repeat:
create a table 'variables' 

execute query : SELECT COLUMN_DEFAULT, TABLE_NAME 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = 'variables'
[11 Jun 2010 9:53] Valeriy Kravchuk
Verified just as described:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 69
Server version: 5.1.46-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table variables(c1 int, c2 int);
Query OK, 0 rows affected (0.11 sec)

mysql> SELECT COLUMN_DEFAULT, TABLE_NAME
    -> FROM INFORMATION_SCHEMA.COLUMNS
    -> WHERE INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = 'variables';
ERROR 1109 (42S02): Unknown table 'variables' in information_schema
mysql> SELECT COLUMN_DEFAULT, TABLE_NAME
    -> FROM INFORMATION_SCHEMA.COLUMNS
    -> WHERE INFORMATION_SCHEMA.COLUMNS.TABLE_NAME LIKE 'variables';
+----------------+------------+
| COLUMN_DEFAULT | TABLE_NAME |
+----------------+------------+
| NULL           | variables  |
| NULL           | variables  |
+----------------+------------+
2 rows in set (0.00 sec)
[11 Jun 2010 9:54] Valeriy Kravchuk
Query works as expected on 5.0.89, so this is a regression bug:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3308 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 20
Server version: 5.0.89-community-nt-log MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table variables(c1 int, c2 int);
Query OK, 0 rows affected (0.92 sec)

mysql> SELECT COLUMN_DEFAULT, TABLE_NAME
    -> FROM INFORMATION_SCHEMA.COLUMNS
    -> WHERE INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = 'variables';
+----------------+------------+
| COLUMN_DEFAULT | TABLE_NAME |
+----------------+------------+
| NULL           | variables  |
| NULL           | variables  |
+----------------+------------+
2 rows in set (0.48 sec)
[21 Jun 2010 6:00] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/111650

3437 Sergey Glukhov	2010-06-21
      Bug#54422 query with = 'variables'
      During creation of the table list of
      processed tables hidden I_S table 'VARIABLES'
      is erroneously added into the table list.
      it leads to ER_UNKNOWN_TABLE error in
      TABLE_LIST::add_table_to_list() function.
      The fix is to skip addition of hidden I_S
      tables into the table list.
     @ mysql-test/r/information_schema.result
        test case
     @ mysql-test/t/information_schema.test
        test case
     @ sql/sql_show.cc
        The fix is to skip addition of hidden I_S
        tables into the table list.
[25 Jun 2010 7:59] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/112173

3443 Sergey Glukhov	2010-06-25
      Bug#54422 query with = 'variables'
      During creation of the table list of
      processed tables hidden I_S table 'VARIABLES'
      is erroneously added into the table list.
      it leads to ER_UNKNOWN_TABLE error in
      TABLE_LIST::add_table_to_list() function.
      The fix is to skip addition of hidden I_S
      tables into the table list.
     @ mysql-test/r/information_schema.result
        test case
     @ mysql-test/t/information_schema.test
        test case
     @ sql/sql_show.cc
        The fix is to skip addition of hidden I_S
        tables into the table list.
[19 Jul 2010 14:35] Bugs System
Pushed into 5.1.49 (revid:build@mysql.com-20100719143034-omcma40sblwmay3x) (version source revid:sergey.glukhov@sun.com-20100625080147-fxzvbn2aa755xner) (merge vers: 5.1.48) (pib:16)
[23 Jul 2010 12:26] Bugs System
Pushed into mysql-trunk 5.5.6-m3 (revid:alik@sun.com-20100723121820-jryu2fuw3pc53q9w) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (merge vers: 5.5.5-m3) (pib:18)
[23 Jul 2010 12:33] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100723121929-90e9zemk3jkr2ocy) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (pib:18)
[28 Jul 2010 19:39] Paul Dubois
Noted in 5.1.49, 5.5.5 changelogs.

Inconsistent checking of the relationship between SHOW statements and
INFORMATION_SCHEMA queries caused such queries to fail sometimes.
[14 Oct 2010 8:34] Bugs System
Pushed into mysql-5.1-telco-7.0 5.1.51-ndb-7.0.20 (revid:martin.skold@mysql.com-20101014082627-jrmy9xbfbtrebw3c) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (merge vers: 5.5.5-m3) (pib:21)
[14 Oct 2010 8:49] Bugs System
Pushed into mysql-5.1-telco-6.3 5.1.51-ndb-6.3.39 (revid:martin.skold@mysql.com-20101014083757-5qo48b86d69zjvzj) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (merge vers: 5.5.5-m3) (pib:21)
[14 Oct 2010 9:03] Bugs System
Pushed into mysql-5.1-telco-6.2 5.1.51-ndb-6.2.19 (revid:martin.skold@mysql.com-20101014084420-y54ecj85j5we27oa) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (merge vers: 5.5.5-m3) (pib:21)
[15 Oct 2010 10:44] Jon Stephens
Already documented in the 5.1.49 changelog. No new changelog entries required. Setting back to Closed.