SQL API - BigQuery - INTERVAL arithmetic with CURRENT_DATE triggers TIMESTAMP < DATETIME type mismatch

[tlangton3]

Failed SQL

SELECT
  test_orders.delivered_on_ts_cast AS "Calculation_3323797312816680970",
  AVG(test_orders.average_amount) AS "avg:avg_first_mile_payment:ok",
  AVG(test_orders.average_amount) AS "avg:avg_last_mile_payment:ok",
  AVG(test_orders.average_amount_calculated) AS "avg:avg_middle_mile_payment:ok",
  AVG(test_orders.average_amount_calculated) AS "avg:avg_pitstop_payment:ok",
  AVG(test_orders.average_amount) AS "avg:avg_sortation_payment:ok",
  AVG(test_orders.average_amount_calculated) AS "avg:avg_total_payment:ok",
  COUNT(test_orders.order_count) AS "cnt:num_parcels:ok",
  SUM(test_orders.order_count) AS "sum:num_parcels:ok"
FROM
  test_orders
WHERE
  (
    (
      CAST(test_orders.service_category AS TEXT) = 'outbound'
    )
    AND (
      CAST(test_orders.delivered_on_ts_cast AS DATE) < (CURRENT_DATE - INTERVAL '1 DAY')
    )
    AND (
      test_orders.delivered_on_ts_cast >= (TIMESTAMP '2024-01-08 00:00:00.000')
    )
    AND (
      test_orders.delivered_on_ts_cast < (TIMESTAMP '2024-01-16 00:00:00.000')
    )
  )
GROUP BY
  1
ORDER BY
  1

triggers

Arrow error: Compute error: Error: No matching signature for operator < for argument types: TIMESTAMP, DATETIME
  Signature: T1 < T1
    Unable to find common supertype for templated argument <T1>
      Input types for <T1>: {TIMESTAMP, DATETIME}

Input query WHERE clause:
CAST(test_orders.delivered_on_ts_cast AS DATE) < (CURRENT_DATE - INTERVAL '1 DAY')

Cube's transformed WHERE clause:
CAST(CAST(timestamp(delivered_on) AS DATE) AS TIMESTAMP) < (CURRENT_DATE - INTERVAL 1 DAY)

The Issue:

• User explicitly casts dimension to DATE: CAST(test_orders.delivered_on_ts_cast AS DATE)
• Cube ignores this explicit cast and wraps it back to TIMESTAMP
• The right-hand side CURRENT_DATE - INTERVAL '1 DAY' evaluates to DATETIME type
• Result: TIMESTAMP < DATETIME type mismatch error

Inconsistent Behavior:
A similar query without INTERVAL arithmetic works correctly:

CAST(test_orders.delivered_on_ts_cast AS DATE) = CURRENT_DATE

Cube generates:

CAST(timestamp(delivered_on) AS DATE) = CURRENT_DATE

No double-casting applied - Cube respects the explicit DATE cast when comparing to CURRENT_DATE alone.

Logical Plan
Search for Can't rewrite plan log message.

Tool
SQL written by myself for API integration suite

Version:
v1.3.81

Additional context

