[MDEV-24019] Assertion is hit for query using recursive CTE with no default DB Created: 2020-10-24  Updated: 2020-12-09  Resolved: 2020-12-09

Status: Closed
Project: MariaDB Server
Component/s: Optimizer - CTE
Affects Version/s: 10.2, 10.3, 10.4, 10.5, 10.6
Fix Version/s: 10.2.37, 10.3.28, 10.4.18, 10.5.9

Type: Bug Priority: Major
Reporter: Roel Van de Paar Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: not-10.1, security

Issue Links:
Relates
relates to MDEV-23886 Reusing CTE inside a function fails w... Closed

 Description    

USE test;
 
DROP DATABASE test;
 
WITH RECURSIVE a AS (SELECT 1 FROM DUAL UNION ALL SELECT * FROM (SELECT * FROM a) AS b) SELECT * FROM a;

Leads to:

10.6.0 5d4599f9750140f92cfdbbe4d292ae1b8dd456f8 (Optimized)

 
Core was generated by `/test/MD201020-mariadb-10.6.0-linux-x86_64-opt/bin/mysqld --no-defaults --core-'.
 
Program terminated with signal SIGSEGV, Segmentation fault.
 
#0  __pthread_kill (threadid=<optimized out>, signo=signo@entry=11)
 
    at ../sysdeps/unix/sysv/linux/pthread_kill.c:56
 
[Current thread is 1 (Thread 0x14b0f04d1700 (LWP 2159588))]
 
(gdb) bt
 
#0  __pthread_kill (threadid=<optimized out>, signo=signo@entry=11) at ../sysdeps/unix/sysv/linux/pthread_kill.c:56
 
#1  0x000055cdebb6ea4f in my_write_core (sig=sig@entry=11) at /test/10.6_opt/mysys/stacktrace.c:424
 
#2  0x000055cdeb592130 in handle_fatal_signal (sig=11) at /test/10.6_opt/sql/signal_handler.cc:330
 
#3  <signal handler called>
 
#4  strnmov (dst=0x14b0f04cc7a5 "\024", src=src@entry=0x0, n=<optimized out>) at /test/10.6_opt/strings/strnmov.c:41
 
#5  0x000055cdeb301e0f in name_hash_search (host=0x55cdebcbc7aa "localhost", ip=0x0, db=db@entry=0x0, user=user@entry=0x14b0a0002a70 "root", tname=tname@entry=0x55cdebcd47a2 "(temporary)", name_tolower=false, exact=false, name_hash=0x55cdec4c3740 <column_priv_hash>) at /test/10.6_opt/sql/sql_acl.cc:5428
 
#6  0x000055cdeb302cb4 in table_hash_search (exact=false, tname=0x55cdebcd47a2 "(temporary)", user=0x14b0a0002a70 "root", db=0x0, ip=<optimized out>, host=<optimized out>) at /test/10.6_opt/sql/sql_acl.cc:8446
 
#7  check_grant_all_columns (thd=thd@entry=0x14b0a0000c58, want_access_arg=want_access_arg@entry=SELECT_ACL, fields=fields@entry=0x14b0f04ccc40) at /test/10.6_opt/sql/sql_acl.cc:8447
 
#8  0x000055cdeb3213a7 in insert_fields (thd=0x14b0a0000c58, context=<optimized out>, db_name=<optimized out>, table_name=0x0, it=0x14b0f04cd030, any_privileges=false, hidden_bit_fields=0x14b0a0011b60) at /test/10.6_opt/sql/sql_base.cc:8036
 
#9  0x000055cdeb3215c0 in setup_wild (thd=0x14b0a0000c58, tables=<optimized out>, fields=@0x14b0a00119a0: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x14b0a0011e00, last = 0x14b0a0011e00, elements = 1}, <No data fields>}, sum_func_list=sum_func_list@entry=0x14b0a001fe00, select_lex=0x14b0a0011850) at /test/10.6_opt/sql/sql_base.cc:7487
 
#10 0x000055cdeb3db715 in JOIN::prepare (this=this@entry=0x14b0a001fad8, tables_init=<optimized out>, conds_init=<optimized out>, og_num=<optimized out>, order_init=<optimized out>, skip_order_by=<optimized out>, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x14b0a0011850, unit_arg=0x14b0a0012538) at /test/10.6_opt/sql/sql_select.cc:1245
 
#11 0x000055cdeb43b74b in st_select_lex_unit::prepare_join (this=0x14b0a0012538, thd_arg=0x14b0a0000c58, sl=0x14b0a0011850, tmp_result=0x14b0a0015fa0, additional_options=<optimized out>, is_union_select=<optimized out>) at /test/10.6_opt/sql/sql_union.cc:1093
 
#12 0x000055cdeb43eb6d in st_select_lex_unit::prepare (this=this@entry=0x14b0a0012538, derived_arg=derived_arg@entry=0x14b0a0012d78, sel_result=0x14b0a0015fa0, additional_options=additional_options@entry=0) at /test/10.6_opt/sql/sql_union.cc:1486
 
#13 0x000055cdeb341b29 in mysql_derived_prepare (thd=0x14b0a0000c58, lex=0x14b0a0004b58, derived=0x14b0a0012d78) at /test/10.6_opt/sql/sql_derived.cc:816
 
#14 0x000055cdeb340b34 in mysql_handle_single_derived (lex=lex@entry=0x14b0a0004b58, derived=derived@entry=0x14b0a0012d78, phases=phases@entry=2) at /test/10.6_opt/sql/sql_derived.cc:206
 
#15 0x000055cdeb45dcee in TABLE_LIST::handle_derived (this=this@entry=0x14b0a0012d78, lex=lex@entry=0x14b0a0004b58, phases=phases@entry=2) at /test/10.6_opt/sql/table.cc:9095
 
#16 0x000055cdeb35d857 in LEX::handle_list_of_derived (phases=2, table_list=<optimized out>, this=0x14b0a0004b58) at /test/10.6_opt/sql/table.h:2705
 
#17 st_select_lex::handle_derived (this=<optimized out>, lex=0x14b0a0004b58, phases=phases@entry=2) at /test/10.6_opt/sql/sql_lex.cc:4872
 
#18 0x000055cdeb3db4cd in JOIN::prepare (this=this@entry=0x14b0a001f560, tables_init=<optimized out>, conds_init=<optimized out>, og_num=0, order_init=<optimized out>, skip_order_by=<optimized out>, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x14b0a0011290, unit_arg=0x14b0a0010a90) at /test/10.6_opt/sql/sql_select.cc:1154
 
#19 0x000055cdeb43b74b in st_select_lex_unit::prepare_join (this=0x14b0a0010a90, thd_arg=0x14b0a0000c58, sl=0x14b0a0011290, tmp_result=0x14b0a0015608, additional_options=<optimized out>, is_union_select=<optimized out>) at /test/10.6_opt/sql/sql_union.cc:1093
 
#20 0x000055cdeb43ebf2 in st_select_lex_unit::prepare (this=this@entry=0x14b0a0010a90, derived_arg=derived_arg@entry=0x14b0a0011e48, sel_result=<optimized out>, additional_options=additional_options@entry=0) at /test/10.6_opt/sql/sql_union.cc:1566
 
#21 0x000055cdeb341b29 in mysql_derived_prepare (thd=0x14b0a0000c58, lex=0x14b0a0004b58, derived=0x14b0a0011e48) at /test/10.6_opt/sql/sql_derived.cc:816
 
#22 0x000055cdeb340b34 in mysql_handle_single_derived (lex=lex@entry=0x14b0a0004b58, derived=derived@entry=0x14b0a0011e48, phases=phases@entry=2) at /test/10.6_opt/sql/sql_derived.cc:206
 
#23 0x000055cdeb45dcee in TABLE_LIST::handle_derived (this=this@entry=0x14b0a0011e48, lex=lex@entry=0x14b0a0004b58, phases=phases@entry=2) at /test/10.6_opt/sql/table.cc:9095
 
#24 0x000055cdeb35d857 in LEX::handle_list_of_derived (phases=2, table_list=<optimized out>, this=0x14b0a0004b58) at /test/10.6_opt/sql/table.h:2705
 
#25 st_select_lex::handle_derived (this=this@entry=0x14b0a0011850, lex=lex@entry=0x14b0a0004b58, phases=phases@entry=2) at /test/10.6_opt/sql/sql_lex.cc:4872
 
#26 0x000055cdeb45dcc6 in TABLE_LIST::handle_derived (this=this@entry=0x14b0a0012d78, lex=lex@entry=0x14b0a0004b58, phases=phases@entry=2) at /test/10.6_opt/sql/table.cc:9092
 
#27 0x000055cdeb35d857 in LEX::handle_list_of_derived (phases=2, table_list=<optimized out>, this=0x14b0a0004b58) at /test/10.6_opt/sql/table.h:2705
 
#28 st_select_lex::handle_derived (this=this@entry=0x14b0a0011290, lex=lex@entry=0x14b0a0004b58, phases=phases@entry=2) at /test/10.6_opt/sql/sql_lex.cc:4872
 
#29 0x000055cdeb45dcc6 in TABLE_LIST::handle_derived (this=this@entry=0x14b0a0013fd0, lex=lex@entry=0x14b0a0004b58, phases=phases@entry=2) at /test/10.6_opt/sql/table.cc:9092
 
#30 0x000055cdeb35d857 in LEX::handle_list_of_derived (phases=2, table_list=<optimized out>, this=0x14b0a0004b58) at /test/10.6_opt/sql/table.h:2705
 
#31 st_select_lex::handle_derived (this=<optimized out>, lex=0x14b0a0004b58, phases=phases@entry=2) at /test/10.6_opt/sql/sql_lex.cc:4872
 
#32 0x000055cdeb3db4cd in JOIN::prepare (this=0x14b0a0014fb0, tables_init=<optimized out>, conds_init=<optimized out>, og_num=0, order_init=<optimized out>, skip_order_by=<optimized out>, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x14b0a00139d8, unit_arg=0x14b0a0004c20) at /test/10.6_opt/sql/sql_select.cc:1154
 
#33 0x000055cdeb3ee9c9 in mysql_select (thd=0x14b0a0000c58, tables=0x14b0a0013fd0, fields=@0x14b0a0013b28: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x14b0a0013f88, last = 0x14b0a0013f88, elements = 1}, <No data fields>}, conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x14b0a0014f88, unit=0x14b0a0004c20, select_lex=0x14b0a00139d8) at /test/10.6_opt/sql/sql_select.cc:4637
 
#34 0x000055cdeb3eed67 in handle_select (thd=thd@entry=0x14b0a0000c58, lex=lex@entry=0x14b0a0004b58, result=result@entry=0x14b0a0014f88, setup_tables_done_option=setup_tables_done_option@entry=0) at /test/10.6_opt/sql/sql_select.cc:417
 
#35 0x000055cdeb37e951 in execute_sqlcom_select (thd=0x14b0a0000c58, all_tables=0x14b0a0013fd0) at /test/10.6_opt/sql/sql_parse.cc:6062
 
#36 0x000055cdeb38c380 in mysql_execute_command (thd=0x14b0a0000c58) at /test/10.6_opt/sql/sql_parse.cc:3784
 
#37 0x000055cdeb37903f in mysql_parse (thd=0x14b0a0000c58, rawbuf=<optimized out>, length=<optimized out>, parser_state=<optimized out>) at /test/10.6_opt/sql/sql_parse.cc:7833
 
#38 0x000055cdeb384967 in dispatch_command (command=COM_QUERY, thd=0x14b0a0000c58, packet=0x14b0a0008009 "WITH RECURSIVE a AS (SELECT 1 FROM DUAL UNION ALL SELECT * FROM (SELECT * FROM a) AS b) SELECT * FROM a", packet_length=<optimized out>) at /test/10.6_opt/sql/sql_class.h:1253
 
#39 0x000055cdeb386d42 in do_command (thd=0x14b0a0000c58) at /test/10.6_opt/sql/sql_parse.cc:1343
 
#40 0x000055cdeb48a6e1 in do_handle_one_connection (connect=<optimized out>, connect@entry=0x55cdedbf7798, put_in_cache=put_in_cache@entry=true) at /test/10.6_opt/sql/sql_connect.cc:1410
 
#41 0x000055cdeb48ab5d in handle_one_connection (arg=arg@entry=0x55cdedbf7798) at /test/10.6_opt/sql/sql_connect.cc:1312
 
#42 0x000055cdeb810266 in pfs_spawn_thread (arg=0x55cdedb729c8) at /test/10.6_opt/storage/perfschema/pfs.cc:2201
 
#43 0x000014b0f3567609 in start_thread (arg=<optimized out>) at pthread_create.c:477
 
#44 0x000014b0f3156293 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

10.6.0 5d4599f9750140f92cfdbbe4d292ae1b8dd456f8 (Debug)

 
mysqld: /test/10.6_dbg/sql/sql_class.cc:1558: void THD::cleanup(): Assertion `open_tables == __null' failed.

10.6.0 5d4599f9750140f92cfdbbe4d292ae1b8dd456f8 (Debug)

 
Core was generated by `/test/MD211020-mariadb-10.6.0-linux-x86_64-dbg/bin/mysqld --no-defaults --core-'.
 
Program terminated with signal SIGABRT, Aborted.
 
#0  __pthread_kill (threadid=<optimized out>, signo=signo@entry=6)
 
    at ../sysdeps/unix/sysv/linux/pthread_kill.c:56
 
[Current thread is 1 (Thread 0x14dbca267800 (LWP 2165546))]
 
(gdb) bt
 
#0  __pthread_kill (threadid=<optimized out>, signo=signo@entry=6) at ../sysdeps/unix/sysv/linux/pthread_kill.c:56
 
#1  0x000055ce6abc6021 in my_write_core (sig=sig@entry=6) at /test/10.6_dbg/mysys/stacktrace.c:424
 
#2  0x000055ce6a30d321 in handle_fatal_signal (sig=6) at /test/10.6_dbg/sql/signal_handler.cc:330
 
#3  <signal handler called>
 
#4  __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
 
#5  0x000014dbca423859 in __GI_abort () at abort.c:79
 
#6  0x000014dbca423729 in __assert_fail_base (fmt=0x14dbca5b9588 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=0x55ce6ad249b9 "open_tables == __null", file=0x55ce6ad22410 "/test/10.6_dbg/sql/sql_class.cc", line=1558, function=<optimized out>) at assert.c:92
 
#7  0x000014dbca434f36 in __GI___assert_fail (assertion=assertion@entry=0x55ce6ad249b9 "open_tables == __null", file=file@entry=0x55ce6ad22410 "/test/10.6_dbg/sql/sql_class.cc", line=line@entry=1558, function=function@entry=0x55ce6ad24786 "void THD::cleanup()") at assert.c:101
 
#8  0x000055ce69fd5cc2 in THD::cleanup (this=this@entry=0x55ce6d56ccc8) at /test/10.6_dbg/sql/sql_class.cc:1558
 
#9  0x000055ce69fd7242 in THD::free_connection (this=this@entry=0x55ce6d56ccc8) at /test/10.6_dbg/sql/sql_class.cc:1625
 
#10 0x000055ce69fe0c57 in THD::~THD (this=0x55ce6d56ccc8, __in_chrg=<optimized out>) at /test/10.6_dbg/sql/sql_class.cc:1703
 
#11 0x000055ce69fe0f49 in THD::~THD (this=0x55ce6d56ccc8, __in_chrg=<optimized out>) at /test/10.6_dbg/sql/sql_class.cc:1674
 
#12 0x000055ce69f8da2d in grant_init () at /test/10.6_dbg/sql/sql_acl.cc:7741
 
#13 0x000055ce69f36fb4 in mysqld_main (argc=<optimized out>, argv=<optimized out>) at /test/10.6_dbg/sql/mysqld.cc:5571
 
#14 0x000055ce69f28b46 in main (argc=<optimized out>, argv=<optimized out>) at /test/10.6_dbg/sql/main.cc:25

Bug confirmed present in:
MariaDB: 10.2.35 (dbg), 10.2.35 (opt), 10.3.26 (dbg), 10.3.26 (opt), 10.4.16 (dbg), 10.4.16 (opt), 10.5.7 (dbg), 10.5.7 (opt), 10.6.0 (dbg), 10.6.0 (opt)

Bug confirmed not present in:
MariaDB: 10.1.48 (dbg), 10.1.48 (opt)
MySQL: 5.5.62 (dbg), 5.5.62 (opt), 5.6.50 (dbg), 5.6.50 (opt), 5.7.32 (dbg), 5.7.32 (opt), 8.0.22 (dbg), 8.0.22 (opt)

 Comments   
Comment by Roel Van de Paar [ 2020-10-24 ]

Likely unrelated to MDEV-23701 but I thought I'd mention it as there are some light similarities in the assert and stack.

Comment by Igor Babaev [ 2020-10-29 ]

The following query without infinite recursion

WITH RECURSIVE a AS 
  (SELECT 1 FROM DUAL UNION SELECT * FROM (SELECT * FROM a) AS b) 
SELECT * FROM a;

also hits the assertion:

bool check_grant_all_columns(THD*, privilege_t, Field_iterator_table_ref*):
 
Assertion `grant_table || grant_table_role'

