[MDEV-10372] [bb-10.2-mdev9864 tree] EXPLAIN with recursive CTE enters endless recursion Created: 2016-07-13  Updated: 2016-08-09  Resolved: 2016-08-09

Status: Closed
Project: MariaDB Server
Component/s: Optimizer - CTE
Affects Version/s: None
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Sergei Petrunia Assignee: Sergei Petrunia
Resolution: Fixed Votes: 0
Labels: CTE

Issue Links:
Relates
relates to MDEV-9864 Implement recursive common table expr... Closed

 Description   

This can be observed on bb-10.2-mdev9864 tree.
EXPLAIN enters into endless recursion on a recursive CTE query:

create table ten(a int);
 
insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
 
 
 
explain format=json 
with recursive T as (select a from ten union select a+10 from T where a < 1000) 
select  * from T;


  
  Program received signal SIGSEGV, Segmentation fault.
 
  [Switching to Thread 0x7ffff427ff00 (LWP 27802)]
 
  0x0000555556367600 in dtoa (dd=100, mode=4, ndigits=6, decpt=0x7ffff4238224, sign=0x7ffff4238228, rve=0x0, buf=0x0, buf_size=3680) at /home/psergey/dev-git/10.2-mdev9864-cte/strings/dtoa.c:2171
 
(gdb) wher
 
  #0  0x0000555556367600 in dtoa (dd=100, mode=4, ndigits=6, decpt=0x7ffff4238224, sign=0x7ffff4238228, rve=0x0, buf=0x0, buf_size=3680) at /home/psergey/dev-git/10.2-mdev9864-cte/strings/dtoa.c:2171
 
  #1  0x0000555556364056 in my_gcvt (x=100, type=MY_GCVT_ARG_DOUBLE, width=6, to=0x7ffff4239380 "10", error=0x0) at /home/psergey/dev-git/10.2-mdev9864-cte/strings/dtoa.c:224
 
  #2  0x0000555556369b62 in process_dbl_arg (to=0x7ffff4239380 "10", end=0x7ffff42393bf "", width=6, par=100, arg_type=103 'g') at /home/psergey/dev-git/10.2-mdev9864-cte/strings/my_vsnprintf.c:250
 
  #3  0x000055555636b729 in my_vsnprintf_ex (cs=0x555556c56f80, to=0x7ffff4239380 "10", n=64, fmt=0x5555564252e1 "g", ap=0x7ffff4239290) at /home/psergey/dev-git/10.2-mdev9864-cte/strings/my_vsnprintf.c:640
 
  #4  0x000055555636bc6b in my_vsnprintf (to=0x7ffff4239380 "10", n=64, fmt=0x5555564252df "%lg", ap=0x7ffff4239290) at /home/psergey/dev-git/10.2-mdev9864-cte/strings/my_vsnprintf.c:706
 
  #5  0x000055555636bd17 in my_snprintf (to=0x7ffff4239380 "10", n=64, fmt=0x5555564252df "%lg") at /home/psergey/dev-git/10.2-mdev9864-cte/strings/my_vsnprintf.c:715
 
  #6  0x0000555555c1b6c4 in Json_writer::add_double (this=0x7ffff427d810, val=100) at /home/psergey/dev-git/10.2-mdev9864-cte/sql/my_json_writer.cc:157
 
  #7  0x0000555555bf2775 in Explain_table_access::print_explain_json (this=0x7fff5802be00, query=0x7fff58017d98, writer=0x7ffff427d810, is_analyze=false) at /home/psergey/dev-git/10.2-mdev9864-cte/sql/sql_explain.cc:1620
 
  #8  0x0000555555bf0870 in Explain_basic_join::print_explain_json_interns (this=0x7fff5802bd40, query=0x7fff58017d98, writer=0x7ffff427d810, is_analyze=false) at /home/psergey/dev-git/10.2-mdev9864-cte/sql/sql_explain.cc:995
 
  #9  0x0000555555bf0404 in Explain_select::print_explain_json (this=0x7fff5802bd40, query=0x7fff58017d98, writer=0x7ffff427d810, is_analyze=false) at /home/psergey/dev-git/10.2-mdev9864-cte/sql/sql_explain.cc:900
 
  #10 0x0000555555bef58d in Explain_union::print_explain_json (this=0x7fff580300d8, query=0x7fff58017d98, writer=0x7ffff427d810, is_analyze=false) at /home/psergey/dev-git/10.2-mdev9864-cte/sql/sql_explain.cc:580
 
  #11 0x0000555555bf2b85 in Explain_table_access::print_explain_json (this=0x7fff5802e000, query=0x7fff58017d98, writer=0x7ffff427d810, is_analyze=false) at /home/psergey/dev-git/10.2-mdev9864-cte/sql/sql_explain.cc:1686
 
  #12 0x0000555555bf0870 in Explain_basic_join::print_explain_json_interns (this=0x7fff5802df00, query=0x7fff58017d98, writer=0x7ffff427d810, is_analyze=false) at /home/psergey/dev-git/10.2-mdev9864-cte/sql/sql_explain.cc:995
 
  #13 0x0000555555bf0404 in Explain_select::print_explain_json (this=0x7fff5802df00, query=0x7fff58017d98, writer=0x7ffff427d810, is_analyze=false) at /home/psergey/dev-git/10.2-mdev9864-cte/sql/sql_explain.cc:900
 
  #14 0x0000555555bef58d in Explain_union::print_explain_json (this=0x7fff580300d8, query=0x7fff58017d98, writer=0x7ffff427d810, is_analyze=false) at /home/psergey/dev-git/10.2-mdev9864-cte/sql/sql_explain.cc:580
 
  #15 0x0000555555bf2b85 in Explain_table_access::print_explain_json (this=0x7fff5802e000, query=0x7fff58017d98, writer=0x7ffff427d810, is_analyze=false) at /home/psergey/dev-git/10.2-mdev9864-cte/sql/sql_explain.cc:1686
 
  #16 0x0000555555bf0870 in Explain_basic_join::print_explain_json_interns (this=0x7fff5802df00, query=0x7fff58017d98, writer=0x7ffff427d810, is_analyze=false) at /home/psergey/dev-git/10.2-mdev9864-cte/sql/sql_explain.cc:995
 
  #17 0x0000555555bf0404 in Explain_select::print_explain_json (this=0x7fff5802df00, query=0x7fff58017d98, writer=0x7ffff427d810, is_analyze=false) at /home/psergey/dev-git/10.2-mdev9864-cte/sql/sql_explain.cc:900
 
  #18 0x0000555555bef58d in Explain_union::print_explain_json (this=0x7fff580300d8, query=0x7fff58017d98, writer=0x7ffff427d810, is_analyze=false) at /home/psergey/dev-git/10.2-mdev9864-cte/sql/sql_explain.cc:580
 
  #19 0x0000555555bf2b85 in Explain_table_access::print_explain_json (this=0x7fff5802e000, query=0x7fff58017d98, writer=0x7ffff427d810, is_analyze=false) at /home/psergey/dev-git/10.2-mdev9864-cte/sql/sql_explain.cc:1686
 
  #20 0x0000555555bf0870 in Explain_basic_join::print_explain_json_interns (this=0x7fff5802df00, query=0x7fff58017d98, writer=0x7ffff427d810, is_analyze=false) at /home/psergey/dev-git/10.2-mdev9864-cte/sql/sql_explain.cc:995
 
  #21 0x0000555555bf0404 in Explain_select::print_explain_json (this=0x7fff5802df00, query=0x7fff58017d98, writer=0x7ffff427d810, is_analyze=false) at /home/psergey/dev-git/10.2-mdev9864-cte/sql/sql_explain.cc:900
 
  #22 0x0000555555bef58d in Explain_union::print_explain_json (this=0x7fff580300d8, query=0x7fff58017d98, writer=0x7ffff427d810, is_analyze=false) at /home/psergey/dev-git/10.2-mdev9864-cte/sql/sql_explain.cc:580
 
...
 
  #1324 0x0000555555bf0870 in Explain_basic_join::print_explain_json_interns (this=0x7fff5802fb48, query=0x7fff58017d98, writer=0x7ffff427d810, is_analyze=false) at /home/psergey/dev-git/10.2-mdev9864-cte/sql/sql_explain.cc:995
 
  #1325 0x0000555555bf0404 in Explain_select::print_explain_json (this=0x7fff5802fb48, query=0x7fff58017d98, writer=0x7ffff427d810, is_analyze=false) at /home/psergey/dev-git/10.2-mdev9864-cte/sql/sql_explain.cc:900
 
  #1326 0x0000555555bee03c in Explain_query::print_explain_json (this=0x7fff58017d98, output=0x7fff58014f68, is_analyze=false) at /home/psergey/dev-git/10.2-mdev9864-cte/sql/sql_explain.cc:224
 
  #1327 0x0000555555a8e684 in execute_sqlcom_select (thd=0x7fff58000b00, all_tables=0x7fff58014880) at /home/psergey/dev-git/10.2-mdev9864-cte/sql/sql_parse.cc:6269
 
  #1328 0x0000555555a844a0 in mysql_execute_command (thd=0x7fff58000b00) at /home/psergey/dev-git/10.2-mdev9864-cte/sql/sql_parse.cc:3356
 
  #1329 0x0000555555a920b8 in mysql_parse (thd=0x7fff58000b00, rawbuf=0x7fff58011ee8 "explain format=json with recursive T as (select a from ten union select a+10 from T where a < 1000) select  * from T", length=116, parser_state=0x7ffff427e8b0, is_com_multi=false, is_next_command=false) at /home/psergey/dev-git/10.2-mdev9864-cte/sql/sql_parse.cc:7730



 Comments   
Comment by Sergei Petrunia [ 2016-07-29 ]

It's only EXPLAIN JSON that fails. Tabular EXPLAIN produces this:

+------+-------------------+------------+------+---------------+------+---------+------+------+-------------+
 
| id   | select_type       | table      | type | possible_keys | key  | key_len | ref  | rows | Extra       |
 
+------+-------------------+------------+------+---------------+------+---------+------+------+-------------+
 
|    1 | PRIMARY           | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |   10 |             |
 
|    2 | SUBQUERY          | ten        | ALL  | NULL          | NULL | NULL    | NULL |   10 |             |
 
|    3 | UNCACHEABLE UNION | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |   10 | Using where |
 
| NULL | UNION RESULT      | <union2,3> | ALL  | NULL          | NULL | NULL    | NULL | NULL |             |
 
+------+-------------------+------------+------+---------------+------+---------+------+------+-------------+

Comment by Sergei Petrunia [ 2016-08-09 ]

Fix pushed into the tree.

Generated at Thu Feb 08 07:41:44 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.