I believe this is a bug.
I have a customer who has created a custom dimension which yields a variable integer value based on the result of a boolean expression which compares a datetime (dimension provided by the LookML model) to a static date. It looks something like this:
case(when(${order.order_date} > date(2024, 6, 1), 10700), 0)
Which when parsed, the Standard SQL generated by Looker is this:
CASE WHEN (TIMESTAMP(DATETIME_TRUNC(order.order_datetime , DAY))) > TIMESTAMP(CONCAT(CAST(2024 AS INT64), '-', LPAD(CAST(CAST(6 AS INT64) AS STRING), 2, '0'), '-', LPAD(CAST(CAST(1 AS INT64) AS STRING), 2, '0'), ' 00:00:00'), 'UTC') THEN 10700 ELSE 0 END
This works just as expected!
The problem comes when we then attempt to aggregate the results as a custom measure, where the Standard SQL generated by Looker is this:
COALESCE(SUM(CAST(CASE WHEN ( DATETIME_TRUNC(order.order_datetime , DAY) ) > TIMESTAMP(CONCAT(CAST(2024 AS INT64), '-', LPAD(CAST(CAST(6 AS INT64) AS STRING), 2, '0'), '-', LPAD(CAST(CAST(1 AS INT64) AS STRING), 2, '0'), ' 00:00:00'), 'UTC') THEN 10700 ELSE 0 END AS NUMERIC)), 0)
Note that the generated Standard SQL generated for the custom measure omits the TIMESTAMP() function around the DATETIME_TRUNC(order.order_datetime , DAY). And this breaks the query since you can’t compare a datetime to a timestamp. If I manually add the TIMESTAMP() function and execute the query myself in the BQ UI, there is no error.
I don’t understand why the TIMESTAMP() function is part of the generated Standard SQL for the custom dimension but not for the custom measure.
If this is not a bug, could someone please explain why not. If it is a bug, could someone on Looker staff please open a ticket for it. Thank you!
In the meantime, I have my customer using this janky workaround:
case(when(
extract_year(${order.order_date}) * 100000
- extract_month(${order.order_date}) * 100
- extract_day(${order.order_date}) >
2024 * 10000 + 6 * 100 + 1, 10700)
, 0)