weird bug with date ranges

[mtoy-googly-moogly]

run: bigquery.sql("""
  SELECT DATE '2001-01-01' as join_date, DATE '2001-02-01' as expiry_date
""") -> {
  select:
    round_wrong is round(days(join_date to expiry_date))
    no_round_right is days(join_date to expiry_date)
}

The SQL generated is

SELECT 
   ROUND(FLOOR(TIMESTAMP_DIFF(TIMESTAMP(base.join_date),TIMESTAMP(base.join_date),hour)/24.0)) as `round_wrong`,
   FLOOR(TIMESTAMP_DIFF(TIMESTAMP(base.expiry_date),TIMESTAMP(base.join_date),hour)/24.0) as `no_round_right`
FROM (
  SELECT DATE '2001-01-01' as join_date, DATE '2001-02-01' as expiry_date
) as base

[mtoy-googly-moogly]

Likely a failure to ... or cloneDeep on an expression fragment somewhere in the code

[doit-mattporter]

I noticed on my end that using FLOOR() instead of ROUND() also throws the bug, but leaving out any function wrapping it leads the calculation to work fine. For example:

months_with_doit is days(segmentation.join_date to coalesce(segmentation.expiry_date, now::date)) / 30 -- months() is broken

Compiles correctly to:

FLOOR(TIMESTAMP_DIFF(TIMESTAMP(COALESCE((segmentation_0.ExpiryDate),CAST(CURRENT_TIMESTAMP() AS date))),TIMESTAMP((segmentation_0.DateJoined)),hour)/24.0)/30 as `months_with_doit`,

However:

months_with_doit is round(days(segmentation.join_date to coalesce(segmentation.expiry_date, now::date)) / 30, 0) -- months() is broken
months_with_doit is floor(days(segmentation.join_date to coalesce(segmentation.expiry_date, now::date)) / 30) -- months() is broken

Both yield the bugged compilation:

ROUND(FLOOR(TIMESTAMP_DIFF(TIMESTAMP((segmentation_0.DateJoined)),TIMESTAMP((segmentation_0.DateJoined)),hour)/24.0)/30, 0) as `months_with_doit`,
FLOOR(FLOOR(TIMESTAMP_DIFF(TIMESTAMP((segmentation_0.DateJoined)),TIMESTAMP((segmentation_0.DateJoined)),hour)/24.0)/30) as `months_with_doit`,