Handle (time_dimension::timestamp + interval '0 minute') in group by expr as issued by DOMO BI tool

[droidraja]

The following query is a similar failing query generated by DOMO BI Tool

Failed SQL

select measure(total_amount),updated_at::timestamp + interval '0 minute' from orders group by 2;

Logical Plan

Projection: #measure(orders.total_amount), #CAST(orders.updated_at AS Timestamp(Nanosecond, None)) + IntervalDayTime("0")
  Aggregate: groupBy=[[CAST(#orders.updated_at AS Timestamp(Nanosecond, None)) + IntervalDayTime("0")]], aggr=[[measure(#orders.total_amount)]]

Version: 1.1.2

Additional context Listing down other time and granularity failing queries issued by DOMO, will be present in both select and group by expr

hour:
DATE_TRUNC('HOUR', (\"T1\".\"period\"::TIMESTAMP + INTERVAL '0 MINUTE')::TIMESTAMP::TIMESTAMP) AS \"CalendarHour\", 

Week:
CONCAT('Week-', CAST(EXTRACT(WEEK FROM CAST(DATE(CAST((CAST(\"T1\".\"period\" AS TIMESTAMP) + INTERVAL '0 MINUTE') AS TIMESTAMP)) AS TIMESTAMP)) AS INT), ' ', CAST(EXTRACT(YEAR FROM CAST(DATE(CAST((CAST(\"T1\".\"period\" AS TIMESTAMP) + INTERVAL '0 MINUTE') AS TIMESTAMP)) AS TIMESTAMP)) AS INT)) AS \"CalendarWeek\", 

Date:
DATE_TRUNC('HOUR', (\"T1\".\"period\"::TIMESTAMP + INTERVAL '0 MINUTE')::TIMESTAMP::TIMESTAMP)

Month:
TO_CHAR(CAST(DATE(CAST((CAST(\"T1\".\"period\" AS TIMESTAMP) + INTERVAL '0 MINUTE') AS TIMESTAMP)) AS TIMESTAMP), 'YYYY-Mon') AS \"CalendarMonth\",

Need to add queries for year and check anything else which fails

Testing this further found out that the query without the group by expression and removing the measure function works

select total_amount,updated_at::timestamp + interval '0 minute' from orders;

Which gets translated to a cubescan which simplifies the projection into just the column updated_at

CubeScan: request={
  "measures": [
    "orders.total_amount"
  ],
  "dimensions": [
    "orders.updated_at"
  ],
  "segments": [],
  "order": [],
  "ungrouped": true
}

Minimal cube model file

cubes:
  - name: orders
    sql: >
      SELECT 1 AS id, 100 AS amount, 19.99 AS price, 'new' status, NOW() as updated_at
      UNION ALL
      SELECT 2 AS id, 200 AS amount, 24.50 AS price, 'new' status, NOW() as updated_at
      UNION ALL
      SELECT 3 AS id, 300 AS amount, 15.75 AS price, 'processed' status, NOW() as updated_at
      UNION ALL
      SELECT 4 AS id, 500 AS amount, 32.25 AS price, 'processed' status, NOW() as updated_at
      UNION ALL
      SELECT 5 AS id, 600 AS amount, 45.99 AS price, 'shipped' status, NOW() as updated_at

    measures:
      - name: count
        type: count
      - name: total_amount
        sql: amount
        type: sum
    dimensions:
      - name: price
        sql: price
        type: number
      - name: id
        sql: id
        type: number
        primary_key: true
      - name: status
        sql: status
        type: string
      - name: updated_at
        sql: updated_at
        type: time

[paveltiunov]

@droidraja This query works in latest version. Could you please try?

[droidraja]

@paveltiunov

Yes seems like it this query succeeds

select measure(total_amount),date(updated_at::timestamp + interval '0 minute') from orders group by 2;

Will check for other queries through DOMO and update the issue.