Merge bk@192.168.21.1:mysql-5.1

into  mysql.com:/home/hf/work/mrg/mysql-5.1-opt


mysql-test/r/gis-rtree.result:
  Auto merged
mysql-test/t/gis-rtree.test:
  Auto merged
sql/sql_parse.cc:
  Auto merged
storage/myisam/ha_myisam.cc:
  Auto merged

16 files changed, 489 insertions, 30 deletions

diff --git a/mysql-test/r/alter_table.result b/mysql-test/r/alter_table.result
index 571ac8498b3..fa18b58a927 100644
--- a/mysql-test/r/alter_table.result
+++ b/mysql-test/r/alter_table.result
@@ -881,6 +881,42 @@ create table t1 (t varchar(255) default null, key t (t(80)))
 engine=myisam default charset=latin1;
 alter table t1 change t t text;
 drop table t1;
+CREATE TABLE t1 (a varchar(500));
+ALTER TABLE t1 ADD b GEOMETRY NOT NULL, ADD SPATIAL INDEX(b);
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` varchar(500) DEFAULT NULL,
+  `b` geometry NOT NULL,
+  SPATIAL KEY `b` (`b`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+ALTER TABLE t1 ADD KEY(b(50));
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` varchar(500) DEFAULT NULL,
+  `b` geometry NOT NULL,
+  SPATIAL KEY `b` (`b`),
+  KEY `b_2` (`b`(50))
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+ALTER TABLE t1 ADD c POINT;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` varchar(500) DEFAULT NULL,
+  `b` geometry NOT NULL,
+  `c` point DEFAULT NULL,
+  SPATIAL KEY `b` (`b`),
+  KEY `b_2` (`b`(50))
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+CREATE TABLE t2 (a INT, KEY (a(20)));
+ERROR HY000: Incorrect sub part key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique sub keys
+ALTER TABLE t1 ADD d INT;
+ALTER TABLE t1 ADD KEY (d(20));
+ERROR HY000: Incorrect sub part key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique sub keys
+ALTER TABLE t1 ADD e GEOMETRY NOT NULL, ADD SPATIAL KEY (e(30));
+ERROR HY000: Incorrect sub part key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique sub keys
+DROP TABLE t1;
 CREATE TABLE t1 (s CHAR(8) BINARY);
 INSERT INTO t1 VALUES ('test');
 SELECT LENGTH(s) FROM t1;
diff --git a/mysql-test/r/endspace.result b/mysql-test/r/endspace.result
index 003ee7ffd5e..6fb33dee826 100644
--- a/mysql-test/r/endspace.result
+++ b/mysql-test/r/endspace.result
@@ -25,10 +25,11 @@ 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_%';
+select * from t1 ignore key (key1) where text1='teststring' or 
+text1 like 'teststring_%' ORDER BY text1;
 text1
-teststring
 teststring	
+teststring
 select * from t1 where text1='teststring' or text1 like 'teststring_%';
 text1
 teststring	
@@ -48,10 +49,11 @@ 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_%';
+select * from t1 ignore key (key1) where text1='teststring' or 
+text1 like 'teststring_%' ORDER BY text1;
 text1
-teststring
 teststring	
+teststring
 select concat('|', text1, '|') from t1 where text1='teststring' or text1 like 'teststring_%';
 concat('|', text1, '|')
 |teststring	|
@@ -132,10 +134,11 @@ concat('|', text1, '|')
 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_%';
+select * from t1 ignore key (key1) where text1='teststring' or 
+text1 like 'teststring_%' ORDER BY text1;
 text1
-teststring
 teststring	
+teststring
 select * from t1 where text1='teststring' or text1 like 'teststring_%';
 text1
 teststring	
diff --git a/mysql-test/r/gis-rtree.result b/mysql-test/r/gis-rtree.result
index 96aa8a17181..b92601e7b5c 100644
--- a/mysql-test/r/gis-rtree.result
+++ b/mysql-test/r/gis-rtree.result
@@ -10,7 +10,7 @@ t1	CREATE TABLE `t1` (
   `fid` int(11) NOT NULL AUTO_INCREMENT,
   `g` geometry NOT NULL,
   PRIMARY KEY (`fid`),
-  SPATIAL KEY `g` (`g`(32))
+  SPATIAL KEY `g` (`g`)
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 INSERT INTO t1 (g) VALUES (GeomFromText('LineString(150 150, 150 150)'));
 INSERT INTO t1 (g) VALUES (GeomFromText('LineString(149 149, 151 151)'));
@@ -293,7 +293,7 @@ t2	CREATE TABLE `t2` (
   `fid` int(11) NOT NULL AUTO_INCREMENT,
   `g` geometry NOT NULL,
   PRIMARY KEY (`fid`),
-  SPATIAL KEY `g` (`g`(32))
+  SPATIAL KEY `g` (`g`)
 ) ENGINE=MyISAM AUTO_INCREMENT=101 DEFAULT CHARSET=latin1
 SELECT count(*) FROM t2;
 count(*)
@@ -803,7 +803,7 @@ CREATE TABLE t2 (geom GEOMETRY NOT NULL, SPATIAL KEY gk(geom));
 INSERT INTO t2 SELECT GeomFromText(st) FROM t1;
 ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
 drop table t1, t2;
-CREATE TABLE t1 (`geometry` geometry NOT NULL default '',SPATIAL KEY `gndx` (`geometry`(32))) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+CREATE TABLE t1 (`geometry` geometry NOT NULL default '',SPATIAL KEY `gndx` (`geometry`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 Warnings:
 Warning	1101	BLOB/TEXT column 'geometry' can't have a default value
 INSERT INTO t1 (geometry) VALUES
@@ -820,7 +820,7 @@ test.t1	check	status	OK
 drop table t1;
 CREATE TABLE t1 (
 c1 geometry NOT NULL default '',
-SPATIAL KEY i1 (c1(32))
+SPATIAL KEY i1 (c1)
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 Warnings:
 Warning	1101	BLOB/TEXT column 'c1' can't have a default value
@@ -836,7 +836,7 @@ test.t1	check	status	OK
 DROP TABLE t1;
 CREATE TABLE t1 (
 c1 geometry NOT NULL default '',
-SPATIAL KEY i1 (c1(32))
+SPATIAL KEY i1 (c1)
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 Warnings:
 Warning	1101	BLOB/TEXT column 'c1' can't have a default value
diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result
index d583eb02aeb..5cff5fec7ed 100644
--- a/mysql-test/r/group_by.result
+++ b/mysql-test/r/group_by.result
@@ -1035,12 +1035,110 @@ HAVING SUM(t1_inner.b)+t1_outer.b > 5);
 ERROR 42000: 'test.t1_outer.b' isn't in GROUP BY
 DROP TABLE t1;
 SET SQL_MODE = '';
-CREATE TABLE t1 (a INT, b INT, KEY(a));
-INSERT INTO t1 VALUES (1, 1), (2, 2), (3,3), (4,4);
-EXPLAIN SELECT a, SUM(b) FROM t1 GROUP BY a LIMIT 2;
+CREATE TABLE t1 (a INT, b INT,
+PRIMARY KEY (a),
+KEY i2(a,b));
+INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8);
+INSERT INTO t1 SELECT a + 8,b FROM t1;
+INSERT INTO t1 SELECT a + 16,b FROM t1;
+INSERT INTO t1 SELECT a + 32,b FROM t1;
+INSERT INTO t1 SELECT a + 64,b FROM t1;
+INSERT INTO t1 SELECT a + 128,b FROM t1;
+ANALYZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+EXPLAIN SELECT a FROM t1 WHERE a < 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index	NULL	a	5	NULL	4	
-EXPLAIN SELECT a, SUM(b) FROM t1 IGNORE INDEX (a) GROUP BY a LIMIT 2;
+1	SIMPLE	t1	range	PRIMARY,i2	PRIMARY	4	NULL	2	Using where; Using index
+EXPLAIN SELECT a FROM t1 WHERE a < 2 ORDER BY a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	Using temporary; Using filesort
-DROP TABLE t1;
+1	SIMPLE	t1	range	PRIMARY,i2	PRIMARY	4	NULL	2	Using where; Using index
+EXPLAIN SELECT a FROM t1 WHERE a < 2 GROUP BY a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	PRIMARY,i2	PRIMARY	4	NULL	2	Using where; Using index
+EXPLAIN SELECT a FROM t1 IGNORE INDEX (PRIMARY,i2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	256	
+EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR JOIN (PRIMARY,i2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	256	
+EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (PRIMARY,i2) GROUP BY a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	256	Using index
+EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY,i2) ORDER BY a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	256	Using index; Using filesort
+EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY)
+IGNORE INDEX FOR GROUP BY (i2) GROUP BY a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	256	Using index
+EXPLAIN SELECT a FROM t1 IGNORE INDEX (PRIMARY) IGNORE INDEX FOR ORDER BY (i2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	i2	9	NULL	256	Using index
+EXPLAIN SELECT a FROM t1 FORCE INDEX (i2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	i2	9	NULL	256	Using index
+EXPLAIN SELECT a FROM t1 USE INDEX ();
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	256	
+EXPLAIN SELECT a FROM t1 USE INDEX () USE INDEX (i2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	256	
+EXPLAIN SELECT a FROM t1 
+FORCE INDEX (PRIMARY) 
+IGNORE INDEX FOR GROUP BY (i2)
+IGNORE INDEX FOR ORDER BY (i2)
+USE INDEX (i2);
+ERROR HY000: Incorrect usage of USE INDEX and FORCE INDEX
+EXPLAIN SELECT a FROM t1 USE INDEX (i2) USE INDEX ();
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	i2	9	NULL	256	Using index
+EXPLAIN SELECT a FROM t1 FORCE INDEX ();
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1
+EXPLAIN SELECT a FROM t1 IGNORE INDEX ();
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1
+EXPLAIN SELECT a FROM t1 USE INDEX FOR JOIN (i2) 
+USE INDEX FOR GROUP BY (i2) GROUP BY a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	i2	9	NULL	256	Using index
+EXPLAIN SELECT a FROM t1 FORCE INDEX FOR JOIN (i2) 
+FORCE INDEX FOR GROUP BY (i2) GROUP BY a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	NULL	i2	4	NULL	257	Using index for group-by
+EXPLAIN SELECT a FROM t1 USE INDEX () IGNORE INDEX (i2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	256	
+EXPLAIN SELECT a FROM t1 IGNORE INDEX (i2) USE INDEX ();
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	256	
+EXPLAIN SELECT a FROM t1 
+USE INDEX FOR GROUP BY (i2) 
+USE INDEX FOR ORDER BY (i2)
+USE INDEX FOR JOIN (i2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	i2	9	NULL	256	Using index
+EXPLAIN SELECT a FROM t1 
+USE INDEX FOR JOIN (i2) 
+USE INDEX FOR JOIN (i2) 
+USE INDEX FOR JOIN (i2,i2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	i2	9	NULL	256	Using index
+EXPLAIN SELECT 1 FROM t1 WHERE a IN
+(SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	NULL	PRIMARY	4	NULL	256	Using where; Using index
+2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	256	Using where
+CREATE TABLE t2 (a INT, b INT, KEY(a));
+INSERT INTO t2 VALUES (1, 1), (2, 2), (3,3), (4,4);
+EXPLAIN SELECT a, SUM(b) FROM t2 GROUP BY a LIMIT 2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	index	NULL	a	5	NULL	4	
+EXPLAIN SELECT a, SUM(b) FROM t2 IGNORE INDEX (a) GROUP BY a LIMIT 2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	4	Using temporary; Using filesort
+EXPLAIN SELECT 1 FROM t2 WHERE a IN
+(SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	index	NULL	a	5	NULL	4	Using where; Using index
+2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	256	Using where
+DROP TABLE t1, t2;
diff --git a/mysql-test/r/key.result b/mysql-test/r/key.result
index 853b837c46e..e348a387252 100644
--- a/mysql-test/r/key.result
+++ b/mysql-test/r/key.result
@@ -489,3 +489,44 @@ EXPLAIN SELECT MAX(a) FROM t1 FORCE INDEX(a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	
 drop table t1;
+CREATE TABLE t1 (
+a INTEGER auto_increment PRIMARY KEY,
+b INTEGER NOT NULL,
+c INTEGER NOT NULL,
+d CHAR(64)
+);
+CREATE TABLE t2 (
+a INTEGER auto_increment PRIMARY KEY,
+b INTEGER NOT NULL,
+c SMALLINT NOT NULL,
+d DATETIME NOT NULL,
+e SMALLINT NOT NULL,
+f INTEGER NOT NULL,
+g INTEGER NOT NULL,  
+h SMALLINT NOT NULL,
+i INTEGER NOT NULL,
+j INTEGER NOT NULL,
+UNIQUE INDEX (b),
+INDEX (b, d, e, f, g, h, i, j, c),
+INDEX (c)
+);
+INSERT INTO t2 VALUES 
+(NULL, 1, 254, '1000-01-01 00:00:00', 257, 0, 0, 0, 0, 0),
+(NULL, 2, 1, '2004-11-30 12:00:00', 1, 0, 0, 0, 0, 0),
+(NULL, 3, 1, '2004-11-30 12:00:00', 1, 0, 0, 2, -21600, 0),
+(NULL, 4, 1, '2004-11-30 12:00:00', 1, 0, 0, 2, -10800, 0),
+(NULL, 5, 1, '2004-11-30 12:00:00', 1, 0, 0, 5, -10800, 0),
+(NULL, 6, 1, '2004-11-30 12:00:00', 102, 0, 0, 0, 0, 0),
+(NULL, 7, 1, '2004-11-30 12:00:00', 105, 2, 0, 0, 0, 0),
+(NULL, 8, 1, '2004-11-30 12:00:00', 105, 10, 0, 0, 0, 0);
+INSERT INTO t1 (b, c, d) VALUES
+(3388000, -553000, NULL),
+(3388000, -553000, NULL);
+SELECT *
+FROM t2 c JOIN t1 pa ON c.b = pa.a 
+WHERE c.c = 1
+ORDER BY c.b, c.d
+;
+a	b	c	d	e	f	g	h	i	j	a	b	c	d
+2	2	1	2004-11-30 12:00:00	1	0	0	0	0	0	2	3388000	-553000	NULL
+DROP TABLE t1, t2;
diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result
index 8a19cfcded2..c7adfb784de 100644
--- a/mysql-test/r/order_by.result
+++ b/mysql-test/r/order_by.result
@@ -958,6 +958,28 @@ a	ratio
 19	1.3333
 9	2.6667
 drop table t1;
+CREATE TABLE t1 (a INT UNSIGNED NOT NULL, b TIME);
+INSERT INTO t1 (a) VALUES (100000), (0), (100), (1000000),(10000), (1000), (10);
+UPDATE t1 SET b = SEC_TO_TIME(a);
+SELECT a, b FROM t1 ORDER BY b DESC;
+a	b
+1000000	277:46:40
+100000	27:46:40
+10000	02:46:40
+1000	00:16:40
+100	00:01:40
+10	00:00:10
+0	00:00:00
+SELECT a, b FROM t1 ORDER BY SEC_TO_TIME(a) DESC;
+a	b
+1000000	277:46:40
+100000	27:46:40
+10000	02:46:40
+1000	00:16:40
+100	00:01:40
+10	00:00:10
+0	00:00:00
+DROP TABLE t1;
 CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a), UNIQUE KEY b (b));
 INSERT INTO t1 VALUES (1,1),(2,2);
 CREATE TABLE t2 (a INT, b INT, KEY a (a,b));
diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result
index e9fda0c2ad6..f88f046f0ac 100644
--- a/mysql-test/r/select.result
+++ b/mysql-test/r/select.result
@@ -3933,4 +3933,42 @@ cc 	cc 	7
 aa  	aa	2
 aa	aa	2
 DROP TABLE t1,t2;
+CREATE TABLE t1 (
+access_id int NOT NULL default '0',
+name varchar(20) default NULL,
+rank int NOT NULL default '0',
+KEY idx (access_id)
+);
+CREATE TABLE t2 (
+faq_group_id int NOT NULL default '0',
+faq_id int NOT NULL default '0',
+access_id int default NULL,
+UNIQUE KEY idx1 (faq_id),
+KEY idx2 (faq_group_id,faq_id)
+);
+INSERT INTO t1 VALUES 
+(1,'Everyone',2),(2,'Help',3),(3,'Technical Support',1),(4,'Chat User',4);
+INSERT INTO t2 VALUES
+(261,265,1),(490,494,1);
+SELECT t2.faq_id 
+FROM t1 INNER JOIN t2 IGNORE INDEX (idx1)
+ON (t1.access_id = t2.access_id)
+LEFT JOIN t2 t
+ON (t.faq_group_id = t2.faq_group_id AND
+find_in_set(t.access_id, '1,4') < find_in_set(t2.access_id, '1,4'))
+WHERE
+t2.access_id IN (1,4) AND t.access_id IS NULL AND t2.faq_id in (265);
+faq_id
+265
+SELECT t2.faq_id 
+FROM t1 INNER JOIN t2
+ON (t1.access_id = t2.access_id)
+LEFT JOIN t2 t
+ON (t.faq_group_id = t2.faq_group_id AND
+find_in_set(t.access_id, '1,4') < find_in_set(t2.access_id, '1,4'))
+WHERE
+t2.access_id IN (1,4) AND t.access_id IS NULL AND t2.faq_id in (265);
+faq_id
+265
+DROP TABLE t1,t2;
 End of 5.0 tests
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 2afdd045203..1372ac3687c 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -3873,3 +3873,16 @@ id_1
 DROP TABLE t1;
 DROP TABLE t2;
 DROP TABLE t1xt2;
+CREATE TABLE t1 (a int);
+INSERT INTO t1 VALUES (3), (1), (2);
+SELECT 'this is ' 'a test.' AS col1, a AS col2 FROM t1;
+col1	col2
+this is a test.	3
+this is a test.	1
+this is a test.	2
+SELECT * FROM (SELECT 'this is ' 'a test.' AS col1, a AS t2 FROM t1) t;
+col1	t2
+this is a test.	3
+this is a test.	1
+this is a test.	2
+DROP table t1;
diff --git a/mysql-test/t/alter_table.test b/mysql-test/t/alter_table.test
index 297813dfead..b8ba35b78ca 100644
--- a/mysql-test/t/alter_table.test
+++ b/mysql-test/t/alter_table.test
@@ -642,6 +642,33 @@ alter table t1 change t t text;
 drop table t1;
 
 #
+# Bug #26794: Adding an index with a prefix on a SPATIAL type breaks ALTER
+# TABLE
+#
+CREATE TABLE t1 (a varchar(500));
+
+ALTER TABLE t1 ADD b GEOMETRY NOT NULL, ADD SPATIAL INDEX(b);
+SHOW CREATE TABLE t1;
+ALTER TABLE t1 ADD KEY(b(50));
+SHOW CREATE TABLE t1;
+
+ALTER TABLE t1 ADD c POINT;
+SHOW CREATE TABLE t1;
+
+--error ER_WRONG_SUB_KEY
+CREATE TABLE t2 (a INT, KEY (a(20)));
+
+ALTER TABLE t1 ADD d INT;
+--error ER_WRONG_SUB_KEY
+ALTER TABLE t1 ADD KEY (d(20));
+
+# the 5.1 part of the test
+--error ER_WRONG_SUB_KEY
+ALTER TABLE t1 ADD e GEOMETRY NOT NULL, ADD SPATIAL KEY (e(30));
+
+DROP TABLE t1;
+
+#
 # Bug#18038  MySQL server corrupts binary columns data
 #
 
diff --git a/mysql-test/t/endspace.test b/mysql-test/t/endspace.test
index c4d53450910..b223c683cde 100644
--- a/mysql-test/t/endspace.test
+++ b/mysql-test/t/endspace.test
@@ -16,7 +16,8 @@ drop table if exists t1;
 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 ignore key (key1) where text1='teststring' or 
+  text1 like 'teststring_%' ORDER BY text1;
 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;
@@ -24,7 +25,8 @@ 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 ignore key (key1) where text1='teststring' or 
+  text1 like 'teststring_%' ORDER BY text1;
 select concat('|', text1, '|') from t1 where text1='teststring' or text1 like 'teststring_%';
 select concat('|', text1, '|') from t1 where text1='teststring' or text1 > 'teststring\t';
 select text1, length(text1) from t1 order by text1;
@@ -57,7 +59,8 @@ 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_%';
+select * from t1 ignore key (key1) where text1='teststring' or 
+  text1 like 'teststring_%' ORDER BY text1;
 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;
diff --git a/mysql-test/t/gis-rtree.test b/mysql-test/t/gis-rtree.test
index 3368aea9741..348b79be6c2 100644
--- a/mysql-test/t/gis-rtree.test
+++ b/mysql-test/t/gis-rtree.test
@@ -172,7 +172,7 @@ CREATE TABLE t2 (geom GEOMETRY NOT NULL, SPATIAL KEY gk(geom));
 INSERT INTO t2 SELECT GeomFromText(st) FROM t1;
 drop table t1, t2;
 
-CREATE TABLE t1 (`geometry` geometry NOT NULL default '',SPATIAL KEY `gndx` (`geometry`(32))) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+CREATE TABLE t1 (`geometry` geometry NOT NULL default '',SPATIAL KEY `gndx` (`geometry`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 
 INSERT INTO t1 (geometry) VALUES
 (PolygonFromText('POLYGON((-18.6086111000 -66.9327777000, -18.6055555000
@@ -192,7 +192,7 @@ drop table t1;
 #
 CREATE TABLE t1 (
   c1 geometry NOT NULL default '',
-  SPATIAL KEY i1 (c1(32))
+  SPATIAL KEY i1 (c1)
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 INSERT INTO t1 (c1) VALUES (
   PolygonFromText('POLYGON((-18.6086111000 -66.9327777000,
@@ -206,7 +206,7 @@ DROP TABLE t1;
 #
 CREATE TABLE t1 (
   c1 geometry NOT NULL default '',
-  SPATIAL KEY i1 (c1(32))
+  SPATIAL KEY i1 (c1)
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 INSERT INTO t1 (c1) VALUES (
   PolygonFromText('POLYGON((-18.6086111000 -66.9327777000,
diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test
index 76e4af8f610..a3318745764 100644
--- a/mysql-test/t/group_by.test
+++ b/mysql-test/t/group_by.test
@@ -757,10 +757,66 @@ SET SQL_MODE = '';
 # Bug #21174: Index degrades sort performance and 
 #             optimizer does not honor IGNORE INDEX
 #
-CREATE TABLE t1 (a INT, b INT, KEY(a));
-INSERT INTO t1 VALUES (1, 1), (2, 2), (3,3), (4,4);
+CREATE TABLE t1 (a INT, b INT,
+                 PRIMARY KEY (a),
+                 KEY i2(a,b));
+INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8);
+INSERT INTO t1 SELECT a + 8,b FROM t1;
+INSERT INTO t1 SELECT a + 16,b FROM t1;
+INSERT INTO t1 SELECT a + 32,b FROM t1;
+INSERT INTO t1 SELECT a + 64,b FROM t1;
+INSERT INTO t1 SELECT a + 128,b FROM t1;
+ANALYZE TABLE t1;
+EXPLAIN SELECT a FROM t1 WHERE a < 2;
+EXPLAIN SELECT a FROM t1 WHERE a < 2 ORDER BY a;
+EXPLAIN SELECT a FROM t1 WHERE a < 2 GROUP BY a;
+EXPLAIN SELECT a FROM t1 IGNORE INDEX (PRIMARY,i2);
+EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR JOIN (PRIMARY,i2);
+EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (PRIMARY,i2) GROUP BY a;
+EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY,i2) ORDER BY a;
+EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY)
+  IGNORE INDEX FOR GROUP BY (i2) GROUP BY a;
+EXPLAIN SELECT a FROM t1 IGNORE INDEX (PRIMARY) IGNORE INDEX FOR ORDER BY (i2);
+EXPLAIN SELECT a FROM t1 FORCE INDEX (i2);
+EXPLAIN SELECT a FROM t1 USE INDEX ();
+EXPLAIN SELECT a FROM t1 USE INDEX () USE INDEX (i2);
+--error ER_WRONG_USAGE
+EXPLAIN SELECT a FROM t1 
+  FORCE INDEX (PRIMARY) 
+  IGNORE INDEX FOR GROUP BY (i2)
+  IGNORE INDEX FOR ORDER BY (i2)
+  USE INDEX (i2);
+EXPLAIN SELECT a FROM t1 USE INDEX (i2) USE INDEX ();
+--error ER_PARSE_ERROR
+EXPLAIN SELECT a FROM t1 FORCE INDEX ();
+--error ER_PARSE_ERROR
+EXPLAIN SELECT a FROM t1 IGNORE INDEX ();
+EXPLAIN SELECT a FROM t1 USE INDEX FOR JOIN (i2) 
+  USE INDEX FOR GROUP BY (i2) GROUP BY a;
+EXPLAIN SELECT a FROM t1 FORCE INDEX FOR JOIN (i2) 
+  FORCE INDEX FOR GROUP BY (i2) GROUP BY a;
+EXPLAIN SELECT a FROM t1 USE INDEX () IGNORE INDEX (i2);
+EXPLAIN SELECT a FROM t1 IGNORE INDEX (i2) USE INDEX ();
+
+EXPLAIN SELECT a FROM t1 
+  USE INDEX FOR GROUP BY (i2) 
+  USE INDEX FOR ORDER BY (i2)
+  USE INDEX FOR JOIN (i2);
+
+EXPLAIN SELECT a FROM t1 
+  USE INDEX FOR JOIN (i2) 
+  USE INDEX FOR JOIN (i2) 
+  USE INDEX FOR JOIN (i2,i2);
+
+EXPLAIN SELECT 1 FROM t1 WHERE a IN
+  (SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2));
+
+CREATE TABLE t2 (a INT, b INT, KEY(a));
+INSERT INTO t2 VALUES (1, 1), (2, 2), (3,3), (4,4);
+EXPLAIN SELECT a, SUM(b) FROM t2 GROUP BY a LIMIT 2; 
+EXPLAIN SELECT a, SUM(b) FROM t2 IGNORE INDEX (a) GROUP BY a LIMIT 2;
+
+EXPLAIN SELECT 1 FROM t2 WHERE a IN
+  (SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2));
 
-EXPLAIN SELECT a, SUM(b) FROM t1 GROUP BY a LIMIT 2; 
-EXPLAIN SELECT a, SUM(b) FROM t1 IGNORE INDEX (a) GROUP BY a LIMIT 2;
-
-DROP TABLE t1;
+DROP TABLE t1, t2;
diff --git a/mysql-test/t/key.test b/mysql-test/t/key.test
index 1a53344c8ef..99736e0f11f 100644
--- a/mysql-test/t/key.test
+++ b/mysql-test/t/key.test
@@ -453,3 +453,51 @@ ALTER TABLE t1 DISABLE KEYS;
 EXPLAIN SELECT MAX(a) FROM t1 FORCE INDEX(a);
 
 drop table t1;
+
+#
+# Bug #24778: Innodb: No result when using ORDER BY
+#
+CREATE TABLE t1 (
+  a INTEGER auto_increment PRIMARY KEY,
+  b INTEGER NOT NULL,
+  c INTEGER NOT NULL,
+  d CHAR(64)
+);
+
+CREATE TABLE t2 (
+  a INTEGER auto_increment PRIMARY KEY,
+  b INTEGER NOT NULL,
+  c SMALLINT NOT NULL,
+  d DATETIME NOT NULL,
+  e SMALLINT NOT NULL,
+  f INTEGER NOT NULL,
+  g INTEGER NOT NULL,  
+  h SMALLINT NOT NULL,
+  i INTEGER NOT NULL,
+  j INTEGER NOT NULL,
+  UNIQUE INDEX (b),
+  INDEX (b, d, e, f, g, h, i, j, c),
+  INDEX (c)
+);
+
+INSERT INTO t2 VALUES 
+  (NULL, 1, 254, '1000-01-01 00:00:00', 257, 0, 0, 0, 0, 0),
+  (NULL, 2, 1, '2004-11-30 12:00:00', 1, 0, 0, 0, 0, 0),
+  (NULL, 3, 1, '2004-11-30 12:00:00', 1, 0, 0, 2, -21600, 0),
+  (NULL, 4, 1, '2004-11-30 12:00:00', 1, 0, 0, 2, -10800, 0),
+  (NULL, 5, 1, '2004-11-30 12:00:00', 1, 0, 0, 5, -10800, 0),
+  (NULL, 6, 1, '2004-11-30 12:00:00', 102, 0, 0, 0, 0, 0),
+  (NULL, 7, 1, '2004-11-30 12:00:00', 105, 2, 0, 0, 0, 0),
+  (NULL, 8, 1, '2004-11-30 12:00:00', 105, 10, 0, 0, 0, 0);
+
+INSERT INTO t1 (b, c, d) VALUES
+  (3388000, -553000, NULL),
+  (3388000, -553000, NULL);
+
+SELECT *
+FROM t2 c JOIN t1 pa ON c.b = pa.a 
+WHERE c.c = 1
+ORDER BY c.b, c.d
+;
+
+DROP TABLE t1, t2;
diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test
index c9ae0964e17..5093263b864 100644
--- a/mysql-test/t/order_by.test
+++ b/mysql-test/t/order_by.test
@@ -679,6 +679,21 @@ select a,(sum(b)/sum(c)) as ratio from t1 group by a order by sum(b)/sum(c) asc;
 drop table t1;
 
 #
+# Bug#26672: Incorrect SEC_TO_TIME() casting in ORDER BY
+#
+CREATE TABLE t1 (a INT UNSIGNED NOT NULL, b TIME);
+INSERT INTO t1 (a) VALUES (100000), (0), (100), (1000000),(10000), (1000), (10);
+UPDATE t1 SET b = SEC_TO_TIME(a);
+
+-- Correct ORDER
+SELECT a, b FROM t1 ORDER BY b DESC;
+
+-- must be ordered as the above
+SELECT a, b FROM t1 ORDER BY SEC_TO_TIME(a) DESC;
+
+DROP TABLE t1;
+
+#
 # BUG#16590: Optimized does not do right "const" table pre-read
 #
 CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a), UNIQUE KEY b (b));
diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test
index f7f458e277c..f613b836350 100644
--- a/mysql-test/t/select.test
+++ b/mysql-test/t/select.test
@@ -3299,4 +3299,51 @@ SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
 
 DROP TABLE t1,t2;
 
+
+#
+# Bug #26963: join with predicates that contain fields from equalities evaluated
+#             to constants after constant table substitution
+#
+
+CREATE TABLE t1 (
+ access_id int NOT NULL default '0',
+ name varchar(20) default NULL,
+ rank int NOT NULL default '0',
+ KEY idx (access_id)
+);
+
+CREATE TABLE t2 (
+  faq_group_id int NOT NULL default '0',
+  faq_id int NOT NULL default '0',
+  access_id int default NULL,
+  UNIQUE KEY idx1 (faq_id),
+  KEY idx2 (faq_group_id,faq_id)
+);
+
+INSERT INTO t1 VALUES 
+  (1,'Everyone',2),(2,'Help',3),(3,'Technical Support',1),(4,'Chat User',4);
+INSERT INTO t2 VALUES
+  (261,265,1),(490,494,1);
+
+
+SELECT t2.faq_id 
+  FROM t1 INNER JOIN t2 IGNORE INDEX (idx1)
+       ON (t1.access_id = t2.access_id)
+       LEFT JOIN t2 t
+       ON (t.faq_group_id = t2.faq_group_id AND
+           find_in_set(t.access_id, '1,4') < find_in_set(t2.access_id, '1,4'))
+   WHERE
+     t2.access_id IN (1,4) AND t.access_id IS NULL AND t2.faq_id in (265);
+
+SELECT t2.faq_id 
+  FROM t1 INNER JOIN t2
+       ON (t1.access_id = t2.access_id)
+       LEFT JOIN t2 t
+       ON (t.faq_group_id = t2.faq_group_id AND
+           find_in_set(t.access_id, '1,4') < find_in_set(t2.access_id, '1,4'))
+   WHERE
+     t2.access_id IN (1,4) AND t.access_id IS NULL AND t2.faq_id in (265);
+
+DROP TABLE t1,t2;
+
 --echo End of 5.0 tests
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index 8ca99130921..9c2248cbcc2 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -2736,3 +2736,15 @@ DROP TABLE t1;
 DROP TABLE t2;
 DROP TABLE t1xt2;
 
+#
+# Bug #26728: derived table with concatanation of literals in select list
+#  
+
+CREATE TABLE t1 (a int);
+INSERT INTO t1 VALUES (3), (1), (2);           
+
+SELECT 'this is ' 'a test.' AS col1, a AS col2 FROM t1;
+SELECT * FROM (SELECT 'this is ' 'a test.' AS col1, a AS t2 FROM t1) t;
+
+DROP table t1;
+