SQL: datetime functions not filtering correctly: ISO_WEEK_OF_YEAR, MINUTE_OF_DAY

[bpintea]

With the following index data:

POST test/_doc
{
  "a": "2021-01-22T14:26:06.039Z"
}

the following functions fail to filter by the same value they return:

1. SELECT ISO_WEEK_OF_YEAR(a) AS x FROM test WHERE x=4
   returns:

       x       
---------------
3

2. SELECT MINUTE_OF_DAY(a) AS x FROM test WHERE x=866
   fails with:

    "type" : "unsupported_operation_exception",
    "reason" : "is there a format for it?"

[elasticmachine]

Pinging @elastic/es-ql (Team:QL)

[blookot]

@bpintea weirdly this used to work (a few weeks ago), and today it works again!
This is my request:

GET /_sql?format=txt
{
  "query": """
  SELECT startTimestamp, CURRENT_TIMESTAMP, ISO_WEEK_OF_YEAR(startTimestamp), ISO_WEEK_OF_YEAR(CURRENT_TIMESTAMP) FROM "user-call-logs" WHERE ISO_WEEK_OF_YEAR(startTimestamp) = ISO_WEEK_OF_YEAR(CURRENT_TIMESTAMP) AND YEAR(startTimestamp) = YEAR(CURRENT_DATE)
  """
}

and the response is correct:

     startTimestamp     |   CURRENT_TIMESTAMP    |ISO_WEEK_OF_YEAR(startTimestamp)|ISO_WEEK_OF_YEAR(CURRENT_TIMESTAMP)
------------------------+------------------------+--------------------------------+-----------------------------------
2021-01-28T18:37:36.769Z|2021-01-28T18:43:51.644Z|4                               |4     

[palesz]

Also worth mentioning that the ISO_WEEK_OF_YEAR function returns different results in group by vs outside of group by and the order by on this function is also incorrect:

With group by:

SELECT IW(birth_date) iso_week, WEEK(birth_date) week FROM test_emp WHERE IW(birth_date) < 5 GROUP BY iso_week, week ORDER BY iso_week

   iso_week    |     week      
---------------+---------------
1              |2              
3              |4              
4              |4              
4              |5              

Same dataset without group by, but with order by:

SELECT birth_date, IW(birth_date) iso_week, WEEK(birth_date) week FROM test_emp WHERE IW(birth_date) < 5 ORDER BY iso_week

       birth_date       |   iso_week    |     week      
------------------------+---------------+---------------
1953-01-07T00:00:00.000Z|2              |2              
1965-01-03T00:00:00.000Z|53             |2              
1955-01-21T00:00:00.000Z|3              |4              
1958-01-21T00:00:00.000Z|4              |4              
1953-01-23T00:00:00.000Z|4              |4              
1959-01-27T00:00:00.000Z|5              |5              

[palesz]

Fixed the issues of both functions, closing.