Merge 10.1 into 10.2

15 files changed, 257 insertions, 19 deletions

diff --git a/mysql-test/t/alter_table.test b/mysql-test/t/alter_table.test
index 97cef0b5794..df077c800d2 100644
--- a/mysql-test/t/alter_table.test
+++ b/mysql-test/t/alter_table.test
@@ -1296,6 +1296,56 @@ MODIFY COLUMN `consultant_id` BIGINT;
 SHOW CREATE TABLE t1;
 DROP TABLE t1;
 
+--echo #
+--echo # BUG#27788685: NO WARNING WHEN TRUNCATING A STRING WITH DATA LOSS
+--echo #
+
+SET GLOBAL max_allowed_packet=17825792;
+
+--connect(con1, localhost, root,,)
+CREATE TABLE t1 (t1_fld1 TEXT);
+CREATE TABLE t2 (t2_fld1 MEDIUMTEXT);
+CREATE TABLE t3 (t3_fld1 LONGTEXT);
+
+INSERT INTO t1 VALUES (REPEAT('a',300));
+INSERT INTO t2 VALUES (REPEAT('b',65680));
+INSERT INTO t3 VALUES (REPEAT('c',16777300));
+
+SELECT LENGTH(t1_fld1) FROM t1;
+SELECT LENGTH(t2_fld1) FROM t2;
+SELECT LENGTH(t3_fld1) FROM t3;
+
+--echo # With strict mode
+SET SQL_MODE='STRICT_ALL_TABLES';
+
+--error ER_DATA_TOO_LONG
+ALTER TABLE t1 CHANGE `t1_fld1` `my_t1_fld1` TINYTEXT;
+--error ER_DATA_TOO_LONG
+ALTER TABLE t2 CHANGE `t2_fld1` `my_t2_fld1` TEXT;
+--error ER_DATA_TOO_LONG
+ALTER TABLE t3 CHANGE `t3_fld1` `my_t3_fld1` MEDIUMTEXT;
+
+--echo # With non-strict mode
+SET SQL_MODE='';
+
+ALTER TABLE t1 CHANGE `t1_fld1` `my_t1_fld1` TINYTEXT;
+ALTER TABLE t2 CHANGE `t2_fld1` `my_t2_fld1` TEXT;
+ALTER TABLE t3 CHANGE `t3_fld1` `my_t3_fld1` MEDIUMTEXT;
+
+SELECT LENGTH(my_t1_fld1) FROM t1;
+SELECT LENGTH(my_t2_fld1) FROM t2;
+SELECT LENGTH(my_t3_fld1) FROM t3;
+
+# Cleanup
+--disconnect con1
+--source include/wait_until_disconnected.inc
+
+--connection default
+DROP TABLE t1, t2, t3;
+
+SET SQL_MODE=default;
+SET GLOBAL max_allowed_packet=default;
+
 #
 # Test of ALTER TABLE IF [NOT] EXISTS
 #
diff --git a/mysql-test/t/create_or_replace.test b/mysql-test/t/create_or_replace.test
index 9f718ab88ce..4b167663742 100644
--- a/mysql-test/t/create_or_replace.test
+++ b/mysql-test/t/create_or_replace.test
@@ -453,14 +453,14 @@ CREATE TABLE t3(a INT);
 LOCK TABLE t2 WRITE;
 SELECT * FROM t2;
 # drops t2
---error ER_UNSUPPORT_COMPRESSED_TEMPORARY_TABLE
-CREATE OR REPLACE TEMPORARY TABLE t1(a INT) ENGINE=InnoDB ROW_FORMAT=COMPRESSED;
+--error ER_INVALID_DEFAULT
+CREATE OR REPLACE TEMPORARY TABLE t1(c INT DEFAULT '');
 # make sure we didn't leave locked tables mode
 --error ER_TABLE_NOT_LOCKED
 SELECT * FROM t3;
 # drops t1
---error ER_UNSUPPORT_COMPRESSED_TEMPORARY_TABLE
-CREATE OR REPLACE TEMPORARY TABLE t2(a INT) ENGINE=InnoDB ROW_FORMAT=COMPRESSED;
+--error ER_INVALID_DEFAULT
+CREATE OR REPLACE TEMPORARY TABLE t2(c INT DEFAULT '');
 # make sure we didn't leave locked tables mode
 --error ER_TABLE_NOT_LOCKED
 SELECT * FROM t3;
