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

[27 Aug 2015 14:52] Daniel G
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.
[27 Aug 2015 14:54] Daniel G
Credit's also go to Flip Hess and one of our customer who originally reported the issue.
[27 Aug 2015 15:51] Daniel G
sample data dump

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

[27 Aug 2015 17:11] Miguel Solorzano
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)
[27 Aug 2015 17:33] Miguel Solorzano
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)
[10 May 2016 8:48] Øystein Grøvlen
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.
[10 May 2016 8:51] Øystein Grøvlen
NB! Typo in previous comment: Second EXPLAIN output is for use_index_extensions=off
[14 Nov 2016 19:11] Paul Dubois
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.