Drill incorrectly translates date/time function when using RDBMS plug-in with underlying MSSQL datasource.
- 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.