5.5 merge

17 files changed, 203 insertions, 47 deletions

diff --git a/mysql-test/r/ctype_binary.result b/mysql-test/r/ctype_binary.result
index 9b38776e1ec..3dfcaf18120 100644
--- a/mysql-test/r/ctype_binary.result
+++ b/mysql-test/r/ctype_binary.result
@@ -740,7 +740,7 @@ create table t1 as select concat(uncompressed_length('')) as c1;
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
-  `c1` varbinary(10) NOT NULL DEFAULT ''
+  `c1` varbinary(10) DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 drop table t1;
 create table t1 as select concat(connection_id()) as c1;
diff --git a/mysql-test/r/ctype_cp1251.result b/mysql-test/r/ctype_cp1251.result
index b6114ba6f59..7030a77b6b2 100644
--- a/mysql-test/r/ctype_cp1251.result
+++ b/mysql-test/r/ctype_cp1251.result
@@ -1132,7 +1132,7 @@ create table t1 as select concat(uncompressed_length('')) as c1;
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
-  `c1` varchar(10) CHARACTER SET cp1251 NOT NULL DEFAULT ''
+  `c1` varchar(10) CHARACTER SET cp1251 DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 drop table t1;
 create table t1 as select concat(connection_id()) as c1;
diff --git a/mysql-test/r/ctype_latin1.result b/mysql-test/r/ctype_latin1.result
index 630288ebcb7..b1507a3222b 100644
--- a/mysql-test/r/ctype_latin1.result
+++ b/mysql-test/r/ctype_latin1.result
@@ -1212,7 +1212,7 @@ create table t1 as select concat(uncompressed_length('')) as c1;
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
-  `c1` varchar(10) NOT NULL DEFAULT ''
+  `c1` varchar(10) DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 drop table t1;
 create table t1 as select concat(connection_id()) as c1;
diff --git a/mysql-test/r/ctype_ucs.result b/mysql-test/r/ctype_ucs.result
index 5ce58fd7617..6e5ea46fde1 100644
--- a/mysql-test/r/ctype_ucs.result
+++ b/mysql-test/r/ctype_ucs.result
@@ -2212,7 +2212,7 @@ create table t1 as select concat(uncompressed_length('')) as c1;
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
-  `c1` varchar(10) CHARACTER SET ucs2 NOT NULL DEFAULT ''
+  `c1` varchar(10) CHARACTER SET ucs2 DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 drop table t1;
 create table t1 as select concat(connection_id()) as c1;
diff --git a/mysql-test/r/ctype_utf8.result b/mysql-test/r/ctype_utf8.result
index 4d558b2902c..ba96712653d 100644
--- a/mysql-test/r/ctype_utf8.result
+++ b/mysql-test/r/ctype_utf8.result
@@ -3089,7 +3089,7 @@ create table t1 as select concat(uncompressed_length('')) as c1;
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
-  `c1` varchar(10) CHARACTER SET utf8 NOT NULL DEFAULT ''
+  `c1` varchar(10) CHARACTER SET utf8 DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 drop table t1;
 create table t1 as select concat(connection_id()) as c1;
diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result
index 80d2d64dced..64501ab5b76 100644
--- a/mysql-test/r/derived_view.result
+++ b/mysql-test/r/derived_view.result
@@ -2366,6 +2366,46 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 3	DEPENDENT SUBQUERY	pi	ref	gallery_id	gallery_id	4	test.gal.id	4	Using temporary; Using filesort
 drop table galleries, pictures;
 #
+# MDEV-5740: Assertion 
+#`!derived->first_select()->exclude_from_table_unique_test ||
+#derived->outer_select()-> exclude_from_table_unique_test'
+#failed on 2nd execution of PS with derived_merge
+#
+set @save_optimizer_switch5740=@@optimizer_switch;
+SET optimizer_switch = 'derived_merge=on';
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (b INT);
+INSERT INTO t2 VALUES (3),(4);
+PREPARE stmt FROM '
+  INSERT INTO t1 SELECT * FROM t2 UNION SELECT * FROM (SELECT * FROM t1) AS sq  
+';
+EXECUTE stmt;
+select * from t1;
+a
+1
+2
+3
+4
+1
+2
+EXECUTE stmt;
+select * from t1;
+a
+1
+2
+3
+4
+1
+2
+3
+4
+1
+2
+deallocate prepare stmt;
+drop table t1,t2;
+set optimizer_switch=@save_optimizer_switch5740;
+#
 # end of 5.3 tests
 #
 set optimizer_switch=@exit_optimizer_switch;
diff --git a/mysql-test/r/failed_auth_unixsocket.result b/mysql-test/r/failed_auth_unixsocket.result
new file mode 100644
index 00000000000..3cf58ca5bec
--- /dev/null
+++ b/mysql-test/r/failed_auth_unixsocket.result
@@ -0,0 +1,12 @@
+update mysql.user set plugin='unix_socket';
+flush privileges;
+connect(localhost,USER,,test,MASTER_PORT,MASTER_SOCKET);
+ERROR HY000: Plugin 'unix_socket' is not loaded
+ERROR HY000: Plugin 'unix_socket' is not loaded
+install plugin unix_socket soname 'auth_socket.so';
+connect(localhost,USER,,test,MASTER_PORT,MASTER_SOCKET);
+ERROR 28000: Access denied for user 'USER'@'localhost'
+ERROR 28000: Access denied for user 'USER'@'localhost'
+update mysql.user set plugin='';
+flush privileges;
+uninstall plugin unix_socket;
diff --git a/mysql-test/r/func_compress.result b/mysql-test/r/func_compress.result
index 011dec4d555..22b75de8c60 100644
--- a/mysql-test/r/func_compress.result
+++ b/mysql-test/r/func_compress.result
@@ -127,3 +127,23 @@ NULL	825307441
 EXPLAIN EXTENDED SELECT * FROM (SELECT UNCOMPRESSED_LENGTH(c1) FROM t1) AS s;
 DROP TABLE t1;
 End of 5.0 tests
+#
+# Start of 5.3 tests
+#
+#
+# MDEV-5783 Assertion `0' failed in make_sortkey(SORTPARAM*, uchar*, uchar*) on ORDER BY HEX(UNCOMPRESSED_LENGTH(pk))
+#
+CREATE TABLE t1 (pk INT PRIMARY KEY);
+INSERT INTO t1 VALUES (1),(2);
+SELECT UNCOMPRESSED_LENGTH(pk) FROM t1;
+UNCOMPRESSED_LENGTH(pk)
+NULL
+NULL
+Warnings:
+Warning	1259	ZLIB: Input data corrupted
+Warning	1259	ZLIB: Input data corrupted
+SELECT * FROM t1 ORDER BY HEX(UNCOMPRESSED_LENGTH(pk));
+DROP TABLE t1;
+#
+# End of 5.3 tests
+#
diff --git a/mysql-test/r/func_misc.result b/mysql-test/r/func_misc.result
index 1d1300a96db..0c4d22f89b7 100644
--- a/mysql-test/r/func_misc.result
+++ b/mysql-test/r/func_misc.result
@@ -272,6 +272,22 @@ SELECT NAME_CONST('a', -(1)) OR 1;
 NAME_CONST('a', -(1)) OR 1
 1
 #
+#MDEV-5446: Assertion `!table || (!table->read_set ||
+#bitmap_is_set(table->read_set, field_index))' fails on
+#EXPLAIN EXTENDED with VALUES function
+#
+CREATE TABLE t1 (a INT, b INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1,10);
+CREATE VIEW v1 AS SELECT * FROM t1;
+EXPLAIN EXTENDED SELECT VALUES(b) FROM v1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	100.00	
+Warnings:
+Note	1003	select values(10) AS `VALUES(b)` from dual
+drop view v1;
+drop table t1;
+End of 5.3 tests
+# 
 # Bug #52165: Assertion failed: file .\dtoa.c, line 465
 # 
 CREATE TABLE t1 (a SET('a'), b INT);
diff --git a/mysql-test/r/locked_temporary-5955.result b/mysql-test/r/locked_temporary-5955.result
new file mode 100644
index 00000000000..8999bdd39c4
--- /dev/null
+++ b/mysql-test/r/locked_temporary-5955.result
@@ -0,0 +1,2 @@
+CREATE TEMPORARY TABLE tmp (i INT) ENGINE=InnoDB;
+LOCK TABLES tmp AS p WRITE;
diff --git a/mysql-test/r/partition.result b/mysql-test/r/partition.result
index 5ea037df759..b8011656415 100644
--- a/mysql-test/r/partition.result
+++ b/mysql-test/r/partition.result
@@ -2535,45 +2535,6 @@ i
 4
 DROP TABLE t1;
 #
