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