MySQL Bugs: #31672: EXPLAIN indicates multiple column index used when it is not

Bug #31672	EXPLAIN indicates multiple column index used when it is not
Submitted:	17 Oct 2007 18:21	Modified:	12 Dec 2007 13:15
Reporter:	Todd Farmer (OCA)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.0.46	OS:	Any
Assigned to:	Martin Hansson	CPU Architecture:	Any
Tags:	bfsm_2007_10_25

Description:
Given the following table:

CREATE TABLE `type_test` (
  `id` bigint(20) unsigned NOT NULL auto_increment,
  `a` double default NULL,
  `b` double default NULL,
  `c` decimal(10,9) default NULL,
  `d` decimal(10,9) default NULL,
  PRIMARY KEY  (`id`),
  KEY `a` (`a`,`b`),
  KEY `c` (`c`,`d`)
) ENGINE=InnoDB;

the following queries indicate that the full composite key index will be used:

SELECT * FROM type_test WHERE a BETWEEN 0.001 AND 0.003 AND b BETWEEN 0.005 AND 0.008;
SELECT * FROM type_test WHERE c BETWEEN 0.001 AND 0.003 AND d BETWEEN 0.005 AND 0.008;

SELECT * FROM type_test WHERE a >= 0.001 AND a <= 0.003 AND b >= 0.005 AND b <= 0.008;
SELECT * FROM type_test WHERE c >= 0.001 AND c <= 0.003 AND d >= 0.005 AND d <= 0.008;

and yet the following indicates that only the leading column is used:

SELECT * FROM type_test WHERE a > 0.001 AND a < 0.003 AND b > 0.005 AND b < 0.008;
SELECT * FROM type_test WHERE c > 0.001 AND c < 0.003 AND d > 0.005 AND d < 0.008;

The latter appears to be correct.  Checking the handler_read_key and handler_read_next values, all statements increment by exactly the same values (in other words, the actual execution is identical).  But the EXPLAIN output indicates that this is not the case:

mysql> EXPLAIN SELECT * FROM type_test WHERE a BETWEEN 0.001 AND 0.003 AND b BETWEEN 0.005 AND 0.008\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: type_test
         type: range
possible_keys: a
          key: a
      key_len: 18
          ref: NULL
         rows: 105860
        Extra: Using where
1 row in set (1.95 sec)

mysql> EXPLAIN SELECT * FROM type_test WHERE c BETWEEN 0.001 AND 0.003 AND d BETWEEN 0.005 AND 0.008\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: type_test
         type: range
possible_keys: c
          key: c
      key_len: 12
          ref: NULL
         rows: 155718
        Extra: Using where
1 row in set (0.09 sec)

mysql>
mysql> EXPLAIN SELECT * FROM type_test WHERE a >= 0.001 AND a <= 0.003 AND b >= 0.005 AND b <= 0.008\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: type_test
         type: range
possible_keys: a
          key: a
      key_len: 18
          ref: NULL
         rows: 105860
        Extra: Using where
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM type_test WHERE c >= 0.001 AND c <= 0.003 AND d >= 0.005 AND d <= 0.008\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: type_test
         type: range
possible_keys: c
          key: c
      key_len: 12
          ref: NULL
         rows: 155718
        Extra: Using where
1 row in set (0.00 sec)

mysql>
mysql> EXPLAIN SELECT * FROM type_test WHERE a > 0.001 AND a < 0.003 AND b > 0.005 AND b < 0.008\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: type_test
         type: range
possible_keys: a
          key: a
      key_len: 9
          ref: NULL
         rows: 105860
        Extra: Using where
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM type_test WHERE c > 0.001 AND c < 0.003 AND d > 0.005 AND d < 0.008\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: type_test
         type: range
possible_keys: c
          key: c
      key_len: 6
          ref: NULL
         rows: 155718
        Extra: Using where
1 row in set (0.00 sec)

It appears that the EXPLAIN output is incorrect when using < and > (instead of <= and >= or BETWEEN).

