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

Support for Date Functions in ORDER 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:
      8

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

      Potential Approach
      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:
      org/alfresco/solr/sql/SolrTable.java:1004
      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.

      Acceptance Criteria

      • no regression in the date sql ORDER BY related tests
      • mentioned date functions to work in the ORDER BY
      • 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