Tesseract bug: filtering by aggregate measures broken in BigQuery due to type casting problem

[PaddyAlton]

Describe the bug
When

• $CUBEJS_TESSERACT_SQL_PLANNER is true
• BigQuery is the data warehouse

Then attempts to filter on aggregate numeric measures (i.e. count and sum) will fail, due to the filter value being passed as a string instead of cast to a suitable type.

To Reproduce
Steps to reproduce the behavior:

1. set up Cube with the minimal reproducible schema below (or use any table with a sum or count measure)
2. set $CUBEJS_TESSERACT_SQL_PLANNER = true
3. query Cube using a filter, e.g. {"measures": [measure], "filters": [{"member": measure, "operator": "lt", "values": ["4"]}] for some measure

Representative result:

--- Generated SQL (/sql) ---
SELECT sum(`test`.test_value) `test__total` 
FROM  (SELECT 1 AS test_value UNION ALL SELECT 2)  AS `test`
HAVING (sum(`test`.test_value) = ?)
LIMIT 10000
params: ['4']

--- Execution result (/load) ---
ERROR (status 400):
"Error: No matching signature for operator = for argument types: INT64, STRING
  Signature: T1 = T1
    Unable to find common supertypefor templated argument <T1>
      Input types for <T1>: {INT64, STRING} at [3:9]"

Expected behavior
I would expect the Tesseract planner to create a query with proper type-casting.

Here is what the standard planner creates with my test schema (see below):

--- Generated SQL (/sql) ---
SELECT
      sum(`test`.test_value) `test__total`
    FROM
      (SELECT 1 AS test_value UNION ALL SELECT 2) AS `test`  HAVING (sum(`test`.test_value) < CAST(? AS FLOAT64)) LIMIT 10000
params: ['4']

--- Execution result (/load) ---
SUCCESS: [{'test.total': '3'}]

Note the HAVING (sum(test.test_value) < CAST(? AS FLOAT64)). That is the expected behaviour.

Minimally reproducible Cube Schema
Here is an example schema that will replicate the problem if you attempt to query cube

cubes:
  - name: test
    sql: SELECT 1 AS test_value UNION ALL SELECT 2
    data_source: default

    measures:
      - name: total
        type: sum
        sql: test_value

      - name: count
        type: count

Version:
v1.6.55 (latest at time of writing, also tested on earlier versions back to v1.3.54)

Additional context
Related to 9646 (closed, I believe incorrectly).

[igorlukanin]

Confirmed reproducing with CUBEJS_TESSERACT_SQL_PLANNER=true on CUBEJS_DB_TYPE=bigquery. Tested on v1.6.55 (reported) and latest v1.6.57 — still reproduces on both.

The Tesseract planner omits the CAST around the bound measure-filter value that the standard planner emits. Same model + query + dialect, only the planner flag flipped:

CUBEJS_TESSERACT_SQL_PLANNER	Generated HAVING
false (standard)	HAVING (sum(...) < CAST(? AS FLOAT64)) ✅
true (Tesseract)	HAVING (sum(...) < ?) ❌ no cast

With the missing cast the bound param is the raw string '4', which BigQuery rejects on a numeric comparison (No matching signature for operator < for argument types: INT64, STRING).

Minor note: the operator itself is correct (<) on current code; the remaining defect is purely the missing CAST.