diff --git a/mysql-test/t/ctype_latin1.test b/mysql-test/t/ctype_latin1.test
index 61df80e2186..a556c86c016 100644
--- a/mysql-test/t/ctype_latin1.test
+++ b/mysql-test/t/ctype_latin1.test
@@ -267,6 +267,18 @@ SET NAMES latin1;
 
 
 --echo #
+--echo # MDEV-17298 ASAN unknown-crash / READ of size 1 in my_strntoul_8bit upon INSERT .. SELECT
+--echo #
+
+SET NAMES latin1;
+CREATE TABLE t1 (a CHAR);
+CREATE TABLE t2 (b ENUM('foo','bar'));
+INSERT INTO t1 VALUES ('1');
+INSERT INTO t2 SELECT * FROM t1;
+DROP TABLE t1, t2;
+
+
+--echo #
 --echo # End of 10.0 tests
 --echo #
 
diff --git a/mysql-test/t/ctype_uca.test b/mysql-test/t/ctype_uca.test
index 15a945fde6d..82be0305f07 100644
--- a/mysql-test/t/ctype_uca.test
+++ b/mysql-test/t/ctype_uca.test
@@ -619,6 +619,24 @@ EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='oe' COLLATE utf8_german2_ci AND a='oe
 DROP TABLE t1;
 
 --echo #
+--echo # MDEV-17064 LIKE function has error behavior on the fields in which the collation is xxx_unicode_xx
+--echo #
+
+CREATE TABLE t1 (name VARCHAR(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci);
+INSERT INTO t1 VALUES ('radio! test');
+SELECT * FROM t1 WHERE name LIKE '%!!%' ESCAPE '!';
+ALTER TABLE t1 CHANGE COLUMN name name VARCHAR(20) CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
+SELECT * FROM t1 WHERE name LIKE '%!!%' ESCAPE '!';
+DROP TABLE t1;
+
+CREATE TABLE t1 (name VARCHAR(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci);
+INSERT INTO t1 VALUES ('radio! test');
+SELECT name LIKE '%!!%' ESCAPE '!' AS c1,
+       name LIKE '%!!%' COLLATE utf8_general_ci ESCAPE '!' AS c2
+FROM t1;
+DROP TABLE t1;
+
+--echo #
 --echo # End of MariaDB-10.0 tests
 --echo #
 
diff --git a/mysql-test/t/flush.test b/mysql-test/t/flush.test
index 03332c06b08..51b5c48c137 100644
--- a/mysql-test/t/flush.test
+++ b/mysql-test/t/flush.test
@@ -688,7 +688,6 @@ FLUSH TABLES v1;
 UNLOCK TABLES;
 
 LOCK TABLES v1 WRITE;
---error ER_TABLE_NOT_LOCKED_FOR_WRITE
 FLUSH TABLES v1;
 UNLOCK TABLES;
 
diff --git a/mysql-test/t/func_concat.test b/mysql-test/t/func_concat.test
index 69dd2c4063e..e1bda4be29e 100644
--- a/mysql-test/t/func_concat.test
+++ b/mysql-test/t/func_concat.test
@@ -242,3 +242,25 @@ SET optimizer_switch=@save_optimizer_switch;
 --echo #
 
 SELECT UNHEX(CONCAT('414C2', HEX(8 + ROUND(RAND()*7)), SUBSTR(SHA(UUID()),6,33),HEX(2+ROUND(RAND()*8)))) IS NULL AS c1;
+
+
+--echo #
+--echo # MDEV-13119 Wrong results with CAST(AS CHAR) and subquery
+--echo #
+
+SET optimizer_switch=_utf8'derived_merge=on';
+CREATE TABLE t1 (t VARCHAR(10) CHARSET latin1);
+INSERT INTO t1 VALUES('abcdefghi');
+SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT CAST(t AS CHAR CHARACTER SET utf8) t2 FROM t1) sub;
+DROP TABLE t1;
+SET optimizer_switch=@save_optimizer_switch;
+
+
+--echo #
+--echo # MDEV-13120 Wrong results with MAKE_SET() and subquery
+--echo #
+
+CREATE TABLE t1 (t VARCHAR(10) CHARSET latin1);
+INSERT INTO t1 VALUES('abcdefghi');
+SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT MAKE_SET(3,t,t) t2 FROM t1) sub;
+DROP TABLE t1;
diff --git a/mysql-test/t/index_merge_myisam.test b/mysql-test/t/index_merge_myisam.test
index d265007431e..75beb9bd883 100644
--- a/mysql-test/t/index_merge_myisam.test
+++ b/mysql-test/t/index_merge_myisam.test
@@ -243,3 +243,38 @@ DROP TABLE t1;
 
 set optimizer_switch= @optimizer_switch_save;
 
