Mailing-List: contact dev-help@drill.apache.org; run by ezmlm
Precedence: bulk
Reply-To: dev@drill.apache.org
Date: Tue, 22 Nov 2016 12:58:58 +0000 (UTC)
From: "Nagarajan Chinnasamy (JIRA)" <jira@apache.org>
To: dev@drill.apache.org
Message-ID: <JIRA.13022489.1479819487000.338746.1479819538344@Atlassian.JIRA>
In-Reply-To: <JIRA.13022489.1479819487000@Atlassian.JIRA>
References: <JIRA.13022489.1479819487000@Atlassian.JIRA> <JIRA.13022489.1479819487354@arcas>
Subject: [jira] [Created] (DRILL-5063) Apache Drill Fails To Query MySQL
 View With Group By In Inner Select
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit
archived-at: Tue, 22 Nov 2016 12:59:00 -0000

Nagarajan Chinnasamy created DRILL-5063:
-------------------------------------------

             Summary: 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.8.0, 1.7.0
            Reporter: Nagarajan Chinnasamy
            Priority: Critical


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!!


--
This message was sent by Atlassian JIRA
(v6.3.4#6332)