cube-js / cube-js/cube

MSSQL Server SQL Query With Date Subtraction Filter Clause

Open
#7,122 2 comments 0 reactions 0 assignees View on GitHub
api:sql driver:mssql
Dominant language
Rust
Stars
20.8k
Forks
2.1k
Avg merge
1d 2h
Merged PRs (30d)
181

Description

**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:

```sql
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:

```sql
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:
```sql
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.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.