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)


BUILD/compile-pentium-valgrind-max:
  Remove FORCE_INIT_OF_VARS to spot wrong LINT_INIT() options
mysql-test/r/distinct.result:
  Update of test results (new optimizer)
mysql-test/r/null_key.result:
  Update after ref_or_null optimization
mysql-test/r/subselect.result:
  Update after ref_or_null optimization
mysql-test/t/null_key.test:
  New tests for ref_or_null optimization
sql/sql_select.cc:
  Added ref_or_null optimization
  Optimized find_best_combinations() and read-functions
sql/sql_select.h:
  Added ref_or_null optimization
sql/sql_yacc.yy:
  Fixed bug in ALTER TABLE ... MODIFY integer-column
sql/table.cc:
  Safety fix for ALTER TABLE .. MODIFY
sql/unireg.cc:
  Safety fix for ALTER TABLE .. MODIFY

3 files changed, 110 insertions, 8 deletions

diff --git a/mysql-test/r/distinct.result b/mysql-test/r/distinct.result
index 9ebcd1fb915..c5841f28830 100644
--- a/mysql-test/r/distinct.result
+++ b/mysql-test/r/distinct.result
@@ -173,9 +173,9 @@ INSERT INTO t2 values (1),(2),(3);
 INSERT INTO t3 VALUES (1,'1'),(2,'2'),(1,'1'),(2,'2');
 explain SELECT distinct t3.a FROM t3,t2,t1 WHERE t3.a=t1.b AND t1.a=t2.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t3	index	a	a	5	NULL	6	Using index; Using temporary
-1	SIMPLE	t2	index	a	a	4	NULL	5	Using index; Distinct
-1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t2.a	1	Using where; Distinct
+1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	2	Using temporary
+1	SIMPLE	t2	ref	a	a	4	test.t1.a	2	Using index
+1	SIMPLE	t3	ref	a	a	5	test.t1.b	2	Using where; Using index
 SELECT distinct t3.a FROM t3,t2,t1 WHERE t3.a=t1.b AND t1.a=t2.a;
 a
 1
diff --git a/mysql-test/r/null_key.result b/mysql-test/r/null_key.result
index 289290ba08c..7dc0b4bfdd3 100644
--- a/mysql-test/r/null_key.result
+++ b/mysql-test/r/null_key.result
@@ -21,7 +21,10 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	range	a,b	a	9	NULL	3	Using where; Using index
 explain select * from t1 where (a is null or a = 7) and b=7;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ref	a,b	b	4	const	2	Using where
+1	SIMPLE	t1	ref_or_null	a,b	a	9	const,const	2	Using where; Using index
+explain select * from t1 where (a is null or a = 7) and b=7 order by a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref_or_null	a,b	a	9	const,const	2	Using where; Using index; Using filesort
 explain select * from t1 where (a is null and b>a) or a is null and b=7 limit 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ref	a,b	a	5	const	3	Using where; Using index
@@ -56,13 +59,15 @@ NULL	9
 NULL	9
 select * from t1 where (a is null or a = 7) and b=7;
 a	b
-NULL	7
 7	7
+NULL	7
 select * from t1 where a is null and b=9 or a is null and b=7 limit 3;
 a	b
 NULL	7
 NULL	9
 NULL	9
+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;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -84,7 +89,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	range	a,b	a	5	NULL	5	Using where
 explain select * from t1 where (a is null or a = 7) and b=7 and c=0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	a,b	NULL	NULL	NULL	12	Using where
+1	SIMPLE	t1	ref_or_null	a,b	a	5	const	4	Using where
 explain select * from t1 where (a is null and b>a) or a is null and b=7 limit 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ref	a,b	a	5	const	3	Using where
@@ -125,8 +130,8 @@ NULL	9	0
 NULL	9	0
 select * from t1 where (a is null or a = 7) and b=7 and c=0;
 a	b	c
-NULL	7	0
 7	7	0
+NULL	7	0
 select * from t1 where a is null and b=9 or a is null and b=7 limit 3;
 a	b	c
 NULL	7	0
