5.3->5.5 merge

8 files changed, 257 insertions, 0 deletions

diff --git a/mysql-test/t/derived_opt.test b/mysql-test/t/derived_opt.test
index c2f831036e1..b01c479111b 100644
--- a/mysql-test/t/derived_opt.test
+++ b/mysql-test/t/derived_opt.test
@@ -212,5 +212,65 @@ INSERT INTO t1 VALUES ( (SELECT 1 FROM ( SELECT * FROM t1 ) as a) );
 drop table t1;
 set optimizer_switch=@save_optimizer_switch;
 
+--echo #
+--echo # MDEV-3801 Reproducible sub select join crash on 5.3.8 and 5.3.9 
+--echo #
+
+CREATE TABLE t1 (
+  pk int(10) unsigned NOT NULL AUTO_INCREMENT,
+  a char(2) DEFAULT NULL,
+  PRIMARY KEY (pk),
+  KEY a (a)
+) ENGINE=MyISAM;
+INSERT INTO t1 (a)
+VALUES (NULL),(NULL),(NULL),('AB'),(NULL),('CD'),(NULL),(NULL);
+INSERT INTO t1 SELECT NULL, a1.a FROM t1 a1, t1 a2, t1 a3, t1 a4, t1 a5;
+
+CREATE TABLE t2 (
+  pk int(10) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL);
+INSERT INTO t2 SELECT NULL FROM t2 a1, t2 a2, t2 a3, t2 a4, t2 a5;
+
+CREATE TABLE t3 (
+  pk int(10) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY
+) ENGINE=MyISAM;
+INSERT INTO t3 VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL);
+INSERT INTO t3 SELECT NULL FROM t3 a1, t3 a2, t3 a3, t3 a4, t3 a5;
+
+CREATE TABLE t4 (
+  a char(2) NOT NULL DEFAULT '',
+  PRIMARY KEY (a)
+) ENGINE=MyISAM;
+INSERT INTO t4 VALUES ('CD');
+
+set @@tmp_table_size=8192;
+
+--replace_column 9 #
+EXPLAIN
+SELECT * FROM t3 AS tx JOIN t2 AS ty ON (tx.pk = ty.pk)
+WHERE
+  tx.pk IN
+  (SELECT *
+   FROM (SELECT DISTINCT ta.pk
+         FROM t3 AS ta
+              JOIN t2 AS tb ON (ta.pk = tb.pk)
+              JOIN t1 AS tc ON (tb.pk = tc.pk)
+              JOIN t4 AS td ON tc.a = td.a) tu)
+limit 10;
+
+SELECT * FROM t3 AS tX JOIN t2 AS tY ON (tX.pk = tY.pk)
+WHERE
+  tX.pk IN
+  (SELECT *
+   FROM (SELECT DISTINCT tA.pk
+         FROM t3 AS tA
+              JOIN t2 AS tB ON (tA.pk = tB.pk)
+              JOIN t1 AS tC ON (tB.pk = tC.pk)
+              JOIN t4 AS tD ON tC.a = tD.a) tU)
+limit 10;
+
+drop table t1, t2, t3, t4;
+
 # The following command must be the last one the file 
 set optimizer_switch=@exit_optimizer_switch;
diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test
index 1226683ba03..3af531418c5 100644
--- a/mysql-test/t/group_by.test
+++ b/mysql-test/t/group_by.test
@@ -1469,6 +1469,20 @@ WHERE a = (
 GROUP BY 1;
 DROP TABLE t1, t2;
 
+#
+# MDEV-736 LP:1004615 - Unexpected warnings "Encountered illegal value '' when converting to DECIMAL" on a query with aggregate functions and GROUP BY
+#
+
+FLUSH STATUS; # this test case *must* use Aria temp tables
+
+CREATE TABLE t1 (f1 INT, f2 decimal(20,1), f3 blob);
+INSERT INTO t1 values(11,NULL,'blob'),(11,NULL,'blob');
+SELECT f3, MIN(f2) FROM t1 GROUP BY f1 LIMIT 1;
+DROP TABLE t1;
+
+--echo the value below *must* be 1
+show status like 'Created_tmp_disk_tables';
+
 --echo # End of 5.3 tests
 
 --echo #
diff --git a/mysql-test/t/information_schema.test b/mysql-test/t/information_schema.test
index b0f8ddd375f..e95f41f6c8d 100644
--- a/mysql-test/t/information_schema.test
+++ b/mysql-test/t/information_schema.test
@@ -1444,6 +1444,17 @@ SELECT length(CAST(b AS CHAR)) FROM ubig;
 
 DROP TABLE ubig;
 
+#
+# Bug #13889741: HANDLE_FATAL_SIGNAL IN _DB_ENTER_ | HANDLE_FATAL_SIGNAL IN STRNLEN
+#
+select 1 from information_schema.tables where table_schema=repeat('a', 2000);
+grant usage on *.* to mysqltest_1@localhost;
+connect (con1, localhost, mysqltest_1,,);
+connection con1;
+select 1 from information_schema.tables where table_schema=repeat('a', 2000);
+connection default;
+disconnect con1;
+drop user mysqltest_1@localhost;
 
 --echo End of 5.1 tests.
 
diff --git a/mysql-test/t/information_schema_all_engines.test b/mysql-test/t/information_schema_all_engines.test
index a5400e8287a..553367d2b9a 100644
--- a/mysql-test/t/information_schema_all_engines.test
+++ b/mysql-test/t/information_schema_all_engines.test
@@ -15,6 +15,7 @@ show tables;
 # Bug#18925: subqueries with MIN/MAX functions on INFORMATION_SCHEMA 
 #
 
+--sorted_result
 SELECT t.table_name, c1.column_name
   FROM information_schema.tables t
        INNER JOIN
@@ -29,6 +30,7 @@ SELECT t.table_name, c1.column_name
                   c2.table_name = t.table_name AND
                   c2.column_name LIKE '%SCHEMA%'
         ) order by t.table_name;
+--sorted_result
 SELECT t.table_name, c1.column_name
   FROM information_schema.tables t
        INNER JOIN
diff --git a/mysql-test/t/partition.test b/mysql-test/t/partition.test
index ef0d3df6661..038907702d5 100644
--- a/mysql-test/t/partition.test
+++ b/mysql-test/t/partition.test
@@ -2395,6 +2395,14 @@ SELECT 1 FROM t1 WHERE b < SOME
 
 DROP TABLE t1;
 
+--echo
+--echo MDEV-612  Valgrind error in  ha_maria::check_if_incompatible_data
+--echo
+
+CREATE TABLE t1 (a INT, b INT, KEY(a)) ENGINE=Aria PARTITION BY KEY(a) PARTITIONS 2;
+ALTER TABLE t1 ADD KEY (b);
+drop table t1;
+
 --echo End of 5.1 tests
 
 --echo #
diff --git a/mysql-test/t/range_vs_index_merge.test b/mysql-test/t/range_vs_index_merge.test
index 613a7cf5760..fb8fd778559 100755
--- a/mysql-test/t/range_vs_index_merge.test
+++ b/mysql-test/t/range_vs_index_merge.test
@@ -675,6 +675,64 @@ SELECT Name, Country, Population FROM City WHERE
 $cond;
 
 set optimizer_switch=@save_optimizer_switch;
+
+--echo # 
+--echo # Bug mdev-585: range vs index-merge with ORDER BY ... LIMIT n
+--echo # (LP bug #637962)
+--echo # 
+
+DROP INDEX CountryPopulation ON City;
+DROP INDEX CountryName ON City;
+DROP INDEX CityName on City;
+
+CREATE INDEX Name ON City(Name);
+CREATE INDEX Population ON City(Population);
+
+
+--replace_column 9 #
+EXPLAIN 
+SELECT * FROM City
+  WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H'))
+        AND (Population >= 100000 AND Population < 120000);
+FLUSH STATUS;
+SELECT * FROM City
+  WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H'))
+        AND (Population >= 100000 AND Population < 120000);
+SHOW STATUS LIKE 'Handler_read_%';
+
+
+--replace_column 9 #
+EXPLAIN 
+SELECT * FROM City
+  WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H'))
+        AND (Population >= 100000 AND Population < 120000)
+ORDER BY Population LIMIT 5;
+
+FLUSH STATUS;
+SELECT * FROM City
+  WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H'))
+        AND (Population >= 100000 AND Population < 120000)
+ORDER BY Population LIMIT 5;
+SHOW STATUS LIKE 'Handler_read_%';
+
+
+set optimizer_switch='index_merge=off';
+
+--replace_column 9 #
+EXPLAIN 
+SELECT * FROM City
+  WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H'))
+        AND (Population >= 100000 AND Population < 120000)
+ORDER BY Population LIMIT 5;
+
+FLUSH STATUS;
+SELECT * FROM City
+  WHERE ((Name > 'Ca' AND Name < 'Cf') OR (Country > 'E' AND Country < 'H'))
+        AND (Population >= 100000 AND Population < 120000)
+ORDER BY Population LIMIT 5;
+SHOW STATUS LIKE 'Handler_read_%';
+
+set optimizer_switch=@save_optimizer_switch;
           
 
 DROP DATABASE world;
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index 07f0084e7ab..e7cb505b19f 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -5078,6 +5078,82 @@ SELECT col_int_nokey FROM ot
 
 DROP TABLE ot,it1,it2;
 
