Uploaded image for project: 'Couchbase Server'
  1. Couchbase Server
  2. MB-19772

Add support for chained LET clauses

    XMLWordPrintable

Details

    • Improvement
    • Resolution: Fixed
    • Major
    • 6.5.0
    • 4.1.1, 4.5.0
    • query
    • None
    • Query-Sprint-01-02-2017

    Description

      When writing a query, LET clauses are an excellent way to make the query easier to read. If the same formula is used multiple places, you can write once and then reference it. However, there is not currently a way to use the result of one clause in the LET statement as input to a second clause in the LET statement.

      Example:

      SELECT NumberDocs.locationKey, MILLIS_TO_STR(createDateBySixHours) AS createDate, items.name as name,
        {
          "sum": SUM(items.`value`),
          "avg": AVG(items.`value`),
          "min": MIN(items.`value`),
          "max": MAX(items.`value`),
          "count": COUNT(items.`value`)
        } as aggregates
      FROM SiteInfo NumberDocs
      UNNEST OBJECT_PAIRS(NumberDocs.item) items
      LET
        createDateByHour = DATE_TRUNC_MILLIS(STR_TO_MILLIS(NumberDocs.createDate, 'hour'),
        createDateBySixHours = DATE_ADD_MILLIS(createDateByHour, -(DATE_PART_MILLIS(createDateByHour, 'hour') % 6), 'hour')
      WHERE NumberDocs.type = 'LocationNumberDataDoc'
      	AND STR_TO_MILLIS(NumberDocs.createDate) >= STR_TO_MILLIS('2016-05-31T00:00:00Z')
      	AND STR_TO_MILLIS(NumberDocs.createDate) < DATE_ADD_MILLIS(STR_TO_MILLIS('2016-05-31T00:00:00Z'), 1, 'day')
      GROUP BY 
        NumberDocs.locationKey,
        createDateBySixHours, 
        items.name
      HAVING COUNT(items.`value`) > 0
      ORDER BY
        NumberDocs.locationKey,
        createDateBySixHours,
        items.name
      

      In the second clause of the LET statement above, DATE_TRUNC_MILLIS(STR_TO_MILLIS(NumberDocs.createDate, 'hour')) is needed twice. So here, we attempt to define this as "createDateByHour" and then reference it in the second clause. This is currently not accepted by the query engine.

      Attachments

        Issue Links

          Activity

            People

              korrigan.clark Korrigan Clark (Inactive)
              btburnett3 Brant Burnett
              Votes:
              0 Vote for this issue
              Watchers:
              9 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                PagerDuty