[MDEV-14629] Recursive CTE does not work as subquery in some cases Created: 2017-12-12  Updated: 2017-12-20  Resolved: 2017-12-20

Status: Closed
Project: MariaDB Server
Component/s: Optimizer - CTE
Affects Version/s: 10.2.10, 10.2
Fix Version/s: 10.2.12

Type: Bug Priority: Major
Reporter: Rich Theobald Assignee: Galina Shalygina (Inactive)
Resolution: Fixed Votes: 0
Labels: None
Environment:

Ubuntu 14.04


 Description   

The documentation doesn't explicitly say recursive CTEs are supported in subqueries, however, I am confused as to why it works in some but not others.

Test case:

MariaDB [test]> WITH RECURSIVE foo AS ( SELECT 1 UNION SELECT 1 FROM foo ) SELECT 1;
 
+---+
 
| 1 |
 
+---+
 
| 1 |
 
+---+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> SELECT * FROM ( WITH RECURSIVE foo AS ( SELECT 1 UNION SELECT 1 FROM foo ) SELECT 1 ) bar;
 
+---+
 
| 1 |
 
+---+
 
| 1 |
 
+---+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> SET @temp = ( WITH RECURSIVE foo AS ( SELECT 1 UNION SELECT 1 FROM foo ) SELECT 1 );
 
ERROR 1146 (42S02): Table 'test.foo' doesn't exist



 Comments   
Comment by Elena Stepanova [ 2017-12-13 ]

Thanks for the report and test case.

The result in the description is for 10.2.10, as stated in the Affects Version/s field. 10.2.11 actually crashes on the same last query, both with debug and release builds:

SET @temp = ( WITH RECURSIVE foo AS ( SELECT 1 UNION SELECT 1 FROM foo ) SELECT 1 );

10.2 a3476a5de2e

 
Program terminated with signal SIGSEGV, Segmentation fault.
 
#0  0x000055bfe4397530 in st_select_lex_unit::set_unique_exclude (this=0x0) at /data/src/10.2/sql/sql_union.cc:1516
 
1516    {
 
#0  0x000055bfe4397530 in st_select_lex_unit::set_unique_exclude (this=0x0) at /data/src/10.2/sql/sql_union.cc:1516
 
#1  0x000055bfe439756f in st_select_lex_unit::set_unique_exclude (this=0x7f986c013700) at /data/src/10.2/sql/sql_union.cc:1524
 
#2  0x000055bfe439756f in st_select_lex_unit::set_unique_exclude (this=0x7f986c013700) at /data/src/10.2/sql/sql_union.cc:1524
 
#3  0x000055bfe439756f in st_select_lex_unit::set_unique_exclude (this=0x7f986c013700) at /data/src/10.2/sql/sql_union.cc:1524
 
#4  0x000055bfe439756f in st_select_lex_unit::set_unique_exclude (this=0x7f986c013700) at /data/src/10.2/sql/sql_union.cc:1524
 
...
 
#5989 0x000055bfe439756f in st_select_lex_unit::set_unique_exclude (this=0x7f986c013700) at /data/src/10.2/sql/sql_union.cc:1524
 
#5990 0x000055bfe43b7227 in TABLE_LIST::set_check_materialized (this=0x7f986c0147a0) at /data/src/10.2/sql/table.cc:5547
 
#5991 0x000055bfe424cf7a in TABLE_LIST::set_materialized_derived (this=0x7f986c0147a0) at /data/src/10.2/sql/table.h:2333
 
#5992 0x000055bfe43bcd33 in TABLE_LIST::init_derived (this=0x7f986c0147a0, thd=0x7f986c000b00, init_view=true) at /data/src/10.2/sql/table.cc:7909
 
#5993 0x000055bfe427fb9d in mysql_derived_init (thd=0x7f986c000b00, lex=0x7f986c0045d8, derived=0x7f986c0147a0) at /data/src/10.2/sql/sql_derived.cc:575
 
#5994 0x000055bfe427ee99 in mysql_handle_derived (lex=0x7f986c0045d8, phases=1) at /data/src/10.2/sql/sql_derived.cc:121
 
#5995 0x000055bfe4242834 in open_and_lock_tables (thd=0x7f986c000b00, options=..., tables=0x7f986c0147a0, derived=true, flags=0, prelocking_strategy=0x7f98884065b0) at /data/src/10.2/sql/sql_base.cc:4698
 
#5996 0x000055bfe4235c83 in open_and_lock_tables (thd=0x7f986c000b00, tables=0x7f986c0147a0, derived=true, flags=0) at /data/src/10.2/sql/sql_base.h:494
 
#5997 0x000055bfe42b3775 in mysql_execute_command (thd=0x7f986c000b00) at /data/src/10.2/sql/sql_parse.cc:4822
 
#5998 0x000055bfe42bcea2 in mysql_parse (thd=0x7f986c000b00, rawbuf=0x7f986c012630 "SET @temp = ( WITH RECURSIVE foo AS ( SELECT 1 UNION SELECT 1 FROM foo ) SELECT 1 )", length=83, parser_state=0x7f9888407200, is_com_multi=false, is_next_command=false) at /data/src/10.2/sql/sql_parse.cc:7896
 
#5999 0x000055bfe42aadd4 in dispatch_command (command=COM_QUERY, thd=0x7f986c000b00, packet=0x7f986c16b281 "SET @temp = ( WITH RECURSIVE foo AS ( SELECT 1 UNION SELECT 1 FROM foo ) SELECT 1 )", packet_length=83, is_com_multi=false, is_next_command=false) at /data/src/10.2/sql/sql_parse.cc:1805
 
#6000 0x000055bfe42a9732 in do_command (thd=0x7f986c000b00) at /data/src/10.2/sql/sql_parse.cc:1360
 
#6001 0x000055bfe43f7351 in do_handle_one_connection (connect=0x55bfe78b53b0) at /data/src/10.2/sql/sql_connect.cc:1354
 
#6002 0x000055bfe43f70de in handle_one_connection (arg=0x55bfe78b53b0) at /data/src/10.2/sql/sql_connect.cc:1260
 
#6003 0x000055bfe4815a76 in pfs_spawn_thread (arg=0x55bfe788fe80) at /data/src/10.2/storage/perfschema/pfs.cc:1863
 
#6004 0x00007f988f601494 in start_thread (arg=0x7f9888408700) at pthread_create.c:333
 
#6005 0x00007f988d9e793f in clone () from /lib/x86_64-linux-gnu/libc.so.6

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