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

            alice Alice Sherepa added a comment - - edited

            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';

            CREATE TABLE t1 ( a int NOT NULL PRIMARY KEY ) ENGINE=myisam;
            INSERT INTO  t1 SELECT seq FROM seq_1_to_1000;
             
            CREATE TABLE t2 ( a int(5) NOT NULL,b int(3) NOT NULL) ENGINE=myisam;
            INSERT INTO t2  VALUES (7878, 96),(3465, 96),(1403, 96),(4189, 96),(8732, 96), (5,96);
            INSERT INTO t2 SELECT seq,56 FROM seq_1_to_1000;
             
            CREATE TABLE t3 (
              c int(10) unsigned NOT NULL,
              b int(10) unsigned NOT NULL,
              PRIMARY KEY (c,b)
            ) ENGINE=myisam;
             
            INSERT INTO t3 (c, b) VALUES (27, 96);
            INSERT INTO t3 SELECT seq,seq FROM seq_30_to_1000;
             
            SELECT t2.a FROM t2 WHERE t2.b IN (SELECT t3.b FROM t3 WHERE t3.c= 27);
             
            SET optimizer_switch='materialization=off';
             
            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;
             
            SET optimizer_switch='materialization=on';
             
            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;
            

            --------------
            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
            

            alice Alice Sherepa added a comment - - edited 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'; CREATE TABLE t1 ( a int NOT NULL PRIMARY KEY ) ENGINE=myisam; INSERT INTO t1 SELECT seq FROM seq_1_to_1000;   CREATE TABLE t2 ( a int (5) NOT NULL ,b int (3) NOT NULL ) ENGINE=myisam; INSERT INTO t2 VALUES (7878, 96),(3465, 96),(1403, 96),(4189, 96),(8732, 96), (5,96); INSERT INTO t2 SELECT seq,56 FROM seq_1_to_1000;   CREATE TABLE t3 ( c int (10) unsigned NOT NULL , b int (10) unsigned NOT NULL , PRIMARY KEY (c,b) ) ENGINE=myisam;   INSERT INTO t3 (c, b) VALUES (27, 96); INSERT INTO t3 SELECT seq,seq FROM seq_30_to_1000;   SELECT t2.a FROM t2 WHERE t2.b IN ( SELECT t3.b FROM t3 WHERE t3.c= 27);   SET optimizer_switch= 'materialization=off' ;   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;   SET optimizer_switch= 'materialization=on' ;   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; -------------- 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
            alice Alice Sherepa added a comment - - edited

            mtr test:

            CREATE TABLE t1 ( a int NOT NULL PRIMARY KEY);
             
            CREATE TABLE t2 ( a int, b int );
            INSERT INTO t2  VALUES (7878, 96),(3465, 96),(1403, 96),(4189, 96),(8732, 96), (5,96);
             
            CREATE TABLE t3 (c int unsigned NOT NULL, b int unsigned NOT NULL, PRIMARY KEY (c,b));
            INSERT INTO t3 (c, b) VALUES (27, 96);
             
            DELIMITER $$;
            CREATE PROCEDURE prepare_data()
            BEGIN
              DECLARE i INT DEFAULT 1;
             
              WHILE i < 1000 DO
                INSERT INTO t1 (a) VALUES (i);
                INSERT INTO t2 (a,b) VALUES (i,56);
                INSERT INTO t3 (c,b) VALUES (i,i);
                SET i = i + 1;
              END WHILE;
            END$$
            DELIMITER ;$$
             
            CALL prepare_data();
             
            SELECT t2.a FROM t2 WHERE t2.b IN (SELECT t3.b FROM t3 WHERE t3.c= 27);
             
            SET optimizer_switch='materialization=off';
             
            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;
             
            SET optimizer_switch='materialization=on';
             
            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;
             
            drop procedure prepare_data;
            drop table t1,t2,t3;
            

            alice Alice Sherepa added a comment - - edited mtr test: CREATE TABLE t1 ( a int NOT NULL PRIMARY KEY );   CREATE TABLE t2 ( a int , b int ); INSERT INTO t2 VALUES (7878, 96),(3465, 96),(1403, 96),(4189, 96),(8732, 96), (5,96);   CREATE TABLE t3 (c int unsigned NOT NULL , b int unsigned NOT NULL , PRIMARY KEY (c,b)); INSERT INTO t3 (c, b) VALUES (27, 96);   DELIMITER $$; CREATE PROCEDURE prepare_data() BEGIN DECLARE i INT DEFAULT 1;   WHILE i < 1000 DO INSERT INTO t1 (a) VALUES (i); INSERT INTO t2 (a,b) VALUES (i,56); INSERT INTO t3 (c,b) VALUES (i,i); SET i = i + 1; END WHILE; END $$ DELIMITER ;$$   CALL prepare_data();   SELECT t2.a FROM t2 WHERE t2.b IN ( SELECT t3.b FROM t3 WHERE t3.c= 27);   SET optimizer_switch= 'materialization=off' ;   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;   SET optimizer_switch= 'materialization=on' ;   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;   drop procedure prepare_data; drop table t1,t2,t3;

            The main issue here is the type difference in the subqueries where clause

            t2.b is defined as INT
            t3.b is defined as INT unsigned

            One is signed and the other is unsigned , so we are not able to create multiple equalities for t2.b = t3.b.

            The issue here is we are not having the condition t2.b= t3.b attached to the BNL join, so materialised table is containing way more rows than expected.

            varun Varun Gupta (Inactive) added a comment - The main issue here is the type difference in the subqueries where clause t2.b is defined as INT t3.b is defined as INT unsigned One is signed and the other is unsigned , so we are not able to create multiple equalities for t2.b = t3.b. The issue here is we are not having the condition t2.b= t3.b attached to the BNL join, so materialised table is containing way more rows than expected.
            varun Varun Gupta (Inactive) added a comment - Patch http://lists.askmonty.org/pipermail/commits/2018-May/012522.html

            Review input provided over email. Ok to push after it is addressed.

            psergei Sergei Petrunia added a comment - Review input provided over email. Ok to push after it is addressed.

            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.