Merge branch '10.3' into 10.4

8 files changed, 609 insertions, 2 deletions

diff --git a/mysql-test/main/cte_recursive.result b/mysql-test/main/cte_recursive.result
index 7aa87fcbc6e..8de8a571052 100644
--- a/mysql-test/main/cte_recursive.result
+++ b/mysql-test/main/cte_recursive.result
@@ -5604,6 +5604,178 @@ r
 3
 drop table t1,t2,t3,x;
 #
+# MDEV-30248: Embedded non-recursive CTE referring to base table 'x'
+#             within a CTE with name 'x' used in a subquery from
+#             select list of another CTE
+#
+CREATE TABLE x (a int) ENGINE=MyISAM;
+INSERT INTO x VALUES (3),(7),(1);
+CREATE TABLE t1 (b int) ENGINE=MYISAM;
+INSERT INTO t1 VALUES (1);
+WITH cte AS
+(
+SELECT
+(
+WITH x AS
+(WITH x AS (SELECT a FROM x AS t) SELECT 1 AS b)
+SELECT b FROM x AS r
+) AS c
+)
+SELECT cte.c FROM cte;
+c
+1
+WITH cte AS
+(
+SELECT
+(
+WITH x AS
+(WITH x AS (SELECT a FROM x AS t) SELECT b FROM t1)
+SELECT b FROM x AS r
+) AS c
+)
+SELECT cte.c FROM cte;
+c
+1
+WITH cte AS
+(
+SELECT
+(
+WITH x AS
+(WITH y AS (SELECT a FROM x AS t) SELECT b FROM t1)
+SELECT b FROM x AS r
+) AS c
+)
+SELECT cte.c FROM cte;
+c
+1
+WITH cte AS
+(
+SELECT
+(
+WITH x AS
+(WITH y(b) AS (SELECT a FROM x AS t LIMIT 1) SELECT b FROM y)
+SELECT b FROM x AS r
+) AS c
+)
+SELECT cte.c FROM cte;
+c
+3
+WITH cte AS
+(
+SELECT
+(
+WITH x AS
+(WITH x(b) AS (SELECT a FROM x AS t LIMIT 1) SELECT b FROM x)
+SELECT b FROM x AS r
+) AS c
+)
+SELECT cte.c FROM cte;
+c
+3
+WITH x AS
+(
+SELECT
+(
+WITH x AS
+(WITH x AS (SELECT a FROM x AS t) SELECT 1 AS b)
+SELECT b FROM x AS r
+) AS c
+)
+SELECT x.c from x;
+c
+1
+WITH cte AS
+(
+SELECT
+(
+WITH x AS
+(WITH x AS (SELECT a FROM x AS t) SELECT 2 AS b)
+SELECT r1.b FROM x AS r1, x AS r2 WHERE r1.b=r2.b
+) AS c
+)
+SELECT cte.c from cte;
+c
+2
+DROP TABLE x;
+WITH cte AS
+(
+SELECT
+(
+WITH x AS
+(WITH x AS (SELECT a FROM x AS t) SELECT 1 AS b)
+SELECT b FROM x AS r
+) AS c
+)
+SELECT cte.c FROM cte;
+ERROR 42S02: Table 'test.x' doesn't exist
+WITH cte AS
+(
+SELECT
+(
+WITH x AS
+(WITH x AS (SELECT a FROM x AS t) SELECT b FROM t1)
+SELECT b FROM x AS r
+) AS c
+)
+SELECT cte.c FROM cte;
+ERROR 42S02: Table 'test.x' doesn't exist
+WITH cte AS
+(
+SELECT
+(
+WITH x AS
+(WITH y AS (SELECT a FROM x AS t) SELECT b FROM t1)
+SELECT b FROM x AS r
+) AS c
+)
+SELECT cte.c FROM cte;
+ERROR 42S02: Table 'test.x' doesn't exist
+WITH cte AS
+(
+SELECT
+(
+WITH x AS
+(WITH y(b) AS (SELECT a FROM x AS t LIMIT 1) SELECT b FROM y)
+SELECT b FROM x AS r
+) AS c
+)
+SELECT cte.c FROM cte;
+ERROR 42S02: Table 'test.x' doesn't exist
+WITH cte AS
+(
+SELECT
+(
+WITH x AS
+(WITH x(b) AS (SELECT a FROM x AS t LIMIT 1) SELECT b FROM x)
+SELECT b FROM x AS r
+) AS c
+)
+SELECT cte.c FROM cte;
+ERROR 42S02: Table 'test.x' doesn't exist
+WITH x AS
+(
+SELECT
+(
+WITH x AS
+(WITH x AS (SELECT a FROM x AS t) SELECT 1 AS b)
+SELECT b FROM x AS r
+) AS c
+)
+SELECT x.c from x;
+ERROR 42S02: Table 'test.x' doesn't exist
+WITH cte AS
+(
+SELECT
+(
+WITH x AS
+(WITH x AS (SELECT a FROM x AS t) SELECT 2 AS b)
+SELECT r1.b FROM x AS r1, x AS r2 WHERE r1.b=r2.b
+) AS c
+)
+SELECT cte.c from cte;
+ERROR 42S02: Table 'test.x' doesn't exist
+DROP TABLE t1;
+#
 # End of 10.3 tests
 #
 #
diff --git a/mysql-test/main/cte_recursive.test b/mysql-test/main/cte_recursive.test
index 3cc3b66b9c3..4d21a494bdc 100644
--- a/mysql-test/main/cte_recursive.test
+++ b/mysql-test/main/cte_recursive.test
@@ -3872,6 +3872,129 @@ select * from cte;
 drop table t1,t2,t3,x;
 
 --echo #
+--echo # MDEV-30248: Embedded non-recursive CTE referring to base table 'x'
+--echo #             within a CTE with name 'x' used in a subquery from
+--echo #             select list of another CTE
+--echo #
+
+CREATE TABLE x (a int) ENGINE=MyISAM;
+INSERT INTO x VALUES (3),(7),(1);
+CREATE TABLE t1 (b int) ENGINE=MYISAM;
+INSERT INTO t1 VALUES (1);
+
+let $q1=
+WITH cte AS
+(
+  SELECT
+    (
+      WITH x AS
+      (WITH x AS (SELECT a FROM x AS t) SELECT 1 AS b)
+      SELECT b FROM x AS r
+    ) AS c
+)
+SELECT cte.c FROM cte;
+eval $q1;
+
+let $q2=
+WITH cte AS
+(
+  SELECT
+    (
+      WITH x AS
+      (WITH x AS (SELECT a FROM x AS t) SELECT b FROM t1)
+      SELECT b FROM x AS r
+    ) AS c
+)
+SELECT cte.c FROM cte;
+eval $q2;
+
+let $q3=
+WITH cte AS
+(
+  SELECT
+    (
+      WITH x AS
+      (WITH y AS (SELECT a FROM x AS t) SELECT b FROM t1)
+      SELECT b FROM x AS r
+    ) AS c
+)
+SELECT cte.c FROM cte;
+eval $q3;
+
+
+let $q4=
+WITH cte AS
+(
+  SELECT
+    (
+      WITH x AS
+      (WITH y(b) AS (SELECT a FROM x AS t LIMIT 1) SELECT b FROM y)
+      SELECT b FROM x AS r
+    ) AS c
+)
+SELECT cte.c FROM cte;
+eval $q4;
+
+let $q5=
+WITH cte AS
+(
+  SELECT
+    (
+      WITH x AS
+      (WITH x(b) AS (SELECT a FROM x AS t LIMIT 1) SELECT b FROM x)
+      SELECT b FROM x AS r
+    ) AS c
+)
+SELECT cte.c FROM cte;
+eval $q5;
+
+let $q6=
+WITH x AS
+(
+  SELECT
+    (
+      WITH x AS
+      (WITH x AS (SELECT a FROM x AS t) SELECT 1 AS b)
+      SELECT b FROM x AS r
+    ) AS c
+)
+SELECT x.c from x;
+eval $q6;
+
+let $q7=
+WITH cte AS
+(
+  SELECT
+    (
+      WITH x AS
+      (WITH x AS (SELECT a FROM x AS t) SELECT 2 AS b)
+      SELECT r1.b FROM x AS r1, x AS r2 WHERE r1.b=r2.b
+    ) AS c
+)
+SELECT cte.c from cte;
+eval $q7;
+
+
+DROP TABLE x;
+
+--ERROR ER_NO_SUCH_TABLE
+eval $q1;
+--ERROR ER_NO_SUCH_TABLE
+eval $q2;
+--ERROR ER_NO_SUCH_TABLE
+eval $q3;
+--ERROR ER_NO_SUCH_TABLE
+eval $q4;
+--ERROR ER_NO_SUCH_TABLE
+eval $q5;
+--ERROR ER_NO_SUCH_TABLE
+eval $q6;
+--ERROR ER_NO_SUCH_TABLE
+eval $q7;
+
+DROP TABLE t1;
+
+--echo #
 --echo # End of 10.3 tests
 --echo #
 
diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result
index 2c2d959a953..41f9ac6fca1 100644
--- a/mysql-test/main/derived_cond_pushdown.result
+++ b/mysql-test/main/derived_cond_pushdown.result
@@ -18190,6 +18190,69 @@ DROP TABLE transaction_items;
 DROP TABLE transactions;
 DROP TABLE charges;
 DROP TABLE ledgers;
+#
+# MDEV-30081: Splitting from a constant mergeable derived table
+#             used in inner part of an outer join.
+#
+CREATE TABLE t1 ( id int PRIMARY KEY ) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (3),(4),(7);
+CREATE TABLE t2 (
+id int,  id1 int, wid int, PRIMARY KEY (id), KEY (id1), KEY (wid)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (4,4,6),(7,7,7);
+CREATE TABLE t3 (
+wid int, wtid int, otid int, oid int,
+PRIMARY KEY (wid), KEY (wtid), KEY (otid), KEY (oid)
+) ENGINE=MyISAM;
+INSERT INTO t3 VALUES (6,30,6,6),(7,17,7,7);
+CREATE TABLE t4 ( id int, a int, PRIMARY KEY (id), KEY (a) )  ENGINE=MyISAM;
+INSERT INTO t4 VALUES (1,17),(2,15),(3,49),(4,3),(5,45),(6,38),(7,17);
+CREATE TABLE t5 (
+id int, id1 int, PRIMARY KEY (id), KEY id1 (id1)
+) ENGINE=MyISAM ;
+INSERT INTO t5 VALUES (1,17),(2,15),(3,49),(4,3),(5,45),(6,38),(7,17);
+ANALYZE TABLE t1,t2,t3,t4,t5;
+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
+test.t3	analyze	status	Engine-independent statistics collected
+test.t3	analyze	status	OK
+test.t4	analyze	status	Engine-independent statistics collected
+test.t4	analyze	status	OK
+test.t5	analyze	status	Engine-independent statistics collected
+test.t5	analyze	status	OK
+CREATE VIEW v1 AS (SELECT id1 FROM t5 GROUP BY id1);
+SELECT t3.*, t1.id AS t1_id, t2.id AS t2_id, dt.*, v1.*
+FROM
+t1, t2, t3
+LEFT JOIN
+(SELECT t4.* FROM t4 WHERE t4.a=3) dt
+ON t3.oid = dt.id AND t3.otid = 14
+LEFT JOIN v1
+ON (v1.id1 = dt.a)
+WHERE t3.oid = t1.id AND t3.oid = t2.id AND t3.wid = 7;
+wid	wtid	otid	oid	t1_id	t2_id	id	a	id1
+7	17	7	7	7	7	NULL	NULL	NULL
+EXPLAIN SELECT t3.*, t1.id AS t1_id, t2.id AS t2_id, dt.*, v1.*
+FROM
+t1, t2, t3
+LEFT JOIN
+(SELECT t4.* FROM t4 WHERE t4.a=3) dt
+ON t3.oid = dt.id AND t3.otid = 14
+LEFT JOIN v1
+ON (v1.id1 = dt.a)
+WHERE t3.oid = t1.id AND t3.oid = t2.id AND t3.wid = 7;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t3	const	PRIMARY,oid	PRIMARY	4	const	1	
+1	PRIMARY	t1	const	PRIMARY	PRIMARY	4	const	1	Using index
+1	PRIMARY	t2	const	PRIMARY	PRIMARY	4	const	1	Using index
+1	PRIMARY	t4	const	PRIMARY,a	NULL	NULL	NULL	1	Impossible ON condition
+1	PRIMARY	<derived3>	ref	key0	key0	5	const	0	Using where
+3	LATERAL DERIVED	t5	ref	id1	id1	5	const	0	Using index
+DROP VIEW v1;
+DROP TABLE t1,t2,t3,t4,t5;
 # End of 10.3 tests
 #
 # MDEV-18679: materialized view with SELECT S containing materialized
diff --git a/mysql-test/main/derived_cond_pushdown.test b/mysql-test/main/derived_cond_pushdown.test
index bd38716f2a6..6cfe23b7866 100644
--- a/mysql-test/main/derived_cond_pushdown.test
+++ b/mysql-test/main/derived_cond_pushdown.test
@@ -3870,6 +3870,55 @@ DROP TABLE transactions;
 DROP TABLE charges;
 DROP TABLE ledgers;
 
+
+--echo #
+--echo # MDEV-30081: Splitting from a constant mergeable derived table
+--echo #             used in inner part of an outer join.
+--echo #
+
+  CREATE TABLE t1 ( id int PRIMARY KEY ) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (3),(4),(7);
+
+CREATE TABLE t2 (
+  id int,  id1 int, wid int, PRIMARY KEY (id), KEY (id1), KEY (wid)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (4,4,6),(7,7,7);
+
+CREATE TABLE t3 (
+  wid int, wtid int, otid int, oid int,
+  PRIMARY KEY (wid), KEY (wtid), KEY (otid), KEY (oid)
+) ENGINE=MyISAM;
+INSERT INTO t3 VALUES (6,30,6,6),(7,17,7,7);
+
+CREATE TABLE t4 ( id int, a int, PRIMARY KEY (id), KEY (a) )  ENGINE=MyISAM;
+INSERT INTO t4 VALUES (1,17),(2,15),(3,49),(4,3),(5,45),(6,38),(7,17);
+
+CREATE TABLE t5 (
+  id int, id1 int, PRIMARY KEY (id), KEY id1 (id1)
+) ENGINE=MyISAM ;
+INSERT INTO t5 VALUES (1,17),(2,15),(3,49),(4,3),(5,45),(6,38),(7,17);
+
+ANALYZE TABLE t1,t2,t3,t4,t5;
+
+CREATE VIEW v1 AS (SELECT id1 FROM t5 GROUP BY id1);
+
+let $q=
+SELECT t3.*, t1.id AS t1_id, t2.id AS t2_id, dt.*, v1.*
+FROM
+  t1, t2, t3
+  LEFT JOIN
+  (SELECT t4.* FROM t4 WHERE t4.a=3) dt
+  ON t3.oid = dt.id AND t3.otid = 14
+  LEFT JOIN v1
+  ON (v1.id1 = dt.a)
+WHERE t3.oid = t1.id AND t3.oid = t2.id AND t3.wid = 7;
+
+eval $q;
+eval EXPLAIN $q;
+
+DROP VIEW v1;
+DROP TABLE t1,t2,t3,t4,t5;
+
 --echo # End of 10.3 tests
 
 --echo #
diff --git a/mysql-test/main/win.result b/mysql-test/main/win.result
index c9a651c7e5e..89dab46f103 100644
--- a/mysql-test/main/win.result
+++ b/mysql-test/main/win.result
@@ -4272,11 +4272,13 @@ GROUP BY
 LEFT((SYSDATE()), 'foo')
 WITH ROLLUP;
 SUM(b)       OVER (PARTITION BY a)	ROW_NUMBER() OVER (PARTITION BY b)
-NULL	1
-NULL	1
+0	1
+0	2
 Warnings:
 Warning	1292	Truncated incorrect INTEGER value: 'foo'
 Warning	1292	Truncated incorrect INTEGER value: 'foo'
+Warning	1292	Truncated incorrect DOUBLE value: 'bar'
+Warning	1292	Truncated incorrect DOUBLE value: 'bar'
 drop table t1;
 #
 #
@@ -4335,6 +4337,46 @@ pk	a	bit_or
 DROP TABLE t2;
 DROP TABLE t1;
 #
+# MDEV-15178: Filesort::make_sortorder: Assertion `pos->field != __null |
+#
+CREATE TABLE t1 (i1 int, a int);
+INSERT INTO t1 VALUES (1, 1), (2, 2),(3, 3);
+CREATE TABLE t2 (i2 int);
+INSERT INTO t2 VALUES (1),(2),(5),(1),(7),(4),(3);
+SELECT
+a,
+RANK() OVER (ORDER BY SUM(DISTINCT i1))
+FROM
+t1, t2 WHERE t2.i2 = t1.i1
+GROUP BY
+a;
+a	RANK() OVER (ORDER BY SUM(DISTINCT i1))
+1	1
+2	2
+3	3
+DROP TABLE t1, t2;
+#
+# MDEV-17014: Crash server using ROW_NUMBER() OVER (PARTITION ..)
+#
+CREATE TABLE t1 (UID BIGINT);
+CREATE TABLE t2 (UID BIGINT);
+CREATE TABLE t3 (UID BIGINT);
+insert into t1 VALUES (1),(2);
+insert into t2 VALUES (1),(2);
+insert into t3 VALUES (1),(2);
+SELECT
+ROW_NUMBER() OVER (PARTITION BY GROUP_CONCAT(TT1.UID))
+FROM t1 TT1,
+t2 TT2,
+t3 TT3
+WHERE TT3.UID = TT1.UID AND TT2.UID = TT3.UID
+GROUP BY TT1.UID
+;
+ROW_NUMBER() OVER (PARTITION BY GROUP_CONCAT(TT1.UID))
+1
+1
+DROP TABLE t1, t2, t3;
+#
 # End of 10.3 tests
 #
 #
diff --git a/mysql-test/main/win.test b/mysql-test/main/win.test
index 3588768f662..939c36e7c5e 100644
--- a/mysql-test/main/win.test
+++ b/mysql-test/main/win.test
@@ -2817,6 +2817,46 @@ DROP TABLE t2;
 
 DROP TABLE t1;
 
+--echo #
+--echo # MDEV-15178: Filesort::make_sortorder: Assertion `pos->field != __null |
+--echo #
+
+CREATE TABLE t1 (i1 int, a int);
+INSERT INTO t1 VALUES (1, 1), (2, 2),(3, 3);
+
+CREATE TABLE t2 (i2 int);
+INSERT INTO t2 VALUES (1),(2),(5),(1),(7),(4),(3);
+
+SELECT
+  a,
+  RANK() OVER (ORDER BY SUM(DISTINCT i1))
+FROM
+  t1, t2 WHERE t2.i2 = t1.i1
+GROUP BY
+  a;
+
+DROP TABLE t1, t2;
+
+--echo #
+--echo # MDEV-17014: Crash server using ROW_NUMBER() OVER (PARTITION ..)
+--echo #
+CREATE TABLE t1 (UID BIGINT);
+CREATE TABLE t2 (UID BIGINT);
+CREATE TABLE t3 (UID BIGINT);
+
+insert into t1 VALUES (1),(2);
+insert into t2 VALUES (1),(2);
+insert into t3 VALUES (1),(2);
+SELECT
+ROW_NUMBER() OVER (PARTITION BY GROUP_CONCAT(TT1.UID))
+FROM t1 TT1,
+  t2 TT2,
+  t3 TT3
+WHERE TT3.UID = TT1.UID AND TT2.UID = TT3.UID
+GROUP BY TT1.UID
+;
+
+DROP TABLE t1, t2, t3;
 
 --echo #
 --echo # End of 10.3 tests
diff --git a/mysql-test/main/win_orderby.result b/mysql-test/main/win_orderby.result
index bf4a40a4db3..1a9860c1c76 100644
--- a/mysql-test/main/win_orderby.result
+++ b/mysql-test/main/win_orderby.result
@@ -24,3 +24,66 @@ pk	count(a) over (order by pk rows between 2 preceding and 2 following)
 28	5
 27	5
 drop table t0,t1;
+#
+# MDEV-30052: Crash with a query containing nested WINDOW clauses
+#
+CREATE TABLE t1 (c INT);
+insert into t1 values (1),(2);
+UPDATE t1 SET c=1
+WHERE c=2
+ORDER BY
+(1 IN ((
+SELECT *
+FROM (SELECT * FROM t1) AS v1
+GROUP BY c
+WINDOW v2 AS (ORDER BY
+(SELECT *
+FROM t1
+GROUP BY c
+WINDOW v3 AS (PARTITION BY c)
+)
+)
+))
+);
+drop table t1;
+#
+# MDEV-29359: Server crashed with heap-use-after-free in
+# Field::is_null(long long) const (Just testcase)
+#
+CREATE TABLE t1 (id int);
+INSERT INTO t1 VALUES (-1),(0),(84);
+SELECT
+id IN (SELECT id
+FROM t1
+WINDOW w AS (ORDER BY (SELECT  1
+FROM t1
+WHERE
+EXISTS ( SELECT id
+FROM t1
+GROUP BY id
+WINDOW w2 AS (ORDER BY id)
+)
+)
+)
+)
+FROM t1;
+id IN (SELECT id
+FROM t1
+WINDOW w AS (ORDER BY (SELECT  1
+FROM t1
+WHERE
+EXISTS ( SELECT id
+FROM t1
+GROUP BY id
+WINDOW w2 AS (ORDER BY id)
+)
+)
+)
+)
+1
+1
+1
+DROP TABLE t1;
+#
+# End of 10.3 tests
+#
diff --git a/mysql-test/main/win_orderby.test b/mysql-test/main/win_orderby.test
index 7f02a582ea0..65421fc095c 100644
--- a/mysql-test/main/win_orderby.test
+++ b/mysql-test/main/win_orderby.test
@@ -33,3 +33,58 @@ limit 4;
 --disable_view_protocol
 
 drop table t0,t1;
+
+
+--echo #
+--echo # MDEV-30052: Crash with a query containing nested WINDOW clauses
+--echo #
+
+CREATE TABLE t1 (c INT);
+insert into t1 values (1),(2);
+UPDATE t1 SET c=1
+WHERE c=2
+ORDER BY
+  (1 IN ((
+      SELECT *
+      FROM (SELECT * FROM t1) AS v1
+      GROUP BY c
+      WINDOW v2 AS (ORDER BY
+                      (SELECT *
+                       FROM t1
+                       GROUP BY c
+                       WINDOW v3 AS (PARTITION BY c)
+                      )
+                    )
+    ))
+  );
+drop table t1;
+
+--echo #
+--echo # MDEV-29359: Server crashed with heap-use-after-free in
+--echo # Field::is_null(long long) const (Just testcase)
+--echo #
+
+CREATE TABLE t1 (id int);
+INSERT INTO t1 VALUES (-1),(0),(84);
+
+SELECT
+  id IN (SELECT id
+         FROM t1
+         WINDOW w AS (ORDER BY (SELECT  1
+                                FROM t1
+                                WHERE
+                                  EXISTS ( SELECT id
+                                           FROM t1
+                                           GROUP BY id
+                                           WINDOW w2 AS (ORDER BY id)
+                                          )
+                                )
+                      )
+         )
+FROM t1;
+
+DROP TABLE t1;
+
+--echo #
+--echo # End of 10.3 tests
+--echo #