MySQL Bugs: #90877: Wrong result from WHERE EXISTS on an ordered subquery

Bug #90877	Wrong result from WHERE EXISTS on an ordered subquery
Submitted:	15 May 2018 21:35	Modified:	16 May 2018 14:05
Reporter:	Alan Curry	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.7.22	OS:	Any
Assigned to:		CPU Architecture:	x86

Description:
In the query below, the result of the subquery named "s2016" is 12 rows each containing the value 10 in the user_id column. You can test that by changing WHERE EXISTS (...) to WHERE 1=1.

The WHERE EXISTS test should be true for all of those rows, so the result of the overall query should be the same as the result of the s2016 subquery. Instead I get a result containing 0 rows.

Also wrong, and stranger, is what happens if I change WHERE EXISTS to WHERE NOT EXISTS. Then I get a result containing 1 row (with the value 10 in the user_id column). Somehow a WHERE clause has filtered 12 identical rows to 1 row. There is no DISTINCT or GROUP BY anywhere in sight.

Attempts to reduce the query to a smaller test case mostly cause the bug to disappear. For example, the s2016 subquery has an ORDER BY clause. Remove that and the query works. Strangest of all, removing either one of the ORDER BY columns and leaving the other one also makes the query work.

The query plan looks like:
+----+--------------------+-------+------------+------+---------------+------+---------+-------------------+------+----------+----------------------------------------------+
| id | select_type        | table | partitions | type | possible_keys | key  | key_len | ref               | rows | filtered | Extra                                        |
+----+--------------------+-------+------------+------+---------------+------+---------+-------------------+------+----------+----------------------------------------------+
|  1 | PRIMARY            | a     | NULL       | ALL  | k1            | NULL | NULL    | NULL              |    2 |    50.00 | Using where; Using temporary; Using filesort |
|  1 | PRIMARY            | s     | NULL       | ref  | uk1           | uk1  | 10      | test.a.year,const |   12 |   100.00 | Using where; Using index                     |
|  3 | DEPENDENT SUBQUERY | a2017 | NULL       | ref  | k1            | k1   | 10      | const,func        |    1 |   100.00 | Using where; Using index                     |
+----+--------------------+-------+------------+------+---------------+------+---------+-------------------+------+----------+----------------------------------------------+

Of all the variations I've tried, the ones that say "Using filesort" in the plan are the ones that produce incorrect output.

This is a non-critical bug for me since an easy workaround is available: move the ORDER BY to the outer query.

How to repeat:
CREATE TABLE `bugdemo_assignment` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int,
  `location_id` int,
  `year` int,
  PRIMARY KEY (`id`),
  KEY `k1` (`year`,`user_id`)
);

CREATE TABLE `bugdemo_score` (
  `event_type` int,
  `event_subtype` int,
  `user_id` int,
  `year` int
);

INSERT INTO `bugdemo_assignment` (`user_id`, `location_id`, `year`)
VALUES
  (10,50,2016),
  (10,50,2017);

INSERT INTO `bugdemo_score` (`event_subtype`, `event_type`, `user_id`, `year`)
VALUES
  (0,1,10,2016),
  (1,2,10,2016),
  (2,2,10,2016),
  (3,2,10,2016),
  (4,2,10,2016),
  (5,2,10,2016),
  (6,2,10,2016),
  (7,2,10,2016),
  (8,2,10,2016),
  (9,2,10,2016),
  (10,2,10,2016),
  (11,2,10,2016);

SELECT `s2016`.`user_id`
FROM
  (
    SELECT `s`.`user_id`
    FROM `bugdemo_score` `s`
    LEFT JOIN `bugdemo_assignment` `a`
      ON `a`.`user_id` = `s`.`user_id` AND `a`.`year` = `s`.`year`
    WHERE `a`.`location_id` = 50 AND `s`.`user_id` = 10
    ORDER BY `s`.`user_id`, `s`.`event_subtype`
  ) `s2016`
WHERE EXISTS(
  SELECT 1
  FROM `bugdemo_assignment` `a2017`
  WHERE `a2017`.`year` = 2017
    AND `a2017`.`user_id` = `s2016`.`user_id`
    AND `a2017`.`location_id` = 50
);

8.0.11:

