summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorMarko Mäkelä <marko.makela@mariadb.com>2020-11-13 20:41:07 +0200
committerMarko Mäkelä <marko.makela@mariadb.com>2020-11-13 20:41:07 +0200
commit6fed6de93f120b5e311b79892e7865639e9613a4 (patch)
tree1fc6c45fdb4f590488c45a6da1a50e100155f617
parentbafb011a82c0a6437515c37e37aef433d043f592 (diff)
parent190e8a4c2aeb417b405756b193e135c542d46b34 (diff)
downloadmariadb-git-6fed6de93f120b5e311b79892e7865639e9613a4.tar.gz
MDEV-23619: Merge 10.2 into 10.3
-rw-r--r--mysql-test/main/cte_recursive.result229
-rw-r--r--mysql-test/main/cte_recursive.test50
-rw-r--r--sql/sql_lex.cc12
-rw-r--r--sql/sql_select.cc5
-rw-r--r--sql/sql_union.cc42
5 files changed, 312 insertions, 26 deletions
diff --git a/mysql-test/main/cte_recursive.result b/mysql-test/main/cte_recursive.result
index 0dfc2df7b61..3f5be4c05a7 100644
--- a/mysql-test/main/cte_recursive.result
+++ b/mysql-test/main/cte_recursive.result
@@ -4008,6 +4008,235 @@ YEAR d1 d2
DROP PROCEDURE p;
DROP TABLE t1,t2,t3,t4;
#
+# MDEV-23619: recursive CTE used only in the second operand of UNION
+#
+create table t1 (
+a bigint(10) not null auto_increment,
+b int(5) not null,
+c bigint(10) default null,
+primary key (a)
+) engine myisam;
+insert into t1 values
+(1,3,12), (2,7,15), (3,1,3), (4,3,1);
+explain with recursive r_cte as
+( select * from t1 as s
+union
+select t1.* from t1, r_cte as r where t1.c = r.a )
+select 0 as b FROM dual union all select b FROM r_cte as t;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used
+2 DERIVED s ALL NULL NULL NULL NULL 4
+3 RECURSIVE UNION t1 ALL NULL NULL NULL NULL 4 Using where
+3 RECURSIVE UNION <derived2> ref key0 key0 9 test.t1.c 2
+NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL
+4 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 4
+with recursive r_cte as
+( select * from t1 as s
+union
+select t1.* from t1, r_cte as r where t1.c = r.a )
+select 0 as b FROM dual union all select b FROM r_cte as t;
+b
+0
+3
+7
+1
+3
+analyze format=json with recursive r_cte as
+( select * from t1 as s
+union
+select t1.* from t1, r_cte as r where t1.c = r.a )
+select 0 as b FROM dual union all select b FROM r_cte as t;
+ANALYZE
+{
+ "query_block": {
+ "union_result": {
+ "table_name": "<union1,4>",
+ "access_type": "ALL",
+ "r_loops": 0,
+ "r_rows": null,
+ "query_specifications": [
+ {
+ "query_block": {
+ "select_id": 1,
+ "table": {
+ "message": "No tables used"
+ }
+ }
+ },
+ {
+ "query_block": {
+ "select_id": 4,
+ "operation": "UNION",
+ "r_loops": 1,
+ "r_total_time_ms": "REPLACED",
+ "table": {
+ "table_name": "<derived2>",
+ "access_type": "ALL",
+ "r_loops": 1,
+ "rows": 4,
+ "r_rows": 4,
+ "r_total_time_ms": "REPLACED",
+ "filtered": 100,
+ "r_filtered": 100,
+ "materialized": {
+ "query_block": {
+ "recursive_union": {
+ "table_name": "<union2,3>",
+ "access_type": "ALL",
+ "r_loops": 0,
+ "r_rows": null,
+ "query_specifications": [
+ {
+ "query_block": {
+ "select_id": 2,
+ "r_loops": 1,
+ "r_total_time_ms": "REPLACED",
+ "table": {
+ "table_name": "s",
+ "access_type": "ALL",
+ "r_loops": 1,
+ "rows": 4,
+ "r_rows": 4,
+ "r_total_time_ms": "REPLACED",
+ "filtered": 100,
+ "r_filtered": 100
+ }
+ }
+ },
+ {
+ "query_block": {
+ "select_id": 3,
+ "operation": "UNION",
+ "r_loops": 1,
+ "r_total_time_ms": "REPLACED",
+ "table": {
+ "table_name": "t1",
+ "access_type": "ALL",
+ "r_loops": 1,
+ "rows": 4,
+ "r_rows": 4,
+ "r_total_time_ms": "REPLACED",
+ "filtered": 100,
+ "r_filtered": 100,
+ "attached_condition": "t1.c is not null"
+ },
+ "table": {
+ "table_name": "<derived2>",
+ "access_type": "ref",
+ "possible_keys": ["key0"],
+ "key": "key0",
+ "key_length": "9",
+ "used_key_parts": ["a"],
+ "ref": ["test.t1.c"],
+ "r_loops": 4,
+ "rows": 2,
+ "r_rows": 0.5,
+ "r_total_time_ms": "REPLACED",
+ "filtered": 100,
+ "r_filtered": 100
+ }
+ }
+ }
+ ]
+ }
+ }
+ }
+ }
+ }
+ }
+ ]
+ }
+ }
+}
+prepare stmt from "with recursive r_cte as
+( select * from t1 as s
+union
+select t1.* from t1, r_cte as r where t1.c = r.a )
+select 0 as b FROM dual union all select b FROM r_cte as t";
+execute stmt;
+b
+0
+3
+7
+1
+3
+execute stmt;
+b
+0
+3
+7
+1
+3
+deallocate prepare stmt;
+#checking hanging cte that uses a recursive cte
+explain with h_cte as
+( with recursive r_cte as
+( select * from t1 as s
+union
+select t1.* from t1, r_cte as r where t1.c = r.a )
+select 0 as b FROM dual union all select b FROM r_cte as t)
+select * from t1 as tt;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY tt ALL NULL NULL NULL NULL 4
+with h_cte as
+( with recursive r_cte as
+( select * from t1 as s
+union
+select t1.* from t1, r_cte as r where t1.c = r.a )
+select 0 as b FROM dual union all select b FROM r_cte as t)
+select * from t1 as tt;
+a b c
+1 3 12
+2 7 15
+3 1 3
+4 3 1
+analyze format=json with h_cte as
+( with recursive r_cte as
+( select * from t1 as s
+union
+select t1.* from t1, r_cte as r where t1.c = r.a )
+select 0 as b FROM dual union all select b FROM r_cte as t)
+select * from t1 as tt;
+ANALYZE
+{
+ "query_block": {
+ "select_id": 1,
+ "r_loops": 1,
+ "r_total_time_ms": "REPLACED",
+ "table": {
+ "table_name": "tt",
+ "access_type": "ALL",
+ "r_loops": 1,
+ "rows": 4,
+ "r_rows": 4,
+ "r_total_time_ms": "REPLACED",
+ "filtered": 100,
+ "r_filtered": 100
+ }
+ }
+}
+prepare stmt from "with h_cte as
+( with recursive r_cte as
+( select * from t1 as s
+union
+select t1.* from t1, r_cte as r where t1.c = r.a )
+select 0 as b FROM dual union all select b FROM r_cte as t)
+select * from t1 as tt";
+execute stmt;
+a b c
+1 3 12
+2 7 15
+3 1 3
+4 3 1
+execute stmt;
+a b c
+1 3 12
+2 7 15
+3 1 3
+4 3 1
+deallocate prepare stmt;
+drop table t1;
+#
# End of 10.2 tests
#
#
diff --git a/mysql-test/main/cte_recursive.test b/mysql-test/main/cte_recursive.test
index 94da45b91ea..58264baac02 100644
--- a/mysql-test/main/cte_recursive.test
+++ b/mysql-test/main/cte_recursive.test
@@ -2676,6 +2676,56 @@ DROP PROCEDURE p;
DROP TABLE t1,t2,t3,t4;
--echo #
+--echo # MDEV-23619: recursive CTE used only in the second operand of UNION
+--echo #
+
+create table t1 (
+ a bigint(10) not null auto_increment,
+ b int(5) not null,
+ c bigint(10) default null,
+ primary key (a)
+) engine myisam;
+insert into t1 values
+ (1,3,12), (2,7,15), (3,1,3), (4,3,1);
+
+let $q=
+with recursive r_cte as
+( select * from t1 as s
+ union
+ select t1.* from t1, r_cte as r where t1.c = r.a )
+select 0 as b FROM dual union all select b FROM r_cte as t;
+
+eval explain $q;
+eval $q;
+--source include/analyze-format.inc
+eval analyze format=json $q;
+eval prepare stmt from "$q";
+execute stmt;
+execute stmt;
+deallocate prepare stmt;
+
+--echo #checking hanging cte that uses a recursive cte
+let $q1=
+with h_cte as
+( with recursive r_cte as
+ ( select * from t1 as s
+ union
+ select t1.* from t1, r_cte as r where t1.c = r.a )
+ select 0 as b FROM dual union all select b FROM r_cte as t)
+select * from t1 as tt;
+
+eval explain $q1;
+eval $q1;
+--source include/analyze-format.inc
+eval analyze format=json $q1;
+eval prepare stmt from "$q1";
+execute stmt;
+execute stmt;
+deallocate prepare stmt;
+
+drop table t1;
+
+--echo #
--echo # End of 10.2 tests
--echo #
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index b5483c671de..dc0662e11b8 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -4712,12 +4712,14 @@ void st_select_lex::set_explain_type(bool on_the_fly)
/*
pos_in_table_list=NULL for e.g. post-join aggregation JOIN_TABs.
*/
- if (tab->table && tab->table->pos_in_table_list &&
- tab->table->pos_in_table_list->with &&
- tab->table->pos_in_table_list->with->is_recursive)
+ if (!tab->table);
+ else if (const TABLE_LIST *pos= tab->table->pos_in_table_list)
{
- uses_cte= true;
- break;
+ if (pos->with && pos->with->is_recursive)
+ {
+ uses_cte= true;
+ break;
+ }
}
}
if (uses_cte)
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 7580dc0f2c6..d2c5470136e 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -13016,6 +13016,9 @@ void JOIN::join_free()
for (tmp_unit= select_lex->first_inner_unit();
tmp_unit;
tmp_unit= tmp_unit->next_unit())
+ {
+ if (tmp_unit->with_element && tmp_unit->with_element->is_recursive)
+ continue;
for (sl= tmp_unit->first_select(); sl; sl= sl->next_select())
{
Item_subselect *subselect= sl->master_unit()->item;
@@ -13033,7 +13036,7 @@ void JOIN::join_free()
/* Can't unlock if at least one JOIN is still needed */
can_unlock= can_unlock && full_local;
}
-
+ }
/*
We are not using tables anymore
Unlock all tables. We may be in an INSERT .... SELECT statement.
diff --git a/sql/sql_union.cc b/sql/sql_union.cc
index d15cc3fa617..e73e55bd600 100644
--- a/sql/sql_union.cc
+++ b/sql/sql_union.cc
@@ -1859,13 +1859,7 @@ bool st_select_lex_unit::cleanup()
{
DBUG_RETURN(FALSE);
}
- /*
- When processing a PS/SP or an EXPLAIN command cleanup of a unit can
- be performed immediately when the unit is reached in the cleanup
- traversal initiated by the cleanup of the main unit.
- */
- if (!thd->stmt_arena->is_stmt_prepare() && !thd->lex->describe &&
- with_element && with_element->is_recursive && union_result)
+ if (with_element && with_element->is_recursive && union_result)
{
select_union_recursive *result= with_element->rec_result;
if (++result->cleanup_count == with_element->rec_outer_references)
@@ -2045,27 +2039,31 @@ bool st_select_lex::cleanup()
if (join)
{
+ List_iterator<TABLE_LIST> ti(leaf_tables);
+ TABLE_LIST *tbl;
+ while ((tbl= ti++))
+ {
+ if (tbl->is_recursive_with_table() &&
+ !tbl->is_with_table_recursive_reference())
+ {
+ /*
+ If query is killed before open_and_process_table() for tbl
+ is called then 'with' is already set, but 'derived' is not.
+ */
+ st_select_lex_unit *unit= tbl->with->spec;
+ error|= (bool) error | (uint) unit->cleanup();
+ }
+ }
DBUG_ASSERT((st_select_lex*)join->select_lex == this);
error= join->destroy();
delete join;
join= 0;
}
- for (TABLE_LIST *tbl= get_table_list(); tbl; tbl= tbl->next_local)
- {
- if (tbl->is_recursive_with_table() &&
- !tbl->is_with_table_recursive_reference())
- {
- /*
- If query is killed before open_and_process_table() for tbl
- is called then 'with' is already set, but 'derived' is not.
- */
- st_select_lex_unit *unit= tbl->with->spec;
- error|= (bool) error | (uint) unit->cleanup();
- }
- }
for (SELECT_LEX_UNIT *lex_unit= first_inner_unit(); lex_unit ;
lex_unit= lex_unit->next_unit())
{
+ if (lex_unit->with_element && lex_unit->with_element->is_recursive)
+ continue;
error= (bool) ((uint) error | (uint) lex_unit->cleanup());
}
inner_refs_list.empty();
@@ -2085,8 +2083,12 @@ void st_select_lex::cleanup_all_joins(bool full)
join->cleanup(full);
for (unit= first_inner_unit(); unit; unit= unit->next_unit())
+ {
+ if (unit->with_element && unit->with_element->is_recursive)
+ continue;
for (sl= unit->first_select(); sl; sl= sl->next_select())
sl->cleanup_all_joins(full);
+ }
DBUG_VOID_RETURN;
}