fix(tesseract): multi-stage pre-aggregation usage substitution and IS NOT DISTINCT FROM

[waralexrom]

Summary

When a multi-stage query needs several distinct scans of the same pre-aggregation, Tesseract emits SQL with <base>__usage_N suffixes (e.g. payer_360_lives_cube_julio__usage_0). The orchestrator's suffix-aware substitution lived only inside the partitioned branch of PreAggregationPartitionRangeLoader.loadPreAggregations, so for non-partitioned rollups the suffix was never mapped to a real table name. Combined with the substitution's lack of word boundaries, this left __usage_N glued onto the substituted full table name and Cubestore failed with Table prod_pre_aggregations.<full>__usage_0 was not found.

This PR fixes the substitution path for non-partitioned rollups and, while at it, registers IS NOT DISTINCT FROM in the Cubestore SQL templates so multi-stage JOIN ON clauses don't balloon into OR (a IS NULL AND b IS NULL) triplets per dimension.

Changes

• PreAggregationPartitionRangeLoader.ts — populate usageTargetTableNames in the non-partitioned else branch too. For non-partitioned rollups every __usage_N maps to the single result.targetTableName.
• CubeStoreQuery.ts — register operators.is_not_distinct_from = 'IS NOT DISTINCT FROM' (Cubestore's SQL frontend supports it natively).
• Integration test in cubejs-schema-compiler (two multi-stage branches sharing one pre-aggregation) exercising the multi-usage planner path.
• Driver test in cubejs-testing-drivers (querying BigECommerce: base measure plus multi-stage over non-partitioned pre-aggregation) reproducing the production failure end-to-end against real Postgres + Cubestore. New non-partitioned CategoryFlat pre-aggregation added to driver fixtures to host the scenario.
• Snapshots added for postgres, bigquery, databricks-jdbc, athena, mysql, redshift, snowflake (*-full plus export-bucket-*/encrypted-pk variants). Test is skipped for mssql (integer encoding of decimals) and clickhouse (FP artifacts) — they keep it in tesseractSkip. Non-Tesseract mode skipped everywhere (multi-stage requires Tesseract).

Testing

• Verified locally that the new testing-drivers test reproduces the production failure with the unpatched cube image: Error during planning: Table prod_pre_aggregations.big_e_commerce__category_flat_external_<hashes>__usage_1 was not found.
• After the fix, the same test passes against real Postgres + Cubestore (Docker compose + local cube CLI via --mode=local).
• Dumped generated SQL with the new IS NOT DISTINCT FROM template — JOIN ON clauses collapse from per-dim (a = b OR (a IS NULL AND b IS NULL)) to single (a IS NOT DISTINCT FROM b) predicates; values in the snapshot unchanged.

Test plan

• CI: testing-drivers full suites for postgres, bigquery, databricks-jdbc, athena, mysql, redshift, snowflake (Tesseract-enabled drivers).
• CI: schema-compiler postgres integration suite, in particular PreAggregationsMultiStage describe.
• Spot-check the new snapshot diffs once CI runs them with -u to catch any per-driver decimal-format discrepancies.

[claude]

Claude finished @waralexrom's task in 7m 7s —— View job

---

Code Review: Multi-stage pre-aggregation fix

• Gather context and read changed source files
• Review core fix: PreAggregationPartitionRangeLoader.ts
• Review CubeStoreQuery.ts change
• Review integration test in schema-compiler
• Review driver test additions and fixtures
• Review snapshot files
• Post final review

---

Overall Assessment

This is a well-targeted bug fix with good test coverage. The core change is minimal and correct — filling in the non-partitioned branch that was missing usageTargetTableNames population. The IS NOT DISTINCT FROM template registration follows established patterns across the codebase. A few items to address below.

---

Core Fix — PreAggregationPartitionRangeLoader.ts

Looks good. The fix at lines 353–370 correctly mirrors the partitioned branch's behavior: for non-partitioned rollups, every __usage_N suffix simply maps to the single result.targetTableName. Key observations:

• The if (result && ...) null guard is correct — loadPreAggregation() can return null (line 173 in PreAggregationLoader.ts under externalRefresh when no version entry exists).
• The { ...result, usageTargetTableNames } spread cleanly extends the result without mutating it.
• The downstream consumer in PreAggregations.ts:578-590 already handles the usageTargetTableNames property correctly — it maps each suffix+usage pair to a separate PreAggregationTableToTempTable entry. No changes needed there.

One minor observation: the partitioned branch (lines 302–339) iterates with Object.entries() to access both key and value, because it needs usageInfo.dateRange for partition filtering. The non-partitioned branch only needs the keys, so Object.keys() is the right choice here — well done.

---

CubeStoreQuery.ts — IS NOT DISTINCT FROM

Looks good. This follows the exact pattern already used by:

• PostgresQuery.ts:102
• BigqueryQuery.ts:369
• SnowflakeQuery.ts:121

CubeStore's SQL frontend natively supports IS NOT DISTINCT FROM, so this is safe. The improvement from multi-clause OR (a IS NULL AND b IS NULL) to single IS NOT DISTINCT FROM per dimension in JOIN ON is meaningful for query readability and plan efficiency.

---

Integration Test — pre-aggregations-multi-stage.test.ts

Looks good. The test properly exercises the two-branch multi-usage scenario:

• revenue_no_id_sum and revenue_doubled_no_id_sum both reduce by id, creating two distinct scans of the same pre-aggregation.
• revenue_no_id_pct combines them as a ratio, triggering the multi-usage planner path.
• The expected values (50% = revenue / (revenue * 2)) are mathematically correct and validate that both branches resolve correctly.
• The assertions on preAggregationsDescription verify both branches target the same pre-aggregation (md_revenue_by_id).

---

Driver Tests — Fixture & Skip Configuration

Potential issue with the "two multi-stage branches sharing one pre-aggregation" test:

This test is added to testQueries.ts (line 1915) and will run for all drivers, but:

• It is only added to the skip list in postgres.json (line 204).
• No other driver fixture (athena, bigquery, databricks-jdbc, mysql, redshift, snowflake) has this test in either skip or tesseractSkip.
• Snapshots for this test only exist in postgres-full.test.ts.snap — no other driver has a matching snapshot.

By contrast, the "SeveralMultiStageMeasures" test (which is also multi-stage) is present in both skip and tesseractSkip for drivers like athena, bigquery, databricks-jdbc, and redshift. If those drivers are expected to run the "two multi-stage branches" test, they need snapshots. If not, they need skip entries.

Recommendation: Either add the "two multi-stage branches" test to the skip list (and tesseractSkip where appropriate) for all non-postgres drivers, or add the corresponding snapshots. Otherwise CI will likely fail for those drivers. Fix this →

---

Postgres Snapshot Reordering

The postgres-full.test.ts.snap diff shows the "ECommerce: count by month + order with non-UTC timezone (Asia/Kolkata)" snapshot being moved from the end of the file to its alphabetically sorted position. This is purely a sort-order change — the snapshot content is identical. Likely caused by Jest's snapshot ordering when new snapshots are inserted. This is benign.

---

Summary

Area	Status	Notes
Core bug fix	✅ Correct	Non-partitioned branch now populates usageTargetTableNames
IS NOT DISTINCT FROM	✅ Correct	Follows established pattern
Integration test	✅ Good coverage	Validates two-branch multi-usage scenario
Driver test - base measure	✅ Complete	Snapshots and skips for all drivers
Driver test - two branches	⚠️ Missing skips/snapshots	Only postgres has skip entry and snapshot
Snapshot changes	✅ Clean	Only additions + one benign reorder

The core fix is solid. The main item to address is the missing skip/tesseractSkip entries (or snapshots) for the "two multi-stage branches" test across non-postgres drivers.

[codecov]

Codecov Report

❌ Patch coverage is 25.00000% with 6 lines in your changes missing coverage. Please review.
✅ Project coverage is 58.41%. Comparing base (346eb56) to head (3beefca).
⚠️ Report is 9 commits behind head on master.

Files with missing lines	Patch %	Lines
...orchestrator/PreAggregationPartitionRangeLoader.ts	28.57%	4 Missing and 1 partial ⚠️
...bejs-schema-compiler/src/adapter/CubeStoreQuery.ts	0.00%	1 Missing ⚠️

❗ There is a different number of reports uploaded between BASE (346eb56) and HEAD (3beefca). Click for more details.

HEAD has 1 upload less than BASE

Flag	BASE (346eb56)	HEAD (3beefca)
cubesql	1	0

Additional details and impacted files

@@             Coverage Diff             @@
##           master   #10925       +/-   ##
===========================================
- Coverage   78.93%   58.41%   -20.53%     
===========================================
  Files         470      216      -254     
  Lines       92862    17000    -75862     
  Branches     3449     3450        +1     
===========================================
- Hits        73304     9931    -63373     
+ Misses      19054     6564    -12490     
- Partials      504      505        +1     

Flag	Coverage Δ
cube-backend	58.41% <25.00%> (-0.03%)	⬇️
cubesql	?

Flags with carried forward coverage won't be shown. Click here to find out more.

☔ View full report in Codecov by Sentry.
📢 Have feedback on the report? Share it here.

🚀 New features to boost your workflow:

• ❄️ Test Analytics: Detect flaky tests, report on failures, and find test suite problems.
• 📦 JS Bundle Analysis: Save yourself from yourself by tracking and limiting bundle sizes in JS merges.