Bug #120736 FORCE INDEX make a query with OR (NOT NULL column <=> NULL) degrade from an index range scan to a full table scan

• Submitted: 22 Jun 6:46
• Reporter: Frank Ma
• Status: Open
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 9.6.0
• OS: Any
• CPU Architecture: Any

[22 Jun 6:46] Frank Ma

Description:
For a NOT NULL column, col <=> NULL is impossible because the column can never contain NULL.

The range optimizer does not recognize this predicate as an impossible range condition before checking candidate indexes. If the index on that column is not available, for example due to FORCE INDEX(i2) or IGNORE INDEX(i1), get_mm_parts() cannot build a range tree for key1 <=> NULL and returns NULL.

Then, for the OR condition: key2 = 45 OR key1 <=> NULL

the optimizer effectively does: tree_or(i2_range_tree, NULL) -> NULL

So the entire OR range tree is discarded. As a result, the optimizer does not evaluate the valid range scan on i2, and chooses a full table scan instead.

How to repeat:
DROP TABLE IF EXISTS t0;

CREATE TABLE t0 (
  key1 INT NOT NULL,
  key2 INT NOT NULL,
  key3 INT NOT NULL,
  key4 INT NOT NULL,
  key5 INT NOT NULL,
  key6 INT NOT NULL,
  key7 INT NOT NULL,
  key8 INT NOT NULL,
  INDEX i1(key1),
  INDEX i2(key2),
  INDEX i3(key3),
  INDEX i4(key4),
  INDEX i5(key5),
  INDEX i6(key6),
  INDEX i7(key7),
  INDEX i8(key8)
);

INSERT INTO t0 VALUES
  (1,1,1,1,1,1,1,1023),
  (2,2,2,2,2,2,2,1022);

INSERT INTO t0 SELECT key1+2,  key2+2,  key3+2,  key4+2,  key5+2,  key6+2,  key7+2,  key8-2  FROM t0;
INSERT INTO t0 SELECT key1+4,  key2+4,  key3+4,  key4+4,  key5+4,  key6+4,  key7+4,  key8-4  FROM t0;
INSERT INTO t0 SELECT key1+8,  key2+8,  key3+8,  key4+8,  key5+8,  key6+8,  key7+8,  key8-8  FROM t0;
INSERT INTO t0 SELECT key1+16, key2+16, key3+16, key4+16, key5+16, key6+16, key7+16, key8-16 FROM t0;
INSERT INTO t0 SELECT key1+32, key2+32, key3+32, key4+32, key5+32, key6+32, key7+32, key8-32 FROM t0;
INSERT INTO t0 SELECT key1+64, key2+64, key3+64, key4+64, key5+64, key6+64, key7+64, key8-64 FROM t0;

ANALYZE TABLE t0;

select version();
+-------------+
| version()   |
+-------------+
| 9.6.0-debug |
+-------------+
1 row in set
Time: 0.004s

explain format=tree select * from t0 where key2 = 45 or key1 <=> null;
+-----------------------------------------------------------------------------+
| EXPLAIN                                                                     |
+-----------------------------------------------------------------------------+
| -> Filter: ((t0.key2 = 45) or (t0.key1 <=> NULL))  (cost=0.71 rows=1)       |
|     -> Index range scan on t0 using i2 over (key2 = 45)  (cost=0.71 rows=1) |
+-----------------------------------------------------------------------------+
1 row in set
Time: 0.005s

explain format=tree select * from t0 force index(i2) where key2 = 45 or key1 <=> null;
+-----------------------------------------------------------------------+
| EXPLAIN                                                               |
+-----------------------------------------------------------------------+
| -> Filter: ((t0.key2 = 45) or (t0.key1 <=> NULL))  (cost=45.1 rows=1) |
|     -> Table scan on t0  (cost=45.1 rows=128)                         |
+-----------------------------------------------------------------------+
1 row in set
Time: 0.005s