merge fix

mysql-test/r/innodb.result:
  Auto merged
mysql-test/r/join_outer.result:
  Auto merged
mysql-test/t/innodb.test:
  Auto merged
sql/ha_innodb.cc:
  Auto merged
sql/item.cc:
  Auto merged
sql/item_strfunc.cc:
  Auto merged
sql/sql_acl.cc:
  Auto merged
sql/sql_db.cc:
  Auto merged
sql/sql_parse.cc:
  Auto merged
sql/sql_select.cc:
  Auto merged
sql/sql_table.cc:
  Auto merged

16 files changed, 274 insertions, 51 deletions

diff --git a/mysql-test/r/ctype_latin1_de.result b/mysql-test/r/ctype_latin1_de.result
index b79bc67138c..630fef9b679 100644
--- a/mysql-test/r/ctype_latin1_de.result
+++ b/mysql-test/r/ctype_latin1_de.result
@@ -212,3 +212,55 @@ select * from t1 where match a against ("te*" in boolean mode)+0;
 a
 test
 drop table t1;
+create table t1 (word varchar(255) not null, word2 varchar(255) not null, index(word));
+insert into t1 (word) values ('ss'),(0xDF),(0xE4),('ae');
+update t1 set word2=word;
+select word, word=0xdf as t from t1 having t > 0;
+word	t
+ß	1
+select word, word=cast(0xdf AS CHAR) as t from t1 having t > 0;
+word	t
+ss	1
+ß	1
+select * from t1 where word=0xDF;
+word	word2
+ß	ß
+select * from t1 where word=CAST(0xDF as CHAR);
+word	word2
+ss	ss
+ß	ß
+select * from t1 where word2=0xDF;
+word	word2
+ß	ß
+select * from t1 where word2=CAST(0xDF as CHAR);
+word	word2
+ss	ss
+ß	ß
+select * from t1 where word='ae';
+word	word2
+ä	ä
+ae	ae
+select * from t1 where word= 0xe4 or word=CAST(0xe4 as CHAR);
+word	word2
+ä	ä
+ae	ae
+select * from t1 where word between 0xDF and 0xDF;
+word	word2
+ß	ß
+select * from t1 where word between CAST(0xDF AS CHAR) and CAST(0xDF AS CHAR);
+word	word2
+ss	ss
+ß	ß
+select * from t1 where word like 'ae';
+word	word2
+ae	ae
+select * from t1 where word like 'AE';
+word	word2
+ae	ae
+select * from t1 where word like 0xDF;
+word	word2
+ß	ß
+select * from t1 where word like CAST(0xDF as CHAR);
+word	word2
+ß	ß
+drop table t1;
diff --git a/mysql-test/r/ctype_ujis.result b/mysql-test/r/ctype_ujis.result
new file mode 100644
index 00000000000..223a18f19e9
--- /dev/null
+++ b/mysql-test/r/ctype_ujis.result
@@ -0,0 +1,8 @@
+drop table if exists t1;
+create table t1 (c text);
+insert into t1 values (0xa4a2),(0xa4a3);
+select hex(left(c,1)) from t1 group by c;
+hex(left(c,1))
+A4A2
+A4A3
+drop table t1;
diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result
index 39214348244..c98f85f93d2 100644
--- a/mysql-test/r/innodb.result
+++ b/mysql-test/r/innodb.result
@@ -1244,12 +1244,6 @@ insert into t1 (a) select b from t2;
 select count(*) from t1;
 count(*)
 29267
-explain select a from t1 where a between 1 and 10000;
-table	type	possible_keys	key	key_len	ref	rows	Extra
-t1	range	PRIMARY	PRIMARY	4	NULL	14745	Using where; Using index
-explain select * from t1 where a between 1 and 10000;
-table	type	possible_keys	key	key_len	ref	rows	Extra
-t1	range	PRIMARY	PRIMARY	4	NULL	14745	Using where
 explain select * from t1 where c between 1 and 10000;
 table	type	possible_keys	key	key_len	ref	rows	Extra
 t1	range	c	c	5	NULL	1	Using where
