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

EXPLAIN+ORDER BY: Different plans produced by SHOW EXPLAIN and EXPLAIN

    Details

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

      Description

      For the following query

      SELECT al1.a FROM t1 AS al1, t1 AS al2 
      WHERE al1.a = al2.a HAVING al1.a != 3 ORDER BY al1.a;

      the plan produced by SHOW EXPLAIN contains 'Using where; Using index' in the Extra field, while normal EXPLAIN only says 'Using index'.

      bzr version-info

      revision-id: psergey@askmonty.org-20120607171922-w5d79pe7733jrh2r
      date: 2012-06-07 21:19:22 +0400
      revno: 3412

      Reproducible with Aria and InnoDB, with the default optimizer_switch.

      SHOW EXPLAIN:

      id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
      1	SIMPLE	al1	index	a	a	4	NULL	98	Using where; Using index
      1	SIMPLE	al2	ref	a	a	4	test.al1.a	11	Using index

      EXPLAIN:

      id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
      1	SIMPLE	al1	index	a	a	4	NULL	8	Using index
      1	SIMPLE	al2	ref	a	a	4	test.al1.a	11	Using index

      Test case:

      CREATE TABLE t1 (a INT NOT NULL, KEY(a)) ENGINE=Aria;
      INSERT INTO t1 VALUES (4),(8),(0),(0),(0),(7),(7),(5),
      (3),(188),(4),(9),(6),(1),(5),(6),(2),(4),(231),(4),
      (3),(3),(7),(6),(7),(9),(4),(4),(2),(1),(2),(194),(2),
      (3),(8),(4),(9),(4),(5),(5),(9),(3),(8),(0),(98),(3),
      (1),(0),(189),(8),(3),(3),(9),(6),(8),(3),(9),(5),(9),
      (2),(2),(5),(8),(6),(9),(0),(3),(6),(5),(8),(2),(120),
      (25),(1),(3),(1),(3),(153),(5),(9),(1),(8),(7),(6),(2),
      (4),(7),(3),(8),(4),(6),(1),(7),(1),(7),(2),(1),(5);
       
      --connect (con1,localhost,root,,)
      let $con_id = `SELECT CONNECTION_ID()`;
       
      EXPLAIN
      SELECT al1.a FROM t1 AS al1, t1 AS al2
      WHERE al1.a = al2.a HAVING al1.a != 3 ORDER BY al1.a;
       
      --send
      SELECT al1.a FROM t1 AS al1, t1 AS al2
      WHERE al1.a = al2.a HAVING al1.a != 3 ORDER BY al1.a;
       
      --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 = 5;
      while ($pass)
      {
        --error 0,ER_ERROR_WHEN_EXECUTING_COMMAND
        --eval SHOW EXPLAIN FOR $con_id
        --dec $pass
        if ($mysql_errno)
        {
          let $pass = 0;
        }
      }
       
      --connection con1
      --disable_result_log
      --reap
       

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                psergey Sergei Petrunia
                Reporter:
                elenst Elena Stepanova
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: