diff options
| -rw-r--r-- | mysql-test/r/join_outer.result | 2 | ||||
| -rw-r--r-- | mysql-test/r/null.result | 19 | ||||
| -rw-r--r-- | mysql-test/t/null.test | 23 | ||||
| -rw-r--r-- | sql/item_cmpfunc.h | 1 |
4 files changed, 44 insertions, 1 deletions
diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result index ae4d99e6241..5778b2f9b72 100644 --- a/mysql-test/r/join_outer.result +++ b/mysql-test/r/join_outer.result @@ -91,7 +91,7 @@ grp a c id a c d NULL NULL NULL NULL NULL NULL explain select t1.*,t2.* from t1,t2 where t1.a=t2.a and isnull(t2.a)=1; Comment -Impossible WHERE noticed after reading const tables +Impossible WHERE explain select t1.*,t2.* from t1 left join t2 on t1.a=t2.a where isnull(t2.a)=1; table type possible_keys key key_len ref rows Extra t1 ALL NULL NULL NULL NULL 7 diff --git a/mysql-test/r/null.result b/mysql-test/r/null.result index ba2161d3147..41a1a06aebe 100644 --- a/mysql-test/r/null.result +++ b/mysql-test/r/null.result @@ -109,3 +109,22 @@ a b c d 0 0000-00-00 00:00:00 0 0 0000-00-00 00:00:00 0 drop table t1; +CREATE TABLE t1(i int, KEY(i)); +INSERT INTO t1 VALUES(1); +INSERT INTO t1 SELECT i*2 FROM t1; +INSERT INTO t1 SELECT i*2 FROM t1; +INSERT INTO t1 SELECT i*2 FROM t1; +INSERT INTO t1 SELECT i*2 FROM t1; +INSERT INTO t1 SELECT i*2 FROM t1; +INSERT INTO t1 SELECT i*2 FROM t1; +INSERT INTO t1 SELECT i*2 FROM t1; +INSERT INTO t1 SELECT i*2 FROM t1; +INSERT INTO t1 SELECT i*2 FROM t1; +EXPLAIN SELECT * FROM t1 WHERE i=2 OR i IS NULL; +table type possible_keys key key_len ref rows Extra +t1 range i i 5 NULL 10 Using where; Using index +ALTER TABLE t1 CHANGE i i int NOT NULL; +EXPLAIN SELECT * FROM t1 WHERE i=2 OR i IS NULL; +table type possible_keys key key_len ref rows Extra +t1 range i i 4 NULL 7 Using where; Using index +DROP TABLE t1; diff --git a/mysql-test/t/null.test b/mysql-test/t/null.test index 6fea7f0d10d..fc9b0a8aff2 100644 --- a/mysql-test/t/null.test +++ b/mysql-test/t/null.test @@ -79,3 +79,26 @@ INSERT INTO t1 (d) values (null),(null); select * from t1; drop table t1; +# Test case for bug #4256 + +CREATE TABLE t1(i int, KEY(i)); + +INSERT INTO t1 VALUES(1); + +INSERT INTO t1 SELECT i*2 FROM t1; +INSERT INTO t1 SELECT i*2 FROM t1; +INSERT INTO t1 SELECT i*2 FROM t1; +INSERT INTO t1 SELECT i*2 FROM t1; +INSERT INTO t1 SELECT i*2 FROM t1; +INSERT INTO t1 SELECT i*2 FROM t1; +INSERT INTO t1 SELECT i*2 FROM t1; +INSERT INTO t1 SELECT i*2 FROM t1; +INSERT INTO t1 SELECT i*2 FROM t1; + +EXPLAIN SELECT * FROM t1 WHERE i=2 OR i IS NULL; + +ALTER TABLE t1 CHANGE i i int NOT NULL; + +EXPLAIN SELECT * FROM t1 WHERE i=2 OR i IS NULL; + +DROP TABLE t1; diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h index a0bcd864d4b..236ebb8d28b 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -469,6 +469,7 @@ public: if (!args[0]->maybe_null) { used_tables_cache= 0; /* is always false */ + const_item_cache= 1; cached_value= (longlong) 0; } else |