Hide
The following is the most minimal example I've been able to set up which shows the bug.
I have the following collection:
sometestdb> db.densifyDateExample.find()
|
[
|
{ _id: 'a', d: ISODate("2022-10-28T22:00:00.000Z") },
|
{ _id: 'b', d: ISODate("2022-10-29T22:00:00.000Z") },
|
{ _id: 'c', d: ISODate("2022-10-30T23:00:00.000Z") },
|
{ _id: 'd', d: ISODate("2022-10-31T23:00:00.000Z") },
|
{ _id: 'e', d: ISODate("2022-11-01T23:00:00.000Z") },
|
{ _id: 'f', d: ISODate("2022-11-02T23:00:00.000Z") }
|
]
|
The following pipeline results in the error:
db.densifyDateExample.aggregate({
|
"$facet": {
|
"facet1": [{
|
"$setWindowFields": {
|
"output": {
|
"prevId": {
|
"$shift": {
|
"by": -1,
|
"default": null,
|
"output": "$_id"
|
}
|
}
|
},
|
"sortBy": {
|
"_id": 1
|
}
|
}
|
}],
|
"facet2": [{
|
"$group": {
|
"_id": {
|
$hour: "$d"
|
}
|
}
|
}]
|
}
|
})
|
With the server log file containing:
{"t":{"$date":"2022-11-30T09:40:35.681+01:00"},"s":"I", "c":"COMMAND", "id":51803, "ctx":"conn22","msg":"Slow query","attr":{"type":"command","ns":"sometestdb.densifyDateExample","appName":"mongosh 1.2.3","command":{"explain":{"aggregate":"densifyDateExample","pipeline":[{"$facet":{"facet1":[{"$setWindowFields":{"output":{"prevId":{"$shift":{"by":-1,"default":null,"output":"$_id"}}},"sortBy":{"_id":1}}}],"facet2":[{"$group":{"_id":{"$hour":"$d"}}}]}}],"cursor":{}},"verbosity":"allPlansExecution","lsid":{"id":{"$uuid":"39dd7c2f-2e49-40ae-aeda-b696ba5d8993"}},"$db":"sometestdb"},"planSummary":"COLLSCAN","numYields":0,"queryHash":"17830885","queryFramework":"classic","ok":0,"errMsg":"Requested document not in SpillableCache. Expected range was 0--1 but got 0","errName":"Location5643004","errCode":5643004,"reslen":166,"locks":{"FeatureCompatibilityVersion":{"acquireCount":{"r":2}},"Global":{"acquireCount":{"r":2}},"Mutex":{"acquireCount":{"r":2}}},"storage":{},"remote":"127.0.0.1:57374","protocol":"op_msg","durationMillis":282}}
|
|
The following pipelines both return the expected result without errors:
db.densifyDateExample.aggregate({
|
"$facet": {
|
"facet1": [{
|
"$setWindowFields": {
|
"output": {
|
"prevId": {
|
"$shift": {
|
"by": -1,
|
"default": null,
|
"output": "$_id"
|
}
|
}
|
},
|
"sortBy": {
|
"_id": 1
|
}
|
}
|
}]
|
}
|
})
|
db.densifyDateExample.aggregate({
|
"$facet": {
|
"facet2": [{
|
"$group": {
|
"_id": {
|
$hour: "$d"
|
}
|
}
|
}]
|
}
|
})
|
I'm using MongoDB 6.0.3-29-g3568ba7 on CentOS 7.
Show
The following is the most minimal example I've been able to set up which shows the bug.
I have the following collection:
sometestdb> db.densifyDateExample.find()
[
{ _id: 'a' , d: ISODate( "2022-10-28T22:00:00.000Z" ) },
{ _id: 'b' , d: ISODate( "2022-10-29T22:00:00.000Z" ) },
{ _id: 'c' , d: ISODate( "2022-10-30T23:00:00.000Z" ) },
{ _id: 'd' , d: ISODate( "2022-10-31T23:00:00.000Z" ) },
{ _id: 'e' , d: ISODate( "2022-11-01T23:00:00.000Z" ) },
{ _id: 'f' , d: ISODate( "2022-11-02T23:00:00.000Z" ) }
]
The following pipeline results in the error:
db.densifyDateExample.aggregate({
"$facet" : {
"facet1" : [{
"$setWindowFields" : {
"output" : {
"prevId" : {
"$shift" : {
"by" : - 1 ,
"default" : null ,
"output" : "$_id"
}
}
},
"sortBy" : {
"_id" : 1
}
}
}],
"facet2" : [{
"$group" : {
"_id" : {
$hour: "$d"
}
}
}]
}
})
With the server log file containing:
{ "t" :{ "$date" : "2022-11-30T09:40:35.681+01:00" }, "s" : "I" , "c" : "COMMAND" , "id" : 51803 , "ctx" : "conn22" , "msg" : "Slow query" , "attr" :{ "type" : "command" , "ns" : "sometestdb.densifyDateExample" , "appName" : "mongosh 1.2.3" , "command" :{ "explain" :{ "aggregate" : "densifyDateExample" , "pipeline" :[{ "$facet" :{ "facet1" :[{ "$setWindowFields" :{ "output" :{ "prevId" :{ "$shift" :{ "by" :- 1 , "default" : null , "output" : "$_id" }}}, "sortBy" :{ "_id" : 1 }}}], "facet2" :[{ "$group" :{ "_id" :{ "$hour" : "$d" }}}]}}], "cursor" :{}}, "verbosity" : "allPlansExecution" , "lsid" :{ "id" :{ "$uuid" : "39dd7c2f-2e49-40ae-aeda-b696ba5d8993" }}, "$db" : "sometestdb" }, "planSummary" : "COLLSCAN" , "numYields" : 0 , "queryHash" : "17830885" , "queryFramework" : "classic" , "ok" : 0 , "errMsg" : "Requested document not in SpillableCache. Expected range was 0--1 but got 0" , "errName" : "Location5643004" , "errCode" : 5643004 , "reslen" : 166 , "locks" :{ "FeatureCompatibilityVersion" :{ "acquireCount" :{ "r" : 2 }}, "Global" :{ "acquireCount" :{ "r" : 2 }}, "Mutex" :{ "acquireCount" :{ "r" : 2 }}}, "storage" :{}, "remote" : "127.0.0.1:57374" , "protocol" : "op_msg" , "durationMillis" : 282 }}
The following pipelines both return the expected result without errors:
db.densifyDateExample.aggregate({
"$facet" : {
"facet1" : [{
"$setWindowFields" : {
"output" : {
"prevId" : {
"$shift" : {
"by" : - 1 ,
"default" : null ,
"output" : "$_id"
}
}
},
"sortBy" : {
"_id" : 1
}
}
}]
}
})
db.densifyDateExample.aggregate({
"$facet" : {
"facet2" : [{
"$group" : {
"_id" : {
$hour: "$d"
}
}
}]
}
})
I'm using MongoDB 6.0.3-29-g3568ba7 on CentOS 7.