Merge dl145s.mysql.com:/data/bk/team_tree_merge/MERGE/mysql-5.0-opt

into  dl145s.mysql.com:/data/bk/team_tree_merge/MERGE/mysql-5.1-opt


BitKeeper/deleted/.del-bdb.result:
  Auto merged
mysql-test/r/func_gconcat.result:
  Auto merged
mysql-test/r/group_min_max.result:
  Auto merged
mysql-test/r/innodb.result:
  Auto merged
mysql-test/r/merge.result:
  Auto merged
mysql-test/r/myisam.result:
  Auto merged
mysql-test/r/olap.result:
  Auto merged
mysql-test/r/select.result:
  Auto merged
mysql-test/r/type_decimal.result:
  Auto merged
mysql-test/r/view.result:
  Auto merged
mysql-test/t/func_gconcat.test:
  Auto merged
mysql-test/t/group_min_max.test:
  Auto merged
mysql-test/t/select.test:
  Auto merged
sql/item_cmpfunc.cc:
  Auto merged
sql/item_cmpfunc.h:
  Auto merged
sql/opt_range.cc:
  Auto merged
sql/opt_range.h:
  Auto merged
sql/sql_base.cc:
  Auto merged
sql/sql_delete.cc:
  Auto merged
sql/sql_lex.cc:
  Auto merged
sql/sql_lex.h:
  Auto merged
sql/sql_select.h:
  Auto merged
sql/sql_update.cc:
  Auto merged
sql/table.cc:
  Auto merged
include/my_base.h:
  SCCS merged
mysql-test/include/mix1.inc:
  SCCS merged
mysql-test/r/group_by.result:
  SCCS merged
mysql-test/r/innodb_mysql.result:
  SCCS merged
mysql-test/t/group_by.test:
  SCCS merged
sql/sql_select.cc:
  SCCS merged

39 files changed, 1019 insertions, 164 deletions

diff --git a/include/my_base.h b/include/my_base.h
index 774ff4c8cd5..cf254f5e0d0 100644
--- a/include/my_base.h
+++ b/include/my_base.h
@@ -229,12 +229,17 @@ enum ha_base_keytype {
 #define HA_USES_PARSER           16384  /* Fulltext index uses [pre]parser */
 #define HA_USES_BLOCK_SIZE	 ((uint) 32768)
 #define HA_SORT_ALLOWS_SAME      512    /* Intern bit when sorting records */
+#if MYSQL_VERSION_ID < 0x50200
 /*
   Key has a part that can have end space.  If this is an unique key
   we have to handle it differently from other unique keys as we can find
   many matching rows for one key (because end space are not compared)
 */
-#define HA_END_SPACE_KEY	4096
+#define HA_END_SPACE_KEY      0 /* was: 4096 */
+#else
+#error HA_END_SPACE_KEY is obsolete, please remove it
+#endif
+
 
 	/* These flags can be added to key-seg-flag */
 
diff --git a/mysql-test/include/mix1.inc b/mysql-test/include/mix1.inc
index fb4d095ed6d..57070d40c5c 100644
--- a/mysql-test/include/mix1.inc
+++ b/mysql-test/include/mix1.inc
@@ -322,6 +322,24 @@ SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id
 DROP TABLE t1,t2;
 
 #
+# Bug#22781: SQL_BIG_RESULT fails to influence sort plan
+#
+CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c FLOAT, KEY b(b)) ENGINE = INNODB;
+
+INSERT INTO t1 VALUES (    1 , 1              , 1);
+INSERT INTO t1 SELECT  a + 1 , MOD(a + 1 , 20), 1 FROM t1;
+INSERT INTO t1 SELECT  a + 2 , MOD(a + 2 , 20), 1 FROM t1;
+INSERT INTO t1 SELECT  a + 4 , MOD(a + 4 , 20), 1 FROM t1;
+INSERT INTO t1 SELECT  a + 8 , MOD(a + 8 , 20), 1 FROM t1;
+INSERT INTO t1 SELECT  a + 16, MOD(a + 16, 20), 1 FROM t1;
+INSERT INTO t1 SELECT  a + 32, MOD(a + 32, 20), 1 FROM t1;
+INSERT INTO t1 SELECT  a + 64, MOD(a + 64, 20), 1 FROM t1;
+
+EXPLAIN SELECT b, SUM(c) FROM t1 GROUP BY b;
+EXPLAIN SELECT SQL_BIG_RESULT b, SUM(c) FROM t1 GROUP BY b;
+DROP TABLE t1;
+
+#
 # Test of behaviour with CREATE ... SELECT
 #
 
diff --git a/mysql-test/r/func_gconcat.result b/mysql-test/r/func_gconcat.result
index 43646bd3d0e..619c8dafeae 100644
--- a/mysql-test/r/func_gconcat.result
+++ b/mysql-test/r/func_gconcat.result
@@ -74,11 +74,6 @@ grp	group_concat(c order by 1)
 1	a
 2	b,c
 3	C,D,d,d,D,E
-select grp,group_concat(c order by "c") from t1 group by grp;
-grp	group_concat(c order by "c")
-1	a
-2	b,c
-3	C,D,d,d,D,E
 select grp,group_concat(distinct c order by c) from t1 group by grp;
 grp	group_concat(distinct c order by c)
 1	a
diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result
index 4d31b2ba496..d85d3eabe5e 100644
--- a/mysql-test/r/func_str.result
+++ b/mysql-test/r/func_str.result
@@ -1143,9 +1143,9 @@ EXPLAIN EXTENDED
 SELECT * FROM t1 INNER JOIN t2 ON code=id 
 WHERE id='a12' AND (LENGTH(code)=5 OR code < 'a00');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	const	PRIMARY	PRIMARY	12	const	1	100.00	Using index
 1	SIMPLE	t1	ref	code	code	13	const	3	100.00	Using where; Using index
-1	SIMPLE	t2	ref	PRIMARY	PRIMARY	12	const	1	100.00	Using where; Using index
 Warnings:
-Note	1003	select `test`.`t1`.`code` AS `code`,`test`.`t2`.`id` AS `id` from `test`.`t1` join `test`.`t2` where ((`test`.`t1`.`code` = _latin1'a12') and (`test`.`t2`.`id` = _latin1'a12') and (length(`test`.`t1`.`code`) = 5))
+Note	1003	select `test`.`t1`.`code` AS `code`,`test`.`t2`.`id` AS `id` from `test`.`t1` join `test`.`t2` where ((`test`.`t1`.`code` = _latin1'a12') and (length(`test`.`t1`.`code`) = 5))
 DROP TABLE t1,t2;
 End of 5.0 tests
diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result
index 80988bd8047..663ef6cced4 100644
--- a/mysql-test/r/group_by.result
+++ b/mysql-test/r/group_by.result
@@ -303,10 +303,10 @@ spid	sum(userid)
 1	1
 explain select sql_big_result score,count(*) from t1 group by score desc;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index	NULL	score	3	NULL	8	Using index
+1	SIMPLE	t1	index	NULL	score	3	NULL	8	Using index; Using filesort
 explain select sql_big_result score,count(*) from t1 group by score desc order by null;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index	NULL	score	3	NULL	8	Using index
+1	SIMPLE	t1	index	NULL	score	3	NULL	8	Using index; Using filesort
 select sql_big_result score,count(*) from t1 group by score desc;
 score	count(*)
 3	5
@@ -775,6 +775,55 @@ select sql_buffer_result max(f1)+1 from t1;
 max(f1)+1
 3
 drop table t1;
+CREATE TABLE t1(a INT);
+INSERT INTO t1 VALUES (1),(2);
+SELECT a FROM t1 GROUP BY 'a';
+a
+1
+SELECT a FROM t1 GROUP BY "a";
+a
+1
+SELECT a FROM t1 GROUP BY `a`;
+a
+1
+2
+set sql_mode=ANSI_QUOTES;
+SELECT a FROM t1 GROUP BY "a";
+a
+1
+2
+SELECT a FROM t1 GROUP BY 'a';
+a
+1
+SELECT a FROM t1 GROUP BY `a`;
+a
+1
+2
+set sql_mode='';
+SELECT a FROM t1 HAVING 'a' > 1;
+a
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: 'a'
+SELECT a FROM t1 HAVING "a" > 1;
+a
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: 'a'
+SELECT a FROM t1 HAVING `a` > 1;
+a
+2
+SELECT a FROM t1 ORDER BY 'a' DESC;
+a
+1
+2
+SELECT a FROM t1 ORDER BY "a" DESC;
+a
+1
+2
+SELECT a FROM t1 ORDER BY `a` DESC;
+a
+2
+1
+DROP TABLE t1;
 create table t1 (c1 char(3), c2 char(3));
 create table t2 (c3 char(3), c4 char(3));
 insert into t1 values ('aaa', 'bb1'), ('aaa', 'bb2');
@@ -821,6 +870,69 @@ a	b	real_b
 68	France	France
 DROP VIEW v1;
 DROP TABLE t1,t2;
+CREATE TABLE t1 (a INT PRIMARY KEY, b INT, key (b));
+INSERT INTO t1 VALUES (1,      1);
+INSERT INTO t1 SELECT  a + 1 , MOD(a + 1 , 20) FROM t1;
+INSERT INTO t1 SELECT  a + 2 , MOD(a + 2 , 20) FROM t1;
+INSERT INTO t1 SELECT  a + 4 , MOD(a + 4 , 20) FROM t1;
+INSERT INTO t1 SELECT  a + 8 , MOD(a + 8 , 20) FROM t1;
+INSERT INTO t1 SELECT  a + 16, MOD(a + 16, 20) FROM t1;
+INSERT INTO t1 SELECT  a + 32, MOD(a + 32, 20) FROM t1;
+INSERT INTO t1 SELECT  a + 64, MOD(a + 64, 20) FROM t1;
+SELECT MIN(b), MAX(b) from t1;
+MIN(b)	MAX(b)
+0	19
+EXPLAIN SELECT b, sum(1) FROM t1 GROUP BY b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	b	5	NULL	128	Using index
+EXPLAIN SELECT SQL_BIG_RESULT b, sum(1) FROM t1 GROUP BY b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	b	5	NULL	128	Using index; Using filesort
+SELECT b, sum(1) FROM t1 GROUP BY b;
+b	sum(1)
+0	6
+1	7
+2	7
+3	7
+4	7
+5	7
+6	7
+7	7
+8	7
+9	6
+10	6
+11	6
+12	6
+13	6
+14	6
+15	6
+16	6
+17	6
+18	6
+19	6
+SELECT SQL_BIG_RESULT b, sum(1) FROM t1 GROUP BY b;
+b	sum(1)
+0	6
+1	7
+2	7
+3	7
+4	7
+5	7
+6	7
+7	7
+8	7
+9	6
+10	6
+11	6
+12	6
+13	6
+14	6
+15	6
+16	6
+17	6
+18	6
+19	6
+DROP TABLE t1;
 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;
diff --git a/mysql-test/r/group_min_max.result b/mysql-test/r/group_min_max.result
index 2d72540c8a9..5485db73fbb 100644
--- a/mysql-test/r/group_min_max.result
+++ b/mysql-test/r/group_min_max.result
@@ -2162,3 +2162,23 @@ t1;
 id2	id3	id5	id4	id3	id6	id5	id1
 1	1	1	1	1	1	1	1
 DROP TABLE t1,t2,t3,t4,t5,t6;
+CREATE TABLE t1 (a int, b int, PRIMARY KEY (a,b), KEY b (b));
+INSERT INTO t1 VALUES (1,1),(1,2),(1,0),(1,3);
+explain SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	PRIMARY,b	PRIMARY	8	NULL	1	Using where; Using index for group-by
+SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a;
+MAX(b)	a
+1	1
+SELECT MIN(b), a FROM t1 WHERE b > 1 AND a = 1 GROUP BY a;
+MIN(b)	a
+2	1
+CREATE TABLE t2 (a int, b int, c int, PRIMARY KEY (a,b,c));
+INSERT INTO t2 SELECT a,b,b FROM t1;
+explain SELECT MIN(c) FROM t2 WHERE b = 2 and a = 1 and c > 1 GROUP BY a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	range	PRIMARY	PRIMARY	12	NULL	1	Using where; Using index for group-by
+SELECT MIN(c) FROM t2 WHERE b = 2 and a = 1 and c > 1 GROUP BY a;
+MIN(c)
+2
+DROP TABLE t1,t2;
diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result
index e26e6fb271a..e7fa950a131 100644
--- a/mysql-test/r/innodb.result
+++ b/mysql-test/r/innodb.result
@@ -2063,15 +2063,15 @@ i	10
 select sql_big_result v,count(c) from t1 group by v limit 10;
 v	count(c)
 a
	1
-a	10
-b	10
-c	10
-d	10
-e	10
-f	10
-g	10
+a 	10
+b     	10
+c    	10
+d   	10
+e  	10
+f     	10
+g    	10
 h	10
-i	10
+i     	10
 select c,count(*) from t1 group by c limit 10;
 c	count(*)
 a
	1
diff --git a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result
index 690ebeb5607..4acfaf58a7d 100644
--- a/mysql-test/r/innodb_mysql.result
+++ b/mysql-test/r/innodb_mysql.result
@@ -290,6 +290,22 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t2	index	NULL	fkey	5	NULL	5	Using index
 1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t2.fkey	1	Using where
 DROP TABLE t1,t2;
+CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c FLOAT, KEY b(b)) ENGINE = INNODB;
+INSERT INTO t1 VALUES (    1 , 1              , 1);
+INSERT INTO t1 SELECT  a + 1 , MOD(a + 1 , 20), 1 FROM t1;
+INSERT INTO t1 SELECT  a + 2 , MOD(a + 2 , 20), 1 FROM t1;
+INSERT INTO t1 SELECT  a + 4 , MOD(a + 4 , 20), 1 FROM t1;
+INSERT INTO t1 SELECT  a + 8 , MOD(a + 8 , 20), 1 FROM t1;
+INSERT INTO t1 SELECT  a + 16, MOD(a + 16, 20), 1 FROM t1;
+INSERT INTO t1 SELECT  a + 32, MOD(a + 32, 20), 1 FROM t1;
+INSERT INTO t1 SELECT  a + 64, MOD(a + 64, 20), 1 FROM t1;
+EXPLAIN SELECT b, SUM(c) FROM t1 GROUP BY b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	b	5	NULL	128	
+EXPLAIN SELECT SQL_BIG_RESULT b, SUM(c) FROM t1 GROUP BY b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	128	Using filesort
+DROP TABLE t1;
 CREATE TABLE t1 (a int, b int);
 insert into t1 values (1,1),(1,2);
 CREATE TABLE t2 (primary key (a)) select * from t1;
diff --git a/mysql-test/r/merge.result b/mysql-test/r/merge.result
index 1e0207683ee..277891788d1 100644
--- a/mysql-test/r/merge.result
+++ b/mysql-test/r/merge.result
@@ -629,7 +629,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 EXPLAIN SELECT * FROM t2 WHERE fileset_id = 2
 AND file_code = '0000000115' LIMIT 1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	ref	PRIMARY,files	PRIMARY	35	const,const	1	Using where
+1	SIMPLE	t2	const	PRIMARY,files	PRIMARY	35	const,const	1	
 DROP TABLE t2, t1;
 create table t1 (x int, y int, index xy(x, y));
 create table t2 (x int, y int, index xy(x, y));
diff --git a/mysql-test/r/myisam.result b/mysql-test/r/myisam.result
index 43deb4ce846..5cf094cfa2c 100644
--- a/mysql-test/r/myisam.result
+++ b/mysql-test/r/myisam.result
@@ -1002,15 +1002,15 @@ i	10
 select sql_big_result v,count(c) from t1 group by v limit 10;
 v	count(c)
 a
	1
-a	10
-b	10
-c	10
-d	10
-e	10
-f	10
-g	10
+a 	10
+b     	10
+c    	10
+d   	10
+e  	10
+f     	10
+g    	10
 h	10
-i	10
+i     	10
 select c,count(*) from t1 group by c limit 10;
 c	count(*)
 a
	1
diff --git a/mysql-test/r/olap.result b/mysql-test/r/olap.result
index f9e211c8186..fd657c93bd7 100644
--- a/mysql-test/r/olap.result
+++ b/mysql-test/r/olap.result
@@ -592,6 +592,21 @@ a	max(b)
 NULL	2
 a	1
 drop table t1;
+create table t1 (a varchar(22) not null , b int);
+insert into t1 values ("2006-07-01 21:30", 1), ("2006-07-01 23:30", 10);
+select left(a,10), a, sum(b) from t1 group by 1,2 with rollup;
+left(a,10)	a	sum(b)
+2006-07-01	2006-07-01 21:30	1
+2006-07-01	2006-07-01 23:30	10
+2006-07-01	NULL	11
+NULL	NULL	11
+select left(a,10) x, a, sum(b) from t1 group by x,a with rollup;
+x	a	sum(b)
+2006-07-01	2006-07-01 21:30	1
+2006-07-01	2006-07-01 23:30	10
+2006-07-01	NULL	11
+NULL	NULL	11
+drop table t1;
 CREATE TABLE t1(id int, type char(1));
 INSERT INTO t1 VALUES
 (1,"A"),(2,"C"),(3,"A"),(4,"A"),(5,"B"),
diff --git a/mysql-test/r/rename.result b/mysql-test/r/rename.result
index 76c0f4422fe..345270231ef 100644
--- a/mysql-test/r/rename.result
+++ b/mysql-test/r/rename.result
@@ -54,3 +54,13 @@ Tables_in_test
 t2
 t4
 drop table t2, t4;
+create table t1(f1 int);
+create view v1 as select * from t1;
+alter table v1 rename to v2;
+alter table v1 rename to v2;
+ERROR 42S02: Table 'test.v1' doesn't exist
+rename table v2 to v1;
+rename table v2 to v1;
+ERROR 42S01: Table 'v1' already exists
+drop view v1;
+drop table t1;
diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result
index 8800ce248d5..f24b109d75a 100644
--- a/mysql-test/r/select.result
+++ b/mysql-test/r/select.result
@@ -3517,3 +3517,97 @@ id	a	b	c	d	e
 2	NULL	NULL	NULL	2	40
 2	NULL	NULL	NULL	2	50
 DROP TABLE t1,t2,t3;
+create table t1 (c1 varchar(1), c2 int, c3 int, c4 int, c5 int, c6 int,
+c7 int, c8 int, c9 int, fulltext key (`c1`));
+select distinct match (`c1`) against ('z') , c2, c3, c4,c5, c6,c7, c8 
+from t1 where c9=1 order by c2, c2;
+match (`c1`) against ('z')	c2	c3	c4	c5	c6	c7	c8
+drop table t1;
+CREATE TABLE t1 (pk varchar(10) PRIMARY KEY, fk varchar(16));
+CREATE TABLE t2 (pk varchar(16) PRIMARY KEY, fk varchar(10));
+INSERT INTO t1 VALUES
+('d','dddd'), ('i','iii'), ('a','aa'), ('b','bb'), ('g','gg'), 
+('e','eee'), ('c','cccc'), ('h','hhh'), ('j','jjj'), ('f','fff');
+INSERT INTO t2 VALUES
+('jjj', 'j'), ('cc','c'), ('ccc','c'), ('aaa', 'a'), ('jjjj','j'),
+('hhh','h'), ('gg','g'), ('fff','f'), ('ee','e'), ('ffff','f'),
+('bbb','b'), ('ff','f'), ('cccc','c'), ('dddd','d'), ('jj','j'),
+('aaaa','a'), ('bb','b'), ('eeee','e'), ('aa','a'), ('hh','h');
+EXPLAIN SELECT t2.* 
+FROM t1 JOIN t2 ON t2.fk=t1.pk
+WHERE t2.fk < 'c' AND t2.pk=t1.fk;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	PRIMARY	PRIMARY	12	NULL	3	Using where
+1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	18	test.t1.fk	1	Using where
+EXPLAIN SELECT t2.* 
+FROM t1 JOIN t2 ON t2.fk=t1.pk 
+WHERE t2.fk BETWEEN 'a' AND 'b' AND t2.pk=t1.fk;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	PRIMARY	PRIMARY	12	NULL	2	Using where
+1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	18	test.t1.fk	1	Using where
+EXPLAIN SELECT t2.* 
+FROM t1 JOIN t2 ON t2.fk=t1.pk 
+WHERE t2.fk IN ('a','b') AND t2.pk=t1.fk;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	PRIMARY	PRIMARY	12	NULL	2	Using where
+1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	18	test.t1.fk	1	Using where
+DROP TABLE t1,t2;
+CREATE TABLE t1 (a int, b varchar(20) NOT NULL, PRIMARY KEY(a));
+CREATE TABLE t2 (a int, b varchar(20) NOT NULL,
+PRIMARY KEY (a), UNIQUE KEY (b));
+INSERT INTO t1 VALUES (1,'a'),(2,'b'),(3,'c');
+INSERT INTO t2 VALUES (1,'a'),(2,'b'),(3,'c');
+EXPLAIN SELECT t1.a FROM t1 LEFT JOIN t2 ON t2.b=t1.b WHERE t1.a=3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	
+1	SIMPLE	t2	const	b	b	22	const	1	Using index
+DROP TABLE t1,t2;
+CREATE TABLE t1(id int PRIMARY KEY, b int, e int);
+CREATE TABLE t2(i int, a int, INDEX si(i), INDEX ai(a));
+CREATE TABLE t3(a int PRIMARY KEY, c char(4), INDEX ci(c));
+INSERT INTO t1 VALUES 
+(1,10,19), (2,20,22), (4,41,42), (9,93,95), (7, 77,79),
+(6,63,67), (5,55,58), (3,38,39), (8,81,89);
+INSERT INTO t2 VALUES
+(21,210), (41,410), (82,820), (83,830), (84,840),
+(65,650), (51,510), (37,370), (94,940), (76,760),
+(22,220), (33,330), (40,400), (95,950), (38,380),
+(67,670), (88,880), (57,570), (96,960), (97,970);
+INSERT INTO t3 VALUES
+(210,'bb'), (950,'ii'), (400,'ab'), (500,'ee'), (220,'gg'),
+(440,'gg'), (310,'eg'), (380,'ee'), (840,'bb'), (830,'ff'),
+(230,'aa'), (960,'ii'), (410,'aa'), (510,'ee'), (290,'bb'),
+(450,'gg'), (320,'dd'), (390,'hh'), (850,'jj'), (860,'ff');
+EXPLAIN
+SELECT t3.a FROM t1,t2 FORCE INDEX (si),t3
+WHERE t1.id = 8 AND t2.i BETWEEN t1.b AND t1.e AND 
+t3.a=t2.a AND t3.c IN ('bb','ee');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	
+1	SIMPLE	t2	range	si	si	5	NULL	4	Using where
+1	SIMPLE	t3	eq_ref	PRIMARY,ci	PRIMARY	4	test.t2.a	1	Using where
+EXPLAIN
+SELECT t3.a FROM t1,t2,t3
+WHERE t1.id = 8 AND t2.i BETWEEN t1.b AND t1.e AND
+t3.a=t2.a AND t3.c IN ('bb','ee') ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	
+1	SIMPLE	t2	range	si,ai	si	5	NULL	4	Using where
+1	SIMPLE	t3	eq_ref	PRIMARY,ci	PRIMARY	4	test.t2.a	1	Using where
+EXPLAIN 
+SELECT t3.a FROM t1,t2 FORCE INDEX (si),t3
+WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND t3.a=t2.a AND
+t3.c IN ('bb','ee');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	
+1	SIMPLE	t2	range	si	si	5	NULL	2	Using where
+1	SIMPLE	t3	eq_ref	PRIMARY,ci	PRIMARY	4	test.t2.a	1	Using where
+EXPLAIN 
+SELECT t3.a FROM t1,t2,t3
+WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND t3.a=t2.a AND
+t3.c IN ('bb','ee');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	
+1	SIMPLE	t2	range	si,ai	si	5	NULL	2	Using where
+1	SIMPLE	t3	eq_ref	PRIMARY,ci	PRIMARY	4	test.t2.a	1	Using where
+DROP TABLE t1,t2,t3;
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index a1463bc2b3c..e57cc44ddd4 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -363,12 +363,12 @@ 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	filtered	Extra
-1	PRIMARY	t8	ref	PRIMARY	PRIMARY	37	const	1	100.00	Using where; Using index
-4	SUBQUERY	t8	ref	PRIMARY	PRIMARY	37		1	100.00	Using where; Using index
-2	SUBQUERY	t8	ref	PRIMARY	PRIMARY	37	const	1	100.00	Using where
-3	SUBQUERY	t8	ref	PRIMARY	PRIMARY	37		1	100.00	Using where; Using index
+1	PRIMARY	t8	const	PRIMARY	PRIMARY	37	const	1	100.00	Using index
+4	SUBQUERY	t8	const	PRIMARY	PRIMARY	37		1	100.00	Using index
+2	SUBQUERY	t8	const	PRIMARY	PRIMARY	37	const	1	100.00	
+3	SUBQUERY	t8	const	PRIMARY	PRIMARY	37		1	100.00	Using index
 Warnings:
