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

different results when using CTE and big_tables=1

Details

    Description

      MariaDB [test]> set big_tables=0;
      		 
      Query OK, 0 rows affected (0.00 sec)
      		 
       
      		 
      MariaDB [test]> with recursive qn as (select 123 as a union all select 1+a from qn where a<130) select * from qn;
      		 
      +-----+
      		 
      | a   |
      		 
      +-----+
      		 
      | 123 |
      		 
      | 124 |
      		 
      | 125 |
      		 
      | 126 |
      		 
      | 127 |
      		 
      | 128 |
      		 
      | 129 |
      		 
      | 130 |
      		 
      +-----+
      		 
      8 rows in set (0.00 sec)
      		 
       
      		 
      MariaDB [test]> set big_tables=1;
      		 
      Query OK, 0 rows affected (0.00 sec)
      		 
       
      		 
      MariaDB [test]> with recursive qn as (select 123 as a union all select 1+a from qn where a<130) select * from qn;
      		 
      +-----+
      		 
      | a   |
      		 
      +-----+
      		 
      | 123 |
      		 
      | 124 |
      		 
      +-----+
      		 
      2 rows in set (0.00 sec)

      Attachments

        Issue Links

          relates to

          Task - A task that needs to be done. MDEV-15447 Import CTE tests from MySQL 8

          • Major - Major loss of function.
          • Stalled

          Activity

            Ascending order - Click to sort in descending order
            igor Igor Babaev (Inactive) added a comment -

            This bug also can be reproduced with the following test case:

            create table  folks(id int, name char(32), dob date, father int, mother int);
            		 
             
            		 
            insert into folks values
            		 
            (100, 'Me', '2000-01-01', 20, 30),
            		 
            (20, 'Dad', '1970-02-02', 10, 9),
            		 
            (30, 'Mom', '1975-03-03', 8, 7),
            		 
            (10, 'Grandpa Bill', '1940-04-05', null, null),
            		 
            (9, 'Grandma Ann', '1941-10-15', null, null),
            		 
            (25, 'Uncle Jim', '1968-11-18', 8, 7),
            		 
            (98, 'Sister Amy', '2001-06-20', 20, 30),
            		 
            (7, 'Grandma Sally', '1943-08-23', null, 6),
            		 
            (8, 'Grandpa Ben', '1940-10-21', null, null),
            		 
            (6, 'Grandgrandma Martha', '1923-05-17', null, null),
            		 
            (67, 'Cousin Eddie', '1992-02-28', 25, 27),
            		 
            (27, 'Auntie Melinda', '1971-03-29', null, null); 
             
            		 
            set big_tables=0;
            		 
            with recursive 
            ancestors
            		 
            as
            		 
            (
            		 
              select *
            		 
                from folks
            		 
                  where name = 'Me' and dob = '2000-01-01'
            		 
              union 
              select p.id, p.name, p.dob, p.father, p.mother
            		 
                from folks as p, ancestors AS a
            		 
                  where p.id = a.father or p.id = a.mother
            		 
            )
            		 
            select * from ancestors;
            		 
             
            		 
            set big_tables=1;
            		 
            with recursive 
            ancestors
            		 
            as
            		 
            (
            		 
              select *
            		 
                from folks
            		 
                  where name = 'Me' and dob = '2000-01-01'
            		 
              union 
              select p.id, p.name, p.dob, p.father, p.mother
            		 
                from folks as p, ancestors AS a
            		 
                  where p.id = a.father or p.id = a.mother
            		 
            )
            		 
            select * from ancestors;
            		 
             
            		 
            MariaDB [test]> set big_tables=0;
            		 
            Query OK, 0 rows affected (0.00 sec)
            		 
             
            		 
            MariaDB [test]> with recursive 
                -> ancestors
            		 
                -> as
            		 
                -> (
            		 
                ->   select *
            		 
                ->     from folks
            		 
                ->       where name = 'Me' and dob = '2000-01-01'
            		 
                ->   union 
                ->   select p.id, p.name, p.dob, p.father, p.mother
            		 
                ->     from folks as p, ancestors AS a
            		 
                ->       where p.id = a.father or p.id = a.mother
            		 
                -> )
            		 
                -> select * from ancestors;
            		 
            +------+---------------------+------------+--------+--------+
            		 
            | id   | name                | dob        | father | mother |
            		 
            +------+---------------------+------------+--------+--------+
            		 
            |  100 | Me                  | 2000-01-01 |     20 |     30 |
            		 
            |   20 | Dad                 | 1970-02-02 |     10 |      9 |
            		 
            |   30 | Mom                 | 1975-03-03 |      8 |      7 |
            		 
            |   10 | Grandpa Bill        | 1940-04-05 |   NULL |   NULL |
            		 
            |    9 | Grandma Ann         | 1941-10-15 |   NULL |   NULL |
            		 
            |    7 | Grandma Sally       | 1943-08-23 |   NULL |      6 |
            		 
            |    8 | Grandpa Ben         | 1940-10-21 |   NULL |   NULL |
            		 
            |    6 | Grandgrandma Martha | 1923-05-17 |   NULL |   NULL |
            		 
            +------+---------------------+------------+--------+--------+
            		 
            8 rows in set (0.00 sec)
            		 
            MariaDB [test]> set big_tables=1;
            		 
            Query OK, 0 rows affected (0.00 sec)
            		 
             
            		 
            MariaDB [test]> with recursive 
                -> ancestors
            		 
                -> as
            		 
                -> (
            		 
                ->   select *
            		 
                ->     from folks
            		 
                ->       where name = 'Me' and dob = '2000-01-01'
            		 
                ->   union 
                ->   select p.id, p.name, p.dob, p.father, p.mother
            		 
                ->     from folks as p, ancestors AS a
            		 
                ->        where p.id = a.father or p.id = a.mother
            		 
                -> )
            		 
                -> select * from ancestors;
            		 
            +------+------+------------+--------+--------+
            		 
            | id   | name | dob        | father | mother |
            		 
            +------+------+------------+--------+--------+
            		 
            |  100 | Me   | 2000-01-01 |     20 |     30 |
            		 
            |   20 | Dad  | 1970-02-02 |     10 |      9 |
            		 
            |   30 | Mom  | 1975-03-03 |      8 |      7 |
            		 
            +------+------+------------+--------+--------+
            		 
            3 rows in set (0.00 sec)

            igor Igor Babaev (Inactive) added a comment - This bug also can be reproduced with the following test case: create table folks(id int, name char(32), dob date, father int, mother int);   insert into folks values (100, 'Me', '2000-01-01', 20, 30), (20, 'Dad', '1970-02-02', 10, 9), (30, 'Mom', '1975-03-03', 8, 7), (10, 'Grandpa Bill', '1940-04-05', null, null), (9, 'Grandma Ann', '1941-10-15', null, null), (25, 'Uncle Jim', '1968-11-18', 8, 7), (98, 'Sister Amy', '2001-06-20', 20, 30), (7, 'Grandma Sally', '1943-08-23', null, 6), (8, 'Grandpa Ben', '1940-10-21', null, null), (6, 'Grandgrandma Martha', '1923-05-17', null, null), (67, 'Cousin Eddie', '1992-02-28', 25, 27), (27, 'Auntie Melinda', '1971-03-29', null, null);   set big_tables=0; with recursive ancestors as ( select * from folks where name = 'Me' and dob = '2000-01-01' union select p.id, p.name, p.dob, p.father, p.mother from folks as p, ancestors AS a where p.id = a.father or p.id = a.mother ) select * from ancestors;   set big_tables=1; with recursive ancestors as ( select * from folks where name = 'Me' and dob = '2000-01-01' union select p.id, p.name, p.dob, p.father, p.mother from folks as p, ancestors AS a where p.id = a.father or p.id = a.mother ) select * from ancestors;   MariaDB [test]> set big_tables=0; Query OK, 0 rows affected (0.00 sec)   MariaDB [test]> with recursive -> ancestors -> as -> ( -> select * -> from folks -> where name = 'Me' and dob = '2000-01-01' -> union -> select p.id, p.name, p.dob, p.father, p.mother -> from folks as p, ancestors AS a -> where p.id = a.father or p.id = a.mother -> ) -> select * from ancestors; +------+---------------------+------------+--------+--------+ | id | name | dob | father | mother | +------+---------------------+------------+--------+--------+ | 100 | Me | 2000-01-01 | 20 | 30 | | 20 | Dad | 1970-02-02 | 10 | 9 | | 30 | Mom | 1975-03-03 | 8 | 7 | | 10 | Grandpa Bill | 1940-04-05 | NULL | NULL | | 9 | Grandma Ann | 1941-10-15 | NULL | NULL | | 7 | Grandma Sally | 1943-08-23 | NULL | 6 | | 8 | Grandpa Ben | 1940-10-21 | NULL | NULL | | 6 | Grandgrandma Martha | 1923-05-17 | NULL | NULL | +------+---------------------+------------+--------+--------+ 8 rows in set (0.00 sec) MariaDB [test]> set big_tables=1; Query OK, 0 rows affected (0.00 sec)   MariaDB [test]> with recursive -> ancestors -> as -> ( -> select * -> from folks -> where name = 'Me' and dob = '2000-01-01' -> union -> select p.id, p.name, p.dob, p.father, p.mother -> from folks as p, ancestors AS a -> where p.id = a.father or p.id = a.mother -> ) -> select * from ancestors; +------+------+------------+--------+--------+ | id | name | dob | father | mother | +------+------+------------+--------+--------+ | 100 | Me | 2000-01-01 | 20 | 30 | | 20 | Dad | 1970-02-02 | 10 | 9 | | 30 | Mom | 1975-03-03 | 8 | 7 | +------+------+------------+--------+--------+ 3 rows in set (0.00 sec)
            igor Igor Babaev (Inactive) added a comment -

            A fix for this bug was pushed into 10.2.
            It should be merged upstream as it is.

            igor Igor Babaev (Inactive) added a comment - A fix for this bug was pushed into 10.2. It should be merged upstream as it is.
            marko Marko Mäkelä added a comment -

            igor, the test main.cte_recursive hangs in 10.3 after the merge. There were only two minor conflicts:

            1. in the parameter of create_tmp_table(), "" was replaced with empty_clex_str in 10.3
            2. in 10.3, some other tests existed at the end of the test main.cte_recursive, and there was no "end of 10.2 tests" or "start of 10.3 tests" marker

            Please re-enable the test and fix it.

            marko Marko Mäkelä added a comment - igor , the test main.cte_recursive hangs in 10.3 after the merge. There were only two minor conflicts: in the parameter of create_tmp_table() , "" was replaced with empty_clex_str in 10.3 in 10.3, some other tests existed at the end of the test main.cte_recursive, and there was no "end of 10.2 tests" or "start of 10.3 tests" marker Please re-enable the test and fix it.
            igor Igor Babaev (Inactive) added a comment -

            A fix for this bug was pushed into 10.2

            igor Igor Babaev (Inactive) added a comment - A fix for this bug was pushed into 10.2
            marko Marko Mäkelä added a comment -

            igor, the test in 10.3 is still disabled, and it still hangs, like I wrote 2 days before your comment. Please check out 10.3. fix the hang and re-enable the test.

            ./mtr --mem --enable-disabled main.cte_recursive &
            		 
            sleep 60
            		 
            echo 'thread apply all backtrace'|gdb -p $(pgrep -x mysqld)

            I am not sure if it is exactly a hang. It could just be a computation gone wild:

            10.3 de942c9f618b590a01a7960c171d7e50e435708f

            		 
            #2  0x0000000001330509 in maria_delete_all_rows (info=0x7f7fbc0e4850)
            		 
                at /mariadb/10.3/storage/maria/ma_delete_all.c:145
            		 
            #3  0x00000000012a2268 in ha_maria::delete_all_rows (this=0x7f7fbc131648)
            		 
                at /mariadb/10.3/storage/maria/ha_maria.cc:2667
            		 
            #4  0x0000000000b3ca5e in handler::ha_delete_all_rows (this=0x7f7fbc131648)
            		 
                at /mariadb/10.3/sql/handler.cc:4239
            		 
            #5  0x000000000091a01d in TABLE::insert_all_rows_into_tmp_table (
            		 
                this=0x7f7fbc2629a8, thd=0x7f7fbc000d60, tmp_table=0x7f7fbc260838, 
                tmp_table_param=0x7f7fbc0c3de8, with_cleanup=true)
            		 
                at /mariadb/10.3/sql/table.cc:7912
            		 
            #6  0x00000000008e808a in st_select_lex_unit::exec_recursive (
            		 
                this=0x7f7fbc038748) at /mariadb/10.3/sql/sql_union.cc:1730
            		 
            #7  0x00000000007671d5 in TABLE_LIST::fill_recursive (this=0x7f7fbc0cf5e8, 
                thd=0x7f7fbc000d60) at /mariadb/10.3/sql/sql_derived.cc:1055
            		 
            #8  0x00000000007662dd in mysql_derived_fill (thd=0x7f7fbc000d60, 
                lex=0x7f7fbc004ae0, derived=0x7f7fbc0cf5e8)
            		 
                at /mariadb/10.3/sql/sql_derived.cc:1139
            		 
            #9 0x0000000000766f67 in mysql_handle_single_derived (lex=0x7f7fbc004ae0, 
                derived=0x7f7fbc0cf5e8, phases=96) at /mariadb/10.3/sql/sql_derived.cc:197
            		 
            #10 0x00000000008425e0 in st_join_table::preread_init (this=0x7f7fbc0bfad8)
            		 
                at /mariadb/10.3/sql/sql_select.cc:12379
            		 
            #11 0x000000000084d527 in sub_select (join=0x7f7fbc0b03c8, 
                join_tab=0x7f7fbc0bfad8, end_of_records=false)
            		 
                at /mariadb/10.3/sql/sql_select.cc:19163
            		 
            #12 0x000000000083580a in do_select (join=0x7f7fbc0b03c8, procedure=0x0)
            		 
                at /mariadb/10.3/sql/sql_select.cc:18735
            		 
            #13 0x00000000008345eb in JOIN::exec_inner (this=0x7f7fbc0b03c8)
            		 
                at /mariadb/10.3/sql/sql_select.cc:3953
            		 
            #14 0x000000000083374e in JOIN::exec (this=0x7f7fbc0b03c8)
            		 
                at /mariadb/10.3/sql/sql_select.cc:3747
            		 
            #15 0x000000000080b2f6 in mysql_select (thd=0x7f7fbc000d60, 
                tables=0x7f7fbc0cf5e8, wild_num=1, fields=..., conds=0x0, og_num=0, 
                order=0x0, group=0x0, having=0x0, proc_param=0x0, 
                select_options=2147748608, result=0x7f7fbc068588, unit=0x7f7fbc004ba8, 
                select_lex=0x7f7fbc005340) at /mariadb/10.3/sql/sql_select.cc:4152
            		 
            #16 0x000000000080ab93 in handle_select (thd=0x7f7fbc000d60, 
                lex=0x7f7fbc004ae0, result=0x7f7fbc068588, setup_tables_done_option=0)
            		 
                at /mariadb/10.3/sql/sql_select.cc:370
            		 
            #17 0x00000000007cabdf in execute_sqlcom_select (thd=0x7f7fbc000d60, 
                all_tables=0x7f7fbc0cf5e8) at /mariadb/10.3/sql/sql_parse.cc:6539
            		 
            #18 0x00000000007bcffa in mysql_execute_command (thd=0x7f7fbc000d60)
            		 
                at /mariadb/10.3/sql/sql_parse.cc:3768
            		 
            #19 0x00000000007b7344 in mysql_parse (thd=0x7f7fbc000d60, 
                rawbuf=0x7f7fbc0d1fa8 "with recursive qn as\n(\nselect 1 as a from dual\nunion all\nselect a*2000 from qn where a<1", '0' <repeats 19 times>, "\n)\nselect * from qn", length=126, parser_state=0x7f7fcc750e00, is_com_multi=false, 
                is_next_command=false) at /mariadb/10.3/sql/sql_parse.cc:8001

            Another sample from the same run shows that data is being inserted into the temporary table, not deleted.

            marko Marko Mäkelä added a comment - igor , the test in 10.3 is still disabled, and it still hangs, like I wrote 2 days before your comment. Please check out 10.3. fix the hang and re-enable the test. ./mtr --mem --enable-disabled main.cte_recursive & sleep 60 echo 'thread apply all backtrace'|gdb -p $(pgrep -x mysqld) I am not sure if it is exactly a hang. It could just be a computation gone wild: 10.3 de942c9f618b590a01a7960c171d7e50e435708f #2 0x0000000001330509 in maria_delete_all_rows (info=0x7f7fbc0e4850) at /mariadb/10.3/storage/maria/ma_delete_all.c:145 #3 0x00000000012a2268 in ha_maria::delete_all_rows (this=0x7f7fbc131648) at /mariadb/10.3/storage/maria/ha_maria.cc:2667 #4 0x0000000000b3ca5e in handler::ha_delete_all_rows (this=0x7f7fbc131648) at /mariadb/10.3/sql/handler.cc:4239 #5 0x000000000091a01d in TABLE::insert_all_rows_into_tmp_table ( this=0x7f7fbc2629a8, thd=0x7f7fbc000d60, tmp_table=0x7f7fbc260838, tmp_table_param=0x7f7fbc0c3de8, with_cleanup=true) at /mariadb/10.3/sql/table.cc:7912 #6 0x00000000008e808a in st_select_lex_unit::exec_recursive ( this=0x7f7fbc038748) at /mariadb/10.3/sql/sql_union.cc:1730 #7 0x00000000007671d5 in TABLE_LIST::fill_recursive (this=0x7f7fbc0cf5e8, thd=0x7f7fbc000d60) at /mariadb/10.3/sql/sql_derived.cc:1055 #8 0x00000000007662dd in mysql_derived_fill (thd=0x7f7fbc000d60, lex=0x7f7fbc004ae0, derived=0x7f7fbc0cf5e8) at /mariadb/10.3/sql/sql_derived.cc:1139 #9 0x0000000000766f67 in mysql_handle_single_derived (lex=0x7f7fbc004ae0, derived=0x7f7fbc0cf5e8, phases=96) at /mariadb/10.3/sql/sql_derived.cc:197 #10 0x00000000008425e0 in st_join_table::preread_init (this=0x7f7fbc0bfad8) at /mariadb/10.3/sql/sql_select.cc:12379 #11 0x000000000084d527 in sub_select (join=0x7f7fbc0b03c8, join_tab=0x7f7fbc0bfad8, end_of_records=false) at /mariadb/10.3/sql/sql_select.cc:19163 #12 0x000000000083580a in do_select (join=0x7f7fbc0b03c8, procedure=0x0) at /mariadb/10.3/sql/sql_select.cc:18735 #13 0x00000000008345eb in JOIN::exec_inner (this=0x7f7fbc0b03c8) at /mariadb/10.3/sql/sql_select.cc:3953 #14 0x000000000083374e in JOIN::exec (this=0x7f7fbc0b03c8) at /mariadb/10.3/sql/sql_select.cc:3747 #15 0x000000000080b2f6 in mysql_select (thd=0x7f7fbc000d60, tables=0x7f7fbc0cf5e8, wild_num=1, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f7fbc068588, unit=0x7f7fbc004ba8, select_lex=0x7f7fbc005340) at /mariadb/10.3/sql/sql_select.cc:4152 #16 0x000000000080ab93 in handle_select (thd=0x7f7fbc000d60, lex=0x7f7fbc004ae0, result=0x7f7fbc068588, setup_tables_done_option=0) at /mariadb/10.3/sql/sql_select.cc:370 #17 0x00000000007cabdf in execute_sqlcom_select (thd=0x7f7fbc000d60, all_tables=0x7f7fbc0cf5e8) at /mariadb/10.3/sql/sql_parse.cc:6539 #18 0x00000000007bcffa in mysql_execute_command (thd=0x7f7fbc000d60) at /mariadb/10.3/sql/sql_parse.cc:3768 #19 0x00000000007b7344 in mysql_parse (thd=0x7f7fbc000d60, rawbuf=0x7f7fbc0d1fa8 "with recursive qn as\n(\nselect 1 as a from dual\nunion all\nselect a*2000 from qn where a<1", '0' <repeats 19 times>, "\n)\nselect * from qn", length=126, parser_state=0x7f7fcc750e00, is_com_multi=false, is_next_command=false) at /mariadb/10.3/sql/sql_parse.cc:8001 Another sample from the same run shows that data is being inserted into the temporary table, not deleted.

            People

              igor Igor Babaev (Inactive)
              alice Alice Sherepa
              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.