Affects Version/s: None
Fix Version/s: Search Services 2.0
Sprint:Team Ninja-King - S&I 29, Team Ninja-King - S&I 30, Team Ninja-King - S&I 31, Team Ninja-King - S&I 32, Team Ninja-King - S&I 33
As a business user
When I connect to Insight Engine from Tableau
Then I want to be able use dates functions in the GROUP BY clause
The Tableau functions we need to support are DAY, MONTH, YEAR, Extract <unit of time> from <date field> (this is the function DAY, MONTH, YEAR translates to) and DATE
SELECT ... FROM ... WHERE ... GROUP BY MONTH(cm_modified)
SELECT ... FROM ... WHERE ... GROUP BY YEAR(cm_modified)
SELECT ... FROM ... WHERE ... GROUP BY Extract YEAR from cm_modified
Handle the date functions "properly" in the 2.0 branch and then backport to other relevant branches
org.alfresco.solr.query.stream.DistributedSqlTimeSeriesIT#timeseries_descDayOrder has been used as a test to explore the behaviour.
The initial exploration of how Calcite handles the transformation revealed to be extremely convoluted and expensive.
So the approached moved to translate the functions calls to the virtual fields currently in place.
It is possible to replace the original SQL string doing the transformation from
MONTH(cm_date) to cm_date_month
YEAR(cm_date) to cm_date_year ,
EXTRACT(<UNIT OF TIME> FROM cm_date) to cm_date_<UNIT OF TIME>
This can be achieved introducing the replacement in an ad hoc transformer following the same approach presented here:
A brutal approach just doing class check is implemented in the draf branch just as Poc
A recommended approach would be to add an ad hoc transformer for dates, with a similar Structure than the SqlInClauseTransformer.
For DAY(cm_date) the solution is different as it is not supported by the Calcite parser, so before parsing it, it must be replaced in here:
N.B. if real fields are introduced instead of the virtual ones, extra care must be taken in the implementation, but it is not of much difference
- no regression in the date sql WHERE related tests
- mentioned date functions to work in the WHERE
- add e2e tests