Aggregate Problems On 4.5 Beta (Works on 4.1.1)

btburnett3 · May 27, 2016, 2:42pm

I have a complicated query which is working great against 4.1.1:

SELECT StringDocs.locationKey, MILLIS_TO_STR(createDateBySixHours) AS createDate, items.name as name,
  {
    "first": FIRST p FOR p IN ARRAY_AGG({"createDate": STR_TO_UTC(StringDocs.createDate), "value": items.`value`}) END,
    "last": FIRST p FOR p IN ARRAY_REVERSE(ARRAY_AGG({"createDate": STR_TO_UTC(StringDocs.createDate), "value": items.`value`})) END
  } as `values`
FROM SiteInfo StringDocs
UNNEST ARRAY p FOR p IN OBJECT_PAIRS(StringDocs.item) WHEN p.`value` IS NOT NULL END items
LET 
  createDateBySixHours = DATE_ADD_MILLIS(DATE_TRUNC_MILLIS(STR_TO_MILLIS(StringDocs.createDate), 'hour'), -(DATE_PART_MILLIS(DATE_TRUNC_MILLIS(STR_TO_MILLIS(StringDocs.createDate), 'hour'), 'hour') % 6), 'hour')
WHERE StringDocs.type = 'LocationStringDataDoc'
	AND STR_TO_MILLIS(StringDocs.createDate) >= STR_TO_MILLIS('2016-05-01T00:00:00Z')
	AND STR_TO_MILLIS(StringDocs.createDate) < DATE_ADD_MILLIS(STR_TO_MILLIS('2016-05-31T00:00:00Z'), 1, 'day')
GROUP BY 
  StringDocs.locationKey,
  createDateBySixHours, 
  items.name
HAVING createDateBySixHours IS NOT NULL
ORDER BY
  StringDocs.locationKey,
  createDateBySixHours,
  items.name

I’m trying to get the first and last values from the group nested in a sub-document in query results. However, when I run it against 4.5 Beta, I’m getting the following error:

Error: Expression must be a group key or aggregate: {“first”: first p for p in array_agg({“createDate”: str_to_utc((StringDocs.createDate)), “value”: (items.value)}) end, “last”: first p for p in array_reverse(array_agg({“createDate”: str_to_utc((StringDocs.createDate)), “value”: (items.value)})
) end} as values

Looks like a regression to me, but I thought I’d float it to you guys to look at.

Thanks,
Brant

btburnett3 · May 27, 2016, 2:43pm

Also, here are some example documents I queried out of the bucket:

[
  {
    "SiteInfo": {
      "createDate": "2016-05-16T13:42:16-04:00",
      "item": {
        "advantageVersion": "16.4.10.101",
        "commCertificatesAreValid": "True",
        "commCertificatesExpiration": "2017-02-03 14:36:29Z",
        "commLastCorporateUpdate": null,
        "commLastStageUpdate": null,
        "commLastWebStoreUpdate": "2016-05-16 17:41:06Z",
        "dbBackupPath1": null,
        "dbBackupPath2": null,
        "dbBackupPath3": null,
        "dbBackupTime": null,
        "dbDataDrive": "C:",
        "dbLastBackup": null,
        "dbSqlVersion": "Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) \n\tDec 28 2012 20:23:12 \n\tCopyright (c) Microsoft Corporation\n\tExpress Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)\n",
        "eodImbalanceDate": "2016-05-15",
        "serverCpuType": "Intel64 Family 6 Model 45 Stepping 7",
        "serverNetVersion": "4.0.30319",
        "serverOSDrive": "C:\\",
        "serverOs": "Microsoft Windows NT 6.2.9200.0"
      },
      "keyId": "LocationStringDataDoc:3202:2016-05-16T13:42:16.0000000-04:00",
      "locationKey": 3202,
      "type": "LocationStringDataDoc"
    }
  },
  {
    "SiteInfo": {
      "createDate": "2016-05-16T13:42:18-04:00",
      "item": {
        "advantageVersion": "16.4.10.101",
        "commCertificatesAreValid": "True",
        "commCertificatesExpiration": "2017-02-03 14:36:29Z",
        "commLastCorporateUpdate": null,
        "commLastStageUpdate": null,
        "commLastWebStoreUpdate": "2016-05-16 17:41:06Z",
        "dbBackupPath1": null,
        "dbBackupPath2": null,
        "dbBackupPath3": null,
        "dbBackupTime": null,
        "dbDataDrive": "C:",
        "dbLastBackup": null,
        "dbSqlVersion": "Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) \n\tDec 28 2012 20:23:12 \n\tCopyright (c) Microsoft Corporation\n\tExpress Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)\n",
        "eodImbalanceDate": "2016-05-15",
        "serverCpuType": "Intel64 Family 6 Model 45 Stepping 7",
        "serverNetVersion": "4.0.30319",
        "serverOSDrive": "C:\\",
        "serverOs": "Microsoft Windows NT 6.2.9200.0"
      },
      "keyId": "LocationStringDataDoc:3202:2016-05-16T13:42:18.0000000-04:00",
      "locationKey": 3202,
      "type": "LocationStringDataDoc"
    }
  },
  {
    "SiteInfo": {
      "createDate": "2016-05-16T13:57:19-04:00",
      "item": {
        "advantageVersion": "16.4.10.101",
        "commCertificatesAreValid": "True",
        "commCertificatesExpiration": "2017-02-03 14:36:29Z",
        "commLastCorporateUpdate": null,
        "commLastStageUpdate": null,
        "commLastWebStoreUpdate": "2016-05-16 17:55:34Z",
        "dbBackupPath1": null,
        "dbBackupPath2": null,
        "dbBackupPath3": null,
        "dbBackupTime": null,
        "dbDataDrive": "C:",
        "dbLastBackup": null,
        "dbSqlVersion": "Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) \n\tDec 28 2012 20:23:12 \n\tCopyright (c) Microsoft Corporation\n\tExpress Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)\n",
        "eodImbalanceDate": "2016-05-15",
        "serverCpuType": "Intel64 Family 6 Model 45 Stepping 7",
        "serverNetVersion": "4.0.30319",
        "serverOSDrive": "C:\\",
        "serverOs": "Microsoft Windows NT 6.2.9200.0"
      },
      "keyId": "LocationStringDataDoc:3202:2016-05-16T13:57:19.0000000-04:00",
      "locationKey": 3202,
      "type": "LocationStringDataDoc"
    }
  },
  {
    "SiteInfo": {
      "createDate": "2016-05-16T14:12:21-04:00",
      "item": {
        "advantageVersion": "16.4.10.101",
        "commCertificatesAreValid": "True",
        "commCertificatesExpiration": "2017-02-03 14:36:29Z",
        "commLastCorporateUpdate": null,
        "commLastStageUpdate": null,
        "commLastWebStoreUpdate": "2016-05-16 18:12:10Z",
        "dbBackupPath1": null,
        "dbBackupPath2": null,
        "dbBackupPath3": null,
        "dbBackupTime": null,
        "dbDataDrive": "C:",
        "dbLastBackup": null,
        "dbSqlVersion": "Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) \n\tDec 28 2012 20:23:12 \n\tCopyright (c) Microsoft Corporation\n\tExpress Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)\n",
        "eodImbalanceDate": "2016-05-15",
        "serverCpuType": "Intel64 Family 6 Model 45 Stepping 7",
        "serverNetVersion": "4.0.30319",
        "serverOSDrive": "C:\\",
        "serverOs": "Microsoft Windows NT 6.2.9200.0"
      },
      "keyId": "LocationStringDataDoc:3202:2016-05-16T14:12:21.0000000-04:00",
      "locationKey": 3202,
      "type": "LocationStringDataDoc"
    }
  },
  {
    "SiteInfo": {
      "createDate": "2016-05-16T14:27:23-04:00",
      "item": {
        "advantageVersion": "16.4.10.101",
        "commCertificatesAreValid": "True",
        "commCertificatesExpiration": "2017-02-03 14:36:29Z",
        "commLastCorporateUpdate": null,
        "commLastStageUpdate": null,
        "commLastWebStoreUpdate": "2016-05-16 18:26:34Z",
        "dbBackupPath1": null,
        "dbBackupPath2": null,
        "dbBackupPath3": null,
        "dbBackupTime": null,
        "dbDataDrive": "C:",
        "dbLastBackup": null,
        "dbSqlVersion": "Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) \n\tDec 28 2012 20:23:12 \n\tCopyright (c) Microsoft Corporation\n\tExpress Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)\n",
        "eodImbalanceDate": "2016-05-15",
        "serverCpuType": "Intel64 Family 6 Model 45 Stepping 7",
        "serverNetVersion": "4.0.30319",
        "serverOSDrive": "C:\\",
        "serverOs": "Microsoft Windows NT 6.2.9200.0"
      },
      "keyId": "LocationStringDataDoc:3202:2016-05-16T14:27:23.0000000-04:00",
      "locationKey": 3202,
      "type": "LocationStringDataDoc"
    }
  },
  {
    "SiteInfo": {
      "createDate": "2016-05-16T14:32:02-04:00",
      "item": {
        "advantageVersion": "16.4.10.103",
        "commCertificatesAreValid": "True",
        "commCertificatesExpiration": "2017-02-03 14:36:29Z",
        "commLastCorporateUpdate": null,
        "commLastStageUpdate": null,
        "commLastWebStoreUpdate": "2016-05-16 18:31:40Z",
        "dbBackupPath1": null,
        "dbBackupPath2": null,
        "dbBackupPath3": null,
        "dbBackupTime": null,
        "dbDataDrive": "C:",
        "dbLastBackup": null,
        "dbSqlVersion": "Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) \n\tDec 28 2012 20:23:12 \n\tCopyright (c) Microsoft Corporation\n\tExpress Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)\n",
        "eodImbalanceDate": "2016-05-15",
        "serverCpuType": "Intel64 Family 6 Model 45 Stepping 7",
        "serverNetVersion": "4.0.30319",
        "serverOSDrive": "C:\\",
        "serverOs": "Microsoft Windows NT 6.2.9200.0"
      },
      "keyId": "LocationStringDataDoc:3202:2016-05-16T14:32:02.0000000-04:00",
      "locationKey": 3202,
      "type": "LocationStringDataDoc"
    }
  },
  {
    "SiteInfo": {
      "createDate": "2016-05-16T14:47:04-04:00",
      "item": {
        "advantageVersion": "16.4.10.103",
        "commCertificatesAreValid": "True",
        "commCertificatesExpiration": "2017-02-03 14:36:29Z",
        "commLastCorporateUpdate": null,
        "commLastStageUpdate": null,
        "commLastWebStoreUpdate": "2016-05-16 18:45:40Z",
        "dbBackupPath1": null,
        "dbBackupPath2": null,
        "dbBackupPath3": null,
        "dbBackupTime": null,
        "dbDataDrive": "C:",
        "dbLastBackup": null,
        "dbSqlVersion": "Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) \n\tDec 28 2012 20:23:12 \n\tCopyright (c) Microsoft Corporation\n\tExpress Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)\n",
        "eodImbalanceDate": "2016-05-15",
        "serverCpuType": "Intel64 Family 6 Model 45 Stepping 7",
        "serverNetVersion": "4.0.30319",
        "serverOSDrive": "C:\\",
        "serverOs": "Microsoft Windows NT 6.2.9200.0"
      },
      "keyId": "LocationStringDataDoc:3202:2016-05-16T14:47:04.0000000-04:00",
      "locationKey": 3202,
      "type": "LocationStringDataDoc"
    }
  },
  {
    "SiteInfo": {
      "createDate": "2016-05-16T15:02:05-04:00",
      "item": {
        "advantageVersion": "16.4.10.103",
        "commCertificatesAreValid": "True",
        "commCertificatesExpiration": "2017-02-03 14:36:29Z",
        "commLastCorporateUpdate": null,
        "commLastStageUpdate": null,
        "commLastWebStoreUpdate": "2016-05-16 19:00:36Z",
        "dbBackupPath1": null,
        "dbBackupPath2": null,
        "dbBackupPath3": null,
        "dbBackupTime": null,
        "dbDataDrive": "C:",
        "dbLastBackup": null,
        "dbSqlVersion": "Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) \n\tDec 28 2012 20:23:12 \n\tCopyright (c) Microsoft Corporation\n\tExpress Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)\n",
        "eodImbalanceDate": "2016-05-15",
        "serverCpuType": "Intel64 Family 6 Model 45 Stepping 7",
        "serverNetVersion": "4.0.30319",
        "serverOSDrive": "C:\\",
        "serverOs": "Microsoft Windows NT 6.2.9200.0"
      },
      "keyId": "LocationStringDataDoc:3202:2016-05-16T15:02:05.0000000-04:00",
      "locationKey": 3202,
      "type": "LocationStringDataDoc"
    }
  },
  {
    "SiteInfo": {
      "createDate": "2016-05-16T15:17:07-04:00",
      "item": {
        "advantageVersion": "16.4.10.103",
        "commCertificatesAreValid": "True",
        "commCertificatesExpiration": "2017-02-03 14:36:29Z",
        "commLastCorporateUpdate": null,
        "commLastStageUpdate": null,
        "commLastWebStoreUpdate": "2016-05-16 19:15:50Z",
        "dbBackupPath1": null,
        "dbBackupPath2": null,
        "dbBackupPath3": null,
        "dbBackupTime": null,
        "dbDataDrive": "C:",
        "dbLastBackup": null,
        "dbSqlVersion": "Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) \n\tDec 28 2012 20:23:12 \n\tCopyright (c) Microsoft Corporation\n\tExpress Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)\n",
        "eodImbalanceDate": "2016-05-15",
        "serverCpuType": "Intel64 Family 6 Model 45 Stepping 7",
        "serverNetVersion": "4.0.30319",
        "serverOSDrive": "C:\\",
        "serverOs": "Microsoft Windows NT 6.2.9200.0"
      },
      "keyId": "LocationStringDataDoc:3202:2016-05-16T15:17:07.0000000-04:00",
      "locationKey": 3202,
      "type": "LocationStringDataDoc"
    }
  },
  {
    "SiteInfo": {
      "createDate": "2016-05-16T15:32:08-04:00",
      "item": {
        "advantageVersion": "16.4.10.103",
        "commCertificatesAreValid": "True",
        "commCertificatesExpiration": "2017-02-03 14:36:29Z",
        "commLastCorporateUpdate": null,
        "commLastStageUpdate": null,
        "commLastWebStoreUpdate": "2016-05-16 19:30:46Z",
        "dbBackupPath1": null,
        "dbBackupPath2": null,
        "dbBackupPath3": null,
        "dbBackupTime": null,
        "dbDataDrive": "C:",
        "dbLastBackup": null,
        "dbSqlVersion": "Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) \n\tDec 28 2012 20:23:12 \n\tCopyright (c) Microsoft Corporation\n\tExpress Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)\n",
        "eodImbalanceDate": "2016-05-15",
        "serverCpuType": "Intel64 Family 6 Model 45 Stepping 7",
        "serverNetVersion": "4.0.30319",
        "serverOSDrive": "C:\\",
        "serverOs": "Microsoft Windows NT 6.2.9200.0"
      },
      "keyId": "LocationStringDataDoc:3202:2016-05-16T15:32:08.0000000-04:00",
      "locationKey": 3202,
      "type": "LocationStringDataDoc"
    }
  }
]

