MySQL Bugs: #78244: SELECT DISTINCT, wrong results combined with use_index

Bug #78244	SELECT DISTINCT, wrong results combined with use_index_extensions=off
Submitted:	27 Aug 2015 14:52	Modified:	14 Nov 2016 19:11
Reporter:	Daniel G	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.6/5.7	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	distrinct, extension, INDEX, Optimizer, SELECT, use_index_extensions

Description:
SELECT DISTINCT returns incorrect result set when optimizer switch "use_index_extensions=off" is configured, expected result would be 1 value returned.

However with index_extensions turned off, the result is 0 values.

How to repeat:
# Mysql dump data can be found here should the direct link stop working: https://gist.github.com/genisd/256fd16a014d53016854

root@mysql-test:~# mysql -e 'create database testing'
root@mysql-test:~# wget https://gist.github.com/genisd/256fd16a014d53016854/raw/11773f3905dba944840c1f0d35aae1f8ba...

root@mysql-test:~# cat gistfile1.txt | mysql testing
root@mysql-test:~# mysql testing -e 'SELECT DISTINCT fieldb FROM testtable WHERE fielda IN (5222,5223,5226,5228,5232,5236,5238,5244,5247,5251,5252,5254,5257,5260,5262,5266,5270,5273,5275,5277,5279,5283,5286,5288,5293,5296,5299) AND fieldb IN (1489,1500,1508,1533,1602,1625,1764,1829,1843,1857,1861,1866,1903,1933,1994,2032,2162,2165,2166,2167,2168,2190,2231,2244);'
+--------+
| fieldb |
+--------+
|   2032 |
+--------+

root@mysql-test:~# echo "optimizer_switch='use_index_extensions=off'" >> /etc/mysql/my.cnf
root@mysql-test:~# service mysql restart

root@mysql-test:~# mysql testing -e 'SELECT DISTINCT fieldb FROM testtable WHERE fielda IN (5222,5223,5226,5228,5232,5236,5238,5244,5247,5251,5252,5254,5257,5260,5262,5266,5270,5273,5275,5277,5279,5283,5286,5288,5293,5296,5299) AND fieldb IN (1489,1500,1508,1533,1602,1625,1764,1829,1843,1857,1861,1866,1903,1933,1994,2032,2162,2165,2166,2167,2168,2190,2231,2244);'

# No result! 1 value was expected though

root@mysql-test:~# 

Removing the optmizer flag and restarting mysql will yield the correct result again.

Suggested fix:
A workaround is to re-enable the optimizer flag use_index_extensions.

In our case we had a good reason to disabled the index_extension optimizer flag.

Credit's also go to Flip Hess and one of our customer who originally reported the issue.

sample data dump

Attachment: sample-data-dump.sql (application/sql, text), 754.70 KiB.

C:\dbs>c:\dbs\5.6\bin\mysql -uroot --port=3560 -p --prompt="mysql 5.6 > "
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.27 Source distribution PULL: 2015/08/14

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.6 > create database testing;
Query OK, 1 row affected (0.00 sec)

mysql 5.6 > use testing;
Database changed
mysql 5.6 > source c:/dump/gistfile1.txt
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.20 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 55220 rows affected (2.38 sec)
Records: 55220  Duplicates: 0  Warnings: 0

mysql 5.6 > SELECT DISTINCT fieldb FROM testtable WHERE fielda IN (5222,5223,5226,5228,5232,5236,5238,5244,5247,5251,5252,5254,5257,5260,5262,5266,5270,5273,5275,5277,5279,5283,5286,5288,5293,5296,5299) AND fieldb IN (1489,1500,1508,1533,1602,1625,1764,1829,1843,1857,1861,1866,1903,1933,1994,2032,2162,2165,2166,2167,2168,2190,2231,2244);
+--------+
| fieldb |
+--------+
|   2032 |
+--------+
1 row in set (0.00 sec)

mysql 5.6 > exit
Bye

C:\dbs>net stop mysqld56
The MySQLD56 service is stopping.
The MySQLD56 service was stopped successfully.

C:\dbs>net start mysqld56
The MySQLD56 service is starting.
The MySQLD56 service was started successfully.

C:\dbs>c:\dbs\5.6\bin\mysql -uroot --port=3560 -p --prompt="mysql 5.6 > "
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.27 Source distribution PULL: 2015/08/14

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.6 > use testing;
Database changed
mysql 5.6 > SELECT DISTINCT fieldb FROM testtable WHERE fielda IN (5222,5223,5226,5228,5232,5236,5238,5244,5247,5251,5252,5254,5257,5260,5262,5266,5270,5273,5275,5277,5279,5283,5286,5288,5293,5296,5299) AND fieldb IN (1489,1500,1508,1533,1602,1625,1764,1829,1843,1857,1861,1866,1903,1933,1994,2032,2162,2165,2166,2167,2168,2190,2231,2244);
Empty set (0.06 sec)

