[MCOL-494] Cannot execute query on mixed engine tables Created: 2017-01-10  Updated: 2017-01-17  Resolved: 2017-01-16

Status: Closed
Project: MariaDB ColumnStore
Component/s: ExeMgr
Affects Version/s: 1.0.6.1
Fix Version/s: 1.0.7

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

Columnstore 1.0.6-1 in Docker container (CentOS 7); host is Fedora 25

Attachments: File columnstoreSupportReport.columnstore-1.tar.gz    
Issue Links:
Relates
relates to MCOL-356 BLOB data type not supported Closed
Sprint: 2017-01

 Description   

Columnstore is configured in my.cnf with:

infinidb_vtable_mode = 2
infinidb_compression_type = 2
...

and in Columnstore.xml with

<CrossEngineSupport>
<Host>localhost</Host>
<Port>3306</Port>
<User>root</User>
<Password/>
</CrossEngineSupport>

root has no password on localhost, as default.

Given the following mixed-engine structure:

create database testDB DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;
use testDB;

CREATE TABLE `idb_expr` (
`id` BIGINT NOT NULL ,
`repositoryid` VARCHAR(255) NOT NULL,
`code` LONGTEXT NOT NULL,
`expression` LONGTEXT NOT NULL,
`hash` BIGINT NOT NULL,
PRIMARY KEY (`id`),
KEY `idb_expr_idx_1` (`repositoryid`,`hash`)
) ENGINE = InnoDB;

CREATE TABLE `cs_cond` (
`id` BIGINT NOT NULL,
`repositoryid` VARCHAR(255) NOT NULL,
`aclinstanceid` BIGINT NULL,
`expressionid` BIGINT NOT NULL
) ENGINE = columnstore;

CREATE TABLE `cs_acl` (
`id` BIGINT NOT NULL,
`repositoryid` VARCHAR(255) NOT NULL,
`type` SMALLINT NOT NULL DEFAULT 0
) ENGINE = columnstore;

the following query:

select expr1.id as expr_id, expr1.code as expr_code, expr1.expression, expr1.hash, expr1.repositoryid as expr_repo from (idb_expr expr1 inner join cs_cond cond1 on expr1.id=cond1.expressionid) inner join cs_acl acl1 on cond1.aclinstanceid=acl1.id where acl1.type=1;

causes an exception:

ERROR 1815 (HY000): Internal error: InetStreamSocket::readToMagic: Remote is closed

Is it a bug or there's something wrong in my procedure?
The output of "columnstoreSupport -a" is atthached

 Comments   
Comment by Andrew Hutchings (Inactive) [ 2017-01-10 ]

Many thanks for the report. The query is causing ExeMgr to crash and the error is due to communication failure with it. I'm investigating further now.

Comment by Andrew Hutchings (Inactive) [ 2017-01-10 ]

The issue is the LONGTEXT columns in InnoDB. The ColumnStore does not yet know how to handle text and blob columns. When the cross-engine join processor passes these columns on they get misinterpreted and causes the crash in ExeMgr.

Obviously this is not the correct behaviour. In this scenario we should error for now. MCOL-356 will allow us to support blob/text columns correctly.

Comment by Andrew Hutchings (Inactive) [ 2017-01-10 ]

Patch triggers an error when there is a BLOB/TEXT in the returned columns list.
For testing just use the create tables and select queries. No data needed.

Comment by SuperC [ 2017-01-10 ]

Thanks for your work.
Are there any expectations for the MCOL-356?

Comment by Andrew Hutchings (Inactive) [ 2017-01-10 ]

We have not fully determined that yet but we are in meetings this week to start scoping that.

Comment by Daniel Lee (Inactive) [ 2017-01-16 ]

Build verified: Github source

[root@localhost mariadb-columnstore-server]# git show
commit 83b0e5c54a644bc31461752cf73f0e1140586d39
Merge: b975814 93c1c7e
Author: david hill <david.hill@mariadb.com>
Date: Thu Jan 12 09:27:28 2017 -0600

Merge pull request #26 from mariadb-corporation/MCOL-500

Update README.md

Server version: 10.1.20-MariaDB Columnstore 1.0.7-1

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use mytest
Database changed
MariaDB [mytest]> CREATE TABLE `idb_expr` (
-> `id` BIGINT NOT NULL ,
-> `repositoryid` VARCHAR(255) NOT NULL,
-> `code` LONGTEXT NOT NULL,
-> `expression` LONGTEXT NOT NULL,
-> `hash` BIGINT NOT NULL,
-> PRIMARY KEY (`id`),
-> KEY `idb_expr_idx_1` (`repositoryid`,`hash`)
-> ) ENGINE = InnoDB;
Query OK, 0 rows affected (0.02 sec)

MariaDB [mytest]> CREATE TABLE `cs_cond` (
-> `id` BIGINT NOT NULL,
-> `repositoryid` VARCHAR(255) NOT NULL,
-> `aclinstanceid` BIGINT NULL,
-> `expressionid` BIGINT NOT NULL
-> ) ENGINE = columnstore;
Query OK, 0 rows affected (0.17 sec)

MariaDB [mytest]> CREATE TABLE `cs_acl` (
-> `id` BIGINT NOT NULL,
-> `repositoryid` VARCHAR(255) NOT NULL,
-> `type` SMALLINT NOT NULL DEFAULT 0
-> ) ENGINE = columnstore;
Query OK, 0 rows affected (0.10 sec)

MariaDB [mytest]> select expr1.id as expr_id, expr1.code as expr_code, expr1.expression, expr1.hash, expr1.repositoryid as expr_repo from (idb_expr expr1 inner join cs_cond cond1 on expr1.id=cond1.expressionid) inner join cs_acl acl1 on cond1.aclinstanceid=acl1.id where acl1.type=1;
ERROR 1815 (HY000): Internal error: BLOB/TEXT data types are not supported by ColumnStore.

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