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.