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

SHOW EXPLAIN: Plan produced by SHOW EXPLAIN is different from standard EXPLAIN: type ALL vs 'index_merge', keys is different, 'Using sort_union(a,b);' is missing

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 10.0.0
    • Component/s: None
    • Labels:
      None

      Description

      For the following query

      SELECT a+SLEEP(0.01) FROM t1
      WHERE a IN ( 255, 0 ) OR b BETWEEN 6 AND 129                                     
      ORDER BY b;

      standard EXPLAIN returns

      id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
      1	SIMPLE	t1	index_merge	a,b	a,b	5,5	NULL	8	Using sort_union(a,b); Using where; Using filesort

      SHOW EXPLAIN sometimes produces the same in the beginning, but then switches to

      id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
      1	SIMPLE	t1	ALL	a,b	NULL	NULL	NULL	30	Using where; Using filesort

      bzr version-info

      revision-id: psergey@askmonty.org-20120607081906-rkc1r1wyc9e0n1hj
      date: 2012-06-07 12:19:06 +0400
      revno: 3409

      Reproducible with MyISAM and Aria, with the default optimizer_switch.

      Test case:

       
      CREATE TABLE t1 (a INT, b INT, KEY(a), KEY(b)) ENGINE=MyISAM;
      INSERT INTO t1 VALUES
      (8,0),(128,5050),(5372,8),(234,7596),(2,0),(2907,8930),(1,0),
      (0,5224),(8,7638),(960,5),(9872,1534),(0,2295),(3408,9809),
      (7,0),(1168,0),(2089,5570),(0,205),(88,1018),(0,26528),
      (0,0),(4,5567),(1444,145),(6,0),(1,7535),(7793,534),(70,9),
      (178,1),(44,5),(189,0),(3,0);
       
      SELECT a+SLEEP(0.01) FROM t1
      WHERE a IN ( 255, 0 ) OR b BETWEEN 6 AND 129
      ORDER BY b;
       
      --connect (con1,localhost,root,,)
      let $con_id = `SELECT CONNECTION_ID()`;
       
      EXPLAIN
      SELECT a+SLEEP(0.01) FROM t1
      WHERE a IN ( 255, 0 ) OR b BETWEEN 6 AND 129
      ORDER BY b;
       
      --send
      SELECT a+SLEEP(0.01) FROM t1
      WHERE a IN ( 255, 0 ) OR b BETWEEN 6 AND 129
      ORDER BY b;
       
      --connection default
      let $fail = 100;
      while ($fail)
      {
        --error 0,ER_ERROR_WHEN_EXECUTING_COMMAND
        --eval SHOW EXPLAIN FOR $con_id
        --dec $fail
        if (!$mysql_errno)
        {
          let $fail = 0;
        }
      }
       
      let $pass = 1;
      while ($pass)
      {
        --error 0,ER_ERROR_WHEN_EXECUTING_COMMAND
        --eval SHOW EXPLAIN FOR $con_id
        if ($mysql_errno)
        {
          let $pass = 0;
        }
      }
       
      --connection con1
      --disable_result_log
      --reap

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              psergei Sergei Petrunia
              Reporter:
              elenst Elena Stepanova
              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.