Comment by Igor Babaev [ 2020-10-29 ]

The bug will be fixed by the patch for MDEV-23886. That's why I change the priority to 'Major'.

Comment by Igor Babaev [ 2020-12-07 ]

The following query already fails in the same way:

WITH RECURSIVE a AS 
   (SELECT 1 FROM DUAL UNION SELECT * FROM a as r)
 
 SELECT * FROM a;

Comment by Igor Babaev [ 2020-12-07 ]

The failure happens when trying to unfold '*' for SELECT * FROM a as r in the function insert_fields() from sql_base.cc in the mode when no default database is set. Here the function check_grant_all_columns() is called for the table reference r. r is table reference to a recursive cte. No acl checking is needed for any CTEs. Now the condition that allows such checking looks like this:

     if (!((table && tables->is_non_derived() &&
 
           (table->grant.privilege & SELECT_ACL)) ||
 
           ((!tables->is_non_derived() && 
             (tables->grant.privilege & SELECT_ACL)))) &&
 
         !any_privileges)

We have for table reference r:
table != NULL
tables->is_non_derived() == false
tables->grant.privilege == 0
For any recursive reference to a CTE the field TABLE_LIST::table becomes not NULL after the anchor part of the CTE definition has been processed at the prepare stage.

In the case when a default database is set we have all privileges set in tables->grant.privilege.
This inconsistency should be fixed in future development.

For now the solution would be:
Explicitly prohibit acl checking in insert_fields() for any table references that are references to derived tables or to CTEs.

Comment by Oleksandr Byelkin [ 2020-12-08 ]

OK to push. (now the condition looks way more comprehansive, thank you!)

Comment by Igor Babaev [ 2020-12-08 ]

Roel,
"Explicitly prohibit acl checking in insert_fields() for any table references that are references to derived tables or to CTEs"
CTEs/Derived tables are transparent for acl checks. There are no ways to set privileges for such objects. This differs them from views.

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