Uploaded image for project: 'Apache Drill'
  1. Apache Drill
  2. DRILL-5063

Apache Drill Fails To Query MySQL View With Group By In Inner Select

    XMLWordPrintableJSON

    Details

      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
      

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              nagarajanchinnasamy Nagarajan Chinnasamy
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated: