Querying MySQL View Fails with DATA_READ ERROR

[Nagarajan Chinnasamy]

Facing this problem with:

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:

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`


Please note that above query was purposely written so to reproduce the
problem.

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


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


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

select * from mydb.`mydb`.`customerview`;


I get the following error:

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


Also, please note that if I remove "group by" in the inner select query,
the error goes away.

Please explain whats going on? Or is this a bug?


Best Regards,
Nagu.