Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-24019

Assertion is hit for query using recursive CTE with no default DB

Details

    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)

      Attachments

        Issue Links

          Activity

            Roel Roel Van de Paar created issue -
            Roel Roel Van de Paar made changes -
            Field Original Value New Value
            Summary SIGSEGV in strnmov from name_hash_search on WITH RECURSIVE SIGSEGV in strnmov from name_hash_search and Assertion `open_tables == __null' failed on WITH RECURSIVE query

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

            Roel Roel Van de Paar added a comment - Likely unrelated to MDEV-23701 but I thought I'd mention it as there are some light similarities in the assert and stack.
            igor Igor Babaev (Inactive) made changes -
            Status Open [ 1 ] In Progress [ 3 ]

            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'
            

            igor Igor Babaev (Inactive) added a comment - 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'
            igor Igor Babaev (Inactive) made changes -
            Priority Critical [ 2 ] Major [ 3 ]

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

            igor Igor Babaev (Inactive) added a comment - The bug will be fixed by the patch for MDEV-23886 . That's why I change the priority to 'Major'.
            Roel Roel Van de Paar made changes -
            Fix Version/s 10.6 [ 24028 ]
            Roel Roel Van de Paar made changes -

            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;
            

            igor Igor Babaev (Inactive) added a comment - 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;

            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.

            igor Igor Babaev (Inactive) added a comment - 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.
            igor Igor Babaev (Inactive) made changes -
            Summary SIGSEGV in strnmov from name_hash_search and Assertion `open_tables == __null' failed on WITH RECURSIVE query Assertion failure for query using recursive CTE when no default database is set
            igor Igor Babaev (Inactive) made changes -
            Summary Assertion failure for query using recursive CTE when no default database is set Assertion is hit for query using recursive CTE with no default database
            igor Igor Babaev (Inactive) made changes -
            Summary Assertion is hit for query using recursive CTE with no default database Assertion is hit for query using recursive CTE with no default DB
            igor Igor Babaev (Inactive) made changes -
            Assignee Igor Babaev [ igor ] Oleksandr Byelkin [ sanja ]
            Status In Progress [ 3 ] In Review [ 10002 ]

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

            sanja Oleksandr Byelkin added a comment - OK to push. (now the condition looks way more comprehansive, thank you!)
            sanja Oleksandr Byelkin made changes -
            Assignee Oleksandr Byelkin [ sanja ] Igor Babaev [ igor ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            igor Igor Babaev (Inactive) added a comment - - edited

            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.

            igor Igor Babaev (Inactive) added a comment - - edited 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.
            igor Igor Babaev (Inactive) made changes -
            Fix Version/s 10.2.37 [ 25112 ]
            Fix Version/s 10.3.28 [ 25111 ]
            Fix Version/s 10.4.18 [ 25110 ]
            Fix Version/s 10.5.9 [ 25109 ]
            Fix Version/s 10.2 [ 14601 ]
            Fix Version/s 10.3 [ 22126 ]
            Fix Version/s 10.4 [ 22408 ]
            Fix Version/s 10.5 [ 23123 ]
            Resolution Fixed [ 1 ]
            Status Stalled [ 10000 ] Closed [ 6 ]
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 114914 ] MariaDB v4 [ 158500 ]

            People

              igor Igor Babaev (Inactive)
              Roel Roel Van de Paar
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.