Merge miguel@bk-internal.mysql.com:/home/bk/mysql-4.1

into hegel.local:/home/miguel/bk/mysql-4.1

16 files changed, 199 insertions, 16 deletions

diff --git a/mysql-test/r/analyse.result b/mysql-test/r/analyse.result
index f18b925460c..b51afab5b54 100644
--- a/mysql-test/r/analyse.result
+++ b/mysql-test/r/analyse.result
@@ -31,3 +31,68 @@ EXPLAIN SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE();
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	1	
 2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+create table t1 (a int not null);
+create table t2 select * from t1 where 0=1 procedure analyse();
+show create table t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `Field_name` char(255) NOT NULL default '',
+  `Min_value` char(255) default NULL,
+  `Max_value` char(255) default NULL,
+  `Min_length` bigint(11) NOT NULL default '0',
+  `Max_length` bigint(11) NOT NULL default '0',
+  `Empties_or_zeros` bigint(11) NOT NULL default '0',
+  `Nulls` bigint(11) NOT NULL default '0',
+  `Avg_value_or_avg_length` char(255) NOT NULL default '',
+  `Std` char(255) default NULL,
+  `Optimal_fieldtype` char(64) NOT NULL default ''
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+select * from t1 where 0=1 procedure analyse();
+Field_name	Min_value	Max_value	Min_length	Max_length	Empties_or_zeros	Nulls	Avg_value_or_avg_length	Std	Optimal_fieldtype
+insert into t1 values(1);
+drop table t2;
+create table t2 select * from t1 where 0=1 procedure analyse();
+show create table t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `Field_name` char(255) NOT NULL default '',
+  `Min_value` char(255) default NULL,
+  `Max_value` char(255) default NULL,
+  `Min_length` bigint(11) NOT NULL default '0',
+  `Max_length` bigint(11) NOT NULL default '0',
+  `Empties_or_zeros` bigint(11) NOT NULL default '0',
+  `Nulls` bigint(11) NOT NULL default '0',
+  `Avg_value_or_avg_length` char(255) NOT NULL default '',
+  `Std` char(255) default NULL,
+  `Optimal_fieldtype` char(64) NOT NULL default ''
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+select * from t2;
+Field_name	Min_value	Max_value	Min_length	Max_length	Empties_or_zeros	Nulls	Avg_value_or_avg_length	Std	Optimal_fieldtype
+insert into t2 select * from t1 procedure analyse();
+select * from t2;
+Field_name	Min_value	Max_value	Min_length	Max_length	Empties_or_zeros	Nulls	Avg_value_or_avg_length	Std	Optimal_fieldtype
+test.t1.a	1	1	1	1	0	0	1.0000	0.0000	ENUM('1') NOT NULL
+insert into t1 values(2);
+drop table t2;
+create table t2 select * from t1 where 0=1 procedure analyse();
+show create table t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `Field_name` char(255) NOT NULL default '',
+  `Min_value` char(255) default NULL,
+  `Max_value` char(255) default NULL,
+  `Min_length` bigint(11) NOT NULL default '0',
+  `Max_length` bigint(11) NOT NULL default '0',
+  `Empties_or_zeros` bigint(11) NOT NULL default '0',
+  `Nulls` bigint(11) NOT NULL default '0',
+  `Avg_value_or_avg_length` char(255) NOT NULL default '',
+  `Std` char(255) default NULL,
+  `Optimal_fieldtype` char(64) NOT NULL default ''
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+select * from t2;
+Field_name	Min_value	Max_value	Min_length	Max_length	Empties_or_zeros	Nulls	Avg_value_or_avg_length	Std	Optimal_fieldtype
+insert into t2 select * from t1 procedure analyse();
+select * from t2;
+Field_name	Min_value	Max_value	Min_length	Max_length	Empties_or_zeros	Nulls	Avg_value_or_avg_length	Std	Optimal_fieldtype
+test.t1.a	1	2	1	1	0	0	1.5000	0.5000	ENUM('1','2') NOT NULL
+drop table t1,t2;
diff --git a/mysql-test/r/bdb.result b/mysql-test/r/bdb.result
index bee40eac30d..a544bbbf0b7 100644
--- a/mysql-test/r/bdb.result
+++ b/mysql-test/r/bdb.result
@@ -203,7 +203,7 @@ a
 2
 check table t1;
 Table	Op	Msg_type	Msg_text
