[SERVER-9540] How to get the previous mongoDB document from a compound index Created: 02/May/13 Updated: 16/May/13 Resolved: 06/May/13 |
|
| Status: | Closed |
| Project: | Core Server |
| Component/s: | Internal Client |
| Affects Version/s: | 2.2.0 |
| Fix Version/s: | None |
| Type: | Question | Priority: | Minor - P4 |
| Reporter: | George Thompson | Assignee: | Thomas Rueckstiess |
| Resolution: | Duplicate | Votes: | 0 |
| Labels: | query | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Environment: |
Windows 7 64 bit SP1 |
||
| Issue Links: |
|
||||||||
| Participants: | |||||||||
| Description |
|
I asked this at mongo-usr, mongo-dev and Stack Overflow with no luck. I may be obtuse and am missing something fundamentally simple. Given this data with a unique compound index:
If I want the next document in the index after {{ { employeenum : 5, check : "E" }}}. I can do this:
which returns {{ { employeenum : 6, check : "A" }}}. But how do I traverse backwards from {{ { employeenum : 5, check : "E" }}}? How do I fetch {{ {employeenum : 2, check : "C" }}}? The way I am doing it:
requires a reverse index, a very inefficient solution. Is there a better way? B-tree indexes are bi-directional. One suggested:
And yes that will gets me 2-C. Now, how to go from 2-C to 2-B? This:
returns {{ {"employeenum" : 6, "check" : "A"}}} I need a general way to traverse the index from a point on the index. I am using the C++ driver but using the shell here for illustration. We are developing a rapid development tool using mongoDB as the underlying database. This is a (presumably) simple use case: A window displays a single document from a collection with a compound index. The user presses a button to display the next document using the compound index. Or another button to display the previous. I did try:
But that crashed the server every time which raises a concern that a shell users could crash the server with an (apparently) malformed query in a mission critical application such as ours. |
| Comments |
| Comment by George Thompson [ 06/May/13 ] | |||||
|
Created | |||||
| Comment by George Thompson [ 06/May/13 ] | |||||
|
Yes, I built a debug version for use with the C++ driver. No, I don't believe it is index corruption since I can replicate this with a small set of test data, first dropping the test collection. Possibly it is related to I'll file a separate ticket with a full stack trace. Thanks | |||||
| Comment by Tad Marshall [ 06/May/13 ] | |||||
|
Hi George, Can you file a separate ticket for the unhandled exception in Windows? Steps to reproduce, a full stack trace (assuming that you got one) and enough log lines before the stack trace to provide context would be helpful. The unhandled exception you got (0x80000003) is a breakpoint exception, which is odd because we do not have breakpoints in our released (prebuilt) code. If you built a debug version yourself then this would make sense; we do have breakpoints in the debug build. The assertion failure is suggesting index corruption. If your query is able to produce this assertion in a non-corrupt index, this would be good to know. If reindexing made the crashes stop, this would tend to confirm the corrupt index theory. Tad | |||||
| Comment by George Thompson [ 06/May/13 ] | |||||
|
Hi Thomas, That is exactly what I wanted to do. Thank you very much for your help on this issue. For single key backward traversal, I was using:
On my setup (Windows 7 64 bit SP1 & mongoDB 2.2.0)
crashes the mongoDB server with:
I have been using the second reverse index workdaround. Disk space isn't an issue. My main concern is that this will be required for all indexes that require bi-directional traversal, say, an index on name, address, etc. The small impact will accumulate. I'll keep an eye on Thanks for the less complex find query for forward traversal. | |||||
| Comment by Thomas Rueckstiess [ 06/May/13 ] | |||||
|
Hi George, Just to confirm that this is what you want to achieve: You'd like to traverse documents from a (compound) index forwards and backwards in the order they're stored in the index, starting from a given document. Is that correct? Walking the index forward (in the direction it was specified) works fine, and I believe you don't even need the complex find query that you used in your examples: Assuming you want to start at {employeenum:2,check:"B"}, walking forwards:
This results in:
If you don't want the first document, you can add .skip(1) to the query. Now for the backwards case. One would expect that adding a .sort({employeenum:-1,check:-1}) would do the trick, but the desired behavior is currently not implemented yet when using .min() / .max(). I've opened a separate server issue where I've minimized your example, using a simple index on a single field, but the result is the same. The additional .sort() causes 0 documents to be returned. See As a work-around until this feature is implemented, I see two alternatives:
I hope that one of these work-arounds is suitable for your use-case. Thank you for submitting this issue. I will link this ticket to Regards, |