Query with date field filter using cast date and `BETWEEN` in the `WHERE` clause

[rmzoni]

Failed SQL Search for Failed SQL log message:

data-product-cube-1  | Cube SQL Error: undefined
data-product-cube-1  | --
data-product-cube-1  | "SELECT * FROM sessions_count_view WHERE CAST(\"public\".\"sessions_count_view\".\"start_scheduled_at\" AS DATE) BETWEEN DATE '2023-05-01' AND DATE '2023-07-31'"
data-product-cube-1  | --
data-product-cube-1  | {
data-product-cube-1  |   "apiType": "sql",
data-product-cube-1  |   "appName": "NULL",
data-product-cube-1  |   "sanitizedQuery": "SELECT * FROM sessions_count_view WHERE CAST(\"public\".\"sessions_count_view\".\"start_scheduled_at\" AS DATE) BETWEEN DATE '2023-05-01' AND DATE '2023-07-31'",
data-product-cube-1  |   "protocol": "postgres"
data-product-cube-1  | }
data-product-cube-1  | Error during rewrite: Can't detect Cube query and it may be not supported yet. Please check logs for additional information.

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

Error during rewrite: Can't detect Cube query and it may be not supported yet. Please check logs for additional information.

Version: v0.33.42

Additional context I am using Metabase's SQL native question with a field filter of date type. Metabase generates the following query to run on cube.dev's PostgreSQL database:

SELECT * FROM sessions_count_view WHERE CAST("public"."sessions_count_view"."start_scheduled_at" AS date) BETWEEN date '2023-05-01' AND date '2023-07-31';

However, when attempting to execute this query, I encounter the following error in the logs:

Cube SQL Error: undefined
"SELECT * FROM sessions_count_view WHERE CAST(\"public\".\"sessions_count_view\".\"start_scheduled_at\" AS DATE) BETWEEN DATE '2023-05-01' AND DATE '2023-07-31'"
{
  "apiType": "sql",
  "appName": "NULL",
  "sanitizedQuery": "SELECT * FROM sessions_count_view WHERE CAST(\"public\".\"sessions_count_view\".\"start_scheduled_at\" AS DATE) BETWEEN DATE '2023-05-01' AND DATE '2023-07-31'",
  "protocol": "postgres"
}
Error during rewrite: Can't detect Cube query and it may be not supported yet. Please check logs for additional information.

It seems that the query generated by Metabase is encountering an issue when executing on cube.dev, resulting in the inability to detect the Cube query and possibly lacking support for the specified operation. This could be related to the usage of CAST and the BETWEEN clause in the query. I would appreciate assistance in resolving this issue or any insights into potential workarounds. Thank you!

[paveltiunov]

@rmzoni This one is interesting. It might be you want to try out to switch the type of this column to the timestamp in metabase. We should support dates as well though.

[rmzoni]

This column is already a timestamp:

[jbzcooper]

FWIW I'm getting the same error but only when joining two cubes (tables) via SQL API. I can query each table individually, but cannot query and join both using either cross join or the documented ... on __cubeJoinField ... approach.

[rmzoni]

Any update about this issue?

[augustoc100]

I'm getting the same error.

For this query

SELECT occurred_at, clinician_id, value
FROM light_processes_measurements_view
WHERE DATE(occurred_at) BETWEEN DATE('2023-12-01') AND DATE('2023-12-20')

I'm getting this error log

Cube SQL Error: undefined
 --
"SELECT occurred_at, clinician_id, value FROM light_processes_measurements_view WHERE DATE(occurred_at) BETWEEN DATE('2023-12-01') AND DATE('2023-12-20')"
 --
 {
   "appName": "NULL",
  "apiType": "sql",
   "sanitizedQuery": "SELECT occurred_at, clinician_id, value FROM light_processes_measurements_view WHERE DATE(occurred_at) BETWEEN DATE('[REPLACED]') AND DATE('[REPLACED]')",
   "protocol": "postgres"
 }
 Error during rewrite: Can't detect Cube query and it may be not supported yet. Please check logs for additional information.

[rmzoni]

@paveltiunov I made some test here and we found out that the problem is related to between in the cube:

WHERE DATE(occurred_at) BETWEEN DATE('2023-12-01') AND DATE('2023-12-20')

When we switch to use greater than and lower than, it works like fine:

WHERE DATE(occurred_at) >= DATE('2023-12-01') AND DATE(occurred_at) <= DATE('2023-12-20')

It sounds like a bug in cube SQL API when using between.

[wasd171]

BETWEEN definitely seems to be problematic, we use Grafana for data visualisation and by default it uses BETWEEN for interval filter. Changing it to >= + <= fixes the issue

[rmzoni]

BETWEEN definitely seems to be problematic, we use Grafana for data visualisation and by default it uses BETWEEN for interval filter. Changing it to >= + <= fixes the issue

The problem for me is that this SQL are generated by the metabase. We do not have control on this generated SQL

[igorlukanin]

I have just checked and was still able to reproduce this on Cube v0.36.7 with query pushdown turned on with the following data model:

cubes:
  - name: test
    sql: > 
      SELECT '2024-01-15T00:00:00.000Z'::TIMESTAMP AS timestamp, 1 AS value UNION ALL
      SELECT '2024-02-15T00:00:00.000Z'::TIMESTAMP AS timestamp, 2 AS value UNION ALL
      SELECT '2024-03-15T00:00:00.000Z'::TIMESTAMP AS timestamp, 3 AS value
 
    dimensions:
      - name: value
        sql: value
        type: number
        primary_key: true
        public: true

      - name: occurred_at
        sql: timestamp
        type: time

Queries: