[
https://issues.apache.org/jira/browse/DRILL-5063?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Nagarajan Chinnasamy updated DRILL-5063:
----------------------------------------
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:
{code}
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;
{code}
I have created a view on MySQL using the following query:
{code}
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`;
{code}
In Drill I have created a storage plugin named mysql with following
configuration:
{code}
{
"type": "jdbc",
"driver": "com.mysql.jdbc.Driver",
"url": "jdbc:mysql://localhost:3306",
"username": "xxxx",
"password": "xxxx",
"enabled": true
}
{code}
When I issue the following query from sqlline.bat on windows:
{code}
select * from mysql.`mydb`.`viewcustomercontact `;
{code}
I get the following error:
{noformat}
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
{noformat}
was:
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 created a view on MySQL using the following query:
{code}
create or replace view `mydb`.`customerview` as
select
`cust`.`Id` as `Customer`,
`tnt`.`Id` as `Tenant`
from
`mydb`.`Customer` cust
left join (
select
`t`.Id as Id
from
`mydb`.`Tenant` t
group by t.Id) tnt
on
`cust`.`Tenant`=`tnt`.`Id`
{code}
Please note that above query was purposely written so to reproduce the problem
when using group by.
In Drill I have created a storage plugin named mydb with following
configuration:
{code}
{
"type": "jdbc",
"driver": "com.mysql.jdbc.Driver",
"url": "jdbc:mysql://localhost:3306",
"username": "xxxx",
"password": "xxxx",
"enabled": true
}
{code}
When I issue the following query from sqlline.bat on windows:
{code}
select * from mydb.`mydb`.`customerview`;
{code}
I get the following error:
{noformat}
Error: DATA_READ ERROR: The JDBC storage plugin failed while trying setup the
SQL query.
sql SELECT * FROM mydb.customerview plugin mydb Fragment 0:0
{noformat}
Also, please note that if I remove group by in the inner select query, the
error goes away!!
> Apache Drill Fails To Query MySQL View With Group By In Inner Select
> --------------------------------------------------------------------
>
> Key: DRILL-5063
> URL: https://issues.apache.org/jira/browse/DRILL-5063
> Project: Apache Drill
> Issue Type: Bug
> Components: Client - JDBC, Storage - JDBC
> Affects Versions: 1.7.0, 1.8.0
> Reporter: Nagarajan Chinnasamy
> Priority: Critical
> Labels: DATA_READ, GROUP_BY, MySQL
>
> 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:
> {code}
> 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;
> {code}
> I have created a view on MySQL using the following query:
> {code}
> 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`;
> {code}
> In Drill I have created a storage plugin named mysql with following
> configuration:
> {code}
> {
> "type": "jdbc",
> "driver": "com.mysql.jdbc.Driver",
> "url": "jdbc:mysql://localhost:3306",
> "username": "xxxx",
> "password": "xxxx",
> "enabled": true
> }
> {code}
> When I issue the following query from sqlline.bat on windows:
> {code}
> select * from mysql.`mydb`.`viewcustomercontact `;
> {code}
> I get the following error:
> {noformat}
> 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
> {noformat}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
