Group by YEAR in sql api return wrong results

[skiod]

Elasticsearch version: 7.6.2
JVM version :1.8.0_131

OS version : 15.6.0 Darwin Kernel Version 15.6.0: Thu Jun 21 20:07:40 PDT 2018; root:xnu-3248.73.11~1/RELEASE_X86_64 x86_64

Description of the problem including expected versus actual behavior:
guys is Group by YEAR not working ?
i think it has a relation with this issue : https://github.com/elastic/elasticsearch/issues/40162

for the following sql query

SELECT          sum(price) as total,YEAR(created_at)as year,MONTH(created_at) as month
FROM             orders 
WHERE          shop ='xxxxxx' AND created_at  between '2018-08-10' AND '2019-08-10' 
GROUP BY     YEAR(created_at), MONTH(created_at)

results in elasticsearch :

     [ 21713.74, 2017, 8 ],
     [ 36740.00, 2017, 9 ],
     [ 35986.20, 2017, 10 ],
     [ 15066.79, 2017, 11 ],
     [ 7987.30, 2017, 12 ],
     [ 1615.14, 2018, 1 ],
     [ 97.90, 2018, 4 ],
     [ 75.90, 2018, 5 ],
     [ 437.20, 2018, 6 ],
     [ 99.80 2018, 7 ],
     [ 251.53, 2018, 8 ],
     [ 688.68, 2018, 12 ] ]

same query in mysql
ps : same data shared in es / mysql

[elasticmachine]

Pinging @elastic/es-search (:Search/SQL)

[matriv]

Hi @skiod,

Indeed this is the same issue as #40162. Currently ES-SQL implements the GROUP BY YEAR(timestamp) as a histogram of milliseconds in a year (365 x 24 x 60 x 60 x 1000).
So the YEAR(timestamp) has a different behavior if it's in the SELECT, WHERE, ORDER BY and HAVING than when it's a GROUP BY column.

I will close this as a duplicate of #40162. Thanks for taking reporting it.