cube
cube copied to clipboard
Published
20 hours ago •
cube-js
cube-js
SQL API: Incorrect dateRange when filtering on a custom DATE_TRUNC column
Failed SQL The SQL does not fail, but it does return the incorrect results. In the EXPLAIN example below, it should have found a corresponding row, but instead returned no results.
Logical Plan
Same results when using CUBESQL_SQL_PUSH_DOWN = true or false:
explain WITH pre_aggregation AS
(
SELECT
status,
date_trunc('month', created_at) AS created_at__month,
measure(order_count) AS value
FROM orders_cube
WHERE status IN ('processing', 'completed', 'shipped')
GROUP BY 1, 2
)
SELECT *
FROM pre_aggregation
WHERE (status = 'completed') AND (created_at__month = CAST('2019-01-01 00:00:00' AS TIMESTAMP));
plan_type | plan
---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
logical_plan | CubeScan: request={ +
| "measures": [ +
| "orders_cube.order_count" +
| ], +
| "dimensions": [ +
| "orders_cube.status" +
| ], +
| "segments": [], +
| "timeDimensions": [ +
| { +
| "dimension": "orders_cube.created_at", +
| "granularity": "month", +
| "dateRange": [ +
| "2019-01-01T00:00:00.000Z", +
| "2019-01-01T00:00:00.000Z" +
| ] +
| } +
| ], +
| "filters": [ +
| { +
| "member": "orders_cube.status", +
| "operator": "equals", +
| "values": [ +
| "processing", +
| "completed", +
| "shipped" +
| ] +
| }, +
| { +
| "member": "orders_cube.status", +
| "operator": "equals", +
| "values": [ +
| "completed" +
| ] +
| } +
| ] +
| }
physical_plan | CubeScanExecutionPlan, Request: +
| {"measures":["orders_cube.order_count"],"dimensions":["orders_cube.status"],"segments":[],"timeDimensions":[{"dimension":"orders_cube.created_at","granularity":"month","dateRange":["2019-01-01T00:00:00.000Z","2019-01-01T00:00:00.000Z"]}],"filters":[{"member":"orders_cube.status","operator":"equals","values":["processing","completed","shipped"]},{"member":"orders_cube.status","operator":"equals","values":["completed"]}]}+
Version: 0.35.47
Additional context The problem here seems to be in the timeDimensions.dateRange, which is producing an inaccurate range.
