Merge 10.2 into 10.3

6 files changed, 241 insertions, 0 deletions

diff --git a/mysql-test/main/cte_nonrecursive.result b/mysql-test/main/cte_nonrecursive.result
index 4711a540f99..9bce4f0a9bd 100644
--- a/mysql-test/main/cte_nonrecursive.result
+++ b/mysql-test/main/cte_nonrecursive.result
@@ -1641,3 +1641,21 @@ a	b
 drop procedure p1;
 drop procedure p2;
 drop table t1;
+#
+# MDEV-17107: PS for CREATE OR REPLACE VIEW defined by SELECT with CTEs
+#
+create table t1(a int);
+insert into t1 values (3), (1), (2);
+create table t2 (b int);
+insert into t2 values (2), (10);
+prepare stmt from
+"create or replace view v1 as
+ with t as (select s.a from (select t1.a from t1) s),
+      r as(select t.a from t2, t where t2.b=t.a)
+ select a from r;";
+execute stmt;
+select * from v1;
+a
+2
+drop view v1;
+drop table t1,t2;
diff --git a/mysql-test/main/cte_nonrecursive.test b/mysql-test/main/cte_nonrecursive.test
index 648fc89975c..920c27a70f9 100644
--- a/mysql-test/main/cte_nonrecursive.test
+++ b/mysql-test/main/cte_nonrecursive.test
@@ -1147,3 +1147,24 @@ select * from t1;
 drop procedure p1;
 drop procedure p2;
 drop table t1;
+
+--echo #
+--echo # MDEV-17107: PS for CREATE OR REPLACE VIEW defined by SELECT with CTEs
+--echo #
+
+create table t1(a int);
+insert into t1 values (3), (1), (2);
+create table t2 (b int);
+insert into t2 values (2), (10);
+
+prepare stmt from
+"create or replace view v1 as
+ with t as (select s.a from (select t1.a from t1) s),
+      r as(select t.a from t2, t where t2.b=t.a)
+ select a from r;";
+
+execute stmt;
+select * from v1;
+
+drop view v1;
+drop table t1,t2;
diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result
index b6ebc4ee855..4d160c3d31d 100644
--- a/mysql-test/main/derived_cond_pushdown.result
+++ b/mysql-test/main/derived_cond_pushdown.result
@@ -16393,6 +16393,152 @@ a	b	c
 1	2	2
 3	2	2
 DROP TABLE t1;
+#
+# MDEV-17354: INSERT SELECT with condition pushdown into derived
+#
+CREATE TABLE t1 (f INT NOT NULL);
+INSERT INTO t1 VALUES (3), (7), (3);
+CREATE ALGORITHM= TEMPTABLE VIEW v1 AS SELECT * FROM ( SELECT * FROM t1 ) AS sq;
+INSERT INTO t1
+SELECT * FROM ( SELECT t1.f FROM v1 JOIN t1 ) AS t WHERE f IS NOT NULL;
+EXPLAIN INSERT INTO t1
+SELECT * FROM ( SELECT t1.f FROM v1 JOIN t1 ) AS t WHERE f IS NOT NULL;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	144	Using where
+2	DERIVED	<derived3>	ALL	NULL	NULL	NULL	NULL	12	
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer (flat, BNL join)
+3	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	12	
+EXPLAIN FORMAT=JSON INSERT INTO t1
+SELECT * FROM ( SELECT t1.f FROM v1 JOIN t1 ) AS t WHERE f IS NOT NULL;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "<derived2>",
+      "access_type": "ALL",
+      "rows": 144,
+      "filtered": 100,
+      "attached_condition": "t.f is not null",
+      "materialized": {
+        "query_block": {
+          "select_id": 2,
+          "table": {
+            "table_name": "<derived3>",
+            "access_type": "ALL",
+            "rows": 12,
+            "filtered": 100,
+            "materialized": {
+              "query_block": {
+                "select_id": 3,
+                "table": {
+                  "table_name": "t1",
+                  "access_type": "ALL",
+                  "rows": 12,
+                  "filtered": 100
+                }
+              }
+            }
+          },
+          "block-nl-join": {
+            "table": {
+              "table_name": "t1",
+              "access_type": "ALL",
+              "rows": 12,
+              "filtered": 100,
+              "attached_condition": "t1.f is not null"
+            },
+            "buffer_type": "flat",
+            "buffer_size": "256Kb",
+            "join_type": "BNL"
+          }
+        }
+      }
+    }
+  }
+}
+SELECT * FROM t1;
+f
+3
+7
+3
+3
+3
+3
+7
+7
+7
+3
+3
+3
+DELETE FROM t1;
+INSERT INTO t1 VALUES (3), (7), (3);
+INSERT INTO t1
+SELECT * FROM ( SELECT t1.f FROM v1 JOIN t1 ON v1.f=t1.f) AS t
+WHERE f IS NOT NULL;
+EXPLAIN FORMAT=JSON INSERT INTO t1
+SELECT * FROM ( SELECT t1.f FROM v1 JOIN t1 ON v1.f=t1.f) AS t
+WHERE f IS NOT NULL;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "<derived2>",
+      "access_type": "ALL",
+      "rows": 16,
+      "filtered": 100,
+      "attached_condition": "t.f is not null",
+      "materialized": {
+        "query_block": {
+          "select_id": 2,
+          "table": {
+            "table_name": "t1",
+            "access_type": "ALL",
+            "rows": 8,
+            "filtered": 100,
+            "attached_condition": "t1.f is not null"
+          },
+          "table": {
+            "table_name": "<derived3>",
+            "access_type": "ref",
+            "possible_keys": ["key0"],
+            "key": "key0",
+            "key_length": "4",
+            "used_key_parts": ["f"],
+            "ref": ["test.t1.f"],
+            "rows": 2,
+            "filtered": 100,
+            "materialized": {
+              "query_block": {
+                "select_id": 3,
+                "table": {
+                  "table_name": "t1",
+                  "access_type": "ALL",
+                  "rows": 8,
+                  "filtered": 100,
+                  "attached_condition": "t1.f is not null"
+                }
+              }
+            }
+          }
+        }
+      }
+    }
+  }
+}
+SELECT * FROM t1;
+f
+3
+7
+3
+3
+3
+7
+3
+3
+DROP VIEW v1;
+DROP TABLE t1;
 # Start of 10.3 tests
 #
 # MDEV-16801: splittable materialized derived/views with
diff --git a/mysql-test/main/derived_cond_pushdown.test b/mysql-test/main/derived_cond_pushdown.test
index 1de1e8a8d15..340aa1f5a54 100644
--- a/mysql-test/main/derived_cond_pushdown.test
+++ b/mysql-test/main/derived_cond_pushdown.test
@@ -3109,6 +3109,40 @@ WHERE ((a,b) IN ((1,2),(3,2)));
 
 DROP TABLE t1;
 
+
+--echo #
+--echo # MDEV-17354: INSERT SELECT with condition pushdown into derived
+--echo #
+
+CREATE TABLE t1 (f INT NOT NULL);
+INSERT INTO t1 VALUES (3), (7), (3);
+
+CREATE ALGORITHM= TEMPTABLE VIEW v1 AS SELECT * FROM ( SELECT * FROM t1 ) AS sq;
+
+let $q1=
+INSERT INTO t1
+SELECT * FROM ( SELECT t1.f FROM v1 JOIN t1 ) AS t WHERE f IS NOT NULL;
+
+eval $q1;
+eval EXPLAIN $q1;
+eval EXPLAIN FORMAT=JSON $q1;
+SELECT * FROM t1;
+
+DELETE FROM t1;
+INSERT INTO t1 VALUES (3), (7), (3);
+
+let $q2=
+INSERT INTO t1
+SELECT * FROM ( SELECT t1.f FROM v1 JOIN t1 ON v1.f=t1.f) AS t
+  WHERE f IS NOT NULL;
+
+eval $q2;
+eval EXPLAIN FORMAT=JSON $q2;
+SELECT * FROM t1;
+
+DROP VIEW v1;
+DROP TABLE t1;
+
 --echo # Start of 10.3 tests
 
 --echo #
diff --git a/mysql-test/main/win.result b/mysql-test/main/win.result
index a81d3b8c2ba..46a5eaea9f8 100644
--- a/mysql-test/main/win.result
+++ b/mysql-test/main/win.result
@@ -3315,6 +3315,18 @@ COUNT(DISTINCT t2.a2)	rank() OVER (ORDER BY t2.b1)
 1	3
 DROP TABLE t1,t2;
 #
+# MDEV-16990: server crashes in base_list_iterator::next
+#
+CREATE TABLE t1(i int);
+insert into t1 values (1),(2);
+SELECT DISTINCT row_number() OVER (), MAX(1) FROM t1;
+row_number() OVER ()	MAX(1)
+1	1
+SELECT DISTINCT BIT_AND(0)  OVER (), MAX(1) FROM t1;
+BIT_AND(0)  OVER ()	MAX(1)
+0	1
+drop table t1;
+#
 # Start of 10.3 tests
 #
 #
diff --git a/mysql-test/main/win.test b/mysql-test/main/win.test
index f1d35bb347d..efc8c712526 100644
--- a/mysql-test/main/win.test
+++ b/mysql-test/main/win.test
@@ -2084,6 +2084,16 @@ FROM t2 ,t1 GROUP BY t2.b1 ORDER BY t1.a1;
 DROP TABLE t1,t2;
 
 --echo #
+--echo # MDEV-16990: server crashes in base_list_iterator::next
+--echo #
+
+CREATE TABLE t1(i int);
+insert into t1 values (1),(2);
+SELECT DISTINCT row_number() OVER (), MAX(1) FROM t1;
+SELECT DISTINCT BIT_AND(0)  OVER (), MAX(1) FROM t1;
+drop table t1;
+
+--echo #
 --echo # Start of 10.3 tests
 --echo #