-test.t1	check	error	The storage engine for the table doesn't support check
+test.t1	check	note	The storage engine for the table doesn't support check
 drop table t1;
 create table t1 (a int,b varchar(20)) engine=bdb;
 insert into t1 values (1,""), (2,"testing");
diff --git a/mysql-test/r/ctype_ujis.result b/mysql-test/r/ctype_ujis.result
index b66ede3bed4..cc062094535 100644
--- a/mysql-test/r/ctype_ujis.result
+++ b/mysql-test/r/ctype_ujis.result
@@ -92,3 +92,22 @@ select @ujis3 = CONVERT(@utf83 USING ujis);
 select @ujis4 = CONVERT(@utf84 USING ujis);
 @ujis4 = CONVERT(@utf84 USING ujis)
 1
+drop table if exists t1;
+Warnings:
+Note	1051	Unknown table 't1'
+create table t1 (c1 varchar(8)) default character set 'ujis';
+insert into t1 values (0xA4A2),(0xA2A2),(0xA4A2);
+select c1 as 'no index' from t1 where c1 like cast(concat(0xA4A2, '%') as char character set ujis);
+no index
+あ
+あ
+create index idx_c1 on t1(c1);
+select c1 as 'using index' from t1 where c1 like cast(concat(0xA4A2, '%') as char character set ujis);
+using index
+あ
+あ
+select c1 as 'no index' from t1 where c1 like cast(concat('%',0xA4A2, '%') as char character set ujis);
+no index
+あ
+あ
+drop table t1;
diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result
index 5192251795b..57b109e1ee6 100644
--- a/mysql-test/r/func_group.result
+++ b/mysql-test/r/func_group.result
@@ -648,3 +648,11 @@ select a from t1 having a=1;
 a
 1
 drop table t1;
+create table t1 (a int);
+select variance(2) from t1;
+variance(2)
+NULL
+select stddev(2) from t1;
+stddev(2)
+NULL
+drop table t1;
diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result
index d3225679b3e..bdba9e4c3e4 100644
--- a/mysql-test/r/func_str.result
+++ b/mysql-test/r/func_str.result
@@ -639,3 +639,14 @@ name
 aaaaaaaaaaccccc
 bbbbbbbbbbddddd
 drop table t1, t2;
+create table t1 (c1 INT);
+insert into t1 (c1) values ('21474836461');
+Warnings:
+Warning	1265	Data truncated for column 'c1' at row 1
+show warnings;
+Level	Code	Message
+Warning	1265	Data truncated for column 'c1' at row 1
+select * from t1;
+c1
+2147483647
+drop table t1;
diff --git a/mysql-test/r/isam.result b/mysql-test/r/isam.result
index 4c698075150..2c7b3a4a568 100644
--- a/mysql-test/r/isam.result
+++ b/mysql-test/r/isam.result
@@ -49,14 +49,14 @@ test.t1	optimize	status	OK
 check table t1,t2;
 Table	Op	Msg_type	Msg_text
 test.t1	check	status	OK
-test.t2	check	error	The storage engine for the table doesn't support check
+test.t2	check	note	The storage engine for the table doesn't support check
 repair table t1,t2;
 Table	Op	Msg_type	Msg_text
 test.t1	repair	status	OK
-test.t2	repair	error	The storage engine for the table doesn't support repair
+test.t2	repair	note	The storage engine for the table doesn't support repair
 check table t2,t1;
 Table	Op	Msg_type	Msg_text
