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

Wrong result (extra row) with AND and OR conditions

    XMLWordPrintable

Details

    Description

      It is most likely a regression introduced (or made visible) by the following revision:

      revno: 3628
      revision-id: igor@askmonty.org-20130225031611-jk8lyhhjazov66qc
      committer: Igor Babaev <igor@askmonty.org>
      message:
        Fixed bug mdev-4177

      I cannot verify it with 100% certainty because the provided test case causes a crash on revisions 3628..3634; but it produces a correct result on maria/5.3 up to revno 3627 and a wrong result starting from revno 3635 and up to (and including) the current 3646.
      Also reproducible on current maria/5.5.

      Test case:

      CREATE TABLE t1 (a INT, b INT) ENGINE=MyISAM;
      INSERT INTO t1 VALUES (1,101),(2,102),(3,103),(4,104),(5,NULL);
       
      SELECT * FROM t1
      WHERE ( NULL OR a = 5 ) AND ( b != 1 OR a = 1 );

      Actual result:

      a	b
      5	NULL

      Expected result - empty set:

      a	b

      Inconsistency of the actual result can be confirmed by executing the second part of AND separately:

      SELECT * FROM t1
      WHERE b != 1 OR a = 1;
      a	b
      1	101
      2	102
      3	103
      4	104

      It is a correct result, and it doesn't include the row with a=5, so the result set for the bigger query cannot include it either (but currently it does).

      Reproducible with the default optimizer_switch as well as with all OFF values.

      EXPLAIN:

      EXPLAIN EXTENDED
      SELECT * FROM t1
      WHERE ( NULL OR a = 5 ) AND ( b != 1 OR a = 1 );
      id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
      1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where
      Warnings:
      Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`a` = 5) and ((`test`.`t1`.`b` <> 1) or 1))

      Attachments

        Issue Links

          Activity

            People

              igor Igor Babaev
              elenst Elena Stepanova
              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.