Bug #83005 When the Optimiser is using index for group-by it often gives wrong results
Submitted: 15 Sep 2016 6:56 Modified: 9 Jan 2017 17:51
Reporter: Yoseph Phillips Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:5.6.10, 5.6.33, 5.7.15, 5.5.52 OS:Any
Assigned to: CPU Architecture:Any
Tags: regression

[15 Sep 2016 6:56] Yoseph Phillips
Description:
When the Optimiser is using index for group-by it often gives wrong results.

This might be related to 81684.

There seems to be a regression between MySQL 5.5 and MySQL 5.6. We have tested this on lots of versions of MySQL and we can reproduce this on 5.6.10, 5.6.33, 5.7.15 and other versions in between such as 5.6.27-log Community Server (GPL) using many different Windows and Linux OSs.

We have also tested on lot of versions of MySQL 5.5 between 5.5.12 and 5.5.49 using many different Windows and Linux OSs, and have not been able to reproduce it on them.

Both of these queries should return the same number of rows, however the first query is often incorrectly returning 23 rows, and the second query if correctly returning 97 rows:
SELECT DISTINCT t.column1 FROM test t WHERE EXISTS (SELECT * FROM DUAL WHERE (t.column3 = 2));

SELECT DISTINCT t.column1 FROM test t WHERE t.column3 = 2; 

Here are the results using 5.6.27-log Community Server (GPL):

Here is the output of the first query:
7
8
10
18
19
25
27
30
32
37
40
53
63
67
81
99
100
116
118
134
150
158
160

Here is the output of the first query explain:
1, PRIMARY, t, range, testIndex3,testIndex4, testIndex4, 4, , 302, Using where; Using index for group-by
2, DEPENDENT SUBQUERY, , , , , , , , No tables used

We have lots of queries that simplify down to queries like these, all of which reproduce this same problem.

How to repeat:
Run the following code. (Sometimes it seems to help to wait between UNLOCK TABLES; and running the remainder of the code).

DROP TABLE IF EXISTS test;

CREATE TABLE test (
  id int(11) NOT NULL AUTO_INCREMENT,
  column1 int(11) NOT NULL,
  column2 int(11) NOT NULL,
  column3 int(11) DEFAULT NULL,
  PRIMARY KEY(id),
  INDEX testIndex1(column3),
  INDEX testIndex2(column2),
  INDEX testIndex3(column1),
  INDEX testIndex4(column1, column3)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT;

LOCK TABLES test WRITE;
ALTER TABLE test DISABLE KEYS;
INSERT INTO test VALUES (6153,132,7,1),(6154,127,7,1),(6155,31,7,1),(6157,69,7,1),(6158,139,7,1),(6159,26,7,1),(6160,135,7,1),(6161,119,7,1),(6162,136,7,1),(6163,34,7,1),(6164,125,7,1),(6165,131,7,1),(6166,141,7,1),(6167,137,7,1),(6168,138,7,1),(6184,132,7,2),(6185,127,7,2),(6186,31,7,2),(6188,69,7,2),(6189,139,7,2),(6190,26,7,2),(6191,135,7,2),(6192,119,7,2),(6193,136,7,2),(6194,34,7,2),(6195,125,7,2),(6196,131,7,2),(6197,141,7,2),(6198,137,7,2),(6199,138,7,2),(6215,133,7,1),(6216,133,7,2),(7067,77,16,1),(7068,77,16,2),(7069,145,25,1),(7070,145,25,2),(7071,145,19,1),(7072,145,19,2),(7724,130,22,1),(7725,143,22,1),(7726,149,22,1),(7727,130,22,2),(7728,143,22,2),(7729,149,22,2),(7732,151,35,2),(7733,151,22,2),(7735,151,35,1),(7736,151,22,1),(7738,151,35,3),(7739,151,22,3),(7936,156,7,1),(7937,156,7,2),(8437,20,1,1),(8438,17,1,1),(8439,1,1,1),(8441,17,1,2),(8442,1,1,2),(8443,20,1,3),(8446,15,36,1),(8447,15,36,2),(8448,15,36,3),(8510,86,21,1),(8511,86,21,2),(8512,86,21,3),(9275,47,3,1),(9276,15,3,1),(9277,112,3,1),(9278,51,3,1),(9279,146,3,1),(9280,52,3,1),(9281,54,3,1),(9282,13,3,1),(9283,96,3,1),(9284,42,3,1),(9285,84,3,1),(9286,68,3,1),(9287,58,3,1),(9288,44,3,1),(9289,45,3,1),(9290,102,3,1),(9291,111,3,1),(9292,29,3,1),(9306,47,3,2),(9307,15,3,2),(9308,32,3,2),(9309,51,3,2),(9310,146,3,2),(9311,30,3,2),(9312,52,3,2),(9313,53,3,2),(9314,81,3,2),(9315,63,3,2),(9316,96,3,2),(9317,134,3,2),(9318,42,3,2),(9319,84,3,2),(9320,68,3,2),(9321,58,3,2),(9322,1,3,2),(9323,37,3,2),(9324,155,3,2),(9474,20,15,1),(9475,93,15,1),(9476,12,15,1),(9477,17,15,1),(9478,24,15,1),(9479,74,15,1),(9480,94,15,1),(9481,144,15,1),(9482,157,15,1),(9483,123,15,1),(9484,104,15,1),(9489,17,15,2),(9490,24,15,2),(9491,74,15,2),(9492,94,15,2),(9493,144,15,2),(9494,8,15,2),(9495,157,15,2),(9496,59,15,2),(9497,104,15,2),(9498,116,15,2),(9503,117,15,3),(9504,20,15,3),(9505,12,15,3),(9507,24,15,3),(9508,74,15,3),(9509,94,15,3),(9510,144,15,3),(9511,8,15,3),(9512,157,15,3),(9513,123,15,3),(9514,120,15,3),(9515,59,15,3),(9516,104,15,3),(9518,20,10,1),(9519,17,10,1),(9520,120,10,1),(9522,17,10,2),(9523,120,10,2),(9524,20,10,3),(9525,120,10,3),(9565,17,1,3),(9566,17,15,3),(9567,17,10,3),(9572,20,1,2),(9573,20,10,2),(9576,20,15,2),(9579,16,4,1),(9580,20,4,1),(9581,93,4,1),(9582,17,4,1),(9583,108,4,1),(9584,83,4,1),(9585,64,4,1),(9586,36,4,1),(9587,56,4,1),(9588,120,4,1),(9589,103,4,1),(9590,115,4,1),(9591,1,4,1),(9592,21,4,1),(9594,16,4,2),(9595,18,4,2),(9596,20,4,2),(9597,93,4,2),(9598,17,4,2),(9599,83,4,2),(9600,118,4,2),(9601,99,4,2),(9602,56,4,2),(9603,27,4,2),(9604,100,4,2),(9605,120,4,2),(9606,21,4,2),(9609,117,4,3),(9610,16,4,3),(9611,18,4,3),(9612,20,4,3),(9613,93,4,3),(9614,17,4,3),(9615,108,4,3),(9616,83,4,3),(9617,64,4,3),(9618,36,4,3),(9619,118,4,3),(9620,99,4,3),(9621,56,4,3),(9622,27,4,3),(9623,100,4,3),(9624,120,4,3),(9625,103,4,3),(9626,59,4,3),(9627,115,4,3),(9629,21,4,3),(9642,49,15,2),(9645,49,15,3),(9646,138,7,3),(9649,117,15,2),(9651,117,4,2),(9656,117,15,1),(9658,117,4,1),(9661,140,2,1),(9662,5,2,1),(9663,48,2,1),(9664,117,2,1),(9665,61,2,1),(9666,49,2,1),(9667,121,2,1),(9668,41,2,1),(9669,70,2,1),(9670,22,2,1),(9671,11,2,1),(9672,71,2,1),(9673,72,2,1),(9674,114,2,1),(9675,62,2,1),(9676,6,2,1),(9677,23,2,1),(9678,38,2,1),(9679,124,2,1),(9680,142,2,1),(9681,55,2,1),(9682,82,2,1),(9683,39,2,1),(9684,35,2,1),(9685,105,2,1),(9686,109,2,1),(9687,129,2,1),(9689,153,2,1),(9690,43,2,1),(9691,157,2,1),(9692,110,2,1),(9693,106,2,1),(9694,123,2,1),(9695,152,2,1),(9696,104,2,1),(9724,140,2,2),(9725,117,2,2),(9726,49,2,2),(9727,78,2,2),(9728,10,2,2),(9729,150,2,2),(9730,71,2,2),(9731,116,2,2),(9732,23,2,2),(9733,74,2,2),(9734,94,2,2),(9735,142,2,2),(9736,7,2,2),(9737,39,2,2),(9738,158,2,2),(9739,40,2,2),(9740,153,2,2),(9741,43,2,2),(9742,67,2,2),(9743,157,2,2),(9744,110,2,2),(9745,106,2,2),(9746,25,2,2),(9747,101,2,2),(9748,152,2,2),(9749,59,2,2),(9750,60,2,2),(9751,19,2,2),(9752,160,2,2),(9755,140,2,3),(9756,5,2,3),(9757,48,2,3),(9758,61,2,3),(9759,49,2,3),(9760,78,2,3),(9761,10,2,3),(9762,121,2,3),(9763,41,2,3),(9764,70,2,3),(9765,22,2,3),(9766,150,2,3),(9767,11,2,3),(9768,71,2,3),(9769,72,2,3),(9770,114,2,3),(9771,62,2,3),(9772,116,2,3),(9773,6,2,3),(9774,23,2,3),(9775,74,2,3),(9776,94,2,3),(9777,38,2,3),(9778,124,2,3),(9779,142,2,3),(9780,55,2,3),(9781,7,2,3),(9782,82,2,3),(9783,39,2,3),(9784,134,2,3),(9785,35,2,3),(9786,105,2,3),(9787,129,2,3),(9789,158,2,3),(9790,40,2,3),(9791,153,2,3),(9792,43,2,3),(9793,67,2,3),(9794,157,2,3),(9795,110,2,3),(9796,106,2,3),(9797,25,2,3),(9798,123,2,3),(9799,101,2,3),(9800,120,2,3),(9801,152,2,3),(9802,59,2,3),(9803,104,2,3),(9804,60,2,3),(9805,19,2,3),(9806,160,2,3),(9818,161,2,1),(9819,161,2,3),(9849,5,18,1),(9850,47,18,1),(9851,48,18,1),(9852,117,18,1),(9853,112,18,1),(9854,61,18,1),(9855,49,18,1),(9856,16,18,1),(9857,51,18,1),(9858,78,18,1),(9859,121,18,1),(9860,41,18,1),(9861,70,18,1),(9862,22,18,1),(9863,20,18,1),(9864,52,18,1),(9865,93,18,1),(9866,11,18,1),(9867,54,18,1),(9868,72,18,1),(9869,114,18,1),(9870,12,18,1),(9871,62,18,1),(9872,17,18,1),(9873,6,18,1),(9874,23,18,1),(9875,24,18,1),(9876,13,18,1),(9877,96,18,1),(9878,108,18,1),(9879,38,18,1),(9880,124,18,1),(9881,55,18,1),(9882,82,18,1),(9883,39,18,1),(9884,83,18,1),(9885,42,18,1),(9886,35,18,1),(9887,64,18,1),(9888,84,18,1),(9889,68,18,1),(9890,36,18,1),(9891,105,18,1),(9892,109,18,1),(9894,56,18,1),(9895,86,18,1),(9896,43,18,1),(9897,110,18,1),(9898,106,18,1),(9899,58,18,1),(9900,123,18,1),(9901,101,18,1),(9902,120,18,1),(9903,45,18,1),(9904,102,18,1),(9905,103,18,1),(9906,59,18,1),(9907,115,18,1),(9908,104,18,1),(9909,60,18,1),(9910,1,18,1),(9911,111,18,1),(9912,21,18,1),(9913,28,18,1),(9914,29,18,1),(9976,47,18,2),(9977,15,18,2),(9978,117,18,2),(9979,49,18,2),(9980,32,18,2),(9981,77,18,2),(9982,51,18,2),(9983,78,18,2),(9984,18,18,2),(9985,10,18,2),(9986,121,18,2),(9987,146,18,2),(9988,30,18,2),(9989,20,18,2),(9990,52,18,2),(9991,150,18,2),(9992,93,18,2),(9993,71,18,2),(9994,53,18,2),(9995,17,18,2),(9996,116,18,2),(9997,23,18,2),(9998,24,18,2),(9999,81,18,2),(10000,63,18,2),(10001,74,18,2),(10002,94,18,2),(10003,13,18,2),(10004,96,18,2),(10005,142,18,2),(10006,7,18,2),(10007,39,18,2),(10008,83,18,2),(10009,8,18,2),(10010,42,18,2),(10011,84,18,2),(10012,68,18,2),(10013,118,18,2),(10015,99,18,2),(10016,56,18,2),(10017,86,18,2),(10018,158,18,2),(10019,40,18,2),(10020,43,18,2),(10021,67,18,2),(10023,27,18,2),(10024,110,18,2),(10025,106,18,2),(10026,58,18,2),(10027,25,18,2),(10028,100,18,2),(10029,123,18,2),(10030,101,18,2),(10031,120,18,2),(10032,103,18,2),(10033,59,18,2),(10034,1,18,2),(10035,37,18,2),(10036,19,18,2),(10037,28,18,2),(10038,160,18,2),(10039,134,18,2),(10040,153,18,2),(10041,152,18,2),(10042,60,18,2),(10043,155,18,2),(10103,140,18,3),(10104,5,18,3),(10105,47,18,3),(10106,48,18,3),(10107,117,18,3),(10108,112,18,3),(10109,61,18,3),(10110,49,18,3),(10111,32,18,3),(10112,16,18,3),(10113,51,18,3),(10114,78,18,3),(10115,18,18,3),(10116,10,18,3),(10117,121,18,3),(10118,41,18,3),(10119,70,18,3),(10120,146,18,3),(10121,30,18,3),(10122,22,18,3),(10123,20,18,3),(10124,52,18,3),(10125,150,18,3),(10126,93,18,3),(10127,11,18,3),(10128,71,18,3),(10129,53,18,3),(10130,54,18,3),(10131,72,18,3),(10132,114,18,3),(10133,12,18,3),(10134,62,18,3),(10135,17,18,3),(10136,116,18,3),(10137,6,18,3),(10138,23,18,3),(10139,24,18,3),(10140,81,18,3),(10141,63,18,3),(10142,74,18,3),(10143,94,18,3),(10144,13,18,3),(10145,96,18,3),(10146,108,18,3),(10147,38,18,3),(10148,124,18,3),(10149,55,18,3),(10150,7,18,3),(10151,82,18,3),(10152,39,18,3),(10153,83,18,3),(10154,134,18,3),(10155,8,18,3),(10156,42,18,3),(10157,35,18,3),(10158,64,18,3),(10159,84,18,3),(10160,68,18,3),(10161,36,18,3),(10162,118,18,3),(10163,105,18,3),(10164,109,18,3),(10165,129,18,3),(10167,99,18,3),(10168,56,18,3),(10169,86,18,3),(10170,158,18,3),(10171,40,18,3),(10172,153,18,3),(10173,43,18,3),(10174,67,18,3),(10176,27,18,3),(10177,110,18,3),(10178,106,18,3),(10179,58,18,3),(10180,25,18,3),(10181,100,18,3),(10182,123,18,3),(10183,101,18,3),(10184,44,18,3),(10185,120,18,3),(10186,45,18,3),(10187,102,18,3),(10188,152,18,3),(10189,103,18,3),(10190,59,18,3),(10191,115,18,3),(10192,104,18,3),(10193,60,18,3),(10194,1,18,3),(10195,111,18,3),(10196,21,18,3),(10197,155,18,3),(10198,37,18,3),(10199,19,18,3),(10200,28,18,3),(10201,29,18,3),(10202,160,18,3),(10203,142,18,3),(10230,112,37,1),(10231,35,37,1),(10232,111,37,1),(10233,29,37,1),(10234,42,37,1),(10237,32,37,2),(10238,81,37,2),(10239,42,37,2),(10240,84,37,2),(10241,58,37,2),(10244,112,37,3),(10245,32,37,3),(10246,42,37,3),(10247,35,37,3),(10248,84,37,3),(10249,86,37,3),(10250,58,37,3),(10251,111,37,3),(10252,29,37,3),(10253,81,37,3),(10259,91,24,1),(10260,91,24,2),(10261,13,17,1),(10262,155,17,1),(10264,13,17,2),(10265,155,17,2),(10267,13,17,3),(10268,155,17,3),(10320,117,5,1),(10321,20,5,1),(10322,163,5,1),(10323,17,5,1),(10325,120,5,1),(10326,1,5,1),(10327,28,5,1),(10335,117,5,2),(10336,20,5,2),(10337,163,5,2),(10338,17,5,2),(10340,120,5,2),(10341,1,5,2),(10342,28,5,2),(10350,117,5,3),(10351,20,5,3),(10352,163,5,3),(10353,17,5,3),(10355,120,5,3),(10356,1,5,3),(10357,28,5,3),(10365,164,5,1),(10366,164,5,2),(10367,164,5,3),(10368,165,2,1),(10369,165,2,3);
ALTER TABLE test ENABLE KEYS;
UNLOCK TABLES;

SELECT DISTINCT t.column1 FROM test t WHERE EXISTS (SELECT * FROM DUAL WHERE (t.column3 = 2));

EXPLAIN SELECT DISTINCT t.column1 FROM test t WHERE EXISTS (SELECT * FROM DUAL WHERE (t.column3 = 2));

SELECT DISTINCT t.column1 FROM test t WHERE t.column3 = 2;

EXPLAIN SELECT DISTINCT t.column1 FROM test t WHERE t.column3 = 2;
[15 Sep 2016 8:59] Umesh Shastry
Hello Yoseph,

Thank you for the report and test case.
Verified as described with 5.5.52/5.6.33 builds.

Thanks,
Umesh
[15 Sep 2016 9:00] Umesh Shastry
test results

Attachment: 83005.results (application/octet-stream, text), 45.94 KiB.

[9 Jan 2017 17:51] Paul Dubois
Posted by developer:
 
Noted in 5.6.36, 5.7.18, 8.0.1 changelogs.

A query could produce incorrect results if the WHERE clause contained
a dependent subquery, the table had a secondary index on the columns
in the select list followed by the columns in the subquery, and GROUP
BY or DISTINCT permitted the query to use a Loose Index Scan.
[3 May 2017 3:47] Andrew nicols
We're also seeing this in Moodle.

Sample test case:

-- Create the test dataset.
CREATE DATABASE testing;
CONNECT testing;
DROP TABLE IF EXISTS `example`;
CREATE TABLE `example` (
  `id` bigint(10) NOT NULL AUTO_INCREMENT,
  `idnumber` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `relatedid` bigint(10) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `example_idx` (`relatedid`,`idnumber`)
) ENGINE=InnoDB AUTO_INCREMENT=181000 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Example';

INSERT INTO example(relatedid) values (185000);
INSERT INTO example(relatedid) values (185000);
INSERT INTO example(relatedid) values (185000);
INSERT INTO example(relatedid) values (185000);

-- And performing the following query:
SELECT COUNT(DISTINCT(relatedid)) FROM example WHERE id IN (181002, 181001);

The expected result is always 1.

Any of the following will cause the correct result to be returned always:
* swap the order of the unique key around (idnumber, then relatedid)
* disabling use_index_extensions
* including the first row in the table in the IN

Here are the various explains:

-- Default installation with no changes.
mysql> SELECT COUNT(DISTINCT(relatedid)) FROM example WHERE id IN (181002, 181001);
+----------------------------+
| COUNT(DISTINCT(relatedid)) |
+----------------------------+
|                          0 |
+----------------------------+
1 row in set (0.00 sec)

-- Incorrect result.

mysql> EXPLAIN SELECT COUNT(DISTINCT(relatedid)) FROM example WHERE id IN (181002, 181001);
+----+-------------+---------+-------+---------------------+-------------+---------+------+------+--------------------------------------------------+
| id | select_type | table   | type  | possible_keys       | key         | key_len | ref  | rows | Extra                                            |
+----+-------------+---------+-------+---------------------+-------------+---------+------+------+--------------------------------------------------+
|  1 | SIMPLE      | example | range | PRIMARY,example_idx | example_idx | 8       | NULL |    5 | Using where; Using index for group-by (scanning) |
+----+-------------+---------+-------+---------------------+-------------+---------+------+------+--------------------------------------------------+
1 row in set (0.00 sec)

-- Include the first row in the IN:
mysql> SELECT COUNT(DISTINCT(relatedid)) FROM example WHERE id IN (181002, 181000);
+----------------------------+
| COUNT(DISTINCT(relatedid)) |
+----------------------------+
|                          1 |
+----------------------------+
1 row in set (0.00 sec)

-- Correct result.

mysql> EXPLAIN SELECT COUNT(DISTINCT(relatedid)) FROM example WHERE id IN (181002, 181000);
+----+-------------+---------+-------+---------------------+-------------+---------+------+------+--------------------------------------------------+
| id | select_type | table   | type  | possible_keys       | key         | key_len | ref  | rows | Extra                                            |
+----+-------------+---------+-------+---------------------+-------------+---------+------+------+--------------------------------------------------+
|  1 | SIMPLE      | example | range | PRIMARY,example_idx | example_idx | 8       | NULL |    5 | Using where; Using index for group-by (scanning) |
+----+-------------+---------+-------+---------------------+-------------+---------+------+------+--------------------------------------------------+
1 row in set (0.00 sec)

-- Now disable use_index_extensions:

mysql> SET optimizer_switch='use_index_extensions=off';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT COUNT(DISTINCT(relatedid)) FROM example WHERE id IN (181002, 181001);
+----------------------------+
| COUNT(DISTINCT(relatedid)) |
+----------------------------+
|                          1 |
+----------------------------+
1 row in set (0.00 sec)

-- Correct result.
mysql> EXPLAIN SELECT COUNT(DISTINCT(relatedid)) FROM example WHERE id IN (181002, 181001);
+----+-------------+---------+-------+---------------------+-------------+---------+------+------+--------------------------+
| id | select_type | table   | type  | possible_keys       | key         | key_len | ref  | rows | Extra                    |
+----+-------------+---------+-------+---------------------+-------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | example | index | PRIMARY,example_idx | example_idx | 311     | NULL |    4 | Using where; Using index |
+----+-------------+---------+-------+---------------------+-------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

-- Re-enable use_index_extensions and swap order of keys:
mysql> ALTER TABLE example DROP KEY example_idx;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE example ADD CONSTRAINT example_idx UNIQUE (idnumber,relatedid);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT COUNT(DISTINCT(relatedid)) FROM example WHERE id IN (181002, 181001);
+----------------------------+
| COUNT(DISTINCT(relatedid)) |
+----------------------------+
|                          1 |
+----------------------------+
1 row in set (0.00 sec)

-- Correct result.

mysql> EXPLAIN SELECT COUNT(DISTINCT(relatedid)) FROM example WHERE id IN (181002, 181001);
+----+-------------+---------+-------+---------------------+-------------+---------+------+------+--------------------------+
| id | select_type | table   | type  | possible_keys       | key         | key_len | ref  | rows | Extra                    |
+----+-------------+---------+-------+---------------------+-------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | example | index | PRIMARY,example_idx | example_idx | 311     | NULL |    4 | Using where; Using index |
+----+-------------+---------+-------+---------------------+-------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

-- Swap the key back.

mysql> ALTER TABLE example DROP INDEX example_idx;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE example ADD CONSTRAINT example_idx UNIQUE (relatedid, idnumber);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- Now delete the current first row.
mysql> DELETE FROM example WHERE id = 181000;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT COUNT(DISTINCT(relatedid)) FROM example WHERE id IN (181004, 181001);
+----------------------------+
| COUNT(DISTINCT(relatedid)) |
+----------------------------+
|                          1 |
+----------------------------+
1 row in set (0.00 sec)

Verified that this is still an issue on:
* 5.6.36
* 5.7.18