[MDEV-15575] different results when using CTE and big_tables=1 - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 10.2(EOL), 10.3(EOL)
Fix Version/s: 10.2.15, 10.3.7
Component/s: Optimizer - CTE
Labels:
None

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

MDEV-15447 Import CTE tests from MySQL 8

Stalled

Activity

Ascending order - Click to sort in descending order

Alice Sherepa created issue - 2018-03-15 14:20

Alice Sherepa made changes - 2018-03-15 14:20

Field	Original Value	New Value
Link		This issue relates to MDEV-15447 [ MDEV-15447 ]

Alice Sherepa made changes - 2018-03-15 14:20

Assignee

Igor Babaev [ igor ]

Alice Sherepa made changes - 2018-03-15 14:29

Description

{noformat}
MariaDB [test]> set big_tables=0;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> with recursive qn as
    -> (select 1 as a from dual group by a union all
    -> select a+1 from qn where a<3)
    -> select * from qn;
+---+
| a |
+---+
| 1 |
| 2 |
| 3 |
+---+
3 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 1 as a from dual group by a union all select a+1 from qn where a<3) select * from qn;
+---+
| a |
+---+
| 1 |
| 2 |
+---+
2 rows in set (0.00 sec)
{noformat}

{noformat}
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)

{noformat}

Igor Babaev (Inactive) added a comment - 2018-04-14 05:52

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 Babaev (Inactive) added a comment - 2018-04-14 05:52 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 Babaev (Inactive) added a comment - 2018-04-17 00:08

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

Igor Babaev (Inactive) added a comment - 2018-04-17 00:08 A fix for this bug was pushed into 10.2. It should be merged upstream as it is.

Igor Babaev (Inactive) made changes - 2018-04-17 00:08

Fix Version/s		10.2.15 [ 23006 ]
Fix Version/s	10.2 [ 14601 ]
Resolution		Fixed [ 1 ]
Status	Open [ 1 ]	Closed [ 6 ]

Igor Babaev (Inactive) made changes - 2018-04-17 17:00

Resolution	Fixed [ 1 ]
Status	Closed [ 6 ]	Stalled [ 10000 ]

Marko Mäkelä added a comment - 2018-04-19 12:30

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.

Marko Mäkelä added a comment - 2018-04-19 12:30 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.

Marko Mäkelä made changes - 2018-04-20 07:16

Affects Version/s

10.3 [ 22126 ]

Marko Mäkelä made changes - 2018-04-20 07:16

Fix Version/s

10.3.7 [ 23005 ]

Marko Mäkelä made changes - 2018-04-20 07:16

Priority

Major [ 3 ]

Critical [ 2 ]

Igor Babaev (Inactive) added a comment - 2018-04-21 06:43

A fix for this bug was pushed into 10.2

Igor Babaev (Inactive) added a comment - 2018-04-21 06:43 A fix for this bug was pushed into 10.2

Igor Babaev (Inactive) made changes - 2018-04-21 06:43

Fix Version/s	10.3.7 [ 23005 ]
Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

Marko Mäkelä added a comment - 2018-04-23 17:02

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 a2000 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 Mäkelä added a comment - 2018-04-23 17:02 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 Mäkelä made changes - 2018-04-23 17:02

Resolution	Fixed [ 1 ]
Status	Closed [ 6 ]	Stalled [ 10000 ]

Marko Mäkelä made changes - 2018-04-23 17:02

Fix Version/s

10.3.7 [ 23005 ]

Igor Babaev (Inactive) made changes - 2018-04-24 07:40

Status

Stalled [ 10000 ]

In Progress [ 3 ]

Marko Mäkelä made changes - 2018-04-25 05:08

Resolution		Fixed [ 1 ]
Status	In Progress [ 3 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2021-12-06 21:46

Workflow

MariaDB v3 [ 86050 ]

MariaDB v4 [ 153964 ]

People

Assignee:: Igor Babaev (Inactive)

Reporter:: Alice Sherepa

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2018-03-15 14:20

Updated:: 2018-05-07 20:22

Resolved:: 2018-04-25 05:08

Git Integration

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

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration