Skip to content

SQL: Remove restriction for single column grouping#31818

Merged
costin merged 5 commits intoelastic:masterfrom
costin:fix-31793
Jul 6, 2018
Merged

SQL: Remove restriction for single column grouping#31818
costin merged 5 commits intoelastic:masterfrom
costin:fix-31793

Conversation

@costin
Copy link
Copy Markdown
Member

@costin costin commented Jul 5, 2018

For historical reasons SQL restricts GROUP BY to only one field.
This commit removes the restriction and improves the test suite with
multi group by tests.

Close #31793

@costin
SQL: Remove restriction for single column grouping
a460a19 
For historical reasons SQL restricts GROUP BY to only one field.
This commit removes the restriction and improves the test suite with
multi group by tests.

Close elastic#31793
@costin costin requested review from astefan, bpintea and nik9000 July 5, 2018 11:07
@elasticmachine
Copy link
Copy Markdown
Collaborator

elasticmachine commented Jul 5, 2018

Pinging @elastic/es-search-aggs

imotov
imotov reviewed Jul 5, 2018
View reviewed changes
Copy link
Copy Markdown
Contributor

@imotov imotov left a comment

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

I think we should either document of fix the limitation that the sequence of fields in GROUP BY and ORDER BY has to be the same. For example, this statement doesn't seem to work:

SELECT gender g, languages l FROM "test_emp" GROUP BY gender, languages ORDER BY languages ASC, gender ASC

@costin
Copy link
Copy Markdown
Member Author

costin commented Jul 6, 2018

@imotov I've tried reproducing the issue and it works:

SELECT gender AS g, languages FROM emp GROUP BY gender, languages ORDER BY languages ASC, gender ASC;

       g       |   languages   
---------------+---------------
F              |1              
F              |2              
F              |3              
F              |4              
F              |5              
M              |1              
M              |2              
M              |3              
M              |4              
M              |5    

SELECT gender AS g, languages FROM emp GROUP BY gender, languages ORDER BY languages ASC, gender DESC;

       g       |   languages   
---------------+---------------
M              |1              
M              |2              
M              |3              
M              |4              
M              |5              
F              |1              
F              |2              
F              |3              
F              |4              
F              |5       

SELECT gender AS g, languages FROM emp GROUP BY gender, languages ORDER BY languages DESC, gender DESC;
       g       |   languages   
---------------+---------------
M              |5              
M              |4              
M              |3              
M              |2              
M              |1              
F              |5              
F              |4              
F              |3              
F              |2              
F              |1

Maybe the error is not about ES or you are using the wrong branch?

@costin
Copy link
Copy Markdown
Member Author

costin commented Jul 6, 2018

Actually scratch that, the order is incorrect...should be first language and then gender...

@costin
Copy link
Copy Markdown
Member Author

costin commented Jul 6, 2018

Found the issue - we don't align the order of the groups with that of the order but rather keep them in declaration format which creates the incorrect order.
I'm looking into fixing this as part of this PR.

astefan
astefan approved these changes Jul 6, 2018
View reviewed changes
Copy link
Copy Markdown
Contributor

@astefan astefan left a comment

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

LGTM

@costin
Copy link
Copy Markdown
Member Author

costin commented Jul 6, 2018

Pushed the fix and updated the test suite along with the docs.

bpintea
bpintea approved these changes Jul 6, 2018
View reviewed changes
Copy link
Copy Markdown
Contributor

@bpintea bpintea left a comment

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

LGTM

@costin costin merged commit 9ffb26a into elastic:master Jul 6, 2018
@costin costin deleted the fix-31793 branch July 6, 2018 17:55
costin added a commit that referenced this pull request Jul 6, 2018
@costin
SQL: Remove restriction for single column grouping (#31818)
1a01724 
For historical reasons SQL restricts GROUP BY to only one field.
This commit removes the restriction and improves the test suite with
multi group by tests.

Close #31793

(cherry picked from commit 9ffb26a)
dnhatn added a commit that referenced this pull request Jul 7, 2018
@dnhatn
Merge branch '6.x' into ccr-6.x
8425a5c 
* 6.x:
  [ML] Fix master node deadlock during ML daily maintenance (#31836)
  Build: Switch integ-test-zip to OSS-only (#31866)
  Build: Fix detection of Eclipse Compiler Server (#31838)
  SQL: Remove restriction for single column grouping (#31818)
  Docs: Inconsistency between description and example (#31858)
  Fix and reenable TribeIntegrationTests
  QA: build improvements related to SQL projects (#31862)
  muted test
  [Docs] Add clarification to analysis example (#31826)
  Check timeZone() argument in AbstractSqlQueryRequest (#31822)
  Remove obsolete parameters from analyze rest spec (#31795)
  SQL: Fix incorrect HAVING equality (#31820)
  Smaller aesthetic fixes to InternalTestCluster (#31831)
  [Docs] Clarify accepted sort case (#31605)
  Do not return all indices if a specific alias is requested via get aliases api. (#29538)
  [Docs] Fix wrong link in Korean analyzer docs (#31815)
  Fix profiling of ordered terms aggs (#31814)
  Fix handling of points_only with term strategy in geo_shape (#31766)
  Docs: Explain _bulk?refresh shard targeting
  REST high-level client: add get index API (#31703)
dnhatn added a commit that referenced this pull request Jul 7, 2018
@dnhatn
Merge branch 'master' into ccr
4be6b0e 
* master:
  [ML] Fix master node deadlock during ML daily maintenance (#31836)
  Build: Switch integ-test-zip to OSS-only (#31866)
  SQL: Remove restriction for single column grouping (#31818)
  Build: Fix detection of Eclipse Compiler Server (#31838)
  Docs: Inconsistency between description and example (#31858)
  Re-enable bwc tests now that #29538 has been backported and 6.x intake build succeeded.
  QA: build improvements related to SQL projects (#31862)
  [Docs] Add clarification to analysis example (#31826)
  Check timeZone() argument in AbstractSqlQueryRequest (#31822)
  SQL: Fix incorrect HAVING equality (#31820)
  Smaller aesthetic fixes to InternalTestCluster (#31831)
  [Docs] Clarify accepted sort case (#31605)
  Temporarily disable bwc test in order to backport #29538
  Remove obsolete parameters from analyze rest spec (#31795)
  [Docs] Fix wrong link in Korean analyzer docs (#31815)
  Fix profiling of ordered terms aggs (#31814)
  Properly mute test involving JDK11 closes #31739
  Do not return all indices if a specific alias is requested via get aliases api. (#29538)
  Get snapshot rest client cleanups (#31740)
  Docs: Explain _bulk?refresh shard targeting
  Fix handling of points_only with term strategy in geo_shape (#31766)
@astefan astefan mentioned this pull request Jul 13, 2018
Closed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Reviewers

@nik9000 nik9000 nik9000 approved these changes

@astefan astefan astefan approved these changes

@bpintea bpintea bpintea approved these changes

+1 more reviewer

@imotov imotov imotov left review comments

Reviewers whose approvals may not affect merge requirements

Assignees

No one assigned

Labels

:Analytics/SQL SQL querying >enhancement v6.4.0 v7.0.0-beta1

Projects

None yet

Milestone

No milestone

Development

Successfully merging this pull request may close these issues.

7 participants

@costin @elasticmachine @nik9000 @imotov @astefan @bpintea @colings86