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

Wrong result (missing rows) on 2nd execution of PS with ANY ( SQ )

        Details

          Description

          The problem appeared on 5.5 tree quite some time ago, with the following revision:

          revno: 3843
revision-id: psergey@askmonty.org-20130731132452-4qb11t56i2jw16aa
parent: psergey@askmonty.org-20130731093701-10tmxhe668f3u1lx
committer: Sergey Petrunya <psergey@askmonty.org>
branch nick: 5.5
timestamp: Wed 2013-07-31 17:24:52 +0400
message:
  MDEV-4817: Optimizer fails to optimize expression of the form 'FOO' IS NULL
  - Modify the way Item_cond::fix_fields() and Item_cond::eval_not_null_tables() 
    calculate bitmap for Item_cond_or::not_null_tables():
    if they see a "... OR inexpensive_const_false_item OR ..." then the item can
    be ignored.
  - Updated test results. There can be more warnings produced since parts of WHERE 
    are evaluated more times.
          Test case
          CREATE TABLE t1 (a INT) ENGINE=MyISAM;
INSERT INTO t1 VALUES (1),(2);

CREATE TABLE t2 (b INT) ENGINE=MyISAM;
INSERT INTO t2 VALUES (1),(2);

CREATE TABLE t3 (c INT) ENGINE=MyISAM;
INSERT INTO t3 VALUES (2),(5);

prepare stmt from "SELECT * FROM t1 INNER JOIN t2 ON (b = a) WHERE 3 > ANY ( SELECT c FROM t3 )";
execute stmt;
execute stmt;

drop table t1, t2, t3;
          Results
          MariaDB [test]> execute stmt;
+------+------+
| a    | b    |
+------+------+
|    1 |    1 |
|    2 |    2 |
+------+------+
2 rows in set (0.05 sec)

MariaDB [test]> execute stmt;
Empty set (0.00 sec)

          5.5 commit fdd6c111c254c5044cd9b6c2f7e4d0c74f427a79, also reproducible on 10.0 and 10.1 trees.

            Gliffy Diagrams

              Attachments

                Activity

                There are no comments yet on this issue.

                  People

                  • Assignee:
                    psergey Sergei Petrunia
                    Reporter:
                    elenst Elena Stepanova
                  • Votes:
                    0 Vote for this issue
                    Watchers:
                    1 Start watching this issue

                    Dates

                    • Created:
                      Updated: