`LAST_DAY` function in BigQuery not working

[carlineng]

Bug report from Slack.

The LAST_DAY function in BigQuery takes a date_part as the second argument, but fails when you pass in a string. Some examples:

The following query fails with a syntax error, A valid date part name is required:

query: table('malloy-data.ecomm.order_items') -> {
    project:
        created_at
        created_date is created_at.day
        last_day_of_week is LAST_DAY(created_at.day, 'WEEK')
    limit: 24
}

The following query does not compile:

query: table('malloy-data.ecomm.order_items') -> {
    project:
        created_at
        created_date is created_at.day
        last_day_of_week is LAST_DAY(created_at.day, WEEK)
    limit: 24
}

[carlineng]

@christopherswenson will any of your recent work on functions be able to help with this? How should we handle arguments that need the "date_part" type?

[mtoy-googly-moogly]

Not sure how we are going to handle this and similar functions. We would need some special new syntax for "this looks like a field name but it is really a keyword in the underlying database".

last_day_of_week is created_at.week + 6 days ?

[carlineng]

This issue came up again in Slack: https://malloy-community.slack.com/archives/C025JAK8G0N/p1691111355523209

Perhaps we do some kind of special quoting or escaping to indicate that a keyword should be passed through directly, and not interpreted as a field?

[christopherswenson]

It should be possible now, using the sql_functions experiment, to do something like sql_date("LAST_DAY(${created_at_day}, WEEK)"), which currently requires that created_at_day be a predefined field. Probably some day we'll support something more like sql date("LAST_DAY(${created_at.day}, WEEK)") but unclear on the path forward to get there.

I think SQL expressions is our (possibly final) solution to the "weird function invocation case."