MSSQL SQL API pushdown emits boolean literals and predicates in invalid contexts

MSSQL SQL API pushdown emits boolean literals and predicates in invalid contexts

Affected configuration

Observed with a Cube Core 1.7.34 deployment, the MSSQL driver and SQL API queries using CubeScanWrappedSql. Simple boolean filters and projections can succeed; the failures depend on the pushed-down plan.

Minimal fixture and queries

Create this fixture in a disposable SQL Server database:

CREATE TABLE dbo.boolean_fixture (
  id INT PRIMARY KEY,
  company_code VARCHAR(10),
  completed BIT NULL
);
INSERT INTO dbo.boolean_fixture VALUES
  (1, 'a', 1), (2, 'b', 0), (3, 'b', NULL);

cubes:
  - name: boolean_fixture
    sql_table: dbo.boolean_fixture
    dimensions:
      - name: id
        sql: id
        type: number
        primary_key: true
        public: true
      - name: company_code
        sql: company_code
        type: string
      - name: completed
        sql: completed
        type: boolean
    measures:
      - name: count
        type: count

Run these through the SQL API and inspect EXPLAIN:

SELECT COUNT(DISTINCT company_code) = 2 AS flag FROM boolean_fixture;
SELECT company_code, MEASURE(count)
FROM boolean_fixture WHERE completed = TRUE GROUP BY company_code;
SELECT company_code, MEASURE(count)
FROM boolean_fixture WHERE NOT completed GROUP BY company_code;
SELECT completed IS NULL AS missing, MEASURE(count)
FROM boolean_fixture GROUP BY completed IS NULL;

Expected results are a true aggregate flag, one row for company a in the true filter, one row for b in the negated filter, and grouped counts of 2 for false and 1 for true in the NULL projection. NULL must remain unknown in nullable comparisons and NOT expressions.

Observed behavior

Equivalent queries against the original model produced these invalid fragments, with names simplified:

WHERE completed = TRUE
WHERE completed = FALSE
WHERE NOT (completed)
SELECT (aggregate_result.distinct_count = 2) AS flag
SELECT (completed IS NULL) AS missing

SQL Server reported invalid column names TRUE and FALSE, a non-boolean expression where a condition was expected, and a syntax error for the predicate-valued aggregate projection. Replacing that aggregate projection with CASE WHEN ... = 2 THEN 1 ELSE 0 END executed successfully. A cast-false input still became FALSE in a failing filter plan. Removing ordering exposed the non-boolean-condition error previously obscured by a FETCH NEXT error.

Confirmed source cause

BaseQuery supplies TRUE and FALSE templates. MssqlQuery inherits them while mapping the boolean type to BIT.

The SQL API wrapper renders scalars and predicates through the same expression function. Its binary and NOT renderers do not adapt expressions to their consuming context. MSSQL segment-specific conversions do not cover these general expressions.

Replacing literals with 1/0 alone leaves invalid predicate projections and NOT(bit_column). General scalarization must preserve unknown, for example CAST(CASE WHEN p THEN 1 WHEN NOT p THEN 0 ELSE NULL END AS BIT) for a deterministic predicate. The existing segment scalarization maps unknown to false and is unsuitable as a general replacement.

The rendering boundary can be corrected without a planner redesign. Separately, the pinned DataFusion SQL planner lowers IS [NOT] TRUE/FALSE to ordinary equality/inequality, returning NULL for NULL inputs instead of the required true/false result. That earlier lowering bug is outside this rendering issue. Pagination and repeated-parameter identity in GROUP BY are also separate issues.