Support for Date Functions in SELECT Clause

Background
As a business user
When I connect to Insight Engine from Tableau
Then I want to be able use dates functions in the SELECT 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
e.g.
SELECT MONTH(cm_modified) FROM ...

Potential Approach
Option 3
Handle the date functions "properly" in the 2.0 branch and then backport to other relevant branches

Implementation Recommendations

1) DATE functions (like MONTH or YEAR) doesn't work with Calcite 1.11.0, that is the version shipped with SOLR 6.6.x.
However, when using Calcite 1.12.0, DATE functions seem to work fine
Identify if Calcite 1.12 properly allow for the syntax MONTH(<date_field>), that is supposed to be automatically translated to the EXTRACT

2) DATA TYPE
Type Solr trieDate is parsed as Java util date in the SolrSchema

3) DATE PARSING IN DIVISION
In the org.alfresco.solr.sql.operation.ArithmeticOperation#getResult the date must be parsed and not the string.
So the division can happen between the date ms and 1DAY ms

4)IMPLEMENT EXTRACT OPERATION
It is necessary to implement an extract operation.
This can be implemented as a mathematical manipulation such as :
1970 + Days/365 ( this is a naive example for extract YEAR)
An alternative could be to add a new class here : org.alfresco.solr.sql.operation
let’s say DateOperations
in here : org/alfresco/solr/sql/SolrRules.java:124
Flag with the unit of time will be translated to a number of days
then it must be implemented an optimal way to extract year, month, day from the epoch
OR

implement directly an extract operation that parse the date and return the appropriate unit of time

OR
Re-Indexing Alternative
If we prefer to change the schema, and that could happen because of WHERE / GROUP BY, this implementation could be simplified + better query time performance but bigger disk space.
These new fields need to be stored.
This would be the recommended approach.

5) Select * and columns
The org/alfresco/solr/sql/SolrEnumerator.java:184 must address the Date type and return a java Date Object

6) Deprecate the transformer as it is unlikely necessary anymore

Note:
See https://git.alfresco.com/search_discovery/insightengine/commit/28f57c7d19af663bdd6278d68bc4537d4d07dc30 for details
The fields will be added to the index in a separate JIRA.

Acceptance Criteria

• no regression in the date sql related tests
• mentioned date functions to work in the SELECT
• add e2e tests related to this work