Date filter and time dimension not working in Oracle #4373
Description
Describe the bug
I am using Cube.js with Docker in dev mode and testing it with our Oracle 12c database.
In Cube.js Playground, I can filter data with any dimension but the dimension of type time.
If I try filtering my data on time either through Time dimension (in the TIME section of the Playground's menu) or by adding date filter (in the FILTER section of the Playground's menu), I get two different errors depending on the case.
-
Time dimension OR date filter with operators in date range, not in date range, before date, after date:
ORA-01830: date format picture ends before converting entire input string -
Date filter with operators equals or not equals:
ORA-01861: literal does not match format string
To Reproduce
Steps to reproduce the behavior:
- Connect Docker instance of Cube to Oracle DB (version 12c)
- Create a simple schema for your Oracle table with one dimension of type time defined on a column of type Date
- Open Cube.js Playground
- In Playground menu, add measures and dimensions of choice, and either Time dimension for a time period and granularity of choice or a date filter of choice
- Run query and see one of the errors above
Expected behavior
No error when filtering data by date.
Minimally reproducible Cube Schema
I did't understand this part of the issue instruction so I am just copying an example of my data schema:
cube(`CubeTest`, {
sql: `SELECT * FROM some_table`,
measures: {
count: {
type: `count`,
drillMembers: []
},
},
dimensions: {
parameter: {
sql: `parameter`,
type: `number`,
},
datum: {
sql: `datum`,
type: `time`
},
station: {
sql: `station`,
type: `string`
},
},
dataSource: `default`
});
Version:
Cube.js (0.29.34), ran in a Docker container from an image built from my custom dev.Dockerfile (in order to solve this open issue)
Additional context
I found out the first error occurs when the date formats of the two arguments provided to Oracle's to_date function don't match. The first argument is the date string you want to transform to Date type and the second argument is the date format used (better explained here).
In Cube.js, that happens inside OracleQuery.js adapter in the DateTimeCast method which returns:
to_date(:"${value}", 'YYYY-MM-DD"T"HH24:MI:SS"Z"');
Cube.js transforms all dates into timestamps with fractional seconds (FF3). So Cube.js constructs SQL query for Oracle this way:
...
WHERE ("cube_test".datum >= to_date(:"'2022-03-01T00:00:00.000Z'", 'YYYY-MM-DD"T"HH24:MI:SS"Z"') AND "cube_test".datum <= to_date(:"'2022-03-31T23:59:59.999Z'", 'YYYY-MM-DD"T"HH24:MI:SS"Z"')) GROUP BY ...
As you can see, the second argument in the to_date function does not include the fractional seconds, therefore it does not match the format of the first argument. However, this function does not allow using fractional seconds anyway, so the shortest way to fix this bug was changing the code here: replacing to_date function with to_timestamp function and adding fractional seconds to date format in the second argument appropriately. So theDateTimeCast method now returns:
to_timestamp(:"${value}", 'YYYY-MM-DD"T"HH24:MI:SS.FF3"Z"')
After building the Cube.js image with my customized dev.Dockerfile to make sure this change took place, this actually fixed the issue and I can now filter my data on time dimension using the operators mentioned above.
The second error happens because in the case of using operators equals or not equals, Cube.js constructs the Oracle SQL query this way:
WHERE ("cube_test".datum = :"'2021-10-15'") GROUP BY ...
Oracle does not like this. I didn't dive into this one though, but it seems that operators_equals_ and_not equals_ do not use the same logic as other time operators. The date string is inserted into SQL query directly and the to_timestamp function is not used.
Also I should mention that I double tested my requests to Cube.js via Postman as well (getting the same errors) and I queried my Oracle database through DBeaver using SQL statements generated and logged by Cube.js (getting the same errors).