cube-js / cube-js/cube

DATE_TRUNC filtering failing only in some cases

Open
#8,886 1 comment 0 reactions 1 assignee Claimed by @paveltiunov View on GitHub
api:sql
Dominant language
Rust
Stars
20.8k
Forks
2.1k
Avg merge
1d 2h
Merged PRs (30d)
181

Description

**Describe the bug**
DATE_TRUNC filtering fails in some cases but not others.

It fails for this query:
```sql
SELECT
"public"."Order"."channel" AS "channel",
SUM(
CASE
WHEN DATE_TRUNC('month', "public"."Order"."orderDate") BETWEEN DATE_TRUNC('month', (NOW() + INTERVAL '-2 month'))

AND DATE_TRUNC('month', (NOW() + INTERVAL '-1 month')) THEN "public"."Order"."lineSubtotal"
ELSE 0.0
END
) AS "tst2"
FROM
"public"."Order"
GROUP BY
"public"."Order"."channel"
ORDER BY
"public"."Order"."channel" ASC
```

But not for this one:
```sql
SELECT
"public"."Order"."channel" AS "channel",
SUM(
CASE
WHEN DATE_TRUNC('month', "public"."Order"."orderDate") = DATE_TRUNC('month', (NOW() + INTERVAL '-1 month')) THEN "public"."Order"."lineSubtotal"
ELSE 0.0
END
) AS "tst2"
FROM
"public"."Order"
GROUP BY
"public"."Order"."channel"
ORDER BY
"public"."Order"."channel" ASC
```

**Error Message:**
```
ERROR: Error during rewrite: Can't detect Cube query and it may be not supported yet. Please check logs for additional information. QUERY: SELECT "public"."Order"."channel" AS "channel", SUM(CASE WHEN DATE_TRUNC('month', "public"."Order"."orderDate") BETWEEN DATE_TRUNC('month', (NOW() + INTERVAL '-2 month')) AND DATE_TRUNC('month', (NOW() + INTERVAL '-1 month')) THEN "public"."Order"."lineSubtotal" ELSE 0.0 END) AS "tst2" FROM "public"."Order" GROUP BY "public"."Order"."channel" ORDER BY "public"."Order"."channel" ASC
```

**Version:**
1.0.5

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.