SQL: group by converted date doesn't work #37023
Description
Test data:
POST localhost:9200/calcs/doc/_bulk
{"index":{}}
{"date2":"1977-04-20","date0":"2004-04-15"}
{"index":{}}
{"date2":"1995-09-03","date0":"1972-07-04"}
{"index":{}}
{"date2":"1997-09-19","date0":"1975-11-12"}
{"index":{}}
{"date2":"1980-07-26","date0":"2004-06-04"}
{"index":{}}
{"date2":"1997-05-30","date0":"2004-06-19"}
Query: SELECT {fn CONVERT(date0, SQL_DOUBLE)} AS x FROM calcs GROUP BY x
Result:
{
"error": {
"root_cause": [
{
"type": "sql_illegal_argument_exception",
"reason": "No idea what's the DataType for class org.elasticsearch.script.JodaCompatibleZonedDateTime"
}
],
"type": "search_phase_execution_exception",
"reason": "all shards failed",
"phase": "query",
"grouped": true,
"failed_shards": [
{
"shard": 0,
"index": "calcs",
"node": "Aw7CDFFcQLmM_uCa97TTow",
"reason": {
"type": "script_exception",
"reason": "runtime error",
"script_stack": [
"org.elasticsearch.xpack.sql.type.DataTypes.fromJava(DataTypes.java:79)",
"org.elasticsearch.xpack.sql.type.DataTypeConversion.convert(DataTypeConversion.java:372)",
"org.elasticsearch.xpack.sql.expression.function.scalar.whitelist.InternalSqlScriptUtils.cast(InternalSqlScriptUtils.java:471)",
"InternalSqlScriptUtils.cast(InternalSqlScriptUtils.docValue(doc,params.v0),params.v1)",
" ^---- HERE"
],
"script": "InternalSqlScriptUtils.cast(InternalSqlScriptUtils.docValue(doc,params.v0),params.v1)",
"lang": "painless",
"caused_by": {
"type": "sql_illegal_argument_exception",
"reason": "No idea what's the DataType for class org.elasticsearch.script.JodaCompatibleZonedDateTime"
}
}
}
],
"caused_by": {
"type": "sql_illegal_argument_exception",
"reason": "No idea what's the DataType for class org.elasticsearch.script.JodaCompatibleZonedDateTime"
}
},
"status": 400
}