(Rolling window?) aggregate generates invalid BQ SQL (Unexpected keyword WITH)

[mfulop]

The query below with the cube schema (below) generates a SQL query that is invalid (BigQuery reports Syntax error: Unexpected keyword WITH at [2:5])

{
  "query": {
    "timezone": "UTC",
    "measures": [
      "SubmissionsTest.totalCount"
    ],
    "segments": [
    ],
    "filters": [],
    "dimensions": [
    ],
    "timeDimensions": [
      {
        "dimension": "SubmissionsTest.createdatDate",
        "granularity": "month",
        "dateRange": [
          "3/1/2024",
          "4/1/2024"
        ]
      }
    ]
  },
  "queryType": "multi"
}

The generated SQL is:

SELECT q_0.`submissions_test__createdat_date_month`, `submissions_test__total_count` `submissions_test__total_count` FROM \n    WITH `SubmissionsTest.createdatDate_series` AS (SELECT DATETIME(TIMESTAMP(dates.f)) date_from, DATETIME(TIMESTAMP(dates.t)) date_to FROM (select '2024-03-01T00:00:00.000' f, '2024-03-31T23:59:59.999' t UNION ALL select '2024-04-01T00:00:00.000' f, '2024-04-30T23:59:59.999' t) AS dates) SELECT `submissions_test_total_count_cumulative__outer_series`.`date_from` `submissions_test__createdat_date_month`, `submissions_test__total_count` FROM\n    `SubmissionsTest.createdatDate_series` `submissions_test_total_count_cumulative__outer_series`\n    LEFT JOIN (\n      SELECT `SubmissionsTest.createdatDate_series`.`date_from` `submissions_test__createdat_date_month`, sum(`submissions_test__total_count`) `submissions_test__total_count` FROM `SubmissionsTest.createdatDate_series`\n      INNER JOIN (SELECT DATETIME_TRUNC(DATETIME(PARSE_TIMESTAMP('%Y-%m-%dT%H:%M:%E*SZ', `submissions_test_total_count_cumulative__submissions_test`.createdat), 'UTC'), MONTH) `submissions_test__createdat_date_month`, count(*) `submissions_test__total_count` FROM xxxxxx AS `submissions_test_total_count_cumulative__submissions_test`  WHERE (DATETIME(PARSE_TIMESTAMP('%Y-%m-%dT%H:%M:%E*SZ', `submissions_test_total_count_cumulative__submissions_test`.createdat), 'UTC') <= DATETIME(TIMESTAMP(?))) GROUP BY 1) AS `submissions_test_total_count_cumulative__base` ON `submissions_test_total_count_cumulative__base`.`submissions_test__createdat_date_month` <= `SubmissionsTest.createdatDate_series`.`date_to`\n       GROUP BY 1\n    ) AS `submissions_test_total_count_cumulative__outer_base` ON `submissions_test_total_count_cumulative__outer_series`.`date_from` = `submissions_test_total_count_cumulative__outer_base`.`submissions_test__createdat_date_month`\n     as q_0  ORDER BY 1 ASC LIMIT 10000

Guarding WITH with (parentheses) makes the query valid.

Minimally reproducible Cube Schema

cube('SubmissionsTest', {
  sql: `SELECT * FROM xxx`,

  segments: {
  },

  measures: { 
    count: {
      sql: `${CUBE}._id`,
      type: `count`
    },
    totalCount: {
      type: `count`,
      rollingWindow: {
        trailing: `unbounded`
      }
    }
  },

  dimensions: {
    createdatDate: {
      sql: `PARSE_TIMESTAMP('%Y-%m-%dT%H:%M:%E*SZ', ${CUBE}.createdat)`,
      type: `time`
    },
    createdat: {
      sql: `${CUBE}.createdat`,
      type: `string`
    }
  }
})

Version:
"@cubejs-backend/bigquery-driver": "0.35.22",
"@cubejs-backend/cubestore-driver": "0.35.22",
"@cubejs-backend/server": "0.35.22",

[mfulop]

works in 0.34.62, broken in 0.35.0