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

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.2
Fix Version/s: 10.2.2
Component/s: Optimizer - CTE
Labels:
None

Description

WITH RECURSIVE cte(n) AS ( SELECT n+1 FROM cte WHERE n < 5 UNION SELECT 1 UNION SELECT 1 ) SELECT * FROM cte;

CPU consumption
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
17171 elenst 20 0 781364 347176 21664 S 103.7 2.1 1:15.52 mysqld

Some consequent stack traces from the same execution
#0 st_select_lex::first_inner_unit (this=0x7f1e6f462140) at /data/src/10.2/sql/sql_lex.h:937
#1 0x00007f1e9ea6f2d9 in st_select_lex_unit::set_unique_exclude (this=0x7f1e6f460638) at /data/src/10.2/sql/sql_union.cc:1490
#2 0x00007f1e9ea8d78d in TABLE_LIST::set_check_materialized (this=0x7f1e6f461028) at /data/src/10.2/sql/table.cc:5498
#3 0x00007f1e9e93a649 in TABLE_LIST::set_materialized_derived (this=0x7f1e6f461028) at /data/src/10.2/sql/table.h:2292
#4 0x00007f1e9ea926c3 in TABLE_LIST::init_derived (this=0x7f1e6f461028, thd=0x7f1e6f416070, init_view=true) at /data/src/10.2/sql/table.cc:7723
#5 0x00007f1e9e967953 in mysql_derived_init (thd=0x7f1e6f416070, lex=0x7f1e6f419a20, derived=0x7f1e6f461028) at /data/src/10.2/sql/sql_derived.cc:571
#6 0x00007f1e9e966d15 in mysql_handle_derived (lex=0x7f1e6f419a20, phases=1) at /data/src/10.2/sql/sql_derived.cc:121
#7 0x00007f1e9e93049d in open_and_lock_tables (thd=0x7f1e6f416070, options=..., tables=0x7f1e6f4627c0, derived=true, flags=0, prelocking_strategy=0x7f1e9f8edd60) at /data/src/10.2/sql/sql_base.cc:4598
#8 0x00007f1e9e925a01 in open_and_lock_tables (thd=0x7f1e6f416070, tables=0x7f1e6f4627c0, derived=true, flags=0) at /data/src/10.2/sql/sql_base.h:497
#9 0x00007f1e9e99d789 in execute_sqlcom_select (thd=0x7f1e6f416070, all_tables=0x7f1e6f4627c0) at /data/src/10.2/sql/sql_parse.cc:6254
#10 0x00007f1e9e993a61 in mysql_execute_command (thd=0x7f1e6f416070) at /data/src/10.2/sql/sql_parse.cc:3364
#11 0x00007f1e9e9a13e5 in mysql_parse (thd=0x7f1e6f416070, rawbuf=0x7f1e6f460088 "WITH RECURSIVE cte(n) AS ( SELECT n+1 FROM cte WHERE n < 5 UNION SELECT 1 UNION SELECT 1 ) SELECT * FROM cte", length=108, parser_state=0x7f1e9f8eee20, is_com_multi=false, is_next_command=false) at /data/src/10.2/sql/sql_parse.cc:7765
#12 0x00007f1e9e98f7b8 in dispatch_command (command=COM_QUERY, thd=0x7f1e6f416070, packet=0x7f1e6f454071 "WITH RECURSIVE cte(n) AS ( SELECT n+1 FROM cte WHERE n < 5 UNION SELECT 1 UNION SELECT 1 ) SELECT * FROM cte", packet_length=108, is_com_multi=false, is_next_command=false) at /data/src/10.2/sql/sql_parse.cc:1793
#13 0x00007f1e9e98e1e9 in do_command (thd=0x7f1e6f416070) at /data/src/10.2/sql/sql_parse.cc:1353
#14 0x00007f1e9eac8926 in do_handle_one_connection (connect=0x7f1e9b85cad0) at /data/src/10.2/sql/sql_connect.cc:1354
#15 0x00007f1e9eac86b3 in handle_one_connection (arg=0x7f1e9b85cad0) at /data/src/10.2/sql/sql_connect.cc:1260
#16 0x00007f1e9e04f0a4 in start_thread (arg=0x7f1e9f8f0300) at pthread_create.c:309
#17 0x00007f1e9c20787d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:111

