runningTotal on Athena with granularity not working

[bluestarfish-t1d]

Describe the bug Running a query with a runningTotal measure and a time dimension with granularity set on Athena results in an error:

Error: SYNTAX_ERROR: line 1:3737: '>=' cannot be applied to timestamp, varchar(23)

This error occurs in the generated SQL's ON clause (LEFT JOIN to the generated time series), where the date range start date is inserted as a varchar and not converted to a timestamp. This results in a comparison between a timestamp and a varchar.

To Reproduce Steps to reproduce the behavior:

1. Create a runningTotal measure on amount in a cube with Athena as the database.
2. In the cubeJS playground add that measure and a time dimension with granularity of day.
3. Run the query and see the error: "Error: SYNTAX_ERROR: line 1:3737: '>=' cannot be applied to timestamp, varchar(23)"
4. View the generated SQL and verify that the ON clause (LEFT JOIN to the generated time series) is a comparison between a timestamp and a varchar.
5. Copy the generated SQL and convert the varchar to a timestamp and run in a SQL editor. The query should run and return a result.

Expected behavior The generated SQL should convert the date range start date when it is inserted into the ON clause (LEFT JOIN to the generated time series) to timestamp, same as it is done in other places where the date range start date and end date is inserted (example: from_iso8601_timestamp('2022-07-25T00:00:00.000')).

Screenshots Error: ON clause (LEFT JOIN to the generated time series):

Minimally reproducible Cube Schema The runningTotal measure:

  measures: {
    runningTotal: {
      sql: `${CUBE}.amount`,
      type: `runningTotal`,
      format: `currency`,
    },
  },

Version: 0.30.42

[ivan-vdovin]

Hi @bluestarfish-t1d and thanks for the report! Could you please sign up with free Cloud deployment where it can be reproduced so we can take a closer look there? It will help us get more details much faster

[bluestarfish-t1d]

Hi @ivan-vdovin Thanks for the reply. I do have access to a free cube cloud account, but cannot do a setup with Athena as the data source since Athena does not fall on AWS free tier and my production Athena runs in a private VPC.

The problem seems to be with the Athena driver, when the cube call (json) is translated to Athena SQL and the call refers to a cube measure of type runningTotal with a date range and granularity set.

{ "measures": [ "InTransactionsA.runningTotal" ], "timeDimensions": [ { "dimension": "InTransactionsA.s2sBalanceTimestamp", "granularity": "day", "dateRange": "Last week" } ] }

The error in the resulting generated SQL occurs in the ON clause (LEFT JOIN to the generated time series), where the date range start date is inserted as a varchar and not converted to type timestamp. This results in a comparison between a type timestamp and a type varchar, which Athena can not handle.

[paveltiunov]

@bluestarfish-t1d We're going to deprecate runningTotal soon. Please use rollingWindow instead: https://cube.dev/docs/schema/reference/measures#rolling-window.