[MCOL-965] group_contact with order by on joined table causes error Created: 2017-10-10  Updated: 2017-10-23  Resolved: 2017-10-23

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

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

Issue Links:
Relates
relates to MCOL-902 Group_concat() performance and gettra... Closed
Sprint: 2017-20, 2017-21

 Description   

When working on MCOL-902, which complains about the reported execution time time, the sample query crashes mysqld. The crash is due to a failure to check return code at ha_exec_plan.cpp around line 3608. The call to buildReturnedColumn() fails, sets gwi.fatalParseError and returns NULL. The return value is used without checking first. In general, buildReturnedColumn() should never fail, as all syntax is checked before getting to this point. However, something is stripping the schema name off of the GROUP_CONCAT's ORDER BY clause.

After fixing the crash issue, the query fails with:
ERROR 1178 (42000): The storage engine for the table doesn't support IDB-3009: Unknown column '..g_position', which is why buildReturnedColumn() reports an error.

This query works in 1.0

CREATE TABLE `phased_genotype` ( `animalID` varchar(18) DEFAULT NULL, `mid` int(11) DEFAULT NULL, `phase` tinyint(4) DEFAULT NULL ) ENGINE=Columnstore DEFAULT CHARSET=utf8;

CREATE TABLE `phased_snp_info` ( `mid` int(11) DEFAULT NULL, `chr` tinyint(4) DEFAULT NULL, `position` bigint(20) DEFAULT NULL, `g_position` int(11) DEFAULT NULL ) ENGINE=Columnstore DEFAULT CHARSET=utf8;

select animalID,group_concat(phase order by b.g_position separator '') as g from phased_genotype a,phased_snp_info b where a.mid=b.mid group by animalID;

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

There is special Columnstore code in sql_select.cc to prevent just this issue. Unfortunately, the surrounding code has been changed to the point that the action was being taken too soon, so the special code had to move a few lines:

// @InfiniDB We don't need tmp table for vtable create phase. Plus
// to build tmp table may corrupt some field table_name & db_name (for some reason)
if (thd->infinidb_vtable.vtable_state == THD::INFINIDB_CREATE_VTABLE)
need_tmp = false;

Moving this to below the call to make_join_readinfo() fixes the problem.

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

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

Verified reported test case, as well as test case with data.

MariaDB [mytest]> CREATE TABLE `phased_genotype` ( `animalID` varchar(18) DEFAULT NULL, `mid` int(11) DEFAULT NULL, `phase` tinyint(4) DEFAULT NULL ) ENGINE=Columnstore DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.56 sec)

MariaDB [mytest]> CREATE TABLE `phased_snp_info` ( `mid` int(11) DEFAULT NULL, `chr` tinyint(4) DEFAULT NULL, `position` bigint(20) DEFAULT NULL, `g_position` int(11) DEFAULT NULL ) ENGINE=Columnstore DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.42 sec)

MariaDB [mytest]> select animalID,group_concat(phase order by b.g_position separator '') as g from phased_genotype a,phased_snp_info b where a.mid=b.mid group by animalID;
Empty set (0.31 sec)

MariaDB [mytest]> select animalID,group_concat(phase order by b.g_position separator ' ') as g from phased_genotype a,phased_snp_info b where a.mid=b.mid group by animalID;
-----------------------------------------+

animalID g

-----------------------------------------+

a1 1
a2 10 11 2 11 10 2 11 2 10 11 10 2

-----------------------------------------+
2 rows in set (0.12 sec)

MariaDB [mytest]> select * from phased_genotype;
-------------------

animalID mid phase

-------------------

a1 1 1
a2 2 2
a2 2 10
a2 2 11

-------------------
4 rows in set (0.05 sec)

MariaDB [mytest]> select * from phased_snp_info;
----------------------------+

mid chr position g_position

----------------------------+

1 1 10001 1
2 2 10002 2
2 2 10002 1
2 2 10002 3
2 2 10002 4

----------------------------+
5 rows in set (0.06 sec)

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