The SQL can be executed correctly, but fails to be saved as a dataset.

[stgztsw]

superset version 6.0.0rc1
In SQL Lab, i can execute the sql correctly，but fails to be saved as a dataset.
I want to bind the SQL parameters to the dashboard filters, so I defined multiple SQL parameters. and ENABLE_TEMPLATE_PROCESSING has been set as true.

SQL is like this:
select *
from udesk_cargo_management
where 1=1
{% if filter_values('is_apply') is not none %}
and is_apply in {{ filter_values('is_apply')|where_in }}
{% endif %}
{% if filter_values('core_attachment') is not none %}
and core_attachment in {{ filter_values('core_attachment')|where_in }}
{% endif %}
{% if filter_values('english_name') is not none %}
and english_name in {{ filter_values('english_name')|where_in }}
{% endif %}
{% if filter_values('attachment_name') is not none %}
and attachment_name in {{ filter_values('attachment_name')|where_in }}
{% endif %}
{% if filter_values('attachment_number') is not none %}
and attachment_number in {{ filter_values('attachment_number')|where_in }}
{% endif %}

Parameters definition:
{
"_filters": [
{
"col": "is_apply",
"op": "IN",
"val": ["是"]
},
{
"col": "core_attachment",
"op": "IN",
"val": ["耗材","核心备件","易损件"]
},
{
"col": "english_name",
"op": "IN",
"val": ["test"]
},
{
"col": "attachment_name",
"op": "IN",
"val": ["中文名"]
},
{
"col": "attachment_number",
"op": "IN",
"val": ["1234567"]
},
{
"col": "mechine_version",
"op": "IN",
"val": ["V1.2.3"]
},
{
"col": "product_id",
"op": "IN",
"val": ["GS0001"]
},
{
"col": "robot_family_code",
"op": "IN",
"val": ["40","50","75","111","S","通用"]
}
]
}

