SQL: GROUP BY with function group returns error #40001

bpintea · 2019-03-13T15:57:48Z

This following statement works as expected:
select convert(truncate(extract(YEAR from "timestamp")), SQL_BIGINT) as yr from "kibana_sample_data_flights" group by yr;

When the yr is swapped with the group expression it replaces, an exception is raised:

sql> select convert(truncate(extract(YEAR from "timestamp")), SQL_BIGINT) as yr from "kibana_sample_data_flights" group by convert(truncate(extract(YEAR from "timestamp")), SQL_BIGINT);
Server error [Server sent bad type [folding_exception]. Original type was [line 1:8: Cannot find grouping for 'convert(truncate(extract(YEAR from "timestamp")), SQL_BIGINT)']. [FoldingException[line 1:8: Cannot find grouping for 'convert(truncate(extract(YEAR from "timestamp")), SQL_BIGINT)']
        at org.elasticsearch.xpack.sql.planner.QueryFolder$FoldAggregate.rule(QueryFolder.java:321)
        at org.elasticsearch.xpack.sql.planner.QueryFolder$FoldAggregate.rule(QueryFolder.java:206)
        at org.elasticsearch.xpack.sql.tree.Node.lambda$transformUp$11(Node.java:196)
        at org.elasticsearch.xpack.sql.tree.Node.transformUp(Node.java:190)
        at org.elasticsearch.xpack.sql.tree.Node.lambda$transformUp$10(Node.java:188)
        at org.elasticsearch.xpack.sql.tree.Node.transformChildren(Node.java:209)
        at org.elasticsearch.xpack.sql.tree.Node.transformUp(Node.java:188)
        at org.elasticsearch.xpack.sql.tree.Node.transformUp(Node.java:196)
        at org.elasticsearch.xpack.sql.planner.QueryFolder$FoldingRule.apply(QueryFolder.java:589)
        at org.elasticsearch.xpack.sql.planner.QueryFolder$FoldingRule.apply(QueryFolder.java:585)
        at org.elasticsearch.xpack.sql.rule.RuleExecutor$Transformation.<init>(RuleExecutor.java:82)
        at org.elasticsearch.xpack.sql.rule.RuleExecutor.executeWithInfo(RuleExecutor.java:158)
        at org.elasticsearch.xpack.sql.rule.RuleExecutor.execute(RuleExecutor.java:130)
        at org.elasticsearch.xpack.sql.planner.QueryFolder.fold(QueryFolder.java:85)
        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:156)
        at org.elasticsearch.action.ActionListener$1.onResponse(ActionListener.java:62)
        at org.elasticsearch.xpack.sql.session.SqlSession.lambda$optimizedPlan$3(SqlSession.java:152)
        at org.elasticsearch.action.ActionListener$1.onResponse(ActionListener.java:62)
        at org.elasticsearch.xpack.sql.session.SqlSession.lambda$preAnalyze$2(SqlSession.java:140)
        at org.elasticsearch.action.ActionListener$1.onResponse(ActionListener.java:62)
        at org.elasticsearch.xpack.sql.analysis.index.IndexResolver.lambda$resolveAsMergedMapping$3(IndexResolver.java:245)
        at org.elasticsearch.action.ActionListener$1.onResponse(ActionListener.java:62)
        at org.elasticsearch.action.support.TransportAction$1.onResponse(TransportAction.java:68)
        at org.elasticsearch.action.support.TransportAction$1.onResponse(TransportAction.java:64)
        at org.elasticsearch.action.support.ContextPreservingActionListener.onResponse(ContextPreservingActionListener.java:43)
        at org.elasticsearch.action.fieldcaps.TransportFieldCapabilitiesAction.lambda$doExecute$1(TransportFieldCapabilitiesAction.java:84)
        at org.elasticsearch.action.fieldcaps.TransportFieldCapabilitiesAction$1.onResponse(TransportFieldCapabilitiesAction.java:97)
        at org.elasticsearch.action.fieldcaps.TransportFieldCapabilitiesAction$1.onResponse(TransportFieldCapabilitiesAction.java:93)
        at org.elasticsearch.action.support.TransportAction$1.onResponse(TransportAction.java:68)
        at org.elasticsearch.action.support.TransportAction$1.onResponse(TransportAction.java:64)
        at org.elasticsearch.action.support.ContextPreservingActionListener.onResponse(ContextPreservingActionListener.java:43)
        at org.elasticsearch.action.support.single.shard.TransportSingleShardAction$AsyncSingleAction$2.handleResponse(TransportSingleShardAction.java:271)
        at org.elasticsearch.action.support.single.shard.TransportSingleShardAction$AsyncSingleAction$2.handleResponse(TransportSingleShardAction.java:255)
        at org.elasticsearch.transport.TransportService$ContextRestoreResponseHandler.handleResponse(TransportService.java:1108)
        at org.elasticsearch.transport.TransportService$DirectResponseChannel.processResponse(TransportService.java:1189)
        at org.elasticsearch.transport.TransportService$DirectResponseChannel.sendResponse(TransportService.java:1169)
        at org.elasticsearch.transport.TaskTransportChannel.sendResponse(TaskTransportChannel.java:54)
        at org.elasticsearch.action.support.ChannelActionListener.onResponse(ChannelActionListener.java:47)
        at org.elasticsearch.action.support.ChannelActionListener.onResponse(ChannelActionListener.java:30)
        at org.elasticsearch.action.support.single.shard.TransportSingleShardAction$1.doRun(TransportSingleShardAction.java:117)
        at org.elasticsearch.common.util.concurrent.ThreadContext$ContextPreservingAbstractRunnable.doRun(ThreadContext.java:751)
        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)
]]