#0  0x00007f1e9ea6f2dd in st_select_lex_unit::set_unique_exclude (this=0x7f1e6f460638) at /data/src/10.2/sql/sql_union.cc:1490

#1  0x00007f1e9ea8d78d in TABLE_LIST::set_check_materialized (this=0x7f1e6f461028) at /data/src/10.2/sql/table.cc:5498

#2  0x00007f1e9e93a649 in TABLE_LIST::set_materialized_derived (this=0x7f1e6f461028) at /data/src/10.2/sql/table.h:2292

#3  0x00007f1e9ea926c3 in TABLE_LIST::init_derived (this=0x7f1e6f461028, thd=0x7f1e6f416070, init_view=true) at /data/src/10.2/sql/table.cc:7723

#4  0x00007f1e9e967953 in mysql_derived_init (thd=0x7f1e6f416070, lex=0x7f1e6f419a20, derived=0x7f1e6f461028) at /data/src/10.2/sql/sql_derived.cc:571

#5  0x00007f1e9e966d15 in mysql_handle_derived (lex=0x7f1e6f419a20, phases=1) at /data/src/10.2/sql/sql_derived.cc:121

#6  0x00007f1e9e93049d in open_and_lock_tables (thd=0x7f1e6f416070, options=..., tables=0x7f1e6f4627c0, derived=true, flags=0, prelocking_strategy=0x7f1e9f8edd60) at /data/src/10.2/sql/sql_base.cc:4598

#7  0x00007f1e9e925a01 in open_and_lock_tables (thd=0x7f1e6f416070, tables=0x7f1e6f4627c0, derived=true, flags=0) at /data/src/10.2/sql/sql_base.h:497

#8  0x00007f1e9e99d789 in execute_sqlcom_select (thd=0x7f1e6f416070, all_tables=0x7f1e6f4627c0) at /data/src/10.2/sql/sql_parse.cc:6254

#9  0x00007f1e9e993a61 in mysql_execute_command (thd=0x7f1e6f416070) at /data/src/10.2/sql/sql_parse.cc:3364

#10 0x00007f1e9e9a13e5 in mysql_parse (thd=0x7f1e6f416070, rawbuf=0x7f1e6f460088 "WITH RECURSIVE cte(n) AS ( SELECT n+1 FROM cte WHERE n < 5 UNION SELECT 1 UNION SELECT 1 ) SELECT * FROM cte", length=108, parser_state=0x7f1e9f8eee20, is_com_multi=false, is_next_command=false) at /data/src/10.2/sql/sql_parse.cc:7765

#11 0x00007f1e9e98f7b8 in dispatch_command (command=COM_QUERY, thd=0x7f1e6f416070, packet=0x7f1e6f454071 "WITH RECURSIVE cte(n) AS ( SELECT n+1 FROM cte WHERE n < 5 UNION SELECT 1 UNION SELECT 1 ) SELECT * FROM cte", packet_length=108, is_com_multi=false, is_next_command=false) at /data/src/10.2/sql/sql_parse.cc:1793

#12 0x00007f1e9e98e1e9 in do_command (thd=0x7f1e6f416070) at /data/src/10.2/sql/sql_parse.cc:1353

#13 0x00007f1e9eac8926 in do_handle_one_connection (connect=0x7f1e9b85cad0) at /data/src/10.2/sql/sql_connect.cc:1354

#14 0x00007f1e9eac86b3 in handle_one_connection (arg=0x7f1e9b85cad0) at /data/src/10.2/sql/sql_connect.cc:1260

#15 0x00007f1e9e04f0a4 in start_thread (arg=0x7f1e9f8f0300) at pthread_create.c:309

#16 0x00007f1e9c20787d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:111

#0  st_select_lex::first_inner_unit (this=0x7f1e6f460290) at /data/src/10.2/sql/sql_lex.h:936

#1  0x00007f1e9ea6f2d9 in st_select_lex_unit::set_unique_exclude (this=0x7f1e6f460638) at /data/src/10.2/sql/sql_union.cc:1490

#2  0x00007f1e9ea8d78d in TABLE_LIST::set_check_materialized (this=0x7f1e6f461028) at /data/src/10.2/sql/table.cc:5498

#3  0x00007f1e9e93a649 in TABLE_LIST::set_materialized_derived (this=0x7f1e6f461028) at /data/src/10.2/sql/table.h:2292

#4  0x00007f1e9ea926c3 in TABLE_LIST::init_derived (this=0x7f1e6f461028, thd=0x7f1e6f416070, init_view=true) at /data/src/10.2/sql/table.cc:7723

#5  0x00007f1e9e967953 in mysql_derived_init (thd=0x7f1e6f416070, lex=0x7f1e6f419a20, derived=0x7f1e6f461028) at /data/src/10.2/sql/sql_derived.cc:571

#6  0x00007f1e9e966d15 in mysql_handle_derived (lex=0x7f1e6f419a20, phases=1) at /data/src/10.2/sql/sql_derived.cc:121

#7  0x00007f1e9e93049d in open_and_lock_tables (thd=0x7f1e6f416070, options=..., tables=0x7f1e6f4627c0, derived=true, flags=0, prelocking_strategy=0x7f1e9f8edd60) at /data/src/10.2/sql/sql_base.cc:4598

#8  0x00007f1e9e925a01 in open_and_lock_tables (thd=0x7f1e6f416070, tables=0x7f1e6f4627c0, derived=true, flags=0) at /data/src/10.2/sql/sql_base.h:497

#9  0x00007f1e9e99d789 in execute_sqlcom_select (thd=0x7f1e6f416070, all_tables=0x7f1e6f4627c0) at /data/src/10.2/sql/sql_parse.cc:6254

#10 0x00007f1e9e993a61 in mysql_execute_command (thd=0x7f1e6f416070) at /data/src/10.2/sql/sql_parse.cc:3364

#11 0x00007f1e9e9a13e5 in mysql_parse (thd=0x7f1e6f416070, rawbuf=0x7f1e6f460088 "WITH RECURSIVE cte(n) AS ( SELECT n+1 FROM cte WHERE n < 5 UNION SELECT 1 UNION SELECT 1 ) SELECT * FROM cte", length=108, parser_state=0x7f1e9f8eee20, is_com_multi=false, is_next_command=false) at /data/src/10.2/sql/sql_parse.cc:7765

#12 0x00007f1e9e98f7b8 in dispatch_command (command=COM_QUERY, thd=0x7f1e6f416070, packet=0x7f1e6f454071 "WITH RECURSIVE cte(n) AS ( SELECT n+1 FROM cte WHERE n < 5 UNION SELECT 1 UNION SELECT 1 ) SELECT * FROM cte", packet_length=108, is_com_multi=false, is_next_command=false) at /data/src/10.2/sql/sql_parse.cc:1793

#13 0x00007f1e9e98e1e9 in do_command (thd=0x7f1e6f416070) at /data/src/10.2/sql/sql_parse.cc:1353

#14 0x00007f1e9eac8926 in do_handle_one_connection (connect=0x7f1e9b85cad0) at /data/src/10.2/sql/sql_connect.cc:1354

#15 0x00007f1e9eac86b3 in handle_one_connection (arg=0x7f1e9b85cad0) at /data/src/10.2/sql/sql_connect.cc:1260

#16 0x00007f1e9e04f0a4 in start_thread (arg=0x7f1e9f8f0300) at pthread_create.c:309

#17 0x00007f1e9c20787d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:111

Notes:
The same query but with one UNION SELECT 1 works.
In PostgreSQL such queries (where the recursive part goes first) are not allowed.

Attachments

Issue Links

relates to

MDEV-29361 Infinite recursive calls when detecting CTE dependencies

Closed

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

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration