Year(MyDateField) <= YEAR(NOW()) not supported

opendistro-for-elasticsearch / sql

🔍 Open Distro SQL Plugin

https://opendistro.github.io/for-elasticsearch/features/SQL%20Support.html

Apache License 2.0

622 stars 186 forks source link

Year(MyDateField) <= YEAR(NOW()) not supported #1073

Open FreCap opened 3 years ago

FreCap commented 3 years ago

Is there any way to get the current year?

YEAR(NOW())

seems currently not supported

{
  "error": {
    "reason": "There was internal problem at backend",
    "details": "Nested function calls like [YEAR(NOW())] are not supported yet",
    "type": "SqlFeatureNotImplementedException"
  },
  "status": 503
}

Version: Version 1.13.0.0

dai-chen commented 3 years ago

Thanks for reporting the issue! Investigating. Are you using the latest ODFE version?

FreCap commented 3 years ago

Yes, latest version

dai-chen commented 3 years ago

It seems function NOW() is missing in new engine. It is supported by old engine by fallback but unfortunately nested function call and SELECT without FROM are not. Need to add it to the new engine: https://github.com/opendistro-for-elasticsearch/sql/blob/develop/docs/user/dql/functions.rst#now

dai-chen commented 3 years ago

Reference to MySQL and PostgreSQL spec:

MySQL's NOW(): "returns a constant time that indicates the time at which the statement began to execute ... The value is expressed in the session time zone ..."
PostgreSQL's NOW(): "returns the start time of the current statement (more specifically, the time of receipt of the latest command message from the client) ... deliver values with time zone ..."