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

CREATE OR REPLACE TABLE never updates statistical tables.

    XMLWordPrintable

    Details

      Description

      The following test case demonstrates the problem:

      SET use_stat_tables= PREFERABLY;
       
      CREATE TABLE t1 (pk int PRIMARY KEY, c varchar(32));
      INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
      ANALYZE TABLE t1;
      SELECT * FROM t1;
      SELECT * FROM mysql.column_stats;
       
      CREATE OR REPLACE TABLE t1 (pk int PRIMARY KEY, a char(7));
      SELECT * FROM t1;
      SELECT * FROM mysql.column_stats;
       
      DROP TABLE t1;
      

      When running this test case we have:

      MariaDB [test]> SET use_stat_tables= PREFERABLY;
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [test]> 
      MariaDB [test]> CREATE TABLE t1 (pk int PRIMARY KEY, c varchar(32));
      Query OK, 0 rows affected (0.02 sec)
       
      MariaDB [test]> INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
      Query OK, 2 rows affected (0.00 sec)
      Records: 2  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> ANALYZE TABLE t1;
      +---------+---------+----------+-----------------------------------------+
      | Table   | Op      | Msg_type | Msg_text                                |
      +---------+---------+----------+-----------------------------------------+
      | test.t1 | analyze | status   | Engine-independent statistics collected |
      | test.t1 | analyze | status   | OK                                      |
      +---------+---------+----------+-----------------------------------------+
      2 rows in set (0.01 sec)
       
      MariaDB [test]> SELECT * FROM t1;
      +----+------+
      | pk | c    |
      +----+------+
      |  1 | foo  |
      |  2 | bar  |
      +----+------+
      2 rows in set (0.00 sec)
       
      MariaDB [test]> SELECT * FROM mysql.column_stats;
      +---------+------------+-------------+-----------+-----------+-------------+------------+---------------+-----------+-----------+-----------+
      | db_name | table_name | column_name | min_value | max_value | nulls_ratio | avg_length | avg_frequency | hist_size | hist_type | histogram |
      +---------+------------+-------------+-----------+-----------+-------------+------------+---------------+-----------+-----------+-----------+
      | test    | t1         | pk          | 1         | 2         |      0.0000 |     4.0000 |        1.0000 |         0 | NULL      | NULL      |
      | test    | t1         | c           | bar       | foo       |      0.0000 |     3.0000 |        1.0000 |         0 | NULL      | NULL      |
      +---------+------------+-------------+-----------+-----------+-------------+------------+---------------+-----------+-----------+-----------+
      2 rows in set (0.00 sec)
       
      MariaDB [test]> 
      MariaDB [test]> CREATE OR REPLACE TABLE t1 (pk int PRIMARY KEY, a char(7));
      Query OK, 0 rows affected (0.03 sec)
       
      MariaDB [test]> SELECT * FROM t1;
      Empty set (0.00 sec)
       
      MariaDB [test]> SELECT * FROM mysql.column_stats;
      +---------+------------+-------------+-----------+-----------+-------------+------------+---------------+-----------+-----------+-----------+
      | db_name | table_name | column_name | min_value | max_value | nulls_ratio | avg_length | avg_frequency | hist_size | hist_type | histogram |
      +---------+------------+-------------+-----------+-----------+-------------+------------+---------------+-----------+-----------+-----------+
      | test    | t1         | pk          | 1         | 2         |      0.0000 |     4.0000 |        1.0000 |         0 | NULL      | NULL      |
      | test    | t1         | c           | bar       | foo       |      0.0000 |     3.0000 |        1.0000 |         0 | NULL      | NULL      |
      +---------+------------+-------------+-----------+-----------+-------------+------------+---------------+-----------+-----------+-----------+
      2 rows in set (0.00 sec)
      

      We see that after executing

      CREATE OR REPLACE TABLE t1 (pk int PRIMARY KEY, a char(7));
      

      we have the same rows in mysql.column_stats as before the execution.

      The command should have removed obsolete statistics on table t1.

        Attachments

          Activity

            People

            Assignee:
            igor Igor Babaev
            Reporter:
            igor Igor Babaev
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Git Integration