MySQL Bugs: #104670: NO_SKIP_SCAN hint disables skip scan for all indexes

Bug #104670	NO_SKIP_SCAN hint disables skip scan for all indexes
Submitted:	19 Aug 2021 23:00	Modified:	11 Jul 2022 23:07
Reporter:	Manuel Ung	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	8.0.26	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When using NO_SKIP_SCAN(tableA indexB), the expected behaviour is that we only disable using skip scan with indexB but it looks like it can potentially disable skip scan for the whole table.

Quoting from the documentation:
https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html#optimizer-hints-index-level

The NO_SKIP_SCAN hint disables Skip Scan for the specified indexes. If the hint specifies no indexes, Skip Scan is not permitted for the table.

How to repeat:
Run this:
create table t (i int, j int, k int, primary key(i, j, k), key(k));
create table u (i int);
insert into u values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);
insert into t  select a.i, a.i * 10 + b.i, a.i * 100 + b.i * 10 + c.i from u a, u b, u c;
analyze table t;

explain select * from t where j < 2;
explain select /*+ NO_SKIP_SCAN(t k) */ * from t where j < 2;

I get this output:

explain select * from t where j < 2;
id      select_type     table   partitions      type    possible_keys   key     key_len ref     rows    filtered        Extra
1       SIMPLE  t       NULL    range   PRIMARY,k       PRIMARY 8       NULL    333     100.00  Using where; Using index for skip scan
Warnings:
Note    1003    /* select#1 */ select `test`.`t`.`i` AS `i`,`test`.`t`.`j` AS `j`,`test`.`t`.`k` AS `k` from `test`.`t` where (`test`.`t`.`j` < 2)
explain select /*+ NO_SKIP_SCAN(t k) */ * from t where j < 2;
id      select_type     table   partitions      type    possible_keys   key     key_len ref     rows    filtered        Extra
1       SIMPLE  t       NULL    index   NULL    k       4       NULL    1000    33.33   Using where; Using index

Note that we should still be able to use the primary key for skip scan, even though we disallowed skip scan on index k.

Suggested fix:
The hint_table_state call in add_loose_index_scan_and_skip_scan_keys needs to check whether the entire table is disallowed, or just a handful of indexes. It should then add the allowed indexes into join_tab->skip_scan_keys.

Hello Manuel Ung,

Thank you for the report and feedback.
Verified as described.

regards,
Umesh

Documented fix as follows in the MySQL 8.0.31 changelog:

    When a NO_SKIP_SCAN hint referred to a specific index not to be
    used for a table skip scan, all the other possible indexes were
    also ignored, and thus a skip scan was not used for any indexes on
    the table.

    This occurred because processing was not performed for all
    possible keys for a skip scan if the NO_SKIP_SCAN hint was not
    applicable to all indexes.

Closed.