Merge branch '5.5' into 10.0

12 files changed, 442 insertions, 61 deletions

diff --git a/mysql-test/t/func_if.test b/mysql-test/t/func_if.test
index 2b89a618aa6..8fdba77db9b 100644
--- a/mysql-test/t/func_if.test
+++ b/mysql-test/t/func_if.test
@@ -206,6 +206,20 @@ SELECT if(1, NULL, (SELECT min('hello')));
 
 --echo End of 5.2 tests
 
+--echo #
+--echo # MDEV-8663: IF Statement returns multiple values erroneously
+--echo # (or Assertion `!null_value' failed in Item::send(Protocol*, String*)
+--echo #
+CREATE TABLE `t1` (
+`datas` VARCHAR(25) NOT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+INSERT INTO `t1` VALUES ('1,2'), ('2,3'), ('3,4');
+
+SELECT IF(FIND_IN_SET('1', `datas`), 1.5, IF(FIND_IN_SET('2', `datas`), 2, NULL)) AS `First`, '1' AS `Second`, '2' AS `Third` FROM `t1`;
+
+drop table t1;
+
 --disable_query_log
 # Restore timezone to default
 set time_zone= @@global.time_zone;
diff --git a/mysql-test/t/func_misc.test b/mysql-test/t/func_misc.test
index 916a952fa3a..26e379299fc 100644
--- a/mysql-test/t/func_misc.test
+++ b/mysql-test/t/func_misc.test
@@ -398,6 +398,209 @@ SELECT GET_LOCK('ul1', NULL);
 SELECT GET_LOCK('ul1', -1);
 
 --echo #
+--echo # MDEV-8624 MariaDB hangs on query with many logical condition
+--echo #
+CREATE TABLE `t1` (
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  `submitdate` datetime DEFAULT NULL,
+  `lastpage` int(11) DEFAULT NULL,
+  `startlanguage` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
+  `token` varchar(36) COLLATE utf8_unicode_ci DEFAULT NULL,
+  `datestamp` datetime NOT NULL,
+  `startdate` datetime NOT NULL,
+  `ipaddr` text COLLATE utf8_unicode_ci,
+  `refurl` text COLLATE utf8_unicode_ci,
+  `57813X540X1723` text COLLATE utf8_unicode_ci,
+  `57813X540X1724` text COLLATE utf8_unicode_ci,
+  `57813X540X1725` text COLLATE utf8_unicode_ci,
+  `57813X540X1726` double DEFAULT NULL,
+  `57813X540X1909` double DEFAULT NULL,
+  `57813X541X17271` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL,
+  `57813X541X17272` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL,
+  `57813X541X17273` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL,
+  `57813X541X17274` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL,
+  `57813X541X17275` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL,
+  `57813X541X17276` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL,
+  `57813X541X17281` text COLLATE utf8_unicode_ci,
+  `57813X541X17282` text COLLATE utf8_unicode_ci,
+  `57813X541X17283` text COLLATE utf8_unicode_ci,
+  `57813X541X17284` text COLLATE utf8_unicode_ci,
+  `57813X541X17285` text COLLATE utf8_unicode_ci,
+  `57813X541X17286` text COLLATE utf8_unicode_ci,
+  `57813X542X18131` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL,
+  `57813X542X18132` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL,
+  `57813X542X18133` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL,
+  `57813X542X18134` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL,
+  `57813X542X18135` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL,
+  `57813X542X18136` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL,
+  `57813X542X18137` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL,
+  `57813X542X18138` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL,
+  `57813X542X18139` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL,
+  `57813X542X18141` text COLLATE utf8_unicode_ci,
+  `57813X542X18142` text COLLATE utf8_unicode_ci,
+  `57813X542X18143` text COLLATE utf8_unicode_ci,
+  `57813X542X18144` text COLLATE utf8_unicode_ci,
+  `57813X542X18145` text COLLATE utf8_unicode_ci,
+  `57813X542X18146` text COLLATE utf8_unicode_ci,
+  `57813X542X18147` text COLLATE utf8_unicode_ci,
+  `57813X542X18148` text COLLATE utf8_unicode_ci,
+  `57813X542X18149` text COLLATE utf8_unicode_ci,
+  `57813X543X18451` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL,
+  `57813X543X18452` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL,
+  `57813X543X18453` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL,
+  `57813X543X18454` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL,
+  `57813X543X18455` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL,
+  `57813X543X18456` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL,
+  `57813X543X18461` text COLLATE utf8_unicode_ci,
+  `57813X543X18462` text COLLATE utf8_unicode_ci,
+  `57813X543X18463` text COLLATE utf8_unicode_ci,
+  `57813X543X18464` text COLLATE utf8_unicode_ci,
+  `57813X543X18465` text COLLATE utf8_unicode_ci,
+  `57813X543X18466` text COLLATE utf8_unicode_ci,
+  `57813X544X18711` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL,
+  `57813X544X18712` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL,
+  `57813X544X18713` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL,
+  `57813X544X18714` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL,
+  `57813X544X18715` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL,
+  `57813X544X18716` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL,
+  `57813X544X18717` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL,
+  `57813X544X18718` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL,
+  `57813X544X18721` text COLLATE utf8_unicode_ci,
+  `57813X544X18722` text COLLATE utf8_unicode_ci,
+  `57813X544X18723` text COLLATE utf8_unicode_ci,
+  `57813X544X18724` text COLLATE utf8_unicode_ci,
+  `57813X544X18725` text COLLATE utf8_unicode_ci,
+  `57813X544X18726` text COLLATE utf8_unicode_ci,
+  `57813X544X18727` text COLLATE utf8_unicode_ci,
+  `57813X544X18728` text COLLATE utf8_unicode_ci,
+  `57813X546X1902` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL,
+  `57813X546X1903` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL,
+  `57813X546X1904` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL,
+  `57813X545X1901` varchar(1) COLLATE utf8_unicode_ci DEFAULT NULL,
+  PRIMARY KEY (`id`),
+  KEY `lime_survey_57813_idx` (`token`),
+  KEY `57813X540X1723` (`57813X540X1723`(100)),
+  KEY `57813X540X1724` (`57813X540X1724`(100)),
+  KEY `57813X540X1726` (`57813X540X1726`),
+  KEY `57813X540X1725` (`57813X540X1725`(100)),
+  KEY `57813X546X1902` (`57813X546X1902`),
+  KEY `57813X546X1903` (`57813X546X1903`),
+  KEY `57813X546X1904` (`57813X546X1904`)
+);
+
+SELECT 
+COUNT(*) as `N`, 
+ROUND( 
+      ( 
+        SUM( 
+            ( 
+             ( 
+	      IF( 57813X541X17271 IS NOT NULL AND 57813X541X17271 != '' AND 57813X541X17271 != '99', 57813X541X17271, 0 ) + 
+              IF( 57813X541X17272 IS NOT NULL AND 57813X541X17272 != '' AND 57813X541X17272 != '99', 57813X541X17272, 0 ) + 
+              IF( 57813X541X17273 IS NOT NULL AND 57813X541X17273 != '' AND 57813X541X17273 != '99', 57813X541X17273, 0 ) + 
+              IF( 57813X541X17274 IS NOT NULL AND 57813X541X17274 != '' AND 57813X541X17274 != '99', 57813X541X17274, 0 ) + 
+              IF( 57813X541X17275 IS NOT NULL AND 57813X541X17275 != '' AND 57813X541X17275 != '99', 57813X541X17275, 0 ) +
+              IF( 57813X541X17276 IS NOT NULL AND 57813X541X17276 != '' AND 57813X541X17276 != '99', 57813X541X17276, 0 ) + 
+              IF( 57813X542X18131 IS NOT NULL AND 57813X542X18131 != '' AND 57813X542X18131 != '99', 57813X542X18131, 0 ) + 
+              IF( 57813X542X18132 IS NOT NULL AND 57813X542X18132 != '' AND 57813X542X18132 != '99', 57813X542X18132, 0 ) + 
+              IF( 57813X542X18133 IS NOT NULL AND 57813X542X18133 != '' AND 57813X542X18133 != '99', 57813X542X18133, 0 ) + 
+              IF( 57813X542X18134 IS NOT NULL AND 57813X542X18134 != '' AND 57813X542X18134 != '99', 57813X542X18134, 0 ) + 
+              IF( 57813X542X18135 IS NOT NULL AND 57813X542X18135 != '' AND 57813X542X18135 != '99', 57813X542X18135, 0 ) + 
+              IF( 57813X542X18136 IS NOT NULL AND 57813X542X18136 != '' AND 57813X542X18136 != '99', 57813X542X18136, 0 ) + 
+              IF( 57813X542X18137 IS NOT NULL AND 57813X542X18137 != '' AND 57813X542X18137 != '99', 57813X542X18137, 0 ) + 
+              IF( 57813X542X18138 IS NOT NULL AND 57813X542X18138 != '' AND 57813X542X18138 != '99', 57813X542X18138, 0 ) + 
+              IF( 57813X542X18139 IS NOT NULL AND 57813X542X18139 != '' AND 57813X542X18139 != '99', 57813X542X18139, 0 ) + 
+              IF( 57813X543X18451 IS NOT NULL AND 57813X543X18451 != '' AND 57813X543X18451 != '99', 57813X543X18451, 0 ) + 
+              IF( 57813X543X18452 IS NOT NULL AND 57813X543X18452 != '' AND 57813X543X18452 != '99', 57813X543X18452, 0 ) + 
+              IF( 57813X543X18453 IS NOT NULL AND 57813X543X18453 != '' AND 57813X543X18453 != '99', 57813X543X18453, 0 ) + 
+              IF( 57813X543X18454 IS NOT NULL AND 57813X543X18454 != '' AND 57813X543X18454 != '99', 57813X543X18454, 0 ) + 
+              IF( 57813X543X18455 IS NOT NULL AND 57813X543X18455 != '' AND 57813X543X18455 != '99', 57813X543X18455, 0 ) + 
+              IF( 57813X543X18456 IS NOT NULL AND 57813X543X18456 != '' AND 57813X543X18456 != '99', 57813X543X18456, 0 ) + 
+              IF( 57813X544X18711 IS NOT NULL AND 57813X544X18711 != '' AND 57813X544X18711 != '99', 57813X544X18711, 0 ) + 
+              IF( 57813X544X18712 IS NOT NULL AND 57813X544X18712 != '' AND 57813X544X18712 != '99', 57813X544X18712, 0 ) + 
+              IF( 57813X544X18713 IS NOT NULL AND 57813X544X18713 != '' AND 57813X544X18713 != '99', 57813X544X18713, 0 ) + 
+              IF( 57813X544X18714 IS NOT NULL AND 57813X544X18714 != '' AND 57813X544X18714 != '99', 57813X544X18714, 0 ) + 
+              IF( 57813X544X18715 IS NOT NULL AND 57813X544X18715 != '' AND 57813X544X18715 != '99', 57813X544X18715, 0 ) + 
+              IF( 57813X544X18716 IS NOT NULL AND 57813X544X18716 != '' AND 57813X544X18716 != '99', 57813X544X18716, 0 ) + 
+              IF( 57813X544X18717 IS NOT NULL AND 57813X544X18717 != '' AND 57813X544X18717 != '99', 57813X544X18717, 0 ) + 
+              IF( 57813X544X18718 IS NOT NULL AND 57813X544X18718 != '' AND 57813X544X18718 != '99', 57813X544X18718, 0 ) 
+             ) 
+             / 
+             (
+              IF( 57813X541X17271 IS NOT NULL AND 57813X541X17271 != '' AND 57813X541X17271 != '99', 1, 0 ) + 
+              IF( 57813X541X17272 IS NOT NULL AND 57813X541X17272 != '' AND 57813X541X17272 != '99', 1, 0 ) + 
+              IF( 57813X541X17273 IS NOT NULL AND 57813X541X17273 != '' AND 57813X541X17273 != '99', 1, 0 ) + 
+              IF( 57813X541X17274 IS NOT NULL AND 57813X541X17274 != '' AND 57813X541X17274 != '99', 1, 0 ) + 
+              IF( 57813X541X17275 IS NOT NULL AND 57813X541X17275 != '' AND 57813X541X17275 != '99', 1, 0 ) + 
+              IF( 57813X541X17276 IS NOT NULL AND 57813X541X17276 != '' AND 57813X541X17276 != '99', 1, 0 ) + 
+              IF( 57813X542X18131 IS NOT NULL AND 57813X542X18131 != '' AND 57813X542X18131 != '99', 1, 0 ) + 
+              IF( 57813X542X18132 IS NOT NULL AND 57813X542X18132 != '' AND 57813X542X18132 != '99', 1, 0 ) + 
+              IF( 57813X542X18133 IS NOT NULL AND 57813X542X18133 != '' AND 57813X542X18133 != '99', 1, 0 ) + 
+              IF( 57813X542X18134 IS NOT NULL AND 57813X542X18134 != '' AND 57813X542X18134 != '99', 1, 0 ) + 
+              IF( 57813X542X18135 IS NOT NULL AND 57813X542X18135 != '' AND 57813X542X18135 != '99', 1, 0 ) + 
+              IF( 57813X542X18136 IS NOT NULL AND 57813X542X18136 != '' AND 57813X542X18136 != '99', 1, 0 ) + 
+              IF( 57813X542X18137 IS NOT NULL AND 57813X542X18137 != '' AND 57813X542X18137 != '99', 1, 0 ) + 
+              IF( 57813X542X18138 IS NOT NULL AND 57813X542X18138 != '' AND 57813X542X18138 != '99', 1, 0 ) + 
+              IF( 57813X542X18139 IS NOT NULL AND 57813X542X18139 != '' AND 57813X542X18139 != '99', 1, 0 ) + 
+              IF( 57813X543X18451 IS NOT NULL AND 57813X543X18451 != '' AND 57813X543X18451 != '99', 1, 0 ) + 
+              IF( 57813X543X18452 IS NOT NULL AND 57813X543X18452 != '' AND 57813X543X18452 != '99', 1, 0 ) + 
+              IF( 57813X543X18453 IS NOT NULL AND 57813X543X18453 != '' AND 57813X543X18453 != '99', 1, 0 ) + 
+              IF( 57813X543X18454 IS NOT NULL AND 57813X543X18454 != '' AND 57813X543X18454 != '99', 1, 0 ) + 
+              IF( 57813X543X18455 IS NOT NULL AND 57813X543X18455 != '' AND 57813X543X18455 != '99', 1, 0 ) + 
+              IF( 57813X543X18456 IS NOT NULL AND 57813X543X18456 != '' AND 57813X543X18456 != '99', 1, 0 ) + 
+              IF( 57813X544X18711 IS NOT NULL AND 57813X544X18711 != '' AND 57813X544X18711 != '99', 1, 0 ) + 
+              IF( 57813X544X18712 IS NOT NULL AND 57813X544X18712 != '' AND 57813X544X18712 != '99', 1, 0 ) + 
+              IF( 57813X544X18713 IS NOT NULL AND 57813X544X18713 != '' AND 57813X544X18713 != '99', 1, 0 ) + 
+              IF( 57813X544X18714 IS NOT NULL AND 57813X544X18714 != '' AND 57813X544X18714 != '99', 1, 0 ) + 
+              IF( 57813X544X18715 IS NOT NULL AND 57813X544X18715 != '' AND 57813X544X18715 != '99', 1, 0 ) + 
+              IF( 57813X544X18716 IS NOT NULL AND 57813X544X18716 != '' AND 57813X544X18716 != '99', 1, 0 ) + 
+              IF( 57813X544X18717 IS NOT NULL AND 57813X544X18717 != '' AND 57813X544X18717 != '99', 1, 0 ) + 
+              IF( 57813X544X18718 IS NOT NULL AND 57813X544X18718 != '' AND 57813X544X18718 != '99', 1, 0 ) 
+              ) 
+             ) 
+            ) 
+        / COUNT(*) ), 4) as `AVG`
+FROM `t1` 
+WHERE `submitdate` IS NOT NULL 
+AND ( 
+     ( 57813X541X17271 IS NOT NULL AND 57813X541X17271 != '' AND 57813X541X17271 != '99' ) OR 
+     ( 57813X541X17272 IS NOT NULL AND 57813X541X17272 != '' AND 57813X541X17272 != '99' ) OR 
+     ( 57813X541X17273 IS NOT NULL AND 57813X541X17273 != '' AND 57813X541X17273 != '99' ) OR 
+     ( 57813X541X17274 IS NOT NULL AND 57813X541X17274 != '' AND 57813X541X17274 != '99' ) OR 
+     ( 57813X541X17275 IS NOT NULL AND 57813X541X17275 != '' AND 57813X541X17275 != '99' ) OR 
+     ( 57813X541X17276 IS NOT NULL AND 57813X541X17276 != '' AND 57813X541X17276 != '99' ) OR 
+     ( 57813X542X18131 IS NOT NULL AND 57813X542X18131 != '' AND 57813X542X18131 != '99' ) OR 
+     ( 57813X542X18132 IS NOT NULL AND 57813X542X18132 != '' AND 57813X542X18132 != '99' ) OR 
+     ( 57813X542X18133 IS NOT NULL AND 57813X542X18133 != '' AND 57813X542X18133 != '99' ) OR 
+     ( 57813X542X18134 IS NOT NULL AND 57813X542X18134 != '' AND 57813X542X18134 != '99' ) OR 
+     ( 57813X542X18135 IS NOT NULL AND 57813X542X18135 != '' AND 57813X542X18135 != '99' ) OR 
+     ( 57813X542X18136 IS NOT NULL AND 57813X542X18136 != '' AND 57813X542X18136 != '99' ) OR 
+     ( 57813X542X18137 IS NOT NULL AND 57813X542X18137 != '' AND 57813X542X18137 != '99' ) OR 
+     ( 57813X542X18138 IS NOT NULL AND 57813X542X18138 != '' AND 57813X542X18138 != '99' ) OR 
+     ( 57813X542X18139 IS NOT NULL AND 57813X542X18139 != '' AND 57813X542X18139 != '99' ) OR 
+     ( 57813X543X18451 IS NOT NULL AND 57813X543X18451 != '' AND 57813X543X18451 != '99' ) OR 
+     ( 57813X543X18452 IS NOT NULL AND 57813X543X18452 != '' AND 57813X543X18452 != '99' ) OR 
+     ( 57813X543X18453 IS NOT NULL AND 57813X543X18453 != '' AND 57813X543X18453 != '99' ) OR 
+     ( 57813X543X18454 IS NOT NULL AND 57813X543X18454 != '' AND 57813X543X18454 != '99' ) OR 
+     ( 57813X543X18455 IS NOT NULL AND 57813X543X18455 != '' AND 57813X543X18455 != '99' ) OR 
+     ( 57813X543X18456 IS NOT NULL AND 57813X543X18456 != '' AND 57813X543X18456 != '99' ) OR 
+     ( 57813X544X18711 IS NOT NULL AND 57813X544X18711 != '' AND 57813X544X18711 != '99' ) OR 
+     ( 57813X544X18712 IS NOT NULL AND 57813X544X18712 != '' AND 57813X544X18712 != '99' ) OR 
+     ( 57813X544X18713 IS NOT NULL AND 57813X544X18713 != '' AND 57813X544X18713 != '99' ) OR 
+     ( 57813X544X18714 IS NOT NULL AND 57813X544X18714 != '' AND 57813X544X18714 != '99' ) OR 
+     ( 57813X544X18715 IS NOT NULL AND 57813X544X18715 != '' AND 57813X544X18715 != '99' ) OR 
+     ( 57813X544X18716 IS NOT NULL AND 57813X544X18716 != '' AND 57813X544X18716 != '99' ) OR 
+     ( 57813X544X18717 IS NOT NULL AND 57813X544X18717 != '' AND 57813X544X18717 != '99' ) OR 
+     ( 57813X544X18718 IS NOT NULL AND 57813X544X18718 != '' AND 57813X544X18718 != '99' ) ) 
+AND 57813X540X1723 = 'Test';
+
+drop table t1;
+
+--echo #
+--echo # End of 5.5 tests
+--echo #
+
+--echo #
 --echo # GET_LOCK, RELEASE_LOCK, IS_USED_LOCK functions test
 --echo #
 
@@ -574,10 +777,6 @@ select is_free_lock(repeat('a', 193));
 --error ER_TOO_LONG_IDENT
 select release_lock(repeat('a', 193));
 
---echo #
---echo # End of 5.5 tests
---echo #
-
 
 --echo
 --echo # --
diff --git a/mysql-test/t/information_schema2.test b/mysql-test/t/information_schema2.test
index d577cdc1e4d..9810c5a0aae 100644
--- a/mysql-test/t/information_schema2.test
+++ b/mysql-test/t/information_schema2.test
@@ -8,15 +8,12 @@ select variable_name from information_schema.session_variables where variable_na
 (select variable_name from information_schema.session_variables where variable_name = 'basedir');
 
 #
-# information_schema tables inside subqueries, they should not be re-populated
-# (i_s.columns needs to scan i_s itself, creating a tmp table for every i_s
-# table. if it's re-populated, it'll do that multiple times)
+# MDEV-8796 Delete with sub query with information_schema.TABLES deletes too many rows
 #
-create table t1 (a char);
-insert t1 values ('a'),('t'),('z');
-flush status;
-select a, exists (select 1 from information_schema.columns where table_schema=concat('tes',a)) from t1;
-# fix the result in ps-protocol
---replace_result 44 43
-show status like 'created_tmp_tables';
-drop table t1;
+create table t1 (x int);
+create table t2 (x int);
+create table t3 (x int);
+create table t4 AS select table_name from information_schema.TABLES where table_schema = database() and table_type = 'BASE TABLE' ;
+delete from t4 where table_name not in (select table_name from information_schema.TABLES where table_schema = database() and table_type = 'BASE TABLE');
+select * from t4;
+drop table t1, t2, t3, t4;
diff --git a/mysql-test/t/loadxml.test b/mysql-test/t/loadxml.test
index 6faf712b6ce..93e6e82189f 100644
--- a/mysql-test/t/loadxml.test
+++ b/mysql-test/t/loadxml.test
@@ -116,3 +116,17 @@ LOAD XML INFILE '../../std_data/loadxml.dat' INTO TABLE t1
 ROWS IDENTIFIED BY '<row>' (a,@b) SET b=concat('!',@b);
 SELECT * FROM t1 ORDER BY a;
 DROP TABLE t1;
+
+
+--echo #
+--echo # Bug#16171518 LOAD XML DOES NOT HANDLE EMPTY ELEMENTS
+--echo #
+CREATE TABLE t1 (col1 VARCHAR(3), col2 VARCHAR(3), col3 VARCHAR(3), col4 VARCHAR(4));
+LOAD XML INFILE '../../std_data/bug16171518_1.dat' INTO TABLE t1;
+SELECT * FROM t1 ORDER BY col1, col2, col3, col4;
+DROP TABLE t1;
+
+CREATE TABLE t1 (col1 VARCHAR(3), col2 VARCHAR(3), col3 INTEGER);
+LOAD XML INFILE '../../std_data/bug16171518_2.dat' INTO TABLE t1;
+SELECT * FROM t1 ORDER BY col1, col2, col3;
+DROP TABLE t1;
diff --git a/mysql-test/t/lowercase_fs_on.test b/mysql-test/t/lowercase_fs_on.test
new file mode 100644
index 00000000000..6da3ef32a0b
--- /dev/null
+++ b/mysql-test/t/lowercase_fs_on.test
@@ -0,0 +1,38 @@
+#
+# Specific tests for case-insensitive file systems
+# i.e. lower_case_filesystem=ON
+#
+-- source include/have_case_insensitive_file_system.inc
+# Embedded server does not support restarting.
+--source include/not_embedded.inc
+
+--echo #
+--echo # Bug#20198490 : LOWER_CASE_TABLE_NAMES=0 ON WINDOWS LEADS TO PROBLEMS
+--echo #
+
+let SEARCH_FILE= $MYSQLTEST_VARDIR/log/my_restart.err;
+
+--error 0,1
+--remove_file $SEARCH_FILE
+
+#Shutdown the server
+--exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
+--shutdown_server
+--source include/wait_until_disconnected.inc
+
+#Start the server with --lower_case_table_names=0 in Windows.
+--enable_reconnect
+--error 1
+--exec $MYSQLD_CMD --lower_case_table_names=0 > $SEARCH_FILE  2>&1
+
+#Search for the error messege in the server error log.
+let SEARCH_PATTERN= \[ERROR\] The server option \'lower_case_table_names\' is configured to use case sensitive table names but the data directory is on a case-insensitive file system which is an unsupported combination\. Please consider either using a case sensitive file system for your data directory or switching to a case-insensitive table name mode\.;
+--source include/search_pattern_in_file.inc
+
+#Restart the server
+--exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
+--source include/wait_until_connected_again.inc
+
+#Cleanup
+--error 0,1
+--remove_file $SEARCH_FILE
diff --git a/mysql-test/t/lowercase_table3-master.opt b/mysql-test/t/lowercase_table3-master.opt
deleted file mode 100644
index 9b27aef9bf8..00000000000
--- a/mysql-test/t/lowercase_table3-master.opt
+++ /dev/null
@@ -1 +0,0 @@
---lower_case_table_names=0
diff --git a/mysql-test/t/lowercase_table3.test b/mysql-test/t/lowercase_table3.test
deleted file mode 100644
index f7ca8211288..00000000000
--- a/mysql-test/t/lowercase_table3.test
+++ /dev/null
@@ -1,37 +0,0 @@
-#
-# Test of force of lower-case-table-names=0
-# (User has case insensitive file system and wants to preserve case of
-# table names)
-#
-
---source include/have_innodb.inc
---source include/have_lowercase0.inc
---source include/have_case_insensitive_file_system.inc
---source include/not_windows.inc
-
-call mtr.add_suppression("Cannot find or open table test/BUG29839 from");
-
---disable_warnings
-DROP TABLE IF EXISTS t1,T1;
---enable_warnings
-
-#
-# This is actually an error, but ok as the user has forced this
-# by using --lower-case-table-names=0
-CREATE TABLE t1 (a INT);
-SELECT * FROM T1;
-FLUSH TABLES;
-DROP TABLE t1;
-
-#
-# InnoDB should in this case be case sensitive
-# Note that this is not true on windows as no this OS, InnoDB is always
-# storing things in lower case.
-#
-
-CREATE TABLE bug29839 (a INT) ENGINE=INNODB;
---error ER_NO_SUCH_TABLE
-SELECT * FROM BUG29839;
-DROP TABLE bug29839;
-
-# End of 4.1 tests
diff --git a/mysql-test/t/mysqldump.test b/mysql-test/t/mysqldump.test
index 9d6789541c0..677a66ab4c8 100644
--- a/mysql-test/t/mysqldump.test
+++ b/mysql-test/t/mysqldump.test
@@ -904,13 +904,8 @@ select * from t1;
 create  view v1 as
 select * from v3 where b in (1, 2, 3, 4, 5, 6, 7);
 
-# Disable warnings since LIMIT warning for unsafe statement if
-# binlog_format = STATEMENT. Note: after BUG#45832, the warning should
-# not be issued.
---disable_warnings
 create  view v2 as
 select v3.a from v3, v1 where v1.a=v3.a and v3.b=3 limit 1;
---enable_warnings
 
 --exec $MYSQL_DUMP --skip-comments test
 
@@ -1780,7 +1775,7 @@ drop table words;
 --replace_regex /.*mysqlimport(\.exe)*/mysqlimport/
 --replace_result mysqldump.exe mysqldump
 --error 1
---exec $MYSQL_IMPORT --silent --use-threads=2 test $MYSQLTEST_VARDIR/tmp/t1.txt $MYSQLTEST_VARDIR/tmp/t2.txt $MYSQLTEST_VARDIR/std_data/words.dat $MYSQLTEST_VARDIR/std_data/words2.dat 2>&1
+--exec $MYSQL_IMPORT --silent --use-threads=2 test $MYSQLTEST_VARDIR/tmp/t1.txt $MYSQLTEST_VARDIR/tmp/t2.txt $MYSQLTEST_VARDIR/std_data/words.dat $MYSQLTEST_VARDIR/std_data/words2.dat
 
 drop table t1;
 drop table t2;
@@ -2459,6 +2454,35 @@ drop table t1, t2;
 --echo # End of 5.1 tests
 --echo #
 
+--echo #
+--echo # Bug #20772273 : MYSQLIMPORT --USE-THREADS DOESN'T USE MULTIPLE THREADS
+--echo #
+
+CREATE DATABASE db_20772273;
+USE db_20772273;
+CREATE TABLE t1(a INT);
+INSERT INTO t1 VALUES (1), (2);
+CREATE TABLE t2(a INT);
+INSERT INTO t2 VALUES (3), (4);
+
+SELECT * FROM t1;
+SELECT * FROM t2;
+
+--exec $MYSQL_DUMP --tab=$MYSQLTEST_VARDIR/tmp/ db_20772273
+--exec $MYSQL db_20772273 < $MYSQLTEST_VARDIR/tmp/t1.sql
+--exec $MYSQL db_20772273 < $MYSQLTEST_VARDIR/tmp/t2.sql
+
+# Test mysqlimport with multiple threads
+--exec $MYSQL_IMPORT --silent --use-threads=2 db_20772273 $MYSQLTEST_VARDIR/tmp/t1.txt $MYSQLTEST_VARDIR/tmp/t2.txt
+
+SELECT * FROM t1;
+SELECT * FROM t2;
+
+#Cleanup
+DROP TABLE t1;
+DROP TABLE t2;
+DROP DATABASE db_20772273;
+
 #
 # MDEV-6091 mysqldump goes in a loop and segfaults if --dump-slave is specified and it cannot connect to the server
 #
diff --git a/mysql-test/t/partition_error.test b/mysql-test/t/partition_error.test
index 5396b9e3543..858213b1e30 100644
--- a/mysql-test/t/partition_error.test
+++ b/mysql-test/t/partition_error.test
@@ -1157,7 +1157,7 @@ subpartition by hash (rand(a+b));
 #
 # Subpartition by hash, wrong subpartition function
 #
---error ER_SUBPARTITION_ERROR
+--error ER_PARTITION_FUNCTION_IS_NOT_ALLOWED
 CREATE TABLE t1 (
 a int not null,
 b int not null,
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index d1c3774947a..67eac0d228c 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -53,7 +53,7 @@ SELECT 1 IN (SELECT 1);
 SELECT 1 FROM (SELECT 1 as a) b WHERE 1 IN (SELECT (SELECT a));
 -- error ER_WRONG_USAGE
 select (SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE(1));
--- error ER_WRONG_PARAMETERS_TO_PROCEDURE
+-- error ER_PARSE_ERROR
 SELECT 1 FROM (SELECT 1) a PROCEDURE ANALYSE((SELECT 1));
 -- error ER_BAD_FIELD_ERROR
 SELECT (SELECT 1) as a FROM (SELECT 1) b WHERE (SELECT a) IS NULL;
@@ -5890,3 +5890,77 @@ DROP TABLE t1,t2;
 create table t1 (c int);
 select exists(select 1 from t1 group by `c` in (select `c` from t1));
 drop table t1;
+
+--echo #
+--echo # MDEV-7565: Server crash with Signal 6 (part 2)
+--echo #
+Select 
+  (Select Sum(`TestCase`.Revenue) From mysql.slow_log E           
+    Where TestCase.TemplateID not in (Select 1 from mysql.slow_log where 2=2)
+  ) As `ControlRev`
+From 
+(Select  3 as Revenue, 4 as TemplateID) As `TestCase` 
+Group By  TestCase.Revenue, TestCase.TemplateID;
+
+--echo #
+--echo # MDEV-7445:Server crash with Signal 6
+--echo #
+
+--delimiter |
+CREATE PROCEDURE procedure2()
+BEGIN
+  Select 
+    (Select Sum(`TestCase`.Revenue) From mysql.slow_log E           
+      Where TestCase.TemplateID not in (Select 1 from mysql.slow_log where 2=2)
+     ) As `ControlRev`
+  From 
+  (Select  3 as Revenue, 4 as TemplateID) As `TestCase` 
+  Group By  TestCase.Revenue, TestCase.TemplateID;
+
+END |
+--delimiter ;
+call procedure2();
+call procedure2();
+
+drop procedure procedure2;
+
+
+SELECT
+  (SELECT user FROM mysql.user
+    WHERE h.host in (SELECT host FROM mysql.user)
+  ) AS sq
+FROM mysql.host h GROUP BY h.host;
+
+
+--echo #
+--echo # MDEV-7846:Server crashes in Item_subselect::fix
+--echo #_fields or fails with Thread stack overrun
+--echo #
+CREATE TABLE t1 (column1 INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (3),(9);
+
+CREATE TABLE t2 (column2 INT) ENGINE=MyISAM;
+
+INSERT INTO t2 VALUES (1),(4);
+
+CREATE TABLE t3 (column3 INT) ENGINE=MyISAM;
+INSERT INTO t3 VALUES (6),(8);
+
+CREATE TABLE t4 (column4 INT) ENGINE=MyISAM;
+INSERT INTO t4 VALUES (2),(5);
+
+
+PREPARE stmt FROM "
+SELECT ( 
+  SELECT MAX( table1.column1 ) AS field1 
+  FROM t1 AS table1
+  WHERE table3.column3 IN ( SELECT table2.column2 AS field2 FROM t2 AS table2 ) 
+) AS sq
+FROM t3 AS table3, t4 AS table4 GROUP BY sq
+";
+
+EXECUTE stmt;
+EXECUTE stmt;
+
+deallocate prepare stmt;
+drop table t1,t2,t3,t4;
diff --git a/mysql-test/t/update.test b/mysql-test/t/update.test
index 09d32966c50..05f9ce39bec 100644
--- a/mysql-test/t/update.test
+++ b/mysql-test/t/update.test
@@ -506,6 +506,21 @@ DROP VIEW v1;
 DROP FUNCTION f1;
 DROP TABLE t1;
 
+--echo # Bug #21143080: UPDATE ON VARCHAR AND TEXT COLUMNS PRODUCE INCORRECT
+--echo #                RESULTS
+
+CREATE TABLE t1 (a VARCHAR(50), b TEXT, c CHAR(50)) ENGINE=INNODB;
+
+INSERT INTO t1 (a, b, c) VALUES ('start trail', '', 'even longer string');
+UPDATE t1 SET b = a, a = 'inject';
+SELECT a, b FROM t1;
+UPDATE t1 SET b = c, c = 'inject';
+SELECT c, b FROM t1;
+
+DROP TABLE t1;
+
+--echo #
+--echo # MDEV-3948 Assertion `records_are_comparable(table)' fails in compare_record(const TABLE*) on UPDATE with simple AND condition, index_merge+index_merge_intersection, InnoDB
 --echo #
 --echo # Verify that UPDATE does the same number of handler_update
 --echo # operations, no matter if there is ORDER BY or not.
diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test
index 8c2a8ac8dc2..cf406db6cd8 100644
--- a/mysql-test/t/view.test
+++ b/mysql-test/t/view.test
@@ -4741,6 +4741,50 @@ SHOW CREATE VIEW v4;
 
 DROP VIEW v1, v2, v3, v4, v5;
 
+
+--echo #
+--echo # BUG#19886430: VIEW CREATION WITH NAMED COLUMNS, OVER UNION,
+--echo #               IS REJECTED
+
+--echo # Without the patch, reports an error.
+CREATE VIEW v1 (fld1, fld2) AS
+  SELECT 1 AS a, 2 AS b
+    UNION ALL
+  SELECT 1 AS a, 1 AS a;
+
+--echo # The column names are explicitly specified and not duplicates, hence
+--echo # succeeds.
+CREATE VIEW v2 (fld1, fld2) AS
+  SELECT 1 AS a, 2 AS a
+    UNION ALL
+  SELECT 1 AS a, 1 AS a;
+
+--echo # The column name in the first SELECT are not duplicates, hence succeeds.
+CREATE VIEW v3 AS
+  SELECT 1 AS a, 2 AS b
+    UNION ALL
+  SELECT 1 AS a, 1 AS a;
+
+--echo # Should report an error, since the explicitly specified column names are
+--echo # duplicates.
+--error ER_DUP_FIELDNAME
+CREATE VIEW v4 (fld1, fld1) AS
+  SELECT 1 AS a, 2 AS b
+    UNION ALL
+  SELECT 1 AS a, 1 AS a;
+
+--echo # Should report an error, since duplicate column name is specified in the
+--echo # First SELECT.
+--error ER_DUP_FIELDNAME
+CREATE VIEW v4 AS
+  SELECT 1 AS a, 2 AS a
+    UNION ALL
+  SELECT 1 AS a, 1 AS a;
+
+--echo # Cleanup
+DROP VIEW v1, v2, v3;
+
+
 # Check that all connections opened by test cases in this file are really
 # gone so execution of other tests won't be affected by their presence.
 --source include/wait_until_count_sessions.inc