Merge 10.2 into 10.3

24 files changed, 798 insertions, 17 deletions

diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result
index 832b9a46289..51a4703d5d4 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 8a7a990bb85..e78389e8eca 100644
--- a/mysql-test/main/func_time.result
+++ b/mysql-test/main/func_time.result
@@ -2797,6 +2797,11 @@ SEC_TO_TIME(MAKEDATE(0,RAND(~0)))
 838:59:59
 Warnings:
 Warning	1292	Truncated incorrect time 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
 #
diff --git a/mysql-test/main/func_time.test b/mysql-test/main/func_time.test
index 53cfd29dc9c..d71447e8b1a 100644
--- a/mysql-test/main/func_time.test
+++ b/mysql-test/main/func_time.test
@@ -1702,6 +1702,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
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/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 13131cf6d23..5a7153f32d3 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
@@ -1400,6 +1405,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/range.result b/mysql-test/main/range.result
index 052ea09d5ac..464c0bf031a 100644
--- a/mysql-test/main/range.result
+++ b/mysql-test/main/range.result
@@ -2981,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 7006f15e320..bd2299bac5f 100644
--- a/mysql-test/main/range.test
+++ b/mysql-test/main/range.test
@@ -2018,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_mrr_icp.result b/mysql-test/main/range_mrr_icp.result
index 9ce6f8bf5f0..b132e63b732 100644
--- a/mysql-test/main/range_mrr_icp.result
+++ b/mysql-test/main/range_mrr_icp.result
@@ -2993,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 1a5f0c8e93b..6a725d553e5 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`) 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`) 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 5ba68f095fd..76dae42d9c0 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`) 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`) 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/win.result b/mysql-test/main/win.result
index 7607cebc3a5..a81d3b8c2ba 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 4b73f70d737..f1d35bb347d 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;