SQL: grouping by WEEK(CAST()) returns incorrect results and translated query

[astefan]

SELECT WEEK(CAST(date AS DATE)) FROM test GROUP BY 1 results in

WEEK(CAST(date AS DATE))
-------------------------
null                     
291772800000             
422409600000             
511660800000             
531792000000             
695692800000             
826156800000             
831945600000             
854841600000             
935107200000             

Adding the field itself - date - to the results and grouping by it as well SELECT WEEK(CAST(date AS DATE)), date FROM calcs GROUP BY 1, 2 gives correct results:

WEEK(CAST(date AS DATE))|         date          
-------------------------+------------------------
null                     |null                    
14                       |1979-04-01T00:00:00.000Z
22                       |1983-05-22T00:00:00.000Z
12                       |1986-03-20T00:00:00.000Z
45                       |1986-11-08T00:00:00.000Z
3                        |1992-01-18T00:00:00.000Z
10                       |1996-03-07T00:00:00.000Z
20                       |1996-05-13T00:00:00.000Z
6                        |1997-02-02T00:00:00.000Z
34                       |1999-08-20T00:00:00.000Z

Moreover, the translated query for SELECT WEEK(CAST(date AS DATE)) FROM test GROUP BY 1 is incorrect:

{
    "size": 0,
    "_source": false,
    "stored_fields": "_none_",
    "aggregations": {
        "groupby": {
            "composite": {
                "size": 1000,
                "sources": [
                    {
                        "322266": {
                            "terms": {
                                "script": {
                                    "source": "InternalSqlScriptUtils.cast(InternalSqlScriptUtils.docValue(doc,params.v0),params.v1)",
                                    "lang": "painless",
                                    "params": {
                                        "v0": "date",
                                        "v1": "DATE"
                                    }
                                },
                                "missing_bucket": true,
                                "value_type": "long",
                                "order": "asc"
                            }
                        }
                    }
                ]
            }
        }
    }
}

[elasticmachine]

Pinging @elastic/es-search

[astefan]

Issue fixed by #46421.