[MDEV-29361] Infinite recursive calls when detecting CTE dependencies - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 10.10.0, 10.3(EOL), 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL)
Fix Version/s: 10.3.37, 10.4.27, 10.5.18, 10.6.11, 10.7.7, 10.8.6, 10.9.4, 10.10.2
Component/s: Optimizer - CTE
Labels:
None

Description

output:

SUMMARY: AddressSanitizer: stack-overflow (/usr/lib/x86_64-linux-gnu/libasan.so.4+0x51575)

poc:

CREATE TABLE x ( x BIGINT ) ;

 INSERT INTO x ( x ) VALUES ( 1 ) ;

 UPDATE x SET x = 1 WHERE x = 1 ;

 INSERT INTO x ( x ) VALUES ( 1.000000 ) , ( 1 ) ;

 WITH x AS ( WITH x AS ( SELECT ( x % ( WITH x AS ( SELECT x FROM ( SELECT x FROM x WHERE x = CASE WHEN x * ( SELECT 1 FROM x AS x WHERE x BETWEEN 1.000000 AND 1 WINDOW x AS ( PARTITION BY x ORDER BY ( SELECT x FROM x x HAVING ( TRUE IN ( CASE x WHEN x THEN 'x' ELSE TRUE END != ( ( ( x OR NOT x ) BETWEEN 1 AND 1 ) ) ) ) ) DESC RANGE BETWEEN 1.000000 FOLLOWING AND 1.000000 FOLLOWING ) ) ^ x THEN 'x' ELSE x END / 1 GROUP BY x ) AS x ) SELECT EXISTS ( WITH RECURSIVE x ( x ) AS ( SELECT 1 UNION SELECT 1 - x FROM x LIMIT 1 ) SELECT DISTINCT ( ( 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 ORDER BY x DESC ) ) <= x ) , 1 FROM x ) SELECT x FROM ( SELECT x FROM x GROUP BY x ) AS x ) SELECT x FROM x WHERE x BETWEEN FALSE AND ( ( ( x OR NOT x ) BETWEEN ( ( ( NOT ( ( 1.000000 ^ 1.000000 AND ( ( TRUE , x ) NOT IN ( 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 ) ) ;

Attachments

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

313_stack
38 kB
2022-08-23 12:51

Issue Links

causes

MDEV-30248 Infinite sequence of recursive calls when processing embedded CTE

Closed

is duplicated by

MDEV-28504 SIGSEGV 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-29358 Server crashed with stack-overflow in st_select_lex_unit::set_unique_exclude()

Closed

relates to

MDEV-10737 Server falls into endless loop in st_select_lex_unit::set_unique_exclude on recursive CTE with two UNIONs

Closed

MDEV-26095 Infinite recursion when processing embedded recursive CTE with missing RECURSIVE

Closed

MDEV-29358 Server crashed with stack-overflow in st_select_lex_unit::set_unique_exclude()

Closed

(1 relates to)

Activity

Ascending order - Click to sort in descending order

View 6 older comments

Igor Babaev (Inactive) added a comment - 2022-09-02 21:29 - 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.

Igor Babaev (Inactive) added a comment - 2022-09-02 21:29 - 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.

Igor Babaev (Inactive) added a comment - 2022-09-03 00:36 - 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.

Igor Babaev (Inactive) added a comment - 2022-09-03 00:36 - 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.

Oleksandr Byelkin added a comment - 2022-09-28 11:38

OK to push.

Oleksandr Byelkin added a comment - 2022-09-28 11:38 OK to push.

Igor Babaev (Inactive) added a comment - 2022-09-29 17:07

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 - 2022-09-29 17:07 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 - 2022-09-29 18:52

A fix for the bug was pushed into 10.3. it has to be merged upstream as it is.

Igor Babaev (Inactive) added a comment - 2022-09-29 18:52 A fix for the bug was pushed into 10.3. it has to be merged upstream as it is.

MariaDB Server

Infinite recursive calls when detecting CTE dependencies

Details

Description

Attachments

Attachments

Issue Links

Activity

People

Dates

Git Integration