-test.t2	check	error	The storage engine for the table doesn't support check
+test.t2	check	note	The storage engine for the table doesn't support check
 test.t1	check	status	OK
 lock tables t1 write;
 check table t2,t1;
diff --git a/mysql-test/r/key.result b/mysql-test/r/key.result
index 115f15bacb6..b8387389075 100644
--- a/mysql-test/r/key.result
+++ b/mysql-test/r/key.result
@@ -206,3 +206,19 @@ select i from t1 where b=repeat(_utf8 'b',310);
 i
 1
 drop table t1;
+CREATE TABLE t1 (id int unsigned auto_increment, name char(50), primary key (id)) engine=myisam;
+insert into t1 (name) values ('a'), ('b'),('c'),('d'),('e'),('f'),('g');
+explain select 1 from t1 where id =2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	Using index
+explain select 1 from t1 where id =2 or id=3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	2	Using where; Using index
+explain select name from t1 where id =2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	
+ALTER TABLE t1 DROP PRIMARY KEY, ADD INDEX (id);
+explain select 1 from t1 where id =2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	id	id	4	const	1	Using where; Using index
+drop table t1;
diff --git a/mysql-test/r/key_primary.result b/mysql-test/r/key_primary.result
index 14ca90b3dd2..7726a8e1d63 100644
--- a/mysql-test/r/key_primary.result
+++ b/mysql-test/r/key_primary.result
@@ -13,7 +13,7 @@ t1
 AB%
 describe select * from t1 where t1="ABC";
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	const	PRIMARY	PRIMARY	3	const	1	
+1	SIMPLE	t1	const	PRIMARY	PRIMARY	3	const	1	Using index
 describe select * from t1 where t1="ABCD";
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
diff --git a/mysql-test/r/null_key.result b/mysql-test/r/null_key.result
index 4dfd17991cc..e57ac321f46 100644
--- a/mysql-test/r/null_key.result
+++ b/mysql-test/r/null_key.result
@@ -12,7 +12,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ref	a,b	a	9	const,const	1	Using where; Using index
 explain select * from t1 where a=2 and b = 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	const	a,b	a	9	const,const	1	
+1	SIMPLE	t1	const	a,b	a	9	const,const	1	Using index
 explain select * from t1 where a<=>b limit 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index	NULL	a	9	NULL	12	Using where; Using index
diff --git a/mysql-test/r/repair.result b/mysql-test/r/repair.result
index f81bbd063ea..dbca5c39a6c 100644
--- a/mysql-test/r/repair.result
+++ b/mysql-test/r/repair.result
@@ -7,7 +7,7 @@ test.t1	repair	status	OK
 alter table t1 ENGINE=HEAP;
 repair table t1 use_frm;
 Table	Op	Msg_type	Msg_text
-test.t1	repair	error	The storage engine for the table doesn't support repair
+test.t1	repair	note	The storage engine for the table doesn't support repair
 drop table t1;
 create table t1(id int PRIMARY KEY, st varchar(10), KEY st_key(st));
 insert into t1 values(1, "One");
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 32770614221..a6e5d90b6c4 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -357,10 +357,10 @@ INSERT INTO t8 (pseudo,email) VALUES ('joce1','test1');
 INSERT INTO t8 (pseudo,email) VALUES ('2joce1','2test1');
 EXPLAIN EXTENDED SELECT pseudo,(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce')) FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t8	const	PRIMARY	PRIMARY	35	const	1	
-4	SUBQUERY	t8	const	PRIMARY	PRIMARY	35		1	
+1	PRIMARY	t8	const	PRIMARY	PRIMARY	35	const	1	Using index
+4	SUBQUERY	t8	const	PRIMARY	PRIMARY	35		1	Using index
 2	SUBQUERY	t8	const	PRIMARY	PRIMARY	35	const	1	
-3	SUBQUERY	t8	const	PRIMARY	PRIMARY	35		1	
+3	SUBQUERY	t8	const	PRIMARY	PRIMARY	35		1	Using index
 Warnings:
 Note	1003	select high_priority test.t8.pseudo AS `pseudo`,(select test.t8.email AS `email` from test.t8 where (test.t8.pseudo = (select test.t8.pseudo AS `pseudo` from test.t8 where (test.t8.pseudo = _latin1'joce')))) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from test.t8 where (test.t8.pseudo = (select test.t8.pseudo AS `pseudo` from test.t8 where (test.t8.pseudo = _latin1'joce')))
 SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo,email FROM
@@ -537,7 +537,7 @@ Warnings:
 Note	1003	select high_priority max(test.t1.numreponse) AS `MAX(numreponse)` from test.t1 where (test.t1.numeropost = _latin1'1')
 EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM t1 WHERE numeropost='1');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	const	PRIMARY,numreponse	PRIMARY	7	const,const	1	
