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

LP:903169 - Created_tmp_tables is incremented with derived_merge=ON

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Minor
    • Resolution: Duplicate
    • Affects Version/s: 5.3.12, 5.5.36, 10.0.9
    • Fix Version/s: N/A
    • Component/s: Optimizer
    • Labels:

      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

              • Assignee:
                Unassigned
                Reporter:
                psergey Sergei Petrunia
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: