merge with 5.5

11 files changed, 237 insertions, 7 deletions

diff --git a/mysql-test/t/func_str.test b/mysql-test/t/func_str.test
index bdb206be07b..d8d8f4538e1 100644
--- a/mysql-test/t/func_str.test
+++ b/mysql-test/t/func_str.test
@@ -1506,3 +1506,14 @@ SET @@global.max_allowed_packet:= @tmp_max;
 --echo #
 --echo # End of 5.5 tests
 --echo #
+
+#
+# Bug#11765562 58545:
+# EXPORT_SET() CAN BE USED TO MAKE ENTIRE SERVER COMPLETELY UNRESPONSIVE
+#
+SELECT @tmp_max:= @@global.max_allowed_packet;
+SET @@global.max_allowed_packet=1024*1024*1024;
+SELECT @@global.max_allowed_packet;
+SELECT CHAR_LENGTH(EXPORT_SET(1,1,1,REPEAT(1,100000000)));
+SET @@global.max_allowed_packet:= @tmp_max;
+
diff --git a/mysql-test/t/join_cache.test b/mysql-test/t/join_cache.test
index 003ffc8ff19..9b04c264faf 100644
--- a/mysql-test/t/join_cache.test
+++ b/mysql-test/t/join_cache.test
@@ -3587,5 +3587,54 @@ set optimizer_switch=@tmp_optimizer_switch;
 
 DROP TABLE t1,t2,t3;
 
+--echo #
+--echo # Bug #1058071: LEFT JOIN using blobs 
+--echo # (mdev-564)    when join buffer size is small 
+--echo #
+
+CREATE TABLE t1 (
+  col269 decimal(31,10) unsigned DEFAULT NULL,
+  col280 multipoint DEFAULT NULL,
+  col281 tinyint(1) DEFAULT NULL,
+  col282 time NOT NULL,
+  col284 datetime DEFAULT NULL,
+  col286 date DEFAULT NULL,
+  col287 datetime DEFAULT NULL,
+  col288 decimal(30,29) DEFAULT NULL,
+  col291 time DEFAULT NULL,
+  col292 time DEFAULT NULL
+) ENGINE=Aria;
+
+INSERT INTO t1 VALUES
+(0.0,PointFromText('POINT(9 0)'),0,'11:24:05','2013-04-14 21:30:28',NULL,'2011-12-20 06:00:34',9.9,'13:04:39',NULL),
+(0.0,NULL,127,'05:43:12','2012-09-05 06:15:27','2027-01-01','2011-10-29 10:48:29',0.0,'06:24:05','11:33:37'),
+(0.0,NULL,127,'12:54:41','2013-01-12 11:32:58','2011-11-03','2013-01-03 02:00:34',00,'11:54:15','20:19:15'),
+(0.0,PointFromText('POINT(9 0)'),0,'19:48:07','2012-07-16 15:45:25','2012-03-25','2013-09-07 17:21:52',0.5,'17:36:54','21:24:19'),
+(0.0,PointFromText('POINT(9 0)'),0,'03:43:48','2012-09-28 00:00:00','2012-06-26','2011-11-16 05:01:09',00,'01:25:42','19:30:06'),
+(0.0,LineStringFromText('LINESTRING(0 0,9 9,0 0,9 0,0 0)'),127,'11:33:21','2012-03-31 10:29:22','2012-10-10','2012-04-21 19:21:06',NULL,'05:13:22','09:48:34'),
+(NULL,PointFromText('POINT(9 0)'),127,'00:00:00','0000-00-00','2012-04-04 21:26:12','2013-03-04',0.0,'12:54:30',NULL),
+(NULL,PointFromText('POINT(9 0)'),1,'00:00:00','2013-05-01 22:37:49','2013-06-26','2012-09-22 17:31:03',0.0,'08:09:57','11:15:36');
+
+CREATE TABLE t2 (b int) ENGINE=Aria;
+INSERT INTO t2 VALUES (NULL);
+CREATE TABLE t3 (c int) ENGINE=Aria;
+INSERT INTO t3 VALUES (NULL);
+
+set @tmp_optimizer_switch=@@optimizer_switch;
+set optimizer_switch = 'outer_join_with_cache=on,join_cache_incremental=on';
+set join_buffer_size=128;
+
+EXPLAIN
+SELECT 1 AS c FROM t1 NATURAL LEFT JOIN t2 LEFT OUTER JOIN t3 ON 1
+  GROUP BY elt(t1.col282,1,t1.col280);
+
+SELECT 1 AS c FROM t1 NATURAL LEFT JOIN t2 LEFT OUTER JOIN t3 ON 1
+  GROUP BY elt(t1.col282,1,t1.col280);
+
+set join_buffer_size=default;
+set optimizer_switch=@tmp_optimizer_switch;
+
+DROP table t1,t2,t3;
+
 # this must be the last command in the file
 set @@optimizer_switch=@save_optimizer_switch;
diff --git a/mysql-test/t/mysqlshow.test b/mysql-test/t/mysqlshow.test
index 105e03c841b..045329fd2e0 100644
--- a/mysql-test/t/mysqlshow.test
+++ b/mysql-test/t/mysqlshow.test
@@ -1,7 +1,5 @@
 # Can't run test of external client with embedded server
 -- source include/not_embedded.inc
-# Test lists tables in Information_schema, and InnoDB adds some
--- source include/have_innodb.inc
 # Don't test when thread_pool active
 --source include/not_threadpool.inc
 
@@ -31,3 +29,14 @@ select "---- -v -v -t ------" as "";
 DROP TABLE t1, t2;
 
 --echo End of 5.0 tests
+
+# because of lp:1066512 this test shows xtradb I_S plugins, even when
+# xtradb is supposed to be disabled
+
+#
+# Bug #19147: mysqlshow INFORMATION_SCHEMA does not work
+#
+# --exec $MYSQL_SHOW information_schema
+# --exec $MYSQL_SHOW INFORMATION_SCHEMA
+# --exec $MYSQL_SHOW inf_rmation_schema
+
diff --git a/mysql-test/t/openssl_1.test b/mysql-test/t/openssl_1.test
index 9ef68d4018d..61e0dcc7197 100644
--- a/mysql-test/t/openssl_1.test
+++ b/mysql-test/t/openssl_1.test
@@ -74,25 +74,31 @@ drop table t1;
 #
 --exec echo "this query should not execute;" > $MYSQLTEST_VARDIR/tmp/test.sql
 # Handle that openssl gives different error messages from YaSSL.
---replace_regex /error:0000000[15]:lib\(0\):func\(0\):(reason\(1\)|DH lib)/ASN: bad other signature confirmation/
+#--replace_regex /error:00000001:lib\(0\):func\(0\):reason\(1\)/ASN: bad other signature confirmation/
+--replace_regex /2026 SSL connection error.*/2026 SSL connection error: xxxx/
 --error 1
 --exec $MYSQL_TEST --ssl-ca=$MYSQL_TEST_DIR/std_data/untrusted-cacert.pem --max-connect-retries=1 < $MYSQLTEST_VARDIR/tmp/test.sql 2>&1
+--echo
 
 #
 # Test that we can't open connection to server if we are using
 # a blank ca
 #
---replace_regex /error:0000000[15]:lib\(0\):func\(0\):(reason\(1\)|DH lib)/ASN: bad other signature confirmation/
+#--replace_regex /error:00000001:lib\(0\):func\(0\):reason\(1\)/ASN: bad other signature confirmation/
+--replace_regex /2026 SSL connection error.*/2026 SSL connection error: xxxx/
 --error 1
 --exec $MYSQL_TEST --ssl-ca= --max-connect-retries=1 < $MYSQLTEST_VARDIR/tmp/test.sql 2>&1
+--echo
 
 #
 # Test that we can't open connection to server if we are using
 # a nonexistent ca file
 #
---replace_regex /error:0000000[15]:lib\(0\):func\(0\):(reason\(1\)|DH lib)/ASN: bad other signature confirmation/
+#--replace_regex /error:00000001:lib\(0\):func\(0\):reason\(1\)/ASN: bad other signature confirmation/
+--replace_regex /2026 SSL connection error.*/2026 SSL connection error: xxxx/
 --error 1
 --exec $MYSQL_TEST --ssl-ca=nonexisting_file.pem --max-connect-retries=1 < $MYSQLTEST_VARDIR/tmp/test.sql 2>&1
+--echo
 
 #
 # Test that we can't open connection to server if we are using
diff --git a/mysql-test/t/order_fill_sortbuf-master.opt b/mysql-test/t/order_fill_sortbuf-master.opt
deleted file mode 100644
index 7b6ade99226..00000000000
--- a/mysql-test/t/order_fill_sortbuf-master.opt
+++ /dev/null
@@ -1 +0,0 @@
---sort_buffer=32804
diff --git a/mysql-test/t/order_fill_sortbuf.test b/mysql-test/t/order_fill_sortbuf.test
index 7a8779b6e55..33c09e34b91 100644
--- a/mysql-test/t/order_fill_sortbuf.test
+++ b/mysql-test/t/order_fill_sortbuf.test
@@ -7,6 +7,8 @@
 drop table if exists t1,t2;
 --enable_warnings
 
