You are viewing a plain text version of this content. The canonical link for it is here.

Posted to notifications@superset.apache.org by GitBox <gi...@apache.org> on 2021/10/02 05:45:59 UTC

[GitHub] [superset] Gradsta opened a new issue #16942: {{ from_dttm }} resolves to None when used in a virtual dataset that populates a dashboard filter's values

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: notifications-unsubscribe@superset.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org

[GitHub] [superset] Gradsta commented on issue #16942: {{ from_dttm }} resolves to None when used in a virtual dataset that populates a dashboard filter's values

Posted by GitBox <gi...@apache.org>.

Gradsta commented on issue #16942:
URL: https://github.com/apache/superset/issues/16942#issuecomment-941346432

@junlincc I would phrase it this way: I'm trying to use JINJA to dynamically populate values for a native filter based on a dashboard's selected time range.

Expanding on my example above, the time filter on the dashboard is `Last year` which should resolve to `2020 <= order_date < 2021`. For the product line filter, I only want to display values that are applicable to the selected time range. In other words, populate the filter with the distinct set of product lines that correspond to vehicle sales that have an order date between 2020 and 2021.

The query I wrote for the example was intended to more clearly highlight the issue but a more realistic query for the product lines filter might look something like this:

```
SELECT DISTINCT product_lines FROM 'Vehicle Sales'
WHERE order_date >= {{ from_dttm}} AND order_date < {{ to_dttm }}
```

And just to reiterate, the problem is that `from_dttm` is resolving to `None` instead of a timestamp. `to_dttm` resolves to a timestamp as expected.

Hopefully this clarifies things! Let me know if you have any other questions.

--
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: notifications-unsubscribe@superset.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org

[GitHub] [superset] rascasse83 commented on issue #16942: {{ from_dttm }} resolves to None when used in a virtual dataset that populates a dashboard filter's values

Posted by GitBox <gi...@apache.org>.

rascasse83 commented on issue #16942:
URL: https://github.com/apache/superset/issues/16942#issuecomment-1040208261


   I had the same issue as the gentleman with "none" being passed through to the query.
   
   The solution on the Filter panel on the dashboard was to go to "SCOPING" tab and ensure i had ticked the right 'chart'


-- 
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: notifications-unsubscribe@superset.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org

[GitHub] [superset] rascasse83 commented on issue #16942: {{ from_dttm }} resolves to None when used in a virtual dataset that populates a dashboard filter's values

Posted by GitBox <gi...@apache.org>.

rascasse83 commented on issue #16942:
URL: https://github.com/apache/superset/issues/16942#issuecomment-1040208261


   I had the same issue as the gentleman with "none" being passed through to the query.
   
   The solution on the Filter panel on the dashboard was to go to "SCOPING" tab and ensure i had ticked the right 'chart'


-- 
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: notifications-unsubscribe@superset.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org

[GitHub] [superset] cyanoboy commented on issue #16942: {{ from_dttm }} resolves to None when used in a virtual dataset that populates a dashboard filter's values

Posted by GitBox <gi...@apache.org>.

cyanoboy commented on issue #16942:
URL: https://github.com/apache/superset/issues/16942#issuecomment-968059455


   @Gradsta you can solve the issue by using the following check:
   
   ```
   {% if from_dttm is not none %}
      WHERE order_date > '{{ from_dttm }}'
   {% endif %}
   ```


-- 
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: notifications-unsubscribe@superset.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org

[GitHub] [superset] Gradsta edited a comment on issue #16942: {{ from_dttm }} resolves to None when used in a virtual dataset that populates a dashboard filter's values

Posted by GitBox <gi...@apache.org>.

Gradsta edited a comment on issue #16942:
URL: https://github.com/apache/superset/issues/16942#issuecomment-941346432

@junlincc I would phrase it this way: I'm trying to use JINJA to dynamically populate values for a native filter based on a dashboard's selected time range.

The query I wrote for the example was intended to more clearly highlight the issue but a more realistic query for the product lines filter might look something like this:

```
SELECT DISTINCT product_line FROM "Vehicle Sales"
WHERE order_date >= {{ from_dttm}} AND order_date < {{ to_dttm }}
```