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

Support for Date Functions in GROUP BY 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:
      20

      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 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
      e.g.
      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

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

      Implementation Recommendations
      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:
      org/alfresco/solr/sql/transformer/SqlInClauseTransformer.java:110
      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:
      org.alfresco.solr.sql.SqlUtil
      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

      Acceptance Criteria

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

        Attachments

          Issue Links

            Structure

              Activity

                People

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

                  Dates

                  • Created:
                    Updated:
                    Resolved:

                    Structure Helper Panel