WITH x AS ( WITH x AS ( SELECT ( x % ( WITH x AS ( SELECT x FROM ( SELECT x FROM x WHERE x = CASEWHEN x * ( SELECT 1 FROM x AS x WHERE x BETWEEN 1.000000 AND 1 WINDOW x AS ( PARTITION BY x ORDERBY ( SELECT x FROM x x HAVING ( TRUEIN ( CASE x WHEN x THEN'x'ELSETRUEEND != ( ( ( x ORNOT x ) BETWEEN 1 AND 1 ) ) ) ) ) DESC RANGE BETWEEN 1.000000 FOLLOWING AND 1.000000 FOLLOWING ) ) ^ x THEN'x'ELSE x END / 1 GROUPBY x ) AS x ) SELECT EXISTS ( WITH RECURSIVE x ( x ) AS ( SELECT 1 UNIONSELECT 1 - x FROM x LIMIT 1 ) SELECTDISTINCT ( ( NOT ( 1.000000 AND x = 1 ) ) = 1 AND x = 1 ) % 1 , ( x = 1 OR x > FALSE ) WHERE x = 1 AND ( x = 1 OR x = 1 OR x = 1 ) ) , 'x'FROM x WINDOW x AS ( PARTITION BY x ORDERBY x DESC ) ) <= x ) , 1 FROM x ) SELECT x FROM ( SELECT x FROM x GROUPBY x ) AS x ) SELECT x FROM x WHERE x BETWEENFALSEAND ( ( ( x ORNOT x ) BETWEEN ( ( ( NOT ( ( 1.000000 ^ 1.000000 AND ( ( TRUE , x ) NOTIN ( SELECT ( NOT ( x = CASE'x' = 'x'WHEN'x'THEN'x'WHEN 1 THEN'x'ELSE 1 END / 1 ) ) , 1 FROM x ) OR x > 'x' ) = 1 ) * NULL ) ) ) ) AND 1.000000 ) ) ;
MDEV-30248Infinite sequence of recursive calls when processing embedded CTE
Closed
is duplicated by
MDEV-28504SIGSEGV in With_element::get_name and UBSAN: runtime error: member call on null pointer of type 'struct With_element' in With_clause::find_table_def + 2 other UBSAN runtime errors.
Closed
MDEV-29358Server crashed with stack-overflow in st_select_lex_unit::set_unique_exclude()
Closed
relates to
MDEV-10737Server falls into endless loop in st_select_lex_unit::set_unique_exclude on recursive CTE with two UNIONs
Closed
MDEV-26095Infinite recursion when processing embedded recursive CTE with missing RECURSIVE
Closed
MDEV-29358Server crashed with stack-overflow in st_select_lex_unit::set_unique_exclude()
Note that in this query x as r2 is supposed to be resolved against the first CTE with name x.
A crash with the same stack is caused by the query
with
cte as
(
with
x as
(
select a from t1 unionselect a+1 from x as r1 where a < 7
)
select * from x as s1
where s1.a in (
with
x as
(
select a from t2
union
select a+2 from x as r2 where a < 10
)
select a from x as s2
)
)
select * from cte;
Here neither x as r1 nor x as r2 could be resolved because both CTEs with name x belong to non-RECURSIVE with clauses.
Igor Babaev (Inactive)
added a comment - - edited The following query
with
cte as
(
with recursive
x as
(
select a from t1 union select a+1 from x as r1 where a < 7
)
select * from x as s1
where s1.a in (
with
x as
(
select a from t2
union
select a+2 from x as r2 where a < 10
)
select a from x as s2
)
)
select * from cte;
causes a crash with the following stack:
sql/sql_cte.h:225(With_element::get_name())[0x555555eae990]
sql/sql_cte.h:226(With_element::get_name_str())[0x555555eae9b4]
sql/sql_cte.cc:437(With_clause::find_table_def(TABLE_LIST*, With_element*))[0x555555eac7af]
sql/sql_cte.cc:480(find_table_def_in_with_clauses(TABLE_LIST*, st_unit_ctxt_elem*))[0x555555eac88d]
sql/sql_cte.cc:542(With_element::check_dependencies_in_select(st_select_lex*, st_unit_ctxt_elem*, bool, unsigned long long*))[0x555555eaca0a]
sql/sql_cte.cc:651(With_element::check_dependencies_in_unit(st_select_lex_unit*, st_unit_ctxt_elem*, bool, unsigned long long*))[0x555555eacd44]
sql/sql_cte.cc:685(With_element::check_dependencies_in_with_clause(With_clause*, st_unit_ctxt_elem*, bool, unsigned long long*))[0x555555eacdaf]
sql/sql_cte.cc:648(With_element::check_dependencies_in_unit(st_select_lex_unit*, st_unit_ctxt_elem*, bool, unsigned long long*))[0x555555eacce1]
sql/sql_cte.cc:565(With_element::check_dependencies_in_select(st_select_lex*, st_unit_ctxt_elem*, bool, unsigned long long*))[0x555555eacb2d]
sql/sql_cte.cc:406(With_element::check_dependencies_in_spec())[0x555555eac71e]
sql/sql_cte.cc:334(With_clause::check_dependencies())[0x555555eac563]
sql/sql_cte.cc:84(LEX::check_dependencies_in_with_clauses())[0x555555eabead]
sql/sql_cte.cc:276(LEX::check_cte_dependencies_and_resolve_references())[0x555555eac40c]
sql/sql_yacc.yy:9257(MYSQLparse(THD*))[0x555555ef7e9d]
sql/sql_parse.cc:10204(parse_sql(THD*, Parser_state*, Object_creation_ctx*, bool))[0x555555c97a7e]
sql/sql_parse.cc:7823(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x555555c9209c]
sql/sql_parse.cc:1855(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x555555c7ec4a]
sql/sql_parse.cc:1399(do_command(THD*))[0x555555c7d53c]
sql/sql_connect.cc:1403(do_handle_one_connection(CONNECT*))[0x555555dfdd54]
sql/sql_connect.cc:1309(handle_one_connection)[0x555555dfdac3]
Note that in this query x as r2 is supposed to be resolved against the first CTE with name x.
A crash with the same stack is caused by the query
with
cte as
(
with
x as
(
select a from t1 union select a+1 from x as r1 where a < 7
)
select * from x as s1
where s1.a in (
with
x as
(
select a from t2
union
select a+2 from x as r2 where a < 10
)
select a from x as s2
)
)
select * from cte;
Here neither x as r1 nor x as r2 could be resolved because both CTEs with name x belong to non-RECURSIVE with clauses.
The following two queries where the first embedded CTE has the name different from the name of the second CTE cause crashes with the same stack as the queries in the previous comment.
with
cte as
(
with recursive
y as
(
select a from t1 unionselect a+1 from y as r1 where a < 7
)
select * from y as s1
where s1.a in (
with
x as
(
select a from t2
union
select a+2 from x as r2 where a < 10
)
select a from x as s2
)
)
select * from cte;
with
cte as
(
with
y(a) as
(
select a+5 from t1
)
select * from y as s1
where s1.a in (
with
x as
(
select a from t2
union
select a+2 from x as r2 where a < 10
)
select a from x as s2
)
)
select * from cte;
Note that in both queries CTE with name x is defined in with clauses without RECURSIVE specifier. So x as r2 cannot be resolved as a reference to CTE and the message
ERROR 42S02: Table 'test.x' doesn't exist
is expected.
If we add this specifier to the with clauses containing the CTE with name x then this CTE is taken as a recursive CTE. In this case the server returns expected result sets.
Igor Babaev (Inactive)
added a comment - - edited The following two queries where the first embedded CTE has the name different from the name of the second CTE cause crashes with the same stack as the queries in the previous comment.
with
cte as
(
with recursive
y as
(
select a from t1 union select a+1 from y as r1 where a < 7
)
select * from y as s1
where s1.a in (
with
x as
(
select a from t2
union
select a+2 from x as r2 where a < 10
)
select a from x as s2
)
)
select * from cte;
with
cte as
(
with
y(a) as
(
select a+5 from t1
)
select * from y as s1
where s1.a in (
with
x as
(
select a from t2
union
select a+2 from x as r2 where a < 10
)
select a from x as s2
)
)
select * from cte;
Note that in both queries CTE with name x is defined in with clauses without RECURSIVE specifier. So x as r2 cannot be resolved as a reference to CTE and the message
ERROR 42S02: Table 'test.x' doesn't exist
is expected.
If we add this specifier to the with clauses containing the CTE with name x then this CTE is taken as a recursive CTE. In this case the server returns expected result sets.
After the fix the reported query returns a syntax error. The query suggested by alice returns the following error:
MariaDB [test]> WITH x AS (
->
-> WITH x AS (
->
-> SELECT ( WITH x AS ( SELECT ( SELECT 1 FROM x ) FROM x ) SELECT EXISTS ( WITH RECURSIVE x AS ( SELECT 1 FROM x) SELECT 1 ))
->
-> FROM x)
->
-> SELECT 1 FROM x)
->
-> SELECT ( SELECT x from x ) FROM x;
ERROR 4005 (HY000): No anchors for recursive WITH element 'x'
that makes a perfect sense.
Igor Babaev (Inactive)
added a comment - After the fix the reported query returns a syntax error. The query suggested by alice returns the following error:
MariaDB [test]> WITH x AS (
->
-> WITH x AS (
->
-> SELECT ( WITH x AS ( SELECT ( SELECT 1 FROM x ) FROM x ) SELECT EXISTS ( WITH RECURSIVE x AS ( SELECT 1 FROM x) SELECT 1 ))
->
-> FROM x)
->
-> SELECT 1 FROM x)
->
-> SELECT ( SELECT x from x ) FROM x;
ERROR 4005 (HY000): No anchors for recursive WITH element 'x'
that makes a perfect sense.
Bug
Critical
The following query
with(()()))causes a crash with the following stack:
sql/sql_cte.h:225(With_element::get_name())[0x555555eae990]sql/sql_cte.h:226(With_element::get_name_str())[0x555555eae9b4]sql/sql_cte.cc:437(With_clause::find_table_def(TABLE_LIST*, With_element*))[0x555555eac7af]sql/sql_cte.cc:480(find_table_def_in_with_clauses(TABLE_LIST*, st_unit_ctxt_elem*))[0x555555eac88d]sql/sql_cte.cc:542(With_element::check_dependencies_in_select(st_select_lex*, st_unit_ctxt_elem*, bool, unsigned long long*))[0x555555eaca0a]sql/sql_cte.cc:651(With_element::check_dependencies_in_unit(st_select_lex_unit*, st_unit_ctxt_elem*, bool, unsigned long long*))[0x555555eacd44]sql/sql_cte.cc:685(With_element::check_dependencies_in_with_clause(With_clause*, st_unit_ctxt_elem*, bool, unsigned long long*))[0x555555eacdaf]sql/sql_cte.cc:648(With_element::check_dependencies_in_unit(st_select_lex_unit*, st_unit_ctxt_elem*, bool, unsigned long long*))[0x555555eacce1]sql/sql_cte.cc:565(With_element::check_dependencies_in_select(st_select_lex*, st_unit_ctxt_elem*, bool, unsigned long long*))[0x555555eacb2d]sql/sql_cte.cc:406(With_element::check_dependencies_in_spec())[0x555555eac71e]sql/sql_cte.cc:334(With_clause::check_dependencies())[0x555555eac563]sql/sql_cte.cc:84(LEX::check_dependencies_in_with_clauses())[0x555555eabead]sql/sql_cte.cc:276(LEX::check_cte_dependencies_and_resolve_references())[0x555555eac40c]sql/sql_yacc.yy:9257(MYSQLparse(THD*))[0x555555ef7e9d]sql/sql_parse.cc:10204(parse_sql(THD*, Parser_state*, Object_creation_ctx*, bool))[0x555555c97a7e]sql/sql_parse.cc:7823(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x555555c9209c]sql/sql_parse.cc:1855(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x555555c7ec4a]sql/sql_parse.cc:1399(do_command(THD*))[0x555555c7d53c]sql/sql_connect.cc:1403(do_handle_one_connection(CONNECT*))[0x555555dfdd54]sql/sql_connect.cc:1309(handle_one_connection)[0x555555dfdac3]Note that in this query x as r2 is supposed to be resolved against the first CTE with name x.
A crash with the same stack is caused by the query
with(()()))Here neither x as r1 nor x as r2 could be resolved because both CTEs with name x belong to non-RECURSIVE with clauses.