Bug #93621 The select query return not all matching rows from single table select
Submitted: 14 Dec 2018 17:20 Modified: 14 Dec 2018 21:00
Reporter: Дмитрий Дмитрий Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S2 (Serious)
Version:5.6 OS:Debian
Assigned to: CPU Architecture:Other

[14 Dec 2018 17:20] Дмитрий Дмитрий
Description:
There is a small table about 2 000 000 records. 
I make a request:

SELECT *
FROM
(
  SELECT `id`, `asin`, `marketplace_id`
  FROM `repricing_product`
  WHERE
    (`marketplace_id` = 10 AND `asin` IN ('1905881215',...))
 OR (`marketplace_id` = 2 AND `asin` IN (...))
 OR (`marketplace_id` = 3 AND `asin` IN ('1905881215',...))
 OR (`marketplace_id` = 1 AND `asin` IN (...))
 OR (`marketplace_id` = 7 AND `asin` IN (...))
 OR (`marketplace_id` = 8 AND `asin` IN (...))
 OR (`marketplace_id` = 5 AND `asin` IN ('1905881215',...))
 OR (`marketplace_id` = 4 AND `asin` IN (...))
) tmp
WHERE tmp.asin = '1905881215';

result:

+---------+------------+----------------+
| id      | asin       | marketplace_id |
+---------+------------+----------------+
| 8420139 | 1905881215 |              5 |
| 7956805 | 1905881215 |             10 |
+---------+------------+----------------+

The result does not contain data that matches the query condition. Let's make sure that a rows is really exists:

SELECT `id`, `asin`, `marketplace_id`
FROM repricing_product rp
WHERE rp.asin = '1905881215' and `marketplace_id` in (10,3,5);

result:

+---------+------------+----------------+
| id      | asin       | marketplace_id |
+---------+------------+----------------+
| 8477125 | 1905881215 |              3 |
| 8420139 | 1905881215 |              5 |
| 7956805 | 1905881215 |             10 |
+---------+------------+----------------+

Lets replace condition (`marketplace_id` = 2 ...) and (`marketplace_id` = 3 ...) with each other. The result is correct. 3 rows are returned: 

SELECT *
FROM
(
  SELECT `id`, `asin`, `marketplace_id`
  FROM `repricing_product`
  WHERE
    (`marketplace_id` = 10 AND `asin` IN ('1905881215',...))
 OR (`marketplace_id` = 3 AND `asin` IN ('1905881215',...))
 OR (`marketplace_id` = 2 AND `asin` IN (...))
 OR (`marketplace_id` = 1 AND `asin` IN (...))
 OR (`marketplace_id` = 7 AND `asin` IN (...))
 OR (`marketplace_id` = 8 AND `asin` IN (...))
 OR (`marketplace_id` = 5 AND `asin` IN ('1905881215',...))
 OR (`marketplace_id` = 4 AND `asin` IN (...))
) tmp
WHERE tmp.asin = '1905881215';

result:

+---------+------------+----------------+
| id      | asin       | marketplace_id |
+---------+------------+----------------+
| 8420139 | 1905881215 |              5 |
| 7956805 | 1905881215 |             10 |
| 8477125 | 1905881215 |              3 |
+---------+------------+----------------+

The following version of request is return correct result too:

SELECT *
FROM
(
  SELECT `id`, `asin`, `marketplace_id`
  FROM `repricing_product`
  WHERE
    (`asin`, `marketplace_id`) IN
    (
      ('1905881215',10),...,
      ('0001839225',2),...,
      ('1905881215',3),...,
      ('0006176666',1),...,
      ('0007103077',7),...,
      ('0007106777',8),...,
      ('1905881215',5),...,
      ('B003G53VSO',4),...
    )  
) tmp
WHERE tmp.asin = '1905881215';

result:

+---------+------------+----------------+
| id      | asin       | marketplace_id |
+---------+------------+----------------+
| 8420139 | 1905881215 |              5 |
| 7956805 | 1905881215 |             10 |
| 8477125 | 1905881215 |              3 |
+---------+------------+----------------+

Table dump and full version of queries can be founded here: https://drive.google.com/drive/folders/112POKiQ5k_r71WlOjnpKraeIrfl1YVeb?usp=sharing

How to repeat:
Create table using table_dump.sql from here: https://drive.google.com/drive/folders/112POKiQ5k_r71WlOjnpKraeIrfl1YVeb?usp=sharing

Run queries in query.sql from here: https://drive.google.com/drive/folders/112POKiQ5k_r71WlOjnpKraeIrfl1YVeb?usp=sharing
[14 Dec 2018 18:39] MySQL Verification Team
Thank you the bug report. Please print here the exactly server version and the real and expected results when running your query.sql source. Thanks in advance.
[14 Dec 2018 19:09] MySQL Verification Team
Please disregard my prior request.
[14 Dec 2018 21:00] MySQL Verification Team
Thank you for the bug report. Only 5.6 version affected, 5.7 and 8.0 not affected.