Special alias name cannot pass the old Druid and new ANTLR parser

[dai-chen]

The following query with field alias 843d-8ab6f-ad74ed5-a47673781bb959ae will fail the old Druid parser.

POST _opendistro/_sql
{
  "query": "SELECT age 843d-8ab6f-ad74ed5-a47673781bb959ae from accounts "
}

{
  "error": {
    "reason": "Invalid SQL query",
    "details": "illegal sql expr : SELECT age 843d-8ab6f-ad74ed5-a47673781bb959ae from accounts",
    "type": "ParserException"
  },
  "status": 400
}

But replacing string between dash with single letter can pass the old Druid parser.

POST _opendistro/_sql
{
  "query": "SELECT age a-b-c-d from accounts "
}

The alias above can pass our new ANTLR parser. However, new parser seems not allow dot in alias name. For example:

POST _opendistro/_sql
{
  "query": "SELECT age 843d-8ab6f-ad74ed5-a47673781bb959ae.abc from accounts "
}

{
  "error": {
    "reason": "Invalid SQL query",
    "details": "Failed to parse query due to offending symbol [.abc] at: 'SELECT age 843d-8ab6f-ad74ed5-a47673781bb959ae.abc' <--- HERE... More details: Expecting tokens in {'SELECT', '('}",
    "type": "SyntaxAnalysisException"
  },
  "status": 400
}

[tjbaker]

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"
}

[abbashus]

Thanks @tjbaker , we will take a look into the discrepancy. What version of plugin are you using?

[tjbaker]

aws elasticsearch R20190927-P3

GET /_cat/plugins
861fb1da31e663010ed0e3c4199a0a4b opendistro_sql x.x.x.x

[abbashus]

Can you also provide the Elasticsearch version, that will help us to map correct opendistro SQL plugin version.

[tjbaker]

{
  "name" : "861fb1da31e663010ed0e3c4199a0a4b",
  "cluster_name" : "xxxx",
  "cluster_uuid" : "xxxx",
  "version" : {
    "number" : "7.1.1",
    "build_flavor" : "oss",
    "build_type" : "tar",
    "build_hash" : "7a013de",
    "build_date" : "2019-09-05T07:25:23.525600Z",
    "build_snapshot" : false,
    "lucene_version" : "8.0.0",
    "minimum_wire_compatibility_version" : "6.8.0",
    "minimum_index_compatibility_version" : "6.0.0-beta1"
  },
  "tagline" : "You Know, for Search"
}

[dai-chen]

@tjbaker I'm creating a new issue #322 for tracking.

[dai-chen]

Hi @Jianfei-Li, I found out that the fix for #212 could be a workaround for this issue. Basically we added support for quoted table or field name with special character by backticks. And this seem work well for alias too. The following queries worked for me. Please try it out from your side too:

POST _opendistro/_sql
{
  "query": "SELECT age AS `843d-8ab6f-ad74ed5-a47673781bb959ae` from accounts "
}

POST _opendistro/_sql
{
  "query": "SELECT age `843d-8ab6f-ad74ed5-a47673781bb959ae.abc` from accounts "
}

[dai-chen]

Hi @tjbaker, I tested and the backticks didn't work for your case. Because of dot in index name and our support for PartiQL syntax, we need to figure out better way to differentiate regular index and nested field in regular index, ex. SELECT * FROM team t, t.project p in which t.project is causing the problem. Will investigate the issue #322 separately.

[dai-chen]

Fixed this in new query engine in the same way. Backticks or double quotes are required due to alias name starting with number. Please see more details: https://github.com/opendistro-for-elasticsearch/sql/blob/master/docs/user/general/identifiers.rst.