Remove FORCE_INIT_OF_VARS when compiling for valgrind/purify to spot wrong LINT_INIT() options

Fixed bug in ALTER TABLE ... MODIFY integer-column
Added ref_or_null optimization (needed for subqueries)

1 files changed, 34 insertions, 1 deletions

diff --git a/mysql-test/t/null_key.test b/mysql-test/t/null_key.test
index 18d0d368891..7d9500e90dd 100644
--- a/mysql-test/t/null_key.test
+++ b/mysql-test/t/null_key.test
@@ -14,6 +14,7 @@ explain select * from t1 where a=2 and b = 2;
 explain select * from t1 where a<=>b limit 2;
 explain select * from t1 where (a is null or a > 0 and a < 3) and b < 5 limit 3;
 explain select * from t1 where (a is null or a = 7) and b=7;
+explain select * from t1 where (a is null or a = 7) and b=7 order by a;
 explain select * from t1 where (a is null and b>a) or a is null and b=7 limit 2;
 explain select * from t1 where a is null and b=9 or a is null and b=7 limit 3;
 explain select * from t1 where a > 1 and a < 3 limit 1;
@@ -25,6 +26,8 @@ select * from t1 where (a is null or a > 0 and a < 3) and b < 5 limit 3;
 select * from t1 where (a is null or a > 0 and a < 3) and b > 7 limit 3;
 select * from t1 where (a is null or a = 7) and b=7;
 select * from t1 where a is null and b=9 or a is null and b=7 limit 3;
+create table t2 like t1;
+insert into t2 select * from t1;
 alter table t1 modify b blob not null, add c int not null, drop key a, add unique key (a,b(20),c), drop key b, add key (b(10));
 explain select * from t1 where a is null and b = 2;
 explain select * from t1 where a is null and b = 2 and c=0;
@@ -47,8 +50,38 @@ select * from t1 where (a is null or a > 0 and a < 3) and b > 7 limit 3;
 select * from t1 where (a is null or a = 7) and b=7 and c=0;
 select * from t1 where a is null and b=9 or a is null and b=7 limit 3;
 select * from t1 where b like "6%";
-drop table t1;
 
+#
+# Test ref_or_null optimization
+#
+drop table t1;
+rename table t2 to t1;
+alter table t1 modify b int null;
+insert into t1 values (7,null), (8,null), (8,7);
+explain select * from t1 where a = 7 and (b=7 or b is null);
+select * from t1 where a = 7 and (b=7 or b is null);
+explain select * from t1 where (a = 7 or a is null) and (b=7 or b is null);
+select * from t1 where (a = 7 or a is null) and (b=7 or b is null);
+explain select * from t1 where (a = 7 or a is null) and (a = 7 or a is null);
+select * from t1 where (a = 7 or a is null) and (a = 7 or a is null);
+create table t2 (a int);
+insert into t2 values (7),(8);
+explain select * from t2 straight_join t1 where t1.a=t2.a and b is null;
+drop index b on t1;
+explain select * from t2,t1 where t1.a=t2.a and b is null;
+select * from t2,t1 where t1.a=t2.a and b is null;
+explain select * from t2,t1 where t1.a=t2.a and (b= 7 or b is null);
+select * from t2,t1 where t1.a=t2.a and (b= 7 or b is null);
+explain select * from t2,t1 where (t1.a=t2.a or t1.a is null) and b= 7;
+select * from t2,t1 where (t1.a=t2.a or t1.a is null) and b= 7;
+explain select * from t2,t1 where (t1.a=t2.a or t1.a is null) and (b= 7 or b is null);
+select * from t2,t1 where (t1.a=t2.a or t1.a is null) and (b= 7 or b is null);
+insert into t2 values (null),(6);
+delete from t1 where a=8;
+explain select * from t2,t1 where t1.a=t2.a or t1.a is null;
+explain select * from t2,t1 where t1.a<=>t2.a or (t1.a is null and t1.b <> 9);
+select * from t2,t1 where t1.a<=>t2.a or (t1.a is null and t1.b <> 9);
+drop table t1,t2;
 
 #
 # The following failed for Matt Loschert