+--echo #
+--echo # MDEV-16695: Estimate for rows of derived tables is very high when we are using index_merge union
+--echo #
+
+create table t0
+(
+  key1 int not null,
+  INDEX i1(key1)
+);
+
+insert into t0 values (1),(2),(3),(4),(5),(6),(7),(8);
+let $1=7;
+set @d=8;
+while ($1)
+{
+  eval insert into t0 select key1+ @d from t0;
+  eval set @d=@d*2;
+  dec $1;
+}
+alter table t0 add key2 int not null, add index i2(key2);
+alter table t0 add key3 int not null, add index i3(key3);
+alter table t0 add key8 int not null, add index i8(key8);
+
+update t0 set key2=key1,key3=key1,key8=1024-key1;
+analyze table t0;
+
+set @optimizer_switch_save=@@optimizer_switch;
+set optimizer_switch='derived_merge=off,derived_with_keys=off';
+explain select * from (select * from t0 where key1 = 3 or key2 =3) as Z where Z.key8 > 5;
+select * from (select * from t0 where key1 = 3 or key2 =3) as Z where Z.key8 > 5;
+set optimizer_use_condition_selectivity=2;
+explain select * from (select * from t0 where key1 = 3 or key2 =3) as Z where Z.key8 > 5;
+select * from (select * from t0 where key1 = 3 or key2 =3) as Z where Z.key8 > 5;
+set @@optimizer_switch= @optimizer_switch_save;
+drop table t0;
diff --git a/mysql-test/t/lock.test b/mysql-test/t/lock.test
index 92ab8294273..ff77b4991c0 100644
--- a/mysql-test/t/lock.test
+++ b/mysql-test/t/lock.test
@@ -192,7 +192,7 @@ drop view v_bug5719;
 select * from t1;
 unlock tables;
 create or replace view v_bug5719 as select * from t1;
-lock tables v_bug5719 write;
+lock tables v_bug5719 read;
 select * from v_bug5719;
 --echo
 --echo Allowed to use an underlying table under LOCK TABLES <view>
@@ -368,7 +368,7 @@ create table t2 (j int);
 --echo #
 --echo # Try to perform DDL on table which is locked through view.
 create view v1 as select * from t2;
-lock tables t1 write, v1 write;
+lock tables t1 write, v1 read;
 --error ER_TABLE_NOT_LOCKED_FOR_WRITE
 flush table t2;
 --error ER_TABLE_NOT_LOCKED_FOR_WRITE
diff --git a/mysql-test/t/lock_multi.test b/mysql-test/t/lock_multi.test
index 51cc4a50233..a945bcdbb74 100644
--- a/mysql-test/t/lock_multi.test
+++ b/mysql-test/t/lock_multi.test
@@ -734,17 +734,21 @@ DROP VIEW  IF EXISTS v1;
 --echo #
 --echo # Test 1: LOCK TABLES v1 WRITE, t1 READ;
 --echo #
---echo # Thanks to the fact that we no longer allow DDL on tables
---echo # which are locked for write implicitly, the exact scenario
---echo # in which assert was failing is no longer repeatable.
 
 CREATE TABLE t1 ( f1 integer );
 CREATE VIEW v1 AS SELECT f1 FROM t1 ;
 
+--echo # Connection 2
+connect (con2,localhost,root);
 LOCK TABLES v1 WRITE, t1 READ;
---error ER_TABLE_NOT_LOCKED_FOR_WRITE
 FLUSH TABLE t1;
-UNLOCK TABLES;
+disconnect con2;
+--source include/wait_until_disconnected.inc
+
+--echo # Connection 1
+connection default;
+LOCK TABLES t1 WRITE;
+FLUSH TABLE t1;                                    # Assertion happened here
 
 # Cleanup
 DROP TABLE t1;
diff --git a/mysql-test/t/lowercase_fs_off.test b/mysql-test/t/lowercase_fs_off.test
index b8a9795db9a..7c5811f9cc3 100644
--- a/mysql-test/t/lowercase_fs_off.test
+++ b/mysql-test/t/lowercase_fs_off.test
@@ -106,6 +106,18 @@ ALTER TABLE T1 RENAME t1;
 DROP TABLE t1;
 
 #
+# MDEV-13912 mysql_upgrade: case (in)sensitivity for stored procedures
+#
+create database TEST;
+create procedure TEST.pr() begin end;
+create procedure test.pr() begin end;
+--exec $MYSQL_UPGRADE --force 2>&1
+drop procedure test.pr;
+drop database TEST;
+
+# End of 5.5 tests
+
+#
 # MDEV-9014 SHOW TRIGGERS not case sensitive
 #
 create table t1 (a int);
@@ -113,4 +125,7 @@ create trigger t1_bi before insert on t1 for each row set new.a= 1;
 show triggers like '%T1%';
 drop table t1;
 
+let $datadir= `select @@datadir`;
+remove_file $datadir/mysql_upgrade_info;
+
 set GLOBAL sql_mode=default;
diff --git a/mysql-test/t/order_by_zerolength-4285.test b/mysql-test/t/order_by_zerolength-4285.test
index 079ce8da71e..52672aa4595 100644
--- a/mysql-test/t/order_by_zerolength-4285.test
+++ b/mysql-test/t/order_by_zerolength-4285.test
@@ -13,3 +13,17 @@ select * from t1 order by now(), cast(pk as char(0));
 --enable_warnings
 show warnings;
 drop table t1;
+
+--echo #
+--echo # MDEV-17020: Assertion `length > 0' failed in ptr_compare upon ORDER BY with bad conversion
+--echo #
+
+set @save_sql_mode= @@sql_mode;
+SET @@sql_mode= '';
+CREATE TABLE t1 (pk INT PRIMARY KEY);
+INSERT INTO t1 VALUES (1),(2);
+explain
+SELECT * FROM t1 ORDER BY 'foo', CONVERT(pk, CHAR(0)) LIMIT 2;
+SELECT * FROM t1 ORDER BY 'foo', Cast(pk as CHAR(0)) LIMIT 2;
+set @@sql_mode= @save_sql_mode;
+drop table t1;
diff --git a/mysql-test/t/partition_explicit_prune.test b/mysql-test/t/partition_explicit_prune.test
index 68b829fbcc3..b8b6e480ce9 100644
--- a/mysql-test/t/partition_explicit_prune.test
+++ b/mysql-test/t/partition_explicit_prune.test
@@ -858,3 +858,22 @@ CREATE TABLE t2 LIKE t1 PARTITION (p0, p2);
 DROP TABLE t1;
 
 SET @@default_storage_engine = @old_default_storage_engine;
+
+
+--echo #
+--echo # MDEV-14815 - Server crash or AddressSanitizer errors or valgrind warnings in thr_lock / has_old_lock upon FLUSH TABLES
+--echo #
+CREATE TABLE t1 (i INT) ENGINE=MEMORY PARTITION BY RANGE (i) (PARTITION p0 VALUES LESS THAN (4), PARTITION pm VALUES LESS THAN MAXVALUE);
+CREATE TABLE t2 (i INT) ENGINE=MEMORY;
+LOCK TABLE t1 WRITE, t2 WRITE;
+SELECT * FROM t1 PARTITION (p0);
+FLUSH TABLES;
+SELECT * FROM t1 PARTITION (p0);
+ALTER TABLE t1 TRUNCATE PARTITION p0;
+SELECT * FROM t1 PARTITION (p0);
+ALTER TABLE t1 EXCHANGE PARTITION p0 WITH TABLE t2;
+SELECT * FROM t1 PARTITION (p0);
+UNLOCK TABLES;
+
+# Cleanup
+DROP TABLE t1, t2;
diff --git a/mysql-test/t/type_datetime.test b/mysql-test/t/type_datetime.test
index 99c9bb656e6..0e2682a053c 100644
--- a/mysql-test/t/type_datetime.test
+++ b/mysql-test/t/type_datetime.test
@@ -175,12 +175,12 @@ set @@sql_mode= @org_mode;
 ## ( Bug#29290 type_datetime.test failure in 5.1 )
 ## Therefore we sleep a bit if we are too close to midnight.
 ## The complete test itself needs around 1 second.
