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

into mysql.com:/home/my/mysql-4.1


sql/item_func.cc:
  Auto merged
sql/sql_base.cc:
  Auto merged
sql/sql_union.cc:
  Auto merged

7 files changed, 321 insertions, 14 deletions

diff --git a/mysql-test/r/ctype_collate.result b/mysql-test/r/ctype_collate.result
index add730fe68f..c837d676b6e 100644
--- a/mysql-test/r/ctype_collate.result
+++ b/mysql-test/r/ctype_collate.result
@@ -337,15 +337,12 @@ SELECT latin1_f,count(*) FROM t1 GROUP BY latin1_f COLLATE latin1_german2_ci;
 latin1_f	count(*)
 A	4
 AD	2
-AE	2
-Ä	2
+AE	4
 AF	2
 B	2
-SS	2
-ß	1
+SS	3
 U	2
-UE	2
-Ü	2
+UE	4
 Y	2
 Z	2
 SELECT latin1_f,count(*) FROM t1 GROUP BY latin1_f COLLATE latin1_general_ci;
@@ -431,13 +428,10 @@ A
 AD
 AE
 AF
-Ä
 B
 U
 UE
-Ü
 SS
-ß
 Y
 Z
 SELECT DISTINCT latin1_f COLLATE latin1_general_ci FROM t1;
