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

Wrong result (extra rows with wrong values) on query with joins, AND/OR conditions, constant table

    XMLWordPrintable

Details

    Description

      The problem appeared on 5.3 tree with the following revision:

      revno: 3765
      revision-id: igor@askmonty.org-20140221052733-ypg4dpfcmy5l0b3q
      parent: psergey@askmonty.org-20140219143412-rp3flx1pmhw6zawn
      committer: Igor Babaev <igor@askmonty.org>
      branch nick: maria-5.3
      timestamp: Thu 2014-02-20 21:27:33 -0800
      message:
        After constant row substitution the optimizer should call the method
        update_used_tables for the the where condition to update cached
        indicators of constant subexpressions. It should be done before further
        possible simplification of the where condition.
        
        This change caused simplification of the executed where conditions 
        in many test cases.

      Test case:

      CREATE TABLE t1 (a INT, b INT) ENGINE=MyISAM;
      INSERT INTO t1 VALUES (10,8);
       
      CREATE TABLE t2 (c INT) ENGINE=MyISAM;
      INSERT INTO t2 VALUES (8),(9);
       
      CREATE TABLE t3 (d INT) ENGINE=MyISAM;
      INSERT INTO t3 VALUES (3),(8);
       
      SELECT * FROM t1 INNER JOIN t2 ON ( c = b ) LEFT JOIN t3 ON ( d = a ) WHERE b IN (1,2,3) OR b = d;

      Expected result: empty set
      Actual result:

      a	b	c	d
      10	8	8	8

      EXPLAIN:

      id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
      1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	100.00	
      1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
      1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (flat, BNL join)
      Warnings:
      Note	1003	select 10 AS `a`,8 AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t3`.`d` AS `d` from `test`.`t1` join `test`.`t2` left join `test`.`t3` on(1) where ((`test`.`t2`.`c` = 8) and (`test`.`t3`.`d` = 8))

      To see that the result is wrong, compare it with the result without the WHERE clause:

      SELECT * FROM t1 INNER JOIN t2 ON ( c = b ) LEFT JOIN t3 ON ( d = a );
      a	b	c	d
      10	8	8	NULL

      Attachments

        Activity

          People

            igor Igor Babaev
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            3 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.