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

LP:903169 - Created_tmp_tables is incremented with derived_merge=ON

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Duplicate
    • 5.3.12, 5.5.36, 10.0.9
    • N/A
    • Optimizer

    Description

      Created_tmp_tables is incremented even when derived_merge=ON, when temp.tables should not be created.
      One can observe this as follows:

      create table t1 (a int);
      insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
      alter table t1 add b int;
      create table t2 as select * from t1;
       
      set optimizer_switch='derived_merge=off';
      explain select * from (select t1.a, t2.b from t1, t2) T;
      +----+-------------+------------+------+---------------+------+---------+------+------+------------------------------------+
      | id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra                              |
      +----+-------------+------------+------+---------------+------+---------+------+------+------------------------------------+
      |  1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |  100 |                                    |
      |  2 | DERIVED     | t1         | ALL  | NULL          | NULL | NULL    | NULL |   10 |                                    |
      |  2 | DERIVED     | t2         | ALL  | NULL          | NULL | NULL    | NULL |   10 | Using join buffer (flat, BNL join) |
      +----+-------------+------------+------+---------------+------+---------+------+------+------------------------------------+
      3 rows in set (0.01 sec)
       
      flush status;
      select * from (select t1.a, t2.b from t1, t2) T;
      show status like 'created%';
      +-------------------------+-------+
      | Variable_name           | Value |
      +-------------------------+-------+
      | Created_tmp_disk_tables | 0     |
      | Created_tmp_files       | 0     |
      | Created_tmp_tables      | 1     |
      +-------------------------+-------+
      3 rows in set (0.00 sec)
       
      set optimizer_switch='derived_merge=on';
      explain select * from (select t1.a, t2.b from t1, t2) T;
      +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------------+
      | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                              |
      +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------------+
      |  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |   10 |                                    |
      |  1 | SIMPLE      | t2    | ALL  | NULL          | NULL | NULL    | NULL |   10 | Using join buffer (flat, BNL join) |
      +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------------+
      2 rows in set (0.00 sec)
      flush status;
      select * from (select t1.a, t2.b from t1, t2) T;
      show status like 'created%';
      +-------------------------+-------+
      | Variable_name           | Value |
      +-------------------------+-------+
      | Created_tmp_disk_tables | 0     |
      | Created_tmp_files       | 0     |
      | Created_tmp_tables      | 1     |
      +-------------------------+-------+
      3 rows in set (0.00 sec)

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              psergei Sergei Petrunia
              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.