[BUG] Fix `CAST ( ... AS DATETIME)`

[Yury-Fridlyand]

How can one reproduce the bug?

opensearchsql> SELECT CAST(TIMESTAMP('2000-01-02 00:00:00') AS DATETIME);
{'reason': 'Invalid SQL query', 'details': '', 'type': 'NullPointerException'}
opensearchsql> SELECT CAST('2000-01-02 00:00:00' AS DATETIME);
{'reason': 'Invalid SQL query', 'details': '', 'type': 'NullPointerException'}

Exception comes from legacy engine. V2 error:

line 1:37 mismatched input 'DATETIME' expecting {'BOOLEAN', 'DOUBLE', 'FLOAT', 'INT', 'INTEGER', 'LONG', 'STRING', 'DATE', 'TIME', 'TIMESTAMP'}

https://github.com/opensearch-project/sql/blob/fa8d5bdc48dca064db22e189225f16c42d0c4175/sql/src/main/antlr/OpenSearchSQLParser.g4#L342-L353

I tried to fix this

diff --git a/sql/src/main/antlr/OpenSearchSQLParser.g4 b/sql/src/main/antlr/OpenSearchSQLParser.g4
index f94834bb..054a16db 100644
--- a/sql/src/main/antlr/OpenSearchSQLParser.g4
+++ b/sql/src/main/antlr/OpenSearchSQLParser.g4
@@ -341,6 +341,7 @@ multiFieldRelevanceFunction

 convertedDataType
     : typeName=DATE
+    | typeName=DATETIME
     | typeName=TIME
     | typeName=TIMESTAMP
     | typeName=INT

Unfortunately, this fix damages few ITs (test report):

SQLFunctionsIT.castFieldToDatetimeWithWhereClauseJdbcFormatTest
SQLFunctionsIT.castKeywordFieldToDatetimeWithAliasJdbcFormatTest
SQLFunctionsIT.castKeywordFieldToDatetimeWithoutAliasJdbcFormatTest
SQLFunctionsIT.castStatementInWhereClauseDatetimeCastTest

For example, error log from one of these tests:

org.opensearch.sql.legacy.SQLFunctionsIT > castKeywordFieldToDatetimeWithAliasJdbcFormatTest FAILED
    java.lang.RuntimeException: org.opensearch.client.ResponseException: method [POST], host [http://[::1]:42733], URI [/_plugins/_sql?format=jdbc], status line [HTTP/1.1 500 Internal Server Error]
    {
      "error": {
        "type": "SemanticCheckException",
        "reason": "Invalid Query",
        "details": "datetime:2014-08-19T07:09:13.434Z in unsupported format, please use yyyy-MM-dd HH:mm:ss[.SSSSSSSSS]"
      },
      "status": 400
    }

The test itself: https://github.com/opensearch-project/sql/blob/7bb52a8fd1fa00fe16f5a71c14c2a8e836c9b8e1/integ-test/src/test/java/org/opensearch/sql/legacy/SQLFunctionsIT.java#L332 So, CAST( ... AS DATETIME) partially works even now, but proposed fix might be a breaking change.

What is the expected behavior?

opensearchsql> SELECT CAST('2000-01-02 00:00:00' AS DATETIME);
fetched rows / total rows = 1/1
+-------------------------------------------+
| CAST('2000-01-02 00:00:00' AS DATETIME)   |
|-------------------------------------------|
| 2000-01-02 00:00:00                       |
+-------------------------------------------+
opensearchsql> SELECT CAST(TIMESTAMP('2000-01-02 00:00:00') AS DATETIME);
fetched rows / total rows = 1/1
+------------------------------------------------------+
| CAST(TIMESTAMP('2000-01-02 00:00:00') AS DATETIME)   |
|------------------------------------------------------|
| 2000-01-02 00:00:00                                  |
+------------------------------------------------------+

What is your host/environment?

2.x @ fa8d5bdc