Open phongvu99 opened 1 year ago
Had the same problem. Thing was i executed the query from the sql-lab. But i had to transform my dataset to a virtual one, the query will be added here and after that define a time range filter in the dataset itself.
Had the same problem. Thing was i executed the query from the sql-lab. But i had to transform my dataset to a virtual one, the query will be added here and after that define a time range filter in the dataset itself.
Thanks, but it's still not working. The key is to get the SQL Lab to recognize the param, and return N/A - due to filter_value instead of Parameter error
This is indeed an annoying bug. What I needed:
SELECT '{{ from_dttm }}'
, I then under Parameters I add {"from_dttm": "2023-01-01 00:00:00"}. What happens here, is that query goes through and I can then click to explore the dataset and create virtual dataset from there. However, this method, causes a sticky value of "2023-01-01 00:00:00" , and value won't get updated from the filters in the view.
What I did to fix this:from_dttm
being in the sql it fails to sync. Now, I have to once delete that statement, sync, and bring back the from_dttm
to the query.You can actually get around this in SQL Lab by setting the values yourself at the top of the SQL Lab window.
{% set from_dttm = '2023-09-18T12:00:00' %}
{% set to_dttm = '2023-10-18T12:00:00' %}
SELECT * FROM ...
Expanding on @Ralkion's solution, I found a nice concise way to set defaults with Jinja, that doesn't overwrite actual values later:
WITH
toDateTime('{{ from_dttm | default('2023-09-18T12:00:00', true) }}') AS from_dttm,
toDateTime('{{ to_dttm | default('2023-10-18T20:00:00', true) }}') AS to_dttm,
to_dttm - from_dttm AS range_dttm,
{{ row_limit | default(1000, true) }} AS row_limit
SELECT *
FROM ...
NB: toDateTime()
is specific to the ClickHouse SQL dialect, you will probably need to use some alternative like CONVERT()
It's been a long time sine anyone touched this, and it sounds like there are valid workarounds. I'm tempted to close it as stale. Are there any proposals to be had on how to fix this more comprehensively, or docs that are worth adding to the documentation site? Is this even still an issue in 3.1/4.0 (the currently supported versions)?
Still seems to be an Issue on 3.1.1
Description: Jinja Template {{from_dttm}} and {{to_dttm}} variables not working in SQL queries.
How to reproduce the bug
Expected results
The results filtered by the date - From A TO B
Actual results
Screenshots
Superset Jinja Variables Issue
Environment
(please complete the following information):
Checklist
Make sure to follow these steps before submitting your issue - thank you!
Additional context
https://github.com/apache/superset/discussions/19619 The issue first discussion, but no solution so far