-# MDEV-5177: ha_partition and innodb index intersection produce fewer rows (MySQL Bug#70703)
-#
-create table t1 (
-a int not null,
-b int not null,
-pk int not null,
-primary key (pk),
-key(a),
-key(b)
-) partition by hash(pk) partitions 10;
-insert into t1 values (1,2,4);
-insert into t1 values (1,0,17);
-insert into t1 values (1,2,25);
-insert into t1 values (10,20,122);
-insert into t1 values (10,20,123);
-create table t2 (a int);
-insert into t2 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
-insert into t1 select 1,2, 200 + A.a + 10*B.a + 100*C.a from t2 A, t2 B, t2 C;
-insert into t1 select 10+A.a + 10*B.a + 100*C.a + 1000*D.a,
-10+A.a + 10*B.a + 100*C.a  + 1000*D.a, 
-2000 + A.a + 10*B.a + 100*C.a + 1000*D.a
-from t2 A, t2 B, t2 C ,t2 D;
-explain select * from t1 where a=1 and b=2 and  pk between 1 and 999999 ;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ref	PRIMARY,a,b	b	4	const	982	Using where
-create temporary table t3 as
-select * from t1 where a=1 and b=2 and  pk between 1 and 999 ;
-select count(*) from t3;
-count(*)
-802
-drop table t3;
-create temporary table t3 as
-select * from t1 ignore index(a,b)  where a=1 and b=2 and  pk between 1 and 999 ;
-select count(*) from t3;
-count(*)
-802
-drop table t3;
-drop table t1,t2;
-#
 # MDEV-5555: Incorrect index_merge on BTREE indices
 #
 CREATE TABLE t1 (
diff --git a/mysql-test/r/partition_innodb.result b/mysql-test/r/partition_innodb.result
index 9a837cb4b5b..07364bd1370 100644
--- a/mysql-test/r/partition_innodb.result
+++ b/mysql-test/r/partition_innodb.result
@@ -654,4 +654,43 @@ col1	col2	col3
 1	2	2013-03-11 16:33:04
 1	2	2013-03-11 16:33:24
 DROP TABLE t1;
+#
+# MDEV-5177: ha_partition and innodb index intersection produce fewer rows (MySQL Bug#70703)
+#
+create table t1 (
+a int not null,
+b int not null,
+pk int not null,
+primary key (pk),
+key(a),
+key(b)
+) engine=innodb partition by hash(pk) partitions 10;
+insert into t1 values (1,2,4);
+insert into t1 values (1,0,17);
+insert into t1 values (1,2,25);
+insert into t1 values (10,20,122);
+insert into t1 values (10,20,123);
+create table t2 (a int);
+insert into t2 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+insert into t1 select 1,2, 200 + A.a + 10*B.a + 100*C.a from t2 A, t2 B, t2 C;
+insert into t1 select 10+A.a + 10*B.a + 100*C.a + 1000*D.a,
+10+A.a + 10*B.a + 100*C.a  + 1000*D.a, 
+2000 + A.a + 10*B.a + 100*C.a + 1000*D.a
+from t2 A, t2 B, t2 C ,t2 D;
+explain select * from t1 where a=1 and b=2 and  pk between 1 and 999999 ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index_merge	PRIMARY,a,b	b,a	4,4	NULL	#	Using intersect(b,a); Using where; Using index
+create temporary table t3 as
+select * from t1 where a=1 and b=2 and  pk between 1 and 999 ;
+select count(*) from t3;
+count(*)
+802
+drop table t3;
+create temporary table t3 as
+select * from t1 ignore index(a,b)  where a=1 and b=2 and  pk between 1 and 999 ;
+select count(*) from t3;
+count(*)
+802
+drop table t3;
+drop table t1,t2;
 set global default_storage_engine=default;
diff --git a/mysql-test/r/partition_order.result b/mysql-test/r/partition_order.result
index 06c1b63a382..cecfc90eefb 100644
--- a/mysql-test/r/partition_order.result
+++ b/mysql-test/r/partition_order.result
@@ -734,8 +734,8 @@ a	b
 7	1
 35	2
 3	3
-2	4
 30	4
+2	4
 4	5
 6	6
 select * from t1 force index (b) where b < 10 ORDER BY b;
@@ -744,16 +744,16 @@ a	b
 7	1
 35	2
 3	3
-2	4
 30	4
+2	4
 4	5
 6	6
 select * from t1 force index (b) where b < 10 ORDER BY b DESC;
 a	b
 6	6
 4	5
-30	4
 2	4
+30	4
 3	3
 35	2
 7	1
diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result
index 66d3fa6214a..a909230121d 100644
--- a/mysql-test/r/subselect4.result
+++ b/mysql-test/r/subselect4.result
@@ -2331,6 +2331,28 @@ id	a2	a3	id	a2	a3
 DROP VIEW v2;
 DROP TABLE t1,t2;
 #
+# MDEV-5686: degenerate disjunct in NOT IN subquery
+#
+CREATE TABLE t1 (a int, b int, c varchar(3)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1,1,'CAN'),(2,2,'AUS');
+CREATE TABLE t2 (f int) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (3);
+EXPLAIN EXTENDED
+SELECT * FROM t2 
+WHERE f NOT IN (SELECT b FROM t1
+WHERE 0 OR (c IN ('USA') OR c NOT IN ('USA')) AND a = b);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	100.00	
+2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+Warnings:
+Note	1003	select 3 AS `f` from dual where (not(<expr_cache><3>(<in_optimizer>(3,<exists>(select `test`.`t1`.`b` from `test`.`t1` where (((`test`.`t1`.`c` = 'USA') or (`test`.`t1`.`c` <> 'USA')) and trigcond(((<cache>(3) = `test`.`t1`.`b`) or isnull(`test`.`t1`.`b`))) and (`test`.`t1`.`b` = `test`.`t1`.`a`)) having trigcond(<is_not_null_test>(`test`.`t1`.`b`)))))))
+SELECT * FROM t2 
+WHERE f NOT IN (SELECT b FROM t1
+WHERE 0 OR (c IN ('USA') OR c NOT IN ('USA')) AND a = b);
+f
+3
+DROP TABLE t1,t2;
+#
 # MDEV-3899  Valgrind warnings (blocks are definitely lost) in filesort on IN subquery with SUM and DISTINCT
 #
 CREATE TABLE t1 (a INT) ENGINE=MyISAM;
diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result
index cd78d9f93fb..6cc627ad16c 100644
--- a/mysql-test/r/subselect_mat.result
+++ b/mysql-test/r/subselect_mat.result
@@ -2055,6 +2055,20 @@ EXECUTE stmt;
 a
 DROP TABLE t1, t2;
 DROP VIEW v2;
+#
+# MDEV-5811: Server crashes in best_access_path with materialization+semijoin and big_tables=ON
+#
+SET @tmp_mdev5811= @@big_tables;
+SET big_tables = ON;
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (b INT);
+INSERT INTO t2 VALUES (3),(4);
+SELECT * FROM t1 AS t1_1, t1 AS t1_2 
+WHERE ( t1_1.a, t1_2.a ) IN ( SELECT MAX(b), MIN(b) FROM t2 );
+a	a
+DROP TABLE t1,t2;
+SET big_tables=@tmp_mdev5811;
 # End of 5.3 tests
 #
 # MDEV-5056: Wrong result (extra rows) with materialization+semijoin, IN subqueries
diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result
index 46972a79a05..95dfc34777b 100644
--- a/mysql-test/r/subselect_sj_mat.result
+++ b/mysql-test/r/subselect_sj_mat.result
@@ -2095,6 +2095,20 @@ EXECUTE stmt;
 a
 DROP TABLE t1, t2;
 DROP VIEW v2;
+#
+# MDEV-5811: Server crashes in best_access_path with materialization+semijoin and big_tables=ON
+#
+SET @tmp_mdev5811= @@big_tables;
+SET big_tables = ON;
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (b INT);
+INSERT INTO t2 VALUES (3),(4);
+SELECT * FROM t1 AS t1_1, t1 AS t1_2 
+WHERE ( t1_1.a, t1_2.a ) IN ( SELECT MAX(b), MIN(b) FROM t2 );
+a	a
+DROP TABLE t1,t2;
+SET big_tables=@tmp_mdev5811;
 # End of 5.3 tests
 #
 # MDEV-5056: Wrong result (extra rows) with materialization+semijoin, IN subqueries
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index 3817df4f440..a382cb86869 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -5000,6 +5000,22 @@ v1_field1
 deallocate prepare my_stmt;
 DROP VIEW v1,v2;
 DROP TABLE t1,t2,t3,t4;
+#
+#MDEV-5717: Server crash with insert statement containing DEFAULT into
+#view
+#
+CREATE TABLE t1 (
+`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+`test` tinyint(3) unsigned NOT NULL DEFAULT '0',
+PRIMARY KEY (`id`)
+);
+CREATE VIEW v1 AS (select  t1.id AS id,  t1.test AS test from t1);
+INSERT INTO v1 SET test = DEFAULT;
+select * from v1;
+id	test
+1	0
+drop view v1;
+drop table t1;
 # -----------------------------------------------------------------
 # -- End of 5.3 tests.
 # -----------------------------------------------------------------