Enter password: **********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.11 MySQL Community Server - GPL

Copyright (c) 2000, 2018, 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> use test
Database changed
mysql> CREATE TABLE `bugdemo_assignment` (
    ->   `id` int NOT NULL AUTO_INCREMENT,
    ->   `user_id` int,
    ->   `location_id` int,
    ->   `year` int,
    ->   PRIMARY KEY (`id`),
    ->   KEY `k1` (`year`,`user_id`)
    -> );
Query OK, 0 rows affected (0.18 sec)

mysql>
mysql> CREATE TABLE `bugdemo_score` (
    ->   `event_type` int,
    ->   `event_subtype` int,
    ->   `user_id` int,
    ->   `year` int
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql>
mysql> INSERT INTO `bugdemo_assignment` (`user_id`, `location_id`, `year`)
    -> VALUES
    ->   (10,50,2016),
    ->   (10,50,2017);
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql>
mysql> INSERT INTO `bugdemo_score` (`event_subtype`, `event_type`, `user_id`, `year`)
    -> VALUES
    ->   (0,1,10,2016),
    ->   (1,2,10,2016),
    ->   (2,2,10,2016),
    ->   (3,2,10,2016),
    ->   (4,2,10,2016),
    ->   (5,2,10,2016),
    ->   (6,2,10,2016),
    ->   (7,2,10,2016),
    ->   (8,2,10,2016),
    ->   (9,2,10,2016),
    ->   (10,2,10,2016),
    ->   (11,2,10,2016);
Query OK, 12 rows affected (0.02 sec)
Records: 12  Duplicates: 0  Warnings: 0

mysql> SELECT `s2016`.`user_id`
    -> FROM
    ->   (
    ->     SELECT `s`.`user_id`
    ->     FROM `bugdemo_score` `s`
    ->     LEFT JOIN `bugdemo_assignment` `a`
    ->       ON `a`.`user_id` = `s`.`user_id` AND `a`.`year` = `s`.`year`
    ->     WHERE `a`.`location_id` = 50 AND `s`.`user_id` = 10
    ->     ORDER BY `s`.`user_id`, `s`.`event_subtype`
    ->   ) `s2016`
    -> WHERE EXISTS(
    ->   SELECT 1
    ->   FROM `bugdemo_assignment` `a2017`
    ->   WHERE `a2017`.`year` = 2017
    ->     AND `a2017`.`user_id` = `s2016`.`user_id`
    ->     AND `a2017`.`location_id` = 50
    -> );
+---------+
| user_id |
+---------+
|      10 |
|      10 |
|      10 |
|      10 |
|      10 |
|      10 |
|      10 |
|      10 |
|      10 |
|      10 |
|      10 |
|      10 |
+---------+
12 rows in set (0.01 sec)

mysql>

C:\tmp\mysql-5.7.22-winx64>bin\mysql -uroot -p test
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.22 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, 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> CREATE TABLE `bugdemo_assignment` (
    ->   `id` int NOT NULL AUTO_INCREMENT,
    ->   `user_id` int,
    ->   `location_id` int,
    ->   `year` int,
    ->   PRIMARY KEY (`id`),
    ->   KEY `k1` (`year`,`user_id`)
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql>
mysql> CREATE TABLE `bugdemo_score` (
    ->   `event_type` int,
    ->   `event_subtype` int,
    ->   `user_id` int,
    ->   `year` int
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql>
mysql> INSERT INTO `bugdemo_assignment` (`user_id`, `location_id`, `year`)
    -> VALUES
    ->   (10,50,2016),
    ->   (10,50,2017);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql>
mysql> INSERT INTO `bugdemo_score` (`event_subtype`, `event_type`, `user_id`, `year`)
    -> VALUES
    ->   (0,1,10,2016),
    ->   (1,2,10,2016),
    ->   (2,2,10,2016),
    ->   (3,2,10,2016),
    ->   (4,2,10,2016),
    ->   (5,2,10,2016),
    ->   (6,2,10,2016),
    ->   (7,2,10,2016),
    ->   (8,2,10,2016),
    ->   (9,2,10,2016),
    ->   (10,2,10,2016),
    ->   (11,2,10,2016);
Query OK, 12 rows affected (0.00 sec)
Records: 12  Duplicates: 0  Warnings: 0

mysql>
mysql> SELECT `s2016`.`user_id`
    -> FROM
    ->   (
    ->     SELECT `s`.`user_id`
    ->     FROM `bugdemo_score` `s`
    ->     LEFT JOIN `bugdemo_assignment` `a`
    ->       ON `a`.`user_id` = `s`.`user_id` AND `a`.`year` = `s`.`year`
    ->     WHERE `a`.`location_id` = 50 AND `s`.`user_id` = 10
    ->     ORDER BY `s`.`user_id`, `s`.`event_subtype`
    ->   ) `s2016`
    -> WHERE EXISTS(
    ->   SELECT 1
    ->   FROM `bugdemo_assignment` `a2017`
    ->   WHERE `a2017`.`year` = 2017
    ->     AND `a2017`.`user_id` = `s2016`.`user_id`
    ->     AND `a2017`.`location_id` = 50
    -> );
Empty set (0.00 sec)

mysql> SELECT `s2016`.`user_id`
    -> FROM
    ->   (
    ->     SELECT `s`.`user_id`
    ->     FROM `bugdemo_score` `s`
    ->     LEFT JOIN `bugdemo_assignment` `a`
    ->       ON `a`.`user_id` = `s`.`user_id` AND `a`.`year` = `s`.`year`
    ->     WHERE `a`.`location_id` = 50 AND `s`.`user_id` = 10
    ->     ORDER BY `s`.`user_id`, `s`.`event_subtype` Limit 12
    ->   ) `s2016`
    -> WHERE EXISTS(
    ->   SELECT 1
    ->   FROM `bugdemo_assignment` `a2017`
    ->   WHERE `a2017`.`year` = 2017
    ->     AND `a2017`.`user_id` = `s2016`.`user_id`
    ->     AND `a2017`.`location_id` = 50
    -> );
+---------+
| user_id |
+---------+
|      10 |
|      10 |
|      10 |
|      10 |
|      10 |
|      10 |
|      10 |
|      10 |
|      10 |
|      10 |
|      10 |
|      10 |
+---------+
12 rows in set (0.00 sec)

mysql>

HI,

Thank you very much for your report. However, this is not a bug.

Simply, you have to set one switch properly. This was fixed in 8.0 !!!!

MySQL 8.0 returns 100 % the correct result. But, if you are looking at the EXPLAIN, it is totally different:

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	s	NULL	ALL	NULL	NULL	NULL	NULL	12	10.00	Using where; Using filesort
1	PRIMARY	a	NULL	ref	k1	k1	10	bug.s.year,const	1	50.00	Using where
3	DEPENDENT SUBQUERY	a2017	NULL	ref	k1	k1	10	const,func	1	50.00	Using index condition; Using where

If you, however, set optimiser's switch to : derived_merge=off'

then 5.7 works just fine ...... this is the output from SELECT and from the EXPLAIN SELECT:

user_id
10
10
10
10
10
10
10
10
10
10
10
10

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	<derived2>	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
3	DEPENDENT SUBQUERY	a2017	NULL	ref	k1	k1	10	const,s2016.user_id	1	50.00	Using where
2	DERIVED	s	NULL	ALL	NULL	NULL	NULL	NULL	12	10.00	Using where; Using filesort
2	DERIVED	a	NULL	ref	k1	k1	10	bug.s.year,const	1	50.00	Using where

This is because 8.0 has this switch set permanently to OFF. 5.7 will remain as it is, since 8.0 has got features which enable it to function properly without changing the switch.

Hence, not a bug.

It's not a bug because it doesn't happen in some non-default configuration? This seems like an extreme definition of "not a bug". The query is well-defined, and the output is not what it should be. Optimizations that change output are bugs.

Possibly, but as I wrote, this is too complex to be fixed in 5.7, which is why it is fixed in 8.0.

We can not bring infra-structure  from 8.0 back to 5.7 as it is not possible.

However, that switch is very well documented.

Sinisa,

I do not believe changing optimizer_switch should affect results somehow (unless query fails under SQL mode ONLY_FULL_GROUP_BY)

Once again ...

This bug is fixed in 8.0. Fix is too complex to be ported back to 5.7.