geraldss · May 27, 2016, 4:11pm

Aha. It’s a subtle bug. We improved 4.5 to restrict the SELECT list to only contain group keys and aggregates.

The bug is that it is not recognizing your FIRST expressions as aggregates, even though they are. We will fix this in the next release. Meanwhile, let me think of a way to rewrite your query for 4.5…

geraldss · May 27, 2016, 4:22pm

Please try this:

SELECT StringDocs.locationKey, MILLIS_TO_STR(createDateBySixHours) AS createDate, items.name as name,
  {
    "first": ARRAY_AGG({"createDate": STR_TO_UTC(StringDocs.createDate), "value": items.`value`})[0],
    "last": ARRAY_REVERSE(ARRAY_AGG({"createDate": STR_TO_UTC(StringDocs.createDate), "value": items.`value`}))[0]
  } as `values`
FROM SiteInfo StringDocs
UNNEST ARRAY p FOR p IN OBJECT_PAIRS(StringDocs.item) WHEN p.`value` IS NOT NULL END items
LET 
  createDateBySixHours = DATE_ADD_MILLIS(DATE_TRUNC_MILLIS(STR_TO_MILLIS(StringDocs.createDate), 'hour'), -(DATE_PART_MILLIS(DATE_TRUNC_MILLIS(STR_TO_MILLIS(StringDocs.createDate), 'hour'), 'hour') % 6), 'hour')
WHERE StringDocs.type = 'LocationStringDataDoc'
	AND STR_TO_MILLIS(StringDocs.createDate) >= STR_TO_MILLIS('2016-05-01T00:00:00Z')
	AND STR_TO_MILLIS(StringDocs.createDate) < DATE_ADD_MILLIS(STR_TO_MILLIS('2016-05-31T00:00:00Z'), 1, 'day')
GROUP BY 
  StringDocs.locationKey,
  createDateBySixHours, 
  items.name
HAVING createDateBySixHours IS NOT NULL
ORDER BY
  StringDocs.locationKey,
  createDateBySixHours,
  items.name

btburnett3 · May 27, 2016, 6:22pm

@geraldss,

Worked like a charm, thanks!

Brant

atom_yang · July 10, 2016, 4:11am

I have the similar issue. At the same time, I found that If you have some aggregates fields on ORDER BY, you might be get this error.
How can I order by records in aggregates fields?

geraldss · July 10, 2016, 7:27pm

Hi @atom_yang, you can order by aggregate fields.

Can you use 4.5 GA, and post your exact query that is not working?

Gerald

atom_yang · July 11, 2016, 6:06am

yes, I found the following N1QL works:

SELECT PreparedName,avg(ResultCount) FROM system:completed_requests 
where PreparedName is not missing group by PreparedName order by avg(ResultCount);

but the following N1QL:

SELECT PreparedName,array_agg({"ResultCount":ResultCount}) as Result FROM system:completed_requests 
where PreparedName is not missing group by PreparedName order by PreparedName,ResultCount;

don’t work.
I want to aggregate ResultCount field group by PreparedName, and order by PreparedName first,and then order by ResultCount in Result array.

atom_yang · July 11, 2016, 6:10am

let me open a new topic.

rmcneal · September 19, 2016, 6:51pm

@geraldss

I am running into this problem as well. Any aggregate function that also has an ORDER BY throws “Error: Expression must be a group key or aggregate”. What release will this be fixed in and when can we expect it?

Thanks

geraldss · September 19, 2016, 8:30pm

Hi @rmcneal,

There is no bug to be fixed. If you post your query here along with the Couchbase version, we can rewrite the query correctly.

rmcneal · September 20, 2016, 5:58pm

@geraldss

It looks like my problem is with spring-data-couchbase (which is the framework I am using to query couchbase). Spring generates a count query for me so I can return pages of my objects. My original query has an ORDER By clause and spring-data-couchbase takes that entire query and changes the select to a select count(*). This worked in the past but it looks like since 4.5.0, couchbase no longer supports an ORDER BY used with an aggregate such as count(). My count query ends up looking like this:

select count(*) from abucket
where _class = "x.y.z.Item"
AND timestampOfExpiration BETWEEN 1 AND 1474128551258
AND bucketName = "a name"
ORDER BY timestampOfExpiration

So I agree, it is not a bug. It’s an incompatibility with spring-data-couchbase and Couchbase 4.5.0.

geraldss · September 20, 2016, 6:06pm

Ok. Do you have a workaround? I would think there is a way to rewrite the query so that spring couchbase does the right thing.

rmcneal · September 20, 2016, 6:29pm

@geraldss

I do not yet have a workaround. Please let me know if you have any ideas.

Thanks.

geraldss · September 20, 2016, 7:01pm

Can you post three things.

(1) The N1QL query you want to execute, without spring data.

(2) The query as written in spring data.

(3) The N1QL query that is generated by spring data.

rmcneal · September 20, 2016, 8:13pm

@geraldss

We submitted a patch to spring-data-couchbase to fix this issue. They have accepted the patch and merged it into a milestone release.

Thanks a ton for your help.