How to repeat:
DROP TABLE IF EXISTS type_test;

CREATE TABLE type_test (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 a DOUBLE,
 b DOUBLE,
 c DECIMAL(10,9),
 d DECIMAL(10,9),
 INDEX (a, b),
 INDEX (c, d)
) ENGINE = InnoDB;

INSERT INTO type_test (a, b, c, d) VALUES
 (RAND(), RAND(), RAND(), RAND());

INSERT INTO type_test (a, b, c, d) 
 SELECT a / RAND(), b / RAND(), c / RAND(), d / RAND()
 FROM type_test;

INSERT INTO type_test (a, b, c, d) 
 SELECT a * RAND(), b * RAND(), c * RAND(), d * RAND()
 FROM type_test;
 
INSERT INTO type_test (a, b, c, d) 
 SELECT a / RAND(), b / RAND(), c / RAND(), d / RAND()
 FROM type_test;

INSERT INTO type_test (a, b, c, d) 
 SELECT a * RAND(), b * RAND(), c * RAND(), d * RAND()
 FROM type_test;
 
 INSERT INTO type_test (a, b, c, d) 
  SELECT a / RAND(), b / RAND(), c / RAND(), d / RAND()
  FROM type_test;
 
 INSERT INTO type_test (a, b, c, d) 
  SELECT a * RAND(), b * RAND(), c * RAND(), d * RAND()
  FROM type_test;
  
 INSERT INTO type_test (a, b, c, d) 
  SELECT a / RAND(), b / RAND(), c / RAND(), d / RAND()
  FROM type_test;
 
 INSERT INTO type_test (a, b, c, d) 
  SELECT a * RAND(), b * RAND(), c * RAND(), d * RAND()
 FROM type_test;
 
 INSERT INTO type_test (a, b, c, d) 
  SELECT a / RAND(), b / RAND(), c / RAND(), d / RAND()
  FROM type_test;
 
 INSERT INTO type_test (a, b, c, d) 
  SELECT a * RAND(), b * RAND(), c * RAND(), d * RAND()
  FROM type_test;
  
 INSERT INTO type_test (a, b, c, d) 
  SELECT a / RAND(), b / RAND(), c / RAND(), d / RAND()
  FROM type_test;
 
 INSERT INTO type_test (a, b, c, d) 
  SELECT a * RAND(), b * RAND(), c * RAND(), d * RAND()
 FROM type_test;
 
EXPLAIN SELECT * FROM type_test WHERE a BETWEEN 0.001 AND 0.003 AND b BETWEEN 0.005 AND 0.008\G
EXPLAIN SELECT * FROM type_test WHERE c BETWEEN 0.001 AND 0.003 AND d BETWEEN 0.005 AND 0.008\G

EXPLAIN SELECT * FROM type_test WHERE a >= 0.001 AND a <= 0.003 AND b >= 0.005 AND b <= 0.008\G
EXPLAIN SELECT * FROM type_test WHERE c >= 0.001 AND c <= 0.003 AND d >= 0.005 AND d <= 0.008\G

EXPLAIN SELECT * FROM type_test WHERE a > 0.001 AND a < 0.003 AND b > 0.005 AND b < 0.008\G
EXPLAIN SELECT * FROM type_test WHERE c > 0.001 AND c < 0.003 AND d > 0.005 AND d < 0.008\G

Range access is designed to work this way. As an example, consider a two-part index (a,b) on some table. A query with a range condition such as 

a >= 1 AND b = 10

Gets translated to a two-part range

(1, 10) <= (a, b) <= (+INF, 10)

But for a range with strict enpoint

a > 1 AND b = 10

we end up with a range with strict endpoint 

(1,10) < (a,b) <= (+INF, 10)

The problem is, we can't set such an endpoint, because we'd miss the first match for 10.
Therefore, we always stop adding more keyparts to a range predicate when we
find a strict inequality. So the range instead becomes

(1) < (a) <= (+INF) 

and b = 10 is checked later.