Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.1.26, 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL)
-
Debian 9 Stretch
Description
Upgraded Debian 8 with MySql 5.5.59 to Debian 9 with MariaDB 10.1.26 and noticed different results (auto-installed by dist-upgrade)
SELECT p.`id_promo`, p.`name`
FROM `ps_promo` p
WHERE p.`id_promo` IN (
SELECT pg.`id_promo` FROM `ps_promo_group` pg WHERE pg.`id_group` IN (
SELECT cg.`id_group` FROM `ps_customer_group` cg WHERE cg.`id_customer`= 727)
)
AND (p.`date_effective` <= CURRENT_DATE AND p.`date_expiry` >= CURRENT_DATE)
AND p.`position_essential` > 0
ORDER BY p.`name` ASC LIMIT 30;
It returns too many results!!. If I run the inner part first and save that into my buffer and paste that in the outer SELECT, I get correct results (2). Now I get 7 results, 5 are incorrect.
Innerpart:
SELECT pg.`id_promo` FROM `ps_promo_group` pg WHERE pg.`id_group` IN (
SELECT cg.`id_group` FROM `ps_customer_group` cg WHERE cg.`id_customer`= 727)
I checked MDEV-13694 and some other similar issues, however my optimizer_switch=orderby_uses_equalities=off is my current set up and should return correct results, but it doesn't.
Attachments
Issue Links
- relates to
-
MDEV-15982 Incorrect results when subquery is materialized.
-
- Closed
-
Thanks for the report!
It is repeatable on 5.5-10.2, with InnoDB and MyIsam.
As a temprorary workaround, please use
SET optimizer_switch='materialization=off';
) ENGINE=myisam;
--------------
SELECT t2.a FROM t2 WHERE t2.b IN (SELECT t3.b FROM t3 WHERE t3.c= 27)
--------------
+------+
| a |
+------+
| 7878 |
| 3465 |
| 1403 |
| 4189 |
| 8732 |
| 5 |
+------+
6 rows in set (0.00 sec)
--------------
SET optimizer_switch='materialization=off'
--------------
Query OK, 0 rows affected (0.00 sec)
--------------
SELECT t1.a FROM t1
WHERE t1.a IN (SELECT t2.a FROM t2 WHERE t2.b IN (SELECT t3.b FROM t3 WHERE t3.c= 27)) LIMIT 5
--------------
+---+
| a |
+---+
| 5 |
+---+
1 row in set (0.00 sec)
--------------
explain extended SELECT t1.a FROM t1
WHERE t1.a IN (SELECT t2.a FROM t2 WHERE t2.b IN (SELECT t3.b FROM t3 WHERE t3.c= 27)) LIMIT 5
--------------
+------+-------------+-------+--------+---------------+---------+---------+-----------+------+----------+-------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+-------------+-------+--------+---------------+---------+---------+-----------+------+----------+-------------------------------------------------+
| 1 | PRIMARY | t3 | ref | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using index; Start temporary |
| 1 | PRIMARY | t2 | ALL | NULL | NULL | NULL | NULL | 1006 | 100.00 | Using where; Using join buffer (flat, BNL join) |
| 1 | PRIMARY | t1 | eq_ref | PRIMARY | PRIMARY | 4 | test.t2.a | 1 | 100.00 | Using index; End temporary |
+------+-------------+-------+--------+---------------+---------+---------+-----------+------+----------+-------------------------------------------------+
3 rows in set, 1 warning (0.00 sec)
Note (Code 1003): select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t3` join `test`.`t2`) where `test`.`t3`.`c` = 27 and `test`.`t1`.`a` = `test`.`t2`.`a` and `test`.`t2`.`b` = `test`.`t3`.`b` limit 5
--------------
SET optimizer_switch='materialization=on'
--------------
Query OK, 0 rows affected (0.00 sec)
--------------
SELECT t1.a FROM t1
WHERE t1.a IN (SELECT t2.a FROM t2 WHERE t2.b IN (SELECT t3.b FROM t3 WHERE t3.c= 27)) LIMIT 5
--------------
+---+
| a |
+---+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+---+
5 rows in set (0.00 sec)
--------------
explain extended SELECT t1.a FROM t1
WHERE t1.a IN (SELECT t2.a FROM t2 WHERE t2.b IN (SELECT t3.b FROM t3 WHERE t3.c= 27)) LIMIT 5
--------------
+------+--------------+-------------+--------+---------------+--------------+---------+-------+------+----------+------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+--------------+-------------+--------+---------------+--------------+---------+-------+------+----------+------------------------------------+
| 1 | PRIMARY | t1 | index | PRIMARY | PRIMARY | 4 | NULL | 1000 | 100.00 | Using index |
| 1 | PRIMARY | <subquery2> | eq_ref | distinct_key | distinct_key | 4 | func | 1 | 100.00 | |
| 2 | MATERIALIZED | t3 | ref | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using index |
| 2 | MATERIALIZED | t2 | ALL | NULL | NULL | NULL | NULL | 1006 | 100.00 | Using join buffer (flat, BNL join) |
+------+--------------+-------------+--------+---------------+--------------+---------+-------+------+----------+------------------------------------+
4 rows in set, 1 warning (0.00 sec)
Note (Code 1003): select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t3` join `test`.`t2`) where `test`.`t3`.`c` = 27 and `test`.`t2`.`b` = `test`.`t3`.`b` limit 5