apache / apache/drill

Drill incorrectly translates date/time function when using RDBMS plug-in with underlying MSSQL datasource.

Open
#2,846 0 comments 0 reactions 0 assignees View on GitHub
bug
Dominant language
Java
Stars
2k
Forks
990
Avg merge
1d 8h
Merged PRs (30d)
5

Description

**Describe the bug**
We have an Apache Drill instance set up and running in embedded mode. RDBMS plug-in is used to connect existing MSSQL database to Drill. Plug-in configuration looks like this

`{
"type": "jdbc",
"driver": "com.microsoft.sqlserver.jdbc.SQLServerDriver",
"url": "jdbc:sqlserver://*********;databaseName=*******",
"username": "MyUserName",
"password": "MyPassword",
"sourceParameters": {
"keepaliveTime": 0,
"minimumIdle": 0,
"idleTimeout": 3600000,
"maximumPoolSize": 10,
"maxLifetime": 21600000
},
"authMode": "SHARED_USER",
"writerBatchSize": 10000,
"enabled": true
}`

Most of the statements we tried running through Drill works fine. But recently we wrote a couple of queries where we need to extract month from a **date** field and we encountered some errors. More precisely:

When executing following SQL statement
`select YEAR(DateColumn) from rdbms_schema.MyTable`

We receive such error response from Apache Drill

`
org.apache.drill.common.exceptions.UserRemoteException: DATA_READ ERROR: The JDBC storage plugin failed while trying setup the SQL query.
Sql: SELECT EXTRACT(YEAR FROM "DateColumn")
FROM "rdbms_schema"."MyTable"
Fragment: 0:0
`

From which we can see that Drill actually translates our query before passing to MSSQL server to use **EXTRACT(...)**, but the problem is that **EXTRACT** keyword is not supported in MSSQL server.

**To Reproduce**
Steps to reproduce the behavior:
1. Set up Apache Drill instance in embedded mode and an MSSQL Server instance.
2. Create a database and a table inside it with _date/datetime/datetime2_ column.
3. Add RDBMS plug-in to your Apache Drill instance and configure it to connect to your MSSQL Server instance.
4. Try executing a query which extracts _year/month/day_ from _date/datetime/datetime2_ column. Something along the lines of `SELECT MONTH([DateColumn]) FROM rdbms_schema.MyTable`
5. See error.

**Expected behavior**
Query should be translated to support underlying RDBMS datasource keywords and functions.

**Drill version**
1.21.1

**Additional context**
As a workaround we tried getting the month field by using FORMAT like this (equivalent works in MSSQL server):
`select FORMAT(DateColumn, 'MM') from rdbms_schema.MyTable`

But we received the following error:
`
org.apache.drill.common.exceptions.UserRemoteException: VALIDATION ERROR: From line 1, column 8 to line 1, column 27: No match found for function signature FORMAT(, )
`

EDIT: Formatting

Contributor guide

No contributing guide indexed for this repository

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.