Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-17558

Problems with INFORMATION_SCHEMA.TABLES.TABLE_NAME and --lower-case-table-names=0

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
    • 10.4(EOL)
    • Information Schema
    • None

    Description

      I start mysqld on Linux with --lower-case-table-names=0, which is the default on Linux, and run this script:

      CREATE OR REPLACE TABLE t1 (a INT);
      SELECT count(*) FROM information_schema.tables WHERE table_name='t1';
      SELECT count(*) FROM information_schema.tables WHERE table_name='T1';
      SELECT count(*) FROM information_schema.tables WHERE table_name LIKE 't1';
      SELECT count(*) FROM information_schema.tables WHERE table_name LIKE 'T1';
      SELECT COLLATION(table_name) FROM information_schema.tables LIMIT 1;
      

      +----------+
      | count(*) |
      +----------+
      |        1 |
      +----------+
      +----------+
      | count(*) |
      +----------+
      |        0 |
      +----------+
      +----------+
      | count(*) |
      +----------+
      |        2 |
      +----------+
      +----------+
      | count(*) |
      +----------+
      |        2 |
      +----------+
      +-----------------------+
      | COLLATION(table_name) |
      +-----------------------+
      | utf8_general_ci       |
      +-----------------------+
      

      The above results look inconsistent:

      • The collation of the column table_name is reported as case insensitive
      • However the comparison operator (=) is actually done case sensitively
      • LIKE is indeed done case insensitively, but this is wrong: tables T1 and t1 must be uniqie

      This inconsistency can be a reason of troubles when one wants to make a list of unique table names, e.g. in the following scenario:

      CREATE OR REPLACE TABLE t1 (a INT);
      CREATE OR REPLACE TABLE T1 (a INT);
      CREATE OR REPLACE TABLE t2 AS
        SELECT DISTINCT table_name 
          FROM information_schema.tables
          WHERE table_schema='test'
            AND table_name LIKE '%1';
      SELECT * FROM t2;
      

      +------------+
      | table_name |
      +------------+
      | t1         |
      +------------+
      

      DISTINCT changes the comparison style to be case sensitive, even for the operator '='.

      The above result is wrong. Both tables 't1' and 'T1' should be in the result. The are unique table names!

      Note, if I now remove the DISTINCT keyword, it returns a good result:

      CREATE OR REPLACE TABLE t1 (a INT);
      CREATE OR REPLACE TABLE T1 (a INT);
      CREATE OR REPLACE TABLE t2 AS
        SELECT table_name 
          FROM information_schema.tables
          WHERE table_schema='test'
            AND table_name LIKE '%1';
      SELECT * FROM t2;
      

      +------------+
      | table_name |
      +------------+
      | t1         |
      | T1         |
      +------------+
      

      Conclusion:

      The choice of utf8_general_ci for INFORMATION_SCHEMA.TABLES.TABLE_NAME does not look good..
      It does not reflect table name uniqueness.

      Attachments

        Issue Links

          Activity

            People

              bar Alexander Barkov
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.