Hacktoberfest 2026: the issues maintainers tagged for October, open and beginner-friendly. Browse Hacktoberfest issues

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

Open
  • Is something wrong?
#2,758 1 comment 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Assessment

Difficulty
4/5
Estimated time
3-5 days
Newbie friendliness
25/100
Issue type
Bug
Clarity
Needs clarification
Activity status
Stale
Tech stack
java, mysql, sql
Domain
backend, databases

Research direction

Start by reproducing the supplied multi-table LEFT JOIN against MySQL through the Drill 1.20.2 JDBC storage configuration and inspect the generated SQL in the error. Done means the query executes successfully without the JDBC storage plugin setup failure.

Written by the indexing model from the issue text.

Description

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

Dominant language
Java
Stars
2k
Forks
989
Avg merge
1d 14h
Merged PRs (30d)
4

Getting set up

  • Ships a Dockerfile or Docker Compose file
  • Has a pull request template
  • No contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from apache/drill

All issues in apache/drill

Similar issues

More Java issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.