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

Query returns different results on a debug vs non-debug build of the same revision

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 5.5, 10.0, 10.1
    • Fix Version/s: 10.1.17
    • Component/s: Optimizer
    • Labels:
      None
    • Environment:
      Jessie x86_64, gcc (Debian 4.9.2-10) 4.9.2, cmake version 3.0.2
    • Sprint:
      10.2.2-3

      Description

      Test case

      CREATE TABLE t1 (i1 INT, i2 INT NOT NULL) ENGINE=MyISAM;
      INSERT INTO t1 VALUES (1,4),(2,6);
                              
      SELECT * FROM t1 AS alias1 
      WHERE alias1.i1 IN ( 
          SELECT i1 FROM t1 WHERE alias1.i2 IN ( SELECT i2 FROM t1 HAVING i2 <> 7 )
      );
      DROP TABLE t1;
      

      10.0 bf2e31500c debug build

      MariaDB [test]> SELECT * FROM t1 AS alias1 
          -> WHERE alias1.i1 IN ( 
          ->     SELECT i1 FROM t1 WHERE alias1.i2 IN ( SELECT i2 FROM t1 HAVING i2 <> 7 )
          -> );
      Empty set (0.01 sec)
      

      MariaDB [test]> EXPLAIN EXTENDED
          -> SELECT * FROM t1 AS alias1 
          -> WHERE alias1.i1 IN ( 
          ->     SELECT i1 FROM t1 WHERE alias1.i2 IN ( SELECT i2 FROM t1 HAVING i2 <> 7 )
          -> );
      +------+--------------+-------------+--------+---------------+--------------+---------+----------------+------+----------+----------------------------------------------------------------------------+
      | id   | select_type  | table       | type   | possible_keys | key          | key_len | ref            | rows | filtered | Extra                                                                      |
      +------+--------------+-------------+--------+---------------+--------------+---------+----------------+------+----------+----------------------------------------------------------------------------+
      |    1 | PRIMARY      | <subquery3> | eq_ref | distinct_key  | distinct_key | 4       | test.alias1.i2 |    1 |   100.00 |                                                                            |
      |    1 | PRIMARY      | alias1      | ALL    | NULL          | NULL         | NULL    | NULL           |    2 |   100.00 | Using join buffer (flat, BNL join)                                         |
      |    1 | PRIMARY      | t1          | ALL    | NULL          | NULL         | NULL    | NULL           |    2 |   100.00 | Using where; FirstMatch(alias1); Using join buffer (incremental, BNL join) |
      |    3 | MATERIALIZED | t1          | ALL    | NULL          | NULL         | NULL    | NULL           |    2 |   100.00 |                                                                            |
      +------+--------------+-------------+--------+---------------+--------------+---------+----------------+------+----------+----------------------------------------------------------------------------+
      4 rows in set, 2 warnings (0.00 sec)
       
      MariaDB [test]> select @@version;
      +-----------------------+
      | @@version             |
      +-----------------------+
      | 10.0.27-MariaDB-debug |
      +-----------------------+
      1 row in set (0.00 sec)
      

      10.0 bf2e31500c relwithdebinfo build

      MariaDB [test]> SELECT * FROM t1 AS alias1 
          -> WHERE alias1.i1 IN ( 
          ->     SELECT i1 FROM t1 WHERE alias1.i2 IN ( SELECT i2 FROM t1 HAVING i2 <> 7 )
          -> );
      +------+----+
      | i1   | i2 |
      +------+----+
      |    1 |  4 |
      |    2 |  6 |
      +------+----+
      2 rows in set (0.00 sec)
      

      MariaDB [test]> EXPLAIN EXTENDED
          -> SELECT * FROM t1 AS alias1 
          -> WHERE alias1.i1 IN ( 
          ->     SELECT i1 FROM t1 WHERE alias1.i2 IN ( SELECT i2 FROM t1 HAVING i2 <> 7 )
          -> );
      +------+--------------+-------------+--------+---------------+--------------+---------+----------------+------+----------+----------------------------------------------------------------------------+
      | id   | select_type  | table       | type   | possible_keys | key          | key_len | ref            | rows | filtered | Extra                                                                      |
      +------+--------------+-------------+--------+---------------+--------------+---------+----------------+------+----------+----------------------------------------------------------------------------+
      |    1 | PRIMARY      | <subquery3> | eq_ref | distinct_key  | distinct_key | 4       | test.alias1.i2 |    1 |   100.00 |                                                                            |
      |    1 | PRIMARY      | alias1      | ALL    | NULL          | NULL         | NULL    | NULL           |    2 |   100.00 | Using join buffer (flat, BNL join)                                         |
      |    1 | PRIMARY      | t1          | ALL    | NULL          | NULL         | NULL    | NULL           |    2 |   100.00 | Using where; FirstMatch(alias1); Using join buffer (incremental, BNL join) |
      |    3 | MATERIALIZED | t1          | ALL    | NULL          | NULL         | NULL    | NULL           |    2 |   100.00 |                                                                            |
      +------+--------------+-------------+--------+---------------+--------------+---------+----------------+------+----------+----------------------------------------------------------------------------+
      4 rows in set, 2 warnings (0.00 sec)
       
      MariaDB [test]> select @@version;
      +-----------------+
      | @@version       |
      +-----------------+
      | 10.0.27-MariaDB |
      +-----------------+
      1 row in set (0.00 sec)
      

        Attachments

          Activity

            People

            Assignee:
            psergei Sergei Petrunia
            Reporter:
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: