Merge 10.3 into 10.4

14 files changed, 385 insertions, 31 deletions

diff --git a/mysql-test/main/invisible_field.result b/mysql-test/main/invisible_field.result
index 36e62645ef2..87c2b940c7e 100644
--- a/mysql-test/main/invisible_field.result
+++ b/mysql-test/main/invisible_field.result
@@ -404,8 +404,8 @@ b	int(11)	YES		NULL
 c	int(11)	YES		NULL	
 explain select * from t1,t2 where t1.b = t2.c and t1.c = t2.b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	b,c	NULL	NULL	NULL	10	
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (flat, BNL join)
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	10	Using where
+1	SIMPLE	t1	ref	b,c	b	5	test.t2.c	1	Using where
 select * from t1,t2 where t1.b = t2.c and t1.c = t2.b;
 a	a	b	c
 1	1	1	1
diff --git a/mysql-test/main/join.result b/mysql-test/main/join.result
index 47c3e78116f..5c7f24b8d62 100644
--- a/mysql-test/main/join.result
+++ b/mysql-test/main/join.result
@@ -1605,3 +1605,34 @@ SELECT STRAIGHT_JOIN * FROM t1, t2 AS t2_1, t2 AS t2_2
 WHERE t2_2.c = t2_1.c AND t2_2.b = t2_1.b AND ( a IS NULL OR t2_1.c = a );
 a	b	c	b	c
 DROP TABLE t1,t2;
+#
+# MDEV-19600: The optimizer should be able to produce rows=1 estimate for unique index with NULLable columns
+#
+create table t0(a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1 (
+pk int not null primary key auto_increment,
+a int,
+b int,
+unique key(a)
+);
+insert into t1 (a,b) select null, 12345 from t0 A, t0 B, t0 C;
+insert into t1 (a,b) select a,a from t0;
+# Simulate InnoDB's persistent statistics (It always uses nulls_equal)
+set @tmp1= @@myisam_stats_method;
+set myisam_stats_method=nulls_equal;
+analyze table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+set myisam_stats_method=@tmp1;
+show keys from t1;
+Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment
+t1	0	PRIMARY	1	pk	A	1010	NULL	NULL		BTREE		
+t1	0	a	1	a	A	1010	NULL	NULL	YES	BTREE		
+# t1 must use ref(t1.a=t0.a) and rows must be 1 (and not 45):
+explain select * from t0,t1 where t0.a=t1.a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	10	Using where
+1	SIMPLE	t1	ref	a	a	5	test.t0.a	1	
+drop table t0,t1;
diff --git a/mysql-test/main/join.test b/mysql-test/main/join.test
index 6b0481d859b..223886b579c 100644
--- a/mysql-test/main/join.test
+++ b/mysql-test/main/join.test
@@ -1255,3 +1255,33 @@ SELECT STRAIGHT_JOIN * FROM t1, t2 AS t2_1, t2 AS t2_2
   WHERE t2_2.c = t2_1.c AND t2_2.b = t2_1.b AND ( a IS NULL OR t2_1.c = a );                  
 
 DROP TABLE t1,t2;
+
+--echo #
+--echo # MDEV-19600: The optimizer should be able to produce rows=1 estimate for unique index with NULLable columns
+--echo #
+
+create table t0(a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+
+create table t1 (
+  pk int not null primary key auto_increment,
+  a int,
+  b int,
+  unique key(a)
+);
+
+# 10K of null values
+insert into t1 (a,b) select null, 12345 from t0 A, t0 B, t0 C;
+insert into t1 (a,b) select a,a from t0;
+
+--echo # Simulate InnoDB's persistent statistics (It always uses nulls_equal)
+set @tmp1= @@myisam_stats_method;
+set myisam_stats_method=nulls_equal;
+analyze table t1;
+set myisam_stats_method=@tmp1;
+show keys from t1;
+
+--echo # t1 must use ref(t1.a=t0.a) and rows must be 1 (and not 45):
+explain select * from t0,t1 where t0.a=t1.a;
+
+drop table t0,t1;
diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result
index 82a2196545d..c0e0b4807a1 100644
--- a/mysql-test/main/opt_trace.result
+++ b/mysql-test/main/opt_trace.result
@@ -867,7 +867,7 @@ explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b	{
                 "table": "t1",
                 "field": "a",
                 "equals": "t2.b + 2",
-                "null_rejecting": false
+                "null_rejecting": true
               },
               {
                 "table": "t2",
@@ -1805,19 +1805,19 @@ explain  select * from t1 where a=1 and b=2 order by c limit 1	{
                 "table": "t1",
                 "field": "a",
                 "equals": "1",
-                "null_rejecting": false
+                "null_rejecting": true
               },
               {
                 "table": "t1",
                 "field": "a",
                 "equals": "1",
-                "null_rejecting": false
+                "null_rejecting": true
               },
               {
                 "table": "t1",
                 "field": "b",
                 "equals": "2",
-                "null_rejecting": false
+                "null_rejecting": true
               }
             ]
           },
@@ -2821,37 +2821,37 @@ explain select * from t1 where pk = 2 and a=5 and b=1	{
                 "table": "t1",
                 "field": "pk",
                 "equals": "2",
-                "null_rejecting": false
+                "null_rejecting": true
               },
               {
                 "table": "t1",
                 "field": "pk",
                 "equals": "2",
-                "null_rejecting": false
+                "null_rejecting": true
               },
               {
                 "table": "t1",
                 "field": "a",
                 "equals": "5",
-                "null_rejecting": false
+                "null_rejecting": true
               },
               {
                 "table": "t1",
                 "field": "pk",
                 "equals": "2",
-                "null_rejecting": false
+                "null_rejecting": true
               },
               {
                 "table": "t1",
                 "field": "a",
                 "equals": "5",
-                "null_rejecting": false
+                "null_rejecting": true
               },
               {
                 "table": "t1",
                 "field": "b",
                 "equals": "1",
-                "null_rejecting": false
+                "null_rejecting": true
               }
             ]
           },