+--echo #
+--echo # MDEV-746
+--echo # Bug#13651009 WRONG RESULT FROM DERIVED TABLE IF THE SUBQUERY
+--echo # HAS AN EMPTY RESULT
+--echo #
+
+CREATE TABLE t1 (
+  pk int NOT NULL,
+  col_int_nokey int NOT NULL,
+  col_int_key int NOT NULL,
+  col_time_key time NOT NULL,
+  col_varchar_key varchar(1) NOT NULL,
+  col_varchar_nokey varchar(1) NOT NULL,
+  PRIMARY KEY (pk),
+  KEY col_int_key (col_int_key),
+  KEY col_time_key (col_time_key),
+  KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=MyISAM;
+
+CREATE TABLE t2 (
+  pk int NOT NULL AUTO_INCREMENT,
+  col_int_nokey int NOT NULL,
+  col_int_key int NOT NULL,
+  col_time_key time NOT NULL,
+  col_varchar_key varchar(1) NOT NULL,
+  col_varchar_nokey varchar(1) NOT NULL,
+  PRIMARY KEY (pk),
+  KEY col_int_key (col_int_key),
+  KEY col_time_key (col_time_key),
+  KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=MyISAM;
+
+INSERT INTO t2 VALUES (1,4,4,'00:00:00','b','b');
+
+SET @var2:=4, @var3:=8;
+
+--echo
+--echo Testcase without inner subquery
+
+let $subq=
+SELECT @var3:=12, sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR
+      sq4_alias1.col_varchar_key = @var3;
+
+eval EXPLAIN $subq;
+eval $subq;
+SELECT @var3;
+
+# Now as derived table:
+eval EXPLAIN SELECT * FROM ( $subq ) AS alias3;
+eval SELECT * FROM ( $subq ) AS alias3;
+SELECT @var3;
+
+--echo
+--echo Testcase with inner subquery; crashed WL#6095
+SET @var3=8;
+let $subq=
+SELECT sq4_alias1.*
+FROM t1 AS sq4_alias1
+WHERE (sq4_alias1.col_varchar_key , sq4_alias1.col_varchar_nokey)
+      NOT IN
+      (SELECT c_sq1_alias1.col_varchar_key AS c_sq1_field1,
+              c_sq1_alias1.col_varchar_nokey AS c_sq1_field2
+       FROM t2 AS c_sq1_alias1
+       WHERE (c_sq1_alias1.col_int_nokey != @var2
+              OR c_sq1_alias1.pk != @var3));
+
+eval EXPLAIN $subq;
+eval $subq;
+# Now as derived table:
+eval EXPLAIN SELECT * FROM ( $subq ) AS alias3;
+eval SELECT * FROM ( $subq ) AS alias3;
+
+DROP TABLE t1,t2;
+
 --echo End of 5.2 tests
 
 --echo #
diff --git a/mysql-test/t/user_var.test b/mysql-test/t/user_var.test
index f509a1ad0f2..c6c4e4d9d2f 100644
--- a/mysql-test/t/user_var.test
+++ b/mysql-test/t/user_var.test
@@ -424,4 +424,32 @@ DROP TABLE t1;
 SET @bug12408412=1;
 SELECT GROUP_CONCAT(@bug12408412 ORDER BY 1) INTO @bug12408412;
 
+#
+# MDEV-616 LP BUG#1002126
+# Bug #11764371 57196: MORE FUN WITH ASSERTION: !TABLE->FILE ||
+# TABLE->FILE->INITED == HANDLER::
+#
+
+CREATE TABLE t1(a INT);
+INSERT INTO t1 VALUES (0);
+SELECT DISTINCT POW(COUNT(*), @a:=(SELECT 1 FROM t1 LEFT JOIN t1 AS t2 ON @a))
+AS b FROM t1 GROUP BY a;
+SELECT @a;
+DROP TABLE t1;
+CREATE TABLE t1(f1 INT, f2 INT);
+INSERT INTO t1 VALUES (1,2),(2,3),(3,1);
+CREATE TABLE t2(a INT);
+INSERT INTO t2 VALUES (1);
+SET @var=NULL;
+SELECT @var:=(SELECT f2 FROM t2 WHERE @var) FROM t1 GROUP BY f1 ORDER BY f2 DESC
+LIMIT 1;
+SELECT @var;
+DROP TABLE t1, t2;
+
+CREATE TABLE t1(a INT);
+INSERT INTO t1 VALUES (0),(1),(3);
+SELECT DISTINCT POW(COUNT(distinct a), @a:=(SELECT 1 FROM t1 LEFT JOIN t1 AS t2 ON @a limit 1)) AS b FROM t1 GROUP BY a;
+SELECT @a;
+DROP TABLE t1;
+
 --echo End of 5.5 tests