Thank you for the bug report.

C:\dbs>c:\dbs\5.7\bin\mysql -uroot -p --port=3570 --prompt="mysql 5.7 > "
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.9 Source distribution PULL 2015/08/14

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.7 > use testing
Database changed
mysql 5.7 > SELECT DISTINCT fieldb FROM testtable WHERE fielda IN (5222,5223,5226,5228,5232,5236,5238,5244,5247,5251,5252,5254,5257,5260,5262,5266,5270,5273,5275,5277,5279,5283,5286,5288,5293,5296,5299) AND fieldb IN (1489,1500,1508,1533,1602,1625,1764,1829,1843,1857,1861,1866,1903,1933,1994,2032,2162,2165,2166,2167,2168,2190,2231,2244);
Empty set (0.13 sec)

mysql 5.7 > SHOW VARIABLES LIKE "optimizer_switch"\G
*************************** 1. row ***************************
Variable_name: optimizer_switch
        Value: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=off,condition_fanout_filter=on,derived_merge=on
1 row in set, 1 warning (0.00 sec)

Plan with optimizer_switch='use_index_extensions=on':

+----+-------------+-----------+------------+-------+----------------+--------+---------+------+------+----------+--------------------------+
| id | select_type | table     | partitions | type  | possible_keys  | key    | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-----------+------------+-------+----------------+--------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | testtable | NULL       | range | PRIMARY,fieldb | fieldb | 16      | NULL |  648 |   100.00 | Using where; Using index |
+----+-------------+-----------+------------+-------+----------------+--------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0,00 sec)

Plan with optimizer_switch='use_index_extensions=on':
+----+-------------+-----------+------------+-------+----------------+--------+---------+------+------+----------+---------------------------------------+
| id | select_type | table     | partitions | type  | possible_keys  | key    | key_len | ref  | rows | filtered | Extra                                 |
+----+-------------+-----------+------------+-------+----------------+--------+---------+------+------+----------+---------------------------------------+
|  1 | SIMPLE      | testtable | NULL       | range | PRIMARY,fieldb | fieldb | 8       | NULL |   33 |    50.00 | Using where; Using index for group-by |
+----+-------------+-----------+------------+-------+----------------+--------+---------+------+------+----------+---------------------------------------+
1 row in set, 1 warning (0,00 sec)

With index extensions off, loose index scan is used ("Using index for group-by").  If index extensions is not used,  I would not think loose index scan would be aplicable.  I suspect what happens is that the first entry for each fieldb value is selected from index, regardless of value of fielda.  Condition on fielda is applied afterwards and causes results to be filtered out even if there would be other fielda values that match.  The following seems to confirm that:

mysql> SELECT fielda FROM testtable WHERE fieldb = 2032 ORDER BY fielda LIMIT 2;
+--------+
| fielda |
+--------+
|   5221 |
|   5222 |
+--------+
2 rows in set (0,00 sec)

mysql> SELECT DISTINCT fieldb FROM testtable WHERE fielda IN (5222,5226,5228,5232,5236,5238,5244,5247,5251,5252,5254,5257,5260,5262,5266,5270,5273,5275,5277,5279,5283,5286,5288,5293,5296,5299) AND fieldb IN (1489,1500,1508,1533,1602,1625,1764,1829,1843,1857,1861,1866,1903,1933,1994,2032,2162,2165,2166,2167,2168,2190,2231,2244);
Empty set (0,00 sec)

mysql> SELECT DISTINCT fieldb FROM testtable WHERE fielda IN (5221,5222,5226,5228,5232,5236,5238,5244,5247,5251,5252,5254,5257,5260,5262,5266,5270,5273,5275,5277,5279,5283,5286,5288,5293,5296,5299) AND fieldb IN (1489,1500,1508,1533,1602,1625,1764,1829,1843,1857,1861,1866,1903,1933,1994,2032,2162,2165,2166,2167,2168,2190,2231,2244);
+--------+
| fieldb |
+--------+
|   2032 |
+--------+
1 row in set (0,00 sec)

In other words, if the lowest matching value for fielda is included in IN-list, result will be correct.

NB! Typo in previous comment: Second EXPLAIN output is for use_index_extensions=off

Posted by developer:
 
Noted in 5.6.36, 5.7.18, 8.0.1 changelogs.

With the use_index_extensions flag of the optimizer_switch system
variable disabled, some SELECT DISTINCT queries could return
incorrect results.