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

SHOW EXPLAIN: Some values are chopped off in SHOW EXPLAIN output

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects versions: None
    • Fix versions: 10.0.0
    • Components: None
    • Labels:
      None
    • Sprint:

      Description

      See 'index_subqu' in the output below:

      id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
      1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
      2	DEPENDENT SUBQUERY	t2	index_subqu	PRIMARY,c	c	5	func	1	Using index; Using where
      

      Standard EXPLAIN looks normal:

      id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
      1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
      2	DEPENDENT SUBQUERY	t2	index_subquery	PRIMARY,c	c	5	func	1	Using index; Using where
      SELECT SUM(a + SLEEP(0.1)) FROM t1 WHERE a IN ( SELECT c FROM t2 WHERE d < b ) OR b < 's';
      

      bzr version-info

      revision-id: psergey@askmonty.org-20120719215203-m2p9cbqb37n0th7n
      date: 2012-07-20 01:52:03 +0400
      build-date: 2012-07-23 03:03:22 +0400
      revno: 3456
      

      Test case (SLEEP is here only to make the query longer, otherwise not needed):

      --source include/have_innodb.inc
      
      CREATE TABLE t1 (a INT, b VARCHAR(35)) ENGINE=InnoDB;
      INSERT INTO t1 VALUES (3989,'Abilene'),(3873,'Akron');
      
      CREATE TABLE t2 (c INT, d VARCHAR(52) PRIMARY KEY, KEY(c)) ENGINE=InnoDB;
      INSERT INTO t2 VALUES (86,'English'),(87,'Russian');
      
      --connect (con1,localhost,root,,)
      --let $con_id = `SELECT CONNECTION_ID()`
      
      send SELECT SUM(a + SLEEP(0.1)) FROM t1 WHERE a IN ( SELECT c FROM t2 WHERE d < b ) OR b < 's';
      
      --connection default
      
      --disable_query_log
      --disable_abort_on_error
      --let $run = 1000
      while ($run)
      {
      	eval SHOW EXPLAIN FOR $con_id;
      	dec $run;
        if (!$mysql_errno)
        {
          let $run = 0;
        }
      }
      --enable_query_log
      --enable_abort_on_error
      --connection con1
      reap;
      
      DROP TABLE t1, t2;
      

      Below are a couple variations:

      See 'col_smallin' – supposed to be 'col_smallint_key'

      1 SIMPLE table10000_aria_int_autoinc index_merge PRIMARY,col_varchar_64_key,col_smallint_key,col_bigint_key,col_varchar_10_key col_varchar_10_key,col_varchar_64_key,col_bigint_key,col_smallin 13,67,9,3  8942 Using sort_union(col_varchar_10_key,col_varchar_<rows>_key,col_bigint_key,col_smallint_key); Using where
      

      See 'col_varchar_1' – usual EXPLAIN shows 'col_varchar_10_key,PRIMARY':

      SIMPLE table10000_innodb_int_autoinc index_merge PRIMARY,col_smallint_key,col_bigint_key,col_varchar_64_key,col_varchar_10_key col_smallint_key,col_bigint_key,col_varchar_64_key,col_varchar_1 3,9,67,13,4  10179 Using sort_union(col_smallint_key,col_bigint_key,col_varchar_64_key,col_varchar_<rows>_key,PRIMARY); Using where
      

        Attachments

          Issue links

            Activity

              People

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

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0 minutes
                  0m
                  Logged:
                  Time Spent - 3 hours
                  3h