Merging upstream version 1:10.11.7.

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 338 insertions, 0 deletions

diff --git a/mysql-test/main/cte_nonrecursive.result b/mysql-test/main/cte_nonrecursive.result
index f7871d4f..67a38ce0 100644
--- a/mysql-test/main/cte_nonrecursive.result
+++ b/mysql-test/main/cte_nonrecursive.result
@@ -2339,4 +2339,342 @@ set sql_mode="oracle";
 with data as (select 1 as id)
 select id into @myid from data;
 set sql_mode= @save_sql_mode;
+#
+# MDEV-31995 Bogus error executing PS for query using CTE with renaming of columns
+#
+create table t1 (a int, b int);
+insert into t1 values (1,1),(1,2),(1,3),(2,1),(2,2);
+create table t2 (a int, b int);
+insert into t2 values (3,1),(3,2),(3,3),(4,1),(4,2);
+with cte (c1,c2) as
+(select a as col1, sum(b) as col2 from t1 group by col1)
+select * from cte;
+c1	c2
+1	6
+2	3
+prepare st from "with cte (c1,c2) as
+(select a as col1, sum(b) as col2 from t1 group by col1)
+select * from cte";
+execute st;
+c1	c2
+1	6
+2	3
+execute st;
+c1	c2
+1	6
+2	3
+drop prepare st;
+create procedure sp() with cte (c1,c2) as
+(select a as col1, sum(b) as col2 from t1 group by col1)
+select * from cte;
+call sp();
+c1	c2
+1	6
+2	3
+call sp();
+c1	c2
+1	6
+2	3
+drop procedure sp;
+with cte (c1,c2) as
+(select a as col1, sum(b) as col2 from t1 order by col1)
+select * from cte;
+c1	c2
+1	9
+prepare st from "with cte (c1,c2) as
+(select a as col1, sum(b) as col2 from t1 order by col1)
+select * from cte";
+execute st;
+c1	c2
+1	9
+execute st;
+c1	c2
+1	9
+drop prepare st;
+create procedure sp() with cte (c1,c2) as
+(select a as col1, sum(b) as col2 from t1 order by col1)
+select * from cte;
+call sp();
+c1	c2
+1	9
+call sp();
+c1	c2
+1	9
+drop procedure sp;
+with cte (c1,c2) as
+(select a as col1, sum(b) as col2 from t1 where a > 1 group by col1
+union select a as col3, sum(b) as col4 from t2 where b > 2 group by col3),
+cte2 (c3, c4) as
+(select a as col5, sum(b) as col6 from t1 where a <= 1 group by col5
+union select a as col7, sum(b) as col8 from t2 where b <= 2 group by col7)
+select * from cte where c1=1 union select * from cte2 where c3=3;
+c1	c2
+3	3
+prepare st from "with cte (c1,c2) as
+(select a as col1, sum(b) as col2 from t1 where a > 1 group by col1
+union select a as col3, sum(b) as col4 from t2 where b > 2 group by col3),
+cte2 (c3, c4) as
+(select a as col5, sum(b) as col6 from t1 where a <= 1 group by col5
+union select a as col7, sum(b) as col8 from t2 where b <= 2 group by col7)
+select * from cte where c1=1 union select * from cte2 where c3=3";
+execute st;
+c1	c2
+3	3
+execute st;
+c1	c2
+3	3
+drop prepare st;
+create procedure sp() with cte (c1,c2) as
+(select a as col1, sum(b) as col2 from t1 where a > 1 group by col1
+union select a as col3, sum(b) as col4 from t2 where b > 2 group by col3),
+cte2 (c3, c4) as
+(select a as col5, sum(b) as col6 from t1 where a <= 1 group by col5
+union select a as col7, sum(b) as col8 from t2 where b <= 2 group by col7)
+select * from cte where c1=1 union select * from cte2 where c3=3;
+call sp();
+c1	c2
+3	3
+call sp();
+c1	c2
+3	3
+drop procedure sp;
+with cte (c1,c2) as (select * from t1)
+select cte.c1+1 as col1 , cte.c2 as col2 from cte where cte.c1  > 1
+union
+select cte.c1 as col3, cte.c2+1 as col4 from cte where cte.c1 < 0;
+col1	col2
+3	1
+3	2
+prepare st from "with cte (c1,c2) as (select * from t1)
+select cte.c1+1 as col1 , cte.c2 as col2 from cte where cte.c1  > 1
+union
+select cte.c1 as col3, cte.c2+1 as col4 from cte where cte.c1 < 0";
+execute st;
+col1	col2
+3	1
+3	2
+execute st;
+col1	col2
+3	1
+3	2
+save this to the end to test errors >drop prepare st;
+create procedure sp() with cte (c1,c2) as (select * from t1)
+select cte.c1+1 as col1 , cte.c2 as col2 from cte where cte.c1  > 1
+union
+select cte.c1 as col3, cte.c2+1 as col4 from cte where cte.c1 < 0;
+call sp();
+col1	col2
+3	1
+3	2
+call sp();
+col1	col2
+3	1
+3	2
+drop procedure sp;
+insert into t1 select * from t2;
+with cte (c1, c2) 
+as (select a, sum(b) from t1 where b > 1 group by a having sum(b) < 5)
+select * from cte where c1 < 4 and c2 > 1;
+c1	c2
+2	2
+# Check pushdown conditions in JSON output
+explain format=json with cte (c1, c2) 
+as (select a, sum(b) from t1 where b > 1 group by a having sum(b) < 5)
+select * from cte where c1 < 4 and c2 > 1;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "<derived2>",
+          "access_type": "ALL",
+          "rows": 10,
+          "filtered": 100,
+          "attached_condition": "cte.c1 < 4 and cte.c2 > 1",
+          "materialized": {
+            "query_block": {
+              "select_id": 2,
+              "having_condition": "sum(t1.b) < 5 and c2 > 1",
+              "filesort": {
+                "sort_key": "t1.a",
+                "temporary_table": {
+                  "nested_loop": [
+                    {
+                      "table": {
+                        "table_name": "t1",
+                        "access_type": "ALL",
+                        "rows": 10,
+                        "filtered": 100,
+                        "attached_condition": "t1.b > 1 and t1.a < 4"
+                      }
+                    }
+                  ]
+                }
+              }
+            }
+          }
+        }
+      }
+    ]
+  }
+}
+alter table t1 add column c int;
+execute st;
+ERROR HY000: WITH column list and SELECT field list have different column counts
+drop prepare st;
+drop table t1,t2;
+Test out recursive CTEs
+create table distances (src char(1), dest char(1), distance int);
+create table city_population (city char(1), population int);
+INSERT INTO `distances` VALUES ('A','A',0),('B','A',593),('C','A',800),
+('D','A',221),('E','A',707),('F','A',869),('G','A',225),('H','A',519),
+('A','B',919),('B','B',0),('C','B',440),('D','B',79),('E','B',79),
+('F','B',154),('G','B',537),('H','B',220),('A','C',491),('B','C',794),
+('C','C',0),('D','C',100),('E','C',350),('F','C',748),('G','C',712),
+('H','C',315),('A','D',440),('B','D',256),('C','D',958),('D','D',0),
+('E','D',255),('F','D',161),('G','D',63),('H','D',831),('A','E',968),
+('B','E',345),('C','E',823),('D','E',81),('E','E',0),('F','E',436),
+('G','E',373),('H','E',558),('A','F',670),('B','F',677),('C','F',375),
+('D','F',843),('E','F',90),('F','F',0),('G','F',328),('H','F',881),
+('A','G',422),('B','G',467),('C','G',67),('D','G',936),('E','G',480),
+('F','G',592),('G','G',0),('H','G',819),('A','H',537),('B','H',229),
+('C','H',534),('D','H',984),('E','H',319),('F','H',643),('G','H',257),
+('H','H',0);
+insert into city_population values ('A', 5000), ('B', 6000), ('C', 100000),
+('D', 80000), ('E', 7000), ('F', 1000), ('G', 100), ('H', -80000);
+#find the biggest city within 300 kellikams of 'E'
+with recursive travel (src, path, dest, distance, population) as (
+select city, cast('' as varchar(10)), city,
+0, population
+from city_population where city='E'
+  union all
+select src.src, concat(src.path, dst.dest), dst.dest,
+src.distance + dst.distance, dstc.population
+from travel src
+join distances dst on src.dest != dst.dest
+join city_population dstc on dst.dest = dstc.city
+where dst.src = src.dest and src.distance + dst.distance < 300
+and length(path) < 10
+)
+select * from travel where dest != 'E' order by population desc, distance
+limit 1;
+src	path	dest	distance	population
+E	FD	D	251	80000
+prepare st from "with recursive travel (src, path, dest, distance, population) as (
+select city, cast('' as varchar(10)), city,
+0, population
+from city_population where city='E'
+  union all
+select src.src, concat(src.path, dst.dest), dst.dest,
+src.distance + dst.distance, dstc.population
+from travel src
+join distances dst on src.dest != dst.dest
+join city_population dstc on dst.dest = dstc.city
+where dst.src = src.dest and src.distance + dst.distance < 300
+and length(path) < 10
+)
+select * from travel where dest != 'E' order by population desc, distance
+limit 1";
+execute st;
+src	path	dest	distance	population
+E	FD	D	251	80000
+execute st;
+src	path	dest	distance	population
+E	FD	D	251	80000
+drop prepare st;
+create procedure sp() with recursive travel (src, path, dest, distance, population) as (
+select city, cast('' as varchar(10)), city,
+0, population
+from city_population where city='E'
+  union all
+select src.src, concat(src.path, dst.dest), dst.dest,
+src.distance + dst.distance, dstc.population
+from travel src
+join distances dst on src.dest != dst.dest
+join city_population dstc on dst.dest = dstc.city
+where dst.src = src.dest and src.distance + dst.distance < 300
+and length(path) < 10
+)
+select * from travel where dest != 'E' order by population desc, distance
+limit 1;
+call sp();
+src	path	dest	distance	population
+E	FD	D	251	80000
+call sp();
+src	path	dest	distance	population
+E	FD	D	251	80000
+drop procedure sp;
+drop table distances, city_population;
+#
+# MDEV-28615: Multi-table UPDATE over derived table containing
+#             row that uses subquery with hanging CTE
+#
+CREATE TABLE t1 (a int) ENGINE=MYISAM;
+INSERT INTO t1 VALUES (3), (7), (1);
+UPDATE
+(SELECT (5, (WITH cte AS (SELECT 1) SELECT a FROM t1))) dt
+JOIN t1 t
+ON t.a=dt.a
+SET t.a = 1;
+ERROR 21000: Operand should contain 1 column(s)
+UPDATE
+(SELECT a FROM t1
+WHERE (5, (WITH cte AS (SELECT 1) SELECT a FROM t1 WHERE a > 4)) <=
+(5,a)) dt
+JOIN t1 t
+ON t.a=dt.a
+SET t.a = 1;
+SELECT * FROM t1;
+a
+3
+1
+1
+DROP TABLE t1;
+#
+# MDEV-31657: CTE with the same name as base table used twice
+#             in another CTE
+#
+create table t (a int);
+insert into t values (3), (7), (1);
+with
+t as (select * from t),
+cte as (select t1.a as t1a, t2.a as t2a from t as t1, t as t2 where t1.a=t2.a)
+select * from cte;
+t1a	t2a
+3	3
+7	7
+1	1
+create table s (a int);
+insert into s values (1), (4), (7);
+with
+t as (select * from t),
+s as (select a-1 as a from s),
+cte as (select t.a as ta, s.a as sa from t, s where t.a=s.a
+union
+select t.a+1, s.a+1 from t, s where t.a=s.a+1)
+select * from cte;
+ta	sa
+3	3
+2	1
+8	7
+with
+t as (select * from t),
+cte as (select t.a as ta, s.a as sa from t, s where t.a=s.a
+union
+select t.a+1, s.a+1 from t, s where t.a=s.a),
+s as (select a+10 as a from s)
+select * from cte;
+ta	sa
+1	1
+7	7
+2	2
+8	8
+drop table t,s;
+with
+t as (select * from t),
+cte as (select t1.a as t1a, t2.a as t2a from t as t1, t as t2 where t1.a=t2.a)
+select * from cte;
+ERROR 42S02: Table 'test.t' doesn't exist
 # End of 10.4 tests