I am using an External JDBC catalog to query a huge table from MSSQL. I am noticing that the queries fired by Starrocks v4 does not contain where conditions. Its doing a full table scan. How to enforce that Starrocks does the predicate pushdown while querying external JDBC catalog ? https://stackoverflow.com/questions/79890091/incremental-model-doing-full-table-scan

I reproduced this with StarRocks 3.5 + SQL Server 2022.

time_slice()

and other functions are NOT the problem. The subquery inside

is_incremental()

is.

WHERE report_time >= '2025-11-01'                                    -- pushed down
  AND report_time <= time_slice(minutes_sub(UTC_TIMESTAMP(),30), …)  -- pushed down (constant-folded)
  AND report_time > (select max(report_time) from {{ this }})        -- NOT pushed down

The subquery gets rewritten into a NESTLOOP JOIN by the optimizer, which causes SQL Server to be scanned a second time with no filters (full table scan). Workaround: pre-compute boundary values with

run_query()

sql
{% if execute and is_incremental() %}
  {% set q %}
    SELECT
      CAST(time_slice(minutes_sub(UTC_TIMESTAMP(),30), INTERVAL 15 MINUTE) AS VARCHAR) as ub,
      CAST(COALESCE(MAX(report_time),'2025-11-01') AS VARCHAR) as lb
    FROM {{ this }}
  {% endset %}
  {% set r = run_query(q) %}
  {% set ub = r.columns[0].values()[0] %}
  {% set lb = r.columns[1].values()[0] %}
{% else %}
  {% set ub = '2099-12-31' %}
  {% set lb = '2025-11-01' %}
{% endif %}

SELECT ...
FROM {{ source(...) }}
WHERE report_time >= '2025-11-01'
  AND report_time <= '{{ ub }}'
  AND report_time > '{{ lb }}'

This turns all WHERE conditions into plain string literals. You can verify with

EXPLAIN

-- check the

QUERY:

line inside

SCAN JDBC

to confirm the WHERE clause is included.

Under the hood: when StarRocks sees

report_time > (select max(report_time) from ...)

, the optimizer rewrites that into a NEST LOOP JOIN because it needs to resolve the subquery value at execution time. Since that value isn't known at planning time, it can't be serialized into the remote SQL sent to MSSQL — so SQL Server gets a scan without that predicate. Literal values and constant-foldable expressions (like

time_slice(minutes_sub(UTC_TIMESTAMP(),30), ...)

) do get pushed down because the optimizer can resolve them during planning and bake them into the remote query. Your

run_query()

workaround is the right pattern for dbt. By resolving the max value before the model compiles, you turn the subquery into a plain string literal that SR pushes down cleanly. To confirm it's working: run

EXPLAIN

on your final query and check the

SCAN JDBC

node — the

QUERY:

line shows you the exact SQL being sent to SQL Server. If your WHERE clauses are in there, you're golden. Also worth noting:

SET global jdbc_predicate_pushdown = true

(on by default in v4) controls whether predicates get pushed down for JDBC catalogs, but it operates on literals and constant expressions — subqueries follow a different path through the optimizer as described above.