+1	PRIMARY	t1	const	PRIMARY,numreponse	PRIMARY	7	const,const	1	Using index
 2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
 Warnings:
 Note	1003	select high_priority test.t1.numreponse AS `numreponse` from test.t1 where ((test.t1.numeropost = _latin1'1') and (test.t1.numreponse = 3))
diff --git a/mysql-test/t/analyse.test b/mysql-test/t/analyse.test
index 6aca345b282..47f3473584b 100644
--- a/mysql-test/t/analyse.test
+++ b/mysql-test/t/analyse.test
@@ -15,3 +15,26 @@ select * from t2;
 drop table t1,t2;
 
 EXPLAIN SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE();
+
+#
+# Test with impossible where
+#
+create table t1 (a int not null);
+create table t2 select * from t1 where 0=1 procedure analyse();
+show create table t2;
+select * from t1 where 0=1 procedure analyse();
+insert into t1 values(1);
+drop table t2;
+create table t2 select * from t1 where 0=1 procedure analyse();
+show create table t2;
+select * from t2;
+insert into t2 select * from t1 procedure analyse();
+select * from t2;
+insert into t1 values(2);
+drop table t2;
+create table t2 select * from t1 where 0=1 procedure analyse();
+show create table t2;
+select * from t2;
+insert into t2 select * from t1 procedure analyse();
+select * from t2;
+drop table t1,t2;
diff --git a/mysql-test/t/ctype_ujis.test b/mysql-test/t/ctype_ujis.test
index abfded5a6c2..6281f2a4249 100644
--- a/mysql-test/t/ctype_ujis.test
+++ b/mysql-test/t/ctype_ujis.test
@@ -61,3 +61,15 @@ select @ujis2 = CONVERT(@utf82 USING ujis);
 select @ujis3 = CONVERT(@utf83 USING ujis);
 select @ujis4 = CONVERT(@utf84 USING ujis);
 
+#
+# Testing with '%' and index (Bug #3438)
+#
+
+drop table if exists t1;
+create table t1 (c1 varchar(8)) default character set 'ujis';
+insert into t1 values (0xA4A2),(0xA2A2),(0xA4A2);
+select c1 as 'no index' from t1 where c1 like cast(concat(0xA4A2, '%') as char character set ujis);
+create index idx_c1 on t1(c1);
+select c1 as 'using index' from t1 where c1 like cast(concat(0xA4A2, '%') as char character set ujis);
+select c1 as 'no index' from t1 where c1 like cast(concat('%',0xA4A2, '%') as char character set ujis);
+drop table t1;
diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test
index a1acf5f89eb..7966a2262f4 100644
--- a/mysql-test/t/func_group.test
+++ b/mysql-test/t/func_group.test
@@ -387,3 +387,12 @@ insert into t1 values (1);
 select max(a) as b from t1 having b=1;
 select a from t1 having a=1;
 drop table t1;
