Duplicate results when sorting using a Spring-Data pageable object on a JPA repository
I have a rest-api that returns a list of users when called. The API uses the org.springframework.data.domain.Pageable to paginate and sort the results. This works by simply passing the pageable to the JPA repository, which then returns the desired page.
For some reason, when sorting by the first name, there is a chance that a duplicate entry will appear, but only if multiple entries have the same first name. However, this never happens when sorting by lastName. Both are simply strings in the entity, there is no discernable difference besides the property name.
Have any of you ever encountered this and if so, how did you fix it?
EDIT: To clarify, there is basically no logic of mine between the controller and the repository. I just pass the pageable through and return the results.
EDIT 2: Solved! Interesting titbit: The reason why the issue was only occurring when sorting by the first name was that only there were there always records that appeared on page 1 and 2, regardless which way the entries were sorted. Lucky me that our testers used that specific test data, or I might have never noticed.
spring spring-data-jpa spring-data
edited Nov 23 at 10:36
Antu
680420
asked Nov 22 at 15:05
Alex Eggers
9311
add a comment |
I have a rest-api that returns a list of users when called. The API uses the org.springframework.data.domain.Pageable to paginate and sort the results. This works by simply passing the pageable to the JPA repository, which then returns the desired page.
For some reason, when sorting by the first name, there is a chance that a duplicate entry will appear, but only if multiple entries have the same first name. However, this never happens when sorting by lastName. Both are simply strings in the entity, there is no discernable difference besides the property name.
Have any of you ever encountered this and if so, how did you fix it?
EDIT: To clarify, there is basically no logic of mine between the controller and the repository. I just pass the pageable through and return the results.
EDIT 2: Solved! Interesting titbit: The reason why the issue was only occurring when sorting by the first name was that only there were there always records that appeared on page 1 and 2, regardless which way the entries were sorted. Lucky me that our testers used that specific test data, or I might have never noticed.
spring spring-data-jpa spring-data
edited Nov 23 at 10:36
Antu
680420
asked Nov 22 at 15:05
Alex Eggers
9311
Are you 100% sure that this duplicate entry is indeed duplicate AND does not exist in the database?
– Sven Hakvoort
Nov 22 at 15:58
How did the multiple entries appear? On the same page? On two consecutive pages?
– Tom
Nov 22 at 16:41
1
Possible duplicate of Paging in SQL with LIMIT/OFFSET sometimes results in duplicates on different pages
– Alan Hay
Nov 22 at 17:37
add a comment |
I have a rest-api that returns a list of users when called. The API uses the org.springframework.data.domain.Pageable to paginate and sort the results. This works by simply passing the pageable to the JPA repository, which then returns the desired page.
For some reason, when sorting by the first name, there is a chance that a duplicate entry will appear, but only if multiple entries have the same first name. However, this never happens when sorting by lastName. Both are simply strings in the entity, there is no discernable difference besides the property name.
Have any of you ever encountered this and if so, how did you fix it?
EDIT: To clarify, there is basically no logic of mine between the controller and the repository. I just pass the pageable through and return the results.
EDIT 2: Solved! Interesting titbit: The reason why the issue was only occurring when sorting by the first name was that only there were there always records that appeared on page 1 and 2, regardless which way the entries were sorted. Lucky me that our testers used that specific test data, or I might have never noticed.
spring spring-data-jpa spring-data
edited Nov 23 at 10:36
Antu
680420
asked Nov 22 at 15:05
Alex Eggers
9311
I have a rest-api that returns a list of users when called. The API uses the org.springframework.data.domain.Pageable to paginate and sort the results. This works by simply passing the pageable to the JPA repository, which then returns the desired page.
For some reason, when sorting by the first name, there is a chance that a duplicate entry will appear, but only if multiple entries have the same first name. However, this never happens when sorting by lastName. Both are simply strings in the entity, there is no discernable difference besides the property name.
Have any of you ever encountered this and if so, how did you fix it?
EDIT: To clarify, there is basically no logic of mine between the controller and the repository. I just pass the pageable through and return the results.
EDIT 2: Solved! Interesting titbit: The reason why the issue was only occurring when sorting by the first name was that only there were there always records that appeared on page 1 and 2, regardless which way the entries were sorted. Lucky me that our testers used that specific test data, or I might have never noticed.
spring spring-data-jpa spring-data
spring spring-data-jpa spring-data
edited Nov 23 at 10:36
Antu
680420
asked Nov 22 at 15:05
Alex Eggers
9311
edited Nov 23 at 10:36
Antu
680420
asked Nov 22 at 15:05
Alex Eggers
9311
edited Nov 23 at 10:36
Antu
680420
edited Nov 23 at 10:36
Antu
680420
edited Nov 23 at 10:36
Antu
680420
680420
asked Nov 22 at 15:05
Alex Eggers
9311
asked Nov 22 at 15:05
Alex Eggers
9311
asked Nov 22 at 15:05
Alex Eggers
9311
9311
Are you 100% sure that this duplicate entry is indeed duplicate AND does not exist in the database?
– Sven Hakvoort
Nov 22 at 15:58
How did the multiple entries appear? On the same page? On two consecutive pages?
– Tom
Nov 22 at 16:41
1
Possible duplicate of Paging in SQL with LIMIT/OFFSET sometimes results in duplicates on different pages
– Alan Hay
Nov 22 at 17:37
add a comment |
Are you 100% sure that this duplicate entry is indeed duplicate AND does not exist in the database?
– Sven Hakvoort
Nov 22 at 15:58
How did the multiple entries appear? On the same page? On two consecutive pages?
– Tom
Nov 22 at 16:41
1
Possible duplicate of Paging in SQL with LIMIT/OFFSET sometimes results in duplicates on different pages
– Alan Hay
Nov 22 at 17:37
Are you 100% sure that this duplicate entry is indeed duplicate AND does not exist in the database?
– Sven Hakvoort
Nov 22 at 15:58
Are you 100% sure that this duplicate entry is indeed duplicate AND does not exist in the database?
– Sven Hakvoort
Nov 22 at 15:58
How did the multiple entries appear? On the same page? On two consecutive pages?
– Tom
Nov 22 at 16:41
How did the multiple entries appear? On the same page? On two consecutive pages?
– Tom
Nov 22 at 16:41
1
1
Possible duplicate of Paging in SQL with LIMIT/OFFSET sometimes results in duplicates on different pages
– Alan Hay
Nov 22 at 17:37
Possible duplicate of Paging in SQL with LIMIT/OFFSET sometimes results in duplicates on different pages
– Alan Hay
Nov 22 at 17:37
add a comment |
1 Answer
1
active
oldest
votes
Yes, I have seen that: a record can appear on, say, page 1 and then again on page 2.
This is an issue at the database level. For example 10 items per page and items at positions 10 and 11 have the same value for the property then it can be random which one appears in which position in each resultset.
Therefore apply a secondary sort on a unique property - the database ID for example - in order to ensure consistent ordering across requests.
answered Nov 22 at 17:37
Alan Hay
15.2k22769
This was the issue. I'm actually annoyed it took me this long to see it...looking back there is obviously no reason the way entries with the same keys should be sorted consistently across multiple requests :D Thanks!
– Alex Eggers
Nov 23 at 9:33
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53433735%2fduplicate-results-when-sorting-using-a-spring-data-pageable-object-on-a-jpa-repo%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Yes, I have seen that: a record can appear on, say, page 1 and then again on page 2.
This is an issue at the database level. For example 10 items per page and items at positions 10 and 11 have the same value for the property then it can be random which one appears in which position in each resultset.
Therefore apply a secondary sort on a unique property - the database ID for example - in order to ensure consistent ordering across requests.
answered Nov 22 at 17:37
Alan Hay
15.2k22769
This was the issue. I'm actually annoyed it took me this long to see it...looking back there is obviously no reason the way entries with the same keys should be sorted consistently across multiple requests :D Thanks!
– Alex Eggers
Nov 23 at 9:33
add a comment |
Yes, I have seen that: a record can appear on, say, page 1 and then again on page 2.
This is an issue at the database level. For example 10 items per page and items at positions 10 and 11 have the same value for the property then it can be random which one appears in which position in each resultset.
Therefore apply a secondary sort on a unique property - the database ID for example - in order to ensure consistent ordering across requests.
answered Nov 22 at 17:37
Alan Hay
15.2k22769
This was the issue. I'm actually annoyed it took me this long to see it...looking back there is obviously no reason the way entries with the same keys should be sorted consistently across multiple requests :D Thanks!
– Alex Eggers
Nov 23 at 9:33
add a comment |
Yes, I have seen that: a record can appear on, say, page 1 and then again on page 2.
This is an issue at the database level. For example 10 items per page and items at positions 10 and 11 have the same value for the property then it can be random which one appears in which position in each resultset.
Therefore apply a secondary sort on a unique property - the database ID for example - in order to ensure consistent ordering across requests.
answered Nov 22 at 17:37
Alan Hay
15.2k22769
Yes, I have seen that: a record can appear on, say, page 1 and then again on page 2.
This is an issue at the database level. For example 10 items per page and items at positions 10 and 11 have the same value for the property then it can be random which one appears in which position in each resultset.
Therefore apply a secondary sort on a unique property - the database ID for example - in order to ensure consistent ordering across requests.
answered Nov 22 at 17:37
Alan Hay
15.2k22769
edited Nov 23 at 9:35
answered Nov 22 at 17:37
Alan Hay
15.2k22769
answered Nov 22 at 17:37
Alan Hay
15.2k22769
answered Nov 22 at 17:37
Alan Hay
15.2k22769
15.2k22769
This was the issue. I'm actually annoyed it took me this long to see it...looking back there is obviously no reason the way entries with the same keys should be sorted consistently across multiple requests :D Thanks!
– Alex Eggers
Nov 23 at 9:33
add a comment |
This was the issue. I'm actually annoyed it took me this long to see it...looking back there is obviously no reason the way entries with the same keys should be sorted consistently across multiple requests :D Thanks!
– Alex Eggers
Nov 23 at 9:33
This was the issue. I'm actually annoyed it took me this long to see it...looking back there is obviously no reason the way entries with the same keys should be sorted consistently across multiple requests :D Thanks!
– Alex Eggers
Nov 23 at 9:33
This was the issue. I'm actually annoyed it took me this long to see it...looking back there is obviously no reason the way entries with the same keys should be sorted consistently across multiple requests :D Thanks!
– Alex Eggers
Nov 23 at 9:33
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53433735%2fduplicate-results-when-sorting-using-a-spring-data-pageable-object-on-a-jpa-repo%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Are you 100% sure that this duplicate entry is indeed duplicate AND does not exist in the database?
– Sven Hakvoort
Nov 22 at 15:58
How did the multiple entries appear? On the same page? On two consecutive pages?
– Tom
Nov 22 at 16:41
1
Possible duplicate of Paging in SQL with LIMIT/OFFSET sometimes results in duplicates on different pages
– Alan Hay
Nov 22 at 17:37