SQL API fails to translate EXTRACT(DOW ...) to BigQuery DAYOFWEEK in complex WHERE clauses

[tlangton3]

Bug Report

Describe the bug

When querying via the SQL API (Postgres wire protocol), EXTRACT(DOW FROM ...) is correctly translated to BigQuery's DAYOFWEEK in simple cases, but the translation fails in more complex WHERE clauses — the raw dow date part is passed to BigQuery, which rejects it.

Error:

A valid date part name is required but found dow

This occurs with both CUBEJS_TESSERACT_SQL_PLANNER=true and false.

Passing cases (translation works)

-- Simple: EXTRACT(DOW FROM CURRENT_DATE) in WHERE
SELECT COUNT(*) as order_count
FROM test_orders
WHERE EXTRACT(DOW FROM CURRENT_DATE) = 1

-- With modulo + INTERVAL arithmetic
SELECT COUNT(*) as order_count
FROM test_orders
WHERE CAST(test_orders.delivered_on_ts_cast AS DATE) < (
  CAST(DATE_TRUNC('DAY', CAST(CURRENT_DATE AS TIMESTAMP)) AS DATE) - (
    ((7 + CAST(EXTRACT(DOW FROM CURRENT_DATE) AS BIGINT) - 1) % 7) * INTERVAL '1 DAY'
  )
)

Failing cases (translation breaks)

Case 1: EXTRACT(DOW FROM column_reference) in WHERE:

SELECT COUNT(*) as order_count
FROM test_orders
WHERE
  EXTRACT(DOW FROM test_orders.delivered_on_ts_cast) = 1
  AND test_orders.delivered_on IS NOT NULL

Case 2: CASE statement + EXTRACT(DOW) + multiple AND conditions:

SELECT COUNT(*) as order_count
FROM test_orders
WHERE
  CAST(test_orders.delivered_on_ts_cast AS DATE) < (
    CAST(DATE_TRUNC('DAY', CAST(CURRENT_DATE AS TIMESTAMP)) AS DATE) - (
      ((7 + CAST(EXTRACT(DOW FROM CURRENT_DATE) AS BIGINT) - 1) % 7) * INTERVAL '1 DAY'
    )
  )
  AND (
    CASE
      WHEN test_orders.customer_id = 'CUST-001' THEN 'outbound'
      WHEN test_orders.customer_id = 'CUST-002' THEN 'outbound'
      ELSE 'inbound'
    END
  ) = 'outbound'
  AND test_orders.status = 'completed'
  AND test_orders.delivered_on IS NOT NULL

To Reproduce

Cube version: 1.6.30
Database: BigQuery

Expected behaviour

The DOW → DAYOFWEEK translation should apply consistently regardless of WHERE clause complexity, whether the argument is CURRENT_DATE or a column reference, and whether the query includes CASE expressions.

Additional context

• Reproduces with both Tesseract enabled and disabled
• Query patterns originate from Tableau connecting via the SQL API (Postgres wire protocol)
• The passing cases suggest the translation logic exists but doesn't cover all code paths — it works with CURRENT_DATE but fails with column references and more complex WHERE clauses