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