MSSQL SQL API pushdown omits joins from generated SELECT statements

[davidda]

MSSQL SQL API pushdown omits joins from generated SELECT statements

Describe the bug

The MSSQL override of statements.select does not render the joins input. Cube can plan a join between grouped subqueries, but the generated SQL projects columns from the right-hand alias without emitting the JOIN that defines it.

SQL Server then rejects the generated query:

The multi-part identifier "a.task_reference" could not be bound.

The base dialect already renders each join. Its MSSQL override is missing that loop.

Observed SQL API query shape

The failure was reproduced with two semantic views exposing a shared task reference. Each source query succeeds independently. The grouped-CTE join fails:

WITH eligible_reports AS (
  SELECT task_reference, company_code
  FROM dynamic_reports
  WHERE report_template_type = 'RPX'
  GROUP BY task_reference, company_code
), affected_reports AS (
  SELECT task_reference
  FROM repair_defect_analysis
  WHERE defect_identifier = 'DefRep_BoardsMissingPartially'
  GROUP BY task_reference
)
SELECT r.company_code,
       COUNT(*) AS eligible_reports,
       COUNT(a.task_reference) AS affected_reports
FROM eligible_reports r
LEFT JOIN affected_reports a ON a.task_reference = r.task_reference
GROUP BY r.company_code;

There is deliberately no ORDER BY here. This isolates missing joins from the independent MSSQL null-ordering defect.

The full source model is not required to demonstrate the template omission. The following inspection works against the published package:

const { MssqlQuery, BaseQuery } = require('@cubejs-backend/schema-compiler');
for (const Query of [BaseQuery, MssqlQuery]) {
  const query = Object.create(Query.prototype);
  const template = query.sqlTemplates().statements.select;
  console.log(Query.name, template.includes('{% for join in joins %}'));
}
// BaseQuery true
// MssqlQuery false

Generated SQL failure

This is a reduced representation of the observed generated SQL, with the expanded model replaced by a literal source:

SELECT r.company_code, a.task_reference
FROM (
  SELECT 'A' AS company_code, 'task-1' AS task_reference
) AS r;

The projection still references a, but its join is absent. SQL Server reports that a.task_reference cannot be bound. This occurs before the presence or absence of matching data matters.

Expected behavior and proposed fix

Render the supplied joins after either FROM branch and before WHERE, as BaseQuery does:

{% for join in joins %}
{{ join }}{% endfor %}

Each supplied join retains its own type, source and ON condition. An empty join list renders nothing. The change does not alter pagination, sorting, semantic relationships or query rewriting.

The local patch adds this one line to packages/cubejs-schema-compiler/src/adapter/MssqlQuery.ts:

'{% for join in joins %}\n{{ join }}{% endfor %}' +

Versions

• Reproduced with cubejs/cube:v1.7.33 and Azure SQL, compatibility level 150.
• Present in upstream commit 650e0b5814bd72c79bb8ed9649a851f74f7a3566, package version 1.7.34.