Tesseract generates `::timestamp` casts for Pinot time-series queries

[0eu]

Describe the bug
When CUBEJS_TESSERACT_SQL_PLANNER=true, any query that generates a date series -- rolling-window or cumulative measures -- produces a time_series CTE that uses ::timestamp casts:

WITH
time_series AS (
  SELECT date_from::timestamp AS "date_from",
         date_to::timestamp AS "date_to"
  FROM (
    VALUES ('2024-01-01T00:00:00.000', '2024-01-01T23:59:59.999'),
           ('2024-01-02T00:00:00.000', '2024-01-02T23:59:59.999')
  ) AS dates (date_from, date_to)
)
...

Pinot's parser does not support the :: cast operator and rejects the query:

SQLParsingError: Caught exception while parsing query:
SELECT date_from::timestamp AS "date_from", date_to::timestamp AS "date_to" ...
Encountered " ":" ": "" at line 1, column 29.

To Reproduce

1. Configure a Cube project with the Pinot driver.
2. Define a cube with sql_table and a rolling_window measure.
3. Enable Tesseract with CUBEJS_TESSERACT_SQL_PLANNER=true.
4. Generate SQL for a query that selects the rolling measure across a time dimension with granularity.
5. Observe that the generated time_series CTE contains date_from::timestamp / date_to::timestamp, which Pinot rejects.

Expected behavior
The time-series template should cast with CAST(... AS TIMESTAMP), which Pinot accepts:

time_series AS (
  SELECT CAST(date_from AS TIMESTAMP) AS "date_from",
         CAST(date_to   AS TIMESTAMP) AS "date_to"
  FROM (
    VALUES ('2024-01-01T00:00:00.000', '2024-01-01T23:59:59.999'),
           ('2024-01-02T00:00:00.000', '2024-01-02T23:59:59.999')
  ) AS dates (date_from, date_to)
)

Minimally reproducible Cube Schema

cube(`Events`, {
  sql_table: `events`,

  measures: {
    totalAmount: {
      sql: `amount`,
      type: `sum`,
    },
    rollingCount: {
      type: `count`,
      rolling_window: {
        trailing: `7 day`,
      },
    },
  },

  dimensions: {
    id: {
      sql: `id`,
      type: `number`,
      primary_key: true,
    },
    createdAt: {
      sql: `created_at`,
      type: `time`,
    },
  },
});

Query that triggers time-series generation:

{
  "measures": ["Events.rollingCount"],
  "timeDimensions": [
    {
      "dimension": "Events.createdAt",
      "granularity": "day",
      "dateRange": ["2024-01-01", "2024-01-07"]
    }
  ]
}

Version:

• Cube 1.7.1
• Pinot 1.6.0

Additional context
I cherry-picked this diff first: #11001

[ovr]

Thank you for bug reporting. I've made the fix and released a new version: v1.7.2.

Feel free to give it a try, and let me know if it doesn't work.