Failing query on CB 5.5.2 using Group By with LETTING


#1

The query below used to be working fine with CB 4.6.3 but its failing since we upgraded CB to 5.5.2 . Wondering what changed exactly so I can monitor rest of the application.

SELECT 
  resource as resourceId, 
  series, 
  IFMISSINGORNULL(seriesId, canonicalId, resourceId) as folderId,
  canonical as canonicalId,
  startTime, 
  COUNT as count
FROM `dvr-cloud-recording-store`
WHERE accountToken = "iRx_89STqADUfKB4edPBXTl6oyjFduxksq6OM8SMq3Y"
GROUP BY IFMISSINGORNULL(seriesId, canonicalId, resourceId)
LETTING 
  startTime = MAX(recordingStartTime), resource = resourceId,
  series = seriesId IS VALUED,
  canonical = canonicalId
ORDER BY startTime DESC
LIMIT 1000 OFFSET 0

#2

You can only project the expression based on GROUP expression and Aggregate expression. Any other is not possible. There is no way project those. It has been fixed in CB 5.x

I am not sure what is COUNT?

As you have doing MAX only, you have following two options.

SELECT
  maxdata[1].resourceId,
  maxdata[1].seriesId IS VALUED AS series,
  IFMISSINGORNULL(seriesId, canonicalId, resourceId) AS folderId,
  maxdata[1].canonicalId,
  maxdata[1].COUNT AS count,
  maxdata[0] AS startTime
FROM `dvr-cloud-recording-store`
WHERE accountToken = "iRx_89STqADUfKB4edPBXTl6oyjFduxksq6OM8SMq3Y"
GROUP BY IFMISSINGORNULL(seriesId, canonicalId, resourceId)
LETTING
  maxdata = MAX([recordingStartTime,{resourceId, canonicalId, seriesId, COUNT}])
ORDER BY startTime DESC
LIMIT 1000 OFFSET 0;

OR

SELECT
  d1.resourceId,
  d1.seriesId IS VALUED AS series,
  IFMISSINGORNULL(d1.seriesId, d1.canonicalId, d1.resourceId) AS folderId,
  d1.canonicalId,
  d1.recordingStartTime AS startTime,
  d1.COUNT AS count
FROM  (SELECT RAW MAX([recordingStartTime,META().id])[1] FROM
       `dvr-cloud-recording-store`
        WHERE accountToken = "iRx_89STqADUfKB4edPBXTl6oyjFduxksq6OM8SMq3Y"
        GROUP BY IFMISSINGORNULL(seriesId, canonicalId, resourceId)) AS d
JOIN `dvr-cloud-recording-store` AS d1 ON KEYS d
ORDER BY startTime DESC
LIMIT 1000 OFFSET 0