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

Recursive CTE does not work as subquery in some cases

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.2.10, 10.2(EOL)
    • 10.2.12
    • Optimizer - CTE
    • None
    • 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

      Attachments

        Activity

          elenst Elena Stepanova added a comment -

          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

          elenst Elena Stepanova added a comment - 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

          People

            shagalla Galina Shalygina (Inactive)
            Rich Rich Theobald
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.