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 ORDER 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 MONTH(cm_modified) FROM ... WHERE ... GROUP BY MONTH(cm_modified) ORDER BY MONTH(cm_modified)
SELECT YEAR(cm_modified) FROM ... WHERE ... ORDER BY YEAR(cm_modified)
There are two possible scenarios that involve two different areas of change:
1) ORDER BY following the GROUP BY
Here we sort grouped records, they are facet buckets from the Solr perspective.
This is managed through comparing tuple column values. The sort happens after the results are retrieved by Solr, specifically in one of the processing streams of aggregations:
org.apache.solr.client.solrj.io.comp.FieldComparator through a org.apache.solr.client.solrj.io.stream.RankStream wrapping the existent stream in the SolrTable class:
Enabling Date functions in this scenario is going to be a Transformer implementation that translate the ORDER BY to the related virtual field (cm_date_day, cm_date_month ect)
2) ORDER BY on its own
Assuming we have added index time support fot the part of date fields, a translation needs to happen to transform the ORDER BY to such fields from the date functions.
This is recommended to happen in the Transformer module ( org.alfresco.solr.sql.transformer )
The integration should be seamless , but it is recommended to take good care of this area: org/alfresco/solr/sql/SolrTable.java:560
To assess the proper behaviour.
- no regression in the date sql ORDER BY related tests
- mentioned date functions to work in the ORDER BY
- add e2e tests