Uploaded image for project: 'Search and Discovery'
  1. Search and Discovery
  2. SEARCH-2151

Support for Date Functions in SELECT Clause

    Details

    • Type: Story
    • Status: Done
    • Resolution: Done
    • Affects Version/s: None
    • Fix Version/s: Search Services 2.0
    • Labels:
      None
    • 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
    • Release Train:
      Southall
    • Delivery Team:
      Search
    • Story Points:
      13

      Description

      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

        Attachments

          Issue Links

            Structure

              Activity

                People

                • Assignee:
                  Unassigned
                  Reporter:
                  abenedetti Alessandro Benedetti
                • Votes:
                  0 Vote for this issue
                  Watchers:
                  5 Start watching this issue

                  Dates

                  • Created:
                    Updated:
                    Resolved:

                    Structure Helper Panel