Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

SQL: Multiple ORDER BY and LIMIT on top of each other leads to incorrect results #71158

Closed
palesz opened this issue Mar 31, 2021 · 2 comments · Fixed by #72991
Closed

SQL: Multiple ORDER BY and LIMIT on top of each other leads to incorrect results #71158

palesz opened this issue Mar 31, 2021 · 2 comments · Fixed by #72991
Assignees
@matriv
Labels
:Analytics/SQL SQL querying >bug Team:QL (Deprecated) Meta label for query languages team

Comments

@palesz
Copy link
Contributor

palesz commented Mar 31, 2021

The following SELECT gives different results in ES vs H2:

SELECT * FROM (SELECT * FROM test_emp ORDER BY emp_no DESC NULLS LAST LIMIT 10) ORDER BY emp_no ASC NULLS LAST LIMIT 3

It is worth adding at least a check in the Verifier for this.
@palesz palesz added >bug :Analytics/SQL SQL querying Team:QL (Deprecated) Meta label for query languages team labels Mar 31, 2021
@elasticmachine
Copy link
Collaborator

Pinging @elastic/es-ql (Team:QL)

@matriv matriv self-assigned this Apr 12, 2021
matriv added a commit to matriv/elasticsearch that referenced this issue May 12, 2021
@matriv
SQL: Disallow non-collapsable subselects with ORDER BY
f7a89ed 
Ordering an already pre-ordered and limited subselect is not allowed,
as such queries cannot be collapsed and translated into query DSL, but
the require an extra ordering step on top of the results returned
internally by the search/agg query.

Fixes: elastic#71158
@matriv matriv mentioned this issue May 12, 2021
Merged
matriv added a commit that referenced this issue May 14, 2021
@matriv
SQL: Disallow non-collapsable subselects with ORDER BY (#72991)
a5a20ae 
Ordering an already pre-ordered and limited subselect is not allowed,
as such queries cannot be collapsed and translated into query DSL, but
the require an extra ordering step on top of the results returned
internally by the search/agg query.

Fixes: #71158
matriv added a commit to matriv/elasticsearch that referenced this issue May 14, 2021
@matriv
SQL: Disallow non-collapsable subselects with ORDER BY (elastic#72991)
8d03fbb 
Ordering an already pre-ordered and limited subselect is not allowed,
as such queries cannot be collapsed and translated into query DSL, but
the require an extra ordering step on top of the results returned
internally by the search/agg query.

Fixes: elastic#71158
(cherry picked from commit a5a20ae)
This was referenced May 14, 2021
Merged
Merged
matriv added a commit to matriv/elasticsearch that referenced this issue May 14, 2021
@matriv
SQL: Disallow non-collapsable subselects with ORDER BY (elastic#72991)
c2eb538 
Ordering an already pre-ordered and limited subselect is not allowed,
as such queries cannot be collapsed and translated into query DSL, but
the require an extra ordering step on top of the results returned
internally by the search/agg query.

Fixes: elastic#71158
(cherry picked from commit a5a20ae)
@matriv matriv mentioned this issue May 14, 2021
Merged
matriv added a commit that referenced this issue May 14, 2021
@matriv
SQL: Disallow non-collapsable subselects with ORDER BY (#72991) (#73094)
0a141c7 
Ordering an already pre-ordered and limited subselect is not allowed,
as such queries cannot be collapsed and translated into query DSL, but
the require an extra ordering step on top of the results returned
internally by the search/agg query.

Fixes: #71158
(cherry picked from commit a5a20ae)
matriv added a commit that referenced this issue May 14, 2021
@matriv
SQL: Disallow non-collapsable subselects with ORDER BY (#72991) (#73096)
3a9ce5c 
Ordering an already pre-ordered and limited subselect is not allowed,
as such queries cannot be collapsed and translated into query DSL, but
the require an extra ordering step on top of the results returned
internally by the search/agg query.

Fixes: #71158
(cherry picked from commit a5a20ae)
matriv added a commit that referenced this issue May 14, 2021
@matriv
SQL: Disallow non-collapsable subselects with ORDER BY (#72991) (#73097)
907379f 
Ordering an already pre-ordered and limited subselect is not allowed,
as such queries cannot be collapsed and translated into query DSL, but
the require an extra ordering step on top of the results returned
internally by the search/agg query.

Fixes: #71158
(cherry picked from commit a5a20ae)
@matriv
Copy link
Contributor

matriv commented May 14, 2021

master : a5a20ae
7.x : 0a141c7
7.13 : 3a9ce5c
7.12 : 907379f

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@matriv matriv

Labels
:Analytics/SQL SQL querying >bug Team:QL (Deprecated) Meta label for query languages team
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

SQL: Disallow non-collapsable subselects with ORDER BY matriv/elasticsearch
3 participants
@palesz @matriv @elasticmachine