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)
LinkedIn
Twitter