+
+#
+# Bug #3435: variance(const), stddev(const) and an empty table
+#
+
+create table t1 (a int);
+select variance(2) from t1;
+select stddev(2) from t1;
+drop table t1;
diff --git a/mysql-test/t/func_str.test b/mysql-test/t/func_str.test
index 1c85f83baac..7efcac030af 100644
--- a/mysql-test/t/func_str.test
+++ b/mysql-test/t/func_str.test
@@ -358,21 +358,19 @@ select substring_index("1abcd;2abcd;3abcd;4abcd", ';', 2),substring_index("1abcd
 explain extended select md5('hello'), sha('abc'), sha1('abc'), soundex(''), 'mood' sounds like 'mud', aes_decrypt(aes_encrypt('abc','1'),'1'),concat('*',space(5),'*'), reverse('abc'), rpad('a',4,'1'), lpad('a',4,'1'),  concat_ws(',','',NULL,'a'),make_set(255,_latin2'a',_latin2'b',_latin2'c'),elt(2,1),locate("a","b",2),format(130,10),char(0),conv(130,16,10),hex(130),binary 'HE', export_set(255,_latin2'y',_latin2'n',_latin2' '),FIELD('b' COLLATE latin1_bin,'A','B'),FIND_IN_SET(_latin1'B',_latin1'a,b,c,d'),collation(conv(130,16,10)), coercibility(conv(130,16,10)),length('\n\t\r\b\0\_\%\\'),bit_length('\n\t\r\b\0\_\%\\'),bit_length('\n\t\r\b\0\_\%\\'),concat('monty',' was here ','again'),length('hello'),char(ascii('h')),ord('h'),quote(1/0),crc32("123"),replace('aaaa','a','b'),insert('txs',2,1,'hi'),left(_latin2'a',1),right(_latin2'a',1),lcase(_latin2'a'),ucase(_latin2'a'),SUBSTR('abcdefg',3,2),substring_index("1abcd;2abcd;3abcd;4abcd", ';', 2),trim(_latin2' a '),ltrim(_latin2' a '),rtrim(_latin2' a '), decode(encode(repeat("a",100000),"monty"),"monty");
 
 #
-# Bug #2182
+# lpad returns incorrect result (Bug #2182)
 #
 
 SELECT lpad(12345, 5, "#");
 
 #
-# Bug #2972
+# Problem the the CONV() function (Bug #2972)
 #
  
 SELECT conv(71, 10, 36), conv('1Z', 36, 10);
 
-
-
 #
-# Bug #3089
+# Bug in SUBSTRING when mixed with CONCAT and ORDER BY (Bug #3089)
 #
 
 create table t1 (id int(1), str varchar(10)) DEFAULT CHARSET=utf8;
@@ -382,3 +380,13 @@ insert into t2 values (1,'cccccccccc'), (2,'dddddddddd');
 select substring(concat(t1.str, t2.str), 1, 15) "name" from t1, t2 
 where t2.id=t1.id order by name;
 drop table t1, t2;
+
+#
+# Test case for conversion of long string value to integer (Bug #3472)
+#
+
+create table t1 (c1 INT);
+insert into t1 (c1) values ('21474836461');
+show warnings;
+select * from t1;
+drop table t1;
diff --git a/mysql-test/t/key.test b/mysql-test/t/key.test
index cdaf6062771..4fa85477318 100644
--- a/mysql-test/t/key.test
+++ b/mysql-test/t/key.test
@@ -204,3 +204,15 @@ select i from t1 where a=repeat(_utf8 0xD0B1,200);
 select i from t1 where b=repeat(_utf8 'b',310);
 drop table t1;
 
+#
+# Test of key read with primary key (Bug #3497)
+#
+
+CREATE TABLE t1 (id int unsigned auto_increment, name char(50), primary key (id)) engine=myisam;
+insert into t1 (name) values ('a'), ('b'),('c'),('d'),('e'),('f'),('g');
+explain select 1 from t1 where id =2;
+explain select 1 from t1 where id =2 or id=3;
+explain select name from t1 where id =2;
+ALTER TABLE t1 DROP PRIMARY KEY, ADD INDEX (id);
+explain select 1 from t1 where id =2;
+drop table t1;