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

CTE: Query with a namesake under a view hangs seemingly forever (falls into an endless loop)

    XMLWordPrintable

    Details

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

      Description

      drop view if exists v;
      drop table if exists t;
      create table t (i int);
      create view v as select * from t;
      with t as (select * from v) select * from t;
      

      At this point the server keeps using 100% CPU and the query does not finish.
      Below are three consequent stack traces from the running server (from 10.2 commit 9664240c948a92c22ccda0e1f5a420eb776ddcb1)

      Thread 2 (Thread 0x7f323ea71f00 (LWP 16883)):
      #0  0x000055a759e7a5f6 in st_select_lex::master_unit (this=0x7f322e66b448) at /src/10.2/sql/sql_lex.h:915
      #1  0x000055a759f517a4 in st_select_lex::get_with_clause (this=0x7f322e66b448) at /src/10.2/sql/sql_lex.h:1092
      #2  0x000055a75a080b6c in st_select_lex::find_table_def_in_with_clauses (this=0x7f321769b6c8, table=0x7f321769c5f0) at /src/10.2/sql/sql_cte.cc:513
      #3  0x000055a759e6b435 in open_and_process_table (thd=0x7f3232e2dc30, lex=0x7f3232e315b8, tables=0x7f321769c5f0, counter=0x7f323ea6f894, flags=0, prelocking_strategy=0x7f323ea6f910, has_prelocking_list=false, ot_ctx=0x7f323ea6f800) at /src/10.2/sql/sql_base.cc:3914
      #4  0x000055a759e6c991 in open_tables (thd=0x7f3232e2dc30, options=..., start=0x7f323ea6f878, counter=0x7f323ea6f894, flags=0, prelocking_strategy=0x7f323ea6f910) at /src/10.2/sql/sql_base.cc:4578
      #5  0x000055a759e6dbcd in open_and_lock_tables (thd=0x7f3232e2dc30, options=..., tables=0x7f3232e691f0, derived=true, flags=0, prelocking_strategy=0x7f323ea6f910) at /src/10.2/sql/sql_base.cc:5240
      #6  0x000055a759e61945 in open_and_lock_tables (thd=0x7f3232e2dc30, tables=0x7f3232e691f0, derived=true, flags=0) at /src/10.2/sql/sql_base.h:520
      #7  0x000055a759edb756 in execute_sqlcom_select (thd=0x7f3232e2dc30, all_tables=0x7f3232e691f0) at /src/10.2/sql/sql_parse.cc:6220
      #8  0x000055a759ed1921 in mysql_execute_command (thd=0x7f3232e2dc30) at /src/10.2/sql/sql_parse.cc:3338
      #9  0x000055a759edf288 in mysql_parse (thd=0x7f3232e2dc30, rawbuf=0x7f3232e67d08 "with t as (select * from v) select * from t", length=43, parser_state=0x7f323ea709e0, is_next_command=false) at /src/10.2/sql/sql_parse.cc:7702
      #10 0x000055a759ecd7f3 in dispatch_command (command=COM_QUERY, thd=0x7f3232e2dc30, packet=0x7f3232e61b71 "with t as (select * from v) select * from t", packet_length=43, is_com_multi=false, is_next_command=false) at /src/10.2/sql/sql_parse.cc:1790
      #11 0x000055a759ecc268 in do_command (thd=0x7f3232e2dc30) at /src/10.2/sql/sql_parse.cc:1353
      #12 0x000055a75a002046 in do_handle_one_connection (connect=0x7f323c29e310) at /src/10.2/sql/sql_connect.cc:1358
      #13 0x000055a75a001dd8 in handle_one_connection (arg=0x7f323c29e310) at /src/10.2/sql/sql_connect.cc:1264
      #14 0x000055a75a716f7a in pfs_spawn_thread (arg=0x7f32371b56f0) at /src/10.2/storage/perfschema/pfs.cc:1862
      #15 0x00007f323e6eb0a4 in start_thread () from /lib64/libpthread.so.0
      #16 0x00007f323c85904d in clone () from /lib64/libc.so.6
      

      #0  0x000055a759e7a5f6 in st_select_lex::master_unit (this=0x7f322a8cc768) at /src/10.2/sql/sql_lex.h:915
      #1  0x000055a759f517a4 in st_select_lex::get_with_clause (this=0x7f322a8cc768) at /src/10.2/sql/sql_lex.h:1092
      #2  0x000055a75a080b6c in st_select_lex::find_table_def_in_with_clauses (this=0x7f3216c1a600, table=0x7f3216c1b528) at /src/10.2/sql/sql_cte.cc:513
      #3  0x000055a759e6b435 in open_and_process_table (thd=0x7f3232e2dc30, lex=0x7f3232e315b8, tables=0x7f3216c1b528, counter=0x7f323ea6f894, flags=0, prelocking_strategy=0x7f323ea6f910, has_prelocking_list=false, ot_ctx=0x7f323ea6f800) at /src/10.2/sql/sql_base.cc:3914
      #4  0x000055a759e6c991 in open_tables (thd=0x7f3232e2dc30, options=..., start=0x7f323ea6f878, counter=0x7f323ea6f894, flags=0, prelocking_strategy=0x7f323ea6f910) at /src/10.2/sql/sql_base.cc:4578
      #5  0x000055a759e6dbcd in open_and_lock_tables (thd=0x7f3232e2dc30, options=..., tables=0x7f3232e691f0, derived=true, flags=0, prelocking_strategy=0x7f323ea6f910) at /src/10.2/sql/sql_base.cc:5240
      #6  0x000055a759e61945 in open_and_lock_tables (thd=0x7f3232e2dc30, tables=0x7f3232e691f0, derived=true, flags=0) at /src/10.2/sql/sql_base.h:520
      #7  0x000055a759edb756 in execute_sqlcom_select (thd=0x7f3232e2dc30, all_tables=0x7f3232e691f0) at /src/10.2/sql/sql_parse.cc:6220
      #8  0x000055a759ed1921 in mysql_execute_command (thd=0x7f3232e2dc30) at /src/10.2/sql/sql_parse.cc:3338
      #9  0x000055a759edf288 in mysql_parse (thd=0x7f3232e2dc30, rawbuf=0x7f3232e67d08 "with t as (select * from v) select * from t", length=43, parser_state=0x7f323ea709e0, is_next_command=false) at /src/10.2/sql/sql_parse.cc:7702
      #10 0x000055a759ecd7f3 in dispatch_command (command=COM_QUERY, thd=0x7f3232e2dc30, packet=0x7f3232e61b71 "with t as (select * from v) select * from t", packet_length=43, is_com_multi=false, is_next_command=false) at /src/10.2/sql/sql_parse.cc:1790
      #11 0x000055a759ecc268 in do_command (thd=0x7f3232e2dc30) at /src/10.2/sql/sql_parse.cc:1353
      #12 0x000055a75a002046 in do_handle_one_connection (connect=0x7f323c29e310) at /src/10.2/sql/sql_connect.cc:1358
      #13 0x000055a75a001dd8 in handle_one_connection (arg=0x7f323c29e310) at /src/10.2/sql/sql_connect.cc:1264
      #14 0x000055a75a716f7a in pfs_spawn_thread (arg=0x7f32371b56f0) at /src/10.2/storage/perfschema/pfs.cc:1862
      #15 0x00007f323e6eb0a4 in start_thread () from /lib64/libpthread.so.0
      #16 0x00007f323c85904d in clone () from /lib64/libc.so.6
      

      #0  st_select_lex::get_with_clause (this=0x7f32302ff6e0) at /src/10.2/sql/sql_lex.h:1093
      #1  0x000055a75a080b6c in st_select_lex::find_table_def_in_with_clauses (this=0x7f32166b1ff0, table=0x7f32166b2e88) at /src/10.2/sql/sql_cte.cc:513
      #2  0x000055a759e6b435 in open_and_process_table (thd=0x7f3232e2dc30, lex=0x7f3232e315b8, tables=0x7f32166b2e88, counter=0x7f323ea6f894, flags=0, prelocking_strategy=0x7f323ea6f910, has_prelocking_list=false, ot_ctx=0x7f323ea6f800) at /src/10.2/sql/sql_base.cc:3914
      #3  0x000055a759e6c991 in open_tables (thd=0x7f3232e2dc30, options=..., start=0x7f323ea6f878, counter=0x7f323ea6f894, flags=0, prelocking_strategy=0x7f323ea6f910) at /src/10.2/sql/sql_base.cc:4578
      #4  0x000055a759e6dbcd in open_and_lock_tables (thd=0x7f3232e2dc30, options=..., tables=0x7f3232e691f0, derived=true, flags=0, prelocking_strategy=0x7f323ea6f910) at /src/10.2/sql/sql_base.cc:5240
      #5  0x000055a759e61945 in open_and_lock_tables (thd=0x7f3232e2dc30, tables=0x7f3232e691f0, derived=true, flags=0) at /src/10.2/sql/sql_base.h:520
      #6  0x000055a759edb756 in execute_sqlcom_select (thd=0x7f3232e2dc30, all_tables=0x7f3232e691f0) at /src/10.2/sql/sql_parse.cc:6220
      #7  0x000055a759ed1921 in mysql_execute_command (thd=0x7f3232e2dc30) at /src/10.2/sql/sql_parse.cc:3338
      #8  0x000055a759edf288 in mysql_parse (thd=0x7f3232e2dc30, rawbuf=0x7f3232e67d08 "with t as (select * from v) select * from t", length=43, parser_state=0x7f323ea709e0, is_next_command=false) at /src/10.2/sql/sql_parse.cc:7702
      #9  0x000055a759ecd7f3 in dispatch_command (command=COM_QUERY, thd=0x7f3232e2dc30, packet=0x7f3232e61b71 "with t as (select * from v) select * from t", packet_length=43, is_com_multi=false, is_next_command=false) at /src/10.2/sql/sql_parse.cc:1790
      #10 0x000055a759ecc268 in do_command (thd=0x7f3232e2dc30) at /src/10.2/sql/sql_parse.cc:1353
      #11 0x000055a75a002046 in do_handle_one_connection (connect=0x7f323c29e310) at /src/10.2/sql/sql_connect.cc:1358
      #12 0x000055a75a001dd8 in handle_one_connection (arg=0x7f323c29e310) at /src/10.2/sql/sql_connect.cc:1264
      #13 0x000055a75a716f7a in pfs_spawn_thread (arg=0x7f32371b56f0) at /src/10.2/storage/perfschema/pfs.cc:1862
      #14 0x00007f323e6eb0a4 in start_thread () from /lib64/libpthread.so.0
      #15 0x00007f323c85904d in clone () from /lib64/libc.so.6
      

        Attachments

          Issue Links

            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: