Bug #53095 SELECT column_name FROM INFORMATION_SCHEMA.STATISTICS returns nothing
Submitted: 23 Apr 2010 6:58 Modified: 14 Oct 2010 14:49
Reporter: Sveta Smirnova Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Information schema Severity:S3 (Non-critical)
Version:5.1 OS:Any
Assigned to: Georgi Kodinov CPU Architecture:Any
Tags: regression
Triage: Triaged: D2 (Serious)

[23 Apr 2010 6:58] Sveta Smirnova
Description:
SELECT column_name FROM INFORMATION_SCHEMA.STATISTICS returns nothing while SELECT * FROM INFORMATION_SCHEMA.STATISTICS returns correct result set if lower_case_table_names=1

Problem is only repeatable with version 5.1

How to repeat:
$cat bug.test
CREATE TABLE `ttt` (
  `f1` char(3) NOT NULL,
  PRIMARY KEY (`f1`)
) ENGINE=myisam DEFAULT CHARSET=latin1;

SELECT column_name FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_NAME = 'TTT';
SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_NAME = 'TTT';

$cat bug-master.opt
--lower_case_table_names=1
[23 Apr 2010 6:59] Sveta Smirnova
Workaround: use LOWER/UPPER function in query.
[23 Apr 2010 7:02] Sveta Smirnova
Can be duplicate of bug #34921, but strange why it is repeatable with 5.1 only.
[23 Apr 2010 7:52] Sveta Smirnova
Problem is not only with information_schema.statistics, any view where column table_name is referred, like COLUMNS,TABLES etc.
[23 Jun 2010 14:45] 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/111933

3438 Georgi Kodinov	2010-06-23
      Bug #53095: SELECT column_name FROM INFORMATION_SCHEMA.STATISTICS 
      returns nothing
            
      When looking for table or database names inside INFORMATION_SCHEMA
      we must convert the table and column names to lowercase (just as it's
      done in the rest of the server) when lowercase_table_names is non-zero.
      This will allow us to find the same tables that we would find if there
      is no condition.
      
      Fixed by converting to lower case when extracting the database and 
      table name conditions.
      Test case added in a separate file because of the command line option.
[25 Jun 2010 13: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/112206

3438 Georgi Kodinov	2010-06-25
      Bug #53095: SELECT column_name FROM INFORMATION_SCHEMA.STATISTICS 
      returns nothing
            
      When looking for table or database names inside INFORMATION_SCHEMA
      we must convert the table and database names to lowercase (just as it's
      done in the rest of the server) when lowercase_table_names is non-zero.
      This will allow us to find the same tables that we would find if there
      is no condition.
      
      Fixed by converting to lower case when extracting the database and 
      table name conditions.
      Test case added.
[5 Jul 2010 9:57] 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/112868

3463 Georgi Kodinov	2010-06-25
      Bug #53095: SELECT column_name FROM INFORMATION_SCHEMA.STATISTICS 
      returns nothing
            
      When looking for table or database names inside INFORMATION_SCHEMA
      we must convert the table and database names to lowercase (just as it's
      done in the rest of the server) when lowercase_table_names is non-zero.
      This will allow us to find the same tables that we would find if there
      is no condition.
      
      Fixed by converting to lower case when extracting the database and 
      table name conditions.
      Test case added.
[7 Jul 2010 9:16] 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/113005

3468 Georgi Kodinov	2010-07-07
      Addendum to the fix for bug #53095 (failing information_schema.test on windows)
      
      Since the original fix for this bug lowercases the search pattern it's not a 
      good idea to copy the search pattern to the output instead of the real table 
      name found (since, depending on the case mode these two names may differ in 
      case).
      Fixed the infrmation_schema.test failure by making sure the actual table 
      name of an inoformation schema table is passed instead of the lookup pattern
      even when the pattern doesn't contain wildcards.
[9 Jul 2010 12:19] 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/113241

3471 Georgi Kodinov	2010-07-09
      Addendum #2 to bug #53095 : fixed a bad testcase result.
[9 Jul 2010 12:20] 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/113242

3473 Georgi Kodinov	2010-07-09
      Addendum #2 to bug #53095 : fixed a bad testcase result.
[9 Jul 2010 12:56] 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/113251

3108 Georgi Kodinov	2010-07-09
      Addendum #3 to bug #53095 : fixed the wrong mysql-trunk tests.
[21 Jul 2010 15:07] 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/114051

3497 Georgi Kodinov	2010-07-21
      Addendum #4 to bug #53095
      
      SHOW DATABASES LIKE ... was not converting to lowercase on comparison as the
      documentation is suggesting. 
      Fixed it to behave similarly to SHOW TABLES LIKE ... and updated the failing
      on MacOSX lowercase_table2 test case.
[23 Jul 2010 12:23] 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:30] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100723121929-90e9zemk3jkr2ocy) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (pib:18)
[30 Jul 2010 2:56] Paul Dubois
Noted in 5.1.50, 5.5.6 changelogs.

