MySQL Bugs: #98279: Skip Scan Range Optimization is not applied on temporary tables

Bug #98279	Skip Scan Range Optimization is not applied on temporary tables
Submitted:	17 Jan 2020 22:49	Modified:	20 Jan 2020 16:07
Reporter:	Saverio Miroddi	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	8.0.16	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When a table is temporary, the skip scan range optimization is not applied.

It's not clear if this is intended (therefore, it's a documentation issue) or if it's intended (thefore, it's a bug).

How to repeat:
Use the example provided at https://dev.mysql.com/doc/refman/8.0/en/range-optimization.html, but use a TEMPORARY table. A full index scan will be performed instead of a skip scan range.

We are unable to see the problem with the test case used in the manual page mentioned. Here is what we tried. Please let us know if this is what you wanted us to look into.

CREATE TABLE t1 (f1 INT NOT NULL, f2 INT NOT NULL, PRIMARY KEY(f1, f2));
INSERT INTO t1 VALUES
  (1,1), (1,2), (1,3), (1,4), (1,5),
  (2,1), (2,2), (2,3), (2,4), (2,5);
INSERT INTO t1 SELECT f1, f2 + 5 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 10 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 20 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 40 FROM t1;
ANALYZE TABLE t1;

mysql> EXPLAIN SELECT f1, f2 FROM t1 WHERE f2 > 40;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                                  |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | range | PRIMARY       | PRIMARY | 8       | NULL |   53 |   100.00 | Using where; Using index for skip scan |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> CREATE TEMPORARY TABLE t2 LIKE t1;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t2 SELECT * FROM t1;
Query OK, 160 rows affected (0.07 sec)
Records: 160  Duplicates: 0  Warnings: 0

mysql> ANALYZE TABLE t2;
+---------+---------+----------+----------+
| Table   | Op      | Msg_type | Msg_text |
+---------+---------+----------+----------+
| test.t2 | analyze | status   | OK       |
+---------+---------+----------+----------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT f1, f2 FROM t2 WHERE f2 > 40;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                                  |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------+
|  1 | SIMPLE      | t2    | NULL       | range | PRIMARY       | PRIMARY | 8       | NULL |   53 |   100.00 | Using where; Using index for skip scan |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------+
1 row in set, 1 warning (0.01 sec)

mysql> SHOW CREATE TABLE t2;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t2    | CREATE TEMPORARY TABLE `t2` (
  `f1` int(11) NOT NULL,
  `f2` int(11) NOT NULL,
  PRIMARY KEY (`f1`,`f2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

You could give us the test case that is not using skip scan. Thanks.

Hello!

This has been a mistake of mine. I've tested directly on a temporary table, and overlooked the failures of the INSERT ... SELECT statements, which caused the table to contain only 10 records, to whom the Skip Scan Range Optimization is not applied.

Therefore, this is not a bug; apologies again.

Closing as "Not a Bug"; see previous comment.