Group By * Query ERROR

[EveyWu]

SQL By bin/elasticsearch-sql-cli is correct

Test Data：

curl -X PUT "localhost:9200/guide/_bulk?refresh&pretty" -H 'Content-Type: application/json' -d'
{"index":{"_id": "358e4f2c-d796-4634-8f7e-ac014b71ab90"}}
{"regionCode":"440600","itemType":"05","orgName":"微博","id":"110253L3053","title":"热搜","region":"柬埔寨","orgNumber":"53","onlineHandle":"0"}
{"index":{"_id": "9653eedf-ed4d-4ebf-8e48-9cb86a317ca8"}}
{"regionCode":"441323106006","itemType":"21","orgName":"新浪","handleUrl":"http://weibo.com","id":"064421","title":"热度","region":"泰国","orgNumber":"","onlineHandle":"1"}
'

Query:

http://localhost:9200/_opendistro/_sql?sql=SELECT COUNT(*), regionCode FROM guide GROUP BY regionCode

Error :

Internal Server Error. Reason: There was internal problem at backend. Type: IllegalStateException. Details: invalid value operation on MISSING_VALUE
	at com.amazon.opendistroforelasticsearch.jdbc.StatementImpl.executeQueryRequest(StatementImpl.java:78)
	at com.amazon.opendistroforelasticsearch.jdbc.StatementImpl.executeQueryX(StatementImpl.java:61)
	at com.amazon.opendistroforelasticsearch.jdbc.StatementImpl.executeQuery(StatementImpl.java:54)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
	at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
	at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
	at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
	at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
	at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
	at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
	at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
	at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
	at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
	at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
	at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
	at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
	at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68)
	at com.intellij.rt.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:33)
	at com.intellij.rt.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:230)
	at com.intellij.rt.junit.JUnitStarter.main(JUnitStarter.java:58)

ES ERROR LOG

ava.lang.IllegalStateException: invalid value operation on MISSING_VALUE
	at com.amazon.opendistroforelasticsearch.sql.expression.model.ExprValue.value(ExprValue.java:23) ~[opendistro_sql-1.6.0.0.jar:1.6.0.0]
	at com.amazon.opendistroforelasticsearch.sql.executor.format.BindingTupleResultSet.lambda$buildDataRows$1(BindingTupleResultSet.java:55) ~[opendistro_sql-1.6.0.0.jar:1.6.0.0]
	at java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:195) ~[?:?]
	at java.util.ArrayList$ArrayListSpliterator.forEachRemaining(ArrayList.java:1621) ~[?:?]
	at java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:484) ~[?:?]
	at java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:474) ~[?:?]
	at java.util.stream.ReduceOps$ReduceOp.evaluateSequential(ReduceOps.java:913) ~[?:?]
	at java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234) ~[?:?]
	at java.util.stream.ReferencePipeline.collect(ReferencePipeline.java:578) ~[?:?]
	at com.amazon.opendistroforelasticsearch.sql.executor.format.BindingTupleResultSet.buildDataRows(BindingTupleResultSet.java:58) ~[opendistro_sql-1.6.0.0.jar:1.6.0.0]
	at com.amazon.opendistroforelasticsearch.sql.executor.format.BindingTupleResultSet.<init>(BindingTupleResultSet.java:35) ~[opendistro_sql-1.6.0.0.jar:1.6.0.0]
	at com.amazon.opendistroforelasticsearch.sql.executor.format.Protocol.loadResultSet(Protocol.java:78) ~[opendistro_sql-1.6.0.0.jar:1.6.0.0]
	at com.amazon.opendistroforelasticsearch.sql.executor.format.Protocol.<init>(Protocol.java:65) ~[opendistro_sql-1.6.0.0.jar:1.6.0.0]
	at com.amazon.opendistroforelasticsearch.sql.executor.format.PrettyFormatRestExecutor.execute(PrettyFormatRestExecutor.java:71) [opendistro_sql-1.6.0.0.jar:1.6.0.0]
	at com.amazon.opendistroforelasticsearch.sql.executor.format.PrettyFormatRestExecutor.execute(PrettyFormatRestExecutor.java:47) [opendistro_sql-1.6.0.0.jar:1.6.0.0]
	at com.amazon.opendistroforelasticsearch.sql.executor.AsyncRestExecutor.doExecuteWithTimeMeasured(AsyncRestExecutor.java:161) [opendistro_sql-1.6.0.0.jar:1.6.0.0]
	at com.amazon.opendistroforelasticsearch.sql.executor.AsyncRestExecutor.lambda$async$1(AsyncRestExecutor.java:121) [opendistro_sql-1.6.0.0.jar:1.6.0.0]
	at com.amazon.opendistroforelasticsearch.sql.utils.LogUtils.lambda$withCurrentContext$0(LogUtils.java:72) [opendistro_sql-1.6.0.0.jar:1.6.0.0]
	at org.elasticsearch.common.util.concurrent.ThreadContext$ContextPreservingRunnable.run(ThreadContext.java:633) [elasticsearch-7.6.1.jar:7.6.1]
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) [?:?]
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) [?:?]
	at java.lang.Thread.run(Thread.java:830) [?:?]

[EveyWu]

it correct for JSON formatting POST _opendistro/_sql/?format=json，but failed for GET _opendistro/_sql?sql= and JDBC query.

[penghuo]

RCA

Original query SELECT COUNT(*), regionCode FROM guide GROUP BY regionCode
The TermQueryRewriter rewrite the original query to SELECT COUNT(*), regionCode FROM guide GROUP BY regionCode.keyword which violate the SQL 92 standard

SQL-92 and earlier does not permit queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are not named in the GROUP BY clause

[dai-chen]

The issue has been fixed in new query engine (which is experimental and disabled by default). You can enable it to preview by changing the plugin setting: https://github.com/opendistro-for-elasticsearch/sql/blob/master/docs/user/admin/settings.rst#opendistro-sql-engine-new-enabled. Thanks!

[Nirmal1212]

Thanks. It works after enabling the new query engine.

PUT /_opendistro/_sql/settings { "transient" : { "opendistro.sql.engine.new.enabled" : "true" } }