Merge gleb.loc:/home/uchum/work/bk/5.0-opt

into  gleb.loc:/home/uchum/work/bk/5.1-opt

10 files changed, 259 insertions, 10 deletions

diff --git a/mysql-test/r/insert_select.result b/mysql-test/r/insert_select.result
index 57339b2e29f..bea8706a5be 100644
--- a/mysql-test/r/insert_select.result
+++ b/mysql-test/r/insert_select.result
@@ -688,7 +688,16 @@ ERROR 42S22: Unknown column 't2.x' in 'field list'
 drop table t1,t2;
 CREATE TABLE t1 (a int PRIMARY KEY);
 INSERT INTO t1 values (1), (2);
+flush status;
 INSERT INTO t1 SELECT a + 2 FROM t1 LIMIT 1;
+show status like 'Handler_read%';
+Variable_name	Value
+Handler_read_first	1
+Handler_read_key	0
+Handler_read_next	0
+Handler_read_prev	0
+Handler_read_rnd	0
+Handler_read_rnd_next	1
 DROP TABLE t1;
 CREATE TABLE t1 (x int, y int);
 CREATE TABLE t2 (z int, y int);
@@ -759,3 +768,25 @@ d
 20
 20
 DROP TABLE t1,t2;
+CREATE TABLE t1 (
+id INT AUTO_INCREMENT PRIMARY KEY,
+prev_id INT,
+join_id INT DEFAULT 0);
+INSERT INTO t1 (prev_id) VALUES (NULL), (1), (2);
+SELECT * FROM t1;
+id	prev_id	join_id
+1	NULL	0
+2	1	0
+3	2	0
+CREATE TABLE t2 (join_id INT);
+INSERT INTO t2 (join_id) VALUES (0);
+INSERT INTO t1 (prev_id) SELECT id
+FROM t2 LEFT JOIN t1 ON t1.join_id = t2.join_id
+ORDER BY id DESC LIMIT 1;
+SELECT * FROM t1;
+id	prev_id	join_id
+1	NULL	0
+2	1	0
+3	2	0
+4	3	0
+DROP TABLE t1,t2;
diff --git a/mysql-test/r/metadata.result b/mysql-test/r/metadata.result
index d33fb038b79..4a776b6a253 100644
--- a/mysql-test/r/metadata.result
+++ b/mysql-test/r/metadata.result
@@ -140,4 +140,45 @@ Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is
 def			a	v_small	v_small	3	9	9	N	32769	0	63
 v_small
 214748364
+CREATE TABLE t1 (c1 CHAR(1));
+CREATE TABLE t2 (c2 CHAR(1));
+CREATE VIEW v1 AS SELECT t1.c1 FROM t1;
+CREATE VIEW v2 AS SELECT t2.c2 FROM t2;
+INSERT INTO t1 VALUES ('1'), ('2'), ('3');
+INSERT INTO t2 VALUES ('1'), ('2'), ('3'), ('2');
+SELECT v1.c1 FROM v1 JOIN t2 ON c1=c2 ORDER BY 1;
+Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
+def	test	t1	v1	c1	c1	254	1	1	Y	0	0	8
+c1
+1
+2
+2
+3
+SELECT v1.c1, v2.c2 FROM v1 JOIN v2 ON c1=c2;
+Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
+def	test	t1	v1	c1	c1	254	1	1	Y	0	0	8
+def	test	t2	v2	c2	c2	254	1	1	Y	0	0	8
+c1	c2
+1	1
+2	2
+3	3
+2	2
+SELECT v1.c1, v2.c2 FROM v1 JOIN v2 ON c1=c2 GROUP BY v1.c1;
+Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
+def	test	t1	v1	c1	c1	254	1	1	Y	32768	0	8
+def	test	t2	v2	c2	c2	254	1	1	Y	0	0	8
+c1	c2
+1	1
+2	2
+3	3
+SELECT v1.c1, v2.c2 FROM v1 JOIN v2 ON c1=c2 GROUP BY v1.c1 ORDER BY v2.c2;
+Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
+def	test	t1	v1	c1	c1	254	1	1	Y	32768	0	8
+def	test	t2	v2	c2	c2	254	1	1	Y	0	0	8
+c1	c2
+1	1
+2	2
+3	3
+DROP VIEW v1,v2;
+DROP TABLE t1,t2;
 End of 5.0 tests
diff --git a/mysql-test/r/mysqlbinlog.result b/mysql-test/r/mysqlbinlog.result
index 57961698034..7a76e45ee10 100644
--- a/mysql-test/r/mysqlbinlog.result
+++ b/mysql-test/r/mysqlbinlog.result
@@ -313,6 +313,12 @@ DELIMITER ;
 # End of log file
 ROLLBACK /* added by mysqlbinlog */;
 /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
+CREATE TABLE t1 (c1 CHAR(10));
+flush logs;
+INSERT INTO t1 VALUES ('0123456789');
+flush logs;
+DROP TABLE t1;
+# 	Query	thread_id=REMOVED	exec_time=REMOVED	error_code=REMOVED
 End of 5.0 tests
 flush logs;
 End of 5.1 tests
diff --git a/mysql-test/r/rpl_change_master.result b/mysql-test/r/rpl_change_master.result
index 680328bcea5..6379d763a84 100644
--- a/mysql-test/r/rpl_change_master.result
+++ b/mysql-test/r/rpl_change_master.result
@@ -12,12 +12,76 @@ insert into t1 values(1);
 insert into t1 values(2);
 stop slave;
 show slave status;
-Slave_IO_State	Master_Host	Master_User	Master_Port	Connect_Retry	Master_Log_File	Read_Master_Log_Pos	Relay_Log_File	Relay_Log_Pos	Relay_Master_Log_File	Slave_IO_Running	Slave_SQL_Running	Replicate_Do_DB	Replicate_Ignore_DB	Replicate_Do_Table	Replicate_Ignore_Table	Replicate_Wild_Do_Table	Replicate_Wild_Ignore_Table	Last_Errno	Last_Error	Skip_Counter	Exec_Master_Log_Pos	Relay_Log_Space	Until_Condition	Until_Log_File	Until_Log_Pos	Master_SSL_Allowed	Master_SSL_CA_File	Master_SSL_CA_Path	Master_SSL_Cert	Master_SSL_Cipher	Master_SSL_Key	Seconds_Behind_Master	Master_SSL_Verify_Server_Cert
-#	127.0.0.1	root	MASTER_MYPORT	1	master-bin.000001	#	#	#	master-bin.000001	No	No							0		0	191	#	None		0	No						#	No
+Slave_IO_State	#
+Master_Host	127.0.0.1
+Master_User	root
+Master_Port	MASTER_MYPORT
+Connect_Retry	1
+Master_Log_File	master-bin.000001
+Read_Master_Log_Pos	#
+Relay_Log_File	#
+Relay_Log_Pos	#
+Relay_Master_Log_File	master-bin.000001
+Slave_IO_Running	No
+Slave_SQL_Running	No
+Replicate_Do_DB	
+Replicate_Ignore_DB	
+Replicate_Do_Table	
+Replicate_Ignore_Table	
+Replicate_Wild_Do_Table	
+Replicate_Wild_Ignore_Table	
+Last_Errno	0
+Last_Error	
+Skip_Counter	0
+Exec_Master_Log_Pos	#
+Relay_Log_Space	#
+Until_Condition	None
+Until_Log_File	
+Until_Log_Pos	0
+Master_SSL_Allowed	No
+Master_SSL_CA_File	
+Master_SSL_CA_Path	
+Master_SSL_Cert	
+Master_SSL_Cipher	
+Master_SSL_Key	
+Seconds_Behind_Master	#
+Master_SSL_Verify_Server_Cert	No
 change master to master_user='root';
 show slave status;
-Slave_IO_State	Master_Host	Master_User	Master_Port	Connect_Retry	Master_Log_File	Read_Master_Log_Pos	Relay_Log_File	Relay_Log_Pos	Relay_Master_Log_File	Slave_IO_Running	Slave_SQL_Running	Replicate_Do_DB	Replicate_Ignore_DB	Replicate_Do_Table	Replicate_Ignore_Table	Replicate_Wild_Do_Table	Replicate_Wild_Ignore_Table	Last_Errno	Last_Error	Skip_Counter	Exec_Master_Log_Pos	Relay_Log_Space	Until_Condition	Until_Log_File	Until_Log_Pos	Master_SSL_Allowed	Master_SSL_CA_File	Master_SSL_CA_Path	Master_SSL_Cert	Master_SSL_Cipher	Master_SSL_Key	Seconds_Behind_Master	Master_SSL_Verify_Server_Cert
-#	127.0.0.1	root	MASTER_MYPORT	1	master-bin.000001	#	#	#	master-bin.000001	No	No							0		0	191	#	None		0	No						#	No
+Slave_IO_State	#
+Master_Host	127.0.0.1
+Master_User	root
+Master_Port	MASTER_MYPORT
+Connect_Retry	1
+Master_Log_File	master-bin.000001
+Read_Master_Log_Pos	#
+Relay_Log_File	#
+Relay_Log_Pos	#
+Relay_Master_Log_File	master-bin.000001
+Slave_IO_Running	No
+Slave_SQL_Running	No
+Replicate_Do_DB	
+Replicate_Ignore_DB	
+Replicate_Do_Table	
+Replicate_Ignore_Table	
+Replicate_Wild_Do_Table	
+Replicate_Wild_Ignore_Table	
+Last_Errno	0
+Last_Error	
+Skip_Counter	0
+Exec_Master_Log_Pos	#
+Relay_Log_Space	#
+Until_Condition	None
+Until_Log_File	
+Until_Log_Pos	0
+Master_SSL_Allowed	No
+Master_SSL_CA_File	
+Master_SSL_CA_Path	
+Master_SSL_Cert	
+Master_SSL_Cipher	
+Master_SSL_Key	
+Seconds_Behind_Master	#
+Master_SSL_Verify_Server_Cert	No
 start slave;
 select * from t1;
 n
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index e0620c2fa57..88e12e26c4a 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -3468,6 +3468,30 @@ a1	c
 2	0
 DROP VIEW v1,v2;
 DROP TABLE t1,t2,t3,t4;
