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.

1 files changed, 21 insertions, 0 deletions

diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result
index 8920539ef2e..fb4cc58d607 100644
--- a/mysql-test/r/join_outer.result
+++ b/mysql-test/r/join_outer.result
@@ -2117,4 +2117,25 @@ SELECT a.* FROM t1 a LEFT JOIN t1 b ON a.id = b.id
 WHERE a.modified > b.modified or b.modified IS NULL;
 id	modified
 DROP TABLE t1;
+#
+# MDEV-4817: Optimizer fails to optimize expression of the form 'FOO' IS NULL
+#
+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;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	10	Using where
+1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t0.a	1	Using index
+explain select * from t1 left join t0 on t0.a=t1.pk where t0.person_id='fooo';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	10	Using where
+1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t0.a	1	Using index
+explain select * from t1 left join t0 on t0.a=t1.pk where t0.person_id='fooo' or t0.person_id='bar';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	10	Using where
+1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t0.a	1	Using index
+drop table t0, t1;
 SET optimizer_switch=@save_optimizer_switch;