Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-15454

Nested SELECT IN returns wrong results

    Details

      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

        1. analyze.txt
          2 kB
        2. mysql.zip
          7 kB
        3. output1.png
          output1.png
          31 kB
        4. output2.png
          output2.png
          28 kB
        5. tables.txt
          1 kB
        6. variables.txt
          17 kB

          Issue Links

            Activity

              People

              • Assignee:
                varun Varun Gupta
                Reporter:
                garyscholten Gary Scholten
              • Votes:
                1 Vote for this issue
                Watchers:
                6 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: