[MDEV-22546] Server hangs on 2nd execution of PS with query containing CTE and table of the same name Created: 2020-05-13  Updated: 2023-04-27

Status: Open
Project: MariaDB Server
Component/s: Optimizer - CTE, Prepared Statements
Affects Version/s: 10.2, 10.3, 10.4, 10.5
Fix Version/s: 10.4, 10.5

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Dmitry Shulga
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-22907 Assertion `(m_storage_engine_name.str... Open
relates to MDEV-9937 CTE: Query with a namesake under a vi... Closed

 Description   

CREATE TABLE x (a INT);
PREPARE stmt FROM "WITH x AS (SELECT * FROM x) SELECT * FROM x";
EXECUTE stmt;
EXECUTE stmt;
 
# Cleanup
DROP TABLE x;

Second EXECUTE hangs, seemingly forever. Stack trace from the running server:

10.4 db537a83

Thread 6 (Thread 0x7f4c542de700 (LWP 12824)):
#0  0x000055dfe883df23 in st_select_lex::find_table_def_in_with_clauses (this=0x7f4c1f521078, table=0x7f4c1f521638) at /data/src/10.4/sql/sql_cte.cc:1071
#1  0x000055dfe855b4d2 in open_and_process_table (thd=0x7f4c38000af0, tables=0x7f4c1f521638, counter=0x7f4c542dbbf4, flags=0, prelocking_strategy=0x7f4c542dbc78, has_prelocking_list=false, ot_ctx=0x7f4c542dbb60) at /data/src/10.4/sql/sql_base.cc:3660
#2  0x000055dfe855cf9d in open_tables (thd=0x7f4c38000af0, options=..., start=0x7f4c542dbbd8, counter=0x7f4c542dbbf4, flags=0, prelocking_strategy=0x7f4c542dbc78) at /data/src/10.4/sql/sql_base.cc:4324
#3  0x000055dfe855f0de in open_and_lock_tables (thd=0x7f4c38000af0, options=..., tables=0x7f4c381772d8, derived=true, flags=0, prelocking_strategy=0x7f4c542dbc78) at /data/src/10.4/sql/sql_base.cc:5226
#4  0x000055dfe8519033 in open_and_lock_tables (thd=0x7f4c38000af0, tables=0x7f4c381772d8, derived=true, flags=0) at /data/src/10.4/sql/sql_base.h:505
#5  0x000055dfe8605690 in execute_sqlcom_select (thd=0x7f4c38000af0, all_tables=0x7f4c381772d8) at /data/src/10.4/sql/sql_parse.cc:6280
#6  0x000055dfe85fc26b in mysql_execute_command (thd=0x7f4c38000af0) at /data/src/10.4/sql/sql_parse.cc:3898
#7  0x000055dfe862965a in Prepared_statement::execute (this=0x7f4c3803fe70, expanded_query=0x7f4c542dcb50, open_cursor=false) at /data/src/10.4/sql/sql_prepare.cc:4760
#8  0x000055dfe8627abc in Prepared_statement::execute_loop (this=0x7f4c3803fe70, expanded_query=0x7f4c542dcb50, open_cursor=false, packet=0x0, packet_end=0x0) at /data/src/10.4/sql/sql_prepare.cc:4246
#9  0x000055dfe862558f in mysql_sql_stmt_execute (thd=0x7f4c38000af0) at /data/src/10.4/sql/sql_prepare.cc:3363
#10 0x000055dfe85fc2b0 in mysql_execute_command (thd=0x7f4c38000af0) at /data/src/10.4/sql/sql_parse.cc:3914
#11 0x000055dfe8609b65 in mysql_parse (thd=0x7f4c38000af0, rawbuf=0x7f4c38013198 "execute stmt", length=12, parser_state=0x7f4c542dd570, is_com_multi=false, is_next_command=false) at /data/src/10.4/sql/sql_parse.cc:7900
#12 0x000055dfe85f6115 in dispatch_command (command=COM_QUERY, thd=0x7f4c38000af0, packet=0x7f4c38136341 "execute stmt", packet_length=12, is_com_multi=false, is_next_command=false) at /data/src/10.4/sql/sql_parse.cc:1842
#13 0x000055dfe85f48b7 in do_command (thd=0x7f4c38000af0) at /data/src/10.4/sql/sql_parse.cc:1360
#14 0x000055dfe877c426 in do_handle_one_connection (connect=0x55dfecb89db0) at /data/src/10.4/sql/sql_connect.cc:1412
#15 0x000055dfe877c175 in handle_one_connection (arg=0x55dfecb89db0) at /data/src/10.4/sql/sql_connect.cc:1316
#16 0x000055dfe917c1b1 in pfs_spawn_thread (arg=0x55dfeca9b670) at /data/src/10.4/storage/perfschema/pfs.cc:1869
#17 0x00007f4c5b8bb4a4 in start_thread (arg=0x7f4c542de700) at pthread_create.c:456
#18 0x00007f4c599efd0f in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:97

Reproducible with at least MyISAM and InnoDB, on 10.2-10.5, debug and release builds alike, including old releases.



 Comments   
Comment by Roel Van de Paar [ 2020-06-16 ]

Please fix together with MDEV-22907 if possible.

Comment by Roel Van de Paar [ 2020-06-16 ]

Confirmed hang still present on 10.5.4 4080e3acefd7e58d88c2f3539fb6a0fb359cf057

Generated at Thu Feb 08 09:15:33 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.