[BUG] SQL describe query with column filter changes response format

[seankao-az]

What is the bug?
The following example uses the opensearch dashboards flights sample data.
The queries I ran are:

• DESCRIBE TABLES LIKE opensearch_dashboards_sample_data_flights COLUMNS LIKE tim%
• DESCRIBE TABLES LIKE opensearch_dashboards_sample_data_flights COLUMNS LIKE time%
• DESCRIBE TABLES LIKE opensearch_dashboards_sample_data_flights COLUMNS LIKE times%

The second one is behaving strangely. The results differ in

• TYPE_NAME. It has date, while it should be timestamp
• ORDINAL_POSITION also changes from 15 to 1
• the "schema" list itself has different "type" for many entries, e.g. integer, short

$ curl -XPOST https://localhost:9200/_plugins/_sql -u 'admin:admin' -k -H 'Content-Type: application/json' -d '{"query": "DESCRIBE TABLES LIKE opensearch_dashboards_sample_data_flights COLUMNS LIKE tim%"}'

{
  "schema": [
    {
      "name": "TABLE_CAT",
      "type": "keyword"
    },
    {
      "name": "TABLE_SCHEM",
      "type": "keyword"
    },
    {
      "name": "TABLE_NAME",
      "type": "keyword"
    },
    {
      "name": "COLUMN_NAME",
      "type": "keyword"
    },
    {
      "name": "DATA_TYPE",
      "type": "keyword"
    },
    {
      "name": "TYPE_NAME",
      "type": "keyword"
    },
    {
      "name": "COLUMN_SIZE",
      "type": "keyword"
    },
    {
      "name": "BUFFER_LENGTH",
      "type": "keyword"
    },
    {
      "name": "DECIMAL_DIGITS",
      "type": "keyword"
    },
    {
      "name": "NUM_PREC_RADIX",
      "type": "keyword"
    },
    {
      "name": "NULLABLE",
      "type": "keyword"
    },
    {
      "name": "REMARKS",
      "type": "keyword"
    },
    {
      "name": "COLUMN_DEF",
      "type": "keyword"
    },
    {
      "name": "SQL_DATA_TYPE",
      "type": "keyword"
    },
    {
      "name": "SQL_DATETIME_SUB",
      "type": "keyword"
    },
    {
      "name": "CHAR_OCTET_LENGTH",
      "type": "keyword"
    },
    {
      "name": "ORDINAL_POSITION",
      "type": "keyword"
    },
    {
      "name": "IS_NULLABLE",
      "type": "keyword"
    },
    {
      "name": "SCOPE_CATALOG",
      "type": "keyword"
    },
    {
      "name": "SCOPE_SCHEMA",
      "type": "keyword"
    },
    {
      "name": "SCOPE_TABLE",
      "type": "keyword"
    },
    {
      "name": "SOURCE_DATA_TYPE",
      "type": "keyword"
    },
    {
      "name": "IS_AUTOINCREMENT",
      "type": "keyword"
    },
    {
      "name": "IS_GENERATEDCOLUMN",
      "type": "keyword"
    }
  ],
  "datarows": [
    [
      "opensearch",
      null,
      "opensearch_dashboards_sample_data_flights",
      "timestamp",
      null,
      "timestamp",
      null,
      null,
      null,
      10,
      2,
      null,
      null,
      null,
      null,
      null,
      15,
      "",
      null,
      null,
      null,
      null,
      "NO",
      ""
    ]
  ],
  "total": 1,
  "size": 1,
  "status": 200
}

$ curl -XPOST https://localhost:9200/_plugins/_sql -u 'admin:admin' -k -H 'Content-Type: application/json' -d '{"query": "DESCRIBE TABLES LIKE opensearch_dashboards_sample_data_flights COLUMNS LIKE time%"}'

{
  "schema": [
    {
      "name": "TABLE_CAT",
      "type": "keyword"
    },
    {
      "name": "TABLE_SCHEM",
      "type": "keyword"
    },
    {
      "name": "TABLE_NAME",
      "type": "keyword"
    },
    {
      "name": "COLUMN_NAME",
      "type": "keyword"
    },
    {
      "name": "DATA_TYPE",
      "type": "integer"
    },
    {
      "name": "TYPE_NAME",
      "type": "keyword"
    },
    {
      "name": "COLUMN_SIZE",
      "type": "integer"
    },
    {
      "name": "BUFFER_LENGTH",
      "type": "integer"
    },
    {
      "name": "DECIMAL_DIGITS",
      "type": "integer"
    },
    {
      "name": "NUM_PREC_RADIX",
      "type": "integer"
    },
    {
      "name": "NULLABLE",
      "type": "integer"
    },
    {
      "name": "REMARKS",
      "type": "keyword"
    },
    {
      "name": "COLUMN_DEF",
      "type": "keyword"
    },
    {
      "name": "SQL_DATA_TYPE",
      "type": "integer"
    },
    {
      "name": "SQL_DATETIME_SUB",
      "type": "integer"
    },
    {
      "name": "CHAR_OCTET_LENGTH",
      "type": "integer"
    },
    {
      "name": "ORDINAL_POSITION",
      "type": "integer"
    },
    {
      "name": "IS_NULLABLE",
      "type": "keyword"
    },
    {
      "name": "SCOPE_CATALOG",
      "type": "keyword"
    },
    {
      "name": "SCOPE_SCHEMA",
      "type": "keyword"
    },
    {
      "name": "SCOPE_TABLE",
      "type": "keyword"
    },
    {
      "name": "SOURCE_DATA_TYPE",
      "type": "short"
    },
    {
      "name": "IS_AUTOINCREMENT",
      "type": "keyword"
    },
    {
      "name": "IS_GENERATEDCOLUMN",
      "type": "keyword"
    }
  ],
  "total": 1,
  "datarows": [[
    "opensearch",
    null,
    "opensearch_dashboards_sample_data_flights",
    "timestamp",
    null,
    "date",
    null,
    null,
    null,
    10,
    2,
    null,
    null,
    null,
    null,
    null,
    1,
    "",
    null,
    null,
    null,
    null,
    "NO",
    ""
  ]],
  "size": 1,
  "status": 200
}

$ curl -XPOST https://localhost:9200/_plugins/_sql -u 'admin:admin' -k -H 'Content-Type: application/json' -d '{"query": "DESCRIBE TABLES LIKE opensearch_dashboards_sample_data_flights COLUMNS LIKE times%"}'

{
  "schema": [
    {
      "name": "TABLE_CAT",
      "type": "keyword"
    },
    {
      "name": "TABLE_SCHEM",
      "type": "keyword"
    },
    {
      "name": "TABLE_NAME",
      "type": "keyword"
    },
    {
      "name": "COLUMN_NAME",
      "type": "keyword"
    },
    {
      "name": "DATA_TYPE",
      "type": "keyword"
    },
    {
      "name": "TYPE_NAME",
      "type": "keyword"
    },
    {
      "name": "COLUMN_SIZE",
      "type": "keyword"
    },
    {
      "name": "BUFFER_LENGTH",
      "type": "keyword"
    },
    {
      "name": "DECIMAL_DIGITS",
      "type": "keyword"
    },
    {
      "name": "NUM_PREC_RADIX",
      "type": "keyword"
    },
    {
      "name": "NULLABLE",
      "type": "keyword"
    },
    {
      "name": "REMARKS",
      "type": "keyword"
    },
    {
      "name": "COLUMN_DEF",
      "type": "keyword"
    },
    {
      "name": "SQL_DATA_TYPE",
      "type": "keyword"
    },
    {
      "name": "SQL_DATETIME_SUB",
      "type": "keyword"
    },
    {
      "name": "CHAR_OCTET_LENGTH",
      "type": "keyword"
    },
    {
      "name": "ORDINAL_POSITION",
      "type": "keyword"
    },
    {
      "name": "IS_NULLABLE",
      "type": "keyword"
    },
    {
      "name": "SCOPE_CATALOG",
      "type": "keyword"
    },
    {
      "name": "SCOPE_SCHEMA",
      "type": "keyword"
    },
    {
      "name": "SCOPE_TABLE",
      "type": "keyword"
    },
    {
      "name": "SOURCE_DATA_TYPE",
      "type": "keyword"
    },
    {
      "name": "IS_AUTOINCREMENT",
      "type": "keyword"
    },
    {
      "name": "IS_GENERATEDCOLUMN",
      "type": "keyword"
    }
  ],
  "datarows": [
    [
      "opensearch",
      null,
      "opensearch_dashboards_sample_data_flights",
      "timestamp",
      null,
      "timestamp",
      null,
      null,
      null,
      10,
      2,
      null,
      null,
      null,
      null,
      null,
      15,
      "",
      null,
      null,
      null,
      null,
      "NO",
      ""
    ]
  ],
  "total": 1,
  "size": 1,
  "status": 200
}

[seankao-az]

When running DESCRIBE TABLES LIKE opensearch_dashboards_sample_data_flights COLUMNS LIKE "time%", the result is correct.
Without the quote around time%, this line throws an exception

sql/legacy/src/main/java/org/opensearch/sql/legacy/plugin/RestSQLQueryAction.java

Line 106 in 127b662

sqlService.parse(request.getQuery())));

org.opensearch.sql.common.antlr.SyntaxCheckException: Failed to parse query due to offending symbol [time] at: 'DESCRIBE TABLES LIKE opensearch_dashboards_sample_data_flights COLUMNS LIKE time' <--- HERE... More details: extraneous input 'time' expecting {'%', STRING_LITERAL, ID, DOUBLE_QUOTE_ID}

[seankao-az]

This error comes from the use of keyword as part of the string literal. Do we actually want to "fix" that? Or do we want to leave it as it is, forcing user to add quotes if the string literal includes keywords?

[dai-chen]

I think this was caused by fallback into legacy code. Labeling it for planning.