Gradsta opened a new issue #16942:
URL: https://github.com/apache/superset/issues/16942
When selecting a time range on a dashboard, I'd like that time range to be
applied to any virtual datasets that populate a dashboard filter's values. I
was experimenting with the `{{ from_dttm }}` and `{{ to_dttm }}` variables but
the `{{ from_dttm }}` variable is resolving to `None` which causes an error.
The `{{ to_dttm }}` variable appears to resolve just fine.
#### How to reproduce the bug
1. Enable dashboard native filters and template processing
2. Start Superset, login, and navigate to SQL Lab
2. Execute the following query which will be used to generate a "Product
Line" native filter on the "Sales Dashboard"
```
SELECT DISTINCT product_line FROM "Vehicle Sales"
WHERE order_date > now() - INTERVAL '100' YEAR
```
3. Click Explore and save the results as a virtual dataset
4. Navigate to Datasets and edit the virtual dataset you just created to use
the `{{ from_dttm }}` variable
```
SELECT DISTINCT product_line FROM "Vehicle Sales"
WHERE order_date > '{{ from_dttm }}'
```
5. Navigate to "Sales Dashboard"
6. Add a time range filter with a default value (any should suffice) and a
"Product Line" value filter using the virtual dataset you created in a previous
step
6. Refresh the dashboard
### Expected results
I would expect the `{{ from_dttm }}` variable to resolve to a timestamp
which would permit the product line filter query to execute successfully.
### Actual results
The `{{ from_dttm }}` variable resolves to `None` which causes the query to
fail and the dashboard to display an error.
#### Screenshots
"Product Line Filter" Virtual Dataset:
<img width="880" alt="Screen Shot 2021-10-01 at 11 56 06 PM"
src="https://user-images.githubusercontent.com/41174775/135704765-871679d9-cc67-416d-81a2-cbbc4c6660d9.png";>
Error:
<img width="1679" alt="Screen Shot 2021-10-01 at 11 57 15 PM"
src="https://user-images.githubusercontent.com/41174775/135704818-828510ae-d8fe-4537-a915-d8ef59033d60.png";>
### Environment
(please complete the following information):
- browser type and version: Chrome Version 94.0.4606.54 (Official Build)
(x86_64)
- superset version: `1.3`
- python version: Using `docker-compose-non-dev.yml`
- node.js version: Using `docker-compose-non-dev.yml`
- any feature flags active: `DASHBOARD_NATIVE_FILTERS`,
`ENABLE_TEMPLATE_PROCESSING`
### 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.
- [x] I have checked the issue tracker for the same issue and I haven't
found one similar.
### Additional context
Logs:
```
superset_app | 192.168.48.1 - - [02/Oct/2021:05:30:39 +0000]
"POST /api/v1/chart/data?form_data=%7B%22slice_id%22%3A130%7D&dashboard_id=8
HTTP/1.1" 200 440
"http://localhost:8088/superset/dashboard/8/?native_filters=%28NATIVE_FILTER-1xz0aSSuP%3A%28extraFormData%3A%28%29%2CfilterState%3A%28%29%2Cid%3ANATIVE_FILTER-1xz0aSSuP%2CownState%3A%28%29%29%2CNATIVE_FILTER-4eLHOEP1N%3A%28extraFormData%3A%28time_range%3A%27Last+year%27%29%2CfilterState%3A%28value%3A%27Last+year%27%29%2Cid%3ANATIVE_FILTER-4eLHOEP1N%2CownState%3A%28%29%29%29";
"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML,
like Gecko) Chrome/94.0.4606.54 Safari/537.36"
superset_app | 192.168.48.1 - - [02/Oct/2021:05:30:40 +0000] "GET
/api/v1/time_range/?q=%27Last%20year%27 HTTP/1.1" 200 102
"http://localhost:8088/superset/dashboard/8/?native_filters=%28NATIVE_FILTER-1xz0aSSuP%3A%28extraFormData%3A%28%29%2CfilterState%3A%28%29%2Cid%3ANATIVE_FILTER-1xz0aSSuP%2CownState%3A%28%29%29%2CNATIVE_FILTER-4eLHOEP1N%3A%28extraFormData%3A%28time_range%3A%27Last+year%27%29%2CfilterState%3A%28value%3A%27Last+year%27%29%2Cid%3ANATIVE_FILTER-4eLHOEP1N%2CownState%3A%28%29%29%29";
"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML,
like Gecko) Chrome/94.0.4606.54 Safari/537.36"
superset_app | 192.168.48.1 - - [02/Oct/2021:05:30:40 +0000]
"POST /api/v1/chart/data?form_data=%7B%22slice_id%22%3A125%7D&dashboard_id=8
HTTP/1.1" 200 425
"http://localhost:8088/superset/dashboard/8/?native_filters=%28NATIVE_FILTER-1xz0aSSuP%3A%28extraFormData%3A%28%29%2CfilterState%3A%28%29%2Cid%3ANATIVE_FILTER-1xz0aSSuP%2CownState%3A%28%29%29%2CNATIVE_FILTER-4eLHOEP1N%3A%28extraFormData%3A%28time_range%3A%27Last+year%27%29%2CfilterState%3A%28value%3A%27Last+year%27%29%2Cid%3ANATIVE_FILTER-4eLHOEP1N%2CownState%3A%28%29%29%29";
"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML,
like Gecko) Chrome/94.0.4606.54 Safari/537.36"
superset_app | 192.168.48.1 - - [02/Oct/2021:05:30:40 +0000]
"POST
/superset/explore_json/?form_data=%7B%22slice_id%22%3A97%7D&dashboard_id=8
HTTP/1.1" 200 1714
"http://localhost:8088/superset/dashboard/8/?native_filters=%28NATIVE_FILTER-1xz0aSSuP%3A%28extraFormData%3A%28%29%2CfilterState%3A%28%29%2Cid%3ANATIVE_FILTER-1xz0aSSuP%2CownState%3A%28%29%29%2CNATIVE_FILTER-4eLHOEP1N%3A%28extraFormData%3A%28time_range%3A%27Last+year%27%29%2CfilterState%3A%28value%3A%27Last+year%27%29%2Cid%3ANATIVE_FILTER-4eLHOEP1N%2CownState%3A%28%29%29%29";
"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML,
like Gecko) Chrome/94.0.4606.54 Safari/537.36"
db_1 | 2021-10-02 05:30:41.081 UTC [842] ERROR: invalid
input syntax for type timestamp: "None" at character 120
db_1 | 2021-10-02 05:30:41.081 UTC [842] STATEMENT:
SELECT product_line AS product_line
db_1 | FROM
db_1 | (SELECT DISTINCT product_line
db_1 | FROM "Vehicle Sales"
db_1 | WHERE order_date < 'None') AS
virtual_table
db_1 | GROUP BY product_line
db_1 | ORDER BY product_line ASC
db_1 | LIMIT 1000
superset_app | Query SELECT product_line AS product_line
superset_app | FROM
superset_app | (SELECT DISTINCT product_line
superset_app | FROM "Vehicle Sales"
superset_app | WHERE order_date < 'None') AS virtual_table
superset_app | GROUP BY product_line
superset_app | ORDER BY product_line ASC
superset_app | LIMIT 1000 on schema public failed
superset_app | Traceback (most recent call last):
superset_app | File "/app/superset/connectors/sqla/models.py",
line 1472, in query
superset_app | df = self.database.get_df(sql, self.schema,
mutator=assign_column_label)
superset_app | File "/app/superset/models/core.py", line 413,
in get_df
superset_app | self.db_engine_spec.execute(cursor, sqls[-1])
superset_app | File "/app/superset/db_engine_specs/base.py",
line 1090, in execute
superset_app | raise cls.get_dbapi_mapped_exception(ex)
superset_app | File "/app/superset/db_engine_specs/base.py",
line 1088, in execute
superset_app | cursor.execute(query)
superset_app | psycopg2.errors.InvalidDatetimeFormat: invalid
input syntax for type timestamp: "None"
superset_app | LINE 5: WHERE order_date < 'None') AS
virtual_table
superset_app | ^
superset_app |
superset_app | 2021-10-02
05:30:41,082:WARNING:superset.connectors.sqla.models:Query SELECT product_line
AS product_line
superset_app | FROM
superset_app | (SELECT DISTINCT product_line
superset_app | FROM "Vehicle Sales"
superset_app | WHERE order_date < 'None') AS virtual_table
superset_app | GROUP BY product_line
superset_app | ORDER BY product_line ASC
superset_app | LIMIT 1000 on schema public failed
superset_app | Traceback (most recent call last):
superset_app | File "/app/superset/connectors/sqla/models.py",
line 1472, in query
superset_app | df = self.database.get_df(sql, self.schema,
mutator=assign_column_label)
superset_app | File "/app/superset/models/core.py", line 413,
in get_df
superset_app | self.db_engine_spec.execute(cursor, sqls[-1])
superset_app | File "/app/superset/db_engine_specs/base.py",
line 1090, in execute
superset_app | raise cls.get_dbapi_mapped_exception(ex)
superset_app | File "/app/superset/db_engine_specs/base.py",
line 1088, in execute
superset_app | cursor.execute(query)
superset_app | psycopg2.errors.InvalidDatetimeFormat: invalid
input syntax for type timestamp: "None"
superset_app | LINE 5: WHERE order_date < 'None') AS
virtual_table
superset_app | ^
superset_app |
superset_app | 192.168.48.1 - - [02/Oct/2021:05:30:41 +0000]
"POST /api/v1/chart/data HTTP/1.1" 400 162
"http://localhost:8088/superset/dashboard/8/?native_filters=%28NATIVE_FILTER-1xz0aSSuP%3A%28extraFormData%3A%28%29%2CfilterState%3A%28%29%2Cid%3ANATIVE_FILTER-1xz0aSSuP%2CownState%3A%28%29%29%2CNATIVE_FILTER-4eLHOEP1N%3A%28extraFormData%3A%28time_range%3A%27Last+year%27%29%2CfilterState%3A%28value%3A%27Last+year%27%29%2Cid%3ANATIVE_FILTER-4eLHOEP1N%2CownState%3A%28%29%29%29";
"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML,
like Gecko) Chrome/94.0.4606.54 Safari/537.36"
superset_app | 192.168.48.1 - - [02/Oct/2021:05:30:41 +0000]
"POST /superset/log/?explode=events&dashboard_id=8 HTTP/1.1" 200 1
"http://localhost:8088/superset/dashboard/8/?native_filters=%28NATIVE_FILTER-1xz0aSSuP%3A%28extraFormData%3A%28%29%2CfilterState%3A%28%29%2Cid%3ANATIVE_FILTER-1xz0aSSuP%2CownState%3A%28%29%29%2CNATIVE_FILTER-4eLHOEP1N%3A%28extraFormData%3A%28time_range%3A%27Last+year%27%29%2CfilterState%3A%28value%3A%27Last+year%27%29%2Cid%3ANATIVE_FILTER-4eLHOEP1N%2CownState%3A%28%29%29%29";
"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML,
like Gecko) Chrome/94.0.4606.54 Safari/537.36"
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
![https://user-images.githubusercontent.com/41174775/135704765-871679d9-cc67-416d-81a2-cbbc4c6660d9.png"](https://user-images.githubusercontent.com/41174775/135704765-871679d9-cc67-416d-81a2-cbbc4c6660d9.png"){kind=link}
![https://user-images.githubusercontent.com/41174775/135704818-828510ae-d8fe-4537-a915-d8ef59033d60.png"](https://user-images.githubusercontent.com/41174775/135704818-828510ae-d8fe-4537-a915-d8ef59033d60.png"){kind=link}