diff --git a/mysql-test/r/join.result b/mysql-test/r/join.result
index 9f6a8762325..e063b5c3e02 100644
--- a/mysql-test/r/join.result
+++ b/mysql-test/r/join.result
@@ -274,3 +274,101 @@ SELECT emp.rate_code, lr.base_rate FROM t1 AS emp LEFT JOIN t2 AS lr USING (site
 rate_code	base_rate
 cust	20
 drop table t1,t2;
+CREATE TABLE t1 (ID INTEGER NOT NULL PRIMARY KEY, Value1 VARCHAR(255));
+CREATE TABLE t2 (ID INTEGER NOT NULL PRIMARY KEY, Value2 VARCHAR(255));
+INSERT INTO t1 VALUES (1, 'A');
+INSERT INTO t2 VALUES (1, 'B');
+SELECT * FROM t1 NATURAL JOIN t2 WHERE 1 AND (Value1 = 'A' AND Value2 <> 'B');
+ID	Value1	ID	Value2
+SELECT * FROM t1 NATURAL JOIN t2 WHERE 1 AND Value1 = 'A' AND Value2 <> 'B';
+ID	Value1	ID	Value2
+SELECT * FROM t1 NATURAL JOIN t2 WHERE (Value1 = 'A' AND Value2 <> 'B') AND 1;
+ID	Value1	ID	Value2
+drop table t1,t2;
+create table t1 (i int);
+create table t2 (i int);
+create table t3 (i int);
+insert into t1 values(1),(2);
+insert into t2 values(2),(3);
+insert into t3 values (2),(4);
+select * from t1 natural left join t2;
+i	i
+1	NULL
+2	2
+select * from t1 left join t2 on (t1.i=t2.i);
+i	i
+1	NULL
+2	2
+select * from t1 natural left join t2 natural left join t3;
+i	i	i
+1	NULL	NULL
+2	2	2
+select * from t1 left join t2 on (t1.i=t2.i) left join t3 on (t2.i=t3.i);
+i	i	i
+1	NULL	NULL
+2	2	2
+select * from t3 natural right join t2;
+i	i
+2	2
+NULL	3
+select * from t3 right join t2 on (t3.i=t2.i);
+i	i
+2	2
+NULL	3
+select * from t3 natural right join t2 natural right join t1;
+i	i	i
+NULL	NULL	1
+2	2	2
+select * from t3 right join t2 on (t3.i=t2.i) right join t1 on (t2.i=t1.i);
+i	i	i
+NULL	NULL	1
+2	2	2
+select * from t1,t2 natural left join t3 order by t1.i,t2.i,t3.i;
+i	i	i
+1	2	2
+1	3	NULL
+2	2	2
+2	3	NULL
+select * from t1,t2 left join t3 on (t2.i=t3.i) order by t1.i,t2.i,t3.i;
+i	i	i
+1	2	2
+1	3	NULL
+2	2	2
+2	3	NULL
+select t1.i,t2.i,t3.i from t2 natural left join t3,t1 order by t1.i,t2.i,t3.i;
+i	i	i
+1	2	2
+1	3	NULL
+2	2	2
+2	3	NULL
+select t1.i,t2.i,t3.i from t2 left join t3 on (t2.i=t3.i),t1 order by t1.i,t2.i,t3.i;
+i	i	i
+1	2	2
+1	3	NULL
+2	2	2
+2	3	NULL
+select * from t1,t2 natural right join t3 order by t1.i,t2.i,t3.i;
+i	i	i
+1	NULL	4
+1	2	2
+2	NULL	4
+2	2	2
+select * from t1,t2 right join t3 on (t2.i=t3.i) order by t1.i,t2.i,t3.i;
+i	i	i
+1	NULL	4
+1	2	2
+2	NULL	4
+2	2	2
+select t1.i,t2.i,t3.i from t2 natural right join t3,t1 order by t1.i,t2.i,t3.i;
+i	i	i
+1	NULL	4
+1	2	2
+2	NULL	4
+2	2	2
+select t1.i,t2.i,t3.i from t2 right join t3 on (t2.i=t3.i),t1 order by t1.i,t2.i,t3.i;
+i	i	i
+1	NULL	4
+1	2	2
+2	NULL	4
+2	2	2
+drop table t1,t2,t3;
diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result
index fe30458d8de..81266f6562e 100644
--- a/mysql-test/r/join_outer.result
+++ b/mysql-test/r/join_outer.result
@@ -351,12 +351,7 @@ select t1.name, t2.name, t2.id,t3.id from t1 right join t2 on (t1.id = t2.owner)
 name	name	id	id
 Antonio Paz	El Gato	1	1
 Antonio Paz	Perrito	2	1
-NULL	Happy	3	1
-NULL	El Gato	1	2
-NULL	Perrito	2	2
-NULL	Happy	3	2
-NULL	El Gato	1	3
-NULL	Perrito	2	3
+NULL	NULL	NULL	2
 Thimble Smith	Happy	3	3
 select t1.name, t2.name, t2.id, t2.owner, t3.id from t1 left join t2 on (t1.id = t2.owner) right join t1 as t3 on t3.id=t2.owner;
 name	name	id	owner	id
diff --git a/mysql-test/r/repair.result b/mysql-test/r/repair.result
index 8b50f9a92e8..adc09ded0e2 100644
--- a/mysql-test/r/repair.result
+++ b/mysql-test/r/repair.result
@@ -4,4 +4,8 @@ repair table t1 use_frm;
 Table	Op	Msg_type	Msg_text
 test.t1	repair	warning	Number of rows changed from 0 to 1
 test.t1	repair	status	OK
-drop table if exists t1;
+alter table t1 TYPE=HEAP;
+repair table t1 use_frm;
+Table	Op	Msg_type	Msg_text
+test.t1	repair	error	The handler for the table doesn't support repair
+drop table t1;
diff --git a/mysql-test/r/rpl_alter.result b/mysql-test/r/rpl_alter.result
index 1dc73c6524a..729c7df6808 100644
--- a/mysql-test/r/rpl_alter.result
+++ b/mysql-test/r/rpl_alter.result
@@ -4,18 +4,18 @@ reset master;
 reset slave;
 drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
 slave start;
-drop database if exists d1;
-create database d1;
-create table d1.t1 ( n int);
-alter table d1.t1 add m int;
-insert into d1.t1 values (1,2);
-create table d1.t2 (n int);
-insert into d1.t2 values (45);
-rename table d1.t2 to d1.t3, d1.t1 to d1.t2;
-select * from d1.t2;
+drop database if exists test_$1;
+create database test_$1;
+create table test_$1.t1 ( n int);
+alter table test_$1.t1 add m int;
+insert into test_$1.t1 values (1,2);
+create table test_$1.t2 (n int);
+insert into test_$1.t2 values (45);
+rename table test_$1.t2 to test_$1.t3, test_$1.t1 to test_$1.t2;
+select * from test_$1.t2;
 n	m
 1	2
-select * from d1.t3;
+select * from test_$1.t3;
 n
 45
-drop database d1;
+drop database test_$1;
diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result
index 1ad3043b4b2..a4c2533ec1a 100644
--- a/mysql-test/r/select.result
+++ b/mysql-test/r/select.result
@@ -3402,13 +3402,7 @@ a	a	a
 select * from t1 natural left join (t1 as t2 left join t1 as t3 using (a));
 a	a	a
 1	1	1
-2	1	NULL
-3	1	NULL
-1	2	NULL
 2	2	2
-3	2	NULL
-1	3	NULL
-2	3	NULL
 3	3	3
 select * from (t1 as t2 left join t1 as t3 using (a)) right join t1 on t1.a>1;
 a	a	a
@@ -3464,13 +3458,7 @@ a	a	a
 select * from t1 natural join (t1 as t2 left join t1 as t3 using (a));
 a	a	a
 1	1	1
-2	1	NULL
-3	1	NULL
-1	2	NULL
 2	2	2
-3	2	NULL
-1	3	NULL
-2	3	NULL
 3	3	3
 select * from (t1 as t2 left join t1 as t3 using (a)) natural join t1;
 a	a	a
diff --git a/mysql-test/t/ctype_latin1_de-master.opt b/mysql-test/t/ctype_latin1_de-master.opt
index 98accd58c46..895a62364d6 100644
--- a/mysql-test/t/ctype_latin1_de-master.opt
+++ b/mysql-test/t/ctype_latin1_de-master.opt
@@ -1 +1,2 @@
---default-character-set=latin1_de
+--default-character-set=latin1_de --new
+
diff --git a/mysql-test/t/ctype_latin1_de.test b/mysql-test/t/ctype_latin1_de.test
index 4b96f5f5867..6353650f420 100644
--- a/mysql-test/t/ctype_latin1_de.test
+++ b/mysql-test/t/ctype_latin1_de.test
@@ -45,3 +45,26 @@ select * from t1 where a like "test%";
 select * from t1 where a like "te_t"; 
 select * from t1 where match a against ("te*" in boolean mode)+0;
 drop table t1;
+
+#
+# Test bug report #152 (problem with index on latin1_de)
+#
+
+create table t1 (word varchar(255) not null, word2 varchar(255) not null, index(word));
+insert into t1 (word) values ('ss'),(0xDF),(0xE4),('ae');
+update t1 set word2=word;
+select word, word=0xdf as t from t1 having t > 0;
+select word, word=cast(0xdf AS CHAR) as t from t1 having t > 0;
+select * from t1 where word=0xDF;
+select * from t1 where word=CAST(0xDF as CHAR);
+select * from t1 where word2=0xDF;
+select * from t1 where word2=CAST(0xDF as CHAR);
+select * from t1 where word='ae';
+select * from t1 where word= 0xe4 or word=CAST(0xe4 as CHAR);
+select * from t1 where word between 0xDF and 0xDF;
+select * from t1 where word between CAST(0xDF AS CHAR) and CAST(0xDF AS CHAR);
+select * from t1 where word like 'ae';
+select * from t1 where word like 'AE';
+select * from t1 where word like 0xDF;
+select * from t1 where word like CAST(0xDF as CHAR);
+drop table t1;
diff --git a/mysql-test/t/ctype_ujis-master.opt b/mysql-test/t/ctype_ujis-master.opt
new file mode 100644
index 00000000000..1f4183d5027
--- /dev/null
+++ b/mysql-test/t/ctype_ujis-master.opt
@@ -0,0 +1 @@
+--default-character-set=ujis
diff --git a/mysql-test/t/ctype_ujis.test b/mysql-test/t/ctype_ujis.test
new file mode 100644
index 00000000000..cd1dc965000
--- /dev/null
+++ b/mysql-test/t/ctype_ujis.test
@@ -0,0 +1,13 @@
+#
+# Tests with the ujis character set
+#
+drop table if exists t1;
+
+#
+# Test problem with LEFT()
+#
+
+create table t1 (c text);
+insert into t1 values (0xa4a2),(0xa4a3);
+select hex(left(c,1)) from t1 group by c;
+drop table t1;
diff --git a/mysql-test/t/innodb.test b/mysql-test/t/innodb.test
index 4fb9351020e..d65b9b1638e 100644
--- a/mysql-test/t/innodb.test
+++ b/mysql-test/t/innodb.test
@@ -837,8 +837,6 @@ insert into t1 (a) select b from t2;
 insert into t2 (a) select b from t1;
 insert into t1 (a) select b from t2;
 select count(*) from t1;
-explain select a from t1 where a between 1 and 10000;
-explain select * from t1 where a between 1 and 10000;
 explain select * from t1 where c between 1 and 10000;
 update t1 set c=a;
 explain select * from t1 where c between 1 and 10000;
diff --git a/mysql-test/t/join.test b/mysql-test/t/join.test
index 63ec90f854c..08cc5731723 100644
--- a/mysql-test/t/join.test
+++ b/mysql-test/t/join.test
@@ -271,3 +271,49 @@ INSERT INTO t2 VALUES ('rivercats','cust',20);
 SELECT emp.rate_code, lr.base_rate FROM t1 AS emp LEFT JOIN t2 AS lr USING (siteid, rate_code) WHERE emp.emp_id = 'psmith' AND lr.siteid = 'rivercats';
 SELECT emp.rate_code, lr.base_rate FROM t1 AS emp LEFT JOIN t2 AS lr USING (siteid, rate_code) WHERE lr.siteid = 'rivercats' AND emp.emp_id = 'psmith';
 drop table t1,t2;
+
+#
+# Problem with internal list handling when reducing WHERE
+#
+
+CREATE TABLE t1 (ID INTEGER NOT NULL PRIMARY KEY, Value1 VARCHAR(255));
+CREATE TABLE t2 (ID INTEGER NOT NULL PRIMARY KEY, Value2 VARCHAR(255));
+INSERT INTO t1 VALUES (1, 'A');
+INSERT INTO t2 VALUES (1, 'B');
+
+SELECT * FROM t1 NATURAL JOIN t2 WHERE 1 AND (Value1 = 'A' AND Value2 <> 'B');
+SELECT * FROM t1 NATURAL JOIN t2 WHERE 1 AND Value1 = 'A' AND Value2 <> 'B';
+SELECT * FROM t1 NATURAL JOIN t2 WHERE (Value1 = 'A' AND Value2 <> 'B') AND 1;
+drop table t1,t2;
+
+#
+# Test combination of join methods
+#
+
+create table t1 (i int);
+create table t2 (i int);
+create table t3 (i int);
+insert into t1 values(1),(2);
+insert into t2 values(2),(3);
+insert into t3 values (2),(4);
+
+select * from t1 natural left join t2;
+select * from t1 left join t2 on (t1.i=t2.i);
+select * from t1 natural left join t2 natural left join t3;
+select * from t1 left join t2 on (t1.i=t2.i) left join t3 on (t2.i=t3.i);
+
+select * from t3 natural right join t2;
+select * from t3 right join t2 on (t3.i=t2.i);
+select * from t3 natural right join t2 natural right join t1;
+select * from t3 right join t2 on (t3.i=t2.i) right join t1 on (t2.i=t1.i);
+
+select * from t1,t2 natural left join t3 order by t1.i,t2.i,t3.i;
+select * from t1,t2 left join t3 on (t2.i=t3.i) order by t1.i,t2.i,t3.i;
+select t1.i,t2.i,t3.i from t2 natural left join t3,t1 order by t1.i,t2.i,t3.i;
+select t1.i,t2.i,t3.i from t2 left join t3 on (t2.i=t3.i),t1 order by t1.i,t2.i,t3.i;
+
+select * from t1,t2 natural right join t3 order by t1.i,t2.i,t3.i;
+select * from t1,t2 right join t3 on (t2.i=t3.i) order by t1.i,t2.i,t3.i;
+select t1.i,t2.i,t3.i from t2 natural right join t3,t1 order by t1.i,t2.i,t3.i;
+select t1.i,t2.i,t3.i from t2 right join t3 on (t2.i=t3.i),t1 order by t1.i,t2.i,t3.i;
+drop table t1,t2,t3;
diff --git a/mysql-test/t/repair.test b/mysql-test/t/repair.test
index 6d79014b23d..b901fb3467f 100644
--- a/mysql-test/t/repair.test
+++ b/mysql-test/t/repair.test
@@ -5,4 +5,6 @@
 drop table if exists t1;
 create table t1 SELECT 1,"table 1";
 repair table t1 use_frm;
-drop table if exists t1;
+alter table t1 TYPE=HEAP;
+repair table t1 use_frm;
+drop table t1;
diff --git a/mysql-test/t/rpl_alter.test b/mysql-test/t/rpl_alter.test
index 710dd2d09d6..f1fbf60776b 100644
--- a/mysql-test/t/rpl_alter.test
+++ b/mysql-test/t/rpl_alter.test
@@ -1,19 +1,19 @@
 source include/master-slave.inc;
-drop database if exists d1;
-create database d1;
-create table d1.t1 ( n int);
-alter table d1.t1 add m int;
-insert into d1.t1 values (1,2);
-create table d1.t2 (n int);
-insert into d1.t2 values (45);
-rename table d1.t2 to d1.t3, d1.t1 to d1.t2;
+drop database if exists test_$1;
+create database test_$1;
+create table test_$1.t1 ( n int);
+alter table test_$1.t1 add m int;
+insert into test_$1.t1 values (1,2);
+create table test_$1.t2 (n int);
+insert into test_$1.t2 values (45);
+rename table test_$1.t2 to test_$1.t3, test_$1.t1 to test_$1.t2;
 save_master_pos;
 connection slave;
 sync_with_master;
-select * from d1.t2;
-select * from d1.t3;
+select * from test_$1.t2;
+select * from test_$1.t3;
 connection master;
-drop database d1;
+drop database test_$1;
 save_master_pos;
 connection slave;
 sync_with_master;