substrait generated by Apache Calcite does not run in DataFusion

[alamb]

Describe the bug

This is a report from @lmwnshn

As part of a series of issues that were discovered at CMU while working on DataFusion

• [DISCUSSION] Lowering the barrier to new users (Lessons from-799 CMU Optimizer Class) #14373

Basically they found that DataFusion couldn't run substrait generated by Apache Calcite

Quoting the readme: https://github.com/lmwnshn/15799-s25-project1-remnants/blob/main/README.md

The idea behind this project was to:

1. Convert SQL to a Substrait plan with Calcite.

2. Execute the Substrait plan on different systems (e.g., DuckDB, DataFusion).
   Students would then experiment with Calcite's different rules. However, this project idea didn't quite work for the following reasons:

3. substrait-java does not run optimizations - while in theory this can be enabled, I recall running into some issues.

4. More critically, at the time of writing, both DuckDB and DataFusion have limited support for executing the Substrait generated by Calcite.

To Reproduce

See the code at https://github.com/lmwnshn/15799-s25-project1-remnants

Note I haven't looked at any of this myself so I won't be able to help with specific questionds

Expected behavior

datafusion should work for all the plans

Additional context

No response

[lmwnshn]

I think people can look directly at Substrait's consumer-testing repo for DataFusion if they want to fix this :) My repo is just a stripped-down rewrite for students.

Some relevant links:

• https://github.com/substrait-io/consumer-testing/tree/main/substrait_consumer
• https://github.com/substrait-io/consumer-testing/blob/main/substrait_consumer/consumers/datafusion_consumer.py
• https://github.com/substrait-io/consumer-testing/blob/main/substrait_consumer/snapshots/results/integration/tpch/q01-isthmus-datafusion_outcome.txt

[alamb]

FYI @vbarua and @Blizzara -- I think this may be relevant to your usecase

[alamb]

@niebayes said they may be interested in this:

• [DISCUSSION] Lowering the barrier to new users (Lessons from-799 CMU Optimizer Class) #14373 (comment)

[niebayes]

Query federation is amazing. I will first look at the Substrait's consumer-testing repo for DataFusion.

[bvolpato]

Hey folks, I looked into https://github.com/substrait-io/consumer-testing and figured out what's going on with all those failing plans.

The problem is related to table name case sensitivity. Out of the 120 Isthmus-generated Substrait plans in the test suite, 118 blow up in DataFusion for exactly the same reason:

- No table named 'LINEITEM' (37 plans)  
- No table named 'PARTSUPP' (33 plans)  
- No table named 'CUSTOMER' (18 plans)  
- No table named 'ORDERS' (12 plans)  
- No table named 'T' (8 plans)  
- No table named 'PART' (3 plans)  
- No table named 'SUPPLIER' (3 plans)  
- No table named 'NATION' (3 plans)  
- No table named 'REGION' (1 plan)  

Calcite/Isthmus uppercases everything (SQL standard style), while DataFusion lowercases. Substrait itself has no quoted/unquoted concept, NamedTable.names is just a list of plain strings. So Isthmus spits out ["LINEITEM"] and DataFusion's catalog only knows lineitem - so lookup fails.

Quick design question: where should we actually normalize these names?

DataFusion already normalizes identifiers right at the SQL entry point (SqlToRel uses IdentNormalizer to lowercase unquoted stuff based on enable_ident_normalization). Catalog lookups stay strictly case-sensitive after that.

For the Substrait consumer, a few ideas:

Option A – normalize right at the input boundary (in read_rel.rs)
Treat the names like unquoted identifiers and just do:

let table_reference = TableReference::parse_str(&nt.names.join("."));

Super clean, mirrors the SQL path exactly, and stays in sync if normalization rules ever change.

Downside: since Substrait has no quoting, there's no way for a producer to say "keep this exact case." A round-trip (register DATA via bare reference) would turn it into data on the way back. Not a huge deal in practice because DataFusion's SQL path always normalizes anyway.

Option B – add a fallback in resolve_table_ref
Try exact match first, then fall back to the normalized version. That way if someone really registered the uppercase name it still works, but we catch the Calcite case too.

Downside: feels a bit magical. The rest of DataFusion's catalog layer doesn't do case-insensitive fallbacks.

Option C – don't touch DataFusion at all
Just make the consumer-testing framework register tables in both cases, or tweak Isthmus to output lowercase. Quick win for the tests, but doesn't solve the broader interop issue.

---

I'm leaning toward Option A: it's explicit, consistent with how the rest of the SQL path works, and keeps things simple (the proposal is here in draft: #20993)

cc @benbellick

[benbellick]

I'm still thinking of a response to the above comment, but just wanted to say that I don't have any experience with the consumer-testing library. It doesn't look like it is actively maintained. At the very least, it is using a version of substrait-java that is ~9 months old.

[bvolpato]

Right, but I guess the main question is how we should be addressing case sensitivity when it comes to plans in the consumers.

In DataFusion SQL, if you run a query select * from nation and select * from NATION are no different, they both get normalized to lower case. But we still have a way to enforce the case select * from "NATION" -- there's no way to encode that today in the plan.