[BUG] Unhelpful explanation for invalid query

[paulstn]

What is the bug? When attempting to run a query that would select a field of type ip, the approach many people would take would be like the one below:

POST _plugins/_sql
{
  "query": "SELECT * FROM opensearch_dashboards_sample_data_logs WHERE ip = '21.8.113.52'"
}

The result returned would say that its invalid, with the explanation below:

"{\n  \"error\": {\n    \"reason\": \"Invalid SQL query\",\n    \"details\": \"= function expected {[BYTE,BYTE],[SHORT,SHORT],[INTEGER,INTEGER],[LONG,LONG],[FLOAT,FLOAT],[DOUBLE,DOUBLE],[STRING,STRING],[BOOLEAN,BOOLEAN],[DATE,DATE],[TIME,TIME],[DATETIME,DATETIME],[TIMESTAMP,TIMESTAMP],[INTERVAL,INTERVAL],[STRUCT,STRUCT],[ARRAY,ARRAY]}, but get [IP,STRING]\",\n    \"type\": \"ExpressionEvaluationException\"\n  },\n  \"status\": 400\n}"

As it turns out, the best way to actually select a field of type ip would be to use a relevance function, which wasn't made very clear by the error message above.

A better option would be to point users in some way to use a relevance function, if attempting to use the = operator for an unsupported field.

Additionally, I was able to use the IS keyword successfully:

SELECT * FROM opensearch_dashboards_sample_data_logs WHERE ip IS '21.8.113.52'

but I couldn't find any docs about it. Is the IS keyword officially supported, and if so, could it be included in the docs somewhere?

[LantaoJin]

ip IS '21.8.113.52' works because that it fallbacks to old SQL engine due to the IS is an unknown syntax for new engine. Adding ?format=json will fallback to old SQL engine manually, so the query with ip = '21.8.113.52' could work too.

POST _plugins/_sql?format=json
{
  "query": "SELECT * FROM opensearch_dashboards_sample_data_logs WHERE ip = '21.8.113.52'"
}

Since IP is not a ExprCoreType, = function couldn't resolve IP type in query analyzing in new engine. There would be two solutions to fix this problem.

1. In DefaultFunctionResolver.resolve, when NOT_MATCH is triggered, if the function signature contains any non-core types, throw SyntaxCheckException to fallback to old engine.
2. Refactor and move OpenSearchDataType to sql.core module from sql.opensearch module. Enhance current type casting expression.

[dblock]

Catch All Triage - 1 2 3 4 5 6