Merge 10.3 into 10.4

33 files changed, 1020 insertions, 107 deletions

diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result
index 2c983e3de3e..a1120ba6de7 100644
--- a/mysql-test/main/derived_cond_pushdown.result
+++ b/mysql-test/main/derived_cond_pushdown.result
@@ -16027,6 +16027,374 @@ a
 aa
 DROP FUNCTION f1;
 #
+# MDEV-17011: condition pushdown into materialized derived used
+#             in INSERT SELECT, multi-table UPDATE and DELETE
+#
+CREATE TABLE t1 (a int ,b int) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(1, 1), (1, 2), (2, 1), (2, 2), (3,1), (3,3), (4,2);
+CREATE TABLE t2 (a int) ENGINE MYISAM;
+INSERT INTO t2 VALUES
+(3), (7), (1), (4), (1);
+CREATE TABLE t3 (a int, b int) ENGINE MYISAM;
+EXPLAIN FORMAT=JSON INSERT INTO t3
+SELECT * FROM (SELECT a, count(*) as c FROM t1 GROUP BY a) t WHERE a<=2;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "<derived2>",
+      "access_type": "ALL",
+      "rows": 7,
+      "filtered": 100,
+      "attached_condition": "t.a <= 2",
+      "materialized": {
+        "query_block": {
+          "select_id": 2,
+          "filesort": {
+            "sort_key": "t1.a",
+            "temporary_table": {
+              "table": {
+                "table_name": "t1",
+                "access_type": "ALL",
+                "rows": 7,
+                "filtered": 100,
+                "attached_condition": "t1.a <= 2"
+              }
+            }
+          }
+        }
+      }
+    }
+  }
+}
+INSERT INTO t3
+SELECT * FROM (SELECT a, count(*) as c FROM t1 GROUP BY a) t WHERE a<=2;
+SELECT * FROM t3;
+a	b
+1	2
+2	2
+EXPLAIN FORMAT=JSON UPDATE t2, (SELECT a, count(*) as c FROM t1 GROUP BY a) t SET t2.a=t.c+10
+WHERE t2.a= t.c and t.a>=3;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "t2",
+      "access_type": "ALL",
+      "rows": 5,
+      "filtered": 100,
+      "attached_condition": "t2.a is not null"
+    },
+    "table": {
+      "table_name": "<derived2>",
+      "access_type": "ref",
+      "possible_keys": ["key0"],
+      "key": "key0",
+      "key_length": "8",
+      "used_key_parts": ["c"],
+      "ref": ["test.t2.a"],
+      "rows": 2,
+      "filtered": 100,
+      "attached_condition": "t2.a = t.c and t.a >= 3",
+      "materialized": {
+        "query_block": {
+          "select_id": 2,
+          "filesort": {
+            "sort_key": "t1.a",
+            "temporary_table": {
+              "table": {
+                "table_name": "t1",
+                "access_type": "ALL",
+                "rows": 7,
+                "filtered": 100,
+                "attached_condition": "t1.a >= 3"
+              }
+            }
+          }
+        }
+      }
+    }
+  }
+}
+UPDATE t2, (SELECT a, count(*) as c FROM t1 GROUP BY a) t SET t2.a=t.c+10
+WHERE t2.a= t.c and t.a>=3;
+SELECT * FROM t2;
+a
+3
+7
+11
+4
+11
+EXPLAIN FORMAT=JSON DELETE t2 FROM t2, (SELECT a, count(*) as c FROM t1 GROUP BY a) t
+WHERE t2.a= t.c+9 and t.a=2;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "t2",
+      "access_type": "ALL",
+      "rows": 5,
+      "filtered": 100
+    },
+    "table": {
+      "table_name": "<derived2>",
+      "access_type": "ALL",
+      "rows": 7,
+      "filtered": 100,
+      "attached_condition": "t.a = 2 and t2.a = t.c + 9",
+      "materialized": {
+        "query_block": {
+          "select_id": 2,
+          "table": {
+            "table_name": "t1",
+            "access_type": "ALL",
+            "rows": 7,
+            "filtered": 100,
+            "attached_condition": "t1.a = 2"
+          }
+        }
+      }
+    }
+  }
+}
+DELETE t2 FROM t2, (SELECT a, count(*) as c FROM t1 GROUP BY a) t
+WHERE t2.a= t.c+9 and t.a=2;
+SELECT * FROM t2;
+a
+3
+7
+4
+DROP TABLE t1,t2,t3;
+#
+# MDEV-16765: pushdown condition with the CASE structure
+#             defined with Item_cond item
+#
+CREATE TABLE t1(a INT, b INT);
+INSERT INTO t1 VALUES (1,2), (3,4), (2,3);
+SELECT *
+FROM
+(
+SELECT CASE WHEN ((tab2.max_a=1) OR (tab2.max_a=2))
+THEN 1 ELSE 0 END AS max_a,b
+FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2
+) AS tab1
+WHERE (tab1.max_a=1);
+max_a	b
+1	2
+1	3
+EXPLAIN FORMAT=JSON SELECT *
+FROM
+(
+SELECT CASE WHEN ((tab2.max_a=1) OR (tab2.max_a=2))
+THEN 1 ELSE 0 END AS max_a,b
+FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2
+) AS tab1
+WHERE (tab1.max_a=1);
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "<derived3>",
+      "access_type": "ALL",
+      "rows": 3,
+      "filtered": 100,
+      "attached_condition": "case when (tab2.max_a = 1 or tab2.max_a = 2) then 1 else 0 end = 1",
+      "materialized": {
+        "query_block": {
+          "select_id": 3,
+          "having_condition": "case when (max_a = 1 or max_a = 2) then 1 else 0 end = 1",
+          "filesort": {
+            "sort_key": "t1.b",
+            "temporary_table": {
+              "table": {
+                "table_name": "t1",
+                "access_type": "ALL",
+                "rows": 3,
+                "filtered": 100
+              }
+            }
+          }
+        }
+      }
+    }
+  }
+}
+SELECT *
+FROM
+(
+SELECT CASE WHEN ((tab2.max_a=1) OR ((tab2.max_a>2) AND (tab2.max_a<4)))
+THEN 1 ELSE 0 END AS max_a,b
+FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2
+) AS tab1
+WHERE (tab1.max_a=1);
+max_a	b
+1	2
+1	4
+EXPLAIN FORMAT=JSON SELECT *
+FROM
+(
+SELECT CASE WHEN ((tab2.max_a=1) OR ((tab2.max_a>2) AND (tab2.max_a<4)))
+THEN 1 ELSE 0 END AS max_a,b
+FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2
+) AS tab1
+WHERE (tab1.max_a=1);
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "<derived3>",
+      "access_type": "ALL",
+      "rows": 3,
+      "filtered": 100,
+      "attached_condition": "case when (tab2.max_a = 1 or tab2.max_a > 2 and tab2.max_a < 4) then 1 else 0 end = 1",
+      "materialized": {
+        "query_block": {
+          "select_id": 3,
+          "having_condition": "case when (max_a = 1 or max_a > 2 and max_a < 4) then 1 else 0 end = 1",
+          "filesort": {
+            "sort_key": "t1.b",
+            "temporary_table": {
+              "table": {
+                "table_name": "t1",
+                "access_type": "ALL",
+                "rows": 3,
+                "filtered": 100
+              }
+            }
+          }
+        }
+      }
+    }
+  }
+}
+SELECT *
+FROM
+(
+SELECT CASE WHEN ((tab2.max_a>1) AND ((tab2.max_a=2) OR (tab2.max_a>2)))
+THEN 1 ELSE 0 END AS max_a,b
+FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2
+) AS tab1
+WHERE (tab1.max_a=1);
+max_a	b
+1	3
+1	4
+EXPLAIN FORMAT=JSON SELECT *
+FROM
+(
+SELECT CASE WHEN ((tab2.max_a>1) AND ((tab2.max_a=2) OR (tab2.max_a>2)))
+THEN 1 ELSE 0 END AS max_a,b
+FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2
+) AS tab1
+WHERE (tab1.max_a=1);
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "<derived3>",
+      "access_type": "ALL",
+      "rows": 3,
+      "filtered": 100,
+      "attached_condition": "case when (tab2.max_a > 1 and (tab2.max_a = 2 or tab2.max_a > 2)) then 1 else 0 end = 1",
+      "materialized": {
+        "query_block": {
+          "select_id": 3,
+          "having_condition": "case when (max_a > 1 and (max_a = 2 or max_a > 2)) then 1 else 0 end = 1",
+          "filesort": {
+            "sort_key": "t1.b",
+            "temporary_table": {
+              "table": {
+                "table_name": "t1",
+                "access_type": "ALL",
+                "rows": 3,
+                "filtered": 100
+              }
+            }
+          }
+        }
+      }
+    }
+  }
+}
+SELECT *
+FROM
+(
+SELECT CASE WHEN ((tab2.b=2) OR (tab2.b=4))
+THEN 1 ELSE 0 END AS max_a,b
+FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2
+) AS tab1
+WHERE (tab1.max_a=1);
+max_a	b
+1	2
+1	4
+EXPLAIN FORMAT=JSON SELECT *
+FROM
+(
+SELECT CASE WHEN ((tab2.b=2) OR (tab2.b=4))
+THEN 1 ELSE 0 END AS max_a,b
+FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2
+) AS tab1
+WHERE (tab1.max_a=1);
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "<derived3>",
+      "access_type": "ALL",
+      "rows": 3,
+      "filtered": 100,
+      "attached_condition": "case when (tab2.b = 2 or tab2.b = 4) then 1 else 0 end = 1",
+      "materialized": {
+        "query_block": {
+          "select_id": 3,
+          "filesort": {
+            "sort_key": "t1.b",
+            "temporary_table": {
+              "table": {
+                "table_name": "t1",
+                "access_type": "ALL",
+                "rows": 3,
+                "filtered": 100,
+                "attached_condition": "case when (t1.b = 2 or t1.b = 4) then 1 else 0 end = 1"
+              }
+            }
+          }
+        }
+      }
+    }
+  }
+}
+DROP TABLE t1;
+#
+# MDEV-16803: pushdown condition with IN predicate in the derived table
+#             defined with several SELECT statements
+#
+CREATE TABLE t1 (a INT, b INT);
+INSERT INTO t1 VALUES (1,2),(3,2),(1,1);
+SELECT * FROM
+(
+SELECT a,b,1 as c
+FROM t1
+UNION ALL
+SELECT a,b,2 as c
+FROM t1
+) AS tab
+WHERE ((a,b) IN ((1,2),(3,2)));
+a	b	c
+1	2	1
+3	2	1
+1	2	2
+3	2	2
+DROP TABLE t1;
+# Start of 10.3 tests
+#
 # MDEV-16801: splittable materialized derived/views with
 #             one grouping field from table without keys
 #
diff --git a/mysql-test/main/derived_cond_pushdown.test b/mysql-test/main/derived_cond_pushdown.test
index b75c56e9ff2..61541d2cdf5 100644
--- a/mysql-test/main/derived_cond_pushdown.test
+++ b/mysql-test/main/derived_cond_pushdown.test
@@ -2988,6 +2988,130 @@ SELECT a FROM (SELECT "aa" a) t WHERE f1(t.a, (SELECT MAX('aa') FROM DUAL LIMIT
 DROP FUNCTION f1;
 
 --echo #
+--echo # MDEV-17011: condition pushdown into materialized derived used
+--echo #             in INSERT SELECT, multi-table UPDATE and DELETE
+--echo #
+
+CREATE TABLE t1 (a int ,b int) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+  (1, 1), (1, 2), (2, 1), (2, 2), (3,1), (3,3), (4,2);
+
+CREATE TABLE t2 (a int) ENGINE MYISAM;
+INSERT INTO t2 VALUES
+  (3), (7), (1), (4), (1);
+
+CREATE TABLE t3 (a int, b int) ENGINE MYISAM;
+
+let $q1=
+INSERT INTO t3
+SELECT * FROM (SELECT a, count(*) as c FROM t1 GROUP BY a) t WHERE a<=2;
+
+eval EXPLAIN FORMAT=JSON $q1;
+eval $q1;
+
+SELECT * FROM t3;
+
+let $q2=
+UPDATE t2, (SELECT a, count(*) as c FROM t1 GROUP BY a) t SET t2.a=t.c+10
+  WHERE t2.a= t.c and t.a>=3;
+
+eval EXPLAIN FORMAT=JSON $q2;
+eval $q2;
+
+SELECT * FROM t2;
+
+let $q3=
+DELETE t2 FROM t2, (SELECT a, count(*) as c FROM t1 GROUP BY a) t
+  WHERE t2.a= t.c+9 and t.a=2;
+
+eval EXPLAIN FORMAT=JSON $q3;
+eval $q3;
+
+SELECT * FROM t2;
+
+DROP TABLE t1,t2,t3;
+
+--echo #
+--echo # MDEV-16765: pushdown condition with the CASE structure
+--echo #             defined with Item_cond item
+--echo #
+
+CREATE TABLE t1(a INT, b INT);
+INSERT INTO t1 VALUES (1,2), (3,4), (2,3);
+
+LET $query=
+SELECT *
+FROM
+(
+  SELECT CASE WHEN ((tab2.max_a=1) OR (tab2.max_a=2))
+    THEN 1 ELSE 0 END AS max_a,b
+  FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2
+) AS tab1
+WHERE (tab1.max_a=1);
+EVAL $query;
+EVAL EXPLAIN FORMAT=JSON $query;
+
+LET $query=
+SELECT *
+FROM
+(
+  SELECT CASE WHEN ((tab2.max_a=1) OR ((tab2.max_a>2) AND (tab2.max_a<4)))
+    THEN 1 ELSE 0 END AS max_a,b
+  FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2
+) AS tab1
+WHERE (tab1.max_a=1);
+EVAL $query;
+EVAL EXPLAIN FORMAT=JSON $query;
+
+LET $query=
+SELECT *
+FROM
+(
+  SELECT CASE WHEN ((tab2.max_a>1) AND ((tab2.max_a=2) OR (tab2.max_a>2)))
+    THEN 1 ELSE 0 END AS max_a,b
+  FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2
+) AS tab1
+WHERE (tab1.max_a=1);
+EVAL $query;
+EVAL EXPLAIN FORMAT=JSON $query;
+
+LET $query=
+SELECT *
+FROM
+(
+  SELECT CASE WHEN ((tab2.b=2) OR (tab2.b=4))
+    THEN 1 ELSE 0 END AS max_a,b
+  FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2
+) AS tab1
+WHERE (tab1.max_a=1);
+EVAL $query;
+EVAL EXPLAIN FORMAT=JSON $query;
+
+DROP TABLE t1;
+
+--echo #
+--echo # MDEV-16803: pushdown condition with IN predicate in the derived table
+--echo #             defined with several SELECT statements
+--echo #
+
+CREATE TABLE t1 (a INT, b INT);
+INSERT INTO t1 VALUES (1,2),(3,2),(1,1);
+
+SELECT * FROM
+(
+  SELECT a,b,1 as c
+  FROM t1
+  UNION ALL
+  SELECT a,b,2 as c
+  FROM t1
+) AS tab
+WHERE ((a,b) IN ((1,2),(3,2)));
+
+DROP TABLE t1;
+
+--echo # Start of 10.3 tests
+
+--echo #
 --echo # MDEV-16801: splittable materialized derived/views with
 --echo #             one grouping field from table without keys
 --echo #
diff --git a/mysql-test/main/func_isnull.result b/mysql-test/main/func_isnull.result
index 15d87997e29..8cbd528f2de 100644
--- a/mysql-test/main/func_isnull.result
+++ b/mysql-test/main/func_isnull.result
@@ -106,5 +106,25 @@ Note	1003	select `test`.`t2`.`d1` AS `d1`,`test`.`t1`.`d1` AS `d1` from `test`.`
 DROP VIEW v1;
 DROP TABLE t1,t2;
 #
+# MDEV-15475: Assertion `!table || (!table->read_set ||
+# bitmap_is_set(table->read_set, field_index))'
+# failed on EXPLAIN EXTENDED with constant table and view
+#
+CREATE TABLE t1 (pk INT PRIMARY KEY) ENGINE=MyISAM;
+CREATE VIEW v1 AS SELECT * FROM t1;
+INSERT INTO t1 VALUES (1);
+EXPLAIN EXTENDED SELECT ISNULL(pk) FROM v1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	100.00	
+Warnings:
+Note	1003	select /*always not null*/ 1 is null AS `ISNULL(pk)` from dual
+EXPLAIN EXTENDED SELECT IFNULL(pk,0) FROM v1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	100.00	
+Warnings:
+Note	1003	select ifnull(1,0) AS `IFNULL(pk,0)` from dual
+DROP VIEW v1;
+DROP TABLE t1;
+#
 # End of 5.5 tests
 #
diff --git a/mysql-test/main/func_isnull.test b/mysql-test/main/func_isnull.test
index 4c59fa3cbe8..7d1a7e83a1a 100644
--- a/mysql-test/main/func_isnull.test
+++ b/mysql-test/main/func_isnull.test
@@ -83,6 +83,22 @@ SELECT * FROM t2 LEFT JOIN v1 ON t2.d1=v1.d1 WHERE v1.d1 IS NULL;
 DROP VIEW v1;
 DROP TABLE t1,t2;
 
+
+--echo #
+--echo # MDEV-15475: Assertion `!table || (!table->read_set ||
+--echo # bitmap_is_set(table->read_set, field_index))'
+--echo # failed on EXPLAIN EXTENDED with constant table and view
+--echo #
+
+CREATE TABLE t1 (pk INT PRIMARY KEY) ENGINE=MyISAM;
+CREATE VIEW v1 AS SELECT * FROM t1;
+INSERT INTO t1 VALUES (1);
+EXPLAIN EXTENDED SELECT ISNULL(pk) FROM v1;
+EXPLAIN EXTENDED SELECT IFNULL(pk,0) FROM v1;
+# Cleanup
+DROP VIEW v1;
+DROP TABLE t1;
+
 --echo #
 --echo # End of 5.5 tests
 --echo #
diff --git a/mysql-test/main/func_time.result b/mysql-test/main/func_time.result
index b6f9ca8e628..840db182fe7 100644
--- a/mysql-test/main/func_time.result
+++ b/mysql-test/main/func_time.result
@@ -2805,6 +2805,11 @@ SEC_TO_TIME(MAKEDATE(0,RAND(~0)))
 838:59:59
 Warnings:
 Warning	1292	Truncated incorrect seconds value: '20000101'
+SELECT PERIOD_DIFF(2018, AES_ENCRYPT('Rae Bareli', 'Rae Bareli'));
+PERIOD_DIFF(2018, AES_ENCRYPT('Rae Bareli', 'Rae Bareli'))
+24257
+Warnings:
+Warning	1292	Truncated incorrect INTEGER value: '-3S\xFA\xDE?\x00\x00\xCA\xB3\xEEE\xA4\xD1\xC1\xA8'
 #
 # End of 5.5 tests
 #
@@ -3578,6 +3583,7 @@ DROP TABLE t1;
 #
 # MDEV-15363 Wrong result for CAST(LAST_DAY(TIME'00:00:00') AS TIME)
 #
+set timestamp=unix_timestamp('2018-08-02 10:10:10');
 SELECT
 LAST_DAY(TIME'00:00:00') AS c1,
 CAST(CAST(LAST_DAY(TIME'00:00:00') AS DATE) AS TIME) AS c2,
diff --git a/mysql-test/main/func_time.test b/mysql-test/main/func_time.test
index b03c14fb8ba..aebe3df761f 100644
--- a/mysql-test/main/func_time.test
+++ b/mysql-test/main/func_time.test
@@ -1705,6 +1705,10 @@ DO TO_DAYS(SEC_TO_TIME(TIME(CEILING(UUID()))));
 DO TO_DAYS(SEC_TO_TIME(MAKEDATE('',RAND(~('')))));
 SELECT SEC_TO_TIME(MAKEDATE(0,RAND(~0)));
 
+#
+# MDEV-16810 AddressSanitizer: stack-buffer-overflow in int10_to_str
+#
+SELECT PERIOD_DIFF(2018, AES_ENCRYPT('Rae Bareli', 'Rae Bareli'));
 
 --echo #
 --echo # End of 5.5 tests
@@ -2158,6 +2162,7 @@ DROP TABLE t1;
 --echo # MDEV-15363 Wrong result for CAST(LAST_DAY(TIME'00:00:00') AS TIME)
 --echo #
 
+set timestamp=unix_timestamp('2018-08-02 10:10:10');
 SELECT
   LAST_DAY(TIME'00:00:00') AS c1,
   CAST(CAST(LAST_DAY(TIME'00:00:00') AS DATE) AS TIME) AS c2,
diff --git a/mysql-test/main/group_min_max.result b/mysql-test/main/group_min_max.result
index 36a44b05817..b3b660c4170 100644
--- a/mysql-test/main/group_min_max.result
+++ b/mysql-test/main/group_min_max.result
@@ -3893,5 +3893,22 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index	a	a	13	NULL	2	Using where; Using index
 drop table t1;
 #
+# MDEV-15433: Optimizer does not use group by optimization with distinct
+#
+CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, a INT NOT NULL, KEY(a));
+OPTIMIZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	optimize	status	OK
+EXPLAIN SELECT DISTINCT a FROM t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	NULL	a	4	NULL	5	Using index for group-by
+SELECT DISTINCT a FROM t1;
+a
+1
+2
+3
+4
+drop table t1;
+#
 # End of 10.1 tests
 #
diff --git a/mysql-test/main/group_min_max.test b/mysql-test/main/group_min_max.test
index b0bc42d7f8c..adad9073235 100644
--- a/mysql-test/main/group_min_max.test
+++ b/mysql-test/main/group_min_max.test
@@ -1609,5 +1609,32 @@ explain select min(a) from t1 where a between "abbbbbbbbbbbbbbbbbbbb" and "Cafe2
 drop table t1;
 
 --echo #
+--echo # MDEV-15433: Optimizer does not use group by optimization with distinct
+--echo #
+
+CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, a INT NOT NULL, KEY(a));
+--disable_query_log
+INSERT INTO t1(a) VALUES (1), (2), (3), (4);
+INSERT INTO t1(a) SELECT a FROM t1;
+INSERT INTO t1(a) SELECT a FROM t1;
+INSERT INTO t1(a) SELECT a FROM t1;
+INSERT INTO t1(a) SELECT a FROM t1;
+INSERT INTO t1(a) SELECT a FROM t1;
+INSERT INTO t1(a) SELECT a FROM t1;
+INSERT INTO t1(a) SELECT a FROM t1;
+INSERT INTO t1(a) SELECT a FROM t1;
+INSERT INTO t1(a) SELECT a FROM t1;
+INSERT INTO t1(a) SELECT a FROM t1;
+INSERT INTO t1(a) SELECT a FROM t1;
+INSERT INTO t1(a) SELECT a FROM t1;
+INSERT INTO t1(a) SELECT a FROM t1;
+INSERT INTO t1(a) SELECT a FROM t1;
+--enable_query_log
+OPTIMIZE TABLE t1;
+EXPLAIN SELECT DISTINCT a FROM t1;
+SELECT DISTINCT a FROM t1;
+drop table t1;
+
+--echo #
 --echo # End of 10.1 tests
 --echo #
diff --git a/mysql-test/main/information_schema-big.result b/mysql-test/main/information_schema-big.result
index cbd8191bc54..d4aa6deb2e2 100644
--- a/mysql-test/main/information_schema-big.result
+++ b/mysql-test/main/information_schema-big.result
@@ -20,6 +20,7 @@ table_name	column_name
 ALL_PLUGINS	PLUGIN_NAME
 APPLICABLE_ROLES	GRANTEE
 CHARACTER_SETS	CHARACTER_SET_NAME
+CHECK_CONSTRAINTS	CONSTRAINT_SCHEMA
 CLIENT_STATISTICS	CLIENT
 COLLATIONS	COLLATION_NAME
 COLLATION_CHARACTER_SET_APPLICABILITY	COLLATION_NAME
@@ -77,6 +78,7 @@ table_name	column_name
 ALL_PLUGINS	PLUGIN_NAME
 APPLICABLE_ROLES	GRANTEE
 CHARACTER_SETS	CHARACTER_SET_NAME
+CHECK_CONSTRAINTS	CONSTRAINT_SCHEMA
 CLIENT_STATISTICS	CLIENT
 COLLATIONS	COLLATION_NAME
 COLLATION_CHARACTER_SET_APPLICABILITY	COLLATION_NAME
diff --git a/mysql-test/main/information_schema.result b/mysql-test/main/information_schema.result
index 6e987dd3266..b6e331a0382 100644
--- a/mysql-test/main/information_schema.result
+++ b/mysql-test/main/information_schema.result
@@ -51,6 +51,7 @@ c
 ALL_PLUGINS
 APPLICABLE_ROLES
 CHARACTER_SETS
+CHECK_CONSTRAINTS
 CLIENT_STATISTICS
 COLLATIONS
 COLLATION_CHARACTER_SET_APPLICABILITY
@@ -937,6 +938,7 @@ connection user10261;
 SELECT TABLE_NAME, COLUMN_NAME, PRIVILEGES FROM INFORMATION_SCHEMA.COLUMNS 
 where COLUMN_NAME='TABLE_NAME' and table_name not like 'innodb%';
 TABLE_NAME	COLUMN_NAME	PRIVILEGES
+CHECK_CONSTRAINTS	TABLE_NAME	select
 COLUMNS	TABLE_NAME	select
 COLUMN_PRIVILEGES	TABLE_NAME	select
 FILES	TABLE_NAME	select
diff --git a/mysql-test/main/information_schema_all_engines.result b/mysql-test/main/information_schema_all_engines.result
index 126a6f4bccd..0ce9f15f753 100644
--- a/mysql-test/main/information_schema_all_engines.result
+++ b/mysql-test/main/information_schema_all_engines.result
@@ -4,6 +4,7 @@ Tables_in_information_schema
 ALL_PLUGINS
 APPLICABLE_ROLES
 CHARACTER_SETS
+CHECK_CONSTRAINTS
 CLIENT_STATISTICS
 COLLATIONS
 COLLATION_CHARACTER_SET_APPLICABILITY
@@ -83,6 +84,7 @@ table_name	column_name
 ALL_PLUGINS	PLUGIN_NAME
 APPLICABLE_ROLES	GRANTEE
 CHARACTER_SETS	CHARACTER_SET_NAME
+CHECK_CONSTRAINTS	CONSTRAINT_SCHEMA
 CLIENT_STATISTICS	CLIENT
 COLLATIONS	COLLATION_NAME
 COLLATION_CHARACTER_SET_APPLICABILITY	COLLATION_NAME
@@ -162,6 +164,7 @@ table_name	column_name
 ALL_PLUGINS	PLUGIN_NAME
 APPLICABLE_ROLES	GRANTEE
 CHARACTER_SETS	CHARACTER_SET_NAME
+CHECK_CONSTRAINTS	CONSTRAINT_SCHEMA
 CLIENT_STATISTICS	CLIENT
 COLLATIONS	COLLATION_NAME
 COLLATION_CHARACTER_SET_APPLICABILITY	COLLATION_NAME
@@ -247,6 +250,7 @@ table_name	group_concat(t.table_schema, '.', t.table_name)	num1
 ALL_PLUGINS	information_schema.ALL_PLUGINS	1
 APPLICABLE_ROLES	information_schema.APPLICABLE_ROLES	1
 CHARACTER_SETS	information_schema.CHARACTER_SETS	1
+CHECK_CONSTRAINTS	information_schema.CHECK_CONSTRAINTS	1
 CLIENT_STATISTICS	information_schema.CLIENT_STATISTICS	1
 COLLATIONS	information_schema.COLLATIONS	1
 COLLATION_CHARACTER_SET_APPLICABILITY	information_schema.COLLATION_CHARACTER_SET_APPLICABILITY	1
@@ -315,6 +319,7 @@ Database: information_schema
 | ALL_PLUGINS                           |
 | APPLICABLE_ROLES                      |
 | CHARACTER_SETS                        |
+| CHECK_CONSTRAINTS                     |
 | CLIENT_STATISTICS                     |
 | COLLATIONS                            |
 | COLLATION_CHARACTER_SET_APPLICABILITY |
@@ -384,6 +389,7 @@ Database: INFORMATION_SCHEMA
 | ALL_PLUGINS                           |
 | APPLICABLE_ROLES                      |
 | CHARACTER_SETS                        |
+| CHECK_CONSTRAINTS                     |
 | CLIENT_STATISTICS                     |
 | COLLATIONS                            |
 | COLLATION_CHARACTER_SET_APPLICABILITY |
@@ -453,5 +459,5 @@ Wildcard: inf_rmation_schema
 | information_schema |
 SELECT table_schema, count(*) FROM information_schema.TABLES WHERE table_schema IN ('mysql', 'INFORMATION_SCHEMA', 'test', 'mysqltest') GROUP BY TABLE_SCHEMA;
 table_schema	count(*)
-information_schema	64
+information_schema	65
 mysql	30
diff --git a/mysql-test/main/mysql.result b/mysql-test/main/mysql.result
index 09f014da627..06ef7e95bc9 100644
--- a/mysql-test/main/mysql.result
+++ b/mysql-test/main/mysql.result
@@ -538,8 +538,6 @@ a
 #
 # End of 10.1 tests
 #
-ERROR 1300 (HY000): Invalid utf8 character string: 'test\xF0\x9F\x98\x81 '
-ERROR 1300 (HY000): Invalid binary character string: 'test\xF0\x9F\x98\x81 '
 ERROR 1300 (HY000) at line 2: Invalid utf8 character string: 'test\xF0\x9F\x98\x81'
 set GLOBAL sql_mode=default;
 
diff --git a/mysql-test/main/mysql.test b/mysql-test/main/mysql.test
index 01953ba2112..c65860eb822 100644
--- a/mysql-test/main/mysql.test
+++ b/mysql-test/main/mysql.test
@@ -638,10 +638,7 @@ EOF
 --echo # End of 10.1 tests
 --echo #
 
---error 1
---exec $MYSQL --default-character-set=utf8 -e "select 1" "test😁 " 2>&1
---error 1
---exec $MYSQL --default-character-set=binary -e "select 1" "test😁 " 2>&1
+
 --write_file $MYSQLTEST_VARDIR/tmp/mdev-6572.sql
 SET NAMES utf8;
 USE test😁 ;
diff --git a/mysql-test/main/mysql_not_windows.result b/mysql-test/main/mysql_not_windows.result
index 1df62d9a12d..96210a366a6 100644
--- a/mysql-test/main/mysql_not_windows.result
+++ b/mysql-test/main/mysql_not_windows.result
@@ -9,3 +9,5 @@ End of tests
 2
 X
 3
+ERROR 1300 (HY000): Invalid utf8 character string: 'test\xF0\x9F\x98\x81 '
+ERROR 1300 (HY000): Invalid binary character string: 'test\xF0\x9F\x98\x81 '
diff --git a/mysql-test/main/mysql_not_windows.test b/mysql-test/main/mysql_not_windows.test
index 591de74cbbf..816160c4f3e 100644
--- a/mysql-test/main/mysql_not_windows.test
+++ b/mysql-test/main/mysql_not_windows.test
@@ -22,3 +22,10 @@ exec $MYSQL test -e "select
 let $query = select 3
     as X;
 exec $MYSQL test -e "$query";
+
+# Not ran on Windows, since non-ASCII does not work on command line.
+# (MDEV-16220)
+--error 1
+--exec $MYSQL --default-character-set=utf8 -e "select 1" "test😁 " 2>&1
+--error 1
+--exec $MYSQL --default-character-set=binary -e "select 1" "test😁 " 2>&1
diff --git a/mysql-test/main/mysqld--help.result b/mysql-test/main/mysqld--help.result
index ac490afe229..8032702ffbb 100644
--- a/mysql-test/main/mysqld--help.result
+++ b/mysql-test/main/mysqld--help.result
@@ -231,6 +231,11 @@ The following specify which files/extra groups are read (specified before remain
  cache, etc)
  --enforce-storage-engine=name 
  Force the use of a storage engine for new tables
+ --eq-range-index-dive-limit=# 
+ The optimizer will use existing index statistics instead
+ of doing index dives for equality ranges if the number of
+ equality ranges for the index is larger than or equal to
+ this number. If set to 0, index dives are always used.
  --event-scheduler[=name] 
  Enable the event scheduler. Possible values are ON, OFF,
  and DISABLED (keep the event scheduler completely
@@ -1404,6 +1409,7 @@ encrypt-binlog FALSE
 encrypt-tmp-disk-tables FALSE
 encrypt-tmp-files FALSE
 enforce-storage-engine (No default value)
+eq-range-index-dive-limit 0
 event-scheduler OFF
 expensive-subquery-limit 100
 expire-logs-days 0
diff --git a/mysql-test/main/opt_tvc.result b/mysql-test/main/opt_tvc.result
index 0ecae5bf157..fdbd932a433 100644
--- a/mysql-test/main/opt_tvc.result
+++ b/mysql-test/main/opt_tvc.result
@@ -486,34 +486,32 @@ a	b
 deallocate prepare stmt;
 # use inside out access from tvc rows
 set @@in_predicate_conversion_threshold= default;
-select * from t3 where a in (1,4,10);
+select * from t3 where a in (1,4);
 a	b
 1	abc
 1	todd
 1	sm
 4	yq
-10	abc
-explain extended select * from t3 where a in (1,4,10);
+explain extended select * from t3 where a in (1,4);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t3	range	idx	idx	5	NULL	5	100.00	Using index condition
+1	SIMPLE	t3	range	idx	idx	5	NULL	4	100.00	Using index condition
 Warnings:
-Note	1003	select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t3` where `test`.`t3`.`a` in (1,4,10)
+Note	1003	select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t3` where `test`.`t3`.`a` in (1,4)
 set @@in_predicate_conversion_threshold= 2;
-select * from t3 where a in (1,4,10);
+select * from t3 where a in (1,4);
 a	b
 1	abc
 1	todd
 1	sm
 4	yq
-10	abc
-explain extended select * from t3 where a in (1,4,10);
+explain extended select * from t3 where a in (1,4);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	2	100.00	
 1	PRIMARY	t3	ref	idx	idx	5	tvc_0.1	3	100.00	
 2	MATERIALIZED	<derived3>	ALL	NULL	NULL	NULL	NULL	2	100.00	
 3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 Warnings:
-Note	1003	/* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t3` semi join ((values (1),(4),(10)) `tvc_0`) where `test`.`t3`.`a` = `tvc_0`.`1`
+Note	1003	/* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t3` semi join ((values (1),(4)) `tvc_0`) where `test`.`t3`.`a` = `tvc_0`.`1`
 # use vectors in IN predeicate
 set @@in_predicate_conversion_threshold= 4;
 select * from t1 where (a,b) in ((1,2),(3,4));
@@ -540,9 +538,9 @@ explain extended select * from t2
 where (a,b) in ((1,2),(8,9)) and
 (a,c) in ((1,3),(8,0),(5,1));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	2	100.00	
+1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	3	100.00	
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where; Using join buffer (flat, BNL join)
-2	MATERIALIZED	<derived3>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+2	MATERIALIZED	<derived3>	ALL	NULL	NULL	NULL	NULL	3	100.00	
 3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 Warnings:
 Note	1003	/* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` semi join ((values (1,3),(8,0),(5,1)) `tvc_0`) where `test`.`t2`.`a` = `tvc_0`.`1` and `test`.`t2`.`c` = `tvc_0`.`3` and (`tvc_0`.`1`,`test`.`t2`.`b`) in (<cache>((1,2)),<cache>((8,9)))
@@ -570,7 +568,7 @@ explain extended select * from t1
 where (a,b) not in ((1,2),(8,9), (5,1));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
-2	MATERIALIZED	<derived3>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+2	MATERIALIZED	<derived3>	ALL	NULL	NULL	NULL	NULL	3	100.00	
 3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 Warnings:
 Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where !<expr_cache><`test`.`t1`.`a`,`test`.`t1`.`b`>(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),(`test`.`t1`.`a`,`test`.`t1`.`b`) in ( <materialize> (/* select#2 */ select `tvc_0`.`1`,`tvc_0`.`2` from (values (1,2),(8,9),(5,1)) `tvc_0` ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery2>`.`1` and `test`.`t1`.`b` = `<subquery2>`.`2`))))
@@ -578,7 +576,7 @@ explain extended select * from t1
 where (a,b) not in (select * from (values (1,2),(8,9), (5,1)) as tvc_0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
-2	MATERIALIZED	<derived3>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+2	MATERIALIZED	<derived3>	ALL	NULL	NULL	NULL	NULL	3	100.00	
 3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 Warnings:
 Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where !<expr_cache><`test`.`t1`.`a`,`test`.`t1`.`b`>(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),(`test`.`t1`.`a`,`test`.`t1`.`b`) in ( <materialize> (/* select#2 */ select `tvc_0`.`1`,`tvc_0`.`2` from (values (1,2),(8,9),(5,1)) `tvc_0` ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery2>`.`1` and `test`.`t1`.`b` = `<subquery2>`.`2`))))
@@ -592,7 +590,7 @@ explain extended select * from t1
 where b < 7 and (a,b) not in ((1,2),(8,9), (5,1));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
-2	MATERIALIZED	<derived3>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+2	MATERIALIZED	<derived3>	ALL	NULL	NULL	NULL	NULL	3	100.00	
 3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 Warnings:
 Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`b` < 7 and !<expr_cache><`test`.`t1`.`a`,`test`.`t1`.`b`>(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),(`test`.`t1`.`a`,`test`.`t1`.`b`) in ( <materialize> (/* select#2 */ select `tvc_0`.`1`,`tvc_0`.`2` from (values (1,2),(8,9),(5,1)) `tvc_0` ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery2>`.`1` and `test`.`t1`.`b` = `<subquery2>`.`2`))))
@@ -608,7 +606,7 @@ explain extended select * from t2
 where (a,c) not in ((1,2),(8,9), (5,1));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
-2	MATERIALIZED	<derived3>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+2	MATERIALIZED	<derived3>	ALL	NULL	NULL	NULL	NULL	3	100.00	
 3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 Warnings:
 Note	1003	/* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where !<expr_cache><`test`.`t2`.`a`,`test`.`t2`.`c`>(<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`c`),(`test`.`t2`.`a`,`test`.`t2`.`c`) in ( <materialize> (/* select#2 */ select `tvc_0`.`1`,`tvc_0`.`2` from (values (1,2),(8,9),(5,1)) `tvc_0` ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on distinct_key where `test`.`t2`.`a` = `<subquery2>`.`1` and `test`.`t2`.`c` = `<subquery2>`.`2`))))
@@ -632,7 +630,7 @@ i
 EXPLAIN EXTENDED SELECT * FROM t1 WHERE i IN (NULL, NULL, NULL, NULL, NULL);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	
-1	PRIMARY	<derived3>	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
+1	PRIMARY	<derived3>	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
 3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 Warnings:
 Note	1003	/* select#1 */ select `test`.`t1`.`i` AS `i` from `test`.`t1` semi join ((values (NULL),(NULL),(NULL),(NULL),(NULL)) `tvc_0`) where `test`.`t1`.`i` = `tvc_0`.`NULL`
diff --git a/mysql-test/main/opt_tvc.test b/mysql-test/main/opt_tvc.test
index d5c9a5cbd3d..2d06a0a0b20 100644
--- a/mysql-test/main/opt_tvc.test
+++ b/mysql-test/main/opt_tvc.test
@@ -255,7 +255,7 @@ deallocate prepare stmt;
 
 --echo # use inside out access from tvc rows
 
-let $query= select * from t3 where a in (1,4,10);
+let $query= select * from t3 where a in (1,4);
 set @@in_predicate_conversion_threshold= default;
 eval $query;
 eval explain extended $query;
diff --git a/mysql-test/main/range.result b/mysql-test/main/range.result
index e2996b963bc..464c0bf031a 100644
--- a/mysql-test/main/range.result
+++ b/mysql-test/main/range.result
@@ -1048,30 +1048,6 @@ select a, hex(filler) from t1 where a not between 'b' and 'b';
 a	hex(filler)
 a	0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
 drop table t1,t2,t3;
-create table t1 (a int);
-insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
-create table t2 (a int, key(a));
-insert into t2 select 2*(A.a + 10*(B.a + 10*C.a)) from t1 A, t1 B, t1 C;
-set @a="select * from t2 force index (a) where a NOT IN(0";
-select count(*) from (select @a:=concat(@a, ',', a) from t2 ) Z;
-count(*)
-1000
-set @a=concat(@a, ')');
-insert into t2 values (11),(13),(15);
-set @b= concat("explain ", @a);
-prepare stmt1 from @b;
-execute stmt1;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t2	index	NULL	a	5	NULL	1003	Using where; Using index
-2	MATERIALIZED	<derived3>	ALL	NULL	NULL	NULL	NULL	2	
-3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
-prepare stmt1 from @a;
-execute stmt1;
-a
-11
-13
-15
-drop table t1, t2;
 CREATE TABLE t1 (
 id int NOT NULL DEFAULT '0',
 b int NOT NULL DEFAULT '0',
@@ -3005,5 +2981,46 @@ deallocate prepare stmt;
 set optimizer_switch=@save_optimizer_switch;
 drop table t1,t2,t3;
 #
+# MDEV-16934: using system variable eq_range_index_dive_limit
+#             to reduce the number of index dives
+#
+create table t1 (a int, b varchar(31), index idx(a));
+insert into t1 values
+(7,'xxxx'), (1,'yy'), (3,'aaa'), (1,'bbb'), (2,'zz'),
+(4,'vvvvv'), (7,'ddd'), (9,'zzzzz'), (1,'cc'), (5,'ffff');
+insert into t1 select a+10, concat(b,'zz') from t1;
+insert into t1 select a+15, concat(b,'yy') from t1;
+insert into t1 select a+100, concat(b,'xx') from t1;
+analyze table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+select cast(count(a)/count(distinct a) as unsigned) as rec_per_key from t1;
+rec_per_key
+2
+set eq_range_index_dive_limit=0;
+explain select * from t1 where a in (8, 15, 31, 1, 9);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	idx	idx	5	NULL	7	Using index condition
+select * from t1 where a in (8, 15, 31, 1, 9);
+a	b
+1	yy
+1	bbb
+1	cc
+9	zzzzz
+15	ffffzz
+set eq_range_index_dive_limit=2;
+explain select * from t1 where a in (8, 15, 31, 1, 9);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	idx	idx	5	NULL	10	Using index condition
+select * from t1 where a in (8, 15, 31, 1, 9);
+a	b
+1	yy
+1	bbb
+1	cc
+9	zzzzz
+15	ffffzz
+set eq_range_index_dive_limit=default;
+drop table t1;
+#
 # End of 10.2 tests
 #
diff --git a/mysql-test/main/range.test b/mysql-test/main/range.test
index 36e0e32b28b..bd2299bac5f 100644
--- a/mysql-test/main/range.test
+++ b/mysql-test/main/range.test
@@ -863,30 +863,6 @@ select a, hex(filler) from t1 where a not between 'b' and 'b';
 drop table t1,t2,t3;
 
 #
-# BUG#21282
-#
-create table t1 (a int);
-insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
-create table t2 (a int, key(a));
-insert into t2 select 2*(A.a + 10*(B.a + 10*C.a)) from t1 A, t1 B, t1 C;
-
-set @a="select * from t2 force index (a) where a NOT IN(0";
-select count(*) from (select @a:=concat(@a, ',', a) from t2 ) Z;
-set @a=concat(@a, ')');
-
-insert into t2 values (11),(13),(15);
-
-set @b= concat("explain ", @a);
-
-prepare stmt1 from @b;
-execute stmt1;
-
-prepare stmt1 from @a;
-execute stmt1;
-
-drop table t1, t2;
-
-#
 # Bug #18165: range access for BETWEEN with a constant for the first argument 
 #
 
@@ -2042,5 +2018,38 @@ set optimizer_switch=@save_optimizer_switch;
 drop table t1,t2,t3;
 
 --echo #
+--echo # MDEV-16934: using system variable eq_range_index_dive_limit
+--echo #             to reduce the number of index dives
+--echo #
+
+create table t1 (a int, b varchar(31), index idx(a));
+
+insert into t1 values
+  (7,'xxxx'), (1,'yy'), (3,'aaa'), (1,'bbb'), (2,'zz'),
+  (4,'vvvvv'), (7,'ddd'), (9,'zzzzz'), (1,'cc'), (5,'ffff');
+insert into t1 select a+10, concat(b,'zz') from t1;
+insert into t1 select a+15, concat(b,'yy') from t1;
+insert into t1 select a+100, concat(b,'xx') from t1;
+
+analyze table t1;
+
+select cast(count(a)/count(distinct a) as unsigned) as rec_per_key from t1;
+
+let $q=
+select * from t1 where a in (8, 15, 31, 1, 9);
+
+set eq_range_index_dive_limit=0;
+eval explain $q;
+eval $q;
+
+set eq_range_index_dive_limit=2;
+eval explain $q;
+eval $q;
+
+set eq_range_index_dive_limit=default;
+
+drop table t1;
+
+--echo #
 --echo # End of 10.2 tests
 --echo #
diff --git a/mysql-test/main/range_debug.result b/mysql-test/main/range_debug.result
new file mode 100644
index 00000000000..5597671071e
--- /dev/null
+++ b/mysql-test/main/range_debug.result
@@ -0,0 +1,24 @@
+create table t1 (a int);
+insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t2 (a int, key(a));
+insert into t2 select 2*(A.a + 10*(B.a + 10*C.a)) from t1 A, t1 B, t1 C;
+set in_predicate_conversion_threshold= 2000;
+set @a="select * from t2 force index (a) where a NOT IN(0";
+select count(*) from (select @a:=concat(@a, ',', a) from t2 ) Z;
+count(*)
+1000
+set @a=concat(@a, ')');
+insert into t2 values (11),(13),(15);
+set @b= concat("explain ", @a);
+prepare stmt1 from @b;
+execute stmt1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	index	a	a	5	NULL	1003	Using where; Using index
+prepare stmt1 from @a;
+execute stmt1;
+a
+11
+13
+15
+set in_predicate_conversion_threshold= default;
+drop table t1, t2;
diff --git a/mysql-test/main/range_debug.test b/mysql-test/main/range_debug.test
new file mode 100644
index 00000000000..ef331cdc84f
--- /dev/null
+++ b/mysql-test/main/range_debug.test
@@ -0,0 +1,30 @@
+source include/have_debug.inc;
+#
+# BUG#21282
+#
+create table t1 (a int);
+insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t2 (a int, key(a));
+insert into t2 select 2*(A.a + 10*(B.a + 10*C.a)) from t1 A, t1 B, t1 C;
+
+set in_predicate_conversion_threshold= 2000;
+
+set @a="select * from t2 force index (a) where a NOT IN(0";
+select count(*) from (select @a:=concat(@a, ',', a) from t2 ) Z;
+set @a=concat(@a, ')');
+
+insert into t2 values (11),(13),(15);
+
+set @b= concat("explain ", @a);
+
+prepare stmt1 from @b;
+execute stmt1;
+
+prepare stmt1 from @a;
+execute stmt1;
+
+set in_predicate_conversion_threshold= default;
+
+drop table t1, t2;
+
+
diff --git a/mysql-test/main/range_mrr_icp.result b/mysql-test/main/range_mrr_icp.result
index 629d183bee7..b132e63b732 100644
--- a/mysql-test/main/range_mrr_icp.result
+++ b/mysql-test/main/range_mrr_icp.result
@@ -1050,30 +1050,6 @@ select a, hex(filler) from t1 where a not between 'b' and 'b';
 a	hex(filler)
 a	0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
 drop table t1,t2,t3;
-create table t1 (a int);
-insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
-create table t2 (a int, key(a));
-insert into t2 select 2*(A.a + 10*(B.a + 10*C.a)) from t1 A, t1 B, t1 C;
-set @a="select * from t2 force index (a) where a NOT IN(0";
-select count(*) from (select @a:=concat(@a, ',', a) from t2 ) Z;
-count(*)
-1000
-set @a=concat(@a, ')');
-insert into t2 values (11),(13),(15);
-set @b= concat("explain ", @a);
-prepare stmt1 from @b;
-execute stmt1;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t2	index	NULL	a	5	NULL	1003	Using where; Using index
-2	MATERIALIZED	<derived3>	ALL	NULL	NULL	NULL	NULL	2	
-3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
-prepare stmt1 from @a;
-execute stmt1;
-a
-11
-13
-15
-drop table t1, t2;
 CREATE TABLE t1 (
 id int NOT NULL DEFAULT '0',
 b int NOT NULL DEFAULT '0',
@@ -3017,6 +2993,47 @@ deallocate prepare stmt;
 set optimizer_switch=@save_optimizer_switch;
 drop table t1,t2,t3;
 #
+# MDEV-16934: using system variable eq_range_index_dive_limit
+#             to reduce the number of index dives
+#
+create table t1 (a int, b varchar(31), index idx(a));
+insert into t1 values
+(7,'xxxx'), (1,'yy'), (3,'aaa'), (1,'bbb'), (2,'zz'),
+(4,'vvvvv'), (7,'ddd'), (9,'zzzzz'), (1,'cc'), (5,'ffff');
+insert into t1 select a+10, concat(b,'zz') from t1;
+insert into t1 select a+15, concat(b,'yy') from t1;
+insert into t1 select a+100, concat(b,'xx') from t1;
+analyze table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+select cast(count(a)/count(distinct a) as unsigned) as rec_per_key from t1;
+rec_per_key
+2
+set eq_range_index_dive_limit=0;
+explain select * from t1 where a in (8, 15, 31, 1, 9);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	idx	idx	5	NULL	7	Using index condition; Rowid-ordered scan
+select * from t1 where a in (8, 15, 31, 1, 9);
+a	b
+1	yy
+1	bbb
+9	zzzzz
+1	cc
+15	ffffzz
+set eq_range_index_dive_limit=2;
+explain select * from t1 where a in (8, 15, 31, 1, 9);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	idx	idx	5	NULL	10	Using index condition; Rowid-ordered scan
+select * from t1 where a in (8, 15, 31, 1, 9);
+a	b
+1	yy
+1	bbb
+9	zzzzz
+1	cc
+15	ffffzz
+set eq_range_index_dive_limit=default;
+drop table t1;
+#
 # End of 10.2 tests
 #
 set optimizer_switch=@mrr_icp_extra_tmp;
diff --git a/mysql-test/main/subselect_extra_no_semijoin.result b/mysql-test/main/subselect_extra_no_semijoin.result
index fc8d5759945..ec9ddb0452e 100644
--- a/mysql-test/main/subselect_extra_no_semijoin.result
+++ b/mysql-test/main/subselect_extra_no_semijoin.result
@@ -482,3 +482,25 @@ DROP TABLE t1,t2;
 set optimizer_switch= @tmp_subselect_extra_derived;
 set optimizer_switch= @subselect_extra_no_sj_tmp;
 set @optimizer_switch_for_subselect_extra_test=null;
+#
+# MDEV-6439: Server crashes in Explain_union::print_explain with explain in slow log, tis620 charset
+#
+connect con1,localhost,root,,;
+SET NAMES tis620;
+set @tmp= @@global.slow_query_log;
+SET GLOBAL slow_query_log = 1;
+SET long_query_time = 0.000001;
+SET log_slow_verbosity = 'explain';
+CREATE TABLE t1 (a VARCHAR(3)) ENGINE=MyISAM;
+SELECT * FROM t1 WHERE a >= ANY ( SELECT 'foo');
+a
+SELECT * FROM t1 WHERE a >= ANY ( SELECT 'foo' UNION SELECT 'bar' );
+ERROR HY000: Illegal mix of collations (tis620_thai_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation '<='
+create table t2 (b int);
+insert into t2 values (1),(2),(3);
+SELECT * FROM t1 WHERE a >= ANY ( SELECT 'foo' FROM t2);
+ERROR HY000: Illegal mix of collations (tis620_thai_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation '<='
+drop table t1,t2;
+SET GLOBAL slow_query_log=@tmp;
+disconnect con1;
+connection default;
diff --git a/mysql-test/main/subselect_extra_no_semijoin.test b/mysql-test/main/subselect_extra_no_semijoin.test
index 4340f4165e7..681f92bd8c4 100644
--- a/mysql-test/main/subselect_extra_no_semijoin.test
+++ b/mysql-test/main/subselect_extra_no_semijoin.test
@@ -6,4 +6,33 @@ set  @optimizer_switch_for_subselect_extra_test='semijoin=off,firstmatch=off,loo
 
 set optimizer_switch= @subselect_extra_no_sj_tmp;
 
-set @optimizer_switch_for_subselect_extra_test=null;
\ No newline at end of file
+set @optimizer_switch_for_subselect_extra_test=null;
+
+--echo #
+--echo # MDEV-6439: Server crashes in Explain_union::print_explain with explain in slow log, tis620 charset
+--echo #
+
+## Using a separate client connection is easier than restoring state
+connect(con1,localhost,root,,);
+
+SET NAMES tis620;
+set @tmp= @@global.slow_query_log;
+SET GLOBAL slow_query_log = 1;
+SET long_query_time = 0.000001;
+SET log_slow_verbosity = 'explain';
+
+CREATE TABLE t1 (a VARCHAR(3)) ENGINE=MyISAM;
+SELECT * FROM t1 WHERE a >= ANY ( SELECT 'foo');
+--error ER_CANT_AGGREGATE_2COLLATIONS
+SELECT * FROM t1 WHERE a >= ANY ( SELECT 'foo' UNION SELECT 'bar' );
+
+create table t2 (b int);
+insert into t2 values (1),(2),(3);
+
+--error ER_CANT_AGGREGATE_2COLLATIONS
+SELECT * FROM t1 WHERE a >= ANY ( SELECT 'foo' FROM t2);
+
+drop table t1,t2;
+SET GLOBAL slow_query_log=@tmp;
+disconnect con1;
+connection default;
diff --git a/mysql-test/main/subselect_mat.result b/mysql-test/main/subselect_mat.result
index e208dfe74c1..010ba03153b 100644
--- a/mysql-test/main/subselect_mat.result
+++ b/mysql-test/main/subselect_mat.result
@@ -499,7 +499,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
 2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 Warnings:
-Note	1003	/* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(/* select#2 */ select '1 - 01','2 - 01' having (<cache>(`test`.`t1`.`a1`) = '1 - 01' or '1 - 01' is null) and (<cache>(`test`.`t1`.`a2`) = '2 - 01' or '2 - 01' is null) and '1 - 01' is null and '2 - 01' is null)))
+Note	1003	/* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(/* select#2 */ select '1 - 01','2 - 01' having (<cache>(`test`.`t1`.`a1`) = '1 - 01' or /*always not null*/ 1 is null) and (<cache>(`test`.`t1`.`a2`) = '2 - 01' or /*always not null*/ 1 is null) and '1 - 01' is null and '2 - 01' is null)))
 select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01');
 a1	a2
 1 - 01	2 - 01
@@ -509,7 +509,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
 2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 Warnings:
-Note	1003	/* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(/* select#2 */ select '1 - 01','2 - 01' having (<cache>(`test`.`t1`.`a1`) = '1 - 01' or '1 - 01' is null) and (<cache>(`test`.`t1`.`a2`) = '2 - 01' or '2 - 01' is null) and '1 - 01' is null and '2 - 01' is null)))
+Note	1003	/* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(/* select#2 */ select '1 - 01','2 - 01' having (<cache>(`test`.`t1`.`a1`) = '1 - 01' or /*always not null*/ 1 is null) and (<cache>(`test`.`t1`.`a2`) = '2 - 01' or /*always not null*/ 1 is null) and '1 - 01' is null and '2 - 01' is null)))
 select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01' from dual);
 a1	a2
 1 - 01	2 - 01
@@ -1925,7 +1925,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (flat, BNL join)
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	
 Warnings:
-Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from  <materialize> (/* select#2 */ select max(`test`.`t2`.`c`) from `test`.`t2` having `MAX(c)` is null or `MAX(c)` = 7) join `test`.`t1` where `test`.`t1`.`b` = 7 and `test`.`t1`.`a` = `<subquery2>`.`MAX(c)` and (<cache>(`<subquery2>`.`MAX(c)` is null) or `<subquery2>`.`MAX(c)` = 7)
+Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from  <materialize> (/* select#2 */ select max(`test`.`t2`.`c`) from `test`.`t2` having `MAX(c)` is null or `MAX(c)` = 7) join `test`.`t1` where `test`.`t1`.`b` = 7 and `test`.`t1`.`a` = `<subquery2>`.`MAX(c)` and (<cache>(/*always not null*/ 1 is null) or `<subquery2>`.`MAX(c)` = 7)
 SELECT * FROM t1
 WHERE a IN (SELECT MAX(c) FROM t2) AND b=7 AND (a IS NULL OR a=b);
 a	b
diff --git a/mysql-test/main/subselect_sj_mat.result b/mysql-test/main/subselect_sj_mat.result
index 7630b0b9c0f..983c6c31c65 100644
--- a/mysql-test/main/subselect_sj_mat.result
+++ b/mysql-test/main/subselect_sj_mat.result
@@ -520,7 +520,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
 2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 Warnings:
-Note	1003	/* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(/* select#2 */ select '1 - 01','2 - 01' having (<cache>(`test`.`t1`.`a1`) = '1 - 01' or '1 - 01' is null) and (<cache>(`test`.`t1`.`a2`) = '2 - 01' or '2 - 01' is null) and '1 - 01' is null and '2 - 01' is null)))
+Note	1003	/* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(/* select#2 */ select '1 - 01','2 - 01' having (<cache>(`test`.`t1`.`a1`) = '1 - 01' or /*always not null*/ 1 is null) and (<cache>(`test`.`t1`.`a2`) = '2 - 01' or /*always not null*/ 1 is null) and '1 - 01' is null and '2 - 01' is null)))
 select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01');
 a1	a2
 1 - 01	2 - 01
@@ -530,7 +530,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
 2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 Warnings:
-Note	1003	/* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(/* select#2 */ select '1 - 01','2 - 01' having (<cache>(`test`.`t1`.`a1`) = '1 - 01' or '1 - 01' is null) and (<cache>(`test`.`t1`.`a2`) = '2 - 01' or '2 - 01' is null) and '1 - 01' is null and '2 - 01' is null)))
+Note	1003	/* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`,`test`.`t1`.`a2`>(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(/* select#2 */ select '1 - 01','2 - 01' having (<cache>(`test`.`t1`.`a1`) = '1 - 01' or /*always not null*/ 1 is null) and (<cache>(`test`.`t1`.`a2`) = '2 - 01' or /*always not null*/ 1 is null) and '1 - 01' is null and '2 - 01' is null)))
 select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01' from dual);
 a1	a2
 1 - 01	2 - 01
@@ -1963,7 +1963,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (flat, BNL join)
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	
 Warnings:
-Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from  <materialize> (/* select#2 */ select max(`test`.`t2`.`c`) from `test`.`t2` having `MAX(c)` is null or `MAX(c)` = 7) join `test`.`t1` where `test`.`t1`.`b` = 7 and `test`.`t1`.`a` = `<subquery2>`.`MAX(c)` and (<cache>(`<subquery2>`.`MAX(c)` is null) or `<subquery2>`.`MAX(c)` = 7)
+Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from  <materialize> (/* select#2 */ select max(`test`.`t2`.`c`) from `test`.`t2` having `MAX(c)` is null or `MAX(c)` = 7) join `test`.`t1` where `test`.`t1`.`b` = 7 and `test`.`t1`.`a` = `<subquery2>`.`MAX(c)` and (<cache>(/*always not null*/ 1 is null) or `<subquery2>`.`MAX(c)` = 7)
 SELECT * FROM t1
 WHERE a IN (SELECT MAX(c) FROM t2) AND b=7 AND (a IS NULL OR a=b);
 a	b
diff --git a/mysql-test/main/table_value_constr.result b/mysql-test/main/table_value_constr.result
index 9129fd0a23b..454356bb5fb 100644
--- a/mysql-test/main/table_value_constr.result
+++ b/mysql-test/main/table_value_constr.result
@@ -2096,3 +2096,95 @@ v
 #
 with t as (values (),()) select 1 from t;
 ERROR HY000: Row with no elements is not allowed in table value constructor in this context
+#
+# MDEV-17017: TVC in derived table
+#
+create table t1 (a int);
+insert into t1 values (9), (3), (2);
+select * from (values (7), (5), (8), (1), (3), (8), (1)) t;
+7
+7
+5
+8
+1
+3
+8
+1
+explain select * from (values (7), (5), (8), (1), (3), (8), (1)) t;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	7	
+2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+select * from (values (1,11), (7,77), (3,31), (4,42)) t;
+1	11
+1	11
+7	77
+3	31
+4	42
+explain select * from (values (1,11), (7,77), (3,31), (4,42)) t;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	4	
+2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+select * from (values (7), (5), (8), (1) union values (3), (8), (1)) t;
+7
+7
+5
+8
+1
+3
+explain select * from (values (7), (5), (8), (1) union values (3), (8), (1)) t;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	7	
+2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
+select * from (values (7), (5), (8), (1) union select * from t1) t;
+7
+7
+5
+8
+1
+9
+3
+2
+explain select * from (values (7), (5), (8), (1) union select * from t1) t;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	7	
+2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	UNION	t1	ALL	NULL	NULL	NULL	NULL	3	
+NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
+drop table t1;
+#
+# MDEV-16930: expression in the first row of TVC specifying derived table
+#
+SELECT 1 + 1, 2, "abc";
+1 + 1	2	abc
+2	2	abc
+SELECT * FROM (SELECT 1 + 1, 2, "abc") t;
+1 + 1	2	abc
+2	2	abc
+WITH cte AS (SELECT 1 + 1, 2, "abc") SELECT * FROM cte;
+1 + 1	2	abc
+2	2	abc
+SELECT 1 + 1, 2, "abc" UNION SELECT 3+4, 3, "abc";
+1 + 1	2	abc
+2	2	abc
+7	3	abc
+CREATE VIEW v1 AS SELECT 1 + 1, 2, "abc";
+SELECT * FROM v1;
+1 + 1	2	abc
+2	2	abc
+DROP VIEW v1;
+VALUES(1 + 1,2,"abc");
+1 + 1	2	abc
+2	2	abc
+SELECT * FROM (VALUES(1 + 1,2,"abc")) t;
+1 + 1	2	abc
+2	2	abc
+PREPARE stmt FROM "SELECT * FROM (VALUES(1 + 1,2,'abc')) t";
+EXECUTE stmt;
+1 + 1	2	abc
+2	2	abc
+EXECUTE stmt;
+1 + 1	2	abc
+2	2	abc
+DEALLOCATE PREPARE stmt;
diff --git a/mysql-test/main/table_value_constr.test b/mysql-test/main/table_value_constr.test
index eb5ea59f829..0dd0a7a04b0 100644
--- a/mysql-test/main/table_value_constr.test
+++ b/mysql-test/main/table_value_constr.test
@@ -1075,3 +1075,51 @@ DELIMITER ;|
 
 --error ER_EMPTY_ROW_IN_TVC
 with t as (values (),()) select 1 from t;
+
+--echo #
+--echo # MDEV-17017: TVC in derived table
+--echo #
+
+create table t1 (a int);
+insert into t1 values (9), (3), (2);
+
+let $q1=
+select * from (values (7), (5), (8), (1), (3), (8), (1)) t;
+eval $q1;
+eval explain $q1;
+
+let $q2=
+select * from (values (1,11), (7,77), (3,31), (4,42)) t;
+eval $q2;
+eval explain $q2;
+
+let $q3=
+select * from (values (7), (5), (8), (1) union values (3), (8), (1)) t;
+eval $q3;
+eval explain $q3;
+
+let $q4=
+select * from (values (7), (5), (8), (1) union select * from t1) t;
+eval $q4;
+eval explain $q4;
+
+drop table t1;
+
+--echo #
+--echo # MDEV-16930: expression in the first row of TVC specifying derived table
+--echo #
+
+SELECT 1 + 1, 2, "abc";
+SELECT * FROM (SELECT 1 + 1, 2, "abc") t;
+WITH cte AS (SELECT 1 + 1, 2, "abc") SELECT * FROM cte; 
+SELECT 1 + 1, 2, "abc" UNION SELECT 3+4, 3, "abc";
+CREATE VIEW v1 AS SELECT 1 + 1, 2, "abc";
+SELECT * FROM v1;
+DROP VIEW v1;
+
+VALUES(1 + 1,2,"abc");
+SELECT * FROM (VALUES(1 + 1,2,"abc")) t;
+PREPARE stmt FROM "SELECT * FROM (VALUES(1 + 1,2,'abc')) t";
+EXECUTE stmt;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
diff --git a/mysql-test/main/win.result b/mysql-test/main/win.result
index fd0fbefdcc5..97801b5e285 100644
--- a/mysql-test/main/win.result
+++ b/mysql-test/main/win.result
@@ -3219,8 +3219,8 @@ DROP TABLE fv_test, fv_result;
 #
 CREATE TABLE t1 (a int);
 INSERT INTO t1 VALUES (0),(1),(2);
-SELECT LEAD(a) OVER (PARTITION BY a) as lead,
-a AND LEAD(a) OVER (PARTITION BY a) AS a_and_lead_part
+SELECT LEAD(a) OVER (PARTITION BY a ORDER BY a) as lead,
+a AND LEAD(a) OVER (PARTITION BY a ORDER BY a) AS a_and_lead_part
 FROM t1;
 lead	a_and_lead_part
 NULL	0
diff --git a/mysql-test/main/win.test b/mysql-test/main/win.test
index cc16595fcd4..f038316ce98 100644
--- a/mysql-test/main/win.test
+++ b/mysql-test/main/win.test
@@ -2000,8 +2000,8 @@ DROP TABLE fv_test, fv_result;
 
 CREATE TABLE t1 (a int);
 INSERT INTO t1 VALUES (0),(1),(2);
-SELECT LEAD(a) OVER (PARTITION BY a) as lead,
-       a AND LEAD(a) OVER (PARTITION BY a) AS a_and_lead_part
+SELECT LEAD(a) OVER (PARTITION BY a ORDER BY a) as lead,
+       a AND LEAD(a) OVER (PARTITION BY a ORDER BY a) AS a_and_lead_part
 FROM t1;
 
 SELECT a OR LEAD(a) OVER (ORDER BY a) AS a_or_lead_order
diff --git a/mysql-test/main/win_lead_lag.result b/mysql-test/main/win_lead_lag.result
index 5ead58fa11a..f3c0b8f7ef2 100644
--- a/mysql-test/main/win_lead_lag.result
+++ b/mysql-test/main/win_lead_lag.result
@@ -226,4 +226,15 @@ pk	a	b	a+b	lag(a + b) over (partition by a order by pk) + pk
 9	2	2	4	12
 10	2	0	2	14
 11	2	10	12	13
+#
+# MDEV-15204 - LAG function doesn't require ORDER BY in OVER clause
+#
+select pk,
+lag(pk, 1) over ()
+from t1;
+ERROR HY000: No order list in window specification for 'lag'
+select pk,
+lead(pk, 1) over ()
+from t1;
+ERROR HY000: No order list in window specification for 'lead'
 drop table t1;
diff --git a/mysql-test/main/win_lead_lag.test b/mysql-test/main/win_lead_lag.test
index 2824f83789c..d154244ecd0 100644
--- a/mysql-test/main/win_lead_lag.test
+++ b/mysql-test/main/win_lead_lag.test
@@ -107,4 +107,17 @@ select pk, a, b, a+b,
 from t1
 order by pk asc;
 
+--echo #
+--echo # MDEV-15204 - LAG function doesn't require ORDER BY in OVER clause
+--echo #
+--error ER_NO_ORDER_LIST_IN_WINDOW_SPEC
+select pk,
+       lag(pk, 1) over ()
+from t1;
+
+--error ER_NO_ORDER_LIST_IN_WINDOW_SPEC
+select pk,
+       lead(pk, 1) over ()
+from t1;
+
 drop table t1;