Merge branch '10.2' into 10.3

18 files changed, 653 insertions, 38 deletions

diff --git a/mysql-test/r/case.result b/mysql-test/r/case.result
index 08fa5a79743..f385a3b20c7 100644
--- a/mysql-test/r/case.result
+++ b/mysql-test/r/case.result
@@ -403,8 +403,38 @@ DROP TABLE t1;
 #
 # End of 10.1 test
 #
+select case 'foo' when time'10:00:00' then 'never' when '0' then 'bug' else 'ok' end;
+case 'foo' when time'10:00:00' then 'never' when '0' then 'bug' else 'ok' end
+ok
+Warnings:
+Warning	1292	Truncated incorrect time value: 'foo'
+select 'foo' in (time'10:00:00','0');
+'foo' in (time'10:00:00','0')
+0
+Warnings:
+Warning	1292	Truncated incorrect time value: 'foo'
+create table t1 (a time);
+insert t1 values (100000), (102030), (203040);
+select case 'foo' when a then 'never' when '0' then 'bug' else 'ok' end from t1;
+case 'foo' when a then 'never' when '0' then 'bug' else 'ok' end
+ok
+ok
+ok
+Warnings:
+Warning	1292	Truncated incorrect time value: 'foo'
+select 'foo' in (a,'0') from t1;
+'foo' in (a,'0')
+0
+0
+0
+Warnings:
+Warning	1292	Truncated incorrect time value: 'foo'
+drop table t1;
+select case '20:10:05' when date'2020-10-10' then 'never' when time'20:10:5' then 'ok' else 'bug' end;
+case '20:10:05' when date'2020-10-10' then 'never' when time'20:10:5' then 'ok' else 'bug' end
+ok
 #
-# Start of 10.3 tests
+# End of 10.2 test
 #
 #
 # MDEV-11554 Wrong result for CASE on a mixture of signed and unsigned expressions
@@ -511,3 +541,6 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 Warnings:
 Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 'a'
 DROP TABLE t1;
+#
+# End of 10.3 test
+#
diff --git a/mysql-test/r/check_constraint.result b/mysql-test/r/check_constraint.result
index 525140d96e7..70d64cd6ff7 100644
--- a/mysql-test/r/check_constraint.result
+++ b/mysql-test/r/check_constraint.result
@@ -152,3 +152,7 @@ a
 1
 NULL
 drop table t1;
+create table t1 (id int auto_increment primary key, datecol datetime, check (datecol>'0001-01-01 00:00:00'));
+insert into t1 (datecol) values (now());
+insert into t1 (datecol) values (now());
+drop table t1;
diff --git a/mysql-test/r/cte_nonrecursive.result b/mysql-test/r/cte_nonrecursive.result
index 53334512b20..534a386fe12 100644
--- a/mysql-test/r/cte_nonrecursive.result
+++ b/mysql-test/r/cte_nonrecursive.result
@@ -1425,3 +1425,40 @@ a
 DEALLOCATE PREPARE stmt;
 DROP TABLE t1;
 DROP VIEW v1,v2;
+#
+# MDEV-15478: Lost name of a explicitly named CTE column used in
+#             the non-recursive CTE defined with UNION
+#
+CREATE TABLE t1 (x int, y int);
+INSERT INTO t1 VALUES (1,2),(2,7),(3,3);
+WITH cte(a) AS (SELECT 1 UNION SELECT 2) SELECT * FROM cte;
+a
+1
+2
+WITH cte(a) AS (SELECT 1 UNION SELECT 2) SELECT a FROM cte;
+a
+1
+2
+WITH cte(a) AS (SELECT 1 UNION ALL SELECT 1) SELECT a FROM cte;
+a
+1
+1
+WITH cte(a) AS (SELECT x from t1 UNION SELECT 4) SELECT a FROM cte;
+a
+1
+2
+3
+4
+WITH cte(a) AS (SELECT 4 UNION SELECT x FROM t1 UNION SELECT 5)
+SELECT a FROM cte;
+a
+4
+1
+2
+3
+5
+WITH cte(a,b) AS (SELECT 4,5 UNION SELECT 4,3) SELECT a,b FROM cte;
+a	b
+4	5
+4	3
+DROP TABLE t1;
diff --git a/mysql-test/r/ctype_latin1.result b/mysql-test/r/ctype_latin1.result
index 085b8b22f67..d5906cb74dd 100644
--- a/mysql-test/r/ctype_latin1.result
+++ b/mysql-test/r/ctype_latin1.result
@@ -8241,6 +8241,19 @@ Warnings:
 Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` where coalesce(`test`.`t1`.`c`,0) = '3 ' and coalesce(`test`.`t1`.`d`,0) = '3 '
 DROP TABLE t1;
 #
+# MDEV-15005 ASAN: stack-buffer-overflow in my_strnncollsp_simple
+#
+SET NAMES latin1;
+SELECT CONVERT(1, CHAR) IN ('100', 10, '101');
+CONVERT(1, CHAR) IN ('100', 10, '101')
+0
+SELECT CONVERT(1, CHAR) IN ('100', 10, '1');
+CONVERT(1, CHAR) IN ('100', 10, '1')
+1
+SELECT CONVERT(1, CHAR) IN ('100', '10', '1');
+CONVERT(1, CHAR) IN ('100', '10', '1')
+1
+#
 # End of 10.1 tests
 #
 #
diff --git a/mysql-test/r/ctype_utf8.result b/mysql-test/r/ctype_utf8.result
index dfe96d1a904..10d0efeff3c 100644
--- a/mysql-test/r/ctype_utf8.result
+++ b/mysql-test/r/ctype_utf8.result
@@ -10543,6 +10543,19 @@ CAST(_utf8 0xC499 AS CHAR CHARACTER SET latin1)
 Warnings:
 Warning	1977	Cannot convert 'utf8' character 0xC499 to 'latin1'
 #
+# MDEV-15005 ASAN: stack-buffer-overflow in my_strnncollsp_simple
+#
+SET NAMES utf8;
+SELECT CONVERT(1, CHAR) IN ('100', 10, '101');
+CONVERT(1, CHAR) IN ('100', 10, '101')
+0
+SELECT CONVERT(1, CHAR) IN ('100', 10, '1');
+CONVERT(1, CHAR) IN ('100', 10, '1')
+1
+SELECT CONVERT(1, CHAR) IN ('100', '10', '1');
+CONVERT(1, CHAR) IN ('100', '10', '1')
+1
+#
 # End of 10.1 tests
 #
 #
diff --git a/mysql-test/r/fast_prefix_index_fetch_innodb.result b/mysql-test/r/fast_prefix_index_fetch_innodb.result
index 92af85f7fdb..c6d96389b08 100644
--- a/mysql-test/r/fast_prefix_index_fetch_innodb.result
+++ b/mysql-test/r/fast_prefix_index_fetch_innodb.result
@@ -30,73 +30,372 @@ id	fake_id	bigfield
 33	1033	yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy
 128	1128	zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
 # Baseline sanity check: 0, 0.
+select "no-op query";
 no-op query
 no-op query
-cluster_lookups_matched
-1
-cluster_lookups_avoided_matched
-1
+select @cluster_lookups;
+@cluster_lookups
+0
+select @cluster_lookups_avoided;
+@cluster_lookups_avoided
+0
 # Eligible for optimization.
+select id, bigfield from prefixinno where bigfield = repeat('d', 31);
 id	bigfield
 31	ddddddddddddddddddddddddddddddd
-cluster_lookups_matched
-1
-cluster_lookups_avoided_matched
+select @cluster_lookups;
+@cluster_lookups
+0
+select @cluster_lookups_avoided;
+@cluster_lookups_avoided
 1
 # Eligible for optimization, access via fake_id only.
+select id, bigfield from prefixinno where fake_id = 1031;
 id	bigfield
 31	ddddddddddddddddddddddddddddddd
-cluster_lookups_matched
-1
-cluster_lookups_avoided_matched
+select @cluster_lookups;
+@cluster_lookups
+0
+select @cluster_lookups_avoided;
+@cluster_lookups_avoided
 1
 # Not eligible for optimization, access via fake_id of big row.
+select id, bigfield from prefixinno where fake_id = 1033;
 id	bigfield
 33	yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy
-cluster_lookups_matched
-1
-cluster_lookups_avoided_matched
+select @cluster_lookups;
+@cluster_lookups
 1
+select @cluster_lookups_avoided;
+@cluster_lookups_avoided
+0
 # Not eligible for optimization.
+select id, bigfield from prefixinno where bigfield = repeat('x', 32);
 id	bigfield
 32	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
-cluster_lookups_matched
-1
-cluster_lookups_avoided_matched
+select @cluster_lookups;
+@cluster_lookups
 1
+select @cluster_lookups_avoided;
+@cluster_lookups_avoided
+0
 # Not eligible for optimization.
+select id, bigfield from prefixinno where bigfield = repeat('y', 33);
 id	bigfield
 33	yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy
-cluster_lookups_matched
-1
-cluster_lookups_avoided_matched
+select @cluster_lookups;
+@cluster_lookups
 1
+select @cluster_lookups_avoided;
+@cluster_lookups_avoided
+0
 # Eligible, should not increment lookup counter.
+select id, bigfield from prefixinno where bigfield = repeat('b', 8);
 id	bigfield
 8	bbbbbbbb
-cluster_lookups_matched
-1
-cluster_lookups_avoided_matched
+select @cluster_lookups;
+@cluster_lookups
+0
+select @cluster_lookups_avoided;
+@cluster_lookups_avoided
 1
 # Eligible, should not increment lookup counter.
+select id, bigfield from prefixinno where bigfield = repeat('c', 24);
 id	bigfield
 24	cccccccccccccccccccccccc
-cluster_lookups_matched
-1
-cluster_lookups_avoided_matched
+select @cluster_lookups;
+@cluster_lookups
+0
+select @cluster_lookups_avoided;
+@cluster_lookups_avoided
 1
 # Should increment lookup counter.
+select id, bigfield from prefixinno where bigfield = repeat('z', 128);
 id	bigfield
 128	zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
-cluster_lookups_matched
-1
-cluster_lookups_avoided_matched
+select @cluster_lookups;
+@cluster_lookups
 1
+select @cluster_lookups_avoided;
+@cluster_lookups_avoided
+0
 # Disable optimization, confirm we still increment counter.
+set global innodb_prefix_index_cluster_optimization = OFF;
+select id, bigfield from prefixinno where fake_id = 1033;
 id	bigfield
 33	yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy
-cluster_lookups_matched
+select @cluster_lookups;
+@cluster_lookups
+1
+select @cluster_lookups_avoided;
+@cluster_lookups_avoided
+0
+drop table prefixinno;
+# Multi-byte handling case
+set global innodb_prefix_index_cluster_optimization = ON;
+SET NAMES utf8mb4;
+CREATE TABLE t1(
+f1 varchar(10) CHARACTER SET UTF8MB4 COLLATE UTF8MB4_BIN,
+INDEX (f1(3)))ENGINE=INNODB;
+INSERT INTO t1 VALUES('a'), ('cccc'), ('až'), ('cčc'), ('ggᵷg'), ('¢¢');
+INSERT INTO t1 VALUES('தமிழ்'), ('🐱🌑'), ('🌒'), ('🌑');
+INSERT INTO t1 VALUES('😊me'), ('eu€'), ('ls¢');
+# Eligible - record length is shorter than prefix
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 = 'a';
+f1
+a
+select @cluster_lookups;
+@cluster_lookups
+0
+select @cluster_lookups_avoided;
+@cluster_lookups_avoided
+1
+# Not eligible - record length longer than prefix length
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like 'c%';
+f1
+cccc
+cčc
+select @cluster_lookups;
+@cluster_lookups
+3
+select @cluster_lookups_avoided;
+@cluster_lookups_avoided
+0
+# Eligible - record length shorter than prefix length
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 = 'až';
+f1
+až
+select @cluster_lookups;
+@cluster_lookups
+0
+select @cluster_lookups_avoided;
+@cluster_lookups_avoided
+1
+# Not eligible - record length longer than prefix length
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 = 'தமிழ்';
+f1
+தமிழ்
+select @cluster_lookups;
+@cluster_lookups
+1
+select @cluster_lookups_avoided;
+@cluster_lookups_avoided
+0
+# Not eligible - record length longer than prefix length
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like 'ggᵷ%';
+f1
+ggᵷg
+select @cluster_lookups;
+@cluster_lookups
+1
+select @cluster_lookups_avoided;
+@cluster_lookups_avoided
+0
+# Not eligible - record length longer than prefix length
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like '😊%';
+f1
+😊me
+select @cluster_lookups;
+@cluster_lookups
+1
+select @cluster_lookups_avoided;
+@cluster_lookups_avoided
+0
+# Not eligible - record length longer than prefix length
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 = 'ls¢';
+f1
+ls¢
+select @cluster_lookups;
+@cluster_lookups
+1
+select @cluster_lookups_avoided;
+@cluster_lookups_avoided
+0
+# Eligible - record length shorter than prefix length
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like '¢¢%';
+f1
+¢¢
+select @cluster_lookups;
+@cluster_lookups
+1
+select @cluster_lookups_avoided;
+@cluster_lookups_avoided
+1
+# Eligible - record length shorter than prefix length
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like '🐱🌑%';
+f1
+🐱🌑
+select @cluster_lookups;
+@cluster_lookups
+1
+select @cluster_lookups_avoided;
+@cluster_lookups_avoided
+1
+# Not eligible - record length longer than prefix length
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like '🌑%';
+f1
+🌑
+select @cluster_lookups;
+@cluster_lookups
+0
+select @cluster_lookups_avoided;
+@cluster_lookups_avoided
+2
+# Not eligible - record length longer than prefix length
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like '🌒%';
+f1
+🌒
+select @cluster_lookups;
+@cluster_lookups
+0
+select @cluster_lookups_avoided;
+@cluster_lookups_avoided
+2
+DROP TABLE t1;
+# Multi-byte with minimum character length > 1 bytes
+CREATE TABLE t1(
+f1 varchar(10) CHARACTER SET UTF16 COLLATE UTF16_BIN,
+INDEX (f1(3)))ENGINE=INNODB;
+INSERT INTO t1 VALUES('a'), ('cccc'), ('až'), ('cčc'), ('ggᵷg'), ('¢¢');
+INSERT INTO t1 VALUES('தமிழ்'), ('🐱🌑'), ('🌒'), ('🌑');
+INSERT INTO t1 VALUES('😊me'), ('eu€'), ('ls¢');
+# Eligible - record length is shorter than prefix
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 = 'a';
+f1
+a
+select @cluster_lookups;
+@cluster_lookups
+0
+select @cluster_lookups_avoided;
+@cluster_lookups_avoided
+1
+# Not eligible - record length longer than prefix length
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like 'c%';
+f1
+cccc
+cčc
+select @cluster_lookups;
+@cluster_lookups
+3
+select @cluster_lookups_avoided;
+@cluster_lookups_avoided
+0
+# Eligible - record length shorter than prefix length
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 = 'až';
+f1
+až
+select @cluster_lookups;
+@cluster_lookups
+0
+select @cluster_lookups_avoided;
+@cluster_lookups_avoided
+1
+# Not eligible - record length longer than prefix length
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 = 'தமிழ்';
+f1
+தமிழ்
+select @cluster_lookups;
+@cluster_lookups
+1
+select @cluster_lookups_avoided;
+@cluster_lookups_avoided
+0
+# Not eligible - record length longer than prefix length
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like 'ggᵷ%';
+f1
+ggᵷg
+select @cluster_lookups;
+@cluster_lookups
+2
+select @cluster_lookups_avoided;
+@cluster_lookups_avoided
+0
+# Not eligible - record length longer than prefix length
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like '😊%';
+f1
+😊me
+select @cluster_lookups;
+@cluster_lookups
+1
+select @cluster_lookups_avoided;
+@cluster_lookups_avoided
+0
+# Not eligible - record length longer than prefix length
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 = 'ls¢';
+f1
+ls¢
+select @cluster_lookups;
+@cluster_lookups
+1
+select @cluster_lookups_avoided;
+@cluster_lookups_avoided
+0
+# Eligible - record length shorter than prefix length
+SELECT f1 FROM t1 FORCE INDEX(`f1`) WHERE f1 like '¢¢%';
+f1
+¢¢
+select @cluster_lookups;
+@cluster_lookups
+1
+select @cluster_lookups_avoided;
+@cluster_lookups_avoided
+1
+# Eligible - record length shorter than prefix length
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like '🐱🌑%';
+f1
+🐱🌑
+select @cluster_lookups;
+@cluster_lookups
+2
+select @cluster_lookups_avoided;
+@cluster_lookups_avoided
+0
+# Eligible - record length is shorter than prefix length
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like '🌑%';
+f1
+🌑
+select @cluster_lookups;
+@cluster_lookups
+0
+select @cluster_lookups_avoided;
+@cluster_lookups_avoided
+2
+# Eligible - record length is shorter than prefix length
+SELECT f1 FROM t1 FORCE INDEX (`f1`) WHERE f1 like '🌒%';
+f1
+🌒
+select @cluster_lookups;
+@cluster_lookups
+1
+select @cluster_lookups_avoided;
+@cluster_lookups_avoided
+1
+DROP TABLE t1;
+CREATE TABLE t1(
+col1 INT,
+col2 BLOB DEFAULT NULL,
+INDEX `idx1`(col2(4), col1))ENGINE=INNODB;
+INSERT INTO t1 VALUES (2, 'test'), (3, repeat('test1', 2000));
+INSERT INTO t1(col1) VALUES(1);
+# Eligible - record length is shorter than prefix length
+SELECT col1 FROM t1 FORCE INDEX (`idx1`) WHERE col2 is NULL;
+col1
 1
-cluster_lookups_avoided_matched
+select @cluster_lookups;
+@cluster_lookups
+0
+select @cluster_lookups_avoided;
+@cluster_lookups_avoided
 1
-# make test suite happy by cleaning up our mess
+# Not eligible - record length longer than prefix index
+SELECT col1 FROM t1 FORCE INDEX (`idx1`) WHERE col2 like 'test1%';
+col1
+3
+select @cluster_lookups;
+@cluster_lookups
+2
+select @cluster_lookups_avoided;
+@cluster_lookups_avoided
+0
+DROP TABLE t1;
+set global innodb_prefix_index_cluster_optimization = OFF;
diff --git a/mysql-test/r/func_date_add.result b/mysql-test/r/func_date_add.result
index fa45353e094..a6201a3c23f 100644
--- a/mysql-test/r/func_date_add.result
+++ b/mysql-test/r/func_date_add.result
@@ -102,6 +102,57 @@ select * from t1 where case a when adddate( '2012-12-12', 7 ) then true end;
 a
 drop table t1;
 End of 5.5 tests
+#
+# Start of 10.1 tests
+#
+#
+# MDEV-14452 Precision in INTERVAL xxx DAY_MICROSECOND parsed wrong?
+#
+SELECT
+DATE_ADD('1000-01-01 00:00:00', INTERVAL '0 00:00:01.5' DAY_MICROSECOND) c1,
+DATE_ADD('1000-01-01 00:00:00', INTERVAL '0 00:00:01.50' DAY_MICROSECOND) c2,
+DATE_ADD('1000-01-01 00:00:00', INTERVAL '0 00:00:01.500' DAY_MICROSECOND) c3,
+DATE_ADD('1000-01-01 00:00:00', INTERVAL '0 00:00:01.5000' DAY_MICROSECOND) c4,
+DATE_ADD('1000-01-01 00:00:00', INTERVAL '0 00:00:01.50000' DAY_MICROSECOND) c5,
+DATE_ADD('1000-01-01 00:00:00', INTERVAL '0 00:00:01.500000' DAY_MICROSECOND) c6,
+DATE_ADD('1000-01-01 00:00:00', INTERVAL '0 00:00:01.5000000' DAY_MICROSECOND) c7,
+DATE_ADD('1000-01-01 00:00:00', INTERVAL '0 00:00:01.50000000' DAY_MICROSECOND) c8,
+DATE_ADD('1000-01-01 00:00:00', INTERVAL '0 00:00:01.500000000' DAY_MICROSECOND) c9,
+DATE_ADD('1000-01-01 00:00:00', INTERVAL '0 00:00:01.5000000000' DAY_MICROSECOND) c10,
+DATE_ADD('1000-01-01 00:00:00', INTERVAL '0 00:00:01.50000000000' DAY_MICROSECOND) c11,
+DATE_ADD('1000-01-01 00:00:00', INTERVAL '0 00:00:01.500000000000' DAY_MICROSECOND) c12,
+DATE_ADD('1000-01-01 00:00:00', INTERVAL '0 00:00:01.5000000000000' DAY_MICROSECOND) c13,
+DATE_ADD('1000-01-01 00:00:00', INTERVAL '0 00:00:01.50000000000000' DAY_MICROSECOND) c14,
+DATE_ADD('1000-01-01 00:00:00', INTERVAL '0 00:00:01.500000000000000' DAY_MICROSECOND) c15,
+DATE_ADD('1000-01-01 00:00:00', INTERVAL '0 00:00:01.5000000000000000' DAY_MICROSECOND) c16,
+DATE_ADD('1000-01-01 00:00:00', INTERVAL '0 00:00:01.50000000000000000' DAY_MICROSECOND) c17,
+DATE_ADD('1000-01-01 00:00:00', INTERVAL '0 00:00:01.500000000000000000' DAY_MICROSECOND) c18,
+DATE_ADD('1000-01-01 00:00:00', INTERVAL '0 00:00:01.5000000000000000000' DAY_MICROSECOND) c19,
+DATE_ADD('1000-01-01 00:00:00', INTERVAL '0 00:00:01.50000000000000000000' DAY_MICROSECOND) c20
+;
+c1	1000-01-01 00:00:01.500000
+c2	1000-01-01 00:00:01.500000
+c3	1000-01-01 00:00:01.500000
+c4	1000-01-01 00:00:01.500000
+c5	1000-01-01 00:00:01.500000
+c6	1000-01-01 00:00:01.500000
+c7	1000-01-01 00:00:01.500000
+c8	1000-01-01 00:00:01.500000
+c9	1000-01-01 00:00:01.500000
+c10	1000-01-01 00:00:01.500000
+c11	1000-01-01 00:00:01.500000
+c12	1000-01-01 00:00:01.500000
+c13	1000-01-01 00:00:01.500000
+c14	1000-01-01 00:00:01.500000
+c15	1000-01-01 00:00:01.500000
+c16	1000-01-01 00:00:01.500000
+c17	1000-01-01 00:00:01.500000
+c18	1000-01-01 00:00:01.500000
+c19	1000-01-01 00:00:01.500000
+c20	NULL
+#
+# End of 10.1 tests
+#
 create or replace view v1 as select 3 & 20010101 + interval 2 day as x;
 show create view v1;
 View	Create View	character_set_client	collation_connection
diff --git a/mysql-test/r/func_json.result b/mysql-test/r/func_json.result
index 4cb0cb43815..9b41c1f9d61 100644
--- a/mysql-test/r/func_json.result
+++ b/mysql-test/r/func_json.result
@@ -740,7 +740,7 @@ select json_extract('{"test":8.437e-5}','$.test');
 json_extract('{"test":8.437e-5}','$.test')
 8.437e-5
 #
-# Start of 10.3 tests
+# End of 10.2 tests
 #
 #
 # MDEV-12854 Synchronize CREATE..SELECT data type and result set metadata data type for INT functions
@@ -765,3 +765,6 @@ def					json_length	3	10	1	Y	32896	0	63
 def					json_depnth	3	10	1	N	32897	0	63
 json_length	json_depnth
 2	3
+#
+# End of 10.3 tests
+#
diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result
index 477a6a4041a..2772f850ce9 100644
--- a/mysql-test/r/func_time.result
+++ b/mysql-test/r/func_time.result
@@ -2905,6 +2905,30 @@ NULL
 Warnings:
 Warning	1441	Datetime function: datetime field overflow
 #
+# MDEV-13202 Assertion `ltime->neg == 0' failed in date_to_datetime
+#
+CREATE TABLE t1 (i INT, d DATE);
+INSERT INTO t1 VALUES (1, '1970-01-01');
+SELECT MAX(NULLIF(i,1)) FROM t1 ORDER BY DATE_SUB(d,INTERVAL 17300000 HOUR);
+MAX(NULLIF(i,1))
+NULL
+Warnings:
+Warning	1441	Datetime function: datetime field overflow
+DROP TABLE t1;
+CREATE TABLE t1 (i INT, d DATE);
+INSERT INTO t1 VALUES (1, '1970-01-01');
+SELECT CONCAT(DATE_SUB(d, INTERVAL 17300000 HOUR)) FROM t1;
+CONCAT(DATE_SUB(d, INTERVAL 17300000 HOUR))
+NULL
+Warnings:
+Warning	1441	Datetime function: datetime field overflow
+DROP TABLE t1;
+SELECT CONCAT(DATE_SUB(TIMESTAMP'1970-01-01 00:00:00', INTERVAL 17300000 HOUR));
+CONCAT(DATE_SUB(TIMESTAMP'1970-01-01 00:00:00', INTERVAL 17300000 HOUR))
+NULL
+Warnings:
+Warning	1441	Datetime function: datetime field overflow
+#
 # End of 10.0 tests
 #
 #
diff --git a/mysql-test/r/having.result b/mysql-test/r/having.result
index fe36ef9d442..6c206a1699d 100644
--- a/mysql-test/r/having.result
+++ b/mysql-test/r/having.result
@@ -721,6 +721,20 @@ SELECT * FROM t1 JOIN t2 ON c1 = c2 HAVING c2 > 'a' ORDER BY c2 LIMIT 1;
 c1	c2
 x	x
 DROP TABLE t1,t2;
+#
+# MDEV-6736: Valgrind warnings 'Invalid read' in subselect_engine::calc_const_tables with SQ 
+# in WHERE and HAVING, ORDER BY, materialization+semijoin
+#
+CREATE TABLE t1 (a INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (3),(8);
+CREATE TABLE t2 (b INT) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (2),(1);
+SELECT a FROM t1
+WHERE 9 IN ( SELECT MIN( a ) FROM t1 )
+HAVING a <> ( SELECT COUNT(*) FROM t2 ) 
+ORDER BY a;
+a
+DROP TABLE t1,t2;
 End of 10.0 tests
 #
 # MDEV-10716: Assertion `real_type() != FIELD_ITEM' failed in
diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result
index d27a136a6c4..6753349dece 100644
--- a/mysql-test/r/join_outer.result
+++ b/mysql-test/r/join_outer.result
@@ -2438,11 +2438,27 @@ CREATE TABLE t1 (b1 BIT NOT NULL);
 INSERT INTO t1 VALUES (0),(1);
 CREATE TABLE t2 (b2 BIT NOT NULL);
 INSERT INTO t2 VALUES (0),(1);
-SET SESSION JOIN_CACHE_LEVEL = 3;
+set @save_join_cache_level= @@join_cache_level;
+SET  @@join_cache_level = 3;
 SELECT t1.b1+'0' , t2.b2 + '0' FROM t1 LEFT JOIN t2 ON b1 = b2;
 t1.b1+'0'	t2.b2 + '0'
 0	0
 1	1
 DROP TABLE t1, t2;
+set @join_cache_level= @save_join_cache_level;
+#
+# MDEV-14779: using left join causes incorrect results with materialization and derived tables
+#
+create table t1(id  int);
+insert into t1 values (1),(2);
+create table t2(sid int, id int);
+insert into t2 values (1,1),(2,2);
+select * from t1 t
+left join (select * from t2 where sid in (select max(sid) from t2 where 0=1 group by id)) r
+on t.id=r.id ;
+id	sid	id
+1	NULL	NULL
+2	NULL	NULL
+drop table t1, t2;
 # end of 5.5 tests
 SET optimizer_switch=@save_optimizer_switch;
diff --git a/mysql-test/r/join_outer_jcl6.result b/mysql-test/r/join_outer_jcl6.result
index 72527040301..9ff501baf6a 100644
--- a/mysql-test/r/join_outer_jcl6.result
+++ b/mysql-test/r/join_outer_jcl6.result
@@ -2449,12 +2449,28 @@ CREATE TABLE t1 (b1 BIT NOT NULL);
 INSERT INTO t1 VALUES (0),(1);
 CREATE TABLE t2 (b2 BIT NOT NULL);
 INSERT INTO t2 VALUES (0),(1);
-SET SESSION JOIN_CACHE_LEVEL = 3;
+set @save_join_cache_level= @@join_cache_level;
+SET  @@join_cache_level = 3;
 SELECT t1.b1+'0' , t2.b2 + '0' FROM t1 LEFT JOIN t2 ON b1 = b2;
 t1.b1+'0'	t2.b2 + '0'
 0	0
 1	1
 DROP TABLE t1, t2;
+set @join_cache_level= @save_join_cache_level;
+#
+# MDEV-14779: using left join causes incorrect results with materialization and derived tables
+#
+create table t1(id  int);
+insert into t1 values (1),(2);
+create table t2(sid int, id int);
+insert into t2 values (1,1),(2,2);
+select * from t1 t
+left join (select * from t2 where sid in (select max(sid) from t2 where 0=1 group by id)) r
+on t.id=r.id ;
+id	sid	id
+1	NULL	NULL
+2	NULL	NULL
+drop table t1, t2;
 # end of 5.5 tests
 SET optimizer_switch=@save_optimizer_switch;
 set join_cache_level=default;
diff --git a/mysql-test/r/ps_qc_innodb.result b/mysql-test/r/ps_qc_innodb.result
new file mode 100644
index 00000000000..29c364348dc
--- /dev/null
+++ b/mysql-test/r/ps_qc_innodb.result
@@ -0,0 +1,29 @@
+#
+# MDEV-15492: Subquery crash similar to MDEV-10050
+#
+SET @qcs.save= @@global.query_cache_size, @qct.save= @@global.query_cache_type;
+SET GLOBAL query_cache_size= 512*1024*1024, query_cache_type= ON;
+connect  con1,localhost,root,,test;
+CREATE TABLE t1 (a INT) ENGINE=InnoDB;
+CREATE TABLE t2 (b INT) ENGINE=InnoDB;
+CREATE VIEW v AS select a from t1 join t2;
+PREPARE stmt1 FROM "SELECT * FROM t1 WHERE a in (SELECT a FROM v)";
+connect  con2,localhost,root,,test;
+PREPARE stmt2 FROM "SELECT * FROM t1 WHERE a in (SELECT a FROM v)";
+EXECUTE stmt2;
+a
+connection con1;
+EXECUTE stmt1;
+a
+INSERT INTO t2 VALUES (0);
+EXECUTE stmt1;
+a
+START TRANSACTION;
+EXECUTE stmt1;
+a
+disconnect con1;
+disconnect con2;
+connection default;
+DROP VIEW v;
+DROP TABLE t1, t2;
+SET GLOBAL query_cache_size= @qcs.save, query_cache_type= @qct.save;
diff --git a/mysql-test/r/shutdown.result b/mysql-test/r/shutdown.result
index be2eb16470c..7a69f58ffd9 100644
--- a/mysql-test/r/shutdown.result
+++ b/mysql-test/r/shutdown.result
@@ -13,4 +13,4 @@ drop user user1@localhost;
 #
 # MDEV-8491 - On shutdown, report the user and the host executed that.
 #
-FOUND 2 /mysqld(\.exe)? \(root\[root\] @ localhost \[(::1)?\]\): Normal shutdown/ in mysqld.1.err
+FOUND 2 /mysqld(\.exe)? \(initiated by: root\[root\] @ localhost \[(::1)?\]\): Normal shutdown/ in mysqld.1.err
diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result
index d3f46429cf7..25290650e9b 100644
--- a/mysql-test/r/subselect4.result
+++ b/mysql-test/r/subselect4.result
@@ -2489,6 +2489,17 @@ FROM t2 WHERE b <= 'quux' GROUP BY field;
 field	COUNT(DISTINCT c)
 0	1
 drop table t1,t2;
+#
+# MDEV-15555: select from DUAL where false yielding wrong result when in a IN
+#
+explain 
+SELECT 2 IN (SELECT 2 from DUAL WHERE 1 != 1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+SELECT 2 IN (SELECT 2 from DUAL WHERE 1 != 1);
+2 IN (SELECT 2 from DUAL WHERE 1 != 1)
+0
 SET optimizer_switch= @@global.optimizer_switch;
 set @@tmp_table_size= @@global.tmp_table_size;
 #
diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result
index 6b5db62093e..4d425d0fe5c 100644
--- a/mysql-test/r/subselect_mat.result
+++ b/mysql-test/r/subselect_mat.result
@@ -2757,6 +2757,21 @@ a	b	sq
 4	2	1
 drop table t1, t2;
 #
+# MDEV-15235: Assertion `length > 0' failed in create_ref_for_key
+#
+CREATE TABLE t1 (i INT);
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (f CHAR(1));
+INSERT INTO t2 VALUES ('a'),('b');
+explain
+SELECT * FROM t2 WHERE f IN ( SELECT LEFT('foo',0) FROM t1 ORDER BY 1 );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	
+SELECT * FROM t2 WHERE f IN ( SELECT LEFT('foo',0) FROM t1 ORDER BY 1 );
+f
+DROP TABLE t1, t2;
+#
 # MDEV-9489: Assertion `0' failed in Protocol::end_statement() on
 # UNION ALL
 #
diff --git a/mysql-test/r/type_temporal_innodb.result b/mysql-test/r/type_temporal_innodb.result
index ce2b3a4e53f..b869822722d 100644
--- a/mysql-test/r/type_temporal_innodb.result
+++ b/mysql-test/r/type_temporal_innodb.result
@@ -154,3 +154,9 @@ SELECT 1 FROM t1 WHERE (SELECT a FROM t1 group by c) = b;
 Warnings:
 Warning	1292	Incorrect datetime value: ''
 DROP TABLE t1;
+CREATE TABLE t1 (d DATE) ENGINE=InnoDB;
+INSERT INTO t1 VALUES ('2012-12-21');
+SELECT * FROM t1 WHERE LEAST( UTC_TIME(), d );
+d
+2012-12-21
+DROP TABLE t1;
diff --git a/mysql-test/r/type_time.result b/mysql-test/r/type_time.result
index 3f34ae6c09b..5cdd3b00924 100644
--- a/mysql-test/r/type_time.result
+++ b/mysql-test/r/type_time.result
@@ -822,7 +822,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	8	100.00	Using where
 Warnings:
 Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'10:20:30' and <cache>(octet_length(TIME'10:20:30')) = 30 + rand()
-# Old mode, TIMESTAMP literal, zon-zero YYYYMMDD, no propagation
+# Old mode, TIMESTAMP literal, non-zero YYYYMMDD, no propagation
 SELECT * FROM t1 WHERE a=TIMESTAMP'0000-00-01 10:20:30';
 a
 34:20:30
@@ -860,7 +860,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	8	100.00	Using where
 Warnings:
 Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIME'10:20:30' and <cache>(octet_length(TIME'10:20:30')) = 30 + rand()
-# Old mode, TIMESTAMP-alike literal, zon-zero YYYYMMDD, no propagation
+# Old mode, TIMESTAMP-alike literal, non-zero YYYYMMDD, no propagation
 SELECT * FROM t1 WHERE a='0000-00-01 10:20:30';
 a
 34:20:30
@@ -1215,6 +1215,37 @@ MAX(a)	MAX(COALESCE(a))
 10:20:30	10:20:30
 DROP TABLE t1;
 #
+# MDEV-15321: different results when using value of optimizer_use_condition_selectivity=4 and =1
+#
+SET @save_old_mode=@@old_mode;
+SET @@old_mode=zero_date_time_cast;
+CREATE TABLE t1 (a TIME);
+INSERT INTO t1 VALUES ('0000-00-00 10:20:30'),('0000-00-00 10:20:31');
+INSERT INTO t1 VALUES ('0000-00-01 10:20:30'),('0000-00-01 10:20:31');
+INSERT INTO t1 VALUES ('31 10:20:30'),('32 10:20:30'),('33 10:20:30'),('34 10:20:30');
+SET @save_optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity;
+SET @@optimizer_use_condition_selectivity=1;
+SELECT * FROM t1 WHERE a='0000-00-01 10:20:30' AND LENGTH(a)=8;
+a
+34:20:30
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='0000-00-01 10:20:30' AND LENGTH(a)=8;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	8	100.00	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = '0000-00-01 10:20:30' and octet_length(`test`.`t1`.`a`) = 8
+SET @@optimizer_use_condition_selectivity=4;
+SELECT * FROM t1 WHERE a='0000-00-01 10:20:30' AND LENGTH(a)=8;
+a
+34:20:30
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='0000-00-01 10:20:30' AND LENGTH(a)=8;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	8	100.00	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = '0000-00-01 10:20:30' and octet_length(`test`.`t1`.`a`) = 8
+drop table t1;
+SET @@optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity;
+set @@old_mode= @save_old_mode;
+#
 # End of 10.1 tests
 #
 #