Enhance support for CAST and/or CONVERT functions for more data types

[Yury-Fridlyand]

Is your feature request related to a problem? Please describe. Functions CAST and CONVERT are not fully supported. A user should be able to convert data between the following types: integer, double, [var]char, date, datetime, time.

Describe the solution you'd like Support them according to the standard: cast, convert.

Describe alternatives you've considered STR function could be used to convert data into strings, but it also not supported.

Additional context String to float CAST works:

curl -XPOST http://localhost:9200/_plugins/_sql -H 'Content-Type: application/json' -
d '{"query": "SELECT cast(\"25.5\" as float)"}'
{
  "schema": [
    {
      "name": "cast(\"25.5\" as float)",
      "type": "float"
    }
  ],
  "datarows": [
    [
      25.0
    ]
  ],
  "total": 1,
  "size": 1,
  "status": 200
}

But not in vice-versa:

curl -XPOST http://localhost:9200/_plugins/_sql -H 'Content-Type: application/json' -d '{"query": "SELECT cast(25.5 as varchar)"}'
{
  "error": {
    "reason": "There was internal problem at backend",
    "details": "class com.alibaba.druid.sql.ast.expr.SQLNumberExpr cannot be cast to class com.alibaba.druid.sql.ast.expr.SQLIdentifierExpr (com.alibaba.druid.sql.ast.expr.SQLNumberExpr and com.alibaba.druid.sql.ast.expr.SQLIdentifierExpr are in unnamed module of loader java.net.FactoryURLClassLoader @1a01d7f0)",
    "type": "ClassCastException"
  },
  "status": 503
}

CONVERT:

curl -XPOST http://localhost:9200/_plugins/_sql -H 'Content-Type: application/json' -d '{"query": "SELECT convert(varchar, 25.5)"}'
{
  "error": {
    "reason": "Invalid SQL query",
    "details": "Cannot invoke \"com.alibaba.druid.sql.ast.statement.SQLTableSource.getAlias()\" because the return value of \"com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock.getFrom()\" is null",
    "type": "NullPointerException"
  },
  "status": 400
}

curl -XPOST http://localhost:9200/_plugins/_sql -H 'Content-Type: application/json' -d '{"query": "SELECT convert(double, \"25.5\")"}'
{
  "error": {
    "reason": "Invalid SQL query",
    "details": "Cannot invoke \"com.alibaba.druid.sql.ast.statement.SQLTableSource.getAlias()\" because the return value of \"com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock.getFrom()\" is null",
    "type": "NullPointerException"
  },
  "status": 400
}

[ghost]

This issue affects TDVT tests:

• exprtests/standard\setup.cast.str.txt
• exprtests/standard\setup.cast.int.nulls.txt

[dai-chen]

The initial efforts were put on conversion from string to date/bool types:

1. https://github.com/opensearch-project/sql/pull/166
2. https://github.com/opensearch-project/sql/pull/171

More support are needed and thus use this issue to track all further requirements:

1. https://github.com/opensearch-project/sql/issues/571

[Yury-Fridlyand]

See also: #853