[MCOL-783] Recursive Common Table Expressions caused mysqld to crash Created: 2017-06-22  Updated: 2017-10-24  Resolved: 2017-10-24

Status: Closed
Project: MariaDB ColumnStore
Component/s: ExeMgr
Affects Version/s: 1.1.0
Fix Version/s: 1.1.1

Type: Bug Priority: Major
Reporter: Daniel Lee (Inactive) Assignee: Daniel Lee (Inactive)
Resolution: Fixed Votes: 0
Labels: relnote

Issue Links:
Relates
relates to MCOL-598 Add CTE functionality to Columnstore Closed
Sprint: 2017-20, 2017-21

 Description   

Build tested: Github source 1.1.0-1
[root@localhost mariadb-columnstore-server]# git show
commit 594ef1807a5d6cba45cf7c2bed03cccdc32f177a
Merge: a5f191d ce815f9
Author: David.Hall <david.hall@mariadb.com>
Date: Thu Jun 8 10:12:50 2017 -0500
[root@localhost mariadb-columnstore-engine]# git show
commit ebaf24473c0838989bf504a7c104c511b876fcb8
Author: david hill <david.hill@mariadb.com>
Date: Fri Jun 16 16:53:48 2017 -0500

Recursive CTE caused mysqld to crash.
create table folks (id int, name varchar(20), father int, mother int) engine=columnstore;
insert into folks values (100, 'Alex', 20, 30);
insert into folks values (20, 'Dad', 10, NULL);
insert into folks values (30, 'Mom', NULL, NULL);
insert into folks values (10, 'Grandpa', NULL, NULL);
insert into folks values (98, 'Sister Amy', 20, 30);
select * from folks;
with recursive ancestors as (
select * from folks
where name = 'Alex'
union
select f.*
from folks as f, ancestors as a
where
f.id = a.father or f.id = a.mother
)
select * from ancestors;
with recursive ancestors as ( select * from folks where name = 'Alex' union select f.* from folks as f, ancestors as a where f.id = a.father or f.id = a.mother ) select * from ancestors;
ERROR 2013 (HY000): Lost connection to MySQL server during query
content in /usr/local/mariadb/columnstore/mysql/db/localhost.localdomain.err
170621 22:52:02 mysqld_safe Number of processes running now: 0
170621 22:52:02 mysqld_safe mysqld restarted
2017-06-21 22:52:02 140282559596608 [Note] /usr/local/mariadb/columnstore/mysql//bin/mysqld (mysqld 10.2.6-MariaDB-log) starting as process 26696 ...
170621 22:52:02 Columnstore: Started; Version: 1.0.2-1
170621 22:52:02 InfiniDB: Started; Version: 1.0.2-1
2017-06-21 22:52:02 140282559596608 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2017-06-21 22:52:02 140282559596608 [Note] InnoDB: Uses event mutexes
2017-06-21 22:52:02 140282559596608 [Note] InnoDB: Compressed tables use zlib 1.2.7
2017-06-21 22:52:02 140282559596608 [Note] InnoDB: Using Linux native AIO
2017-06-21 22:52:02 140282559596608 [Note] InnoDB: Number of pools: 1
2017-06-21 22:52:02 140282559596608 [Note] InnoDB: Using SSE2 crc32 instructions
2017-06-21 22:52:02 140282559596608 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2017-06-21 22:52:02 140282559596608 [Note] InnoDB: Completed initialization of buffer pool
2017-06-21 22:52:02 140281482487552 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2017-06-21 22:52:02 140282559596608 [Note] InnoDB: Highest supported file format is Barracuda.
2017-06-21 22:52:02 140282559596608 [Note] InnoDB: Starting crash recovery from checkpoint LSN=7231013722
InnoDB: Last MySQL binlog file position 0 1526, file name /usr/local/mariadb/columnstore/mysql/db/mysql-bin.000003
2017-06-21 22:52:02 140282559596608 [Note] InnoDB: 128 out of 128 rollback segments are active.
2017-06-21 22:52:02 140282559596608 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2017-06-21 22:52:02 140282559596608 [Note] InnoDB: Creating shared tablespace for temporary tables
2017-06-21 22:52:02 140282559596608 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2017-06-21 22:52:02 140282559596608 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2017-06-21 22:52:02 140282559596608 [Note] InnoDB: Waiting for purge to start
2017-06-21 22:52:02 140282559596608 [Note] InnoDB: 5.7.14 started; log sequence number 7231013731
2017-06-21 22:52:02 140281332619008 [Note] InnoDB: Loading buffer pool(s) from /usr/local/mariadb/columnstore/mysql/db/ib_buffer_pool
2017-06-21 22:52:02 140281332619008 [Note] InnoDB: Buffer pool(s) load completed at 170621 22:52:02
2017-06-21 22:52:02 140282559596608 [Note] Plugin 'FEEDBACK' is disabled.
2017-06-21 22:52:02 140282559596608 [Note] Recovering after a crash using /usr/local/mariadb/columnstore/mysql/db/mysql-bin
2017-06-21 22:52:02 140282559596608 [Note] Starting crash recovery...
2017-06-21 22:52:02 140282559596608 [Note] Crash recovery finished.
2017-06-21 22:52:02 140282559596608 [Note] Server socket created on IP: '::'.
2017-06-21 22:52:02 140282559596608 [Note] Reading of all Master_info entries succeded
2017-06-21 22:52:02 140282559596608 [Note] Added new Master_info '' to hash table
2017-06-21 22:52:02 140282559596608 [Note] /usr/local/mariadb/columnstore/mysql//bin/mysqld: ready for connections.
Version: '10.2.6-MariaDB-log' socket: '/usr/local/mariadb/columnstore/mysql/lib/mysql/mysql.sock' port: 3306 Columnstore 1.1.0-1

 Comments   
Comment by David Thompson (Inactive) [ 2017-07-31 ]

If possible to fix could be powerful though likely only performant on reduced data sets.

Comment by David Hall (Inactive) [ 2017-10-05 ]

Added check for recursive CTE and report not supported.

Comment by David Thompson (Inactive) [ 2017-10-06 ]

This also prevents non recursive CTE's from working which seem to work just fine. We should just block the recursive case. The following should still work:

create table i1(i int, c char(3)) engine=columnstore;
 
insert into i1 values (1, 'abc'), (2, 'abc');
 
with v as (select c, sum(i) s from i1 group by c) select * from v;

Comment by David Thompson (Inactive) [ 2017-10-06 ]

i.e we should just block the 'with recursive' case.

Comment by Daniel Lee (Inactive) [ 2017-10-23 ]

According to the comment, there is no work done to the ticket so it should not be in QA. Reopening it.

Comment by David Hall (Inactive) [ 2017-10-23 ]

It turns out that the flag "with_clause->with_recursive" is set for non-recursive CTE as well as recursive. The test is changed to use "table_ptr->is_recursive_with_table()" instead. Also, the check is moved such that all CTE in the query will be checked rather than just the first one. Thus if there are a mix of recursive and non-recursive CTE, each will be checked in turn until a recursive CTE is found, at which point the error will be returned and the query aborted.

Comment by Daniel Lee (Inactive) [ 2017-10-24 ]

Build verified: 1.1.1-1 GitHub source
/root/columnstore/mariadb-columnstore-server
commit 3d846d3277ba970b32ba3f471323fcac58b5c35d
Author: david hill <david.hill@mariadb.com>
Date: Mon Oct 23 09:57:05 2017 -0500
change to 1.1.1
/root/columnstore/mariadb-columnstore-server/mariadb-columnstore-engine
commit 4aa7eb1830ddf585706f804b1982589c7d67ff0a
Author: root <root@srvhill01.lan>
Date: Mon Oct 23 09:56:07 2017 -0500
change to 1.1.1

MariaDB [mytest]> with recursive ancestors as ( select * from folks where name = 'Alex' union select f.* from folks as f, ancestors as a where f.id = a.father or f.id = a.mother ) select * from ancestors;
ERROR 1178 (42000): The storage engine for the table doesn't support Recursive CTE
MariaDB [mytest]> create table i1(i int, c char(3)) engine=columnstore;
Query OK, 0 rows affected (0.35 sec)

MariaDB [mytest]> insert into i1 values (1, 'abc'), (2, 'abc');
Query OK, 2 rows affected (0.55 sec)
Records: 2 Duplicates: 0 Warnings: 0

MariaDB [mytest]> with v as (select c, sum s from i1 group by c) select * from v;
----------+

c s

----------+

abc 3

----------+
1 row in set (0.34 sec)

Generated at Thu Feb 08 02:23:47 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.