WIth lower_case_table_names set to a nonzero value, searches for 
table or database names in INFORMATION_SCHEMA tables could produce
incorrect results.
[4 Aug 2010 7:53] Bugs System
Pushed into mysql-trunk 5.5.6-m3 (revid:alik@sun.com-20100731131027-1n61gseejyxsqk5d) (version source revid:alik@sun.com-20100731074942-o840woifuqioxxe4) (merge vers: 5.5.6-m3) (pib:18)
[4 Aug 2010 8:04] Bugs System
Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804080001-bny5271e65xo34ig) (version source revid:alik@sun.com-20100731075120-qz9z8c25zum2wgmm) (merge vers: 5.6.99-m4) (pib:18)
[4 Aug 2010 8:20] Bugs System
Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804081533-c1d3rbipo9e8rt1s) (version source revid:alik@sun.com-20100731075120-qz9z8c25zum2wgmm) (merge vers: 5.6.99-m4) (pib:18)
[4 Aug 2010 9:01] Bugs System
Pushed into mysql-next-mr (revid:alik@ibmvm-20100804081630-ntapn8bf9pko9vj3) (version source revid:alik@sun.com-20100731075120-qz9z8c25zum2wgmm) (pib:20)
[4 Aug 2010 23:07] Paul Dubois
Bug does not appear in any released 5.6.x version.
[16 Aug 2010 20:15] Ron Gommers
Sveta,

I'm running MySQL 5.1.46, and just renamed column 'PURCHASE.AMOUNT' to 'PURCHASE.OLDAMOUNT'.

When issuing:
   select * from information_schema.columns
   where table_schema = database()
   and table_name = 'purchase';
MySql correctly reports:
   column 'OLDAMOUNT' DOUBLE

However when issuing
   select * from information_schema.columns
   where table_schema = database()
   and table_name = 'PURCHASE';   -- uppercased table name
MySql incorrectly reports on the "old" column:
   column 'AMOUNT' DOUBLE

I hope this situation is handled as well in the upcoming 5.1.50 release.

I was able to fix this by forcing selections on information_schema
to use lowercase table_names and column_names.

Hope it'll be fixed soon,

Regards,
Ron Gommers.
[19 Aug 2010 15:42] Bugs System
Pushed into mysql-5.1 5.1.51 (revid:build@mysql.com-20100819151858-muaaor6jojb5ouzj) (version source revid:build@mysql.com-20100819151858-muaaor6jojb5ouzj) (merge vers: 5.1.51) (pib:20)
[14 Oct 2010 8:28] 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:martin.skold@mysql.com-20101014082627-jrmy9xbfbtrebw3c) (merge vers: 5.1.51-ndb-7.0.20) (pib:21)
[14 Oct 2010 8:43] 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:martin.skold@mysql.com-20101014083757-5qo48b86d69zjvzj) (merge vers: 5.1.51-ndb-6.3.39) (pib:21)
[14 Oct 2010 8:58] 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:martin.skold@mysql.com-20101014084420-y54ecj85j5we27oa) (merge vers: 5.1.51-ndb-6.2.19) (pib:21)
[14 Oct 2010 14:49] Jon Stephens
Already documented in the 5.1.50 changelog; no new changelog entries required. setting back to Closed state.