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: Cannot GROUP BY when constant function is used #36074

Closed
matriv opened this issue Nov 29, 2018 · 3 comments · Fixed by #43072
Closed

SQL: Cannot GROUP BY when constant function is used #36074

matriv opened this issue Nov 29, 2018 · 3 comments · Fixed by #43072
Labels
:Analytics/SQL SQL querying >bug

Comments

@matriv
Copy link
Contributor

matriv commented Nov 29, 2018 

SELECT PI() * emp_no
FROM test_emp
GROUP BY 1;

throws:

java.sql.SQLException: Server sent bad type [folding_exception]. Original type was [line 1:13: Cannot find grouping for 'PI * (emp_no)']. [FoldingException[line 1:13: Cannot find grouping for 'PI * (emp_no)']
	at org.elasticsearch.xpack.sql.planner.QueryFolder$FoldAggregate.rule(QueryFolder.java:311)
	at org.elasticsearch.xpack.sql.planner.QueryFolder$FoldAggregate.rule(QueryFolder.java:196)
	at org.elasticsearch.xpack.sql.tree.Node.lambda$transformUp$11(Node.java:188)
	at org.elasticsearch.xpack.sql.tree.Node.transformUp(Node.java:182)
	at org.elasticsearch.xpack.sql.tree.Node.transformUp(Node.java:188)
	at org.elasticsearch.xpack.sql.planner.QueryFolder$FoldingRule.apply(QueryFolder.java:554)
	at org.elasticsearch.xpack.sql.planner.QueryFolder$FoldingRule.apply(QueryFolder.java:550)
	at org.elasticsearch.xpack.sql.rule.RuleExecutor$Transformation.<init>(RuleExecutor.java:82)
	at org.elasticsearch.xpack.sql.rule.RuleExecutor.executeWithInfo(RuleExecutor.java:155)
	at org.elasticsearch.xpack.sql.rule.RuleExecutor.execute(RuleExecutor.java:130)
	at org.elasticsearch.xpack.sql.planner.QueryFolder.fold(QueryFolder.java:82)
	at org.elasticsearch.xpack.sql.planner.Planner.foldPlan(Planner.java:38)
	at org.elasticsearch.xpack.sql.planner.Planner.plan(Planner.java:28)
	at org.elasticsearch.xpack.sql.session.SqlSession.lambda$physicalPlan$4(SqlSession.java:153)
	at org.elasticsearch.action.ActionListener$1.onResponse(ActionListener.java:60)
	at org.elasticsearch.xpack.sql.session.SqlSession.lambda$optimizedPlan$3(SqlSession.java:149)
	at org.elasticsearch.action.ActionListener$1.onResponse(ActionListener.java:60)
	at org.elasticsearch.xpack.sql.session.SqlSession.lambda$preAnalyze$2(SqlSession.java:137)
	at org.elasticsearch.action.ActionListener$1.onResponse(ActionListener.java:60)
	at org.elasticsearch.xpack.sql.analysis.index.IndexResolver.lambda$resolveAsMergedMapping$3(IndexResolver.java:246)
	at org.elasticsearch.action.ActionListener$1.onResponse(ActionListener.java:60)
	at org.elasticsearch.action.support.ThreadedActionListener$1.doRun(ThreadedActionListener.java:97)
	at org.elasticsearch.common.util.concurrent.ThreadContext$ContextPreservingAbstractRunnable.doRun(ThreadContext.java:759)
	at org.elasticsearch.common.util.concurrent.AbstractRunnable.run(AbstractRunnable.java:37)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
	at java.base/java.lang.Thread.run(Thread.java:834)
@matriv matriv added >bug :Analytics/SQL SQL querying labels Nov 29, 2018
@elasticmachine
Copy link
Collaborator

Pinging @elastic/es-search

@matriv matriv changed the title SQL: Cannot GROUP BY function with field SQL: Cannot GROUP BY when constant function is used Dec 4, 2018
@matriv
Copy link
Contributor Author

matriv commented Dec 4, 2018 •
edited
Loading

Relates to #33361, same issue: ending up with different IDs for the functions after ConstantFolding.

This was referenced May 9, 2019
Closed
Closed
Closed
@matriv
Copy link
Contributor Author

matriv commented Jul 29, 2019

Also:
SELECT PI() * emp_no FROM test_emp GROUP BY PI() * emp_no ORDER BY PI() * emp_no LIMIT 10
throws:

java.sql.SQLDataException: Found 1 problem(s)
    line 1:75: Cannot use non-grouped column [emp_no], expected [PI() * emp_no]

@emasab emasab mentioned this issue Jul 29, 2019
Merged
matriv pushed a commit that referenced this issue Oct 31, 2019
@emasab @matriv
SQL: Failing Group By queries due to different ExpressionIds (#43072)
3c38ea5 
Fix an issue that arises from the use of ExpressionIds as keys in a lookup map
that helps the QueryTranslator to identify the grouping columns. The issue is
that the same expression in different parts of the query (SELECT clause and GROUP BY clause)
ends up with different ExpressionIds so the lookup fails. So, instead of ExpressionIds
use the hashCode() of NamedExpression.

Fixes: #41159
Fixes: #40001
Fixes: #40240
Fixes: #33361
Fixes: #46316
Fixes: #36074
Fixes: #34543
Fixes: #37044

Fixes: #42041
matriv pushed a commit that referenced this issue Oct 31, 2019
@emasab @matriv
SQL: Failing Group By queries due to different ExpressionIds (#43072)
185e067 
Fix an issue that arises from the use of ExpressionIds as keys in a lookup map
that helps the QueryTranslator to identify the grouping columns. The issue is
that the same expression in different parts of the query (SELECT clause and GROUP BY clause)
ends up with different ExpressionIds so the lookup fails. So, instead of ExpressionIds
use the hashCode() of NamedExpression.

Fixes: #41159
Fixes: #40001
Fixes: #40240
Fixes: #33361
Fixes: #46316
Fixes: #36074
Fixes: #34543
Fixes: #37044

Fixes: #42041
(cherry picked from commit 3c38ea5)
matriv pushed a commit that referenced this issue Oct 31, 2019
@emasab @matriv
SQL: Failing Group By queries due to different ExpressionIds (#43072)
6d71682 
Fix an issue that arises from the use of ExpressionIds as keys in a lookup map
that helps the QueryTranslator to identify the grouping columns. The issue is
that the same expression in different parts of the query (SELECT clause and GROUP BY clause)
ends up with different ExpressionIds so the lookup fails. So, instead of ExpressionIds
use the hashCode() of NamedExpression.

Fixes: #41159
Fixes: #40001
Fixes: #40240
Fixes: #33361
Fixes: #46316
Fixes: #36074
Fixes: #34543
Fixes: #37044

Fixes: #42041
(cherry picked from commit 3c38ea5)
matriv pushed a commit that referenced this issue Oct 31, 2019
@emasab @matriv
SQL: Failing Group By queries due to different ExpressionIds (#43072)
bcbba30 
Fix an issue that arises from the use of ExpressionIds as keys in a lookup map
that helps the QueryTranslator to identify the grouping columns. The issue is
that the same expression in different parts of the query (SELECT clause and GROUP BY clause)
ends up with different ExpressionIds so the lookup fails. So, instead of ExpressionIds
use the hashCode() of NamedExpression.

Fixes: #41159
Fixes: #40001
Fixes: #40240
Fixes: #33361
Fixes: #46316
Fixes: #36074
Fixes: #34543
Fixes: #37044

Fixes: #42041
(cherry picked from commit 3c38ea5)
matriv pushed a commit that referenced this issue Oct 31, 2019
@emasab @matriv
SQL: Failing Group By queries due to different ExpressionIds (#43072)
a759375 
Fix an issue that arises from the use of ExpressionIds as keys in a lookup map
that helps the QueryTranslator to identify the grouping columns. The issue is
that the same expression in different parts of the query (SELECT clause and GROUP BY clause)
ends up with different ExpressionIds so the lookup fails. So, instead of ExpressionIds
use the hashCode() of NamedExpression.

Fixes: #41159
Fixes: #40001
Fixes: #40240
Fixes: #33361
Fixes: #46316
Fixes: #36074
Fixes: #34543
Fixes: #37044

Fixes: #42041
(cherry picked from commit 3c38ea5)
This was referenced Feb 3, 2020
Closed
Closed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
:Analytics/SQL SQL querying >bug
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

SQL: Failing Group By queries due to different ExpressionIds emasab/elasticsearch
2 participants
@matriv @elasticmachine