Superset 4.1 upgrade breaks Pinot charts requiring joins

[yuribogomolov]

Bug description

Issue

After upgrading from Superset 4.0.2 to 4.1.1, charts that rely on JOIN operations (such as those using a series limit) fail with a "JOIN is not supported" error when using Apache Pinot as the database backend. This issue stems from a recent PR that assumes Pinot supports JOINs by default—even though not all Pinot versions (or configurations) actually do.

Background on Pinot JOIN Support

Lates Pinot versions can perform JOINs using the multi-stage engine. However:

• Older Pinot releases lack multi-stage engine support entirely.
• Even in current releases, the multi-stage engine isn’t enabled by default.
• Enabling the multi-stage engine introduces overheads and degrades performance.

Some users might be running older Pinot versions without multi-stage engine support. Others, though on newer versions, may opt not to enable the multi-stage engine for performance or operational reasons. Consequently, they cannot fully leverage JOIN functionality, yet Superset 4.1.1 now assumes they can.

Steps to Reproduce

• Configure Superset 4.1.1 with Apache Pinot v1 (or any version/configuration not running multi-stage engine).
• Create a bar chart that applies a series limit (which requires a JOIN in the generated query).
• Attempt to run the chart and observe the query failure: Error: "JOIN is not supported"

Screenshots/recordings

Superset version

4.1.1

Python version

3.11

Node version

16

Browser

Chrome

Additional context

Preferred Approach:
Ideally, Superset should differentiate between Pinot’s v1 engine and v2 (multi-stage engine) at the connection level. That way, users can explicitly enable or disable JOIN support based on their Pinot version and configuration.

Interim Measure:
If supporting both engines simultaneously is too large in scope, reverting the assumption that Pinot supports JOINs would unblock upgrades to Superset 4.1.1 for users unable or unwilling to enable the multi-stage engine.

Checklist

• I have searched Superset docs and Slack and didn't find a solution to my problem.
• I have searched the GitHub issue tracker and didn't find a similar bug report.
• I have checked Superset's logs for errors and if I found a relevant Python stacktrace, I included it here as text in the "additional context" section.

[dosubot]

To address the issue with Superset 4.1.1 assuming that Apache Pinot supports JOINs by default, you can configure Superset to explicitly disable JOIN operations for databases that do not support them. This can be done by setting the allows_joins attribute to False in the database configuration. This will prevent Superset from attempting JOIN operations that are not supported by your current Pinot setup ^[1].

Additionally, if you are using a version of Pinot that supports the multi-stage engine, you can enable it by adding the following argument in the database connection settings under Advanced -> Other -> ENGINE PARAMETERS:

{"connect_args":{"use_multistage_engine":"true"}}

This will allow Superset to perform JOIN operations if the multi-stage engine is available and enabled ^[2].

To continue talking to Dosu, mention @dosu.

---

^{Help Dosu learn! Give it feedback: Great Response | Irrelevant Answer | Incorrect Sources | Too Verbose | Hallucination | Bug Report | Other}