[Jinja] {{from_dttm}} and {{to_dttm}} variables not working as expected

[phongvu99]

Description: Jinja Template {{from_dttm}} and {{to_dttm}} variables not working in SQL queries.

How to reproduce the bug

1. Go to 'SQL Lab'
2. Write a query containing either the {{from_dttm}} or {{to_dttm}} variables
3. Run the query
4. See the error

Expected results

The results filtered by the date - From A TO B

Actual results

Parameter error
Failed to execute query '1314' - 'SELECT *
FROM lorem_ipsum.Dim_Lorem_Ipsum
WHERE (
  {% if from_dttm is not none %}
      dttm_col > {{ from_dttm }} AND
  {% endif %}
  {% if to_dttm is not none %}
      dttm_col < {{ to_dttm }} AND
  {% endif %}
  true
)': The following parameters in your query are undefined: "from_dttm", "to_dttm". 
 Please check your template parameters for syntax errors and make sure they match across your SQL query and Set Parameters. Then, try running your query again.

Screenshots

Superset Jinja Variables Issue

Environment

(please complete the following information):

• browser type and version: Microsoft Edge (beta) Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/105.0.0.0 Safari/537.36 Edg/105.0.1343.27
• superset version: latest-dev
• python version: running on k8s, defined by the Helm charts
• node.js version: N/A
• any feature flags active: ALERT_REPORTS, ENABLE_TEMPLATE_PROCESSING, DYNAMIC_PLUGINS

Checklist

Make sure to follow these steps before submitting your issue - thank you!

• [x] I have checked the superset logs for python stacktraces and included it here as text if there are any.
• [x] I have reproduced the issue with at least the latest released version of superset.
• [ ] I have checked the issue tracker for the same issue and I haven't found one similar.

Additional context

https://github.com/apache/superset/discussions/19619 The issue first discussion, but no solution so far

[ASchmidtGit]

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.

This tutorial send me the right way

[phongvu99]

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.

This tutorial send me the right way

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

[mona-mk]

This is indeed an annoying bug. What I needed:

• I needed a virtual dataset, and how I create a virtual dataset typically is: from sql editor, let's say I have 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:
• created the virtual dataset from the dataset view. Opened the original dataset and converted it to a new virtual dataset, defined the sql statements and changed the name. Now this new one, won't have the cached value of "2023-01-01 00:00:00". However, I still need to click on "SYNC COLUMNS FROM SOURCE" to get the correct columns added, and with the 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.

[Ralkion]

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 ...

[Henri-J-Norden]

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()

Alternatively, using the datetime bounds from the dataset as the defaults

WITH
  (SELECT MIN( YOUR_DATETIME_FIELD ) FROM ...) AS _first_dttm,
  (SELECT MAX( YOUR_DATETIME_FIELD ) FROM ...) AS _last_dttm,
  toDateTime("{{ from_dttm | default('_first_dttm', true) }}") AS from_dttm,
  toDateTime("{{ to_dttm | default('_last_dttm', true) }}") AS to_dttm,
  to_dttm - from_dttm AS range_dttm,
  {{ row_limit | default(1000, true) }} AS row_limit
SELECT *
FROM ...

[rusackas]

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)?

[JZ6]

Still seems to be an Issue on 3.1.1

[rusackas]

Still an issue on 4.x, I presume? It'd be appreciated if anyone can validate this in newer, better supported versions.