Error grouping by week

[kikoncuo]

Failed SQL

2024-08-18 09:24:53,866 ERROR [cubesql::compile::query_engine] It may be this query is not supported yet. Please post an issue on GitHub https://github.com/cube-js/cube.js/issues/new?template=sql_api_query_issue.md or ask about it in Slack https://slack.cube.dev. SQL API Error: 36bab2b8-43c3-4e00-8fe1-f181787fdd32-span-1 (32ms)

{
  "sql": "-- Metabase:: userID: 1 queryType: MBQL queryHash: d57d4e2ba4756c2506c850ae2fb00f102b77b39935458234df2c2736c8f7e895\nSELECT (DATE_TRUNC('week', (\"public\".\"Inventory\".\"date\" + INTERVAL '1 day')) + INTERVAL '-1 day') AS \"date\", COUNT(*) AS \"count\" FROM \"public\".\"Inventory\" GROUP BY (DATE_TRUNC('week', (\"public\".\"Inventory\".\"date\" + INTERVAL '1 day')) + INTERVAL '-1 day') ORDER BY \"count\" ASC, (DATE_TRUNC('week', (\"public\".\"Inventory\".\"date\" + INTERVAL '1 day')) + INTERVAL '-1 day') ASC"
}
{
  "securityContext": {},
  "appName": "Metabase v0.1 [1495f22d-0198-4602-a4a2-5a1a40f31254]",
  "protocol": "postgres",
  "apiType": "sql"
} 

Logical Plan

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

"SELECT (DATE_TRUNC('week', (\"public\".\"Inventory\".\"date\" + INTERVAL '1 day')) + INTERVAL '-1 day') AS \"date\", COUNT(*) AS \"count\" FROM \"public\".\"Inventory\" GROUP BY (DATE_TRUNC('week', (\"public\".\"Inventory\".\"date\" + INTERVAL '1 day')) + INTERVAL '-1 day') ORDER BY \"count\" ASC, (DATE_TRUNC('week', (\"public\".\"Inventory\".\"date\" + INTERVAL '1 day')) + INTERVAL '-1 day') ASC"

{
  "protocol": "postgres",
  "apiType": "sql",
  "sanitizedQuery": "SELECT (DATE_TRUNC('[REPLACED]', (\"public\".\"Inventory\".\"date\" + INTERVAL '1 day')) + INTERVAL '-1 day') AS \"date\", COUNT(*) AS \"count\" FROM \"public\".\"Inventory\" GROUP BY (DATE_TRUNC('[REPLACED]', (\"public\".\"Inventory\".\"date\" + INTERVAL '1 day')) + INTERVAL '-1 day') ORDER BY \"count\" ASC, (DATE_TRUNC('week', (\"public\".\"Inventory\".\"date\" + INTERVAL '1 day')) + INTERVAL '-1 day') ASC",
  "appName": "Metabase v0.1 [1495f22d-0198-4602-a4a2-5a1a40f31254]"
} 
Error during rewrite: Can't detect Cube query and it may be not supported yet. Please check logs for additional information.

Version: v0.35.69

Additional context

The problem seems to pop up whenever I try to use date grouping by week in metabase, the query changes drastically only for the week grouping and I get the error.

IE: this query works:

SELECT
  DATE_TRUNC('month', "public"."Inventory"."date") AS "date",
  COUNT(*) AS "count"
FROM
  "public"."Inventory"
GROUP BY
  DATE_TRUNC('month', "public"."Inventory"."date")
ORDER BY
  DATE_TRUNC('month', "public"."Inventory"."date") ASC

And this one fails:

SELECT
  (
    DATE_TRUNC(
      'week',
      ("public"."Inventory"."date" + INTERVAL '1 day')
    ) + INTERVAL '-1 day'
  ) AS "date",
  COUNT(*) AS "count"
FROM
  "public"."Inventory"
GROUP BY
  (
    DATE_TRUNC(
      'week',
      ("public"."Inventory"."date" + INTERVAL '1 day')
    ) + INTERVAL '-1 day'
  )
ORDER BY
  (
    DATE_TRUNC(
      'week',
      ("public"."Inventory"."date" + INTERVAL '1 day')
    ) + INTERVAL '-1 day'
  ) ASC

[kikoncuo]

Might be a duplicate of https://github.com/cube-js/cube/issues/6332

[igorlukanin]

Hi @kikoncuo 👋

Thanks for posting this. Do you have query pushdown enabled?

[kikoncuo]

@igorlukanin When enabled the connection to Metabase broke and it couldn't find any tables. Also afaik pre-aggregations won't work either right?

I can investigate more in the coming days and ping an update here, my temp fix was to set the start of the week to Monday which simplified the resulting query and stopped the error.

[igorlukanin]

@kikoncuo If you can test this once again with the latest version (v1.1.x), it would be fantastic.