summaryrefslogtreecommitdiff
path: root/mysql-test/main
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/main')
-rw-r--r--mysql-test/main/invisible_field.result4
-rw-r--r--mysql-test/main/join.result31
-rw-r--r--mysql-test/main/join.test30
-rw-r--r--mysql-test/main/opt_trace.result20
-rw-r--r--mysql-test/main/order_by.result2
-rw-r--r--mysql-test/main/subselect_no_semijoin.result55
-rw-r--r--mysql-test/main/subselect_no_semijoin.test23
-rw-r--r--mysql-test/main/subselect_sj.result79
-rw-r--r--mysql-test/main/subselect_sj.test52
-rw-r--r--mysql-test/main/subselect_sj_jcl6.result63
-rw-r--r--mysql-test/main/subselect_sj_nonmerged.result12
-rw-r--r--mysql-test/main/table_elim.result2
-rw-r--r--mysql-test/main/win.result27
-rw-r--r--mysql-test/main/win.test16
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 #
View Lorry Controller Status