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

Nested SELECT IN returns wrong results

    XMLWordPrintable

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. output1.png
          output1.png
          31 kB
        2. output2.png
          output2.png
          28 kB
        3. tables.txt
          1 kB
        4. mysql.zip
          7 kB
        5. analyze.txt
          2 kB
        6. variables.txt
          17 kB

        Issue Links

          Activity

            People

              varun Varun Gupta (Inactive)
              garyscholten Gary Scholten
              Votes:
              1 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.