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

TABLE_STATISTICS and INDEX_STATISTICS are case insensitive with lower-case-table-names=0

Details

    Description

      I execute the following SQL script with a Linux MariaDB server running with --lower-case-table-names=0:

      SET GLOBAL userstat=1;
      		 
      CREATE TABLE t1 (a INT, KEY(a));
      		 
      INSERT INTO t1 VALUES (1),(2),(3),(4);
      		 
      SELECT * FROM t1 ORDER BY a;
      		 
      CREATE TABLE T1 (a INT, KEY(a));
      		 
      INSERT INTO T1 VALUES (1),(2),(3),(4);
      		 
      SELECT * FROM T1 ORDER BY a;


      +--------------+------------+-----------+--------------+------------------------+
      		 
      | TABLE_SCHEMA | TABLE_NAME | ROWS_READ | ROWS_CHANGED | ROWS_CHANGED_X_INDEXES |
      		 
      +--------------+------------+-----------+--------------+------------------------+
      		 
      | test         | T1         |         1 |            1 |                      1 |
      		 
      +--------------+------------+-----------+--------------+------------------------+

      Looks wrong. The statistics for t1 and T1 should have separate records.

      INDEX_STATISTICS has the same problem:

      SELECT * FROM INFORMATION_SCHEMA.INDEX_STATISTICS;


      +--------------+------------+------------+-----------+
      		 
      | TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | ROWS_READ |
      		 
      +--------------+------------+------------+-----------+
      		 
      | test         | T1         | a          |         1 |
      		 
      +--------------+------------+------------+-----------+

      The problem is in these function in sql_connect.cc:

      void init_global_table_stats(void)
      		 
      {
      		 
        my_hash_init(PSI_INSTRUMENT_ME, &global_table_stats, system_charset_info,
      		 
                     max_connections, 0, 0, (my_hash_get_key) get_key_table_stats,
      		 
                    (my_hash_free_key) free_table_stats, 0);
      		 
      }


      void init_global_index_stats(void)
      		 
      {
      		 
        my_hash_init(PSI_INSTRUMENT_ME, &global_index_stats, system_charset_info,
      		 
                     max_connections, 0, 0, (my_hash_get_key) get_key_index_stats,
      		 
                     (my_hash_free_key) free_index_stats, 0);
      		 
      }

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          bar Alexander Barkov created issue -
          bar Alexander Barkov made changes -
          Field Original Value New Value
          Description I execute the following SQL script with a Linux MariaDB server running with --lower-case-table-names=0:
          {code:sql}
          SET GLOBAL userstat=1;
          CREATE OR REPLACE TABLE t1 (a INT), KEY(a));
          INSERT INTO t1 VALUES (1);
          SELECT * FROM t1;
          CREATE OR REPLACE TABLE T1 (a INT, KEY(a));
          INSERT INTO T1 VALUES (2);
          SELECT * FROM T1;
          SELECT * FROM INFORMATION_SCHEMA.TABLE_STATISTICS;
          {code}
          {noformat}
          +--------------+------------+-----------+--------------+------------------------+
          | TABLE_SCHEMA | TABLE_NAME | ROWS_READ | ROWS_CHANGED | ROWS_CHANGED_X_INDEXES |
          +--------------+------------+-----------+--------------+------------------------+
          | test | T1 | 1 | 1 | 1 |
          +--------------+------------+-----------+--------------+------------------------+
          {noformat}

          INDEX_STATISTICS has the same problem:
          {code:sql}
          SELECT * FROM INFORMATION_SCHEMA.INDEX_STATISTICS;
          {code}
          {noformat}
          +--------------+------------+------------+-----------+
          | TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | ROWS_READ |
          +--------------+------------+------------+-----------+
          | test | T1 | a | 1 |
          +--------------+------------+------------+-----------+
          {noformat}


          Looks wrong. The statistics for t1 and T1 should have separate records in both tables

          The problem is in these function in sql_connect.cc:

          {noformat}
          void init_global_table_stats(void)
          {
            my_hash_init(PSI_INSTRUMENT_ME, &global_table_stats, system_charset_info,
                         max_connections, 0, 0, (my_hash_get_key) get_key_table_stats,
                        (my_hash_free_key) free_table_stats, 0);
          }
          {noformat}


          {noformat}
          void init_global_index_stats(void)
          {
            my_hash_init(PSI_INSTRUMENT_ME, &global_index_stats, system_charset_info,
                         max_connections, 0, 0, (my_hash_get_key) get_key_index_stats,
                         (my_hash_free_key) free_index_stats, 0);
          }
          {noformat}
          		I execute the following SQL script with a Linux MariaDB server running with --lower-case-table-names=0:
          {code:sql}
          SET GLOBAL userstat=1;
          CREATE OR REPLACE TABLE t1 (a INT), KEY(a));
          INSERT INTO t1 VALUES (1);
          SELECT * FROM t1;
          CREATE OR REPLACE TABLE T1 (a INT, KEY(a));
          INSERT INTO T1 VALUES (2);
          SELECT * FROM T1;
          SELECT * FROM INFORMATION_SCHEMA.TABLE_STATISTICS;
          {code}
          {noformat}
          +--------------+------------+-----------+--------------+------------------------+
          | TABLE_SCHEMA | TABLE_NAME | ROWS_READ | ROWS_CHANGED | ROWS_CHANGED_X_INDEXES |
          +--------------+------------+-----------+--------------+------------------------+
          | test | T1 | 1 | 1 | 1 |
          +--------------+------------+-----------+--------------+------------------------+
          {noformat}
          Looks wrong. The statistics for t1 and T1 should have separate records in both tables

          INDEX_STATISTICS has the same problem:
          {code:sql}
          SELECT * FROM INFORMATION_SCHEMA.INDEX_STATISTICS;
          {code}
          {noformat}
          +--------------+------------+------------+-----------+
          | TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | ROWS_READ |
          +--------------+------------+------------+-----------+
          | test | T1 | a | 1 |
          +--------------+------------+------------+-----------+
          {noformat}


          The problem is in these function in sql_connect.cc:

          {noformat}
          void init_global_table_stats(void)
          {
            my_hash_init(PSI_INSTRUMENT_ME, &global_table_stats, system_charset_info,
                         max_connections, 0, 0, (my_hash_get_key) get_key_table_stats,
                        (my_hash_free_key) free_table_stats, 0);
          }
          {noformat}


          {noformat}
          void init_global_index_stats(void)
          {
            my_hash_init(PSI_INSTRUMENT_ME, &global_index_stats, system_charset_info,
                         max_connections, 0, 0, (my_hash_get_key) get_key_index_stats,
                         (my_hash_free_key) free_index_stats, 0);
          }
          {noformat}
          bar Alexander Barkov made changes -
          Description I execute the following SQL script with a Linux MariaDB server running with --lower-case-table-names=0:
          {code:sql}
          SET GLOBAL userstat=1;
          CREATE OR REPLACE TABLE t1 (a INT), KEY(a));
          INSERT INTO t1 VALUES (1);
          SELECT * FROM t1;
          CREATE OR REPLACE TABLE T1 (a INT, KEY(a));
          INSERT INTO T1 VALUES (2);
          SELECT * FROM T1;
          SELECT * FROM INFORMATION_SCHEMA.TABLE_STATISTICS;
          {code}
          {noformat}
          +--------------+------------+-----------+--------------+------------------------+
          | TABLE_SCHEMA | TABLE_NAME | ROWS_READ | ROWS_CHANGED | ROWS_CHANGED_X_INDEXES |
          +--------------+------------+-----------+--------------+------------------------+
          | test | T1 | 1 | 1 | 1 |
          +--------------+------------+-----------+--------------+------------------------+
          {noformat}
          Looks wrong. The statistics for t1 and T1 should have separate records in both tables

          INDEX_STATISTICS has the same problem:
          {code:sql}
          SELECT * FROM INFORMATION_SCHEMA.INDEX_STATISTICS;
          {code}
          {noformat}
          +--------------+------------+------------+-----------+
          | TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | ROWS_READ |
          +--------------+------------+------------+-----------+
          | test | T1 | a | 1 |
          +--------------+------------+------------+-----------+
          {noformat}


          The problem is in these function in sql_connect.cc:

          {noformat}
          void init_global_table_stats(void)
          {
            my_hash_init(PSI_INSTRUMENT_ME, &global_table_stats, system_charset_info,
                         max_connections, 0, 0, (my_hash_get_key) get_key_table_stats,
                        (my_hash_free_key) free_table_stats, 0);
          }
          {noformat}


          {noformat}
          void init_global_index_stats(void)
          {
            my_hash_init(PSI_INSTRUMENT_ME, &global_index_stats, system_charset_info,
                         max_connections, 0, 0, (my_hash_get_key) get_key_index_stats,
                         (my_hash_free_key) free_index_stats, 0);
          }
          {noformat}
          		I execute the following SQL script with a Linux MariaDB server running with --lower-case-table-names=0:
          {code:sql}
          SET GLOBAL userstat=1;
          CREATE OR REPLACE TABLE t1 (a INT), KEY(a));
          INSERT INTO t1 VALUES (1);
          SELECT * FROM t1;
          CREATE OR REPLACE TABLE T1 (a INT, KEY(a));
          INSERT INTO T1 VALUES (2);
          SELECT * FROM T1;
          SELECT * FROM INFORMATION_SCHEMA.TABLE_STATISTICS;
          {code}
          {noformat}
          +--------------+------------+-----------+--------------+------------------------+
          | TABLE_SCHEMA | TABLE_NAME | ROWS_READ | ROWS_CHANGED | ROWS_CHANGED_X_INDEXES |
          +--------------+------------+-----------+--------------+------------------------+
          | test | T1 | 1 | 1 | 1 |
          +--------------+------------+-----------+--------------+------------------------+
          {noformat}
          Looks wrong. The statistics for t1 and T1 should have separate records.

          INDEX_STATISTICS has the same problem:
          {code:sql}
          SELECT * FROM INFORMATION_SCHEMA.INDEX_STATISTICS;
          {code}
          {noformat}
          +--------------+------------+------------+-----------+
          | TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | ROWS_READ |
          +--------------+------------+------------+-----------+
          | test | T1 | a | 1 |
          +--------------+------------+------------+-----------+
          {noformat}


          The problem is in these function in sql_connect.cc:

          {noformat}
          void init_global_table_stats(void)
          {
            my_hash_init(PSI_INSTRUMENT_ME, &global_table_stats, system_charset_info,
                         max_connections, 0, 0, (my_hash_get_key) get_key_table_stats,
                        (my_hash_free_key) free_table_stats, 0);
          }
          {noformat}


          {noformat}
          void init_global_index_stats(void)
          {
            my_hash_init(PSI_INSTRUMENT_ME, &global_index_stats, system_charset_info,
                         max_connections, 0, 0, (my_hash_get_key) get_key_index_stats,
                         (my_hash_free_key) free_index_stats, 0);
          }
          {noformat}
          bar Alexander Barkov made changes -
          Description I execute the following SQL script with a Linux MariaDB server running with --lower-case-table-names=0:
          {code:sql}
          SET GLOBAL userstat=1;
          CREATE OR REPLACE TABLE t1 (a INT), KEY(a));
          INSERT INTO t1 VALUES (1);
          SELECT * FROM t1;
          CREATE OR REPLACE TABLE T1 (a INT, KEY(a));
          INSERT INTO T1 VALUES (2);
          SELECT * FROM T1;
          SELECT * FROM INFORMATION_SCHEMA.TABLE_STATISTICS;
          {code}
          {noformat}
          +--------------+------------+-----------+--------------+------------------------+
          | TABLE_SCHEMA | TABLE_NAME | ROWS_READ | ROWS_CHANGED | ROWS_CHANGED_X_INDEXES |
          +--------------+------------+-----------+--------------+------------------------+
          | test | T1 | 1 | 1 | 1 |
          +--------------+------------+-----------+--------------+------------------------+
          {noformat}
          Looks wrong. The statistics for t1 and T1 should have separate records.

          INDEX_STATISTICS has the same problem:
          {code:sql}
          SELECT * FROM INFORMATION_SCHEMA.INDEX_STATISTICS;
          {code}
          {noformat}
          +--------------+------------+------------+-----------+
          | TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | ROWS_READ |
          +--------------+------------+------------+-----------+
          | test | T1 | a | 1 |
          +--------------+------------+------------+-----------+
          {noformat}


          The problem is in these function in sql_connect.cc:

          {noformat}
          void init_global_table_stats(void)
          {
            my_hash_init(PSI_INSTRUMENT_ME, &global_table_stats, system_charset_info,
                         max_connections, 0, 0, (my_hash_get_key) get_key_table_stats,
                        (my_hash_free_key) free_table_stats, 0);
          }
          {noformat}


          {noformat}
          void init_global_index_stats(void)
          {
            my_hash_init(PSI_INSTRUMENT_ME, &global_index_stats, system_charset_info,
                         max_connections, 0, 0, (my_hash_get_key) get_key_index_stats,
                         (my_hash_free_key) free_index_stats, 0);
          }
          {noformat}
          		I execute the following SQL script with a Linux MariaDB server running with --lower-case-table-names=0:
          {code:sql}
          SET GLOBAL userstat=1;
          CREATE OR REPLACE TABLE t1 (a INT, KEY(a));
          INSERT INTO t1 VALUES (1);
          SELECT * FROM t1;
          CREATE OR REPLACE TABLE T1 (a INT, KEY(a));
          INSERT INTO T1 VALUES (2);
          SELECT * FROM T1;
          SELECT * FROM INFORMATION_SCHEMA.TABLE_STATISTICS;
          {code}
          {noformat}
          +--------------+------------+-----------+--------------+------------------------+
          | TABLE_SCHEMA | TABLE_NAME | ROWS_READ | ROWS_CHANGED | ROWS_CHANGED_X_INDEXES |
          +--------------+------------+-----------+--------------+------------------------+
          | test | T1 | 1 | 1 | 1 |
          +--------------+------------+-----------+--------------+------------------------+
          {noformat}
          Looks wrong. The statistics for t1 and T1 should have separate records.

          INDEX_STATISTICS has the same problem:
          {code:sql}
          SELECT * FROM INFORMATION_SCHEMA.INDEX_STATISTICS;
          {code}
          {noformat}
          +--------------+------------+------------+-----------+
          | TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | ROWS_READ |
          +--------------+------------+------------+-----------+
          | test | T1 | a | 1 |
          +--------------+------------+------------+-----------+
          {noformat}


          The problem is in these function in sql_connect.cc:

          {noformat}
          void init_global_table_stats(void)
          {
            my_hash_init(PSI_INSTRUMENT_ME, &global_table_stats, system_charset_info,
                         max_connections, 0, 0, (my_hash_get_key) get_key_table_stats,
                        (my_hash_free_key) free_table_stats, 0);
          }
          {noformat}


          {noformat}
          void init_global_index_stats(void)
          {
            my_hash_init(PSI_INSTRUMENT_ME, &global_index_stats, system_charset_info,
                         max_connections, 0, 0, (my_hash_get_key) get_key_index_stats,
                         (my_hash_free_key) free_index_stats, 0);
          }
          {noformat}
          bar Alexander Barkov made changes -
          Description I execute the following SQL script with a Linux MariaDB server running with --lower-case-table-names=0:
          {code:sql}
          SET GLOBAL userstat=1;
          CREATE OR REPLACE TABLE t1 (a INT, KEY(a));
          INSERT INTO t1 VALUES (1);
          SELECT * FROM t1;
          CREATE OR REPLACE TABLE T1 (a INT, KEY(a));
          INSERT INTO T1 VALUES (2);
          SELECT * FROM T1;
          SELECT * FROM INFORMATION_SCHEMA.TABLE_STATISTICS;
          {code}
          {noformat}
          +--------------+------------+-----------+--------------+------------------------+
          | TABLE_SCHEMA | TABLE_NAME | ROWS_READ | ROWS_CHANGED | ROWS_CHANGED_X_INDEXES |
          +--------------+------------+-----------+--------------+------------------------+
          | test | T1 | 1 | 1 | 1 |
          +--------------+------------+-----------+--------------+------------------------+
          {noformat}
          Looks wrong. The statistics for t1 and T1 should have separate records.

          INDEX_STATISTICS has the same problem:
          {code:sql}
          SELECT * FROM INFORMATION_SCHEMA.INDEX_STATISTICS;
          {code}
          {noformat}
          +--------------+------------+------------+-----------+
          | TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | ROWS_READ |
          +--------------+------------+------------+-----------+
          | test | T1 | a | 1 |
          +--------------+------------+------------+-----------+
          {noformat}


          The problem is in these function in sql_connect.cc:

          {noformat}
          void init_global_table_stats(void)
          {
            my_hash_init(PSI_INSTRUMENT_ME, &global_table_stats, system_charset_info,
                         max_connections, 0, 0, (my_hash_get_key) get_key_table_stats,
                        (my_hash_free_key) free_table_stats, 0);
          }
          {noformat}


          {noformat}
          void init_global_index_stats(void)
          {
            my_hash_init(PSI_INSTRUMENT_ME, &global_index_stats, system_charset_info,
                         max_connections, 0, 0, (my_hash_get_key) get_key_index_stats,
                         (my_hash_free_key) free_index_stats, 0);
          }
          {noformat}
          		I execute the following SQL script with a Linux MariaDB server running with --lower-case-table-names=0:
          {code:sql}
          SET GLOBAL userstat=1;
          CREATE TABLE t1 (a INT, KEY(a));
          INSERT INTO t1 VALUES (1),(2),(3),(4);
          SELECT * FROM t1 ORDER BY a;
          CREATE TABLE T1 (a INT, KEY(a));
          INSERT INTO T1 VALUES (1),(2),(3),(4);
          SELECT * FROM T1 ORDER BY a;
          {code}
          {noformat}
          +--------------+------------+-----------+--------------+------------------------+
          | TABLE_SCHEMA | TABLE_NAME | ROWS_READ | ROWS_CHANGED | ROWS_CHANGED_X_INDEXES |
          +--------------+------------+-----------+--------------+------------------------+
          | test | T1 | 1 | 1 | 1 |
          +--------------+------------+-----------+--------------+------------------------+
          {noformat}
          Looks wrong. The statistics for t1 and T1 should have separate records.

          INDEX_STATISTICS has the same problem:
          {code:sql}
          SELECT * FROM INFORMATION_SCHEMA.INDEX_STATISTICS;
          {code}
          {noformat}
          +--------------+------------+------------+-----------+
          | TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | ROWS_READ |
          +--------------+------------+------------+-----------+
          | test | T1 | a | 1 |
          +--------------+------------+------------+-----------+
          {noformat}


          The problem is in these function in sql_connect.cc:

          {noformat}
          void init_global_table_stats(void)
          {
            my_hash_init(PSI_INSTRUMENT_ME, &global_table_stats, system_charset_info,
                         max_connections, 0, 0, (my_hash_get_key) get_key_table_stats,
                        (my_hash_free_key) free_table_stats, 0);
          }
          {noformat}


          {noformat}
          void init_global_index_stats(void)
          {
            my_hash_init(PSI_INSTRUMENT_ME, &global_index_stats, system_charset_info,
                         max_connections, 0, 0, (my_hash_get_key) get_key_index_stats,
                         (my_hash_free_key) free_index_stats, 0);
          }
          {noformat}
          bar Alexander Barkov made changes -
          Fix Version/s 11.4.2 [ 29633 ]
          Fix Version/s 11.5.1 [ 29634 ]
          Fix Version/s 10.5.26 [ 29832 ]
          Fix Version/s 10.6.19 [ 29833 ]
          Fix Version/s 10.11.9 [ 29834 ]
          Fix Version/s 11.1.6 [ 29835 ]
          Fix Version/s 11.2.5 [ 29836 ]
          Fix Version/s 10.4 [ 22408 ]
          Fix Version/s 11.4 [ 29301 ]
          Resolution Fixed [ 1 ]
          Status Open [ 1 ] Closed [ 6 ]
          dbart Daniel Bartholomew made changes -
          Fix Version/s 11.4.3 [ 29837 ]
          Fix Version/s 11.5.2 [ 29838 ]
          Fix Version/s 11.4.2 [ 29633 ]
          Fix Version/s 11.5.1 [ 29634 ]

          People

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

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

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