+set @@sort_buffer_size=32804;
+
 CREATE TABLE `t1` (
   `id` int(11) NOT NULL default '0',
   `id2` int(11) NOT NULL default '0',
diff --git a/mysql-test/t/ps_1general.test b/mysql-test/t/ps_1general.test
index b9e84d8d7df..812b1b5ff94 100644
--- a/mysql-test/t/ps_1general.test
+++ b/mysql-test/t/ps_1general.test
@@ -827,6 +827,16 @@ execute stmt1 ;
 
 drop table t1, t5, t9;
 
+--echo #
+--echo # testcase for bug#11765413 - Crash with dependent subquery and
+--echo #                             prepared statement
+create table t1 (c1 int);
+insert into t1 values (1);
+prepare stmt1 from "select 1 from t1 where 1=(select 1 from t1 having c1)";  
+execute stmt1;
+drop prepare stmt1;
+drop table t1;
+
 ##### RULES OF THUMB TO PRESERVE THE SYSTEMATICS OF THE PS TEST CASES #####
 #
 # 0. You  don't have the time to 
diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test
index 618d6eb3ed4..44eaf7130a7 100644
--- a/mysql-test/t/select.test
+++ b/mysql-test/t/select.test
@@ -4174,7 +4174,7 @@ INSERT INTO t5 VALUES (20),(5);
 CREATE TABLE t6(f1 int);
 INSERT INTO t6 VALUES (9),(7);
 
-SET SESSION join_buffer_size = 2048;
+SET SESSION join_buffer_size = 2176;
 
 EXPLAIN
 SELECT STRAIGHT_JOIN * FROM t2, (t1 LEFT JOIN (t3,t4) ON t1.f1 = t4.f1), t5, t6;
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index 9f35ecc43f1..07f0084e7ab 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -5597,6 +5597,22 @@ set optimizer_switch=@mdev367_optimizer_switch;
 DROP TABLE t1;
 
 --echo #
+--echo # MDEV-521 single value subselect transformation problem
+--echo #
+CREATE TABLE t1 (f1 char(2), PRIMARY KEY (f1)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES ('u1'),('u2');
+
+SELECT a.* FROM t1 a WHERE ( SELECT EXISTS ( SELECT 1 FROM t1 b WHERE b.f1 = a.f1 ) );
+FLUSH TABLES;
+SELECT a.* FROM t1 a WHERE ( SELECT EXISTS ( SELECT 1 FROM t1 b WHERE b.f1 = a.f1 ) );
+
+# Cleanup
+DROP TABLE t1;
+
+--echo # return optimizer switch changed in the beginning of this test
+set optimizer_switch=@subselect_tmp;
+
+--echo #
 --echo # lp:944706 Query with impossible or constant subquery in WHERE or HAVING is not
 --echo # precomputed and thus not part of optimization
 --echo #
@@ -5757,6 +5773,29 @@ EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP B
 DROP TABLE t1,t2;
 
 --echo #
+--echo # MDEV-435: Expensive subqueries may be evaluated during optimization in merge_key_fields
+--echo #
+
+CREATE TABLE t1 (a INT, KEY(a)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (8),(0);
+
+CREATE TABLE t2 (b INT, c VARCHAR(1)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (4,'j'),(6,'v');
+
+CREATE TABLE t3 (d VARCHAR(1)) ENGINE=MyISAM;
+INSERT INTO t3 VALUES ('b'),('c');
+
+EXPLAIN
+SELECT * FROM t1
+WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10;
+
+SELECT * FROM t1
+WHERE a = (SELECT MAX(b) FROM t2 WHERE c IN (SELECT MAX(d) FROM t3)) OR a = 10;
+
+drop table t1, t2, t3;
+
+
+--echo #
 --echo # MDEV-405: Server crashes in test_if_skip_sort_order on EXPLAIN with GROUP BY and HAVING in EXISTS subquery
 --echo #
 CREATE TABLE t1 (a INT, KEY(a));
diff --git a/mysql-test/t/subselect2.test b/mysql-test/t/subselect2.test
index 8d2939bdb53..75cf842fbdb 100644
--- a/mysql-test/t/subselect2.test
+++ b/mysql-test/t/subselect2.test
@@ -203,5 +203,24 @@ SET optimizer_switch=@tmp_optimizer_switch;
 DROP VIEW v1;
 DROP TABLE t1,t2,t3;
 
+--echo #
+--echo # MDEV-567: Wrong result from a query with correlated subquery if ICP is allowed
+--echo #
+CREATE TABLE t1 (a int, b int, INDEX idx(a));
+INSERT INTO t1 VALUES (9,0), (7,1), (1,9), (7,3), (2,1);
+
+CREATE TABLE t2 (a int, b int, INDEX idx(a));
+INSERT INTO t2 VALUES (2,1), (6,4), (7,6), (9,4);
+
+CREATE TABLE t3 (a int, b int);
+INSERT INTO t3 VALUES (1,0), (1,1), (1,3);
+
+SELECT * FROM t3
+  WHERE a = (SELECT COUNT(DISTINCT t2.b) FROM t1, t2
+                WHERE t1.a = t2.a AND t2.a BETWEEN 7 AND 9
+                      AND t3.b = t1.b
+              GROUP BY t1.b);
+DROP TABLE t1, t2, t3;
+
 set optimizer_switch=@subselect2_test_tmp;
 
diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test
index c4881f7df3f..817da816c48 100644
--- a/mysql-test/t/view.test
+++ b/mysql-test/t/view.test
@@ -4381,6 +4381,46 @@ SELECT * FROM v1, t2
 DROP VIEW v1;
 DROP TABLE t1,t2,t3,t4;
 
+--echo #
+--echo # LP bug #823237: dependent subquery with LEFT JOIN
+--echo #                 referencing view in WHERE
+--echo # (duplicate of LP bug #823189)
+--echo # 
+
+CREATE TABLE t1 (a int);
+
+CREATE TABLE t2 ( b int, d int, e int);
+INSERT INTO t2 VALUES (7,8,0);
+
+CREATE TABLE t3 ( c int);
+INSERT INTO t3 VALUES (0);
+
+CREATE TABLE t4 (a int, b int, c int);
+INSERT INTO t4 VALUES (93,1,0), (95,NULL,0);
+
+CREATE VIEW v4 AS SELECT * FROM t4;
+
+EXPLAIN EXTENDED
+SELECT * FROM t3 , t4 
+  WHERE t4.c <= (SELECT t2.e FROM t2 LEFT JOIN t1 ON ( t1.a = t2.d )
+                   WHERE t2.b > t4.b);
+SELECT * FROM t3 , t4 
+  WHERE t4.c <= (SELECT t2.e FROM t2 LEFT JOIN t1 ON ( t1.a = t2.d )
+                   WHERE t2.b > t4.b);
+
+
+EXPLAIN EXTENDED
+SELECT * FROM t3, v4 
+  WHERE v4.c <= (SELECT t2.e FROM t2 LEFT JOIN t1 ON ( t1.a = t2.d )
+                   WHERE t2.b > v4.b);
+
+SELECT * FROM t3, v4 
+  WHERE v4.c <= (SELECT t2.e FROM t2 LEFT JOIN t1 ON ( t1.a = t2.d )
+                   WHERE t2.b > v4.b);
+
+DROP VIEW v4;
+DROP TABLE t1,t2,t3,t4;
+
 #
 # Bug#9801 (Views: imperfect error message)
 #
@@ -4661,6 +4701,52 @@ create algorithm=MERGE view v2 as select 2 as id, id is null as bbb, id as iddqd
 select t1.*, v2.* from t1 left join v2 on t1.id = v2.id;
 drop view v2;
 drop table t1,t2;
+
+--echo #
+--echo # MDEV-589 (LP BUG#1007647) :
+--echo # Assertion `vcol_table == 0 || vcol_table == table' failed in
+--echo # fill_record(THD*, List<Item>&, List<Item>&, bool)
+--echo #
+CREATE TABLE t1 (f1 INT, f2 INT);
+CREATE TABLE t2 (f1 INT, f2 INT);
+CREATE ALGORITHM=MERGE VIEW v1 AS SELECT a1.f1, a2.f2 FROM t1 AS a1, t1 AS a2;
+CREATE ALGORITHM=MERGE VIEW v2 AS SELECT * FROM v1;
+CREATE ALGORITHM=MERGE VIEW v3 AS SELECT a1.f1, a2.f2 FROM t1 AS a1, t2 AS a2;
+CREATE ALGORITHM=MERGE VIEW v4 AS SELECT * FROM v3;
+--error ER_VIEW_MULTIUPDATE
+INSERT INTO v3 (f1, f2) VALUES (1, 2);
+--error ER_VIEW_MULTIUPDATE
+INSERT INTO v1 (f1, f2) VALUES (1, 2);
+--error ER_VIEW_MULTIUPDATE
+INSERT INTO v4 (f1, f2) VALUES (1, 2);
+--error ER_VIEW_MULTIUPDATE
+INSERT INTO v2 (f1, f2) VALUES (1, 2);
+drop view v4,v3,v2,v1;
+drop table t1,t2;
+
+--echo #
+--echo # MDEV-3799 fix of above bugfix (MDEV-589) 
+--echo # Wrong result (NULLs instead of real values) with RIGHT JOIN
+--echo # in a FROM subquery and derived_merge=on
+--echo #
+
+CREATE TABLE t1 (f1 INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (4),(6);
+
+CREATE TABLE t2 (f2 INT) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (7),(8);
+
+SELECT * FROM (  
+  SELECT * FROM t1 RIGHT JOIN t2 ON f1 = f2
+) AS alias;
+
+SELECT * FROM (  
+  SELECT * FROM t2 LEFT JOIN t1 ON f1 = f2
+) AS alias;
+
+drop tables t1,t2;
+
+
 --echo # -----------------------------------------------------------------
 --echo # -- End of 5.3 tests.
 --echo # -----------------------------------------------------------------