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

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

    XMLWordPrintable

    Details

      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

          Activity

            People

            Assignee:
            igor Igor Babaev
            Reporter:
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Git Integration