diff options
| -rw-r--r-- | mysql-test/r/join_outer.result | 19 | ||||
| -rw-r--r-- | mysql-test/t/join_outer.test | 20 | ||||
| -rw-r--r-- | sql/item_cmpfunc.cc | 11 |
3 files changed, 46 insertions, 4 deletions
diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result index bd725d9ac84..694bab2597c 100644 --- a/mysql-test/r/join_outer.result +++ b/mysql-test/r/join_outer.result @@ -1135,3 +1135,22 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 4 Using where 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.a 1 DROP TABLE t1,t2; +CREATE TABLE t1 (id int(11) NOT NULL PRIMARY KEY, name varchar(20), +INDEX (name)) ENGINE=InnoDB; +CREATE TABLE t2 (id int(11) NOT NULL PRIMARY KEY, fkey int(11), +FOREIGN KEY (fkey) REFERENCES t2(id)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1,'A1'),(2,'A2'),(3,'B'); +INSERT INTO t2 VALUES (1,1),(2,2),(3,2),(4,3),(5,3); +EXPLAIN +SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id +WHERE t1.name LIKE 'A%'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index PRIMARY,name name 23 NULL 3 Using where; Using index +1 SIMPLE t2 ref fkey fkey 5 test.t1.id 1 Using where; Using index +EXPLAIN +SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id +WHERE t1.name LIKE 'A%' OR FALSE; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index PRIMARY,name name 23 NULL 3 Using where; Using index +1 SIMPLE t2 ref fkey fkey 5 test.t1.id 1 Using where; Using index +DROP TABLE t1,t2; diff --git a/mysql-test/t/join_outer.test b/mysql-test/t/join_outer.test index 03ad56ed2b6..c194213e0c9 100644 --- a/mysql-test/t/join_outer.test +++ b/mysql-test/t/join_outer.test @@ -759,3 +759,23 @@ EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a > IF(t1.a = t2.b DROP TABLE t1,t2; +# +# Test for bug #17164: ORed FALSE blocked conversion of outer join into join +# + +CREATE TABLE t1 (id int(11) NOT NULL PRIMARY KEY, name varchar(20), + INDEX (name)) ENGINE=InnoDB; +CREATE TABLE t2 (id int(11) NOT NULL PRIMARY KEY, fkey int(11), + FOREIGN KEY (fkey) REFERENCES t2(id)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1,'A1'),(2,'A2'),(3,'B'); +INSERT INTO t2 VALUES (1,1),(2,2),(3,2),(4,3),(5,3); + +EXPLAIN +SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id + WHERE t1.name LIKE 'A%'; + +EXPLAIN +SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id + WHERE t1.name LIKE 'A%' OR FALSE; + +DROP TABLE t1,t2; diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index b31853ccf02..2d74ea9c518 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -2563,10 +2563,13 @@ Item_cond::fix_fields(THD *thd, Item **ref) (item= *li.ref())->check_cols(1)) return TRUE; /* purecov: inspected */ used_tables_cache|= item->used_tables(); - tmp_table_map= item->not_null_tables(); - not_null_tables_cache|= tmp_table_map; - and_tables_cache&= tmp_table_map; - const_item_cache&= item->const_item(); + if (!item->const_item()) + { + tmp_table_map= item->not_null_tables(); + not_null_tables_cache|= tmp_table_map; + and_tables_cache&= tmp_table_map; + const_item_cache= FALSE; + } with_sum_func= with_sum_func || item->with_sum_func; if (item->maybe_null) maybe_null=1; |