Launching soon: The world's first vendor agnostic bug scrubLearn more & join waitlist
OPERATIONAL DEFECT DATABASE
...
...
We attempt to find the earliest timestamp in a timeseries collection. In a usual collection, you can write an aggregation with ascending $sort, $limit 1 and $project steps without issues. After migrating to a timeseries collection on Atlas, this fails with the following error: PlanExecutor error during aggregation :: caused by :: BoundedSorter input is too out-of-order: with bound 2024-03-28T19:00:00.000+00:00, did not expect input 2024-03-28T18:04:01.000+00:00 Further observations: The sort (aggregation) step works when you invert the sorting (descending).
JIRAUSER1275120 commented on Wed, 30 Oct 2024 00:35:34 +0000: I couldn't reproduce this given the information we have. Seems like it's something about the user's data that is causing this behavior. If/when we have more details, we can re-open. JIRAUSER1278087 commented on Thu, 26 Sep 2024 15:22:43 +0000: Hi chris.wolff@mongodb.com, the timestamps should all be from this year. The broken data I see in Atlas' UI and that I receive in the error message is all from this year. There shouldn't be data with a timestamp before 1970. So, it is not 100% identical to the error message in the other ticket. The broken data is not always shown as well as the error message. For some reason it depends what I use to run the aggregation and it behaves differently for two timeseries collections that are both showing the same issues (but for one Compass works and not for the other). One apparently has 300+ healthy entries, the other one that still works in Compass has like 16 healthy entries. So, let's go through it: Java 21 + Spring Boot 3.3.4 + Spring Data MongoDB Service In our services, we try to execute this query in Spring Data MongoDB on the collection that has 16 healthy entries: @Aggregation({ "{ $sort: { timestamp: 1 } }", "{ $limit: 1 }", "{ $project: { timestamp: 1, _id: 0 } }", }) TimestampDto findOldestTimestamp(); But we will receive this error in the console logs: {\"ok\": 0.0, \"errmsg\": \"PlanExecutor error during aggregation :: caused by :: BoundedSorter input is too out-of-order: with bound 2024-03-15T08:00:00.000+00:00, did not expect input 2024-03-14T13:01:34.000+00:00\", \"code\": 6369910, \"codeName\": \"Location6369910\", \"$clusterTime\": {\"clusterTime\": {\"$timestamp\": {\"t\": 1727358780, \"i\": 4}}, \"signature\": {\"hash\": {\"$binary\": {\"base64\": \"WH66tgeyBj/PtC2zONLvtjdeJyA=\", \"subType\": \"00\"}}, \"keyId\": 7374440855613997093}}, \"operationTime\": {\"$timestamp\": {\"t\": 1727358780, \"i\": 4}}} The query doesn't run for the other collection since this one broke. ATLAS 7.0.12: in Atlas' UI shows me the broken data for both collections. The first entry's timestamp is shown like this: timestamp: 2024-03-15T08:20:13.000+00:00 If I run with sort { "timestamp": 1 } , I will receive this error message: "An error occurred when performing the requested operation. Check your query and try again." If I run with sort { "timestamp": -1 } , I will receive data as expected. If I browse through the pages, I notice that there are at least 200 entries in both collections according to this view. If I look at the timestamps, it goes from 15th of March and a page later, I see entries from 14th of March. Maybe this is the core issue... MongoDB Compass 1.44.4 color: Color value is invalid Let's start with the collection that shows errors in Compass: Compass' Documents tab does not list the broken data. There, I can only see new data from September 2024. The broken data from March is not visible. If I run with sort { "timestamp": -1 } , I will receive like 340 valid entries sorted correctly. If I run with sort { "timestamp": 1 } , I will receive this error message: Encountered non-retryable error during query :: caused by :: PlanExecutor error during aggregation :: caused by :: BoundedSorter input is too out-of-order: with bound 2024-09-22T17:00:00.000+00:00, did not expect input 2024-09-22T12:16:26.000+00:00 In the Aggregations tab, I will now execute the following aggregation: [ { $sort: { timestamp: 1 } }, { $limit: 1 }, { $project: { timestamp: 1, _id: 0 } } ] Run, explain, and the preview runs in both view modes will show this error: PlanExecutor error during aggregation :: caused by :: BoundedSorter input is too out-of-order: with bound 2024-09-22T17:00:00.000+00:00, did not expect input 2024-09-22T12:16:26.000+00:00 If I check the same thing on the collection with 16 healthy entries, it works. It just does not see the data from March. It shows me 16 newer entries in all aggregation runs mentioned above. The Documents tab and its sort work as intended as well. For this collection, you would not think that anything is wrong via compass. But for the other collection Compass spits out errors. Why this is the case is something you might want to figure out. MongoSh When I run the aggregation via mongoSH, I will receive errors as well: command: db["thatCollectionThatShowsErrorsInCompass"].aggregate([ { $sort: { timestamp: 1 } }, { $limit: 1 }, { $project: { timestamp: 1, _id: 0 } } ]); results in error: MongoServerError[Location6369910]: PlanExecutor error during aggregation :: caused by :: BoundedSorter input is too out-of-order: with bound 2024-09-22T17:00:00.000+00:00, did not expect input 2024-09-22T12:16:26.000+00:00 but if I run with explain: db["thatCollectionThatShowsErrorsInCompass"].explain().aggregate([ { $sort: { timestamp: 1 } }, { $limit: 1 }, { $project: { timestamp: 1, _id: 0 } } ]); I receive this explanation: { serverInfo: { host: 'atlas-wwnekq-shard-00-02.oeavr.mongodb.net', port: 27016, version: '7.0.12', gitVersion: 'b6513ce0781db6818e24619e8a461eae90bc94fc' }, serverParameters: { internalQueryFacetBufferSizeBytes: 104857600, internalQueryFacetMaxOutputDocSizeBytes: 104857600, internalLookupStageIntermediateDocumentMaxSizeBytes: 104857600, internalDocumentSourceGroupMaxMemoryBytes: 104857600, internalQueryMaxBlockingSortMemoryUsageBytes: 104857600, internalQueryProhibitBlockingMergeOnMongoS: 0, internalQueryMaxAddToSetBytes: 104857600, internalDocumentSourceSetWindowFieldsMaxMemoryBytes: 104857600, internalQueryFrameworkControl: 'trySbeRestricted' }, explainVersion: '1', stages: [ { '$cursor': { queryPlanner: { namespace: 'daq-service.system.buckets.daqDomesticHotWaterMeasurements', indexFilterSet: false, parsedQuery: {}, queryHash: '8880B5AF', planCacheKey: '8880B5AF', maxIndexedOrSolutionsReached: false, maxIndexedAndSolutionsReached: false, maxScansToExplodeReached: false, winningPlan: { stage: 'COLLSCAN', direction: 'forward' }, rejectedPlans: [] } } }, { '$_internalUnpackBucket': { include: [ 'timestamp' ], timeField: 'timestamp', metaField: 'metaData', bucketMaxSpanSeconds: 86400, assumeNoMixedSchemaData: true, includeMinTimeAsMetadata: true } }, { '$_internalBoundedSort': { sortKey: { timestamp: 1 }, bound: { base: 'min', offsetSeconds: 0 }, limit: 1 } }, { '$project': { timestamp: true, _id: false } } ], command: { aggregate: 'system.buckets.daqDomesticHotWaterMeasurements', pipeline: [ { '$_internalUnpackBucket': { timeField: 'timestamp', metaField: 'metaData', bucketMaxSpanSeconds: 86400, assumeNoMixedSchemaData: true, usesExtendedRange: false } }, { '$sort': { timestamp: 1 } }, { '$limit': 1 }, { '$project': { timestamp: 1, _id: 0 } } ], cursor: {}, collation: { locale: 'simple' }, let: {}, fromMongos: false, '$db': 'daq-service' }, ok: 1, '$clusterTime': { clusterTime: Timestamp({ t: 1727363753, i: 3 }), signature: { hash: Binary.createFromBase64('F6f4SzqpMqsMQ7ptXv48Gsxb+ZU=', 0), keyId: 7377410292923236000 } }, operationTime: Timestamp({ t: 1727363753, i: 3 }) } If I run this for the collection that works in compass' UI, but not in our Java service, it will show me a timestamp from September. { timestamp: 2024-09-26T13:09:53.000Z } (Remember, Atlas' UI showed me data from March!) Its explain plan is the following: { serverInfo: { host: 'atlas-wwnekq-shard-00-01.oeavr.mongodb.net', port: 27016, version: '7.0.12', gitVersion: 'b6513ce0781db6818e24619e8a461eae90bc94fc' }, serverParameters: { internalQueryFacetBufferSizeBytes: 104857600, internalQueryFacetMaxOutputDocSizeBytes: 104857600, internalLookupStageIntermediateDocumentMaxSizeBytes: 104857600, internalDocumentSourceGroupMaxMemoryBytes: 104857600, internalQueryMaxBlockingSortMemoryUsageBytes: 104857600, internalQueryProhibitBlockingMergeOnMongoS: 0, internalQueryMaxAddToSetBytes: 104857600, internalDocumentSourceSetWindowFieldsMaxMemoryBytes: 104857600, internalQueryFrameworkControl: 'trySbeRestricted' }, explainVersion: '1', stages: [ { '$cursor': { queryPlanner: { namespace: 'daq-service.system.buckets.daqGeneralMeasurements', indexFilterSet: false, parsedQuery: {}, queryHash: '8880B5AF', planCacheKey: '8880B5AF', maxIndexedOrSolutionsReached: false, maxIndexedAndSolutionsReached: false, maxScansToExplodeReached: false, winningPlan: { stage: 'COLLSCAN', direction: 'forward' }, rejectedPlans: [] } } }, { '$_internalUnpackBucket': { include: [ 'timestamp' ], timeField: 'timestamp', metaField: 'metaData', bucketMaxSpanSeconds: 86400, assumeNoMixedSchemaData: true, includeMinTimeAsMetadata: true } }, { '$_internalBoundedSort': { sortKey: { timestamp: 1 }, bound: { base: 'min', offsetSeconds: 0 }, limit: 1 } }, { '$project': { timestamp: true, _id: false } } ], command: { aggregate: 'system.buckets.daqGeneralMeasurements', pipeline: [ { '$_internalUnpackBucket': { timeField: 'timestamp', metaField: 'metaData', bucketMaxSpanSeconds: 86400, assumeNoMixedSchemaData: true, usesExtendedRange: false } }, { '$sort': { timestamp: 1 } }, { '$limit': 1 }, { '$project': { timestamp: 1, _id: 0 } } ], cursor: {}, collation: { locale: 'simple' }, let: {}, fromMongos: false, '$db': 'daq-service' }, ok: 1, '$clusterTime': { clusterTime: Timestamp({ t: 1727363900, i: 1 }), signature: { hash: Binary.createFromBase64('hRfGDAh1WxMKY6ZiaSLOjRh4rn0=', 0), keyId: 7377410292923236000 } }, operationTime: Timestamp({ t: 1727363899, i: 12 }) } Why this is working in mongosh, but not our Java service is beyond me. Do you need other questions answered? Would it help if I created a support ticket about this, so you can look at the actual data/collections? Anyway, thank you for dealing with this odd issue. update: using header style in here breaks the formatting.... JIRAUSER1275120 commented on Mon, 16 Sep 2024 23:05:37 +0000: Hi christoph.ahlers@vaillant-group.com, I know it's been a while since you filed this bug, but I recently looked at SERVER-94017, which looks quite similar to what you saw. In that case the error message you reported sometimes occurred if the collection contained timestamps outside of the Unix epoch, that is, before 1970 or after 2038 or so. Did you have such data in your time series collection, and if so, are they actually meaningful for your application? Anything else you can tell me about the distribution of the timestamps in your data? It would also be helpful if you could provide explain output for a query that reproduces the issue. JIRAUSER1278087 commented on Tue, 30 Apr 2024 12:46:46 +0000: Version: Atlas shows me the version 7.0.8. That's the version that we used when we've noticed this issue and is still the currently used version. Queried against: primary History of the data: Initially, we persisted our entries with Java service using Spring Data MongoDB in a default collection where each measurement was a single document. We've migrated these documents to a timeseries collection. We ran an aggregation in MongoDB Compass and inserted the data via $out. The format of the data did not change. We've executed an aggregation like this: [ { $out: { db: "myDB", coll: "myCollectionTs", timeseries: { timeField: "timestamp", metaField: "metaData", granularity: "minutes" } } } ] Further observations: The issue is not present on all our clusters and collections. So, this is probably not a general issue how we've handled this. It is possible to bypass this issue via a match before the $sort. For example: $match: { timestamp: { $lt: ISODate() } } I believe it has to filter out measurements for the aggregation to work. It did not work when I used dates far into the future. The issue is still present. I can recreate it via MongoDB Compass 1.42.5. The affected timeseries-collection has 146547 measurements. We are constantly adding and removing documents (add with current timestamps, remove the oldest ones). We remove manually, so we do not use TTL. The data that caused should have been removed by now. But we still get this error, just with newer timestamps. This is the error I see right now: PlanExecutor error during aggregation :: caused by :: BoundedSorter input is too out-of-order: with bound 2024-04-14T03:00:00.000+00:00, did not expect input 2024-04-14T02:03:03.000+00:00 I have the feeling that something in the internal structure of that timeseries collection broke. Is there a way I can provide you with more insights to that? Would it help if we created a support ticket about this? Maybe you can analyze the actual affected collection. JIRAUSER1265262 commented on Tue, 30 Apr 2024 03:17:43 +0000: Hi Christoph! Thanks for your report, and your patience on the reply. I attempted to reproduce your issue by: 1) Creating a timeseries collection db.createCollection("sensorData", { timeseries: { timeField: "timestamp", granularity: "seconds" } }); 2) Inserting sample data db.sensorData.insertMany([ { timestamp: ISODate("2024-03-28T18:00:00.000Z"), value: 10 }, { timestamp: ISODate("2024-03-28T19:05:00.000Z"), value: 20 }, { timestamp: ISODate("2024-03-28T18:04:01.000Z"), value: 30 }, { timestamp: ISODate("2024-03-28T19:00:00.000Z"), value: 40 } ]); 3) Performing the $sort aggregation you point out db.sensorData.aggregate([ { $sort: { timestamp: 1 } } ]); 4) Also attempting the descending sort db.sensorData.aggregate([ { $sort: { timestamp: 1 } } ]); I did not observe an error when doing this on MongoDB 6.0.15 and 7.3.0 which are available on Atlas. I'm interested in getting some more information to better reproduce your issue. Can you describe in greater detail: What version was the query working on? What version and cluster topology is the query not working on? Are you running your query against a primary or secondary? How did you migrate to Atlas? Was it with mongosync or mongomirror? If so, what version? Is the issue still present?
have a timeseries collection with different timestamps execute this aggregation: { timestamp: 1, } receive error message about BoundedSorter input is too out-of-order. Screenshot from Compass is attached.