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
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:
Set up Apache Drill instance in embedded mode and an MSSQL Server instance.
Create a database and a table inside it with date/datetime/datetime2 column.
Add RDBMS plug-in to your Apache Drill instance and configure it to connect to your MSSQL Server instance.
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
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(<DATE>, <CHARACTER>)
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.MyTableWe 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:0From 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:
SELECT MONTH([DateColumn]) FROM rdbms_schema.MyTableExpected 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.MyTableBut 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(<DATE>, <CHARACTER>)EDIT: Formatting