fail to execute multi-table left join queries in MySQL when using Drill 1.20.2

[SeaSoonKeun]

Hello everyone, I encountered some problems when I was using Drill 1.20.2 to connect MySQL database. When I execute the following SQL statement, an error occurred:

SQL statement as follows:

SELECT t.name AS apiName, t.param_name AS requestParamName, t.unit_field_name AS unitFieldName FROM (SELECT t1.id AS api_id, t3.api_request_param_id AS id, t1.name AS name, t1.created_by AS created_by, t2.param_name AS param_name, t2.param_type AS param_type, t3.unit_field_name AS unit_field_name FROM mysql11.drillTest.`api_request_param` t2 LEFT JOIN mysql11.drillTest.`api_config` t1 ON t2.api_id = t1.id LEFT JOIN mysql11.drillTest.`api_request_param_unit_assoc` t3 ON t2.id = t3.api_request_param_id) t WHERE t.api_id = '240'

Error message as follows:

org.apache.drill.common.exceptions.UserRemoteException: DATA_READ ERROR: The JDBC storage plugin failed while trying setup the SQL query.
  Sql: SELECT `t0`.`name` AS `apiName`, `t0`.`param_name` AS `requestParamName`, `api_request_param_unit_assoc`.`unit_field_name` AS `unitFieldName`
  FROM (SELECT `api_request_param`.`id`, `api_request_param`.`api_id`, `api_request_param`.`param_name`, `api_request_param`.`param_type`, `api_request_param`.`op`, `api_request_param`.`sample`, `api_request_param`.`description`, `api_request_param`.`is_required`, `api_request_param`.`is_public`, `api_request_param`.`create_time`, `api_request_param`.`update_time`, `api_request_param`.`is_deleted`, CAST(`t`.`id` AS SIGNED) AS `id0`, `t`.`name`, `t`.`api_type`, `t`.`request_type`, `t`.`description` AS `description0`, `t`.`return_type`, `t`.`return_limit_num`, `t`.`expire_ttl`, `t`.`is_buffered`, `t`.`buffer_expire_ttl`, `t`.`api_sql`, `t`.`status`, `t`.`created_by`, `t`.`user_id`, `t`.`create_time` AS `create_time0`, `t`.`updated_by`, `t`.`update_time` AS `update_time0`, CAST(`t`.`is_deleted` AS TINYINT) AS `is_deleted0`
  FROM `drillTest`.`api_request_param`
  INNER JOIN (SELECT *
  FROM `drillTest`.`api_config`
  WHERE `id` = '240') AS `t` ON `api_request_param`.`api_id` = `t`.`id`) AS `t0`
  LEFT JOIN `drillTest`.`api_request_param_unit_assoc` ON `t0`.`id` = `api_request_param_unit_assoc`.`api_request_param_id`
  Fragment: 0:0

  [Error Id: c1c179d8-f849-4a84-b798-93bf4dfcd159 on master1:31010]

This is my storage related configuration:

  {
    "type": "jdbc",
    "driver": "com.mysql.jdbc.Driver",
    "url": "jdbc:<mysql://182.44.3.11:31150/drillTest>",
    "username": "xxxx",
    "password": "xxxx",
    "sourceParameters": {
      "keepaliveTime": 0,
      "minimumIdle": 0,
      "idleTimeout": 3600000,
      "maximumPoolSize": 10,
      "maxLifetime": 21600000
    },
  "writerBatchSize": 10000,
  "enabled": true
}

How do we solve this problem? Thank you!

Slack Message

[SeaSoonKeun]

Does Drill support multi-table left join queries in MySQL? I have found that the corresponding statement of EXPLAIN PLAN FOR cannot be executed in MySQL.

drill query sql：

SELECT t.name AS apiName, t.param_name AS requestParamName, t.unit_field_name AS unitFieldName FROM (SELECT t1.id AS api_id, t3.api_request_param_id AS id, t1.name AS name, t1.created_by AS created_by, t2.param_name AS param_name, t2.param_type AS param_type, t3.unit_field_name AS unit_field_name FROM mysql11.drillTest.`api_request_param` t2 LEFT JOIN mysql11.drillTest.`api_config` t1 ON t2.api_id = t1.id LEFT JOIN mysql11.drillTest.`api_request_param_unit_assoc` t3 ON t2.id = t3.api_request_param_id) t WHERE t.api_id = '240'

expliain plan for sql：

SELECT
	`t0`.`name` AS `apiName`,
	`t0`.`param_name` AS `requestParamName`,
	`api_request_param_unit_assoc`.`unit_field_name` AS `unitFieldName` 
FROM
	(
	SELECT
		`api_request_param`.`id`,
		`api_request_param`.`api_id`,
		`api_request_param`.`param_name`,
		`api_request_param`.`param_type`,
		`api_request_param`.`op`,
		`api_request_param`.`sample`,
		`api_request_param`.`description`,
		`api_request_param`.`is_required`,
		`api_request_param`.`is_public`,
		`api_request_param`.`create_time`,
		`api_request_param`.`update_time`,
		`api_request_param`.`is_deleted`,
		CAST( `t`.`id` AS SIGNED ) AS `id0`,
		`t`.`name`,
		`t`.`api_type`,
		`t`.`request_type`,
		`t`.`description` AS `description0`,
		`t`.`return_type`,
		`t`.`return_limit_num`,
		`t`.`expire_ttl`,
		`t`.`is_buffered`,
		`t`.`buffer_expire_ttl`,
		`t`.`api_sql`,
		`t`.`status`,
		`t`.`created_by`,
		`t`.`user_id`,
		`t`.`create_time` AS `create_time0`,
		`t`.`updated_by`,
		`t`.`update_time` AS `update_time0`,
		CAST( `t`.`is_deleted` AS TINYINT ) AS `is_deleted0` 
	FROM
		`drillTest`.`api_request_param`
		INNER JOIN ( SELECT * FROM `drillTest`.`api_config` WHERE `id` = '240' ) AS `t` ON `api_request_param`.`api_id` = `t`.`id` 
	) AS `t0`
	LEFT JOIN `drillTest`.`api_request_param_unit_assoc` ON `t0`.`id` = `api_request_param_unit_assoc`.`api_request_param_id`

mysql execute info：