+CREATE TABLE t1 (a int, b int);
+INSERT INTO t1 VALUES (1,2), (2,2), (1,3), (1,2);
+CREATE VIEW v1 AS SELECT a, b+1 as b FROM t1;
+SELECT b, SUM(a) FROM v1 WHERE b=3 GROUP BY b;
+b	SUM(a)
+3	4
+EXPLAIN SELECT b, SUM(a) FROM v1 WHERE b=3 GROUP BY b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	Using where
+SELECT a, SUM(b) FROM v1 WHERE b=3 GROUP BY a;
+a	SUM(b)
+1	6
+2	3
+EXPLAIN SELECT a, SUM(b) FROM v1 WHERE b=3 GROUP 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 where; Using temporary; Using filesort
+SELECT a, SUM(b) FROM v1 WHERE a=1 GROUP BY a;
+a	SUM(b)
+1	10
+EXPLAIN SELECT a, SUM(b) FROM v1 WHERE a=1 GROUP 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 where
+DROP VIEW v1;
+DROP TABLE t1;
 End of 5.0 tests.
 DROP DATABASE IF EXISTS `d-1`;
 CREATE DATABASE `d-1`;
diff --git a/mysql-test/t/insert_select.test b/mysql-test/t/insert_select.test
index 655b43d65ad..10bc58303ca 100644
--- a/mysql-test/t/insert_select.test
+++ b/mysql-test/t/insert_select.test
@@ -233,7 +233,9 @@ drop table t1,t2;
 CREATE TABLE t1 (a int PRIMARY KEY);
 INSERT INTO t1 values (1), (2);
 
+flush status;
 INSERT INTO t1 SELECT a + 2 FROM t1 LIMIT 1;
+show status like 'Handler_read%';
 
 DROP TABLE t1;
 
@@ -319,3 +321,26 @@ INSERT INTO t2 (d)
 
 SELECT * FROM t2;
 DROP TABLE t1,t2;
+
+#
+# Bug #29095: incorrect pushing of LIMIT into the temporary
+# table ignoring ORDER BY clause
+#
+
+CREATE TABLE t1 (
+  id INT AUTO_INCREMENT PRIMARY KEY,
+  prev_id INT,
+  join_id INT DEFAULT 0);
+
+INSERT INTO t1 (prev_id) VALUES (NULL), (1), (2);
+SELECT * FROM t1;
+
+CREATE TABLE t2 (join_id INT);
+INSERT INTO t2 (join_id) VALUES (0);
+
+INSERT INTO t1 (prev_id) SELECT id
+  FROM t2 LEFT JOIN t1 ON t1.join_id = t2.join_id
+  ORDER BY id DESC LIMIT 1;
+SELECT * FROM t1;
+
+DROP TABLE t1,t2;
diff --git a/mysql-test/t/metadata.test b/mysql-test/t/metadata.test
index df4acec2021..65c062399b7 100644
--- a/mysql-test/t/metadata.test
+++ b/mysql-test/t/metadata.test
@@ -90,5 +90,26 @@ select a.* from (select 2147483648 as v_large) a;
 select a.* from (select 214748364 as v_small) a;
 --disable_metadata
 
+#
+# Bug #28898: table alias and database name of VIEW columns is empty in the
+# metadata of # SELECT statement where join is executed via temporary table.
+#
+
+CREATE TABLE t1 (c1 CHAR(1));
+CREATE TABLE t2 (c2 CHAR(1));
+CREATE VIEW v1 AS SELECT t1.c1 FROM t1;
+CREATE VIEW v2 AS SELECT t2.c2 FROM t2;
+INSERT INTO t1 VALUES ('1'), ('2'), ('3');
+INSERT INTO t2 VALUES ('1'), ('2'), ('3'), ('2');
+
+--enable_metadata
+SELECT v1.c1 FROM v1 JOIN t2 ON c1=c2 ORDER BY 1;
+SELECT v1.c1, v2.c2 FROM v1 JOIN v2 ON c1=c2;
+SELECT v1.c1, v2.c2 FROM v1 JOIN v2 ON c1=c2 GROUP BY v1.c1;
+SELECT v1.c1, v2.c2 FROM v1 JOIN v2 ON c1=c2 GROUP BY v1.c1 ORDER BY v2.c2;
+--disable_metadata
+
+DROP VIEW v1,v2;
+DROP TABLE t1,t2;
 
 --echo End of 5.0 tests
