Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.2(EOL), 10.3(EOL)
-
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
A fix for this bug was pushed into 10.2.
It should be merged upstream as it is.
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, 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.
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 recursiveancestorsas(select *from folkswhere name = 'Me' and dob = '2000-01-01'unionselect p.id, p.name, p.dob, p.father, p.motherfrom folks as p, ancestors AS awhere p.id = a.father or p.id = a.mother)select * from ancestors;set big_tables=1;with recursiveancestorsas(select *from folkswhere name = 'Me' and dob = '2000-01-01'unionselect p.id, p.name, p.dob, p.father, p.motherfrom folks as p, ancestors AS awhere 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)