diff --git a/mysql-test/r/endspace.result b/mysql-test/r/endspace.result
new file mode 100644
index 00000000000..d2519523f36
--- /dev/null
+++ b/mysql-test/r/endspace.result
@@ -0,0 +1,196 @@
+drop table if exists t1;
+select 'a' = 'a', 'a' = 'a ', 'a ' = 'a';
+'a' = 'a'	'a' = 'a '	'a ' = 'a'
+1	1	1
+select 'a\0' = 'a', 'a\0' < 'a', 'a\0' > 'a';
+'a\0' = 'a'	'a\0' < 'a'	'a\0' > 'a'
+0	1	0
+select 'a' = 'a\0', 'a' < 'a\0', 'a' > 'a\0';
+'a' = 'a\0'	'a' < 'a\0'	'a' > 'a\0'
+0	0	1
+select 'a\0' = 'a ', 'a\0' < 'a ', 'a\0' > 'a ';
+'a\0' = 'a '	'a\0' < 'a '	'a\0' > 'a '
+0	1	0
+select 'a ' = 'a\0', 'a ' < 'a\0', 'a ' > 'a\0';
+'a ' = 'a\0'	'a ' < 'a\0'	'a ' > 'a\0'
+0	0	1
+select 'a  a' > 'a', 'a  \0' < 'a';
+'a  a' > 'a'	'a  \0' < 'a'
+1	1
+select binary 'a  a' > 'a', binary 'a  \0' > 'a', binary 'a\0' > 'a';
+binary 'a  a' > 'a'	binary 'a  \0' > 'a'	binary 'a\0' > 'a'
+1	1	1
+create table t1 (text1 varchar(32) not NULL, KEY key1 (text1));
+insert into t1 values ('teststring'), ('nothing'), ('teststring\t');
+check table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	check	status	OK
+select * from t1 ignore key (key1) where text1='teststring' or text1 like 'teststring_%';
+text1
+teststring
+teststring	
+select * from t1 where text1='teststring' or text1 like 'teststring_%';
+text1
+teststring	
+teststring
+select * from t1 where text1='teststring' or text1 > 'teststring\t';
+text1
+teststring
+select * from t1 order by text1;
+text1
+nothing
+teststring	
+teststring
+explain select * from t1 order by text1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	key1	32	NULL	3	Using index
+alter table t1 modify text1 char(32) binary not null;
+check table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	check	status	OK
+select * from t1 ignore key (key1) where text1='teststring' or text1 like 'teststring_%';
+text1
+teststring
+teststring	
+select * from t1 where text1='teststring' or text1 like 'teststring_%';
+text1
+teststring	
+teststring
+select * from t1 where text1='teststring' or text1 > 'teststring\t';
+text1
+teststring
+select text1, length(text1) from t1 order by text1;
+text1	length(text1)
+nothing	7
+teststring		11
+teststring	10
+select text1, length(text1) from t1 order by binary text1;
+text1	length(text1)
+nothing	7
+teststring	10
+teststring		11
+alter table t1 modify text1 blob not null, drop key key1, add key key1 (text1(20));
+insert into t1 values ('teststring ');
+select concat('|', text1, '|') from t1 order by text1;
+concat('|', text1, '|')
+|nothing|
+|teststring|
+|teststring	|
+|teststring |
+alter table t1 modify text1 text not null, pack_keys=1;
+select * from t1 where text1 like 'teststring_%';
+text1
+teststring	
+teststring 
+select * from t1 where text1='teststring' or text1 like 'teststring_%';
+text1
+teststring
+teststring	
+teststring 
+select * from t1 where text1='teststring' or text1 > 'teststring\t';
+text1
+teststring
+teststring 
+select concat('|', text1, '|') from t1 order by text1;
+concat('|', text1, '|')
+|nothing|
+|teststring	|
+|teststring|
+|teststring |
+drop table t1;
+create table t1 (text1 varchar(32) not NULL, KEY key1 (text1)) pack_keys=0;
+insert into t1 values ('teststring'), ('nothing'), ('teststring\t');
+select * from t1 where text1='teststring' or text1 like 'teststring_%';
+text1
+teststring	
+teststring
+select * from t1 where text1='teststring' or text1 >= 'teststring\t';
+text1
+teststring	
+teststring
+drop table t1;
+create table t1 (text1 varchar(32) not NULL, KEY key1 using BTREE (text1)) engine=heap;
+insert into t1 values ('teststring'), ('nothing'), ('teststring\t');
+select * from t1 ignore key (key1) where text1='teststring' or text1 like 'teststring_%';
+text1
+teststring
+teststring	
+select * from t1 where text1='teststring' or text1 like 'teststring_%';
+text1
+teststring	
+teststring
+select * from t1 where text1='teststring' or text1 >= 'teststring\t';
+text1
+teststring	
+teststring
+select * from t1 order by text1;
+text1
+nothing
+teststring	
+teststring
+explain select * from t1 order by text1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	key1	32	NULL	3	
+alter table t1 modify text1 char(32) binary not null;
+select * from t1 order by text1;
+text1
+nothing
+teststring	
+teststring
+drop table t1;
+create table t1 (text1 varchar(32) not NULL, KEY key1 (text1)) engine=innodb;
+insert into t1 values ('teststring'), ('nothing'), ('teststring\t');
+check table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	check	status	OK
+select * from t1 where text1='teststring' or text1 like 'teststring_%';
+text1
+teststring	
+teststring
+select * from t1 where text1='teststring' or text1 > 'teststring\t';
+text1
+teststring
+select * from t1 order by text1;
+text1
+nothing
+teststring	
+teststring
+explain select * from t1 order by text1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	key1	32	NULL	4	Using index
+alter table t1 modify text1 char(32) binary not null;
+select * from t1 order by text1;
+text1
+nothing
+teststring	
+teststring
+alter table t1 modify text1 blob not null, drop key key1, add key key1 (text1(20));
+insert into t1 values ('teststring ');
+select concat('|', text1, '|') from t1 order by text1;
+concat('|', text1, '|')
+|nothing|
+|teststring|
+|teststring	|
+|teststring |
+alter table t1 modify text1 text not null, pack_keys=1;
+select * from t1 where text1 like 'teststring_%';
+text1
+teststring	
+teststring 
+select text1, length(text1) from t1 where text1='teststring' or text1 like 'teststring_%';
+text1	length(text1)
+teststring	10
+teststring		11
+teststring 	11
+select text1, length(text1) from t1 where text1='teststring' or text1 >= 'teststring\t';
+text1	length(text1)
+teststring	10
+teststring		11
+teststring 	11
+select concat('|', text1, '|') from t1 order by text1;
+concat('|', text1, '|')
+|nothing|
+|teststring	|
+|teststring|
+|teststring |
+drop table t1;
diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result
index 38dcf17ae1d..d2ea3c4a87c 100644
--- a/mysql-test/r/func_time.result
+++ b/mysql-test/r/func_time.result
@@ -555,11 +555,12 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 Warnings:
 Note	1003	select high_priority no_cache period_add(_latin1'9602',-(12)) AS `period_add("9602",-12)`,period_diff(199505,_latin1'9404') AS `period_diff(199505,"9404")`,from_days(to_days(_latin1'960101')) AS `from_days(to_days("960101"))`,dayofmonth(_latin1'1997-01-02') AS `dayofmonth("1997-01-02")`,month(_latin1'1997-01-02') AS `month("1997-01-02")`,monthname(_latin1'1972-03-04') AS `monthname("1972-03-04")`,dayofyear(_latin1'0000-00-00') AS `dayofyear("0000-00-00")`,hour(_latin1'1997-03-03 23:03:22') AS `HOUR("1997-03-03 23:03:22")`,minute(_latin1'23:03:22') AS `MINUTE("23:03:22")`,second(230322) AS `SECOND(230322)`,quarter(980303) AS `QUARTER(980303)`,week(_latin1'1998-03-03',0) AS `WEEK("1998-03-03")`,yearweek(_latin1'2000-01-01',1) AS `yearweek("2000-01-01",1)`,week(19950101,1) AS `week(19950101,1)`,year(_latin1'98-02-03') AS `year("98-02-03")`,(weekday(to_days(curdate())) - weekday(to_days(now()))) AS `weekday(curdate())-weekday(now())`,dayname(to_days(_latin1'1962-03-03')) AS `dayname("1962-03-03")`,unix_timestamp() AS `unix_timestamp()`,sec_to_time((time_to_sec(_latin1'0:30:47') / 6.21)) AS `sec_to_time(time_to_sec("0:30:47")/6.21)`,curtime() AS `curtime()`,utc_time() AS `utc_time()`,curdate() AS `curdate()`,utc_date() AS `utc_date()`,utc_timestamp() AS `utc_timestamp()`,date_format(_latin1'1997-01-02 03:04:05',_latin1'%M %W %D %Y %y %m %d %h %i %s %w') AS `date_format("1997-01-02 03:04:05", "%M %W %D %Y %y %m %d %h %i %s %w")`,from_unixtime(unix_timestamp(_latin1'1994-03-02 10:11:12')) AS `from_unixtime(unix_timestamp("1994-03-02 10:11:12"))`,(_latin1'1997-12-31 23:59:59' + interval 1 second) AS `"1997-12-31 23:59:59" + INTERVAL 1 SECOND`,(_latin1'1998-01-01 00:00:00' - interval 1 second) AS `"1998-01-01 00:00:00" - INTERVAL 1 SECOND`,(_latin1'1997-12-31' + interval 1 day) AS `INTERVAL 1 DAY + "1997-12-31"`,extract(year from _latin1'1999-01-02 10:11:12') AS `extract(YEAR FROM "1999-01-02 10:11:12")`,(_latin1'1997-12-31 23:59:59' + interval 1 second) AS `date_add("1997-12-31 23:59:59",INTERVAL 1 SECOND)`
+SET @TMP=NOW();
 CREATE TABLE t1 (d DATETIME);
 INSERT INTO t1 VALUES (NOW());
 INSERT INTO t1 VALUES (NOW());
 INSERT INTO t1 VALUES (NOW());
-SELECT count(*) FROM t1 WHERE d>FROM_DAYS(TO_DAYS(NOW())) AND d<=FROM_DAYS(TO_DAYS(NOW())+1);
+SELECT count(*) FROM t1 WHERE d>FROM_DAYS(TO_DAYS(@TMP)) AND d<=FROM_DAYS(TO_DAYS(@TMP)+1);
 count(*)
 3
 DROP TABLE t1;
diff --git a/mysql-test/r/heap_btree.result b/mysql-test/r/heap_btree.result
index ef63b1d4e00..f92a8653a69 100644
--- a/mysql-test/r/heap_btree.result
+++ b/mysql-test/r/heap_btree.result
@@ -173,9 +173,22 @@ f1	f2
 drop table t1;
 create table t1 (btn char(10) not null, key using BTREE (btn)) engine=heap;
 insert into t1 values ("hello"),("hello"),("hello"),("hello"),("hello"),("a"),("b"),("c"),("d"),("e"),("f"),("g"),("h"),("i");
-explain select * from t1 where btn like "q%";
+explain select * from t1 where btn like "i%";
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	btn	NULL	NULL	NULL	14	Using where
+1	SIMPLE	t1	range	btn	btn	10	NULL	1	Using where
+explain select * from t1 where btn like "h%";
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	btn	btn	10	NULL	4	Using where
+explain select * from t1 where btn like "a%";
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	btn	btn	10	NULL	1	Using where
+explain select * from t1 where btn like "b%";
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	btn	btn	10	NULL	1	Using where
+select * from t1 where btn like "ff%";
+btn
+select * from t1 where btn like " %";
+btn
 select * from t1 where btn like "q%";
 btn
 alter table t1 add column new_col char(1) not null, add key using BTREE (btn,new_col), drop key btn;
diff --git a/mysql-test/t/endspace.test b/mysql-test/t/endspace.test
new file mode 100644
index 00000000000..a9933ff93b5
--- /dev/null
+++ b/mysql-test/t/endspace.test
@@ -0,0 +1,96 @@
+#
+# Test problem with characters < ' ' at end of strings (Bug #3152)
+#
+
+-- source include/have_innodb.inc
+--disable_warnings
+drop table if exists t1;
+--enable_warnings
+
+select 'a' = 'a', 'a' = 'a ', 'a ' = 'a';
+select 'a\0' = 'a', 'a\0' < 'a', 'a\0' > 'a';
+select 'a' = 'a\0', 'a' < 'a\0', 'a' > 'a\0';
+select 'a\0' = 'a ', 'a\0' < 'a ', 'a\0' > 'a ';
+select 'a ' = 'a\0', 'a ' < 'a\0', 'a ' > 'a\0';
+select 'a  a' > 'a', 'a  \0' < 'a';
+select binary 'a  a' > 'a', binary 'a  \0' > 'a', binary 'a\0' > 'a';
+
+#
+# Test MyISAM tables.
+#
+
+create table t1 (text1 varchar(32) not NULL, KEY key1 (text1));
+insert into t1 values ('teststring'), ('nothing'), ('teststring\t');
+check table t1;
+select * from t1 ignore key (key1) where text1='teststring' or text1 like 'teststring_%';
+select * from t1 where text1='teststring' or text1 like 'teststring_%';
+select * from t1 where text1='teststring' or text1 > 'teststring\t';
+select * from t1 order by text1;
+explain select * from t1 order by text1;
+
+alter table t1 modify text1 char(32) binary not null;
+check table t1;
+select * from t1 ignore key (key1) where text1='teststring' or text1 like 'teststring_%';
+select * from t1 where text1='teststring' or text1 like 'teststring_%';
+select * from t1 where text1='teststring' or text1 > 'teststring\t';
+select text1, length(text1) from t1 order by text1;
+select text1, length(text1) from t1 order by binary text1;
+
+alter table t1 modify text1 blob not null, drop key key1, add key key1 (text1(20));
+insert into t1 values ('teststring ');
+select concat('|', text1, '|') from t1 order by text1;
+
+alter table t1 modify text1 text not null, pack_keys=1;
+select * from t1 where text1 like 'teststring_%';
+select * from t1 where text1='teststring' or text1 like 'teststring_%';
+select * from t1 where text1='teststring' or text1 > 'teststring\t';
+select concat('|', text1, '|') from t1 order by text1;
+drop table t1;
+
+create table t1 (text1 varchar(32) not NULL, KEY key1 (text1)) pack_keys=0;
+insert into t1 values ('teststring'), ('nothing'), ('teststring\t');
+select * from t1 where text1='teststring' or text1 like 'teststring_%';
+select * from t1 where text1='teststring' or text1 >= 'teststring\t';
+drop table t1;
+
+# Test HEAP tables (with BTREE keys)
+
+create table t1 (text1 varchar(32) not NULL, KEY key1 using BTREE (text1)) engine=heap;
+insert into t1 values ('teststring'), ('nothing'), ('teststring\t');
+select * from t1 ignore key (key1) where text1='teststring' or text1 like 'teststring_%';
+select * from t1 where text1='teststring' or text1 like 'teststring_%';
+select * from t1 where text1='teststring' or text1 >= 'teststring\t';
+select * from t1 order by text1;
+explain select * from t1 order by text1;
+
+alter table t1 modify text1 char(32) binary not null;
+select * from t1 order by text1;
+drop table t1;
+
+#
+# Test InnoDB tables
+#
+
+create table t1 (text1 varchar(32) not NULL, KEY key1 (text1)) engine=innodb;
+insert into t1 values ('teststring'), ('nothing'), ('teststring\t');
+check table t1;
+select * from t1 where text1='teststring' or text1 like 'teststring_%';
+select * from t1 where text1='teststring' or text1 > 'teststring\t';
+select * from t1 order by text1;
+explain select * from t1 order by text1;
+
+alter table t1 modify text1 char(32) binary not null;
+select * from t1 order by text1;
+
+alter table t1 modify text1 blob not null, drop key key1, add key key1 (text1(20));
+insert into t1 values ('teststring ');
+select concat('|', text1, '|') from t1 order by text1;
+
+alter table t1 modify text1 text not null, pack_keys=1;
+select * from t1 where text1 like 'teststring_%';
+
+# The following gives wrong result in InnoDB
+select text1, length(text1) from t1 where text1='teststring' or text1 like 'teststring_%';
+select text1, length(text1) from t1 where text1='teststring' or text1 >= 'teststring\t';
+select concat('|', text1, '|') from t1 order by text1;
+drop table t1;
diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test
index ecb440ff079..4b8c26a00d8 100644
--- a/mysql-test/t/func_time.test
+++ b/mysql-test/t/func_time.test
@@ -273,10 +273,11 @@ select strcmp(concat(utc_date(),' ',utc_time()),utc_timestamp())=0;
 
 explain extended select period_add("9602",-12),period_diff(199505,"9404"),from_days(to_days("960101")),dayofmonth("1997-01-02"), month("1997-01-02"), monthname("1972-03-04"),dayofyear("0000-00-00"),HOUR("1997-03-03 23:03:22"),MINUTE("23:03:22"),SECOND(230322),QUARTER(980303),WEEK("1998-03-03"),yearweek("2000-01-01",1),week(19950101,1),year("98-02-03"),weekday(curdate())-weekday(now()),dayname("1962-03-03"),unix_timestamp(),sec_to_time(time_to_sec("0:30:47")/6.21),curtime(),utc_time(),curdate(),utc_date(),utc_timestamp(),date_format("1997-01-02 03:04:05", "%M %W %D %Y %y %m %d %h %i %s %w"),from_unixtime(unix_timestamp("1994-03-02 10:11:12")),"1997-12-31 23:59:59" + INTERVAL 1 SECOND,"1998-01-01 00:00:00" - INTERVAL 1 SECOND,INTERVAL 1 DAY + "1997-12-31", extract(YEAR FROM "1999-01-02 10:11:12"),date_add("1997-12-31 23:59:59",INTERVAL 1 SECOND);
 
