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

Explain output difference in Extra column

    XMLWordPrintable

Details

    Description

      Issue seen in main.show_explain

      Trace output:

      ------------------------
      Capture : trace1.txt
      replay : trace2.txt

      Capture Explain:

      -----------

      +------+-------------+--------+------+---------------+------+---------+------+------+------------------------------------+
      | id   | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra                              |
      +------+-------------+--------+------+---------------+------+---------+------+------+------------------------------------+
      |    1 | PRIMARY     | alias1 | ALL  | NULL          | NULL | NULL    | NULL | 14   |                                    |
      |    1 | PRIMARY     | t2     | ALL  | NULL          | NULL | NULL    | NULL | 20   | Using join buffer (flat, BNL join) |
      |    3 | SUBQUERY    | t3     | ALL  | NULL          | NULL | NULL    | NULL | 20   | Using where                        |
      +------+-------------+--------+------+---------------+------+---------+------+------+------------------------------------+
      
      

      Replay Explain:

      ----------------

      +------+-------------+--------+------+---------------+------+---------+------+------+------------------------------------+
      | id   | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra                              |
      +------+-------------+--------+------+---------------+------+---------+------+------+------------------------------------+
      |    1 | PRIMARY     | alias1 | ALL  | NULL          | NULL | NULL    | NULL | 14   | Using where                        |
      |    1 | PRIMARY     | t2     | ALL  | NULL          | NULL | NULL    | NULL | 20   | Using join buffer (flat, BNL join) |
      |    3 | SUBQUERY    | t3     | ALL  | NULL          | NULL | NULL    | NULL | 20   | Using where                        |
      +------+-------------+--------+------+---------------+------+---------+------+------+------------------------------------+
      

      How to repro:

      ----------------

      set optimizer_record_context=ON;
       
      CREATE TABLE t1 (a INT) ENGINE=MyISAM;
      INSERT INTO t1 VALUES (7),(0),(9),(3),(4),(2),(5),(7),(0),(9),(3),(4),(2),(5);		
       
      CREATE TABLE t2 (b INT, c INT) ENGINE=MyISAM;
      INSERT INTO t2 VALUES 
      (0,4),(8,6),(1,3),(8,5),(9,3),(24,246),(6,2),(1,9),(6,3),(2,8),
      (4,1),(8,8),(4,8),(4,5),(7,7),(4,5),(1,1),(9,6),(4,2),(8,9);
       
      create table t3 like t2;
      insert into t3 select * from t2;
       
      explain 
      SELECT max(a+b+c) FROM t1 AS alias1, ( SELECT * FROM t2 ) AS alias 
      WHERE EXISTS ( SELECT * FROM t3 WHERE b = c )  OR a <= 10;
      SELECT context INTO DUMPFILE 'context1.txt' FROM INFORMATION_SCHEMA.OPTIMIZER_CONTEXT;
      source context1.txt
      

      Attachments

        1. trace2.txt
          12 kB
        2. trace1.txt
          12 kB

        Issue Links

          Activity

            People

              bsrikanth Srikanth Bondalapati
              mariadb-pavithrapandith Pavithra Pandith
              Votes:
              0 Vote for this issue
              Watchers:
              1 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.