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

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.

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;

a	b	c	d

10	8	8	8

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))

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

a	b	c	d

10	8	8	NULL