From 04684b7709f55a5a9de9226e834bcfbed05ee5c0 Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Wed, 31 Jul 2013 17:24:52 +0400 Subject: 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. --- mysql-test/t/join_outer.test | 17 +++++++++++++++++ 1 file changed, 17 insertions(+) (limited to 'mysql-test/t/join_outer.test') diff --git a/mysql-test/t/join_outer.test b/mysql-test/t/join_outer.test index 24885c056b8..b0000b2b943 100644 --- a/mysql-test/t/join_outer.test +++ b/mysql-test/t/join_outer.test @@ -1670,4 +1670,21 @@ SELECT a.* FROM t1 a LEFT JOIN t1 b ON a.id = b.id DROP TABLE t1; +--echo # +--echo # MDEV-4817: Optimizer fails to optimize expression of the form 'FOO' IS NULL +--echo # +create table t0 (a int not null); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +alter table t0 add person_id varchar(255) not null; +create table t1 (pk int not null primary key); +insert into t1 select A.a + 10*B.a from t0 A, t0 B; + +explain select * from t1 left join t0 on t0.a=t1.pk where t0.person_id='fooo' or 'xyz' IS NULL; +explain select * from t1 left join t0 on t0.a=t1.pk where t0.person_id='fooo'; +explain select * from t1 left join t0 on t0.a=t1.pk where t0.person_id='fooo' or t0.person_id='bar'; + +drop table t0, t1; + + SET optimizer_switch=@save_optimizer_switch; -- cgit v1.2.1