The sql executed correctly and the template has also been replaced normally.
But when I saved sql as dataset, below error occured
2025-09-17 10:15:25,280:ERROR:flask_appbuilder.api:(1105, 'errCode = 2, detailMessage = Syntax error in line 170:\n AND is_apply IN ()\n ^\nEncountered: )\nExpected: IDENTIFIER\n')
Traceback (most recent call last):
File "/app/superset/connectors/sqla/utils.py", line 153, in get_columns_description
cursor.execute(mutated_query)
File "/app/.venv/lib/python3.11/site-packages/MySQLdb/cursors.py", line 179, in execute
res = self._query(mogrified_query)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/app/.venv/lib/python3.11/site-packages/MySQLdb/cursors.py", line 330, in _query
db.query(q)
File "/app/.venv/lib/python3.11/site-packages/MySQLdb/connections.py", line 265, in query
_mysql.connection.query(self, query)
MySQLdb.OperationalError: (1105, 'errCode = 2, detailMessage = Syntax error in line 170:\n AND is_apply IN ()\n ^\nEncountered: )\nExpected: IDENTIFIER\n')

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
File "/app/.venv/lib/python3.11/site-packages/flask_appbuilder/api/init.py", line 115, in wraps
return f(self, *args, **kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^
File "/app/superset/views/base_api.py", line 120, in wraps
duration, response = time_function(f, self, *args, **kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/app/superset/utils/core.py", line 1409, in time_function
response = func(*args, **kwargs)
^^^^^^^^^^^^^^^^^^^^^
File "/app/superset/utils/log.py", line 304, in wrapper
value = f(*args, **kwargs)
^^^^^^^^^^^^^^^^^^
File "/app/superset/views/base_api.py", line 92, in wraps
return f(self, *args, **kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^
File "/app/superset/datasets/api.py", line 360, in post
new_model = CreateDatasetCommand(item).run()
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/app/superset/utils/decorators.py", line 267, in wrapped
return on_error(ex)
^^^^^^^^^^^^
File "/app/superset/utils/decorators.py", line 232, in on_error
raise ex
File "/app/superset/utils/decorators.py", line 260, in wrapped
result = func(*args, **kwargs)
^^^^^^^^^^^^^^^^^^^^^
File "/app/superset/commands/dataset/create.py", line 51, in run
dataset.fetch_metadata()
File "/app/superset/connectors/sqla/models.py", line 1646, in fetch_metadata
new_columns = self.external_metadata()
^^^^^^^^^^^^^^^^^^^^^^^^
File "/app/superset/connectors/sqla/models.py", line 1287, in external_metadata
return get_virtual_table_metadata(dataset=self)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/app/superset/connectors/sqla/utils.py", line 131, in get_virtual_table_metadata
return get_columns_description(
^^^^^^^^^^^^^^^^^^^^^^^^
File "/app/superset/connectors/sqla/utils.py", line 159, in get_columns_description
raise SupersetGenericDBErrorException(message=str(ex)) from ex
superset.exceptions.SupersetGenericDBErrorException: (1105, 'errCode = 2, detailMessage = Syntax error in line 170:\n AND is_apply IN ()\n ^\nEncountered: )\nExpected: IDENTIFIER\n')

[stgztsw]

@dosu
Hi dosu, can you help me to reslove this problem？

[dosubot[bot]]

The error happens because, during dataset creation, Superset doesn't have access to runtime filter values—so your Jinja template's filter_values calls return empty lists. When you use where_in on an empty list, it generates SQL like AND is_apply IN (), which is invalid and causes the syntax error you see. This is different from SQL Lab, where you can provide test filter values and the query works as expected.

To fix this, update your Jinja template to only include the IN clause if filter_values returns a non-empty list. For example:

{% set is_apply_values = filter_values('is_apply') %}
{% if is_apply_values %}
  and is_apply in {{ is_apply_values | where_in }}
{% endif %}

Repeat this pattern for each filter. This way, if no filter values are present, the IN clause is omitted and you avoid generating invalid SQL. You can find more details and examples in the official Superset docs on SQL templating.

Recent Superset versions have improved error reporting for template issues, but template authors still need to guard against empty filter values to prevent errors like this one.

To reply, just mention @dosu.

---

^{How did I do? Good | Irrelevant | Incorrect | Verbose | Hallucination | Report 🐛 | Other}    

[stgztsw]

Hi @dosu, thanks for your reply.
another question, Now I have the a virtural dataset with multiple sql parameters.
How can i bind this sql parameter with filter in chart or dashboard。I can't find any info from offical document.

[dosubot[bot]]

You can bind SQL parameters in a virtual dataset to dashboard or chart filters by using Jinja templating with the filter_values function in your dataset SQL. When you add a filter to a dashboard or chart and map it to a column in your dataset, Superset automatically passes the selected filter values to your SQL via filter_values('column_name')—no extra configuration is needed beyond mapping the filter to the correct column and enabling ENABLE_TEMPLATE_PROCESSING docs.

For each parameter, use defensive Jinja like:

{% set is_apply_values = filter_values('is_apply') %}
{% if is_apply_values %}
  and is_apply in {{ is_apply_values | where_in }}
{% endif %}

Repeat for each filterable column. When a user selects a filter in the dashboard or chart, the value is injected into your SQL at query time. If no filter is selected, the clause is omitted, avoiding SQL errors.

Key points:

• The filter must be mapped to the dataset column (column names must match).
• Use filter_values('colname') in your SQL.
• No need to define parameters in the dataset; just use the Jinja template.
• The _filters parameter is only for SQL Lab testing and does not affect dashboards or charts.
• This works for multiple parameters—just repeat the pattern for each.

More details and examples are in the official SQL templating docs.

Let me know if you need a concrete example for your specific columns or run into any issues with filter mapping!

To reply, just mention @dosu.

---

^{How did I do? Good | Irrelevant | Incorrect | Verbose | Hallucination | Report 🐛 | Other}    

[stgztsw]

You can bind SQL parameters in a virtual dataset to dashboard or chart filters by using Jinja templating with the filter_values function in your dataset SQL. When you add a filter to a dashboard or chart and map it to a column in your dataset, Superset automatically passes the selected filter values to your SQL via filter_values('column_name')—no extra configuration is needed beyond mapping the filter to the correct column and enabling ENABLE_TEMPLATE_PROCESSING docs.

For each parameter, use defensive Jinja like:

{% set is_apply_values = filter_values('is_apply') %}
{% if is_apply_values %}
  and is_apply in {{ is_apply_values | where_in }}
{% endif %}

Repeat for each filterable column. When a user selects a filter in the dashboard or chart, the value is injected into your SQL at query time. If no filter is selected, the clause is omitted, avoiding SQL errors.

Key points:

• The filter must be mapped to the dataset column (column names must match).
• Use filter_values('colname') in your SQL.
• No need to define parameters in the dataset; just use the Jinja template.
• The _filters parameter is only for SQL Lab testing and does not affect dashboards or charts.
• This works for multiple parameters—just repeat the pattern for each.

More details and examples are in the official SQL templating docs.

Let me know if you need a concrete example for your specific columns or run into any issues with filter mapping!

To reply, just mention @dosu.

How did I do? Good | Irrelevant | Incorrect | Verbose | Hallucination | Report 🐛 | Other    

Hi, @dosu
I'm currently dealing with a more complex scenario.
The virtual dataset is a complex nested query. The sql parameter is only in subquery, not in the dataset output column, like this:
select
v1,
v2,
v3
from (
(
select
aa,
bb,
cc,
dd
from udesk_cargo_management
where 1=1
{% if filter_values('is_apply') is not none %}
and is_apply in {{ filter_values('is_apply')|where_in }}
{% endif %}
) t1
right join
(
select
distinct aa, ee, ff, gg
from robot_type rt
left join robot_basic rb
on rt.id = rb.robot_type_id
where 1=1
and ${rt.name:mechine_version}
and ${rb.product_id:product_id}
) t2
on t1.aa = t2.aa
group by t1.aa, t2.ff
...... omit more complex logic ......
)
So, how can I bind the parameter with the filter?

[dosubot[bot]]

You can use Jinja's filter_values in any part of your virtual dataset SQL—including subqueries—even if the filtered column isn't in the final output. Superset injects dashboard or chart filter values into your SQL at runtime, as long as the filter is mapped to the correct column in the dataset. You don't need to expose the filtered column in the SELECT clause; referencing it in the subquery is enough. Just use defensive Jinja like:

{% set is_apply_values = filter_values('is_apply') %}
{% if is_apply_values %}
  and is_apply in {{ is_apply_values | where_in }}
{% endif %}

This pattern works in subqueries, joins, or any nested SQL. If no filter is selected, the clause is omitted, so you avoid SQL errors. The ${rt.name:mechine_version} and ${rb.product_id:product_id} syntax is not standard Superset or Jinja—replace these with Jinja templating if you want dynamic filter binding.

If you want a filter to only apply inside a subquery and not affect the outer query, you can use filter_values('col', remove_filter=True).

No extra configuration is needed beyond mapping the dashboard filter to the dataset column and enabling ENABLE_TEMPLATE_PROCESSING. This approach is fully supported in Superset 6.x for complex/nested queries—see this reference for backend details.

Let me know if you want a concrete example for your specific columns or more help with the Jinja syntax!

To reply, just mention @dosu.

---

^{How did I do? Good | Irrelevant | Incorrect | Verbose | Hallucination | Report 🐛 | Other}    

[stgztsw]

You can use Jinja's filter_values in any part of your virtual dataset SQL—including subqueries—even if the filtered column isn't in the final output. Superset injects dashboard or chart filter values into your SQL at runtime, as long as the filter is mapped to the correct column in the dataset. You don't need to expose the filtered column in the SELECT clause; referencing it in the subquery is enough. Just use defensive Jinja like:

{% set is_apply_values = filter_values('is_apply') %}
{% if is_apply_values %}
  and is_apply in {{ is_apply_values | where_in }}
{% endif %}

This pattern works in subqueries, joins, or any nested SQL. If no filter is selected, the clause is omitted, so you avoid SQL errors. The ${rt.name:mechine_version} and ${rb.product_id:product_id} syntax is not standard Superset or Jinja—replace these with Jinja templating if you want dynamic filter binding.

If you want a filter to only apply inside a subquery and not affect the outer query, you can use filter_values('col', remove_filter=True).

No extra configuration is needed beyond mapping the dashboard filter to the dataset column and enabling ENABLE_TEMPLATE_PROCESSING. This approach is fully supported in Superset 6.x for complex/nested queries—see this reference for backend details.

Let me know if you want a concrete example for your specific columns or more help with the Jinja syntax!

To reply, just mention @dosu.

How did I do? Good | Irrelevant | Incorrect | Verbose | Hallucination | Report 🐛 | Other    

Hi @dosu
How can i mapper the filter to column.
In dataset editor, filter can only be selected as the dataset output column

So, how can I mapper the filter to parameter which is not dataset output column.

[dosubot[bot]]

In Superset, dashboard filters can only be mapped to columns that are present in the dataset's columns list—meaning columns you select in the dataset editor, which are either output columns from your SQL or calculated columns you define. There is no supported way to map a filter to a column that is only used in a subquery or WHERE clause and not included in the dataset columns list.

If you need to filter on a column that's only used in a subquery, the only workaround is to add that column as a calculated column in your dataset. This will make it available for filter mapping, but it will also appear in the dataset's column list and be selectable elsewhere in the UI; there is no way to make it "filter-only" or hidden from the rest of the interface (source).

This is a current limitation of both the backend and the dataset editor UI—there are no hidden options or advanced configurations to bypass it. If you want to keep your dataset UI clean, you might consider naming the calculated column in a way that indicates it's for filtering only, but it will still be visible.

Let me know if you want a concrete example of how to add such a calculated column or need help structuring your SQL!

To reply, just mention @dosu.

---

^{How did I do? Good | Irrelevant | Incorrect | Verbose | Hallucination | Report 🐛 | Other}