The text was updated successfully, but these errors were encountered:

elasticmachine · 2019-03-13T15:57:49Z

Pinging @elastic/es-search

astefan · 2019-03-19T10:03:21Z

After some digging, this is specifically related to binary functions (ROUND, TRUNCATE) that have the second parameter optional. No other math function or string function has this issue.

astefan · 2019-03-22T06:32:16Z

master (8.0.0): 3e314f8
7.x (7.1.0): 35fe053
7.0 (7.0.0): b0d9017
6.7 (6.7.1): ac8ef99
6.6 (6.6.3): 0049f1c

bpintea · 2019-03-22T11:38:40Z

Just had a re-run of the query with a fresh build(build[default/zip/4b45c1f/2019-03-22T11:14:28.484336600Z]) and seems to still fail. I'm probably missing something, let me know please.

sql> select convert(truncate(extract(YEAR from "timestamp")), SQL_BIGINT) from "kibana_sample_data_flights" group by convert(truncate(extract(YEAR from "timestamp")), SQL_BIGINT);
Server error [Server sent bad type [folding_exception]. Original type was [line 1:8: Cannot find grouping for 'convert(truncate(extract(YEAR from "timestamp")), SQL_BIGINT)']. [FoldingException[line 1:8: Cannot find grouping for 'convert(truncate(extract(YEAR from "timestamp")), SQL_BIGINT)']
        at org.elasticsearch.xpack.sql.planner.QueryFolder$FoldAggregate.rule(QueryFolder.java:319)
        at org.elasticsearch.xpack.sql.planner.QueryFolder$FoldAggregate.rule(QueryFolder.java:203)
        at org.elasticsearch.xpack.sql.tree.Node.lambda$transformUp$11(Node.java:196)
        at org.elasticsearch.xpack.sql.tree.Node.transformUp(Node.java:190)
        at org.elasticsearch.xpack.sql.tree.Node.transformUp(Node.java:196)
        at org.elasticsearch.xpack.sql.planner.QueryFolder$FoldingRule.apply(QueryFolder.java:584)
        at org.elasticsearch.xpack.sql.planner.QueryFolder$FoldingRule.apply(QueryFolder.java:580)
        at org.elasticsearch.xpack.sql.rule.RuleExecutor$Transformation.<init>(RuleExecutor.java:82)
        at org.elasticsearch.xpack.sql.rule.RuleExecutor.executeWithInfo(RuleExecutor.java:158)
        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:156)
        at org.elasticsearch.action.ActionListener$1.onResponse(ActionListener.java:62)
        at org.elasticsearch.xpack.sql.session.SqlSession.lambda$optimizedPlan$3(SqlSession.java:152)
        at org.elasticsearch.action.ActionListener$1.onResponse(ActionListener.java:62)
        at org.elasticsearch.xpack.sql.session.SqlSession.lambda$preAnalyze$2(SqlSession.java:140)
        at org.elasticsearch.action.ActionListener$1.onResponse(ActionListener.java:62)
        at org.elasticsearch.xpack.sql.analysis.index.IndexResolver.lambda$resolveAsMergedMapping$3(IndexResolver.java:245)
        at org.elasticsearch.action.ActionListener$1.onResponse(ActionListener.java:62)
        at org.elasticsearch.action.support.TransportAction$1.onResponse(TransportAction.java:68)
        at org.elasticsearch.action.support.TransportAction$1.onResponse(TransportAction.java:64)
        at org.elasticsearch.action.support.ContextPreservingActionListener.onResponse(ContextPreservingActionListener.java:43)
        at org.elasticsearch.action.fieldcaps.TransportFieldCapabilitiesAction.lambda$doExecute$1(TransportFieldCapabilitiesAction.java:84)
        at org.elasticsearch.action.fieldcaps.TransportFieldCapabilitiesAction$1.onResponse(TransportFieldCapabilitiesAction.java:97)
        at org.elasticsearch.action.fieldcaps.TransportFieldCapabilitiesAction$1.onResponse(TransportFieldCapabilitiesAction.java:93)
        at org.elasticsearch.action.support.TransportAction$1.onResponse(TransportAction.java:68)
        at org.elasticsearch.action.support.TransportAction$1.onResponse(TransportAction.java:64)
        at org.elasticsearch.action.support.ContextPreservingActionListener.onResponse(ContextPreservingActionListener.java:43)
        at org.elasticsearch.action.support.single.shard.TransportSingleShardAction$AsyncSingleAction$2.handleResponse(TransportSingleShardAction.java:271)
        at org.elasticsearch.action.support.single.shard.TransportSingleShardAction$AsyncSingleAction$2.handleResponse(TransportSingleShardAction.java:255)
        at org.elasticsearch.transport.TransportService$ContextRestoreResponseHandler.handleResponse(TransportService.java:1095)
        at org.elasticsearch.transport.TransportService$DirectResponseChannel.processResponse(TransportService.java:1176)
        at org.elasticsearch.transport.TransportService$DirectResponseChannel.sendResponse(TransportService.java:1156)
        at org.elasticsearch.transport.TaskTransportChannel.sendResponse(TaskTransportChannel.java:54)
        at org.elasticsearch.action.support.ChannelActionListener.onResponse(ChannelActionListener.java:47)
        at org.elasticsearch.action.support.ChannelActionListener.onResponse(ChannelActionListener.java:30)
        at org.elasticsearch.action.support.single.shard.TransportSingleShardAction$1.doRun(TransportSingleShardAction.java:117)
        at org.elasticsearch.common.util.concurrent.ThreadContext$ContextPreservingAbstractRunnable.doRun(ThreadContext.java:747)
        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)
]]

