MAX aggregator returns NULL for groups with only a single Date object

[t-wy]

Coming over this issue when finding a compromise on finding the MAX among a column with ISO8601 Time String (Check #609).
Since MAX aggregator seems to have implemented some comparison regarding Date (Following #425) but not String, so we try with parsing it to a date column instead. (As per src/423groupby.js)
Custom function:

alasql.fn.DATETIME = function (date) {
    return new Date(date);
}

Sample Data:

var data = [
    {"id": 1, "date": "2025-01-01T01:00:00.000Z"},
    {"id": 1, "date": "2025-01-02T01:00:00.000Z"},
    {"id": 1, "date": "2025-01-03T01:00:00.000Z"},
    {"id": 2, "date": "2025-02-01T01:00:00.000Z"},
    {"id": 2, "date": "2025-02-02T01:00:00.000Z"},
    {"id": 3, "date": "2025-03-01T01:00:00.000Z"},
]

Sample Query:

alasql("SELECT id, MAX(DATETIME(date)), COUNT(*) FROM ? GROUP BY id;", [ data ])

Expected:

[
    {"id": 1, "MAX(DATETIME(date))": "2025-01-03T01:00:00.000Z", "COUNT(*)": 3},
    {"id": 2, "MAX(DATETIME(date))": "2025-02-02T01:00:00.000Z", "COUNT(*)": 2},
    {"id": 3, "MAX(DATETIME(date))": "2025-03-01T01:00:00.000Z", "COUNT(*)": 1}
]

Received:

[
    {"id": 1, "MAX(DATETIME(date))": "2025-01-03T01:00:00.000Z", "COUNT(*)": 3},
    {"id": 2, "MAX(DATETIME(date))": "2025-02-02T01:00:00.000Z", "COUNT(*)": 2},
    {"id": 3, "MAX(DATETIME(date))": null, "COUNT(*)": 1}
]

It does seem to be an issue when handling a nested expression instead of the column value, since a Date instance can pass the
typeof value == 'object' && typeof Number(value) == 'number'
check.

Also, the following nested expression actually works:

Sample Query:

alasql("SELECT id, MAX(date), COUNT(*) FROM (SELECT id, DATETIME(date) AS date FROM ?) GROUP BY id;", [ data ])

Received:

[
    {"id": 1, "MAX(date)": "2025-01-03T01:00:00.000Z", "COUNT(*)": 3},
    {"id": 2, "MAX(date)": "2025-02-02T01:00:00.000Z", "COUNT(*)": 2},
    {"id": 3, "MAX(date)": "2025-03-01T01:00:00.000Z", "COUNT(*)": 1}
]

As an example to the String case (#609), the following is an example showing the failing result:

Sample Query:

alasql("SELECT id, MAX(date), COUNT(*) FROM (SELECT id, date AS date FROM ?) GROUP BY id;", [ data ])

Received:

[
    {"id": 1, "MAX(date)": null, "COUNT(*)": 3},
    {"id": 2, "MAX(date)": null, "COUNT(*)": 2},
    {"id": 3, "MAX(date)": null, "COUNT(*)": 1}
]

By the way, this library still compares Dates like in JS, that new Date("2025") == new Date("2025") returns false. A workaround for that is to compare with date + 0 (force cast to number) instead. This is a common case when handling JOIN with ON condition involving Date equality (e.g. finding the other fields of the row with the MAX date)

[mathiasrw]

Fix released as part of v4.8