Test cube (same as #10073):

cubes:
  - name: test_orders
    sql: |
      SELECT * FROM (
        -- Current day data (2024-01-15)
        SELECT
          1 as id,
          'ORD-001' as order_id,
          'CUST-001' as customer_id,
          120.50 as amount,
          'completed' as status,
          TIMESTAMP('2024-01-15 10:00:00') as created_at,
          TIMESTAMP('2024-01-15 15:30:00') as delivered_at,
          DATE('2024-01-15') as delivered_on
        UNION ALL
        SELECT
          2 as id,
          'ORD-002' as order_id,
          'CUST-002' as customer_id,
          250.75 as amount,
          'completed' as status,
          TIMESTAMP('2024-01-15 11:00:00') as created_at,
          TIMESTAMP('2024-01-15 16:00:00') as delivered_at,
          DATE('2024-01-15') as delivered_on
        UNION ALL
        SELECT
          3 as id,
          'ORD-003' as order_id,
          'CUST-001' as customer_id,
          75.25 as amount,
          'pending' as status,
          TIMESTAMP('2024-01-15 12:00:00') as created_at,
          CAST(NULL AS TIMESTAMP) as delivered_at,
          CAST(NULL AS DATE) as delivered_on
        UNION ALL
        SELECT
          4 as id,
          'ORD-004' as order_id,
          'CUST-003' as customer_id,
          320.00 as amount,
          'completed' as status,
          TIMESTAMP('2024-01-15 13:00:00') as created_at,
          TIMESTAMP('2024-01-15 18:00:00') as delivered_at,
          DATE('2024-01-15') as delivered_on

        -- Previous day data (2024-01-14)
        UNION ALL
        SELECT
          5 as id,
          'ORD-005' as order_id,
          'CUST-001' as customer_id,
          150.00 as amount,
          'completed' as status,
          TIMESTAMP('2024-01-14 10:00:00') as created_at,
          TIMESTAMP('2024-01-14 15:00:00') as delivered_at,
          DATE('2024-01-14') as delivered_on
        UNION ALL
        SELECT
          6 as id,
          'ORD-006' as order_id,
          'CUST-002' as customer_id,
          200.00 as amount,
          'completed' as status,
          TIMESTAMP('2024-01-14 11:00:00') as created_at,
          TIMESTAMP('2024-01-14 16:00:00') as delivered_at,
          DATE('2024-01-14') as delivered_on

        -- Week ago data (2024-01-08)
        UNION ALL
        SELECT
          7 as id,
          'ORD-007' as order_id,
          'CUST-001' as customer_id,
          100.00 as amount,
          'completed' as status,
          TIMESTAMP('2024-01-08 10:00:00') as created_at,
          TIMESTAMP('2024-01-08 15:00:00') as delivered_at,
          DATE('2024-01-08') as delivered_on
        UNION ALL
        SELECT
          8 as id,
          'ORD-008' as order_id,
          'CUST-002' as customer_id,
          180.00 as amount,
          'completed' as status,
          TIMESTAMP('2024-01-08 11:00:00') as created_at,
          TIMESTAMP('2024-01-08 16:00:00') as delivered_at,
          DATE('2024-01-08') as delivered_on
        UNION ALL
        SELECT
          9 as id,
          'ORD-009' as order_id,
          'CUST-003' as customer_id,
          90.00 as amount,
          'completed' as status,
          TIMESTAMP('2024-01-08 12:00:00') as created_at,
          TIMESTAMP('2024-01-08 17:00:00') as delivered_at,
          DATE('2024-01-08') as delivered_on
      ) as mock_orders

    measures:
      - name: count
        type: count

      - name: total_amount
        type: sum
        sql: "{amount}"

      - name: average_amount
        type: avg
        sql: "{amount}"

      - name: order_count
        type: count

      - name: average_amount_calculated
        type: number
        sql: "{total_amount} / nullif({order_count}, 0)"

    dimensions:
      - name: id
        sql: id
        type: number
        primary_key: true

      - name: order_id
        sql: order_id
        type: string

      - name: customer_id
        sql: customer_id
        type: string

      - name: amount
        sql: amount
        type: number

      - name: status
        sql: status
        type: string

      - name: created_at
        sql: created_at
        type: time

      - name: delivered_at
        sql: delivered_at
        type: time

      - name: service_category
        sql: |
          CASE
            WHEN customer_id = 'CUST-001' THEN 'outbound'
            WHEN customer_id = 'CUST-002' THEN 'outbound'
            ELSE 'inbound'
          END
        type: string

      - name: delivered_on
        sql: delivered_on
        type: string

      - name: delivered_on_ts_cast
        sql: timestamp(delivered_on)
        type: time

[igorlukanin]

Hi @tlangton3 👋

Could you please try this with the latest version? On 1.5.0, with the data model adapted to DuckDB, the query runs just fine.

Data model:

cubes:
  - name: test_orders
    sql: |
      SELECT * FROM (
        -- Current day data (2024-01-15)
        SELECT
          1 as id,
          'ORD-001' as order_id,
          'CUST-001' as customer_id,
          120.50 as amount,
          'completed' as status,
          '2024-01-15 10:00:00'::TIMESTAMP as created_at,
          '2024-01-15 15:30:00'::TIMESTAMP as delivered_at,
          '2024-01-15'::DATE as delivered_on
        UNION ALL
        SELECT
          2 as id,
          'ORD-002' as order_id,
          'CUST-002' as customer_id,
          250.75 as amount,
          'completed' as status,
          '2024-01-15 11:00:00'::TIMESTAMP as created_at,
          '2024-01-15 16:00:00'::TIMESTAMP as delivered_at,
          '2024-01-15'::DATE as delivered_on
        UNION ALL
        SELECT
          3 as id,
          'ORD-003' as order_id,
          'CUST-001' as customer_id,
          75.25 as amount,
          'pending' as status,
          '2024-01-15 12:00:00'::TIMESTAMP as created_at,
          NULL::TIMESTAMP as delivered_at,
          NULL::DATE as delivered_on
        UNION ALL
        SELECT
          4 as id,
          'ORD-004' as order_id,
          'CUST-003' as customer_id,
          320.00 as amount,
          'completed' as status,
          '2024-01-15 13:00:00'::TIMESTAMP as created_at,
          '2024-01-15 18:00:00'::TIMESTAMP as delivered_at,
          '2024-01-15'::DATE as delivered_on

        -- Previous day data (2024-01-14)
        UNION ALL
        SELECT
          5 as id,
          'ORD-005' as order_id,
          'CUST-001' as customer_id,
          150.00 as amount,
          'completed' as status,
          '2024-01-14 10:00:00'::TIMESTAMP as created_at,
          '2024-01-14 15:00:00'::TIMESTAMP as delivered_at,
          '2024-01-14'::DATE as delivered_on
        UNION ALL
        SELECT
          6 as id,
          'ORD-006' as order_id,
          'CUST-002' as customer_id,
          200.00 as amount,
          'completed' as status,
          '2024-01-14 11:00:00'::TIMESTAMP as created_at,
          '2024-01-14 16:00:00'::TIMESTAMP as delivered_at,
          '2024-01-14'::DATE as delivered_on

        -- Week ago data (2024-01-08)
        UNION ALL
        SELECT
          7 as id,
          'ORD-007' as order_id,
          'CUST-001' as customer_id,
          100.00 as amount,
          'completed' as status,
          '2024-01-08 10:00:00'::TIMESTAMP as created_at,
          '2024-01-08 15:00:00'::TIMESTAMP as delivered_at,
          '2024-01-08'::DATE as delivered_on
        UNION ALL
        SELECT
          8 as id,
          'ORD-008' as order_id,
          'CUST-002' as customer_id,
          180.00 as amount,
          'completed' as status,
          '2024-01-08 11:00:00'::TIMESTAMP as created_at,
          '2024-01-08 16:00:00'::TIMESTAMP as delivered_at,
          '2024-01-08'::DATE as delivered_on
        UNION ALL
        SELECT
          9 as id,
          'ORD-009' as order_id,
          'CUST-003' as customer_id,
          90.00 as amount,
          'completed' as status,
          '2024-01-08 12:00:00'::TIMESTAMP as created_at,
          '2024-01-08 17:00:00'::TIMESTAMP as delivered_at,
          '2024-01-08'::DATE as delivered_on
      ) as mock_orders

    measures:
      - name: count
        type: count

      - name: total_amount
        type: sum
        sql: "{amount}"

      - name: average_amount
        type: avg
        sql: "{amount}"

      - name: order_count
        type: count

      - name: average_amount_calculated
        type: number
        sql: "{total_amount} / nullif({order_count}, 0)"

    dimensions:
      - name: id
        sql: id
        type: number
        primary_key: true

      - name: order_id
        sql: order_id
        type: string

      - name: customer_id
        sql: customer_id
        type: string

      - name: amount
        sql: amount
        type: number

      - name: status
        sql: status
        type: string

      - name: created_at
        sql: created_at
        type: time

      - name: delivered_at
        sql: delivered_at
        type: time

      - name: service_category
        sql: |
          CASE
            WHEN customer_id = 'CUST-001' THEN 'outbound'
            WHEN customer_id = 'CUST-002' THEN 'outbound'
            ELSE 'inbound'
          END
        type: string

      - name: delivered_on
        sql: delivered_on
        type: time

      - name: delivered_on_ts_cast
        sql: delivered_on::TIMESTAMP
        type: time

[tlangton3]

Hi Igor! I just ran our test suite against 1.5.0 and confirm this is now passing. The only one left failing is #10073

Thank you!

[igorlukanin]

OK, let's close this one and take a look at the other!