SQL API - BigQuery - Cube converts DATE() function casts to timestamp type that triggers DATE <> TIMESTAMP error #10073
Description
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 (
test_orders.delivered_on_ts_cast IS NOT NULL
)
AND (
(
(
(
CAST(test_orders.delivered_on_ts_cast AS DATE) = DATE('2024-01-08')
) IS NULL
)
OR (
CAST(test_orders.delivered_on_ts_cast AS DATE) <> DATE('2024-01-08')
)
)
OR (
CAST(test_orders.delivered_on_ts_cast AS DATE) = DATE('2024-01-08')
)
)
)
GROUP BY
1
ORDER BY
1triggers
Arrow error: Compute error: Error: No matching signature for operator = for argument types: TIMESTAMP, DATE
Input query WHERE clause:
CAST(test_orders.delivered_on_ts_cast AS DATE) = DATE('2024-01-08')
Cube's transformed WHERE clause:
CAST(CAST(timestamp(delivered_on) AS DATE) AS TIMESTAMP) = DATE(?)
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
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:
# Basic measures (for backward compatibility)
- name: count
type: count
title: "Order Count"
description: "Total number of orders"
- name: total_amount
type: sum
sql: "{amount}"
title: "Total Revenue"
description: "Sum of all order amounts"
- name: average_amount
type: avg
sql: "{amount}"
format: currency
title: "Average Order Value"
description: "Average revenue per order"
- name: completed_count
type: count
title: "Completed Orders"
description: "Count of completed orders"
filters:
- sql: "{CUBE}.status = 'completed'"
- name: order_count
type: count
title: "Order Count"
description: "Count of orders (for num_parcels simulation)"
- name: average_amount_calculated
type: number
title: "Average Amount Calculated"
description: "Total amount divided by order count"
sql: "{total_amount} / nullif({order_count}, 0)"
# Rolling window measures - Previous day
- name: count_prev_1_day
type: count
title: "Order Count - Previous 1 Day"
description: "Count of orders from the previous day"
sql: "{id}"
rolling_window:
trailing: 1 day
offset: start
- name: total_amount_prev_1_day
type: sum
sql: "{amount}"
title: "Total Revenue - Previous 1 Day"
description: "Total revenue from the previous day"
rolling_window:
trailing: 1 day
offset: start
# Rolling window measures - 7 days
- name: count_7_day
type: count
title: "Order Count - Rolling 7 Days"
description: "Count of orders over the past 7 days"
sql: "{id}"
rolling_window:
trailing: 7 day
- name: total_amount_7_day
type: sum
sql: "{amount}"
title: "Total Revenue - Rolling 7 Days"
description: "Total revenue over the past 7 days"
rolling_window:
trailing: 7 day
# Day-over-Day percentage change (like num_parcels_pct_change_dod)
- name: count_pct_change_dod
type: number
title: "Day-over-Day % Change in Orders"
description: >
Percentage change in order count compared to the previous day.
Calculation: ((Current day orders - Previous day orders) / Previous day orders) * 100
sql: |
(
({count} - {count_prev_1_day}) /
nullif({count_prev_1_day}, 0)
) * 100
- name: revenue_pct_change_dod
type: number
title: "Day-over-Day % Change in Revenue"
description: >
Percentage change in revenue compared to the previous day.
Calculation: ((Current day revenue - Previous day revenue) / Previous day revenue) * 100
sql: |
(
({total_amount} - {total_amount_prev_1_day}) /
nullif({total_amount_prev_1_day}, 0)
) * 100
# Average order value with day-over-day change
- name: avg_order_value_pct_change_dod
type: number
title: "Day-over-Day % Change in Average Order Value"
description: >
Percentage change in average order value compared to the previous day.
Calculation: ((Current AOV - Previous AOV) / Previous AOV) * 100
sql: |
((
({total_amount} / nullif({count}, 0)) -
({total_amount_prev_1_day} / nullif({count_prev_1_day}, 0))
) /
nullif({total_amount_prev_1_day} / nullif({count_prev_1_day}, 0), 0)) * 100
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
title: "Service Category"
description: "Service category for the order (outbound/inbound)"
- name: delivered_on
sql: delivered_on
type: string
title: "Delivered On Date"
description: "Date when the order was delivered"
- name: delivered_on_ts_cast
sql: timestamp(delivered_on)
type: time
title: "Delivered On Timestamp"
description: "Delivered date wrapped in timestamp()"Related to #9768
#10040 fixed cast('1900-01-01 as date) and DATE '1900-01-01' comparisons.