-Note	1003	select `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')))
+Note	1003	select `test`.`t8`.`pseudo` AS `pseudo`,(select `test`.`t8`.`email` AS `email` from `test`.`t8` where 1) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where 1
 SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo,email FROM
 t8 WHERE pseudo='joce');
 ERROR 21000: Operand should contain 1 column(s)
@@ -3458,6 +3458,32 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 4	UNION	t12	system	NULL	NULL	NULL	NULL	0	const row not found
 NULL	UNION RESULT	<union2,4>	ALL	NULL	NULL	NULL	NULL	NULL	
 DROP TABLE t1;
+CREATE TABLE t1 (a VARCHAR(250), b INT auto_increment, PRIMARY KEY (b));
+insert into t1 (a) values (FLOOR(rand() * 100));
+insert into t1 (a) select FLOOR(rand() * 100) from t1;
+insert into t1 (a) select FLOOR(rand() * 100) from t1;
+insert into t1 (a) select FLOOR(rand() * 100) from t1;
+insert into t1 (a) select FLOOR(rand() * 100) from t1;
+insert into t1 (a) select FLOOR(rand() * 100) from t1;
+insert into t1 (a) select FLOOR(rand() * 100) from t1;
+insert into t1 (a) select FLOOR(rand() * 100) from t1;
+insert into t1 (a) select FLOOR(rand() * 100) from t1;
+insert into t1 (a) select FLOOR(rand() * 100) from t1;
+insert into t1 (a) select FLOOR(rand() * 100) from t1;
+insert into t1 (a) select FLOOR(rand() * 100) from t1;
+insert into t1 (a) select FLOOR(rand() * 100) from t1;
+insert into t1 (a) select FLOOR(rand() * 100) from t1;
+SELECT a, 
+(SELECT REPEAT(' ',250) FROM t1 i1 
+WHERE i1.b=t1.a ORDER BY RAND() LIMIT 1) AS a 
+FROM t1 ORDER BY a LIMIT 5;
+a	a
+0	NULL
+0	NULL
+0	NULL
+0	NULL
+0	NULL
+DROP TABLE t1;
 CREATE TABLE t1 (a INT, b INT);
 CREATE TABLE t2 (a INT);
 INSERT INTO t2 values (1);
diff --git a/mysql-test/r/type_decimal.result b/mysql-test/r/type_decimal.result
index 9c6a9b07544..0840872a7a9 100644
--- a/mysql-test/r/type_decimal.result
+++ b/mysql-test/r/type_decimal.result
@@ -779,3 +779,14 @@ select f1 from t1 where f1 in (select f1 from t1);
 f1
 40
 drop table t1;
+create table t1 as
+select from_days(s) as date,t
+from (select 1 as s,'t' as t union select null, null ) as sub1;
+select group_concat(t) from t1 group by week(date)/10;
+group_concat(t)
+t
+Warnings:
+Warning	1292	Truncated incorrect datetime value: '0000-00-00'
+Warning	1292	Truncated incorrect datetime value: '0000-00-00'
+Warning	1292	Truncated incorrect datetime value: '0000-00-00'
+drop table t1;
diff --git a/mysql-test/t/func_gconcat.test b/mysql-test/t/func_gconcat.test
index 19753430dde..c2035132d06 100644
--- a/mysql-test/t/func_gconcat.test
+++ b/mysql-test/t/func_gconcat.test
@@ -32,7 +32,6 @@ select grp,group_concat(d order by a desc) from t1 group by grp;
 select grp,group_concat(a order by a,d+c-ascii(c)-a) from t1 group by grp;
 select grp,group_concat(a order by d+c-ascii(c),a) from t1 group by grp;
 select grp,group_concat(c order by 1) from t1 group by grp;
-select grp,group_concat(c order by "c") from t1 group by grp;
 select grp,group_concat(distinct c order by c) from t1 group by grp;
 select grp,group_concat(distinct c order by c desc) from t1 group by grp;
 explain extended select grp,group_concat(distinct c order by c desc) from t1 group by grp;
diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test
index 8a514108dc3..7f887335753 100644
--- a/mysql-test/t/group_by.test
+++ b/mysql-test/t/group_by.test
@@ -609,6 +609,30 @@ select sql_buffer_result max(f1) is null from t1;
 select sql_buffer_result max(f1)+1 from t1;
 drop table t1;
 
+#
+# BUG#14019-4.1-opt
+#
+CREATE TABLE t1(a INT); INSERT INTO t1 VALUES (1),(2);
+
+SELECT a FROM t1 GROUP BY 'a';
+SELECT a FROM t1 GROUP BY "a";
+SELECT a FROM t1 GROUP BY `a`;
+
+set sql_mode=ANSI_QUOTES;
+SELECT a FROM t1 GROUP BY "a";
+SELECT a FROM t1 GROUP BY 'a';
+SELECT a FROM t1 GROUP BY `a`;
+set sql_mode='';
+
+SELECT a FROM t1 HAVING 'a' > 1;
+SELECT a FROM t1 HAVING "a" > 1;
+SELECT a FROM t1 HAVING `a` > 1;
+
+SELECT a FROM t1 ORDER BY 'a' DESC;
+SELECT a FROM t1 ORDER BY "a" DESC;
+SELECT a FROM t1 ORDER BY `a` DESC;
+DROP TABLE t1;
+
 # End of 4.1 tests
 
 #
@@ -657,6 +681,28 @@ DROP VIEW v1;
 DROP TABLE t1,t2;
 
 #
+# Bug#22781: SQL_BIG_RESULT fails to influence sort plan
+#
+CREATE TABLE t1 (a INT PRIMARY KEY, b INT, key (b));
+
+INSERT INTO t1 VALUES (1,      1);
+INSERT INTO t1 SELECT  a + 1 , MOD(a + 1 , 20) FROM t1;
+INSERT INTO t1 SELECT  a + 2 , MOD(a + 2 , 20) FROM t1;
+INSERT INTO t1 SELECT  a + 4 , MOD(a + 4 , 20) FROM t1;
+INSERT INTO t1 SELECT  a + 8 , MOD(a + 8 , 20) FROM t1;
+INSERT INTO t1 SELECT  a + 16, MOD(a + 16, 20) FROM t1;
+INSERT INTO t1 SELECT  a + 32, MOD(a + 32, 20) FROM t1;
+INSERT INTO t1 SELECT  a + 64, MOD(a + 64, 20) FROM t1;
+
+SELECT MIN(b), MAX(b) from t1;
+
+EXPLAIN SELECT b, sum(1) FROM t1 GROUP BY b;
+EXPLAIN SELECT SQL_BIG_RESULT b, sum(1) FROM t1 GROUP BY b;
+SELECT b, sum(1) FROM t1 GROUP BY b;
+SELECT SQL_BIG_RESULT b, sum(1) FROM t1 GROUP BY b;
+DROP TABLE t1;
+
+#
 # Bug #21174: Index degrades sort performance and 
 #             optimizer does not honor IGNORE INDEX
 #
diff --git a/mysql-test/t/group_min_max.test b/mysql-test/t/group_min_max.test
index 0354fc465af..11ba6885dd1 100644
--- a/mysql-test/t/group_min_max.test
+++ b/mysql-test/t/group_min_max.test
@@ -819,3 +819,19 @@ SELECT * FROM
 t1;
 
 DROP TABLE t1,t2,t3,t4,t5,t6;
+
+#
+# Bug#22342: No results returned for query using max and group by
+#
+CREATE TABLE t1 (a int, b int, PRIMARY KEY (a,b), KEY b (b));
+INSERT INTO t1 VALUES (1,1),(1,2),(1,0),(1,3);
+
+explain SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a;
+SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a;
+SELECT MIN(b), a FROM t1 WHERE b > 1 AND a = 1 GROUP BY a;
+CREATE TABLE t2 (a int, b int, c int, PRIMARY KEY (a,b,c));
+INSERT INTO t2 SELECT a,b,b FROM t1;
+explain SELECT MIN(c) FROM t2 WHERE b = 2 and a = 1 and c > 1 GROUP BY a;
+SELECT MIN(c) FROM t2 WHERE b = 2 and a = 1 and c > 1 GROUP BY a;
+
+DROP TABLE t1,t2;
diff --git a/mysql-test/t/olap.test b/mysql-test/t/olap.test
index e34ac6a865c..4e5e7b72fc8 100644
--- a/mysql-test/t/olap.test
+++ b/mysql-test/t/olap.test
@@ -283,6 +283,15 @@ select a, max(b) from t1 group by a with rollup;
 select distinct a, max(b) from t1 group by a with rollup;
 drop table t1;
 
+#
+# Bug #20825: rollup puts non-equal values together
+#
+create table t1 (a varchar(22) not null , b int);
+insert into t1 values ("2006-07-01 21:30", 1), ("2006-07-01 23:30", 10);
+select left(a,10), a, sum(b) from t1 group by 1,2 with rollup;
+select left(a,10) x, a, sum(b) from t1 group by x,a with rollup;
+drop table t1;
+
 # End of 4.1 tests
 
 #
@@ -318,4 +327,3 @@ SELECT * FROM v1;
 
 DROP VIEW v1;
 DROP TABLE t1;
-# End of 4.1 tests
diff --git a/mysql-test/t/rename.test b/mysql-test/t/rename.test
index 86e4b6eed0a..054b1bd3403 100644
--- a/mysql-test/t/rename.test
+++ b/mysql-test/t/rename.test
@@ -72,4 +72,17 @@ disconnect con2;
 disconnect con1;
 connection default;
 
+#
+# Bug#14959: ALTER TABLE isn't able to rename a view
+#
+create table t1(f1 int);
+create view v1 as select * from t1;
+alter table v1 rename to v2;
+--error 1146
+alter table v1 rename to v2;
+rename table v2 to v1;
+--error 1050
+rename table v2 to v1;
+drop view v1;
+drop table t1;
 # End of 4.1 tests
diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test
index a4a343dd167..bc0dfd6de76 100644
--- a/mysql-test/t/select.test
+++ b/mysql-test/t/select.test
@@ -2997,5 +2997,99 @@ SELECT * FROM t1 LEFT JOIN t2 ON t2.b=t1.a INNER JOIN t3 ON t3.d=t1.id
 SELECT * FROM t1 LEFT JOIN t2 ON t2.b=t1.a INNER JOIN t3 ON t3.d=t1.id
   WHERE t1.id=2;
 
+DROP TABLE t1,t2,t3;
+
+#
+# Bug#20503: Server crash due to the ORDER clause isn't taken into account
+#            while space allocation
+#
+create table t1 (c1 varchar(1), c2 int, c3 int, c4 int, c5 int, c6 int,
+c7 int, c8 int, c9 int, fulltext key (`c1`));
+select distinct match (`c1`) against ('z') , c2, c3, c4,c5, c6,c7, c8 
+  from t1 where c9=1 order by c2, c2;
+drop table t1;
+
+#
+# Bug #22735: no equality propagation for BETWEEN and IN with STRING arguments
+#
+
+CREATE TABLE t1 (pk varchar(10) PRIMARY KEY, fk varchar(16));
+CREATE TABLE t2 (pk varchar(16) PRIMARY KEY, fk varchar(10));
+
+INSERT INTO t1 VALUES
+  ('d','dddd'), ('i','iii'), ('a','aa'), ('b','bb'), ('g','gg'), 
+  ('e','eee'), ('c','cccc'), ('h','hhh'), ('j','jjj'), ('f','fff');
+INSERT INTO t2 VALUES
+  ('jjj', 'j'), ('cc','c'), ('ccc','c'), ('aaa', 'a'), ('jjjj','j'),
+  ('hhh','h'), ('gg','g'), ('fff','f'), ('ee','e'), ('ffff','f'),
+  ('bbb','b'), ('ff','f'), ('cccc','c'), ('dddd','d'), ('jj','j'),
+  ('aaaa','a'), ('bb','b'), ('eeee','e'), ('aa','a'), ('hh','h');
+
+EXPLAIN SELECT t2.* 
+  FROM t1 JOIN t2 ON t2.fk=t1.pk
+    WHERE t2.fk < 'c' AND t2.pk=t1.fk;
+EXPLAIN SELECT t2.* 
+  FROM t1 JOIN t2 ON t2.fk=t1.pk 
+    WHERE t2.fk BETWEEN 'a' AND 'b' AND t2.pk=t1.fk;
+EXPLAIN SELECT t2.* 
+  FROM t1 JOIN t2 ON t2.fk=t1.pk 
+    WHERE t2.fk IN ('a','b') AND t2.pk=t1.fk;
+
+DROP TABLE t1,t2;
+
+#
+# Bug #22367: Optimizer uses ref join type instead of eq_ref for simple 
+#               join on strings
+#
+CREATE TABLE t1 (a int, b varchar(20) NOT NULL, PRIMARY KEY(a));
+CREATE TABLE t2 (a int, b varchar(20) NOT NULL,
+                 PRIMARY KEY (a), UNIQUE KEY (b));
+INSERT INTO t1 VALUES (1,'a'),(2,'b'),(3,'c');
+INSERT INTO t2 VALUES (1,'a'),(2,'b'),(3,'c');
+
+EXPLAIN SELECT t1.a FROM t1 LEFT JOIN t2 ON t2.b=t1.b WHERE t1.a=3;
+
+DROP TABLE t1,t2;
+
+#
+# Bug #19579: predicates that become sargable after reading const tables
+#             are not taken into account by optimizer
+#
+
+CREATE TABLE t1(id int PRIMARY KEY, b int, e int);
+CREATE TABLE t2(i int, a int, INDEX si(i), INDEX ai(a));
+CREATE TABLE t3(a int PRIMARY KEY, c char(4), INDEX ci(c));
+
+INSERT INTO t1 VALUES 
+  (1,10,19), (2,20,22), (4,41,42), (9,93,95), (7, 77,79),
+  (6,63,67), (5,55,58), (3,38,39), (8,81,89);
+INSERT INTO t2 VALUES
+  (21,210), (41,410), (82,820), (83,830), (84,840),
+  (65,650), (51,510), (37,370), (94,940), (76,760),
+  (22,220), (33,330), (40,400), (95,950), (38,380),
+  (67,670), (88,880), (57,570), (96,960), (97,970);
+INSERT INTO t3 VALUES
+  (210,'bb'), (950,'ii'), (400,'ab'), (500,'ee'), (220,'gg'),
+  (440,'gg'), (310,'eg'), (380,'ee'), (840,'bb'), (830,'ff'),
+  (230,'aa'), (960,'ii'), (410,'aa'), (510,'ee'), (290,'bb'),
+  (450,'gg'), (320,'dd'), (390,'hh'), (850,'jj'), (860,'ff');
+
+EXPLAIN
+SELECT t3.a FROM t1,t2 FORCE INDEX (si),t3
+  WHERE t1.id = 8 AND t2.i BETWEEN t1.b AND t1.e AND 
+        t3.a=t2.a AND t3.c IN ('bb','ee');
+EXPLAIN
+SELECT t3.a FROM t1,t2,t3
+  WHERE t1.id = 8 AND t2.i BETWEEN t1.b AND t1.e AND
+        t3.a=t2.a AND t3.c IN ('bb','ee') ;
+
+EXPLAIN 
+SELECT t3.a FROM t1,t2 FORCE INDEX (si),t3
+  WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND t3.a=t2.a AND
+        t3.c IN ('bb','ee');
+EXPLAIN 
+SELECT t3.a FROM t1,t2,t3
+  WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND t3.a=t2.a AND
+        t3.c IN ('bb','ee');
 
 DROP TABLE t1,t2,t3;
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index 7811301a9bc..6d5082c360b 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -2371,6 +2371,32 @@ explain select * from t1 where not exists
 DROP TABLE t1;
 
 #
+# Bug#21798: memory leak during query execution with subquery in column 
+#             list using a function
+#
+CREATE TABLE t1 (a VARCHAR(250), b INT auto_increment, PRIMARY KEY (b));
+insert into t1 (a) values (FLOOR(rand() * 100));
+insert into t1 (a) select FLOOR(rand() * 100) from t1;
+insert into t1 (a) select FLOOR(rand() * 100) from t1;
+insert into t1 (a) select FLOOR(rand() * 100) from t1;
+insert into t1 (a) select FLOOR(rand() * 100) from t1;
+insert into t1 (a) select FLOOR(rand() * 100) from t1;
+insert into t1 (a) select FLOOR(rand() * 100) from t1;
+insert into t1 (a) select FLOOR(rand() * 100) from t1;
+insert into t1 (a) select FLOOR(rand() * 100) from t1;
+insert into t1 (a) select FLOOR(rand() * 100) from t1;
+insert into t1 (a) select FLOOR(rand() * 100) from t1;
+insert into t1 (a) select FLOOR(rand() * 100) from t1;
+insert into t1 (a) select FLOOR(rand() * 100) from t1;
+insert into t1 (a) select FLOOR(rand() * 100) from t1;
+
+SELECT a, 
+       (SELECT REPEAT(' ',250) FROM t1 i1 
+        WHERE i1.b=t1.a ORDER BY RAND() LIMIT 1) AS a 
+FROM t1 ORDER BY a LIMIT 5;
+DROP TABLE t1;
+
+#
 # Bug #21540: Subqueries with no from and aggregate functions return 
 #              wrong results
 CREATE TABLE t1 (a INT, b INT);
diff --git a/mysql-test/t/type_decimal.test b/mysql-test/t/type_decimal.test
index 441d750004e..4fdb0c8458f 100644
--- a/mysql-test/t/type_decimal.test
+++ b/mysql-test/t/type_decimal.test
@@ -385,3 +385,12 @@ insert into t1 values (40);
 flush tables;
 select f1 from t1 where f1 in (select f1 from t1);
 drop table t1;
+
+#
+# Bug#22183: Unhandled NULL caused server crash
+#
+create table t1 as
+ select from_days(s) as date,t
+ from (select 1 as s,'t' as t union select null, null ) as sub1;
+select group_concat(t) from t1 group by week(date)/10;
+drop table t1;
diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test
index f6615914005..f86e32e6a82 100644
--- a/mysql-test/t/view.test
+++ b/mysql-test/t/view.test
@@ -2856,6 +2856,7 @@ EXPLAIN SELECT * FROM v1 t WHERE t.s1+1 < (SELECT MAX(t1.s1) FROM t1);
 DROP VIEW v1;
 DROP TABLE t1;
 
+#
 # Bug #5505: Wrong error message on INSERT into a view
 #
 create table t1 (s1 int);
diff --git a/sql/item_buff.cc b/sql/item_buff.cc
index 1661f04a4ae..37f9ca7ce6c 100644
--- a/sql/item_buff.cc
+++ b/sql/item_buff.cc
@@ -132,11 +132,17 @@ bool Cached_item_decimal::cmp()
 {
   my_decimal tmp;
   my_decimal *ptmp= item->val_decimal(&tmp);
-  if (null_value != item->null_value || my_decimal_cmp(&value, ptmp))
+  if (null_value != item->null_value ||
+      (!item->null_value && my_decimal_cmp(&value, ptmp)))
   {
     null_value= item->null_value;
-    my_decimal2decimal(ptmp, &value);
-    return TRUE;
+    /* Save only not null values */
+    if (!null_value)
+    {
+      my_decimal2decimal(ptmp, &value);
+      return TRUE;
+    }
+    return FALSE;
   }
   return FALSE;
 }
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index 135e4596996..9435b3767a0 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -1122,6 +1122,8 @@ bool Item_func_between::fix_fields(THD *thd, Item **ref)
   if (Item_func_opt_neg::fix_fields(thd, ref))
     return 1;
 
+  thd->lex->current_select->between_count++;
+
   /* not_null_tables_cache == union(T1(e),T1(e1),T1(e2)) */
   if (pred_level && !negated)
     return 0;
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index 1b88153b049..c2d93fdd5ef 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -448,6 +448,7 @@ public:
     negated= !negated;
     return this;
   }
+  bool subst_argument_checker(byte **arg) { return TRUE; }
 };
 
 
diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h
index 339ca9d965a..5fb742e362e 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -784,6 +784,9 @@ bool quick_rm_table(handlerton *base,const char *db,
                     const char *table_name, uint flags);
 void close_cached_table(THD *thd, TABLE *table);
 bool mysql_rename_tables(THD *thd, TABLE_LIST *table_list, bool silent);
+bool do_rename(THD *thd, TABLE_LIST *ren_table, char *new_db,
+                      char *new_table_name, char *new_table_alias,
+                      bool skip_error);
 bool mysql_change_db(THD *thd,const char *name,bool no_access_check);
 void mysql_parse(THD *thd,char *inBuf,uint length);
 bool mysql_test_parse_for_slave(THD *thd,char *inBuf,uint length);
@@ -870,7 +873,8 @@ bool mysql_xa_recover(THD *thd);
 bool check_simple_select();
 int mysql_alter_tablespace(THD* thd, st_alter_tablespace *ts_info);
 
-SORT_FIELD * make_unireg_sortorder(ORDER *order, uint *length);
+SORT_FIELD * make_unireg_sortorder(ORDER *order, uint *length,
+                                  SORT_FIELD *sortorder);
 int setup_order(THD *thd, Item **ref_pointer_array, TABLE_LIST *tables,
 		List<Item> &fields, List <Item> &all_fields, ORDER *order);
 int setup_group(THD *thd, Item **ref_pointer_array, TABLE_LIST *tables,
diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index 0abb37b5345..5878771c096 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -9787,6 +9787,7 @@ TRP_GROUP_MIN_MAX::make_quick(PARAM *param, bool retrieve_full_rows,
     quick->quick_prefix_select= NULL;
 
   quick->update_key_stat();
+  quick->adjust_prefix_ranges();
 
   DBUG_RETURN(quick);
 }
@@ -10017,6 +10018,42 @@ bool QUICK_GROUP_MIN_MAX_SELECT::add_range(SEL_ARG *sel_range)
 
 
 /*
+  Opens the ranges if there are more conditions in quick_prefix_select than
+  the ones used for jumping through the prefixes.
+
+  SYNOPSIS
+    QUICK_GROUP_MIN_MAX_SELECT::adjust_prefix_ranges()
+
+  NOTES
+    quick_prefix_select is made over the conditions on the whole key.
+    It defines a number of ranges of length x. 
+    However when jumping through the prefixes we use only the the first 
+    few most significant keyparts in the range key. However if there
+    are more keyparts to follow the ones we are using we must make the 
+    condition on the key inclusive (because x < "ab" means 
+    x[0] < 'a' OR (x[0] == 'a' AND x[1] < 'b').
+    To achive the above we must turn off the NEAR_MIN/NEAR_MAX
+*/
+void QUICK_GROUP_MIN_MAX_SELECT::adjust_prefix_ranges ()
+{
+  if (quick_prefix_select &&
+      group_prefix_len < quick_prefix_select->max_used_key_length)
+  {
+    DYNAMIC_ARRAY *arr;
+    uint inx;
+
+    for (inx= 0, arr= &quick_prefix_select->ranges; inx < arr->elements; inx++)
+    {
+      QUICK_RANGE *range;
+
+      get_dynamic(arr, (gptr)&range, inx);
+      range->flag &= ~(NEAR_MIN | NEAR_MAX);
+    }
+  }
+}
+
+
+/*
   Determine the total number and length of the keys that will be used for
   index lookup.
 
diff --git a/sql/opt_range.h b/sql/opt_range.h
index 6099608f7cd..a88c79e8bab 100644
--- a/sql/opt_range.h
+++ b/sql/opt_range.h
@@ -295,6 +295,7 @@ protected:
   friend class QUICK_SELECT_DESC;
   friend class QUICK_INDEX_MERGE_SELECT;
   friend class QUICK_ROR_INTERSECT_SELECT;
+  friend class QUICK_GROUP_MIN_MAX_SELECT;
 
   DYNAMIC_ARRAY ranges;     /* ordered array of range ptrs */
   QUICK_RANGE **cur_range;  /* current element in ranges  */
@@ -643,6 +644,7 @@ public:
   ~QUICK_GROUP_MIN_MAX_SELECT();
   bool add_range(SEL_ARG *sel_range);
   void update_key_stat();
+  void adjust_prefix_ranges();
   bool alloc_buffers();
   int init();
   int reset();
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index a530c7f7fdc..3e7bcf7798a 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -4362,13 +4362,20 @@ find_item_in_list(Item *find, List<Item> &items, uint *counter,
   const char *field_name=0;
   const char *table_name=0;
   bool found_unaliased_non_uniq= 0;
+  /*
+    true if the item that we search for is a valid name reference
+    (and not an item that happens to have a name).
+  */
+  bool is_ref_by_name= 0;
   uint unaliased_counter;
 
   LINT_INIT(unaliased_counter);                 // Dependent on found_unaliased
 
   *unaliased= FALSE;
 
-  if (find->type() == Item::FIELD_ITEM	|| find->type() == Item::REF_ITEM)
+  is_ref_by_name= (find->type() == Item::FIELD_ITEM  || 
+                   find->type() == Item::REF_ITEM);
+  if (is_ref_by_name)
   {
     field_name= ((Item_ident*) find)->field_name;
     table_name= ((Item_ident*) find)->table_name;
@@ -4480,7 +4487,7 @@ find_item_in_list(Item *find, List<Item> &items, uint *counter,
       }
     }
     else if (!table_name && (find->eq(item,0) ||
-			     find->name && item->name &&
+			     is_ref_by_name && find->name && item->name &&
 			     !my_strcasecmp(system_charset_info, 
 					    item->name,find->name)))
     {
@@ -5810,6 +5817,7 @@ int setup_conds(THD *thd, TABLE_LIST *tables, TABLE_LIST *leaves,
   thd->mark_used_columns= MARK_COLUMNS_READ;
   DBUG_PRINT("info", ("thd->mark_used_columns: %d", thd->mark_used_columns));
   select_lex->cond_count= 0;
+  select_lex->between_count= 0;
 
   for (table= tables; table; table= table->next_local)
   {
diff --git a/sql/sql_delete.cc b/sql/sql_delete.cc
index ca006faf6e7..f128b5e8706 100644
--- a/sql/sql_delete.cc
+++ b/sql/sql_delete.cc
@@ -183,7 +183,7 @@ bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds,
                                                    MYF(MY_FAE | MY_ZEROFILL));
     
       if (!(sortorder= make_unireg_sortorder((ORDER*) order->first,
-                                             &length)) ||
+                                             &length, NULL)) ||
 	  (table->sort.found_records = filesort(thd, table, sortorder, length,
                                                 select, HA_POS_ERROR, 1,
                                                 &examined_rows))
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index d94c45c4bdd..dd358492d0d 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -1147,7 +1147,7 @@ void st_select_lex::init_query()
     initialization is checked for failure.
   */
   parent_lex->push_context(&context);
-  cond_count= with_wild= 0;
+  cond_count= between_count= with_wild= 0;
   conds_processed_with_permanent_arena= 0;
   ref_pointer_array= 0;
   select_n_having_items= 0;
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index a2806a03369..1815774526f 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -556,7 +556,8 @@ public:
     list during split_sum_func
   */
   uint select_n_having_items;
-  uint cond_count;      /* number of arguments of and/or/xor in where/having */
+  uint cond_count;    /* number of arguments of and/or/xor in where/having/on */
+  uint between_count; /* number of between predicates in where/having/on      */   
   enum_parsing_place parsing_place; /* where we are parsing expression */
   bool with_sum_func;   /* sum function indicator */
   /* 
diff --git a/sql/sql_rename.cc b/sql/sql_rename.cc
index 73473ddd24b..8b04345640b 100644
--- a/sql/sql_rename.cc
+++ b/sql/sql_rename.cc
@@ -126,95 +126,141 @@ static TABLE_LIST *reverse_table_list(TABLE_LIST *table_list)
 
 
 /*
-  Rename all tables in list; Return pointer to wrong entry if something goes
-  wrong.  Note that the table_list may be empty!
+  Rename a single table or a view
+
+  SYNPOSIS
+    do_rename()
+      thd               Thread handle
+      ren_table         A table/view to be renamed
+      new_db            The database to which the table to be moved to
+      new_table_name    The new table/view name
+      new_table_alias   The new table/view alias
+      skip_error        Whether to skip error
+
+  DESCRIPTION
+    Rename a single table or a view.
+
+  RETURN
+    false     Ok
+    true      rename failed
 */
 
-static TABLE_LIST *
-rename_tables(THD *thd, TABLE_LIST *table_list, bool skip_error)
+bool
+do_rename(THD *thd, TABLE_LIST *ren_table, char *new_db, char *new_table_name,
+          char *new_table_alias, bool skip_error)
 {
-  TABLE_LIST *ren_table,*new_table;
+  int rc= 1;
+  char name[FN_REFLEN];
+  const char *new_alias, *old_alias;
   frm_type_enum frm_type;
   enum legacy_db_type table_type;
 
-  DBUG_ENTER("rename_tables");
+  DBUG_ENTER("do_rename");
 
-  for (ren_table= table_list; ren_table; ren_table= new_table->next_local)
+  if (lower_case_table_names == 2)
   {
-    int rc= 1;
-    char name[FN_REFLEN];
-    const char *new_alias, *old_alias;
-
-    new_table= ren_table->next_local;
-    if (lower_case_table_names == 2)
-    {
-      old_alias= ren_table->alias;
-      new_alias= new_table->alias;
-    }
-    else
-    {
-      old_alias= ren_table->table_name;
-      new_alias= new_table->table_name;
-    }
-    build_table_filename(name, sizeof(name),
-                         new_table->db, new_alias, reg_ext, 0);
-    if (!access(name,F_OK))
-    {
-      my_error(ER_TABLE_EXISTS_ERROR, MYF(0), new_alias);
-      DBUG_RETURN(ren_table);			// This can't be skipped
-    }
-    build_table_filename(name, sizeof(name),
-                         ren_table->db, old_alias, reg_ext, 0);
+    old_alias= ren_table->alias;
+    new_alias= new_table_alias;
+  }
+  else
+  {
+    old_alias= ren_table->table_name;
+    new_alias= new_table_table_name;
+  }
+  build_table_filename(name, sizeof(name),
+                       new_db, new_alias, reg_ext, 0);
+  if (!access(name,F_OK))
+  {
+    my_error(ER_TABLE_EXISTS_ERROR, MYF(0), new_alias);
+    DBUG_RETURN(1);			// This can't be skipped
+  }
+  build_table_filename(name, sizeof(name),
+                       ren_table->db, old_alias, reg_ext, 0);
 
-    frm_type= mysql_frm_type(thd, name, &table_type);
-    switch (frm_type)
-    {
-      case FRMTYPE_TABLE:
+  frm_type= mysql_frm_type(thd, name, &table_type);
+  switch (frm_type)
+  {
+    case FRMTYPE_TABLE:
       {
-        if (table_type == DB_TYPE_UNKNOWN) 
-          my_error(ER_FILE_NOT_FOUND, MYF(0), name, my_errno);
-        else
+        if (!(rc= mysql_rename_table(table_type, ren_table->db, old_alias,
+                                     new_db, new_alias)))
         {
-          if (!(rc= mysql_rename_table(ha_resolve_by_legacy_type(thd,
-                                                                 table_type),
-                                       ren_table->db, old_alias,
-                                       new_table->db, new_alias, 0)))
+          if ((rc= Table_triggers_list::change_table_name(thd, ren_table->db,
+                                                          old_alias,
+                                                          new_db,
+                                                          new_alias)))
           {
-            if ((rc= Table_triggers_list::change_table_name(thd, ren_table->db,
-                                                            old_alias,
-                                                            new_table->db,
-                                                            new_alias)))
-            {
-              /*
-                We've succeeded in renaming table's .frm and in updating
-                corresponding handler data, but have failed to update table's
-                triggers appropriately. So let us revert operations on .frm
-                and handler's data and report about failure to rename table.
-              */
-              (void) mysql_rename_table(ha_resolve_by_legacy_type(thd,
-                                                                  table_type),
-                                        new_table->db, new_alias,
-                                        ren_table->db, old_alias, 0);
-            }
+            /*
+              We've succeeded in renaming table's .frm and in updating
+              corresponding handler data, but have failed to update table's
+              triggers appropriately. So let us revert operations on .frm
+              and handler's data and report about failure to rename table.
+            */
+            (void) mysql_rename_table(ha_resolve_by_legacy_type(thd,
+                                                                table_type),
+                                      new_db, new_alias,
+                                      ren_table->db, old_alias, 0);
           }
         }
-        break;
       }
-      case FRMTYPE_VIEW:
-        /* change of schema is not allowed */
-        if (strcmp(ren_table->db, new_table->db))
-          my_error(ER_FORBID_SCHEMA_CHANGE, MYF(0), ren_table->db, 
-                   new_table->db);
-        else
-          rc= mysql_rename_view(thd, new_alias, ren_table);
-        break;
-      default:
-        DBUG_ASSERT(0); // should never happen
-      case FRMTYPE_ERROR:
-        my_error(ER_FILE_NOT_FOUND, MYF(0), name, my_errno);
-        break;
+      break;
     }
-    if (rc && !skip_error)
+    case FRMTYPE_VIEW:
+      /* change of schema is not allowed */
+      if (strcmp(ren_table->db, new_db))
+        my_error(ER_FORBID_SCHEMA_CHANGE, MYF(0), ren_table->db, 
+                 new_db);
+      else
+        rc= mysql_rename_view(thd, new_alias, ren_table);
+      break;
+    default:
+      DBUG_ASSERT(0); // should never happen
+    case FRMTYPE_ERROR:
+      my_error(ER_FILE_NOT_FOUND, MYF(0), name, my_errno);
+      break;
+  }
+  if (rc && !skip_error)
+    DBUG_RETURN(1);
+
+  DBUG_RETURN(0);
+
+}
+/*
+  Rename all tables in list; Return pointer to wrong entry if something goes
+  wrong.  Note that the table_list may be empty!
+*/
+
+/*
+  Rename tables/views in the list
+
+  SYNPOSIS
+    rename_tables()
+      thd               Thread handle
+      table_list        List of tables to rename
+      skip_error        Whether to skip errors
+
+  DESCRIPTION
+    Take a table/view name from and odd list element and rename it to a
+    the name taken from list element+1. Note that the table_list may be
+    empty.
+
+  RETURN
+    false     Ok
+    true      rename failed
+*/
+
+static TABLE_LIST *
+rename_tables(THD *thd, TABLE_LIST *table_list, bool skip_error)
+{
+  TABLE_LIST *ren_table,*new_table, *tmp_table;
+
+  DBUG_ENTER("rename_tables");
+
+  for (ren_table= table_list; ren_table; ren_table= new_table->next_local)
+  {
+    new_table= ren_table->next_local;
+    if (do_rename(thd, ren_table, new_table->db, new_table->table_name,
+                  new_table->alias, skip_error))
       DBUG_RETURN(ren_table);
   }
   DBUG_RETURN(0);
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index ed91719fd46..9e6c19bfac8 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -35,14 +35,17 @@ const char *join_type_str[]={ "UNKNOWN","system","const","eq_ref","ref",
                               "index_merge"
 };
 
+struct st_sargable_param;
+
 static void optimize_keyuse(JOIN *join, DYNAMIC_ARRAY *keyuse_array);
 static bool make_join_statistics(JOIN *join, TABLE_LIST *leaves, COND *conds,
 				 DYNAMIC_ARRAY *keyuse);
 static bool update_ref_and_keys(THD *thd, DYNAMIC_ARRAY *keyuse,
-				JOIN_TAB *join_tab,
+                                JOIN_TAB *join_tab,
                                 uint tables, COND *conds,
                                 COND_EQUAL *cond_equal,
-				table_map table_map, SELECT_LEX *select_lex);
+                                table_map table_map, SELECT_LEX *select_lex,
+                                st_sargable_param **sargables);
 static int sort_keyuse(KEYUSE *a,KEYUSE *b);
 static void set_position(JOIN *join,uint index,JOIN_TAB *table,KEYUSE *key);
 static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, KEYUSE *org_keyuse,
@@ -1413,7 +1416,8 @@ JOIN::exec()
       simple_order= simple_group;
       skip_sort_order= 0;
     }
-    if (order &&
+    if (order && 
+        (order != group_list || !(select_options & SELECT_BIG_RESULT)) &&
 	(const_tables == tables ||
  	 ((simple_order || skip_sort_order) &&
 	  test_if_skip_sort_order(&join_tab[const_tables], order,
@@ -1591,6 +1595,7 @@ JOIN::exec()
 	{
 	  DBUG_VOID_RETURN;
 	}
+        sortorder= curr_join->sortorder;
       }
       
       thd->proc_info="Copying to group table";
@@ -1803,6 +1808,7 @@ JOIN::exec()
 			    (select_options & OPTION_FOUND_ROWS ?
 			     HA_POS_ERROR : unit->select_limit_cnt)))
 	DBUG_VOID_RETURN;
+      sortorder= curr_join->sortorder;
       if (curr_join->const_tables != curr_join->tables &&
           !curr_join->join_tab[curr_join->const_tables].table->sort.io_cache)
       {
@@ -2069,6 +2075,19 @@ static ha_rows get_quick_record_count(THD *thd, SQL_SELECT *select,
   DBUG_RETURN(HA_POS_ERROR);			/* This shouldn't happend */
 }
 
+/*
+   This structure is used to collect info on potentially sargable
+   predicates in order to check whether they become sargable after
+   reading const tables.
+   We form a bitmap of indexes that can be used for sargable predicates.
+   Only such indexes are involved in range analysis.
+*/
+typedef struct st_sargable_param
+{
+  Field *field;              /* field against which to check sargability */
+  Item **arg_value;          /* values of potential keys for lookups     */
+  uint num_values;           /* number of values in the above array      */
+} SARGABLE_PARAM;  
 
 /*
   Calculate the best possible join and initialize the join structure
@@ -2091,6 +2110,7 @@ make_join_statistics(JOIN *join, TABLE_LIST *tables, COND *conds,
   JOIN_TAB *stat,*stat_end,*s,**stat_ref;
   KEYUSE *keyuse,*start_keyuse;
   table_map outer_join=0;
+  SARGABLE_PARAM *sargables= 0;
   JOIN_TAB *stat_vector[MAX_TABLES+1];
   DBUG_ENTER("make_join_statistics");
 
@@ -2223,7 +2243,7 @@ make_join_statistics(JOIN *join, TABLE_LIST *tables, COND *conds,
   if (conds || outer_join)
     if (update_ref_and_keys(join->thd, keyuse_array, stat, join->tables,
                             conds, join->cond_equal,
-                            ~outer_join, join->select_lex))
+                            ~outer_join, join->select_lex, &sargables))
       DBUG_RETURN(1);
 
   /* Read tables with 0 or 1 rows (system tables) */
@@ -2373,6 +2393,26 @@ make_join_statistics(JOIN *join, TABLE_LIST *tables, COND *conds,
     }
   } while (join->const_table_map & found_ref && ref_changed);
 
+  /* 
+    Update info on indexes that can be used for search lookups as
+    reading const tables may has added new sargable predicates. 
+  */
+  if (const_count && sargables)
+  {
+    for( ; sargables->field ; sargables++)
+    {
+      Field *field= sargables->field;
+      JOIN_TAB *stat= field->table->reginfo.join_tab;
+      key_map possible_keys= field->key_start;
+      possible_keys.intersect(field->table->keys_in_use_for_query);
+      bool is_const= 1;
+      for (uint i=0; i< sargables->num_values; i++)
+        is_const&= sargables->arg_value[i]->const_item();
+      if (is_const)
+        stat[0].const_keys.merge(possible_keys);
+    }
+  }
+
   /* Calc how many (possible) matched records in each table */
 
   for (s=stat ; s < stat_end ; s++)
@@ -2632,6 +2672,7 @@ merge_key_fields(KEY_FIELD *start,KEY_FIELD *new_fields,KEY_FIELD *end,
     eq_func			True if we used =, <=> or IS NULL
     value			Value used for comparison with field
     usable_tables		Tables which can be used for key optimization
+    sargables            IN/OUT Array of found sargable candidates
 
   NOTES
     If we are doing a NOT NULL comparison on a NOT NULL field in a outer join
@@ -2643,8 +2684,8 @@ merge_key_fields(KEY_FIELD *start,KEY_FIELD *new_fields,KEY_FIELD *end,
 
 static void
 add_key_field(KEY_FIELD **key_fields,uint and_level, Item_func *cond,
-	      Field *field, bool eq_func, Item **value, uint num_values,
-	      table_map usable_tables)
+              Field *field, bool eq_func, Item **value, uint num_values,
+              table_map usable_tables, SARGABLE_PARAM **sargables)
 {
   uint exists_optimize= 0;
   if (!(field->flags & PART_KEY_FLAG))
@@ -2703,6 +2744,19 @@ add_key_field(KEY_FIELD **key_fields,uint and_level, Item_func *cond,
       }
       if (is_const)
         stat[0].const_keys.merge(possible_keys);
+      else if (!eq_func)
+      {
+        /* 
+          Save info to be able check whether this predicate can be 
+          considered as sargable for range analisis after reading const tables.
+          We do not save info about equalities as update_const_equal_items
+          will take care of updating info on keys from sargable equalities. 
+        */
+        (*sargables)--;
+        (*sargables)->field= field;
+        (*sargables)->arg_value= value;
+        (*sargables)->num_values= num_values;
+      }
       /*
 	We can't always use indexes when comparing a string index to a
 	number. cmp_type() is checked to allow compare of dates to numbers.
@@ -2793,6 +2847,7 @@ add_key_field(KEY_FIELD **key_fields,uint and_level, Item_func *cond,
     value			Value used for comparison with field
 				Is NULL for BETWEEN and IN    
     usable_tables		Tables which can be used for key optimization
+    sargables            IN/OUT Array of found sargable candidates
 
   NOTES
     If field items f1 and f2 belong to the same multiple equality and
@@ -2806,11 +2861,12 @@ static void
 add_key_equal_fields(KEY_FIELD **key_fields, uint and_level,
                      Item_func *cond, Item_field *field_item,
                      bool eq_func, Item **val,
-                     uint num_values, table_map usable_tables)
+                     uint num_values, table_map usable_tables,
+                     SARGABLE_PARAM **sargables)
 {
   Field *field= field_item->field;
   add_key_field(key_fields, and_level, cond, field,
-                eq_func, val, num_values, usable_tables);
+                eq_func, val, num_values, usable_tables, sargables);
   Item_equal *item_equal= field_item->item_equal;
   if (item_equal)
   { 
@@ -2825,7 +2881,8 @@ add_key_equal_fields(KEY_FIELD **key_fields, uint and_level,
       if (!field->eq(item->field))
       {
         add_key_field(key_fields, and_level, cond, item->field,
-                      eq_func, val, num_values, usable_tables);
+                      eq_func, val, num_values, usable_tables,
+                      sargables);
       }
     }
   }
@@ -2833,7 +2890,8 @@ add_key_equal_fields(KEY_FIELD **key_fields, uint and_level,
 
 static void
 add_key_fields(KEY_FIELD **key_fields,uint *and_level,
-	       COND *cond, table_map usable_tables)
+               COND *cond, table_map usable_tables,
+               SARGABLE_PARAM **sargables)
 {
   if (cond->type() == Item_func::COND_ITEM)
   {
@@ -2844,20 +2902,20 @@ add_key_fields(KEY_FIELD **key_fields,uint *and_level,
     {
       Item *item;
       while ((item=li++))
-	add_key_fields(key_fields,and_level,item,usable_tables);
+	add_key_fields(key_fields,and_level,item,usable_tables,sargables);
       for (; org_key_fields != *key_fields ; org_key_fields++)
 	org_key_fields->level= *and_level;
     }
     else
     {
       (*and_level)++;
-      add_key_fields(key_fields,and_level,li++,usable_tables);
+      add_key_fields(key_fields,and_level,li++,usable_tables,sargables);
       Item *item;
       while ((item=li++))
       {
 	KEY_FIELD *start_key_fields= *key_fields;
 	(*and_level)++;
-	add_key_fields(key_fields,and_level,item,usable_tables);
+	add_key_fields(key_fields,and_level,item,usable_tables,sargables);
 	*key_fields=merge_key_fields(org_key_fields,start_key_fields,
 				     *key_fields,++(*and_level));
       }
@@ -2888,9 +2946,9 @@ add_key_fields(KEY_FIELD **key_fields,uint *and_level,
                   cond_func->argument_count() != 2);
       add_key_equal_fields(key_fields, *and_level, cond_func,
                            (Item_field*) (cond_func->key_item()->real_item()),
-                           0, values,
+                           0, values, 
                            cond_func->argument_count()-1,
-                           usable_tables);
+                           usable_tables, sargables);
     }
     if (cond_func->functype() == Item_func::BETWEEN)
     {
@@ -2904,7 +2962,8 @@ add_key_fields(KEY_FIELD **key_fields,uint *and_level,
         {
           field_item= (Item_field *) (cond_func->arguments()[i]->real_item());
           add_key_equal_fields(key_fields, *and_level, cond_func,
-                               field_item, 0, values, 1, usable_tables);
+                               field_item, 0, values, 1, usable_tables, 
+                               sargables);
         }
       }  
     }
@@ -2921,7 +2980,8 @@ add_key_fields(KEY_FIELD **key_fields,uint *and_level,
       add_key_equal_fields(key_fields, *and_level, cond_func,
 	                (Item_field*) (cond_func->arguments()[0])->real_item(),
 		           equal_func,
-		           cond_func->arguments()+1, 1, usable_tables);
+                           cond_func->arguments()+1, 1, usable_tables,
+                           sargables);
     }
     if (cond_func->arguments()[1]->real_item()->type() == Item::FIELD_ITEM &&
 	cond_func->functype() != Item_func::LIKE_FUNC &&
@@ -2930,7 +2990,8 @@ add_key_fields(KEY_FIELD **key_fields,uint *and_level,
       add_key_equal_fields(key_fields, *and_level, cond_func, 
                        (Item_field*) (cond_func->arguments()[1])->real_item(),
 		           equal_func,
-		           cond_func->arguments(),1,usable_tables);
+                           cond_func->arguments(),1,usable_tables,
+                           sargables);
     }
     break;
   }
@@ -2945,7 +3006,7 @@ add_key_fields(KEY_FIELD **key_fields,uint *and_level,
       add_key_equal_fields(key_fields, *and_level, cond_func,
 		    (Item_field*) (cond_func->arguments()[0])->real_item(),
 		    cond_func->functype() == Item_func::ISNULL_FUNC,
-		    &tmp, 1, usable_tables);
+			   &tmp, 1, usable_tables, sargables);
     }
     break;
   case Item_func::OPTIMIZE_EQUAL:
@@ -2963,7 +3024,7 @@ add_key_fields(KEY_FIELD **key_fields,uint *and_level,
       while ((item= it++))
       {
         add_key_field(key_fields, *and_level, cond_func, item->field,
-                      TRUE, &const_item, 1, usable_tables);
+                      TRUE, &const_item, 1, usable_tables, sargables);
       }
     }
     else 
@@ -2983,7 +3044,8 @@ add_key_fields(KEY_FIELD **key_fields,uint *and_level,
           if (!field->eq(item->field))
           {
             add_key_field(key_fields, *and_level, cond_func, field,
-                          TRUE, (Item **) &item, 1, usable_tables);
+                          TRUE, (Item **) &item, 1, usable_tables,
+                          sargables);
           }
         }
         it.rewind();
@@ -3134,6 +3196,7 @@ sort_keyuse(KEYUSE *a,KEYUSE *b)
       nested_join_table  IN     Nested join pseudo-table to process
       end                INOUT  End of the key field array
       and_level          INOUT  And-level
+      sargables          IN/OUT Array of found sargable candidates
 
   DESCRIPTION
     This function populates KEY_FIELD array with entries generated from the 
@@ -3157,7 +3220,8 @@ sort_keyuse(KEYUSE *a,KEYUSE *b)
 */
 
 static void add_key_fields_for_nj(TABLE_LIST *nested_join_table,
-                                  KEY_FIELD **end, uint *and_level)
+                                  KEY_FIELD **end, uint *and_level,
+                                  SARGABLE_PARAM **sargables)
 {
   List_iterator<TABLE_LIST> li(nested_join_table->nested_join->join_list);
   table_map tables= 0;
@@ -3167,12 +3231,12 @@ static void add_key_fields_for_nj(TABLE_LIST *nested_join_table,
   while ((table= li++))
   {
     if (table->nested_join)
-      add_key_fields_for_nj(table, end, and_level);
+      add_key_fields_for_nj(table, end, and_level, sargables);
     else
       if (!table->on_expr)
         tables |= table->table->map;
   }
-  add_key_fields(end, and_level, nested_join_table->on_expr, tables);
+  add_key_fields(end, and_level, nested_join_table->on_expr, tables, sargables);
 }
 
 
@@ -3187,9 +3251,10 @@ static void add_key_fields_for_nj(TABLE_LIST *nested_join_table,
       tables         Number of tables in join
       cond           WHERE condition (note that the function analyzes 
                      join_tab[i]->on_expr too)
-      normal_tables  tables not inner w.r.t some outer join (ones for which
+      normal_tables  Tables not inner w.r.t some outer join (ones for which
                      we can make ref access based the WHERE clause)
       select_lex     current SELECT
+      sargables  OUT Array of found sargable candidates
       
   RETURN 
    0 - OK
@@ -3198,27 +3263,55 @@ static void add_key_fields_for_nj(TABLE_LIST *nested_join_table,
 
 static bool
 update_ref_and_keys(THD *thd, DYNAMIC_ARRAY *keyuse,JOIN_TAB *join_tab,
-		    uint tables, COND *cond, COND_EQUAL *cond_equal,
-                    table_map normal_tables, SELECT_LEX *select_lex)
+                    uint tables, COND *cond, COND_EQUAL *cond_equal,
+                    table_map normal_tables, SELECT_LEX *select_lex,
+                    SARGABLE_PARAM **sargables)
 {
   uint	and_level,i,found_eq_constant;
   KEY_FIELD *key_fields, *end, *field;
+  uint sz;
   uint m= 1;
   
   if (cond_equal && cond_equal->max_members)
     m= cond_equal->max_members;
-
-  if (!(key_fields=(KEY_FIELD*)
-	thd->alloc(sizeof(key_fields[0])*
-		   (thd->lex->current_select->cond_count+1)*2*m)))
+  
+  /* 
+    We use the same piece of memory to store both  KEY_FIELD 
+    and SARGABLE_PARAM structure.
+    KEY_FIELD values are placed at the beginning this memory
+    while  SARGABLE_PARAM values are put at the end.
+    All predicates that are used to fill arrays of KEY_FIELD
+    and SARGABLE_PARAM structures have at most 2 arguments
+    except BETWEEN predicates that have 3 arguments and 
+    IN predicates.
+    This any predicate if it's not BETWEEN/IN can be used 
+    directly to fill at most 2 array elements, either of KEY_FIELD
+    or SARGABLE_PARAM type. For a BETWEEN predicate 3 elements
+    can be filled as this predicate is considered as
+    saragable with respect to each of its argument.
+    An IN predicate can require at most 1 element as currently
+    it is considered as sargable only for its first argument.
+    Multiple equality can add  elements that are filled after
+    substitution of field arguments by equal fields. There
+    can be not more than cond_equal->max_members such substitutions.
+  */ 
+  sz= max(sizeof(KEY_FIELD),sizeof(SARGABLE_PARAM))*
+      (((thd->lex->current_select->cond_count+1)*2 +
+	thd->lex->current_select->between_count)*m+1);
+  if (!(key_fields=(KEY_FIELD*)	thd->alloc(sz)))
     return TRUE; /* purecov: inspected */
   and_level= 0;
   field= end= key_fields;
+  *sargables= (SARGABLE_PARAM *) key_fields + 
+                (sz - sizeof((*sargables)[0].field))/sizeof(SARGABLE_PARAM);
+  /* set a barrier for the array of SARGABLE_PARAM */
+  (*sargables)[0].field= 0; 
+
   if (my_init_dynamic_array(keyuse,sizeof(KEYUSE),20,64))
     return TRUE;
   if (cond)
   {
-    add_key_fields(&end,&and_level,cond,normal_tables);
+    add_key_fields(&end,&and_level,cond,normal_tables,sargables);
     for (; field != end ; field++)
     {
       add_key_part(keyuse,field);
@@ -3241,7 +3334,7 @@ update_ref_and_keys(THD *thd, DYNAMIC_ARRAY *keyuse,JOIN_TAB *join_tab,
     */ 
     if (*join_tab[i].on_expr_ref)
       add_key_fields(&end,&and_level,*join_tab[i].on_expr_ref,
-		     join_tab[i].table->map);
+                     join_tab[i].table->map,sargables);
   }
 
   /* Process ON conditions for the nested joins */
@@ -3251,7 +3344,7 @@ update_ref_and_keys(THD *thd, DYNAMIC_ARRAY *keyuse,JOIN_TAB *join_tab,
     while ((table= li++))
     {
       if (table->nested_join)
-	add_key_fields_for_nj(table, &end, &and_level);
+	add_key_fields_for_nj(table, &end, &and_level, sargables);
     }
   }
 
@@ -5117,9 +5210,28 @@ make_simple_join(JOIN *join,TABLE *tmp_table)
   JOIN_TAB *join_tab;
   DBUG_ENTER("make_simple_join");
 
-  if (!(tableptr=(TABLE**) join->thd->alloc(sizeof(TABLE*))) ||
-      !(join_tab=(JOIN_TAB*) join->thd->alloc(sizeof(JOIN_TAB))))
-    DBUG_RETURN(TRUE);
+  /*
+    Reuse TABLE * and JOIN_TAB if already allocated by a previous call
+    to this function through JOIN::exec (may happen for sub-queries).
+  */
+  if (!join->table_reexec)
+  {
+    if (!(join->table_reexec= (TABLE**) join->thd->alloc(sizeof(TABLE*))))
+      DBUG_RETURN(TRUE);                        /* purecov: inspected */
+    if (join->tmp_join)
+      join->tmp_join->table_reexec= join->table_reexec;
+  }
+  if (!join->join_tab_reexec)
+  {
+    if (!(join->join_tab_reexec=
+          (JOIN_TAB*) join->thd->alloc(sizeof(JOIN_TAB))))
+      DBUG_RETURN(TRUE);                        /* purecov: inspected */
+    if (join->tmp_join)
+      join->tmp_join->join_tab_reexec= join->join_tab_reexec;
+  }
+  tableptr= join->table_reexec;
+  join_tab= join->join_tab_reexec;
+
   join->join_tab=join_tab;
   join->table=tableptr; tableptr[0]=tmp_table;
   join->tables=1;
@@ -7507,7 +7619,22 @@ static void update_const_equal_items(COND *cond, JOIN_TAB *tab)
            ((Item_cond*) cond)->functype() == Item_func::MULT_EQUAL_FUNC)
   {
     Item_equal *item_equal= (Item_equal *) cond;
+    bool contained_const= item_equal->get_const() != NULL;
     item_equal->update_const();
+    if (!contained_const && item_equal->get_const())
+    {
+      /* Update keys for range analysis */
+      Item_equal_iterator it(*item_equal);
+      Item_field *item_field;
+      while ((item_field= it++))
+      {
+        Field *field= item_field->field;
+        JOIN_TAB *stat= field->table->reginfo.join_tab;
+        key_map possible_keys= field->key_start;
+        possible_keys.intersect(field->table->keys_in_use_for_query);
+        stat[0].const_keys.merge(possible_keys);
+      }
+    }
   }
 }
 
@@ -12213,7 +12340,6 @@ static int
 create_sort_index(THD *thd, JOIN *join, ORDER *order,
 		  ha_rows filesort_limit, ha_rows select_limit)
 {
-  SORT_FIELD *sortorder;
   uint length;
   ha_rows examined_rows;
   TABLE *table;
@@ -12227,11 +12353,18 @@ create_sort_index(THD *thd, JOIN *join, ORDER *order,
   table=  tab->table;
   select= tab->select;
 
-  if (test_if_skip_sort_order(tab,order,select_limit,0))
+  /*
+    When there is SQL_BIG_RESULT do not sort using index for GROUP BY,
+    and thus force sorting on disk.
+  */
+  if ((order != join->group_list || 
+       !(join->select_options & SELECT_BIG_RESULT)) &&
+      test_if_skip_sort_order(tab,order,select_limit,0))
     DBUG_RETURN(0);
-  if (!(sortorder=make_unireg_sortorder(order,&length)))
+  if (!(join->sortorder= 
+        make_unireg_sortorder(order,&length,join->sortorder)))
     goto err;				/* purecov: inspected */
-  /* It's not fatal if the following alloc fails */
+
   table->sort.io_cache=(IO_CACHE*) my_malloc(sizeof(IO_CACHE),
                                              MYF(MY_WME | MY_ZEROFILL));
   table->status=0;				// May be wrong if quick_select
@@ -12276,7 +12409,7 @@ create_sort_index(THD *thd, JOIN *join, ORDER *order,
 
   if (table->s->tmp_table)
     table->file->info(HA_STATUS_VARIABLE);	// Get record count
-  table->sort.found_records=filesort(thd, table,sortorder, length,
+  table->sort.found_records=filesort(thd, table,join->sortorder, length,
                                      select, filesort_limit, 0,
                                      &examined_rows);
   tab->records= table->sort.found_records;	// For SQL_CALC_ROWS
@@ -12624,7 +12757,8 @@ err:
 }
 
 
-SORT_FIELD *make_unireg_sortorder(ORDER *order, uint *length)
+SORT_FIELD *make_unireg_sortorder(ORDER *order, uint *length,
+                                  SORT_FIELD *sortorder)
 {
   uint count;
   SORT_FIELD *sort,*pos;
@@ -12633,7 +12767,9 @@ SORT_FIELD *make_unireg_sortorder(ORDER *order, uint *length)
   count=0;
   for (ORDER *tmp = order; tmp; tmp=tmp->next)
     count++;
-  pos=sort=(SORT_FIELD*) sql_alloc(sizeof(SORT_FIELD)*(count+1));
+  if (!sortorder)
+    sortorder= (SORT_FIELD*) sql_alloc(sizeof(SORT_FIELD)*(count+1));
+  pos=sort=sortorder;
   if (!pos)
     return 0;
 
@@ -13751,7 +13887,19 @@ bool JOIN::alloc_func_list()
     disctinct->group_by optimization
   */
   if (select_distinct)
+  {
     group_parts+= fields_list.elements;
+    /*
+      If the ORDER clause is specified then it's possible that
+      it also will be optimized, so reserve space for it too
+    */
+    if (order)
+    {
+      ORDER *ord;
+      for (ord= order; ord; ord= ord->next)
+        group_parts++;
+    }
+  }
 
   /* This must use calloc() as rollup_make_fields depends on this */
   sum_funcs= (Item_sum**) thd->calloc(sizeof(Item_sum**) * (func_count+1) +
@@ -14234,12 +14382,17 @@ bool JOIN::rollup_init()
   while ((item= it++))
   {
     ORDER *group_tmp;
+    bool found_in_group= 0;
+
     for (group_tmp= group_list; group_tmp; group_tmp= group_tmp->next)
     {
       if (*group_tmp->item == item)
+      {
         item->maybe_null= 1;
+        found_in_group= 1;
+      }
     }
-    if (item->type() == Item::FUNC_ITEM)
+    if (item->type() == Item::FUNC_ITEM && !found_in_group)
     {
       bool changed= FALSE;
       if (change_group_ref(thd, (Item_func *) item, group_list, &changed))
diff --git a/sql/sql_select.h b/sql/sql_select.h
index eb6d2d5d34f..a66529a6459 100644
--- a/sql/sql_select.h
+++ b/sql/sql_select.h
@@ -321,6 +321,18 @@ public:
   bool union_part; // this subselect is part of union 
   bool optimized; // flag to avoid double optimization in EXPLAIN
 
+  /* 
+    storage for caching buffers allocated during query execution. 
+    These buffers allocations need to be cached as the thread memory pool is
+    cleared only at the end of the execution of the whole query and not caching
+    allocations that occur in repetition at execution time will result in 
+    excessive memory usage.
+  */  
+  SORT_FIELD *sortorder;                        // make_unireg_sortorder()
+  TABLE **table_reexec;                         // make_simple_join()
+  JOIN_TAB *join_tab_reexec;                    // make_simple_join()
+  /* end of allocation caching storage */
+
   JOIN(THD *thd_arg, List<Item> &fields_arg, ulonglong select_options_arg,
        select_result *result_arg)
     :fields_list(fields_arg)
@@ -346,6 +358,9 @@ public:
     examined_rows= 0;
     exec_tmp_table1= 0;
     exec_tmp_table2= 0;
+    sortorder= 0;
+    table_reexec= 0;
+    join_tab_reexec= 0;
     thd= thd_arg;
     sum_funcs= sum_funcs2= 0;
     procedure= 0;
diff --git a/sql/sql_table.cc b/sql/sql_table.cc
index 7796bc35a79..d2f108cb8f2 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -5151,8 +5151,9 @@ bool mysql_alter_table(THD *thd,char *new_db, char *new_name,
   char reg_path[FN_REFLEN+1];
   ha_rows copied,deleted;
   uint db_create_options, used_fields;
-  handlerton *old_db_type, *new_db_type;
+  handlerton *old_db_type, *new_db_type, table_type;
   HA_CREATE_INFO *create_info;
+  frm_type_enum frm_type;
   uint need_copy_table= 0;
   bool no_table_reopen= FALSE, varchar= FALSE;
 #ifdef WITH_PARTITION_STORAGE_ENGINE
@@ -5234,6 +5235,51 @@ bool mysql_alter_table(THD *thd,char *new_db, char *new_name,
   if (alter_info->tablespace_op != NO_TABLESPACE_OP)
     DBUG_RETURN(mysql_discard_or_import_tablespace(thd,table_list,
 						   alter_info->tablespace_op));
+  sprintf(new_name_buff,"%s/%s/%s%s",mysql_data_home, db, table_name, reg_ext);
+  unpack_filename(new_name_buff, new_name_buff);
+  if (lower_case_table_names != 2)
+    my_casedn_str(files_charset_info, new_name_buff);
+  frm_type= mysql_frm_type(thd, new_name_buff, &table_type);
+  /* Rename a view */
+  if (frm_type == FRMTYPE_VIEW && !(alter_info->flags & ~ALTER_RENAME))
+  {
+    /*
+      Avoid problems with a rename on a table that we have locked or
+      if the user is trying to to do this in a transcation context
+    */
+
+    if (thd->locked_tables || thd->active_transaction())
+    {
+      my_message(ER_LOCK_OR_ACTIVE_TRANSACTION,
+                 ER(ER_LOCK_OR_ACTIVE_TRANSACTION), MYF(0));
+      DBUG_RETURN(1);
+    }
+
+    if (wait_if_global_read_lock(thd,0,1))
+      DBUG_RETURN(1);
+    VOID(pthread_mutex_lock(&LOCK_open));
+    if (lock_table_names(thd, table_list))
+      goto view_err;
+    
+    error=0;
+    if (!do_rename(thd, table_list, new_db, new_name, new_name, 1))
+    {
+      if (mysql_bin_log.is_open())
+      {
+        thd->clear_error();
+        Query_log_event qinfo(thd, thd->query, thd->query_length, 0, FALSE);
+        mysql_bin_log.write(&qinfo);
+      }
+      send_ok(thd);
+    }
+
+    unlock_table_names(thd, table_list, (TABLE_LIST*) 0);
+
+view_err:
+    pthread_mutex_unlock(&LOCK_open);
+    start_waiting_global_read_lock(thd);
+    DBUG_RETURN(error);
+  }
   if (!(table=open_ltable(thd,table_list,TL_WRITE_ALLOW_READ)))
     DBUG_RETURN(TRUE);
   table->use_all_columns();
@@ -6515,7 +6561,7 @@ copy_data_between_tables(TABLE *from,TABLE *to,
     if (thd->lex->select_lex.setup_ref_array(thd, order_num) ||
 	setup_order(thd, thd->lex->select_lex.ref_pointer_array,
 		    &tables, fields, all_fields, order) ||
-	!(sortorder=make_unireg_sortorder(order, &length)) ||
+	!(sortorder=make_unireg_sortorder(order, &length, NULL)) ||
 	(from->sort.found_records = filesort(thd, from, sortorder, length,
 					     (SQL_SELECT *) 0, HA_POS_ERROR, 1,
 					     &examined_rows)) ==
diff --git a/sql/sql_update.cc b/sql/sql_update.cc
index 51ec287a6cb..1d119b99df0 100644
--- a/sql/sql_update.cc
+++ b/sql/sql_update.cc
@@ -327,7 +327,7 @@ int mysql_update(THD *thd,
 
       table->sort.io_cache = (IO_CACHE *) my_malloc(sizeof(IO_CACHE),
 						    MYF(MY_FAE | MY_ZEROFILL));
-      if (!(sortorder=make_unireg_sortorder(order, &length)) ||
+      if (!(sortorder=make_unireg_sortorder(order, &length, NULL)) ||
           (table->sort.found_records= filesort(thd, table, sortorder, length,
                                                select, limit, 1,
                                                &examined_rows))