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

Wrong result when using IN subquery with order by

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 5.5.37, 10.0.11
    • 5.5.38, 10.0.12
    • None
    • None
    • Ubuntu Precise, also Ubuntu 14.04

    Description

      I got a wrong result in two different machines

      I tried after uploading the prueba.sql file the following query:

      SELECT * FROM agentes WHERE id in (select distinct id_agente from otra) ORDER BY nombre ASC;

      I get:

      +----+--------+
      | id | nombre |
      +----+--------+
      |  1 | row 1  |
      |  1 | row 1  |
      |  1 | row 1  |
      |  2 | row 2  |
      |  2 | row 2  |
      |  3 | row 3  |
      +----+--------+

      but if I remove the order by clause I get:

      +----+--------+
      | id | nombre |
      +----+--------+
      |  1 | row 1  |
      |  2 | row 2  |
      |  3 | row 3  |
      +----+--------+

      I don't put much more information i think it's very reproductible since i got it in two different machines.

      Attachments

        Activity

          Hi,

          Could you please provide SHOW CREATE TABLE results for `agentes` and `otra` tables?

          Also,

          EXPLAIN EXTENDED SELECT * FROM agentes WHERE id in (select distinct id_agente from otra) ORDER BY nombre ASC

          could be useful.

          Thanks.

          elenst Elena Stepanova added a comment - Hi, Could you please provide SHOW CREATE TABLE results for `agentes` and `otra` tables? Also, EXPLAIN EXTENDED SELECT * FROM agentes WHERE id in ( select distinct id_agente from otra) ORDER BY nombre ASC could be useful. Thanks.
          satonio jose antonio added a comment -

          MariaDB [test]> show create table agentes;
          +---------+-------------------------------------------------------------------------------------------------------------------------------------------------+
          | Table   | Create Table                                                                                                                                    |
          +---------+-------------------------------------------------------------------------------------------------------------------------------------------------+
          | agentes | CREATE TABLE `agentes` (
            `id` int(11) NOT NULL,
            `nombre` varchar(255) NOT NULL,
            PRIMARY KEY (`id`)
          ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
          +---------+-------------------------------------------------------------------------------------------------------------------------------------------------+
          1 row in set (0.00 sec)
           
          MariaDB [test]> show create table otra;
          +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
          | Table | Create Table                                                                                                                                                                                         |
          +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
          | otra  | CREATE TABLE `otra` (
            `id_algo` int(11) NOT NULL,
            `id_agente` int(11) NOT NULL,
            PRIMARY KEY (`id_algo`,`id_agente`),
            KEY `another_data` (`id_agente`)
          ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
          +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
          1 row in set (0.00 sec)
           
          MariaDB [test]> EXPLAIN EXTENDED SELECT * FROM agentes WHERE id in (select distinct id_agente from otra) ORDER BY nombre ASC;
          +------+-------------+---------+--------+---------------+---------+---------+---------------------+------+----------+---------------------------------------------------------+
          | id   | select_type | table   | type   | possible_keys | key     | key_len | ref                 | rows | filtered | Extra                                                   |
          +------+-------------+---------+--------+---------------+---------+---------+---------------------+------+----------+---------------------------------------------------------+
          |    1 | PRIMARY     | otra    | index  | another_data  | PRIMARY | 8       | NULL                |    6 |   100.00 | Using index; Using temporary; Using filesort; LooseScan |
          |    1 | PRIMARY     | agentes | eq_ref | PRIMARY       | PRIMARY | 4       | test.otra.id_agente |    1 |   100.00 |                                                         |
          +------+-------------+---------+--------+---------------+---------+---------+---------------------+------+----------+---------------------------------------------------------+
          2 rows in set, 1 warning (0.00 sec)
           

          satonio jose antonio added a comment - MariaDB [test]> show create table agentes; +---------+-------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +---------+-------------------------------------------------------------------------------------------------------------------------------------------------+ | agentes | CREATE TABLE `agentes` ( `id` int(11) NOT NULL, `nombre` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +---------+-------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)   MariaDB [test]> show create table otra; +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | otra | CREATE TABLE `otra` ( `id_algo` int(11) NOT NULL, `id_agente` int(11) NOT NULL, PRIMARY KEY (`id_algo`,`id_agente`), KEY `another_data` (`id_agente`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)   MariaDB [test]> EXPLAIN EXTENDED SELECT * FROM agentes WHERE id in (select distinct id_agente from otra) ORDER BY nombre ASC; +------+-------------+---------+--------+---------------+---------+---------+---------------------+------+----------+---------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+---------+--------+---------------+---------+---------+---------------------+------+----------+---------------------------------------------------------+ | 1 | PRIMARY | otra | index | another_data | PRIMARY | 8 | NULL | 6 | 100.00 | Using index; Using temporary; Using filesort; LooseScan | | 1 | PRIMARY | agentes | eq_ref | PRIMARY | PRIMARY | 4 | test.otra.id_agente | 1 | 100.00 | | +------+-------------+---------+--------+---------------+---------+---------+---------------------+------+----------+---------------------------------------------------------+ 2 rows in set, 1 warning (0.00 sec)  

          Hi Jose,

          Thank you.
          As a workaround, please try to set optimizer_switch='semijoin=off' or optimizer_switch='loosescan=off'

          elenst Elena Stepanova added a comment - Hi Jose, Thank you. As a workaround, please try to set optimizer_switch='semijoin=off' or optimizer_switch='loosescan=off'
          satonio jose antonio added a comment -

          Neither helps.

          MariaDB [test]> set optimizer_switch='semijoin=off';
          Query OK, 0 rows affected (0.00 sec)
           
          MariaDB [test]> set optimizer_switch='loosescan=off';
          Query OK, 0 rows affected (0.00 sec)
           
          MariaDB [test]> explain extended SELECT * FROM agentes WHERE id in (select distinct id_agente from otra) ORDER BY nombre ASC;
          +------+--------------+---------+-------+---------------+---------+---------+------+------+----------+-----------------------------+
          | id   | select_type  | table   | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                       |
          +------+--------------+---------+-------+---------------+---------+---------+------+------+----------+-----------------------------+
          |    1 | PRIMARY      | agentes | ALL   | NULL          | NULL    | NULL    | NULL |    6 |   100.00 | Using where; Using filesort |
          |    2 | MATERIALIZED | otra    | index | another_data  | PRIMARY | 8       | NULL |    6 |   100.00 | Using index                 |
          +------+--------------+---------+-------+---------------+---------+---------+------+------+----------+-----------------------------+
          2 rows in set, 1 warning (0.00 sec)
           
          MariaDB [test]> SELECT * FROM agentes WHERE id in (select distinct id_agente from otra) ORDER BY nombre ASC;
          +----+--------+
          | id | nombre |
          +----+--------+
          |  1 | row 1  |
          |  1 | row 1  |
          |  1 | row 1  |
          |  2 | row 2  |
          |  2 | row 2  |
          |  3 | row 3  |
          +----+--------+
          6 rows in set (0.00 sec)

          satonio jose antonio added a comment - Neither helps. MariaDB [test]> set optimizer_switch='semijoin=off'; Query OK, 0 rows affected (0.00 sec)   MariaDB [test]> set optimizer_switch='loosescan=off'; Query OK, 0 rows affected (0.00 sec)   MariaDB [test]> explain extended SELECT * FROM agentes WHERE id in (select distinct id_agente from otra) ORDER BY nombre ASC; +------+--------------+---------+-------+---------------+---------+---------+------+------+----------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+--------------+---------+-------+---------------+---------+---------+------+------+----------+-----------------------------+ | 1 | PRIMARY | agentes | ALL | NULL | NULL | NULL | NULL | 6 | 100.00 | Using where; Using filesort | | 2 | MATERIALIZED | otra | index | another_data | PRIMARY | 8 | NULL | 6 | 100.00 | Using index | +------+--------------+---------+-------+---------------+---------+---------+------+------+----------+-----------------------------+ 2 rows in set, 1 warning (0.00 sec)   MariaDB [test]> SELECT * FROM agentes WHERE id in (select distinct id_agente from otra) ORDER BY nombre ASC; +----+--------+ | id | nombre | +----+--------+ | 1 | row 1 | | 1 | row 1 | | 1 | row 1 | | 2 | row 2 | | 2 | row 2 | | 3 | row 3 | +----+--------+ 6 rows in set (0.00 sec)

          Hi Jose,

          I suppose you have query_cache enabled. If so, after you had set semijoin=off or loosescan=off, either run the query as SELECT SQL_NO_CACHE * FROM ..., or execute FLUSH TABLES, or disable the query cache (set global query_cache_size=0).

          elenst Elena Stepanova added a comment - Hi Jose, I suppose you have query_cache enabled. If so, after you had set semijoin=off or loosescan=off, either run the query as SELECT SQL_NO_CACHE * FROM ... , or execute FLUSH TABLES , or disable the query cache ( set global query_cache_size=0 ).

          Test case (exactly the same as attached + query from the description, just all together in the form suitable for MTR):

          --source include/have_innodb.inc
           
          CREATE TABLE IF NOT EXISTS `agentes` (
            `id` int(11) NOT NULL,
            `nombre` varchar(255) NOT NULL,
            PRIMARY KEY (`id`)
          ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
           
          INSERT INTO `agentes` (`id`, `nombre`) VALUES
          (1, 'row 1'),(2, 'row 2'),(3, 'row 3'),
          (4, 'row 4'),(5, 'row 5'),(6, 'row 6');
           
          CREATE TABLE IF NOT EXISTS `otra` (
            `id_algo` int(11) NOT NULL,
            `id_agente` int(11) NOT NULL,
            PRIMARY KEY (`id_algo`,`id_agente`), 
            KEY `another_data` (`id_agente`)
          ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
           
          INSERT INTO `otra` (`id_algo`, `id_agente`) VALUES
          (1, 1),(1, 2),(2, 1),(2, 2),(2, 3),(3, 1);
           
          SELECT * FROM agentes WHERE id in (select distinct id_agente from otra) ORDER BY nombre ASC;
           
          SELECT * FROM agentes WHERE id in (select distinct id_agente from otra);

          Actual result:

          SELECT * FROM agentes WHERE id in (select distinct id_agente from otra) ORDER BY nombre ASC;
          id	nombre
          1	row 1
          1	row 1
          1	row 1
          2	row 2
          2	row 2
          3	row 3
          SELECT * FROM agentes WHERE id in (select distinct id_agente from otra);
          id	nombre
          1	row 1
          2	row 2
          3	row 3

          Expected result:

          SELECT * FROM agentes WHERE id in (select distinct id_agente from otra) ORDER BY nombre ASC;
          id	nombre
          1	row 1
          2	row 2
          3	row 3
          SELECT * FROM agentes WHERE id in (select distinct id_agente from otra);
          id	nombre
          1	row 1
          2	row 2
          3	row 3

          elenst Elena Stepanova added a comment - Test case (exactly the same as attached + query from the description, just all together in the form suitable for MTR): --source include/have_innodb.inc   CREATE TABLE IF NOT EXISTS `agentes` ( `id` int (11) NOT NULL , `nombre` varchar (255) NOT NULL , PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;   INSERT INTO `agentes` (`id`, `nombre`) VALUES (1, 'row 1' ),(2, 'row 2' ),(3, 'row 3' ), (4, 'row 4' ),(5, 'row 5' ),(6, 'row 6' );   CREATE TABLE IF NOT EXISTS `otra` ( `id_algo` int (11) NOT NULL , `id_agente` int (11) NOT NULL , PRIMARY KEY (`id_algo`,`id_agente`), KEY `another_data` (`id_agente`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;   INSERT INTO `otra` (`id_algo`, `id_agente`) VALUES (1, 1),(1, 2),(2, 1),(2, 2),(2, 3),(3, 1);   SELECT * FROM agentes WHERE id in ( select distinct id_agente from otra) ORDER BY nombre ASC ;   SELECT * FROM agentes WHERE id in ( select distinct id_agente from otra); Actual result: SELECT * FROM agentes WHERE id in (select distinct id_agente from otra) ORDER BY nombre ASC; id nombre 1 row 1 1 row 1 1 row 1 2 row 2 2 row 2 3 row 3 SELECT * FROM agentes WHERE id in (select distinct id_agente from otra); id nombre 1 row 1 2 row 2 3 row 3 Expected result: SELECT * FROM agentes WHERE id in (select distinct id_agente from otra) ORDER BY nombre ASC; id nombre 1 row 1 2 row 2 3 row 3 SELECT * FROM agentes WHERE id in (select distinct id_agente from otra); id nombre 1 row 1 2 row 2 3 row 3
          satonio jose antonio added a comment -

          The problem seems to be semijoin, with it disabled and SQL_NO_CACHE It got the right result.

          Thanks.

          satonio jose antonio added a comment - The problem seems to be semijoin, with it disabled and SQL_NO_CACHE It got the right result. Thanks.

          Query plan without ORDER BY:

          MariaDB [j21]> explain select * FROM agentes WHERE id in (select distinct id_agente from otra);
          +------+--------------+-------------+--------+---------------+--------------+---------+------+------+-------------+
          | id   | select_type  | table       | type   | possible_keys | key          | key_len | ref  | rows | Extra       |
          +------+--------------+-------------+--------+---------------+--------------+---------+------+------+-------------+
          |    1 | PRIMARY      | agentes     | ALL    | PRIMARY       | NULL         | NULL    | NULL |    6 |             |
          |    1 | PRIMARY      | <subquery2> | eq_ref | distinct_key  | distinct_key | 4       | func |    1 |             |
          |    2 | MATERIALIZED | otra        | index  | another_data  | PRIMARY      | 8       | NULL |    6 | Using index |
          +------+--------------+-------------+--------+---------------+--------------+---------+------+------+-------------+

          This seems ok.

          Query plan with ORDER BY:

          MariaDB [j21]> explain SELECT * FROM agentes WHERE id in (select distinct id_agente from otra) ORDER BY nombre ASC;
          +------+-------------+---------+--------+---------------+---------+---------+--------------------+------+---------------------------------------------------------+
          | id   | select_type | table   | type   | possible_keys | key     | key_len | ref                | rows | Extra                                                   |
          +------+-------------+---------+--------+---------------+---------+---------+--------------------+------+---------------------------------------------------------+
          |    1 | PRIMARY     | otra    | index  | another_data  | PRIMARY | 8       | NULL               |    6 | Using index; Using temporary; Using filesort; LooseScan |
          |    1 | PRIMARY     | agentes | eq_ref | PRIMARY       | PRIMARY | 4       | j21.otra.id_agente |    1 |                                                         |
          +------+-------------+---------+--------+---------------+---------+---------+--------------------+------+---------------------------------------------------------+

          This seems to be wrong. otra.PRIMARY is defined as

            PRIMARY KEY (`id_algo`,`id_agente`), 

          We need to produce a distinct stream of otra.id_agente. It is not possible to do with LooseScan - there are different values of id_algo, and index scan will produce data ordered by id_algo, id_agente.

          Somehow, the presence of ORDER BY causes invalid query plan to be chosen. Will investigate further.

          psergei Sergei Petrunia added a comment - Query plan without ORDER BY: MariaDB [j21]> explain select * FROM agentes WHERE id in (select distinct id_agente from otra); +------+--------------+-------------+--------+---------------+--------------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+--------------+-------------+--------+---------------+--------------+---------+------+------+-------------+ | 1 | PRIMARY | agentes | ALL | PRIMARY | NULL | NULL | NULL | 6 | | | 1 | PRIMARY | <subquery2> | eq_ref | distinct_key | distinct_key | 4 | func | 1 | | | 2 | MATERIALIZED | otra | index | another_data | PRIMARY | 8 | NULL | 6 | Using index | +------+--------------+-------------+--------+---------------+--------------+---------+------+------+-------------+ This seems ok. Query plan with ORDER BY: MariaDB [j21]> explain SELECT * FROM agentes WHERE id in (select distinct id_agente from otra) ORDER BY nombre ASC; +------+-------------+---------+--------+---------------+---------+---------+--------------------+------+---------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+---------+--------+---------------+---------+---------+--------------------+------+---------------------------------------------------------+ | 1 | PRIMARY | otra | index | another_data | PRIMARY | 8 | NULL | 6 | Using index; Using temporary; Using filesort; LooseScan | | 1 | PRIMARY | agentes | eq_ref | PRIMARY | PRIMARY | 4 | j21.otra.id_agente | 1 | | +------+-------------+---------+--------+---------------+---------+---------+--------------------+------+---------------------------------------------------------+ This seems to be wrong. otra.PRIMARY is defined as PRIMARY KEY (`id_algo`,`id_agente`), We need to produce a distinct stream of otra.id_agente. It is not possible to do with LooseScan - there are different values of id_algo, and index scan will produce data ordered by id_algo, id_agente. Somehow, the presence of ORDER BY causes invalid query plan to be chosen. Will investigate further.

          People

            psergei Sergei Petrunia
            satonio jose antonio
            Votes:
            0 Vote for this issue
            Watchers:
            4 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.