+SET @TMP=NOW();
 CREATE TABLE t1 (d DATETIME);
 INSERT INTO t1 VALUES (NOW());
 INSERT INTO t1 VALUES (NOW());
 INSERT INTO t1 VALUES (NOW());
-SELECT count(*) FROM t1 WHERE d>FROM_DAYS(TO_DAYS(NOW())) AND d<=FROM_DAYS(TO_DAYS(NOW())+1);
+SELECT count(*) FROM t1 WHERE d>FROM_DAYS(TO_DAYS(@TMP)) AND d<=FROM_DAYS(TO_DAYS(@TMP)+1);
 DROP TABLE t1;
 
diff --git a/mysql-test/t/heap_btree.test b/mysql-test/t/heap_btree.test
index a520065a8b3..ad78636d002 100644
--- a/mysql-test/t/heap_btree.test
+++ b/mysql-test/t/heap_btree.test
@@ -110,7 +110,13 @@ drop table t1;
 
 create table t1 (btn char(10) not null, key using BTREE (btn)) engine=heap;
 insert into t1 values ("hello"),("hello"),("hello"),("hello"),("hello"),("a"),("b"),("c"),("d"),("e"),("f"),("g"),("h"),("i");
-explain select * from t1 where btn like "q%";
+explain select * from t1 where btn like "i%";
+explain select * from t1 where btn like "h%";
+explain select * from t1 where btn like "a%";
+explain select * from t1 where btn like "b%";
+# For the following the BTREE MAY notice that there is no possible matches
+select * from t1 where btn like "ff%";
+select * from t1 where btn like " %";
 select * from t1 where btn like "q%";
 alter table t1 add column new_col char(1) not null, add key using BTREE (btn,new_col), drop key btn;
 update t1 set new_col=left(btn,1);