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

Wrong Auto Stats Collection after INSERT ON DUPLICATE

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.4.17
    • None
    • None
    • None

    Description

      Hi,

      After running INSERT ON DUPLICATE statements table stats show rows as 0 momentarily which it may affect optimizer decisions. Please find below the test case for this issue:

      mysql [localhost:10417] {msandbox} (test) > select @@version ;
      +-----------------+
      | @@version       |
      +-----------------+
      | 10.4.17-MariaDB |
      +-----------------+
      1 row in set (0.000 sec)
       
      mysql [localhost:10417] {msandbox} (test) > CREATE TABLE `t1` (
          ->   `c1` int(10) unsigned NOT NULL DEFAULT 0,
          ->   `c2` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
          ->   `c3` varchar(128) DEFAULT NULL,
          ->   `c4` char(1) DEFAULT NULL,
          ->   `c5` tinyint(3) unsigned DEFAULT NULL,
          ->   UNIQUE KEY `uniq` (`c1`,`c3`),
          ->   KEY `c3` (`c3`),
          ->   KEY `c2` (`c2`)
          -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ;
      Query OK, 0 rows affected (0.010 sec)
       
      mysql [localhost:10417] {msandbox} (test) > source test_case.sql
      Query OK, 0 rows affected (0.003 sec)
      ...
       
      mysql [localhost:10417] {msandbox} (test) > show table status like 't1'\G
      *************************** 1. row ***************************
                  Name: t1
                Engine: InnoDB
               Version: 10
            Row_format: Dynamic
                  Rows: 0
        Avg_row_length: 0
           Data_length: 16384
       Max_data_length: 0
          Index_length: 49152
             Data_free: 0
        Auto_increment: NULL
           Create_time: 2020-11-18 17:17:41
           Update_time: 2020-11-18 17:17:53
            Check_time: NULL
             Collation: latin1_swedish_ci
              Checksum: NULL
        Create_options: 
               Comment: 
      Max_index_length: 0
             Temporary: N
      1 row in set (0.001 sec)
       
      mysql [localhost:10417] {msandbox} (test) > show indexes from t1 ;
      +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
      | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
      +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
      | t1    |          0 | uniq     |            1 | c1          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
      | t1    |          0 | uniq     |            2 | c3          | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
      | t1    |          1 | c3       |            1 | c3          | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
      | t1    |          1 | c2       |            1 | c2          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
      +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
      4 rows in set (0.001 sec)
      

      I've attached test_case.sql to reproduce this issue, in case on first attempt doesn't show same behavior, please run it again.

      Regards,

      Attachments

        1. test_case.sql
          15 kB
          Juan Pablo Arruti

        Activity

          People

            Unassigned Unassigned
            juanarru Juan Pablo Arruti
            Votes:
            2 Vote for this issue
            Watchers:
            4 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.