@@ -136,6 +141,103 @@ select * from t1 where b like "6%";
 a	b	c
 6	6	0
 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);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref_or_null	a,b	a	10	const,const	2	Using where; Using index
+select * from t1 where a = 7 and (b=7 or b is null);
+a	b
+7	7
+7	NULL
+explain select * from t1 where (a = 7 or a is null) and (b=7 or b is null);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	a,b	a	10	NULL	4	Using where; Using index
+select * from t1 where (a = 7 or a is null) and (b=7 or b is null);
+a	b
+NULL	7
+7	NULL
+7	7
+explain select * from t1 where (a = 7 or a is null) and (a = 7 or a is null);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref_or_null	a	a	5	const	5	Using where; Using index
+select * from t1 where (a = 7 or a is null) and (a = 7 or a is null);
+a	b
+7	NULL
+7	7
+NULL	7
+NULL	9
+NULL	9
+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;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	
+1	SIMPLE	t1	ref	a,b	a	10	test.t2.a,const	2	Using where; Using index
+drop index b on t1;
+explain select * from t2,t1 where t1.a=t2.a and b is null;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	
+1	SIMPLE	t1	ref	a	a	10	test.t2.a,const	2	Using where; Using index
+select * from t2,t1 where t1.a=t2.a and b is null;
+a	a	b
+7	7	NULL
+8	8	NULL
+explain select * from t2,t1 where t1.a=t2.a and (b= 7 or b is null);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	
+1	SIMPLE	t1	ref_or_null	a	a	10	test.t2.a,const	4	Using where; Using index
+select * from t2,t1 where t1.a=t2.a and (b= 7 or b is null);
+a	a	b
+7	7	7
+7	7	NULL
+8	8	7
+8	8	NULL
+explain select * from t2,t1 where (t1.a=t2.a or t1.a is null) and b= 7;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	
+1	SIMPLE	t1	ref_or_null	a	a	10	test.t2.a,const	4	Using where; Using index
+select * from t2,t1 where (t1.a=t2.a or t1.a is null) and b= 7;
+a	a	b
+7	7	7
+7	NULL	7
+8	8	7
+8	NULL	7
+explain select * from t2,t1 where (t1.a=t2.a or t1.a is null) and (b= 7 or b is null);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	
+1	SIMPLE	t1	ref_or_null	a	a	5	test.t2.a	4	Using where; Using index
+select * from t2,t1 where (t1.a=t2.a or t1.a is null) and (b= 7 or b is null);
+a	a	b
+7	7	NULL
+7	7	7
+7	NULL	7
+8	8	NULL
+8	8	7
+8	NULL	7
+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;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	4	
+1	SIMPLE	t1	ref_or_null	a	a	5	test.t2.a	4	Using where; Using index
+explain select * from t2,t1 where t1.a<=>t2.a or (t1.a is null and t1.b <> 9);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	4	
+1	SIMPLE	t1	ref_or_null	a	a	5	test.t2.a	4	Using where; Using index
+select * from t2,t1 where t1.a<=>t2.a or (t1.a is null and t1.b <> 9);
+a	a	b
+7	7	NULL
+7	7	7
+7	NULL	7
+8	NULL	7
+NULL	NULL	7
+NULL	NULL	9
+NULL	NULL	9
+6	6	6
+6	NULL	7
+drop table t1,t2;
 CREATE TABLE t1 (
 id int(10) unsigned NOT NULL auto_increment,
 uniq_id int(10) unsigned default NULL,
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index c41434336e5..354394e804c 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -827,7 +827,7 @@ a	t1.a in (select t2.a from t2)
 explain SELECT t1.a, t1.a in (select t2.a from t2) FROM t1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	index	NULL	PRIMARY	4	NULL	4	Using index
-2	DEPENDENT SUBQUERY	t2	index	a	a	5	NULL	3	Using where; Using index
+2	DEPENDENT SUBQUERY	t2	ref_or_null	a	a	5	const	2	Using where; Using index
 drop table t1,t2;
 create table t1 (a float);
 select 10.5 IN (SELECT * from t1 LIMIT 1);