[SERVER-21783] aggregation framework indexed date(IsoDate) field sorting bug Created: 07/Dec/15  Updated: 07/Dec/15  Resolved: 07/Dec/15

Status: Closed
Project: Core Server
Component/s: Aggregation Framework
Affects Version/s: 3.0.7
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: erdal ceylan Assignee: Unassigned
Resolution: Duplicate Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Related
is related to SERVER-7568 Aggregation framework favors non-bloc... Closed
Operating System: ALL
Participants:

 Description   

Hı, good work

I found an bug
The complex consists of the following ways

1.) aggregation framework
2.) date(ISODate) field
3.) createIndex(

{date:-1}

)
4.) run query (if dropIndex or remove sort field everything is ok

my query:

db.messages.aggregation([{
    "$match": {
        "$and": [{
            "recipient_id": 1607389
        }, {
            "deletedbyrecipient": 0
        }, {
            "blocked": 0
        }]
    }
}, {
    "$sort": {
        "date": -1/*if indexed very slovly worked , if dropIndex no problem*/
    }
}, {
    "$group": {
        "_id": "$sender_id",
        "id": {
            "$first": "$_id"
        },
        "smileid": {
            "$first": "$_id"
        },
        "blocked": {
            "$first": "$blocked"
        },
        "date": {
            "$first": "$date"
        },
        "deletedbyrecipient": {
            "$first": "$deletedbyrecipient"
        },
        "deletedbysender": {
            "$first": "$deletedbysender"
        },
        "seen": {
            "$first": "$seen"
        },
        "recipient_id": {
            "$first": "$recipient_id"
        },
        "sender_id": {
            "$first": "$sender_id"
        },
        "unseencount": {
            "$sum": {
                "$cond": ["$seen", 0, 1]
            }
        }
    }
}, {
    "$sort": {
        "date": -1
    }
}, {
    "$limit": 10
}, {
    "$skip": 0
}]);



 Comments   
Comment by Charlie Swanson [ 07/Dec/15 ]

Thanks erdalceylan,

It does look like you are seeing the same issue. I'm going to close this ticket as a duplicate, and encourage you to watch the other ticket for updates on resolving this bug. There is a suggested workaround in the linked ticket. Sorry for the inconvenience!

Charlie

Comment by erdal ceylan [ 07/Dec/15 ]

without index on date output

{
	"stages" : [
		{
			"$cursor" : {
				"query" : {
					"$and" : [
						{
							"recipient_id" : 1607389
						},
						{
							"deletedbyrecipient" : 0
						},
						{
							"blocked" : 0
						}
					]
				},
				"fields" : {
					"blocked" : 1,
					"date" : 1,
					"deletedbyrecipient" : 1,
					"deletedbysender" : 1,
					"recipient_id" : 1,
					"seen" : 1,
					"sender_id" : 1,
					"_id" : 1
				},
				"queryPlanner" : {
					"plannerVersion" : 1,
					"namespace" : "gs.messages",
					"indexFilterSet" : false,
					"parsedQuery" : {
						"$and" : [
							{
								"blocked" : {
									"$eq" : 0
								}
							},
							{
								"deletedbyrecipient" : {
									"$eq" : 0
								}
							},
							{
								"recipient_id" : {
									"$eq" : 1607389
								}
							}
						]
					},
					"winningPlan" : {
						"stage" : "KEEP_MUTATIONS",
						"inputStage" : {
							"stage" : "FETCH",
							"filter" : {
								"blocked" : {
									"$eq" : 0
								}
							},
							"inputStage" : {
								"stage" : "IXSCAN",
								"keyPattern" : {
									"recipient_id" : 1,
									"deletedbyrecipient" : 1,
									"seen" : 1
								},
								"indexName" : "recipient_id_1_deletedbyrecipient_1_seen_1",
								"isMultiKey" : false,
								"direction" : "forward",
								"indexBounds" : {
									"recipient_id" : [
										"[1607389.0, 1607389.0]"
									],
									"deletedbyrecipient" : [
										"[0.0, 0.0]"
									],
									"seen" : [
										"[MinKey, MaxKey]"
									]
								}
							}
						}
					},
					"rejectedPlans" : [ ]
				}
			}
		},
		{
			"$sort" : {
				"sortKey" : {
					"date" : -1
				}
			}
		},
		{
			"$group" : {
				"_id" : "$sender_id",
				"id" : {
					"$first" : "$_id"
				},
				"smileid" : {
					"$first" : "$_id"
				},
				"blocked" : {
					"$first" : "$blocked"
				},
				"date" : {
					"$first" : "$date"
				},
				"deletedbyrecipient" : {
					"$first" : "$deletedbyrecipient"
				},
				"deletedbysender" : {
					"$first" : "$deletedbysender"
				},
				"seen" : {
					"$first" : "$seen"
				},
				"recipient_id" : {
					"$first" : "$recipient_id"
				},
				"sender_id" : {
					"$first" : "$sender_id"
				},
				"unseencount" : {
					"$sum" : {
						"$cond" : [
							"$seen",
							{
								"$const" : 0
							},
							{
								"$const" : 1
							}
						]
					}
				}
			}
		},
		{
			"$sort" : {
				"sortKey" : {
					"date" : -1
				},
				"limit" : NumberLong(10)
			}
		},
		{
			"$skip" : NumberLong(0)
		}
	],
	"ok" : 1
}

Comment by Charlie Swanson [ 07/Dec/15 ]

This is with the index on date? Can you post the explain output without the index on date?

Comment by erdal ceylan [ 07/Dec/15 ]

explain output:

{
	"stages" : [
		{
			"$cursor" : {
				"query" : {
					"$and" : [
						{
							"recipient_id" : 1607389
						},
						{
							"deletedbyrecipient" : 0
						},
						{
							"blocked" : 0
						}
					]
				},
				"sort" : {
					"date" : -1
				},
				"fields" : {
					"blocked" : 1,
					"date" : 1,
					"deletedbyrecipient" : 1,
					"deletedbysender" : 1,
					"recipient_id" : 1,
					"seen" : 1,
					"sender_id" : 1,
					"_id" : 1
				},
				"queryPlanner" : {
					"plannerVersion" : 1,
					"namespace" : "gs.messages",
					"indexFilterSet" : false,
					"parsedQuery" : {
						"$and" : [
							{
								"blocked" : {
									"$eq" : 0
								}
							},
							{
								"deletedbyrecipient" : {
									"$eq" : 0
								}
							},
							{
								"recipient_id" : {
									"$eq" : 1607389
								}
							}
						]
					},
					"winningPlan" : {
						"stage" : "FETCH",
						"filter" : {
							"$and" : [
								{
									"blocked" : {
										"$eq" : 0
									}
								},
								{
									"deletedbyrecipient" : {
										"$eq" : 0
									}
								},
								{
									"recipient_id" : {
										"$eq" : 1607389
									}
								}
							]
						},
						"inputStage" : {
							"stage" : "IXSCAN",
							"keyPattern" : {
								"date" : -1
							},
							"indexName" : "date_-1",
							"isMultiKey" : false,
							"direction" : "forward",
							"indexBounds" : {
								"date" : [
									"[MaxKey, MinKey]"
								]
							}
						}
					},
					"rejectedPlans" : [ ]
				}
			}
		},
		{
			"$group" : {
				"_id" : "$sender_id",
				"id" : {
					"$first" : "$_id"
				},
				"smileid" : {
					"$first" : "$_id"
				},
				"blocked" : {
					"$first" : "$blocked"
				},
				"date" : {
					"$first" : "$date"
				},
				"deletedbyrecipient" : {
					"$first" : "$deletedbyrecipient"
				},
				"deletedbysender" : {
					"$first" : "$deletedbysender"
				},
				"seen" : {
					"$first" : "$seen"
				},
				"recipient_id" : {
					"$first" : "$recipient_id"
				},
				"sender_id" : {
					"$first" : "$sender_id"
				},
				"unseencount" : {
					"$sum" : {
						"$cond" : [
							"$seen",
							{
								"$const" : 0
							},
							{
								"$const" : 1
							}
						]
					}
				}
			}
		},
		{
			"$sort" : {
				"sortKey" : {
					"date" : -1
				},
				"limit" : NumberLong(10)
			}
		},
		{
			"$skip" : NumberLong(0)
		}
	],
	"ok" : 1
}

Comment by Charlie Swanson [ 07/Dec/15 ]

Hi erdalceylan,

Thanks for the report. I believe you are experiencing SERVER-7568, where adding the index will cause the aggregation pipeline to use a non-blocking sort, even if it's not faster. To verify, can you run the pipeline with explain both with and without the index, and post the output here?

db.messages.explain().aggregate([ ... ])

Thanks!

Generated at Thu Feb 08 03:58:24 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.