diff --git a/mysql-test/main/order_by.result b/mysql-test/main/order_by.result
index 9532c2995ce..b059cc686cd 100644
--- a/mysql-test/main/order_by.result
+++ b/mysql-test/main/order_by.result
@@ -1589,7 +1589,7 @@ WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE'
 ORDER BY t2.c LIMIT 1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t2	range	a,b,c	c	5	NULL	420	Using where
-1	SIMPLE	t1	ref	a	a	39	test.t2.a,const	10	Using where; Using index
+1	SIMPLE	t1	ref	a	a	39	test.t2.a,const	1	Using where; Using index
 SELECT d FROM t3 AS t1, t2 AS t2 
 WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE'
 ORDER BY t2.c LIMIT 1;
diff --git a/mysql-test/main/subselect_no_semijoin.result b/mysql-test/main/subselect_no_semijoin.result
index 3265a4f81bb..c9a52512614 100644
--- a/mysql-test/main/subselect_no_semijoin.result
+++ b/mysql-test/main/subselect_no_semijoin.result
@@ -7314,5 +7314,60 @@ ERROR HY000: Illegal parameter data types row and boolean for operation '='
 #
 # End of 10.4 tests
 #
+#
+# MDEV-19714: JOIN::pseudo_bits_cond is not visible in EXPLAIN FORMAT=JSON
+#
+CREATE TABLE t1 ( a INT );
+INSERT INTO t1 VALUES (1),(5);
+CREATE TABLE t2 ( b INT ) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1);
+CREATE TABLE t3 ( c INT );
+INSERT INTO t3 VALUES (4),(5);
+SET @tmp19714=@@optimizer_switch;
+SET optimizer_switch='subquery_cache=off';
+explain format=json
+SELECT ( SELECT b FROM t2 WHERE b = a OR EXISTS ( SELECT c FROM t3 WHERE c = b ) ) FROM t1;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "t1",
+      "access_type": "ALL",
+      "rows": 2,
+      "filtered": 100
+    },
+    "subqueries": [
+      {
+        "query_block": {
+          "select_id": 2,
+          "pseudo_bits_condition": "1 = t1.a or <in_optimizer>(1,<exists>(subquery#3))",
+          "table": {
+            "table_name": "t2",
+            "access_type": "system",
+            "rows": 1,
+            "filtered": 100
+          },
+          "subqueries": [
+            {
+              "query_block": {
+                "select_id": 3,
+                "table": {
+                  "table_name": "t3",
+                  "access_type": "ALL",
+                  "rows": 2,
+                  "filtered": 100,
+                  "attached_condition": "1 = t3.c"
+                }
+              }
+            }
+          ]
+        }
+      }
+    ]
+  }
+}
+SET optimizer_switch=@tmp19714;
+drop table t1,t2,t3;
 set @optimizer_switch_for_subselect_test=null;
 set @join_cache_level_for_subselect_test=NULL;
diff --git a/mysql-test/main/subselect_no_semijoin.test b/mysql-test/main/subselect_no_semijoin.test
index 6b82b748912..84d312c03c8 100644
--- a/mysql-test/main/subselect_no_semijoin.test
+++ b/mysql-test/main/subselect_no_semijoin.test
@@ -8,5 +8,28 @@ set @join_cache_level_for_subselect_test=@@join_cache_level;
 
 --source subselect.test
 
+--echo #
+--echo # MDEV-19714: JOIN::pseudo_bits_cond is not visible in EXPLAIN FORMAT=JSON
+--echo #
+CREATE TABLE t1 ( a INT );
+INSERT INTO t1 VALUES (1),(5);
+
+# t2 must be MyISAM or Aria and contain 1 row
+CREATE TABLE t2 ( b INT ) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1);
+
+CREATE TABLE t3 ( c INT );
+INSERT INTO t3 VALUES (4),(5);
+
+SET @tmp19714=@@optimizer_switch;
+SET optimizer_switch='subquery_cache=off';
+
+explain format=json
+SELECT ( SELECT b FROM t2 WHERE b = a OR EXISTS ( SELECT c FROM t3 WHERE c = b ) ) FROM t1;
+
+SET optimizer_switch=@tmp19714;
+
+drop table t1,t2,t3;
+
 set @optimizer_switch_for_subselect_test=null; 
 set @join_cache_level_for_subselect_test=NULL;
diff --git a/mysql-test/main/subselect_sj.result b/mysql-test/main/subselect_sj.result
index 7114024310c..98143246673 100644
--- a/mysql-test/main/subselect_sj.result
+++ b/mysql-test/main/subselect_sj.result
@@ -2555,33 +2555,94 @@ CREATE TABLE t1 ( a INT PRIMARY KEY, b INT, KEY(b) );
 INSERT INTO t1 VALUES
 (1,2),(2,1),(3,3),(4,2),(5,5),
 (6,3),(7,1),(8,4),(9,3),(10,2);
-CREATE TABLE t2 ( c INT, d INT, UNIQUE KEY(c) );
+CREATE TABLE t2 ( c INT, d INT, KEY(c) );
 INSERT INTO t2 VALUES
 (1,2),(2,1),(3,3),(4,2),(5,5),(6,3),(7,1);
+analyze table t1,t2;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+test.t2	analyze	status	Engine-independent statistics collected
+test.t2	analyze	status	OK
+explain
+SELECT a, b, d FROM t1, t2
+WHERE ( b, d ) IN
+( SELECT b, d FROM t1, t2 WHERE b = c );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	7	
+1	PRIMARY	t1	index	b	b	5	NULL	10	Using where; Using index; LooseScan
+1	PRIMARY	t2	ref	c	c	5	test.t1.b	1	Using where; FirstMatch(t1)
+1	PRIMARY	t1	ref	b	b	5	test.t1.b	2	
 SELECT a, b, d FROM t1, t2
 WHERE ( b, d ) IN
 ( SELECT b, d FROM t1, t2 WHERE b = c );
 a	b	d
 2	1	2
 7	1	2
-2	1	2
-7	1	2
-1	2	1
-4	2	1
-10	2	1
+8	4	2
 1	2	1
 4	2	1
 10	2	1
 3	3	3
 6	3	3
 9	3	3
+2	1	2
+7	1	2
+8	4	2
+5	5	5
 3	3	3
 6	3	3
 9	3	3
-8	4	2
-8	4	2
-5	5	5
+1	2	1
+4	2	1
+10	2	1
 DROP TABLE t1, t2;
+# Another testcase for the above that still uses LooseScan:
+create table t0(a int primary key);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t10(a int primary key);
+insert into t10 select A.a + B.a* 10 + C.a * 100 from t0 A, t0 B, t0 C;
+create table t1 (
+pk int primary key auto_increment,
+kp1 int,
+kp2 int,
+filler char(100),
+key (kp1, kp2)
+);
+insert into t1 (kp1, kp2, filler) 
+select 
+A.a, B.a, 'filler-data'
+from t0 A, t0 B;
+create table t2 (a int, filler char(100), key(a));
+create table t3 (a int);
+insert into t3 values (1),(2);
+insert into t2 
+select (A.a+1000*B.a)/20, 'filler_data' from t10 A, t0 B;
+analyze table t1,t2,t3;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	Table is already up to date
+test.t2	analyze	status	Engine-independent statistics collected
+test.t2	analyze	status	Table is already up to date
+test.t3	analyze	status	Engine-independent statistics collected
+test.t3	analyze	status	OK
+delete from t1 where kp2 in (1,3);
+# Ref + LooseScan on t1:
+explain select sum(t2.a)
+from t2,t3
+where (t3.a,t2.a) in (select kp1,kp2 from t1,t0 where t0.a=2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t0	const	PRIMARY	PRIMARY	4	const	1	Using index
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	PRIMARY	t1	ref	kp1	kp1	5	test.t3.a	10	Using where; Using index; LooseScan
+1	PRIMARY	t2	ref	a	a	5	test.t1.kp2	19	Using index
+select sum(t2.a)
+from t2,t3
+where (t3.a,t2.a) in (select kp1,kp2 from t1,t0 where t0.a=2);
+sum(t2.a)
+1640
+drop table t0,t10;
+drop table t1,t2,t3;
 #
 # BUG#920713: Wrong result (missing rows) with firstmatch+BNL, IN subquery, ...
 #
diff --git a/mysql-test/main/subselect_sj.test b/mysql-test/main/subselect_sj.test
index ad4577b72b0..b693f7b5b93 100644
--- a/mysql-test/main/subselect_sj.test
+++ b/mysql-test/main/subselect_sj.test
@@ -2285,16 +2285,66 @@ INSERT INTO t1 VALUES
   (1,2),(2,1),(3,3),(4,2),(5,5),
   (6,3),(7,1),(8,4),(9,3),(10,2);
 
-CREATE TABLE t2 ( c INT, d INT, UNIQUE KEY(c) );
+CREATE TABLE t2 ( c INT, d INT, KEY(c) );
 INSERT INTO t2 VALUES
   (1,2),(2,1),(3,3),(4,2),(5,5),(6,3),(7,1);
 
+analyze table t1,t2;
+explain
+SELECT a, b, d FROM t1, t2
+WHERE ( b, d ) IN
+  ( SELECT b, d FROM t1, t2 WHERE b = c );
 SELECT a, b, d FROM t1, t2
 WHERE ( b, d ) IN
   ( SELECT b, d FROM t1, t2 WHERE b = c );
 
 DROP TABLE t1, t2;
 
+--echo # Another testcase for the above that still uses LooseScan:
+
+create table t0(a int primary key);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+
+create table t10(a int primary key);
+insert into t10 select A.a + B.a* 10 + C.a * 100 from t0 A, t0 B, t0 C;
+
+create table t1 (
+   pk int primary key auto_increment,
+   kp1 int,
+   kp2 int,
+   filler char(100),
+   key (kp1, kp2)
+);
+
+# 10 groups, each has 10 elements.
+insert into t1 (kp1, kp2, filler) 
+select 
+  A.a, B.a, 'filler-data'
+from t0 A, t0 B;
+
+create table t2 (a int, filler char(100), key(a));
+
+create table t3 (a int);
+insert into t3 values (1),(2);
+
+insert into t2 
+select (A.a+1000*B.a)/20, 'filler_data' from t10 A, t0 B;
+
+analyze table t1,t2,t3;
+delete from t1 where kp2 in (1,3);
+
+--echo # Ref + LooseScan on t1:
+explain select sum(t2.a)
+from t2,t3
+where (t3.a,t2.a) in (select kp1,kp2 from t1,t0 where t0.a=2);
+
+select sum(t2.a)
+from t2,t3
+where (t3.a,t2.a) in (select kp1,kp2 from t1,t0 where t0.a=2);
+
+drop table t0,t10;
+drop table t1,t2,t3;
+
 --echo #
 --echo # BUG#920713: Wrong result (missing rows) with firstmatch+BNL, IN subquery, ...
 --echo #
diff --git a/mysql-test/main/subselect_sj_jcl6.result b/mysql-test/main/subselect_sj_jcl6.result
index f7f87fc2511..acfafde6d7e 100644
--- a/mysql-test/main/subselect_sj_jcl6.result
+++ b/mysql-test/main/subselect_sj_jcl6.result
@@ -2569,9 +2569,24 @@ CREATE TABLE t1 ( a INT PRIMARY KEY, b INT, KEY(b) );
 INSERT INTO t1 VALUES
 (1,2),(2,1),(3,3),(4,2),(5,5),
 (6,3),(7,1),(8,4),(9,3),(10,2);
-CREATE TABLE t2 ( c INT, d INT, UNIQUE KEY(c) );
+CREATE TABLE t2 ( c INT, d INT, KEY(c) );
 INSERT INTO t2 VALUES
 (1,2),(2,1),(3,3),(4,2),(5,5),(6,3),(7,1);
+analyze table t1,t2;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+test.t2	analyze	status	Engine-independent statistics collected
+test.t2	analyze	status	OK
+explain
+SELECT a, b, d FROM t1, t2
+WHERE ( b, d ) IN
+( SELECT b, d FROM t1, t2 WHERE b = c );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	7	
+1	PRIMARY	t1	index	b	b	5	NULL	10	Using where; Using index; LooseScan
+1	PRIMARY	t2	ref	c	c	5	test.t1.b	1	Using where; FirstMatch(t1)
+1	PRIMARY	t1	ref	b	b	5	test.t1.b	2	Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
 SELECT a, b, d FROM t1, t2
 WHERE ( b, d ) IN
 ( SELECT b, d FROM t1, t2 WHERE b = c );
@@ -2596,6 +2611,52 @@ a	b	d
 10	2	1
 10	2	1
 DROP TABLE t1, t2;
+# Another testcase for the above that still uses LooseScan:
+create table t0(a int primary key);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t10(a int primary key);
+insert into t10 select A.a + B.a* 10 + C.a * 100 from t0 A, t0 B, t0 C;
+create table t1 (
+pk int primary key auto_increment,
+kp1 int,
+kp2 int,
+filler char(100),
+key (kp1, kp2)
+);
+insert into t1 (kp1, kp2, filler) 
+select 
+A.a, B.a, 'filler-data'
+from t0 A, t0 B;
+create table t2 (a int, filler char(100), key(a));
+create table t3 (a int);
+insert into t3 values (1),(2);
+insert into t2 
+select (A.a+1000*B.a)/20, 'filler_data' from t10 A, t0 B;
+analyze table t1,t2,t3;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	Table is already up to date
+test.t2	analyze	status	Engine-independent statistics collected
+test.t2	analyze	status	Table is already up to date
+test.t3	analyze	status	Engine-independent statistics collected
+test.t3	analyze	status	OK
+delete from t1 where kp2 in (1,3);
+# Ref + LooseScan on t1:
+explain select sum(t2.a)
+from t2,t3
+where (t3.a,t2.a) in (select kp1,kp2 from t1,t0 where t0.a=2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t0	const	PRIMARY	PRIMARY	4	const	1	Using index
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	PRIMARY	t1	ref	kp1	kp1	5	test.t3.a	10	Using where; Using index; LooseScan
+1	PRIMARY	t2	ref	a	a	5	test.t1.kp2	19	Using index
+select sum(t2.a)
+from t2,t3
+where (t3.a,t2.a) in (select kp1,kp2 from t1,t0 where t0.a=2);
+sum(t2.a)
+1640
+drop table t0,t10;
+drop table t1,t2,t3;
 #
 # BUG#920713: Wrong result (missing rows) with firstmatch+BNL, IN subquery, ...
 #
diff --git a/mysql-test/main/subselect_sj_nonmerged.result b/mysql-test/main/subselect_sj_nonmerged.result
index 47970668ae5..4d9a70e6bba 100644
--- a/mysql-test/main/subselect_sj_nonmerged.result
+++ b/mysql-test/main/subselect_sj_nonmerged.result
@@ -67,9 +67,9 @@ insert into t4 select A.a + 10*B.a, A.a + 10*B.a, 'filler' from t0 A, t0 B;
 explain select * from t0, t4 where 
 t4.b=t0.a and t4.a in (select max(t2.a) from t1, t2 group by t2.b);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	
-1	PRIMARY	t4	ALL	a	NULL	NULL	NULL	100	Using where; Using join buffer (flat, BNL join)
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	test.t4.a	1	
+1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	5	
+1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	t4	ref	a	a	10	<subquery2>.max(t2.a),test.t0.a	1	
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	5	Using temporary
 2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	10	Using join buffer (flat, BNL join)
 insert into t4 select 100 + (B.a *100 + A.a), 100 + (B.a*100 + A.a), 'filler' from t4 A, t0 B;
@@ -77,9 +77,9 @@ explain select * from t4 where
 t4.a in (select max(t2.a) from t1, t2 group by t2.b) and 
 t4.b in (select max(t2.a) from t1, t2 group by t2.b);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	5	
-1	PRIMARY	t4	ref	a	a	5	<subquery2>.max(t2.a)	12	Using index condition
-1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	4	test.t4.b	1	
+1	PRIMARY	<subquery3>	ALL	distinct_key	NULL	NULL	NULL	5	
+1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	5	Using join buffer (flat, BNL join)
+1	PRIMARY	t4	ref	a	a	10	<subquery2>.max(t2.a),<subquery3>.max(t2.a)	1	
 3	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	5	Using temporary
 3	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	10	Using join buffer (flat, BNL join)
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	5	Using temporary
diff --git a/mysql-test/main/table_elim.result b/mysql-test/main/table_elim.result
index 2bfbbfb433f..bc03e1b251d 100644
--- a/mysql-test/main/table_elim.result
+++ b/mysql-test/main/table_elim.result
@@ -279,7 +279,7 @@ insert into t2 values
 explain select t1.* from t1 left join t2 on t2.a=t1.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index	NULL	PRIMARY	10	NULL	2	Using index
-1	SIMPLE	t2	ref	a	a	3	test.t1.a	2	Using where
+1	SIMPLE	t2	ref	a	a	3	test.t1.a	1	Using where
 drop table t1, t2;
 #
 # check UPDATE/DELETE that look like they could be eliminated
diff --git a/mysql-test/main/win.result b/mysql-test/main/win.result
index 85d645359ea..51a0f35ad61 100644
--- a/mysql-test/main/win.result
+++ b/mysql-test/main/win.result
@@ -3607,6 +3607,33 @@ b	row_number() over (partition by sum(a)+1)
 2000	1
 drop table t1;
 #
+# MDEV-18015: Assertion `global_status_var.global_memory_used == 0' failed when using UDF,
+# window functions and views
+#
+create table t1 (id int, n1 int);
+insert into t1 values (1,1),(2,1),(3,2),(4,4);
+explain
+select max(n1) over (partition by 'abc') from t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	Using temporary
+select max(n1) over (partition by 'abc') from t1;
+max(n1) over (partition by 'abc')
+4
+4
+4
+4
+explain
+select rank() over (partition by 'abc' order by 'xyz') from t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	Using temporary
+select rank() over (partition by 'abc' order by 'xyz') from t1;
+rank() over (partition by 'abc' order by 'xyz')
+1
+1
+1
+1
+drop table t1;
+#
 # End of 10.2 tests
 #
 #
diff --git a/mysql-test/main/win.test b/mysql-test/main/win.test
index c5c5215b215..01b8f17b0f2 100644
--- a/mysql-test/main/win.test
+++ b/mysql-test/main/win.test
@@ -2326,6 +2326,22 @@ select b, row_number() over (partition by sum(a)+1) from t1 group by b;
 drop table t1;
 
 --echo #
+--echo # MDEV-18015: Assertion `global_status_var.global_memory_used == 0' failed when using UDF,
+--echo # window functions and views
+--echo #
+
+create table t1 (id int, n1 int);
+insert into t1 values (1,1),(2,1),(3,2),(4,4);
+explain
+select max(n1) over (partition by 'abc') from t1;
+select max(n1) over (partition by 'abc') from t1;
+
+explain
+select rank() over (partition by 'abc' order by 'xyz') from t1;
+select rank() over (partition by 'abc' order by 'xyz') from t1;
+drop table t1;
+
+--echo #
 --echo # End of 10.2 tests
 --echo #