I’m currently using Couchbase Server 7.1.5 and Java SDK 3.4.7. I have a document model named diagnosticsReports.
{
"propCode": "MEMCW",
"roomNumber": "118",
"macAddress": "E81863521C47",
"date": "2020-03-23",
"lastReportedTime": 1603224693,
"diagnosticsReport": [
{
"created": 1603224693,
"payload": [
{
"data": {
"innCode": "MSYOR",
"location": 0,
"roomNumber": "1413"
},
"name": "Provision Info",
"isError": 0,
"timestamp": 1608087137
},
{
"data": {
"isWiFiConnected": 0,
"isEthernetConnected": 1
},
"name": "Network Status",
"isError": 0,
"timestamp": 1608087137
}
]
}
]
}
With a query to get the latest date for each group:
SELECT MAX([d.date, d][1]).*
FROM (
SELECT document.*
FROM diagnosticReports AS document
WHERE document.propCode = $propCode
AND ($roomNumber IS NULL
OR document.roomNumber = $roomNumber)
AND ($macAddress IS NULL
OR document.macAddress = $macAddress) ) AS d
GROUP BY d.propCode,
d.roomNumber,
d.macAddress
ORDER BY MAX([d.date, d][1]).lastReportedTime DESC
OFFSET $offset
LIMIT $limit;
Using server version 7.1.5, the above query works. After upgrading to 7.6.1, the below error is observed:
{
"completed": true,
"coreId": "0x1a7e82c00000001",
"errors": [
{
"code": 5001,
"message": "Panic: runtime error: invalid memory address or nil pointer dereference",
"retry": false
}
],
"httpStatus": 200,
"idempotent": true,
"lastDispatchedFrom": "127.0.0.1:53307",
"lastDispatchedTo": "localhost:8093",
"requestId": 343,
"requestType": "QueryRequest",
"retried": 0,
"service": {
"bucket": "connectedroom",
"operationId": "null",
"scope": "ecmp",
"statement": "SELECT MAX([d.date, d][1]).* FROM ( SELECT document.* FROM guestDiagnosticReports AS document WHERE document.propCode = $propCode AND ($roomNumber IS NULL OR document.roomNumber = $roomNumber) AND ($macAddress IS NULL OR document.macAddress = $macAddress) ) AS d GROUP BY d.propCode, d.roomNumber, d.macAddress ORDER BY MAX([d.date, d][1]).lastReportedTime DESC OFFSET $offset LIMIT $limit",
"type": "query"
},
"timeoutMs": 45000,
"timings": {
"dispatchMicros": 8446,
"totalDispatchMicros": 8446,
"totalMicros": 8990
}
}
New working query.
SELECT document.*
FROM (
SELECT MAX([d.date, d][1]).*
FROM diagnosticReports AS d
WHERE d.propCode = $propCode
AND ($roomNumber IS NULL
OR d.roomNumber = $roomNumber)
AND ($macAddress IS NULL
OR d.macAddress = $macAddress)
GROUP BY d.propCode,
d.roomNumber,
d.macAddress
ORDER BY MAX([d.date, d][1]).lastReportedTime DESC ) AS document
OFFSET $offset
LIMIT $limit;
I’m using the query as a String in the statement variable.
return scope.query(
statement,
queryOptions()
.parameters(JsonObject.create()
.put("propCode", propCode)
.put("roomNumber", roomNumber)
.put("macAddress", macAddress)
.put("offset", offset)
.put("limit", limit))
.scanConsistency(QueryScanConsistency.REQUEST_PLUS)
.metrics(true)
.readonly(true)
.adhoc(false))
.rowsAs(DiagnosticReportsEntity.class);
Placing the aggregate function in the FROM clause resolves this error. I believe this is because of the first order of execution?
Is this a memory configuration issue, something more related to how the query engine works or poor query construction?