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

Wrong result (missing rows) and unexpected warnings with condition_pushdown_for_derived

    XMLWordPrintable

    Details

      Description

      Note: MDEV-11103 has been fixed, so it must be a different problem.

      DROP VIEW if exists v2;
      DROP TABLE IF EXISTS t1, t2;
       
      CREATE TABLE t1 (pk1 INT PRIMARY KEY, a INT, b INT) ENGINE=MyISAM;
      INSERT INTO t1 VALUES (10,7,1),(11,0,2);
       
      CREATE TABLE t2 (pk2 INT PRIMARY KEY, c INT, d DATETIME) ENGINE=MyISAM;
      CREATE ALGORITHM=TEMPTABLE VIEW v2 AS SELECT * FROM t2;
      INSERT INTO t2 VALUES (1,4,'2008-09-27 00:34:58'),(2,5,'2007-05-28 00:00:00'),(3,6,'2009-07-25 09:21:20');
       
      SELECT * FROM ( SELECT * FROM t1 ) AS sq 
      WHERE b IN ( SELECT pk2 FROM v2 WHERE c > sq.b ) OR b = 100;
      

      Actual result (10.2 b162068456)

      MariaDB [test]> SELECT * FROM ( SELECT * FROM t1 ) AS sq  WHERE b IN ( SELECT pk2 FROM v2 WHERE c > sq.b ) OR b = 100;
      Empty set, 3 warnings (0.01 sec)
       
      MariaDB [test]> show warnings;
      +---------+------+-------------------------------------------------------+
      | Level   | Code | Message                                               |
      +---------+------+-------------------------------------------------------+
      | Warning | 1292 | Incorrect datetime value: '4' for column 'c' at row 1 |
      | Warning | 1292 | Incorrect datetime value: '5' for column 'c' at row 2 |
      | Warning | 1292 | Incorrect datetime value: '6' for column 'c' at row 3 |
      +---------+------+-------------------------------------------------------+
      3 rows in set (0.01 sec)
      

      (Please note the strange warnings – the datetime column does not participate in the query at all).

      Expected result

      MariaDB [test]> SELECT * FROM ( SELECT * FROM t1 ) AS sq  WHERE b IN ( SELECT pk2 FROM v2 WHERE c > sq.b ) OR b = 100;
      +-----+------+------+
      | pk1 | a    | b    |
      +-----+------+------+
      |  10 |    7 |    1 |
      |  11 |    0 |    2 |
      +-----+------+------+
      2 rows in set (0.00 sec)
      

        Attachments

          Activity

            People

            Assignee:
            igor Igor Babaev
            Reporter:
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: