MSSQL Server SQL Query With Date Subtraction Filter Clause

[HoseinBahmany]

Failed SQL Search for Failed SQL log message.

2023-09-12 08:48:50 Cube SQL Error: undefined 
2023-09-12 08:48:50 --
2023-09-12 08:48:50 "SELECT first_name, last_name, leave_start_date, leave_end_date FROM employee_leaves WHERE leave_end_date - leave_start_date > 20"
2023-09-12 08:48:50 --
2023-09-12 08:48:50 {
2023-09-12 08:48:50   "appName": "DataGrip 2022.3.3",
2023-09-12 08:48:50   "protocol": "postgres",
2023-09-12 08:48:50   "apiType": "sql",
2023-09-12 08:48:50   "sanitizedQuery": "SELECT first_name, last_name, leave_start_date, leave_end_date FROM employee_leaves WHERE leave_end_date - leave_start_date > 20"
2023-09-12 08:48:50 }

Logical Plan Search for Can't rewrite plan log message.

2023-09-12 08:48:50 Error during rewrite: Can't detect Cube query and it may be not supported yet. Please check logs for additional information.

Version: 0.33.53

Additional context I'm using MS SQL Server as the data source. When I run the following query via the SQL API:

SELECT first_name, last_name, leave_start_date, leave_end_date
FROM employee_leaves
WHERE leave_end_date - leave_start_date > 20;

I get the described error. The error seems to be with the date filter condition. Removing the condition it works:

SELECT first_name, last_name, leave_start_date, leave_end_date
FROM employee_leaves

and results in the following data:

first_name	last_name	leave_start_date	leave_end_date
XXX	YYYY	2017-07-02 00:00:00.000000	2017-07-09 00:00:00.000000
ZZZ	WWW	2017-07-16 00:00:00.000000	2017-07-17 00:00:00.000000

I thought maybe the date subtraction is not supported in the filter clause but the following sample works:

WITH temp_table AS (SELECT 1)
SELECT 1
FROM temp_table
WHERE ('2022-01-22'::DATE - '2022-01-01'::DATE) > 20;

So not sure what is causing the issue.

[paveltiunov]

@HoseinBahmany This type of query isn't supported yet but is on our roadmap. It'll be supported by SQL push-down.

[HoseinBahmany]

Thanks for the feedback @paveltiunov