[BUG]: Spark SPARQL translator silently drops unsupported clauses and breaks OPTIONAL

[benoitcayladbx]

Is there an existing issue for this?

• I have searched the existing issues

Current Behavior

The Explorer SPARQL path (SparqlTranslator.translate_sparql_to_spark) translates SPARQL to Spark SQL with a regex pipeline. Valid SPARQL outside the supported subset can disappear from the generated SQL without an error. The SQL is executable but has different semantics from the submitted query.

Confirmed silent drops:

• GROUP BY / aggregates
• numeric or complex FILTER expressions (the parser never emits gt/lt/gte/lte, despite the docstring advertising FILTER(?var > value))
• property paths

A second, independent defect: relationship OPTIONAL patterns emit LEFT JOINs, then add {rel}.{col} IS NOT NULL predicates to the top-level WHERE. A single OPTIONAL becomes mandatory; multiple OPTIONALs are OR-joined, so the query requires at least one optional match.

The public /api/v1/query default (engine=local) is not an equivalent fallback: RDFLib queries the R2RML mapping graph, not warehouse data. Explorer (/dtwin/execute, /dtwin/translate) always uses the Spark translator with no RDFLib fallback.

Expected Behavior

1. Valid-but-unsupported Spark SPARQL is rejected with ValidationError before SQL generation (fail-closed). Messages identify the construct, e.g. Spark SPARQL does not support GROUP BY.
2. The currently supported subset still translates (SELECT, DISTINCT, LIMIT, simple BGP, supported string FILTERs, literal BIND, specialized relationship UNION).
3. Relationship OPTIONAL preserves unmatched primary rows (LEFT JOIN without a top-level presence predicate). Unbound optional vars are NULL.
4. Local RDFLib is not advertised as a warehouse-data fallback.

Steps To Reproduce

1. Open Explorer SPARQL against a mapped domain with warehouse data.
2. Run:

SELECT ?s WHERE { ?s a <http://example.org/Customer> } GROUP BY ?s

3. Observe a successful SQL translation with no GROUP BY (same for numeric FILTER and property paths).
4. Run a relationship OPTIONAL:

SELECT ?c ?o WHERE {
  ?c a <http://example.org/Customer> .
  OPTIONAL { ?c <http://example.org/hasOrder> ?o }
}

5. Inspect generated SQL: LEFT JOIN plus WHERE (rel.customer_id IS NOT NULL), which drops customers with no order.

Unit-level reproduction (no warehouse):

from back.core.w3c.sparql.SparqlTranslator import SparqlTranslator
# GROUP BY / FILTER / property path currently return success with incomplete SQL
# OPTIONAL currently emits WHERE (rel_o.customer_id IS NOT NULL)

Cloud

Azure

Browser

Chrome

OntoBricks Version

0.9.0

Relevant log output

# Example: GROUP BY query currently succeeds with incomplete SQL
SELECT t_s.customer_id AS s
    FROM (SELECT * FROM test_catalog.test_schema.customers) AS t_s
    WHERE 1=1 LIMIT 10

# Example: relationship OPTIONAL currently adds a presence filter
SELECT t_c.customer_id AS c, rel_o.order_id AS o
    FROM ... LEFT JOIN (...) AS rel_o ON t_c.customer_id = rel_o.customer_id
    WHERE (rel_o.customer_id IS NOT NULL)
LIMIT 10

Additional Context

Primary files

• src/back/core/w3c/sparql/SparqlTranslator.py (_parse_value_filters ~505–611, _spark_finalize_query_sql ~2036–2041, OPTIONAL LEFT JOIN + optional_rel_conditions ~1813–1943)
• src/back/core/w3c/sparql/DomainQueryService.py (local engine queries R2RML, not warehouse)
• tests/back/core/w3c/sparql/test_sparql_translator_units.py (19 tests; 9 use if result.get("success") and can pass on failed translation)

Approved design (this issue)

docs/superpowers/specs/2026-09-11-spark-sparql-fail-closed-design.md

Scope: RDFLib algebra capability gate + OPTIONAL correction + test hardening. Non-goals: full SPARQL 1.1, numeric FILTER implementation, algebra-to-SQL rewrite, RDFLib fallback, SWRL/T-Box changes.

Related findings (separate follow-ups, not this issue)

• SWRL: negated built-ins silently skipped in _build_negated_atoms; unbound vars become string literals
• Reasoning: no T-Box ↔ A-Box fixed-point; CWA/UNA from NOT EXISTS under-documented
• Lint noise: claimed F821 locations are stale (0 on current tree); B904/F841 are hygiene

Acceptance

• Unsupported constructs raise ValidationError instead of returning incomplete SQL
• Relationship OPTIONAL keeps unmatched rows
• Translator unit tests assert success unconditionally
• Docs state the Spark SPARQL subset and that local RDFLib is not a warehouse fallback

[benoitcayladbx]

Status

This is not fixed in 0.8.0. The fail-closed Spark SPARQL gate and the relationship OPTIONAL correction land in 0.8.1.

Until then, Explorer SPARQL (/dtwin/execute, /dtwin/translate) still uses the regex translator. Valid SPARQL outside the supported subset can disappear from the generated SQL with no error. Relationship OPTIONAL still emits LEFT JOIN plus a top-level {rel}.{col} IS NOT NULL predicate.

POST /api/v1/query with engine=local is not a warehouse-data workaround: RDFLib queries the R2RML mapping graph, not Databricks tables.

Workaround on 0.8.0

1. Relationship OPTIONAL

Do not rely on OPTIONAL for relationships. A single OPTIONAL becomes mandatory; several OPTIONALs require at least one match.

If you only need matched pairs, drop OPTIONAL:

SELECT ?c ?o WHERE {
  ?c a <http://example.org/Customer> .
  ?c <http://example.org/hasOrder> ?o
}

If you need unmatched primaries (true left-join semantics), run two queries and left-join in a notebook or SQL:

SELECT ?c WHERE { ?c a <http://example.org/Customer> }

SELECT ?c ?o WHERE {
  ?c a <http://example.org/Customer> .
  ?c <http://example.org/hasOrder> ?o
}

2. GROUP BY, numeric / complex FILTER, property paths

Stay in the subset the translator actually implements: SELECT / SELECT *, DISTINCT, LIMIT, simple BGP, string filters (CONTAINS, STR(...) =, STRSTARTS, STRENDS), predicate IN, literal BIND.

• Aggregates: run a simple SPARQL SELECT, then GROUP BY in Databricks SQL.
• Numeric predicates: do not use FILTER(?x > 5); filter in SQL or after fetch.
• Property paths (+, /, *): expand to explicit triples.

Use Translate and inspect the SQL. If GROUP BY, the FILTER, or the path is missing, the result is not the query you submitted.

0.8.1

Unsupported constructs raise ValidationError before SQL generation. Relationship OPTIONAL keeps unmatched rows (LEFT JOIN without a presence WHERE).