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

SHOW EXPLAIN: Different select_type in plans produced by SHOW EXPLAIN and standard EXPLAIN: 'SUBQUERY' vs 'DEPENDENT SUBQUERY'

Details

    • Bug
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Fixed
    • None
    • 10.0.0
    • None
    • None

    Description

      For the following query

      SELECT * FROM t1, ( SELECT * FROM t2 ) AS alias
      		 
      WHERE a < ALL ( SELECT b FROM t1, t2 WHERE a = b )

      with the default optimizer_switch the usual EXPLAIN returns

      id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
      		 
      1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	20	Using where
      		 
      1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	20	Using join buffer (flat, BNL join)
      		 
      3	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	20	
      3	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	20	Using where; Using join buffer (flat, BNL join)

      while SHOW EXPLAIN produces

      id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
      		 
      1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	20	Using where
      		 
      1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	20	Using join buffer (flat, BNL join)
      		 
      3	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	20	
      3	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	20	Using where; Using join buffer (flat, BNL join)

      bzr version-info

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

      Reproducible with MyISAM, Aria, InnoDB. The test case contains Aria because with MyISAM it is less reliable.

      Test case:

      CREATE TABLE t1 (a INT) ENGINE=Aria;
      		 
      INSERT INTO t1 VALUES
      		 
      (4),(6),(3),(5),(3),(246),(2),(9),(3),(8),
      		 
      (1),(8),(8),(5),(7),(5),(1),(6),(2),(9);
      		 
       
      		 
      CREATE TABLE t2 (b INT) ENGINE=Aria;
      		 
      INSERT INTO t2 VALUES
      		 
      (1),(7),(4),(7),(0),(2),(9),(4),(0),(9),
      		 
      (1),(3),(8),(8),(18),(84),(6),(3),(6),(6);
      		 
       
      		 
      --connect (con1,localhost,root,,)
      		 
      let $con_id = `SELECT CONNECTION_ID()`;
      		 
       
      		 
      EXPLAIN 
      SELECT * FROM t1, ( SELECT * FROM t2 ) AS alias
      		 
      WHERE a < ALL ( SELECT b FROM t1, t2 WHERE a = b );
      		 
       
      		 
      --send
      		 
      SELECT * FROM t1, ( SELECT * FROM t2 ) AS alias
      		 
      WHERE a < ALL ( SELECT b FROM t1, t2 WHERE a = 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

          relates to

          Task - A task that needs to be done. MDEV-165 MWL#182: Explain running statements: finish coding, submit for review

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            psergei Sergei Petrunia added a comment -

            There is an interesting difference: 

            MariaDB [j2]> explain extended SELECT * FROM t1,  t2 AS alias WHERE a < ALL ( SELECT b FROM t1, t2 WHERE a = b );
            		 
            +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+
            		 
            | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                           |
            		 
            +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+
            		 
            |    1 | PRIMARY     | t1    | ALL  | NULL          | NULL | NULL    | NULL |   20 |   100.00 | Using where                                     |
            		 
            |    1 | PRIMARY     | alias | ALL  | NULL          | NULL | NULL    | NULL |   20 |   100.00 | Using join buffer (flat, BNL join)              |
            		 
            |    2 | SUBQUERY    | t1    | ALL  | NULL          | NULL | NULL    | NULL |   20 |   100.00 |                                                 |
            		 
            |    2 | SUBQUERY    | t2    | ALL  | NULL          | NULL | NULL    | NULL |   20 |   100.00 | Using where; Using join buffer (flat, BNL join) |
            		 
            +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+
            		 
            4 rows in set, 1 warning (0.00 sec)
            		 

            MariaDB [j2]> explain extended SELECT * FROM t1, ( SELECT * FROM t2 ) AS alias WHERE a < ALL ( SELECT b FROM t1, t2 WHERE a = b );
            		 
            +------+--------------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+
            		 
            | id   | select_type        | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                           |
            		 
            +------+--------------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+
            		 
            |    1 | PRIMARY            | t1    | ALL  | NULL          | NULL | NULL    | NULL |   20 |   100.00 | Using where                                     |
            		 
            |    1 | PRIMARY            | t2    | ALL  | NULL          | NULL | NULL    | NULL |   20 |   100.00 | Using join buffer (flat, BNL join)              |
            		 
            |    3 | DEPENDENT SUBQUERY | t1    | ALL  | NULL          | NULL | NULL    | NULL |   20 |   100.00 |                                                 |
            		 
            |    3 | DEPENDENT SUBQUERY | t2    | ALL  | NULL          | NULL | NULL    | NULL |   20 |   100.00 | Using where; Using join buffer (flat, BNL join) |
            		 
            +------+--------------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+
            		 
            4 rows in set, 1 warning (0.00 sec)

            psergei Sergei Petrunia added a comment - There is an interesting difference: MariaDB [j2]> explain extended SELECT * FROM t1, t2 AS alias WHERE a < ALL ( SELECT b FROM t1, t2 WHERE a = b ); +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+ | 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | Using where | | 1 | PRIMARY | alias | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | Using join buffer (flat, BNL join) | | 2 | SUBQUERY | t1 | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | | | 2 | SUBQUERY | t2 | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | Using where; Using join buffer (flat, BNL join) | +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+ 4 rows in set, 1 warning (0.00 sec) MariaDB [j2]> explain extended SELECT * FROM t1, ( SELECT * FROM t2 ) AS alias WHERE a < ALL ( SELECT b FROM t1, t2 WHERE a = b ); +------+--------------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+--------------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+ | 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | Using where | | 1 | PRIMARY | t2 | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | Using join buffer (flat, BNL join) | | 3 | DEPENDENT SUBQUERY | t1 | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | | | 3 | DEPENDENT SUBQUERY | t2 | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | Using where; Using join buffer (flat, BNL join) | +------+--------------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+ 4 rows in set, 1 warning (0.00 sec)
            psergei Sergei Petrunia added a comment -

            The difference is a bug in subquery handling. Filed it as https://bugs.launchpad.net/maria/+bug/1013343.

            psergei Sergei Petrunia added a comment - The difference is a bug in subquery handling. Filed it as https://bugs.launchpad.net/maria/+bug/1013343 .
            psergei Sergei Petrunia added a comment -

            With fix for that bug, SHOW EXPLAIN and EXPLAIN outputs are no longer different, both show 'SUBQUERY'.

            psergei Sergei Petrunia added a comment - With fix for that bug, SHOW EXPLAIN and EXPLAIN outputs are no longer different, both show 'SUBQUERY'.

            People

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