cube-js / cube-js/cube

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

Open
#11,826 0 comments 0 reactions 0 assignees View on GitHub
Dominant language
Rust
Stars
20.8k
Forks
2.1k
Avg merge
1d 10h
Merged PRs (30d)
203

Description

# 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:

```sql
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);
```

```yaml
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`:

```sql
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:

```sql
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](https://github.com/cube-js/cube/blob/master/packages/cubejs-schema-compiler/src/adapter/BaseQuery.js) supplies `TRUE` and `FALSE` templates. [MssqlQuery](https://github.com/cube-js/cube/blob/master/packages/cubejs-schema-compiler/src/adapter/MssqlQuery.ts) inherits them while mapping the boolean type to `BIT`.

The [SQL API wrapper](https://github.com/cube-js/cube/blob/master/rust/cubesql/cubesql/src/compile/engine/df/wrapper.rs) 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.

Contributor guide

Open the contributing guide

Research direction

Start with the TRUE/FALSE templates in packages/cubejs-schema-compiler/src/adapter/BaseQuery.js and the inherited behavior in MssqlQuery.ts, then trace scalar and predicate rendering in rust/cubesql/cubesql/src/compile/engine/df/wrapper.rs. Run the supplied SQL Server fixture and SQL API queries with EXPLAIN. Done means valid MSSQL in filters, NOT expressions, projections, and grouped predicates while preserving NULL semantics.

Written by the indexing model from the issue text.

Assessment

Tech stack
javascript, rust, sql, typescript
Domain
api, backend, compilers, databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Active
Clarity
Mostly clear
Newbie friendliness
48/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.