sql icon indicating copy to clipboard operation
sql copied to clipboard
verified publisher icon opensearch-project

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

Open seankao-az opened this issue 3 years ago • 3 comments

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 avatar Jun 22 '22 01:06 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 https://github.com/opensearch-project/sql/blob/127b6627f380272da48c21705224802d1b717862/legacy/src/main/java/org/opensearch/sql/legacy/plugin/RestSQLQueryAction.java#L106

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 avatar Jun 22 '22 18:06 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?

seankao-az avatar Jun 23 '22 22:06 seankao-az

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

dai-chen avatar Aug 30 '22 16:08 dai-chen