Query pushdown in the SQL API not working for a MySQL query

[hvavhp]

Describe the bug
Cube is unable to process a rather simple query, as follows:

SELECT 
  CASE WHEN 'a' = 'a' THEN test_sales.order_date WHEN 'a' = 'c' THEN (
    DATE_TRUNC('week', test_sales.order_date)
  ) WHEN 'a' = 'b' THEN (
    DATE_TRUNC('month', test_sales.order_date)
  ) ELSE NULL END AS "dynamic_date__0", 
  MEASURE(test_sales.total_sum) AS "test_sales.total_sum__sum__0" 
FROM 
  test_sales 
GROUP BY 
  1;

This query is generated dynamically by BI tool, so it is not possible to make it simpler. It yields the following error when using psql:

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

and more errors on docker log:

2025-03-24 08:33:01,258 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.
2025-03-24 08:33:01,259 TRACE [cubesql::sql::postgres::shim] disabled backtrace
SQL API Error: 5082dab4-f3a8-4737-af04-10087e46c477-span-1 (40ms)
--
{
  "sql": "SELECT \n    CASE WHEN 'a' = 'a' THEN test_sales.order_date WHEN 'a' = 'c' THEN (\n    DATE_TRUNC(\n        'week', test_sales.order_date\n    )\n    ) WHEN 'a' = 'b' THEN (\n    DATE_TRUNC(\n        'month', test_sales.order_date\n    )\n    ) ELSE NULL END AS \"dynamic_date__0\", \n    MEASURE(\n    test_sales.total_sum\n    ) AS \"test_sales.total_sum__sum__0\" \nFROM \n    test_sales \nGROUP BY \n    1;"
}
--
{
  "securityContext": {},
  "appName": "NULL",
  "protocol": "postgres",
  "apiType": "sql"
} 

However, if I switch the function from MEASURE() to SUM() in the above query, then it was able to run, and translated to the following generated SQL query, which I assume the result of which will go through further processing within Cube before returning:

SELECT 
  CONVERT_TZ(
    `test_sales`.order_date, @@session.time_zone, 
    '+00:00'
  ) `test_sales__order_date`, 
  CAST(
    DATE_FORMAT(
      DATE_ADD(
        '1900-01-01', 
        INTERVAL TIMESTAMPDIFF(
          WEEK, 
          '1900-01-01', 
          CONVERT_TZ(
            `test_sales`.order_date, @@session.time_zone, 
            '+00:00'
          )
        ) WEEK
      ), 
      '%Y-%m-%dT00:00:00.000'
    ) AS DATETIME
  ) `test_sales__order_date_week`, 
  CAST(
    DATE_FORMAT(
      CONVERT_TZ(
        `test_sales`.order_date, @@session.time_zone, 
        '+00:00'
      ), 
      '%Y-%m-01T00:00:00.000'
    ) AS DATETIME
  ) `test_sales__order_date_month`, 
  sum(`test_sales`.total_amount) `test_sales__total_sum` 
FROM 
  (
    SELECT 
      '2025-01-01' as order_date, 
      100 as total_amount 
    UNION ALL 
    SELECT 
      '2025-01-02' as order_date, 
      200 as total_amount 
    UNION ALL 
    SELECT 
      '2025-02-03' as order_date, 
      300 as total_amount 
    UNION ALL 
    SELECT 
      '2025-01-04' as order_date, 
      400 as total_amount 
    UNION ALL 
    SELECT 
      '2025-03-05' as order_date, 
      500 as total_amount 
    UNION ALL 
    SELECT 
      '2025-05-06' as order_date, 
      600 as total_amount
  ) AS `test_sales` 
GROUP BY 
  1, 
  2, 
  3 
LIMIT 
  50000

To Reproduce
Here's the YML file:

cubes:
  - name: test_sales
    sql: > 
      SELECT '2025-01-01' as order_date, 100 as total_amount
      UNION ALL
      SELECT '2025-01-02' as order_date, 200 as total_amount
      UNION ALL
      SELECT '2025-02-03' as order_date, 300 as total_amount
      UNION ALL
      SELECT '2025-01-04' as order_date, 400 as total_amount
      UNION ALL
      SELECT '2025-03-05' as order_date, 500 as total_amount
      UNION ALL
      SELECT '2025-05-06' as order_date, 600 as total_amount
      

    
    dimensions:
      - name: order_date
        sql: "order_date"
        type: time

    measures:
      - name: total_sum
        sql: total_amount
        type: sum

Expected behavior
Should not produce error and should process the query successfully.

Version:
v1.2.26
Connection: mysql

Additional context
None

[igorlukanin]

Hi @hvavhp 👋

Thanks for reporting!

First of all, I think the data model should be slightly different since time dimensions are supposed to reference timestamp expressions, not string ones:

cubes:
  - name: test_sales
    sql: > 
      SELECT '2025-01-01' as order_date, 100 as total_amount UNION ALL
      SELECT '2025-01-02' as order_date, 200 as total_amount UNION ALL
      SELECT '2025-02-03' as order_date, 300 as total_amount UNION ALL
      SELECT '2025-01-04' as order_date, 400 as total_amount UNION ALL
      SELECT '2025-03-05' as order_date, 500 as total_amount UNION ALL
      SELECT '2025-05-06' as order_date, 600 as total_amount
    
    dimensions:
      - name: order_date
        sql: "UNIX_TIMESTAMP(order_date)"
        type: time

    measures:
      - name: total_sum
        sql: total_amount
        type: sum

However, I'm still able to reproduce this error for MySQL on v1.2.34:

ERROR:  Error during rewrite: Can't detect Cube query and it may be not supported yet. Please check logs for additional information.
QUERY: SELECT CASE WHEN 'a' = 'a' THEN test_sales.order_date WHEN 'a' = 'c' THEN (DATE_TRUNC('week', test_sales.order_date)) WHEN 'a' = 'b' THEN (DATE_TRUNC('month', test_sales.order_date)) ELSE NULL END AS "dynamic_date__0", MEASURE(test_sales.total_sum) AS "test_sales.total_sum__sum__0" FROM test_sales GROUP BY 1

It doesn't reproduce for Postgres or ClickHouse though, so I suspect the SQL query rewrite engine needs to be updated with some MySQL templates.