Error when an index name contains a dot midstream

[dai-chen]

I get a similar error when an index name contains a dot midstream.

POST _opendistro/_sql
{
  "query": "SELECT * FROM cwl-foo-bar-baz-2019.10.19 LIMIT 1"
}

It complains about the first dot.

If I surround the index name with backticks I get:

Field [`cwl-foo-bar-baz-2019.10.19`] cannot be found or used here.

Interestingly, it works on indexes with a dot as the first char.

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

Originally posted by @tjbaker in #209 (comment)

[konstantin-kornienko]

Getting same error on SQL plugin 1.4.0 and elasticsearch 7.4.2:

curl -k "http://localhost:9200/_opendistro/_sql?sql=SELECT%20%2A%20from%20access-2019.12.22"

result is:

    "reason": "Invalid SQL query",
    "details": "Failed to parse query due to offending symbol [.12] at: 'SELECT * from access-2019.12' <--- HERE... More details: Expecting tokens in ```

it's strange because looks like there's a test for it:

 @Test
    public void indexWithDotsAndHyphen() throws SqlParseException {
        String query = "select * from data-2015.08.22";
        SQLExpr sqlExpr = queryToExpr(query);
        Select select = parser.parseSelect((SQLQueryExpr) sqlExpr);
        Assert.assertEquals(1, select.getFrom().size());
        Assert.assertEquals("data-2015.08.22", select.getFrom().get(0).getIndex());
    }

[dai-chen]

This could be fixed along with #306

[dai-chen]

We should refer to official doc for index name format: https://www.elastic.co/guide/en/elasticsearch/reference/7.5/indices-create-index.html#indices-create-api-path-params. But one concern for dot in the middle is that some of our code decides if field name is prefixed by table alias or a nested field. We need to make sure this won't mess up the logic behind.

[tjbaker]

Please note that the Cloudwatch Logs subscription Lambda that AWS maintains creates indexes like cwl-xxxxx-YYYY.MM.DD

[timothynode]

is there any temporary workaround for this issue?

[penghuo]

is there any temporary workaround for this issue?

One workaround maybe add aliases, e.g.

# Add Index
PUT /access.log.2020.04.07/_doc/1
{
  "domain":"www.amazon.com",
  "status":200
}

# Add aliases
POST /_aliases
{
    "actions" : [
        { "add" : { "index" : "access.log.2020.04.07", "alias" : "access_log_2020_04_07" } }
    ]
}

# Query with aliases
POST _opendistro/_sql
{
  "query": "SELECT * FROM access_log_2020_04_07"
}

# Result
{
  "schema": [
    {
      "name": "domain",
      "type": "text"
    },
    {
      "name": "status",
      "type": "long"
    }
  ],
  "total": 1,
  "datarows": [[
    "www.amazon.com",
    200
  ]],
  "size": 1,
  "status": 200
}

[timothynode]

is there any temporary workaround for this issue?

One workaround maybe add aliases, e.g.

# Add Index
PUT /access.log.2020.04.07/_doc/1
{
  "domain":"www.amazon.com",
  "status":200
}

# Add aliases
POST /_aliases
{
    "actions" : [
        { "add" : { "index" : "access.log.2020.04.07", "alias" : "access_log_2020_04_07" } }
    ]
}

# Query with aliases
POST _opendistro/_sql
{
  "query": "SELECT * FROM access_log_2020_04_07"
}

# Result
{
  "schema": [
    {
      "name": "domain",
      "type": "text"
    },
    {
      "name": "status",
      "type": "long"
    }
  ],
  "total": 1,
  "datarows": [[
    "www.amazon.com",
    200
  ]],
  "size": 1,
  "status": 200
}

it's hard to add dynamic alias base on date.
can't find any solution.

finding solutions like
PUT _template/xxxx
{
...
"alias":{
"auto-alias-{yyyy}-{mm}-{dd}": ...
}
}
only to see es doesn't support yet..
elastic/elasticsearch#20367

[penghuo]

You can disable the strict semantic check. ref the doc. https://github.com/opendistro-for-elasticsearch/sql/blob/v1.4.0.0/docs/user/admin/settings.rst#opendistro-sql-query-analysis-enabled

# disable semantic check
PUT _cluster/settings
{
  "transient" : {
    "opendistro.sql.query.analysis.enabled" : "false"
  }
}

# run query
POST _opendistro/_sql
{
  "query": "SELECT * FROM access.log.2020.04.07"
}

[timothynode]

cool!
it works...

[dai-chen]

@tjbaker @konstantin-kornienko @timothynode

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 back ticks. We're migrating all existing SQL features to the new engine and will enable new engine when it's ready. Please see the doc for more details: https://github.com/opendistro-for-elasticsearch/sql/blob/develop/docs/user/general/identifiers.rst. Thanks!

POST cwl-foo-bar-baz-2019.10.19/_doc
{
  "name": "hello"
}

POST _opendistro/_sql
{
  "query": "SELECT * FROM `cwl-foo-bar-baz-2019.10.19` "
}

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