SQL Plugin returns double instead of date type for functions NOW() and CURDATE() [BUG]

[ghost]

Describe the bug
Calling NOW() and CURDATE() returns NULL for the JDBC driver while calling NOW() and CURDATE() on the SQL Plugin returns the expected results

To Reproduce
Steps to reproduce the behavior:

1. Load the calcs table on to the server
2. Run a query with NOW() or CURDATE() to the SQL Plugin for example curl -XPOST http://localhost:9200/_plugins/_sql -u admin:admin --insecure -H 'Content-Type: application/json' -d '{"query": "SELECT CURDATE() FROM calcs LIMIT 1"}' for Windows Subsystem for Linux

Expected behavior
The output should be of some date type like date or datetime

Actual behavior
The output is of type double.

Screenshot

Host/Environment (please complete the following information):

• OS: Windows 10
• Version 21H1

[Yury-Fridlyand]

See also #259. Perhaps this bug could be fixed in scope of #259.

[chloe-zh]

The new engine has not supported these functions, looks like the functions are executed in old engine. Should add such support in new query engine to address this issue

[chloe-zh]

related issue: #48

[ghost]

This issue affects TDVT tests:

• exprtests/standard\setup.date.now.txt
• exprtests/standard\setup.date.today.txt
• exprtests/standard\setup.date.math.txt

[dai-chen]

I think this is already fixed along with the new impl in v2 engine. Tested in OpenSearch playground as below:

POST _plugins/_sql
{
 "query": "select now(), curdate()"
}

{
 "schema": [
   {
     "name": "now()",
     "type": "datetime"
   },
   {
     "name": "curdate()",
     "type": "date"
   }
 ],
 "datarows": [
   [
     "2022-12-14 03:27:48",
     "2022-12-14"
   ]
 ],
 "total": 1,
 "size": 1,
 "status": 200
}