[MDEV-14217] [db crash] Recursive CTE when SELECT includes new field Created: 2017-10-30  Updated: 2017-11-05  Resolved: 2017-11-05

Status: Closed
Project: MariaDB Server
Component/s: Optimizer - CTE
Affects Version/s: 10.3.2
Fix Version/s: 10.3.3

Type: Bug Priority: Critical
Reporter: Samuel Marks Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: None
Environment:

Mac (10.12.6; 15″; 2017);
brew install mariadb --devel;
mysql_install_db --verbose --user=`whoami` --basedir="$(brew --prefix mysql)" --datadir=/usr/local/var/mysql --tmpdir=/tmp

Attachments: File mariadb.err    

 Description   

Debugging my CTE, and accidentally crashed the server. Here's a minimal representative sample:

rec_cte.sql

 
CREATE TEMPORARY TABLE a_tbl (
 
  a VARCHAR(33) PRIMARY KEY,
 
  b VARCHAR(33)
 
);
 
 
 
INSERT INTO a_tbl VALUES
 
  ('block0', 'block0'),
 
  ('block1', NULL);
 
  
WITH RECURSIVE Q0 AS (
 
  SELECT T0.a, T0.b, 5
 
  FROM a_tbl T0
 
  WHERE b IS NULL
 
  UNION ALL
 
  SELECT T1.a, T1.b
 
  FROM Q0
 
  JOIN a_tbl T1
 
  ON T1.a=Q0.a
 
) SELECT distinct(Q0.a), Q0.b
 
  FROM Q0;

I've attached the database log.

 Comments   
Comment by Samuel Marks [ 2017-10-30 ]

FYI: With the latest MySQL—8.0.3rc—I get this error (and the DB doesn't crash):

ERROR 1137 (HY000): Can't reopen table: 'T0'

Comment by Elena Stepanova [ 2017-10-31 ]

Thanks for the report and test case. Reproducible as described, both with InnoDB and MyISAM.

10.3 ecee3c71e1e

 
#3  <signal handler called>
 
#4  0x000055772800d7e0 in fill_record (thd=0x7fd858000b00, table=0x7fd85816b878, ptr=0x7fd85816c918, values=..., ignore_errors=true, use_value=false) at /data/src/10.3/sql/sql_base.cc:8205
 
#5  0x0000557728166611 in select_unit::send_data (this=0x7fd858019510, values=...) at /data/src/10.3/sql/sql_union.cc:128
 
#6  0x0000557728166de9 in select_union_recursive::send_data (this=0x7fd858019510, values=...) at /data/src/10.3/sql/sql_union.cc:304
 
#7  0x00005577280f3d82 in end_send (join=0x7fd85801a498, join_tab=0x7fd858038e30, end_of_records=false) at /data/src/10.3/sql/sql_select.cc:20039
 
#8  0x00005577280f1700 in evaluate_join_record (join=0x7fd85801a498, join_tab=0x7fd858038a80, error=0) at /data/src/10.3/sql/sql_select.cc:19091
 
#9  0x00005577280f1017 in sub_select (join=0x7fd85801a498, join_tab=0x7fd858038a80, end_of_records=false) at /data/src/10.3/sql/sql_select.cc:18871
 
#10 0x00005577280f1700 in evaluate_join_record (join=0x7fd85801a498, join_tab=0x7fd8580386d0, error=0) at /data/src/10.3/sql/sql_select.cc:19091
 
#11 0x00005577280f1017 in sub_select (join=0x7fd85801a498, join_tab=0x7fd8580386d0, end_of_records=false) at /data/src/10.3/sql/sql_select.cc:18871
 
#12 0x00005577280f0580 in do_select (join=0x7fd85801a498, procedure=0x0) at /data/src/10.3/sql/sql_select.cc:18411
 
#13 0x00005577280c907c in JOIN::exec_inner (this=0x7fd85801a498) at /data/src/10.3/sql/sql_select.cc:3548
 
#14 0x00005577280c8514 in JOIN::exec (this=0x7fd85801a498) at /data/src/10.3/sql/sql_select.cc:3343
 
#15 0x000055772816b891 in st_select_lex_unit::exec_recursive (this=0x7fd858015028) at /data/src/10.3/sql/sql_union.cc:1667
 
#16 0x0000557728044614 in TABLE_LIST::fill_recursive (this=0x7fd8580187d0, thd=0x7fd858000b00) at /data/src/10.3/sql/sql_derived.cc:1007
 
#17 0x0000557728044917 in mysql_derived_fill (thd=0x7fd858000b00, lex=0x7fd8580046f8, derived=0x7fd8580187d0) at /data/src/10.3/sql/sql_derived.cc:1087
 
#18 0x0000557728042a1f in mysql_handle_single_derived (lex=0x7fd8580046f8, derived=0x7fd8580187d0, phases=96) at /data/src/10.3/sql/sql_derived.cc:197
 
#19 0x00005577280e05ff in st_join_table::preread_init (this=0x7fd85803ac70) at /data/src/10.3/sql/sql_select.cc:12189
 
#20 0x00005577280f0e79 in sub_select (join=0x7fd858018ed0, join_tab=0x7fd85803ac70, end_of_records=false) at /data/src/10.3/sql/sql_select.cc:18839
 
#21 0x00005577280f0580 in do_select (join=0x7fd858018ed0, procedure=0x0) at /data/src/10.3/sql/sql_select.cc:18411
 
#22 0x00005577280c907c in JOIN::exec_inner (this=0x7fd858018ed0) at /data/src/10.3/sql/sql_select.cc:3548
 
#23 0x00005577280c8514 in JOIN::exec (this=0x7fd858018ed0) at /data/src/10.3/sql/sql_select.cc:3343
 
#24 0x00005577280c96f5 in mysql_select (thd=0x7fd858000b00, tables=0x7fd8580187d0, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748609, result=0x7fd858018eb0, unit=0x7fd8580047c0, select_lex=0x7fd858004ef8) at /data/src/10.3/sql/sql_select.cc:3743
 
#25 0x00005577280bdc08 in handle_select (thd=0x7fd858000b00, lex=0x7fd8580046f8, result=0x7fd858018eb0, setup_tables_done_option=0) at /data/src/10.3/sql/sql_select.cc:378
 
#26 0x0000557728089123 in execute_sqlcom_select (thd=0x7fd858000b00, all_tables=0x7fd8580187d0) at /data/src/10.3/sql/sql_parse.cc:6467
 
#27 0x000055772807f699 in mysql_execute_command (thd=0x7fd858000b00) at /data/src/10.3/sql/sql_parse.cc:3731
 
#28 0x000055772808ca3c in mysql_parse (thd=0x7fd858000b00, rawbuf=0x7fd8580149f8 "WITH RECURSIVE Q0 AS (\nSELECT T0.a, T0.b, 5\nFROM a_tbl T0\nWHERE b IS NULL\nUNION ALL\nSELECT T1.a, T1.b\nFROM Q0\nJOIN a_tbl T1\nON T1.a=Q0.a\n) SELECT distinct(Q0.a), Q0.b\nFROM Q0", length=174, parser_state=0x7fd868048610, is_com_multi=false, is_next_command=false) at /data/src/10.3/sql/sql_parse.cc:7921
 
#29 0x000055772807a1dd in dispatch_command (command=COM_QUERY, thd=0x7fd858000b00, packet=0x7fd8580888e1 "", packet_length=174, is_com_multi=false, is_next_command=false) at /data/src/10.3/sql/sql_parse.cc:1819
 
#30 0x0000557728078c3b in do_command (thd=0x7fd858000b00) at /data/src/10.3/sql/sql_parse.cc:1370
 
#31 0x00005577281cfa14 in do_handle_one_connection (connect=0x55772a3442a0) at /data/src/10.3/sql/sql_connect.cc:1418
 
#32 0x00005577281cf7a1 in handle_one_connection (arg=0x55772a3442a0) at /data/src/10.3/sql/sql_connect.cc:1324
 
#33 0x0000557728649f76 in pfs_spawn_thread (arg=0x55772a3dd280) at /data/src/10.3/storage/perfschema/pfs.cc:1862
 
#34 0x00007fd86fcf6494 in start_thread (arg=0x7fd868049700) at pthread_create.c:333
 
#35 0x00007fd86de6e93f in clone () from /lib/x86_64-linux-gnu/libc.so.6

10.2 does not crash, instead it produces the error (as it apparently should):

1222: The used SELECT statements have a different number of columns

Comment by Igor Babaev [ 2017-11-02 ]

Alexander,
Apparently this bug is a result of your re-writing/re-engineering.
Please correct your code.

Comment by Sergei Golubchik [ 2017-11-04 ]

simple test case:

with recursive x as (select 1,2 union all select 1 from x) select * from x;

Comment by Alexander Barkov [ 2017-11-05 ]

Pushed to bb-10.2-ext

Generated at Thu Feb 08 08:11:50 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.