Feature request: support index name contains "."

[seraphjiang]

Use case

We have following index we want to make sql query.

dc.meta-data
dc.all-data
.meta

I have tried following queries.
It only works when I use widecard index pattern. it doesn't work if I want query use extra index name.
Tried to use [] to enclosure the index name, still doesn't work

-- works
SELECT * FROM  [dc*];
SELECT * FROM  dc*;
SELECT * FROM  .meta;
  
--doesn't work, but expect work
SELECT * FROM  [dc.meta-data];
SELECT * FROM  dc.meta;

Index [dc.meta-data]

Error message

[elasticsearch] SemanticAnalysisException: Field [dc.domain-config-latest-version] cannot be found or used here.

[elasticsearch] com.amazon.opendistroforelasticsearch.sql.antlr.semantic.SemanticAnalysisException: Field [dc.domain-config-latest-version] cannot be found or used here.
[elasticsearch]         at com.amazon.opendistroforelasticsearch.sql.antlr.semantic.visitor.TypeChecker.resolve(TypeChecker.java:218) ~[opendistro_sql-1.4.0.0.jar:1.4.0.0]
[elasticsearch]         at com.amazon.opendistroforelasticsearch.sql.antlr.semantic.visitor.TypeChecker.visitIndexName(TypeChecker.java:122) ~[opendistro_sql-1.4.0.0.jar:1.4.0.0]
[elasticsearch]         at com.amazon.opendistroforelasticsearch.sql.antlr.semantic.visitor.SemanticAnalyzer.visitIndexName(SemanticAnalyzer.java:73) ~[opendistro_sql-1.4.0.0.jar:1.4.0.0]

Index dc.*

Error message

'select * from dc.*' <--- HERE... More details: Expecting tokens in {'SELECT', '('}

[elasticsearch] com.amazon.opendistroforelasticsearch.sql.antlr.syntax.SyntaxAnalysisException: Failed to parse query due to offending symbol [*] at: 'select * from dc.*' <--- HERE... More details: Expecting tokens in {'SELECT', '('}
[elasticsearch]         at com.amazon.opendistroforelasticsearch.sql.antlr.syntax.SyntaxAnalysisErrorListener.syntaxError(SyntaxAnalysisErrorListener.java:41) ~[opendistro_sql-1.4.0.0.jar:1.4.0.0]
[elasticsearch]         at org.antlr.v4.runtime.ProxyErrorListener.syntaxError(ProxyErrorListener.java:41) ~[antlr4-runtime-4.7.1.jar:4.7.1]
[elasticsearch]         at org.antlr.v4.runtime.Parser.notifyErrorListeners(Parser.java:544) ~[antlr4-runtime-4.7.1.jar:4.7.1]
[elasticsearch]         at org.antlr.v4.runtime.DefaultErrorStrategy.reportNoViableAlternative(DefaultErrorStrategy.java:310) ~[antlr4-runtime-4.7.1.jar:4.7.1]
[elasticsearch]         at org.antlr.v4.runtime.DefaultErrorStrategy.reportError(DefaultErrorStrategy.java:136) ~[antlr4-runtime-4.7.1.jar:4.7.1]
[elasticsearch]         at com.amazon.opendistroforelasticsearch.sql.antlr.parser.OpenDistroSqlParser.selectStatement(OpenDistroSqlParser.java:629) ~[opendistro_sql-1.4.0.0.jar:1.4.0.0]

version

1.4.0.0

[dai-chen]

@seraphjiang Thanks for your patience! This issue is already fixed in our new query engine. For now we can support SELECT * with index name with special character by double quotes or backticks. We're migrating all existing SQL features to the new engine. Please see the doc for more details: https://github.com/opendistro-for-elasticsearch/sql/blob/develop/docs/user/general/identifiers.rst. Thanks!

PUT dc.meta/_doc/1
{
  "name": "hello"
}

PUT dc.meta2/_doc/1
{
  "name": "world"
}
{
  "schema": [{
    "name": "name",
    "type": "string"
  }],
  "total": 1,
  "datarows": [["hello"]],
  "size": 1
}

Index pattern query:

POST _opendistro/_sql
{
  "query": """SELECT * FROM "dc.*" """
}

{
  "schema": [{
    "name": "name",
    "type": "string"
  }],
  "total": 2,
  "datarows": [
    ["hello"],
    ["world"]
  ],
  "size": 2
}

Hidden indices query:

POST _opendistro/_sql
{
  "query": "SELECT * FROM .kibana"
}

[seraphjiang]

This is awesome, thanks