[DRILL-5063] Apache Drill Fails To Query MySQL View With Group By In Inner Select

• Type: Bug
• Status: Open
• Priority: Critical
• Resolution: Unresolved
• Affects Version/s: 1.7.0, 1.8.0
• Component/s: Client - JDBC, Storage - JDBC
• Labels: DATA_READ GROUP_BY MySQL
• Assignee: Unassigned
• Reporter: Nagarajan Chinnasamy
• Created: 22/Nov/16 12:58
• Updated: 24/Nov/16 07:54

Description

I am using

MySQL Version: 5.7.8 Drill Version: 1.7 and 1.8 MySQL JDBC Connector: mysql-connector-java-5.1.39-bin.jar

I have following 2 tables created using:

CREATE DATABASE IF NOT EXISTS mydb;
USE `mydb`;

CREATE TABLE `customer` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`Id`),
  KEY `fk_` (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

CREATE TABLE `customercontact` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `CustomerId` int(11) DEFAULT NULL,
  `ContactValue` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`Id`),
  KEY `fk_cust_idx` (`CustomerId`),
  CONSTRAINT `fk_cust` FOREIGN KEY (`CustomerId`) REFERENCES `customer` (`Id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

I have created a view on MySQL using the following query:

create or replace view viewcustomercontact as
	select
		`c`.`id` as `cusomertid`,
        `contact`.`contactvalue` as `contactvalue`
	from
		mydb.customer as c
	left join (
		select
			`cc`.`CustomerId` as `CustomerId`,
             group_concat(`cc`.`ContactValue`,',') as `ContactValue`
		from
			mydb.customerContact as cc
		group by `cc`.`CustomerId`) as contact
	on `c`.`Id` = `Contact`.`CustomerId`;

In Drill I have created a storage plugin named mysql with following configuration:

{
  "type": "jdbc",
  "driver": "com.mysql.jdbc.Driver",
  "url": "jdbc:mysql://localhost:3306",
  "username": "xxxx",
  "password": "xxxx",
  "enabled": true
}

When I issue the following query from sqlline.bat on windows:

select * from mysql.`mydb`.`viewcustomercontact `;

I get the following error:

Error: DATA_READ ERROR: The JDBC storage plugin failed while trying setup the SQL query.

sql SELECT * FROM mydb.viewcustomercontact plugin mysql Fragment 0:0