astefan · 2019-03-22T11:47:28Z

I hope I'm not mistaken, but this is a second bug, strictly related to CAST/CONVERT: #40240. ROUND and TRUNCATE were failing not only in a CONVERT combination but with other functions as well. And, at the same time, CAST/CONVERT fail with other functions as well, not only ROUND and TRUNCATE. I chose to close this issue after making it clear it's ROUND/TRUNCATE specific, but forgot to detail this some more and link the second bug I created. Sorry about that.

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

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)

bpintea added the :Analytics/SQL SQL querying label Mar 13, 2019

astefan added the >bug label Mar 18, 2019

astefan self-assigned this Mar 18, 2019

astefan mentioned this issue Mar 20, 2019

SQL: rewrite ROUND and TRUNCATE functions with a different optional parameter handling method #40242

Merged

astefan closed this as completed in #40242 Mar 21, 2019

astefan added v7.0.0 v8.0.0 v7.2.0 v6.6.3 v6.7.1 labels Mar 22, 2019

bpintea reopened this Mar 22, 2019

colings86 added v6.7.2 and removed v6.7.1 labels Mar 30, 2019

$@polyfractal$ polyfractal removed the v7.0.0 label Apr 9, 2019

colings86 added v6.7.3 and removed v6.7.2 labels Apr 17, 2019

jakelandis added v6.8.0 and removed v6.7.3 labels May 3, 2019

matriv mentioned this issue May 9, 2019

SQL: Failing Group By queries due to different ExpressionIds #42041

Closed

8 tasks

jakelandis added v6.8.1 and removed v6.8.0 labels May 19, 2019

jakelandis added v7.3.0 v6.8.2 and removed v7.2.0 v6.8.1 labels Jun 17, 2019

jpountz added v7.4.0 and removed v7.3.0 labels Jul 3, 2019

jakelandis added v6.8.3 and removed v6.8.2 labels Jul 24, 2019

emasab mentioned this issue Jul 29, 2019

SQL: Failing Group By queries due to different ExpressionIds #43072

Merged

pcsanwald added v6.8.4 and removed v6.8.3 labels Aug 29, 2019

colings86 added v7.5.0 and removed v7.4.0 labels Aug 30, 2019

tomcallahan added v6.8.5 and removed v6.8.4 labels Oct 22, 2019

matriv closed this as completed in #43072 Oct 31, 2019

This was referenced Feb 3, 2020

[meta] 7.6 release elastic/elasticsearch-net#4340

Closed

[meta] 7.6 release elastic/elasticsearch-net#4341

Closed

jakelandis added v8.0.0-alpha1 and removed v8.0.0 labels Jul 26, 2021

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

SQL: GROUP BY with function group returns error #40001

SQL: GROUP BY with function group returns error #40001

bpintea commented Mar 13, 2019 •

edited

Loading

elasticmachine commented Mar 13, 2019

astefan commented Mar 19, 2019

astefan commented Mar 22, 2019

bpintea commented Mar 22, 2019

astefan commented Mar 22, 2019 •

edited

Loading

SQL: GROUP BY with function group returns error #40001

SQL: GROUP BY with function group returns error #40001

Comments

bpintea commented Mar 13, 2019 • edited Loading

elasticmachine commented Mar 13, 2019

astefan commented Mar 19, 2019

astefan commented Mar 22, 2019

bpintea commented Mar 22, 2019

astefan commented Mar 22, 2019 • edited Loading

bpintea commented Mar 13, 2019 •

edited

Loading

astefan commented Mar 22, 2019 •

edited

Loading