Bug #46771 Using FLUSH TABLES affects results when querying information_schema

Description:
table_name='Tablea' and table_name='TableA' give different results when querying the information_schema on Windows

'database, table, and trigger names are not case sensitive in Windows'
http://dev.mysql.com/doc/refman/5.1/en/identifier-case-sensitivity.html

How to repeat:
mysql> SELECT DATABASE(),VERSION();
+------------+------------------+
| DATABASE() | VERSION()        |
+------------+------------------+
| test       | 5.1.36-community |
+------------+------------------+
1 row in set (0.00 sec)

mysql> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| tablea         |
| tableb         |
+----------------+
2 rows in set (0.00 sec)

mysql> SHOW CREATE TABLE tablea;
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                           |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| tablea | CREATE TABLE `tablea` (
  `col_A` int(11) DEFAULT NULL,
  `col_B` int(11) DEFAULT NULL,
  KEY `IDX_B` (`col_B`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select Count(index_name) from information_schema.statistics where table_schema='test' and table_name='tablea' and index_name='IDX_B';
+-------------------+
| Count(index_name) |
+-------------------+
|                 1 |
+-------------------+
1 row in set (0.00 sec)

mysql> select Count(index_name) from information_schema.statistics where table_schema='test' and table_name='TableA' and index_name='IDX_B';
+-------------------+
| Count(index_name) |
+-------------------+
|                 0 |
+-------------------+
1 row in set (0.00 sec)

mysql> select Count(index_name) from information_schema.statistics where table_schema='test' and table_name='Tablea' and index_name='IDX_B';
+-------------------+
| Count(index_name) |
+-------------------+
|                 1 |
+-------------------+
1 row in set (0.00 sec)

Suggested fix:
Fix behavior

Thank you for the report.

I can not repeat described behavior with version 5.1.37. Please try with this latest version and inform us if problem still exists.

Confirmed:

----------
mysql> SELECT DATABASE(),VERSION();
+------------+------------------+
| DATABASE() | VERSION()        |
+------------+------------------+
| test       | 5.1.37-community |
+------------+------------------+
1 row in set (0.00 sec)

mysql> select Count(index_name) from information_schema.statistics where
    -> table_schema='test' and table_name='tablea' and index_name='IDX_B';
+-------------------+
| Count(index_name) |
+-------------------+
|                 1 |
+-------------------+
1 row in set (0.07 sec)

mysql> select Count(index_name) from information_schema.statistics where
    -> table_schema='test' and table_name='TableA' and index_name='IDX_B';
+-------------------+
| Count(index_name) |
+-------------------+
|                 1 |
+-------------------+
1 row in set (0.00 sec)

mysql> select Count(index_name) from information_schema.statistics where
    -> table_schema='test' and table_name='Tablea' and index_name='IDX_B';
+-------------------+
| Count(index_name) |
+-------------------+
|                 1 |
+-------------------+
1 row in set (0.00 sec)
----------

Issue seems resolved in 5.1.37. Closing bug.

Verified in 5.1.37:

Testcase:
----------
DROP DATABASE IF EXISTS test2;
CREATE DATABASE test2;
USE test2

CREATE TABLE `tablea` (`col_A` int(11) DEFAULT '1',`col_B` varchar(40) DEFAULT NULL,KEY `IDX_B` (`col_B`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `tableb` (`col_A` int(11) DEFAULT '1',`col_B` varchar(40) DEFAULT NULL,KEY `IDX_B` (`col_B`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;

SELECT VERSION();
SELECT COUNT(index_name) FROM information_schema.statistics WHERE table_schema='test2' AND table_name='tableA';
SELECT COUNT(index_name) FROM information_schema.statistics WHERE table_schema='test2' AND table_name='TableA';
SELECT COUNT(index_name) FROM information_schema.statistics WHERE table_schema='test2' AND table_name='tablea';
----------

Result:
----------
mysql> SELECT VERSION();
+------------------+
| VERSION()        |
+------------------+
| 5.1.37-community |
+------------------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(index_name) FROM information_schema.statistics WHERE table_schema='test2' AND table_name='tableA';
+-------------------+
| COUNT(index_name) |
+-------------------+
|                 1 |
+-------------------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(index_name) FROM information_schema.statistics WHERE table_schema='test2' AND table_name='TableA';
+-------------------+
| COUNT(index_name) |
+-------------------+
|                 0 | /* Incorrect */
+-------------------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(index_name) FROM information_schema.statistics WHERE table_schema='test2' AND table_name='tablea';
+-------------------+
| COUNT(index_name) |
+-------------------+
|                 1 |
+-------------------+
1 row in set (0.00 sec)
----------

Looks like there are more serious issues than the one described above:

Notice the incorrect result of 1 when querying for a non-existing schema:

----
mysql> DROP DATABASE IF EXISTS test;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+-------+------+----------------------------------------------------+
| Level | Code | Message                                            |
+-------+------+----------------------------------------------------+
| Note  | 1008 | Can't drop database 'test'; database doesn't exist |
+-------+------+----------------------------------------------------+
1 row in set (0.00 sec)

mysql> DROP DATABASE IF EXISTS test2;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+-------+------+-----------------------------------------------------+
| Level | Code | Message                                             |
+-------+------+-----------------------------------------------------+
| Note  | 1008 | Can't drop database 'test2'; database doesn't exist |
+-------+------+-----------------------------------------------------+
1 row in set (0.00 sec)

mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.00 sec)

mysql> USE test
Database changed
mysql>
mysql> CREATE TABLE `tablea` (`col_A` int(11) DEFAULT '1',`col_B` varchar(40) DEFAULT NULL,KEY
    -> `IDX_B` (`col_B`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> SELECT VERSION();
+------------------+
| VERSION()        |
+------------------+
| 5.1.37-community |
+------------------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(index_name) FROM information_schema.statistics WHERE table_schema='test2' AND table_name='tableA';
+-------------------+
| COUNT(index_name) |
+-------------------+
|                 1 | /* INCORRECT: there is no table_schema 'test2' ! */
+-------------------+
1 row in set (0.00 sec)

mysql> SELECT table_schema, table_name, index_name FROM information_schema.statistics WHERE table_schema LIKE '%test%';
+--------------+------------+------------+
| table_schema | table_name | index_name |
+--------------+------------+------------+
| test         | tablea     | IDX_B      |
+--------------+------------+------------+
1 row in set (0.00 sec)

mysql> SELECT table_schema, table_name, index_name FROM information_schema.statistics WHERE table_schema = 'test2';
Empty set (0.00 sec)
----

Aha! Cached info:

----
mysql> SELECT COUNT(index_name) FROM information_schema.statistics WHERE table_schema='test2' AND table_name='tableA';
+-------------------+
| COUNT(index_name) |
+-------------------+
|                 1 |
+-------------------+
1 row in set (0.00 sec)

mysql> flush tables;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT COUNT(index_name) FROM information_schema.statistics WHERE table_schema='test2' AND table_name='tableA';
+-------------------+
| COUNT(index_name) |
+-------------------+
|                 0 |
+-------------------+
1 row in set (0.00 sec)
----

A testcase that reproduces the problem reliably:

----
DROP DATABASE test2;
CREATE DATABASE test2;
USE TEST2
CREATE TABLE `TableA` (`col_A` int(11) DEFAULT '1',`col_B` varchar(40) DEFAULT NULL) ENGINE=InnoDB;
CREATE TABLE `TableB` (`col_A` int(11) DEFAULT '1',`col_B` varchar(40) DEFAULT NULL,KEY `IDX_B` (`col_B`)) ENGINE=InnoDB;
DELIMITER $$
DROP PROCEDURE IF EXISTS `sp_AddIndex` $$
CREATE PROCEDURE `sp_AddIndex`
(tblName VARCHAR(64),ndxName VARCHAR(64), colName VARCHAR(64))
BEGIN
DECLARE IndexColumnCount INT;
DECLARE SQLStatement VARCHAR(256);
SELECT COUNT(index_name) INTO IndexColumnCount
FROM information_schema.statistics
WHERE table_schema = database()
AND table_name = tblName
AND index_name = ndxName;
IF IndexColumnCount = 0 THEN
SET SQLStatement = CONCAT('ALTER TABLE ',tblName,' ADD INDEX ',ndxName,' (',colName,')');
SET @SQLStmt = SQLStatement;
PREPARE s FROM @SQLStmt;
EXECUTE s;
DEALLOCATE PREPARE s;
END IF;
END $$
DELIMITER ;
CALL sp_AddIndex('TableA','IDX_B','col_B');
CALL sp_AddIndex('TableA','IDX_B','col_B'); /* Should give ERROR 1061 (42000): Duplicate key name 'IDX_B' */
select count(index_name) from information_schema.statistics where table_schema='test2' and table_name='TableA'; /* Gives incorrect '0' output */
select count(index_name) from information_schema.statistics where table_schema='test2' and table_name='TableB'; /* Correct '1' result */
FLUSH TABLES;
select count(index_name) from information_schema.statistics where table_schema='test2' and table_name='TableA'; /* Correct '1' result [*] */
select count(index_name) from information_schema.statistics where table_schema='test2' and table_name='TableB'; /* Correct '1' result */
----

Now, the interesting thing is that if you re-run this test-case exactly as-is above, instead of the first result being an incorrect '0' the third result (marked with [*]) is an incorrect '0' (while the first one is a correct '1').

Workaround is to only query the table_name using lowercase:

------
mysql> select count(index_name) from information_schema.statistics where table_schema='test2' and table_name='TableA';
+-------------------+
| count(index_name) |
+-------------------+
|                 0 | /* Incorrect result */
+-------------------+
1 row in set (0.00 sec)

mysql> select count(index_name) from information_schema.statistics where table_schema='test2' and table_name='tablea';
+-------------------+
| count(index_name) |
+-------------------+
|                 1 | /* Correct result */
+-------------------+
1 row in set (0.00 sec)
------

This bug was seen on MacOSX with the following settings:

localhost/root:[test2]> show variables like 'lower%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| lower_case_file_system | ON |
| lower_case_table_names | 2 |
+------------------------+-------+

And on Windows with the following settings:

mysql> show variables like 'lower%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | ON    |
| lower_case_table_names | 1     |
+------------------------+-------+
2 rows in set (0.00 sec)

This reminds me of
Bug#34921 comparisons with Information schema tables don't honor collation

To clarify, there are two issues: 
1. Caching problems: FLUSH TABLES affects outcome
2. Casetype comparison problems: TableA <> tablea

Also see bug #25630

Thanks to Paul, some other viable/better workarounds:

1. where lower(table_name)='tablename'
2. where upper(table_name)='tablename'
3. where table_name COLLATE utf8_general_ci ='tablename'

Results:

select count(index_name) from information_schema.statistics where table_schema='test2' and lower(table_name)='tablea'; /* Correct '1' result */
select count(index_name) from information_schema.statistics where table_schema='test2' and upper(table_name)='TABLEB'; /* Correct '1' result */
select count(index_name) from information_schema.statistics where table_schema='test2' and table_name COLLATE utf8_general_ci ='tablea'; /* Correct '1' result */
FLUSH TABLES;
select count(index_name) from information_schema.statistics where table_schema='test2' and lower(table_name)='tablea'; /* Correct '1' result */
select count(index_name) from information_schema.statistics where table_schema='test2' and upper(table_name)='TABLEB'; /* Correct '1' result */
select count(index_name) from information_schema.statistics where table_schema='test2' and table_name COLLATE utf8_general_ci ='tablea'; /* Correct '1' result */

I believe that suggestion 2. in the previous comment should be:

2. where upper(table_name)='TABLENAME'

Re: two issues:

1. Caching problems: FLUSH TABLES affects outcome - see last few lines of '[24 Aug 4:01] Roel Van de Paar' note.

This should be resolved in this bug.

2. Casetype comparison problems: TableA <> tablea
For this part, it's apparently a duplicate of #34921 as per notes from Peter Gulutzan on issue #39754 (note #16/19)

Hence, changing the title to match point 1 only & asking re-triage, mainly for change to W1 (there's no workaround for the FLUSH issue).

Issue #2 described above has been addressed in the documentation here:
http://dev.mysql.com/doc/refman/5.1/en/information-schema.html

As follows:

-------
The definition for character columns (for example, TABLES.TABLE_NAME) is generally VARCHAR(N) CHARACTER SET utf8 where N is at least 64. MySQL uses the default collation for this character set (utf8_general_ci) for all searches, sorts, comparisons, and other string operations on such columns. Values such as table names in INFORMATION_SCHEMA columns are treated as strings, not identifiers, and are not compared using the identifier rules described in Section 8.2.2, “Identifier Case Sensitivity”. If the result of a string operation on an INFORMATION_SCHEMA column differs from expectations, a workaround is to use an explicit COLLATE clause to force a suitable collation (Section 9.1.7.1, “Using COLLATE in SQL Statements”). You can also use the UPPER() or LOWER() function. For example, in a WHERE clause, you might use:

WHERE TABLE_NAME COLLATE utf8_bin = 'City'
WHERE TABLE_NAME COLLATE utf8_general_ci = 'city'
WHERE UPPER(TABLE_NAME) = 'CITY'
WHERE LOWER(TABLE_NAME) = 'city'
-------

Issue #1 remains to be addressed in this bug as noted above.

See http://dev.mysql.com/doc/refman/5.1/en/charset-collation-information-schema.html.

Fixed in 5.1.58