Indices with "." in names cause exception but error is never raised.

[jasonrojas]

After attempting to query some date based index names and no results except a 500 from the back-end being returned I found this error in the logs.

c2904330-9c90-4306-96e6-d4335dd6c917 Client side error during query execution com.amazon.opendistroforelasticsearch.sql.legacy.antlr.syntax.SyntaxAnalysisException: Failed to parse query due to offending symbol [.10] at: 'select count(*) from logstash-2020.10' <--- HERE... More details: Expecting tokens in {<EOF>, ';'} at com.amazon.opendistroforelasticsearch.sql.legacy.antlr.syntax.SyntaxAnalysisErrorListener.syntaxError(SyntaxAnalysisErrorListener.java:41) ~[legacy-1.8.0.0.jar:?]

Did not see any warnings about this in the documentation section so I figured I would post this here. I totally get why this is not valid from the dot perspective.

As a side note, the following query works:

select count(*) from .kibana_1;

Similar issues happen with the PPL.

[chloe-zh]

Hi @jasonrojas
Thank you for reporting this. In our current syntax rule, dot(.) would be recognized as a special character or say a delimiter for nested fields, except when it turns up at the starting place like the system index .kibana_1. We would suggest you quote the index name (or any other identifier i.e. field name etc.) using backticks if it contains special characters (., $, ,...) to skip the analysis of the chars in it. Take the query you provided as an example:

select count(*) from `logstash-2020.10`

Please let us know if there's any other question/issue, thanks!

[chloe-zh]

Transferred this issue to SQL repository since sql-workbench repo is deprecated
Link: #805

[jasonrojas]

Just gave the escaping a try and it did not seem to work:

[2020-10-28T19:52:18,740][INFO ][c.a.o.s.l.p.RestSqlAction] [read] [07ecea78-e860-4979-a239-1d40798ace10] Incoming request /_opendistro/_sql: ( SELECT COUNT(*) FROM table ) [2020-10-28T19:52:18,742][ERROR][c.a.o.s.l.p.RestSqlAction] [read] 07ecea78-e860-4979-a239-1d40798ace10 Client side error during query execution com.amazon.opendistroforelasticsearch.sql.legacy.antlr.semantic.SemanticAnalysisException: Field [logstash-2020.10.26] cannot be found or used here.

[chloe-zh]

Could you turn off the semantic analyzer from the devtools or from bash with this command and give it another shot:

curl -H 'Content-Type: application/json' -X PUT localhost:9200/_opendistro/_sql/settings -d '{
  "transient" : {
    "opendistro.sql.query.analysis.enabled" : "false"
  }
}'

[jasonrojas]

similar error:

[d5e92506-d646-43c2-8575-041f27bc8bfe] Incoming request /_opendistro/_sql: ( SELECT COUNT(*) FROM table ) d5e92506-d646-43c2-8575-041f27bc8bfe Client side error during query execution org.elasticsearch.index.IndexNotFoundException: no such index

[jasonrojas]

Here is the full stack:

[2020-10-28T20:46:38,968][ERROR][c.a.o.s.l.p.RestSqlAction] [read] d5e92506-d646-43c2-8575-041f27bc8bfe Client side error during query execution org.elasticsearch.index.IndexNotFoundException: no such index [logstash-2020.10.26] at org.elasticsearch.cluster.metadata.IndexNameExpressionResolver$WildcardExpressionResolver.indexNotFoundException(IndexNameExpressionResolver.java:882) ~[elasticsearch-7.9.1.jar:7.9.1] at org.elasticsearch.cluster.metadata.IndexNameExpressionResolver$WildcardExpressionResolver.innerResolve(IndexNameExpressionResolver.java:819) ~[elasticsearch-7.9.1.jar:7.9.1] at org.elasticsearch.cluster.metadata.IndexNameExpressionResolver$WildcardExpressionResolver.resolve(IndexNameExpressionResolver.java:775) ~[elasticsearch-7.9.1.jar:7.9.1] at org.elasticsearch.cluster.metadata.IndexNameExpressionResolver.concreteIndices(IndexNameExpressionResolver.java:191) ~[elasticsearch-7.9.1.jar:7.9.1] at org.elasticsearch.cluster.metadata.IndexNameExpressionResolver.concreteIndexNames(IndexNameExpressionResolver.java:171) ~[elasticsearch-7.9.1.jar:7.9.1] at org.elasticsearch.cluster.metadata.IndexNameExpressionResolver.concreteIndexNames(IndexNameExpressionResolver.java:103) ~[elasticsearch-7.9.1.jar:7.9.1] at com.amazon.opendistroforelasticsearch.sql.legacy.esdomain.LocalClusterState.resolveIndexExpression(LocalClusterState.java:212) ~[legacy-1.8.0.0.jar:?] at com.amazon.opendistroforelasticsearch.sql.legacy.esdomain.LocalClusterState.getFieldMappings(LocalClusterState.java:191) ~[legacy-1.8.0.0.jar:?] at com.amazon.opendistroforelasticsearch.sql.legacy.esdomain.LocalClusterState.getFieldMappings(LocalClusterState.java:161) ~[legacy-1.8.0.0.jar:?] at com.amazon.opendistroforelasticsearch.sql.legacy.rewriter.matchtoterm.TermFieldRewriter.getMappings(TermFieldRewriter.java:259) ~[legacy-1.8.0.0.jar:?] at com.amazon.opendistroforelasticsearch.sql.legacy.rewriter.matchtoterm.TermFieldRewriter.visit(TermFieldRewriter.java:78) ~[legacy-1.8.0.0.jar:?] at com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock.accept0(MySqlSelectQueryBlock.java:255) ~[druid-1.0.15.jar:1.0.15] at com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock.accept0(MySqlSelectQueryBlock.java:246) ~[druid-1.0.15.jar:1.0.15] at com.alibaba.druid.sql.ast.SQLObjectImpl.accept(SQLObjectImpl.java:40) ~[druid-1.0.15.jar:1.0.15] at com.alibaba.druid.sql.ast.SQLObjectImpl.acceptChild(SQLObjectImpl.java:62) ~[druid-1.0.15.jar:1.0.15] at com.alibaba.druid.sql.ast.statement.SQLSelect.accept0(SQLSelect.java:85) ~[druid-1.0.15.jar:1.0.15] at com.alibaba.druid.sql.ast.SQLObjectImpl.accept(SQLObjectImpl.java:40) ~[druid-1.0.15.jar:1.0.15] at com.alibaba.druid.sql.ast.SQLObjectImpl.acceptChild(SQLObjectImpl.java:62) ~[druid-1.0.15.jar:1.0.15] at com.alibaba.druid.sql.ast.expr.SQLQueryExpr.accept0(SQLQueryExpr.java:55) ~[druid-1.0.15.jar:1.0.15] at com.alibaba.druid.sql.ast.SQLObjectImpl.accept(SQLObjectImpl.java:40) ~[druid-1.0.15.jar:1.0.15] at com.amazon.opendistroforelasticsearch.sql.legacy.query.ESActionFactory.create(ESActionFactory.java:121) ~[legacy-1.8.0.0.jar:?] at com.amazon.opendistroforelasticsearch.sql.legacy.plugin.SearchDao.explain(SearchDao.java:61) ~[legacy-1.8.0.0.jar:?] at com.amazon.opendistroforelasticsearch.sql.legacy.plugin.RestSqlAction.explainRequest(RestSqlAction.java:205) [legacy-1.8.0.0.jar:?] at com.amazon.opendistroforelasticsearch.sql.legacy.plugin.RestSqlAction.prepareRequest(RestSqlAction.java:167) [legacy-1.8.0.0.jar:?] at org.elasticsearch.rest.BaseRestHandler.handleRequest(BaseRestHandler.java:94) [elasticsearch-7.9.1.jar:7.9.1] at com.amazon.opendistroforelasticsearch.security.filter.OpenDistroSecurityRestFilter$1.handleRequest(OpenDistroSecurityRestFilter.java:116) [opendistro_security-1.11.0.0.jar:1.11.0.0] at org.elasticsearch.rest.RestController.dispatchRequest(RestController.java:236) [elasticsearch-7.9.1.jar:7.9.1] at org.elasticsearch.rest.RestController.tryAllHandlers(RestController.java:318) [elasticsearch-7.9.1.jar:7.9.1] at org.elasticsearch.rest.RestController.dispatchRequest(RestController.java:176) [elasticsearch-7.9.1.jar:7.9.1] at org.elasticsearch.http.AbstractHttpServerTransport.dispatchRequest(AbstractHttpServerTransport.java:318) [elasticsearch-7.9.1.jar:7.9.1] at org.elasticsearch.http.AbstractHttpServerTransport.handleIncomingRequest(AbstractHttpServerTransport.java:372) [elasticsearch-7.9.1.jar:7.9.1] at org.elasticsearch.http.AbstractHttpServerTransport.incomingRequest(AbstractHttpServerTransport.java:308) [elasticsearch-7.9.1.jar:7.9.1] at org.elasticsearch.http.netty4.Netty4HttpRequestHandler.channelRead0(Netty4HttpRequestHandler.java:42) [transport-netty4-client-7.9.1.jar:7.9.1] at org.elasticsearch.http.netty4.Netty4HttpRequestHandler.channelRead0(Netty4HttpRequestHandler.java:28) [transport-netty4-client-7.9.1.jar:7.9.1] at io.netty.channel.SimpleChannelInboundHandler.channelRead(SimpleChannelInboundHandler.java:99) [netty-transport-4.1.49.Final.jar:4.1.49.Final] at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379) [netty-transport-4.1.49.Final.jar:4.1.49.Final] at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365) [netty-transport-4.1.49.Final.jar:4.1.49.Final] at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357) [netty-transport-4.1.49.Final.jar:4.1.49.Final] at org.elasticsearch.http.netty4.Netty4HttpPipeliningHandler.channelRead(Netty4HttpPipeliningHandler.java:58) [transport-netty4-client-7.9.1.jar:7.9.1] at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379) [netty-transport-4.1.49.Final.jar:4.1.49.Final] at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365) [netty-transport-4.1.49.Final.jar:4.1.49.Final] at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357) [netty-transport-4.1.49.Final.jar:4.1.49.Final] at io.netty.handler.codec.MessageToMessageDecoder.channelRead(MessageToMessageDecoder.java:103) [netty-codec-4.1.49.Final.jar:4.1.49.Final] at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379) [netty-transport-4.1.49.Final.jar:4.1.49.Final] at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365) [netty-transport-4.1.49.Final.jar:4.1.49.Final] at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357) [netty-transport-4.1.49.Final.jar:4.1.49.Final] at io.netty.handler.codec.MessageToMessageDecoder.channelRead(MessageToMessageDecoder.java:103) [netty-codec-4.1.49.Final.jar:4.1.49.Final] at io.netty.handler.codec.MessageToMessageCodec.channelRead(MessageToMessageCodec.java:111) [netty-codec-4.1.49.Final.jar:4.1.49.Final] at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379) [netty-transport-4.1.49.Final.jar:4.1.49.Final] at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365) [netty-transport-4.1.49.Final.jar:4.1.49.Final] at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357) [netty-transport-4.1.49.Final.jar:4.1.49.Final] at io.netty.handler.codec.MessageToMessageDecoder.channelRead(MessageToMessageDecoder.java:103) [netty-codec-4.1.49.Final.jar:4.1.49.Final] at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379) [netty-transport-4.1.49.Final.jar:4.1.49.Final] at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365) [netty-transport-4.1.49.Final.jar:4.1.49.Final] at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357) [netty-transport-4.1.49.Final.jar:4.1.49.Final] at io.netty.handler.codec.MessageToMessageDecoder.channelRead(MessageToMessageDecoder.java:103) [netty-codec-4.1.49.Final.jar:4.1.49.Final] at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379) [netty-transport-4.1.49.Final.jar:4.1.49.Final] at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365) [netty-transport-4.1.49.Final.jar:4.1.49.Final] at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357) [netty-transport-4.1.49.Final.jar:4.1.49.Final] at io.netty.handler.codec.ByteToMessageDecoder.fireChannelRead(ByteToMessageDecoder.java:324) [netty-codec-4.1.49.Final.jar:4.1.49.Final] at io.netty.handler.codec.ByteToMessageDecoder.channelRead(ByteToMessageDecoder.java:296) [netty-codec-4.1.49.Final.jar:4.1.49.Final] at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379) [netty-transport-4.1.49.Final.jar:4.1.49.Final] at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365) [netty-transport-4.1.49.Final.jar:4.1.49.Final] at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357) [netty-transport-4.1.49.Final.jar:4.1.49.Final] at io.netty.handler.timeout.IdleStateHandler.channelRead(IdleStateHandler.java:286) [netty-handler-4.1.49.Final.jar:4.1.49.Final] at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379) [netty-transport-4.1.49.Final.jar:4.1.49.Final] at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365) [netty-transport-4.1.49.Final.jar:4.1.49.Final] at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357) [netty-transport-4.1.49.Final.jar:4.1.49.Final] at io.netty.channel.DefaultChannelPipeline$HeadContext.channelRead(DefaultChannelPipeline.java:1410) [netty-transport-4.1.49.Final.jar:4.1.49.Final] at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379) [netty-transport-4.1.49.Final.jar:4.1.49.Final] at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365) [netty-transport-4.1.49.Final.jar:4.1.49.Final] at io.netty.channel.DefaultChannelPipeline.fireChannelRead(DefaultChannelPipeline.java:919) [netty-transport-4.1.49.Final.jar:4.1.49.Final] at io.netty.channel.nio.AbstractNioByteChannel$NioByteUnsafe.read(AbstractNioByteChannel.java:163) [netty-transport-4.1.49.Final.jar:4.1.49.Final] at io.netty.channel.nio.NioEventLoop.processSelectedKey(NioEventLoop.java:714) [netty-transport-4.1.49.Final.jar:4.1.49.Final] at io.netty.channel.nio.NioEventLoop.processSelectedKeysPlain(NioEventLoop.java:615) [netty-transport-4.1.49.Final.jar:4.1.49.Final] at io.netty.channel.nio.NioEventLoop.processSelectedKeys(NioEventLoop.java:578) [netty-transport-4.1.49.Final.jar:4.1.49.Final] at io.netty.channel.nio.NioEventLoop.run(NioEventLoop.java:493) [netty-transport-4.1.49.Final.jar:4.1.49.Final] at io.netty.util.concurrent.SingleThreadEventExecutor$4.run(SingleThreadEventExecutor.java:989) [netty-common-4.1.49.Final.jar:4.1.49.Final] at io.netty.util.internal.ThreadExecutorMap$2.run(ThreadExecutorMap.java:74) [netty-common-4.1.49.Final.jar:4.1.49.Final] at java.lang.Thread.run(Thread.java:832) [?:?]

[tjbaker]

Also see #322

[jasonrojas]

Seems like #322 is closed, but this issue still exists in the latest build.

[dai-chen]

@jasonrojas Sorry for the confusion. I guess this is because COUNT(*) support was not there in our new engine. This caused your query fall back to current engine impl which still has the issue. Here is the PR for COUNT(*/literal) support in the new engine: #802.

For now, you can try the suggestion that @chloe-zh gave earlier with our develop branch. It works for me now. Or wait for next release build which includes the PR changes aforementioned.

select count(*) from `logstash-2020.10`

Reference: https://github.com/opendistro-for-elasticsearch/sql/blob/develop/docs/user/general/identifiers.rst#delimited-identifiers

[dai-chen]

Closing. Feel free to reopen if anything else. Thanks.