Possible bug in jinja template processing on datasets

[rluech]

Bug description

I can successfully use jinja template in SQL Lab and Row Level Security. For example, this works fine:
select * from schema.table where "User Principal Name" = '{{ current_username() }}'

I can also set a custom column to a dataset with "SQL EXPRESSION":
"User Principal Name" = '{{ current_username() }}'`
However, when I use this custom column as a dashboard filter I get the DB Error: "FROM keyword not found where expected". Based on the related Q&A and issues I wonder if there is a bug in the jinja template processing on datasets?

related:
Q&A 32215
Q&A 30448
issue 25786
fix 33195

Screenshots/recordings

No response

Superset version

master / latest-dev

Python version

3.9

Node version

16

Browser

Chrome

Additional context

No response

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]

I found a similar unsolved discussion related to Jinja template processing: Jinja Template, which might provide some insights or context for your issue ^[1].

Regarding the specific issue you're facing with Jinja templates in custom columns used as dashboard filters, there isn't a direct solution documented in the repository. However, it's worth noting that Superset processes Jinja templates by enabling the ENABLE_TEMPLATE_PROCESSING feature flag in superset_config.py. This allows Python code to be embedded in virtual datasets and Custom SQL, which might be relevant to your setup ^[2].

If you haven't already, ensure that this feature flag is enabled and that your Jinja context is correctly set up. Additionally, reviewing the related Q&A and issues you mentioned might provide further insights or workarounds.

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}

[rusackas]

I know @Vitor-Avila / @betodealmeida / @villebro / @justinpark are looking into the PR you linked to. I'm not sure if they have any additional input here. This might be effectively a duplicate of #25786 which you also linked to. I think we simply don't support jinja in virtual datasets right now, but I'm far from an expert here :)

[Vitor-Avila]

@rluech in your case, what's the final SQL query used to power the dataset? is it working to build charts, and only failing for the dashboard filter? or is it not working at all?

[rluech]

Issue is solved. Jinja template is processed in custom column as expected.

@rusackas it is actually a physical dataset with custom columns (not a virtual dataset) .
@Vitor-Avila the error occurs already on chart level. Good question, that helped.

The chart query shows that the jinja template was evaluated es expected and there is a plain string in place of the jinja template.

The solution is simple, in the sql expression of the custom column, I replaced:
"User Principal Name" = '{{ current_username() }}'
with:
CASE WHEN "User Principal Name" = '{{ current_username() }}' THEN 1 ELSE 0 END