diff --git a/mysql-test/t/mysqlbinlog.test b/mysql-test/t/mysqlbinlog.test
index 39a84304ebd..92f4b34da83 100644
--- a/mysql-test/t/mysqlbinlog.test
+++ b/mysql-test/t/mysqlbinlog.test
@@ -206,6 +206,19 @@ flush logs;
 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
 --exec $MYSQL_BINLOG --short-form --local-load=$MYSQLTEST_VARDIR/tmp/ $MYSQLTEST_VARDIR/log/master-bin.000010
 
+#
+# Bug#28293 missed '#' sign in the hex dump when the dump length
+# is divisible by 16.
+#
+
+CREATE TABLE t1 (c1 CHAR(10));
+# we need this for getting fixed timestamps inside of this test
+flush logs;
+INSERT INTO t1 VALUES ('0123456789');
+flush logs;
+DROP TABLE t1;
+--exec $MYSQL_BINLOG --hexdump --local-load=$MYSQLTEST_VARDIR/tmp/ $MYSQLTEST_VARDIR/log/master-bin.000012 | grep 'Query' | sed 's/[0-9]\{1,\}/REMOVED/g'
+
 --echo End of 5.0 tests
 
 #
@@ -213,7 +226,7 @@ flush logs;
 #
 flush logs;
 --error 1
---exec $MYSQL_BINLOG $MYSQLTEST_VARDIR/log/master-bin.000012 >/dev/null 2>/dev/null
---exec $MYSQL_BINLOG --force-if-open $MYSQLTEST_VARDIR/log/master-bin.000012 >/dev/null 2>/dev/null
+--exec $MYSQL_BINLOG $MYSQLTEST_VARDIR/log/master-bin.000014 >/dev/null 2>/dev/null
+--exec $MYSQL_BINLOG --force-if-open $MYSQLTEST_VARDIR/log/master-bin.000014 >/dev/null 2>/dev/null
 
 --echo End of 5.1 tests
diff --git a/mysql-test/t/rpl_change_master.test b/mysql-test/t/rpl_change_master.test
index c031464c95e..c5249e15f71 100644
--- a/mysql-test/t/rpl_change_master.test
+++ b/mysql-test/t/rpl_change_master.test
@@ -19,12 +19,12 @@ connection slave;
 --real_sleep 3 # wait for I/O thread to have read updates
 stop slave;
 --replace_result $MASTER_MYPORT MASTER_MYPORT
---replace_column 1 # 7 # 8 # 9 # 23 # 33 #
-show slave status;
+--replace_column 1 # 7 # 8 # 9 # 22 # 23 # 33 #
+query_vertical show slave status;
 change master to master_user='root';
 --replace_result $MASTER_MYPORT MASTER_MYPORT
---replace_column 1 # 7 # 8 # 9 # 23 # 33 #
-show slave status;
+--replace_column 1 # 7 # 8 # 9 # 22 # 23 # 33 #
+query_vertical show slave status;
 start slave;
 sync_with_master;
 select * from t1;
diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test
index 03d7a52c640..c441c7b5efc 100644
--- a/mysql-test/t/view.test
+++ b/mysql-test/t/view.test
@@ -3316,6 +3316,30 @@ SELECT * FROM t1;
 DROP VIEW v1,v2;
 DROP TABLE t1,t2,t3,t4;
 
+#
+# Bug #29104: assertion abort for a query with a view column reference
+#             in the GROUP BY list and a condition requiring the value  
+#             of another view column to be equal to a constant
+#
+
+CREATE TABLE t1 (a int, b int);
+INSERT INTO t1 VALUES (1,2), (2,2), (1,3), (1,2);
+
+CREATE VIEW v1 AS SELECT a, b+1 as b FROM t1;
+
+
+SELECT b, SUM(a) FROM v1 WHERE b=3 GROUP BY b;
+EXPLAIN SELECT b, SUM(a) FROM v1 WHERE b=3 GROUP BY b;
+
+SELECT a, SUM(b) FROM v1 WHERE b=3 GROUP BY a;
+EXPLAIN SELECT a, SUM(b) FROM v1 WHERE b=3 GROUP BY a;
+
+SELECT a, SUM(b) FROM v1 WHERE a=1 GROUP BY a;
+EXPLAIN SELECT a, SUM(b) FROM v1 WHERE a=1 GROUP BY a;
+
+DROP VIEW v1;
+DROP TABLE t1;
+
 --echo End of 5.0 tests.
 
 #