-## Therefore a time_distance to midnight of 5 seconds should be sufficient.
-if (`SELECT CURTIME() > SEC_TO_TIME(24 * 3600 - 5)`)
+## Therefore a time_distance to midnight of 10 seconds should be sufficient.
+if (`SELECT CURTIME() > SEC_TO_TIME(24 * 3600 - 10)`)
 {
-   # We are here when CURTIME() is between '23:59:56' and '23:59:59'.
-   # So a sleep time of 5 seconds brings us between '00:00:01' and '00:00:04'.
-   --real_sleep 5
+   # We are here when CURTIME() is between '23:59:51' and '23:59:59'.
+   # So a sleep time of 10 seconds brings us between '00:00:01' and '00:00:09'.
+   --real_sleep 10
 }
 create table t1 (f1 date, f2 datetime, f3 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
 insert into t1(f1) values(curdate());
diff --git a/mysql-test/t/type_newdecimal.test b/mysql-test/t/type_newdecimal.test
index dd5311e4be3..d2d64b0baee 100644
--- a/mysql-test/t/type_newdecimal.test
+++ b/mysql-test/t/type_newdecimal.test
@@ -1583,8 +1583,51 @@ select 0.000000000000000000000000000000000000000000000000001 mod 1;
 select 0.0000000001 mod 1;
 select 0.01 mod 1;
 
+#
+# MDEV-17256 Decimal field multiplication bug
+#
+
+CREATE TABLE t1 (
+    `FLD1` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000,
+    `FLD2` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000,
+    `FLD3` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000,
+    `FLD4` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000,
+    `FLD5` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000,
+    `FLD6` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000,
+    `FLD7` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000,
+    `FLD8` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000,
+    `FLD9` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000,
+    `FLD10` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000,
+    `FLD11` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000,
+    `FLD12` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000,
+    `FLD13` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000,
+    `FLD14` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000,
+    `FLD15` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000,
+    `FLD16` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000,
+    `FLD17` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000,
+    `FLD18` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000,
+    `FLD19` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000,
+    `FLD20` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000,
+    `FLD21` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000,
+    `FLD22` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000,
+    `FLD23` decimal(7,4) unsigned zerofill NOT NULL DEFAULT 001.0000
+);
+     
+INSERT INTO t1 VALUES (001.0760,000.9500,001.0000,001.0000,001.0000,
+    001.0000,001.0000,001.0000,001.0000,001.0000,001.0000,000.5949,001.0194,
+    001.0000,001.0000,001.0000,001.0000,001.0000,001.0000,000.9220,001.1890,001.2130,327.2690);
+     
+select FLD1*FLD2*FLD3*FLD4*FLD5*FLD6*FLD7*FLD8*FLD9*FLD10*FLD11*FLD12*FLD13*FLD14*FLD15*FLD16*FLD17*FLD18*FLD19*FLD20*FLD21*FLD22*FLD23 as calc1 from t1;
+select FLD23*FLD2*FLD1*FLD4*FLD5*FLD11*FLD12*FLD13*FLD3*FLD15*FLD16*FLD17*FLD18*FLD19*FLD20*FLD21*FLD22*FLD14*FLD6*FLD7*FLD8*FLD9*FLD10 as calc2 from t1;
+     
+DROP TABLE t1;
+
+CREATE TABLE t1 AS SELECT 1.0 * 2.000;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
 --echo #
---echo # Start of 10.0 tests
+--echo # End of 5.5 tests
 --echo #
 
 --echo #
diff --git a/mysql-test/t/type_year.test b/mysql-test/t/type_year.test
index 117906fd889..5a02c038caa 100644
--- a/mysql-test/t/type_year.test
+++ b/mysql-test/t/type_year.test
@@ -187,9 +187,16 @@ select a from t1 where a=b; # not a constant
 drop table t1;
 drop function y2k;
 
+--echo #
+--echo # MDEV-17257 Server crashes in Item::field_type_for_temporal_comparison or in get_datetime_value on SELECT with YEAR field and IN
+--echo #
+
+CREATE TABLE t1 (y YEAR);
+SELECT * FROM t1 WHERE y IN ( CAST( '1993-03-26 10:14:20' AS DATE ), NULL );
+DROP TABLE t1;
 
 --echo #
---echo # Start of 10.1 tests
+--echo # End of 10.0 tests
 --echo #
 
 --echo #