Jinja template is not working when async query is enabled

[anthony6401]

Following up from #16412

I tried using jinja template with global async queries(GAQ) enabled in version 1.3.0. I got the same error as stated in here (#14786).

In recent release (v1.3.1rc1), the issue was addressed and it should be fixed. However, I tried to install the new release and enable jinja template as well as GAQ to simulate the previous error. It doesn't throw any error, but now it shows wrong values.

Expected results

GAQ should work with jinja templating and shows correct values.

Actual results

As you can see from the screenshot, the chart returns 0. However, if you look at the bottom data, it returns 4625 (which is the correct one) instead of 0.

Screenshots

Looking at the logs it seems the first query (for the chart) pass through the jinja template, but has no context/variable that should be appended, hence it put the value None.

superset_app             | 172.21.0.1 - - [09/Sep/2021 10:47:39] "GET /api/v1/async_event/?last_id=1631183590197-0 HTTP/1.1" 200 -
superset_app             | 2021-09-09 10:47:39,394:INFO:werkzeug:172.21.0.1 - - [09/Sep/2021 10:47:39] "GET /api/v1/async_event/?last_id=1631183590197-0 HTTP/1.1" 200 -
superset_app             | 2021-09-09 10:47:39,443:DEBUG:superset.sql_parse:Parsing with sqlparse statement: SELECT *
superset_app             | FROM "Flights"
superset_app             | WHERE "AIRLINE" = 'None'
superset_app             | 2021-09-09 10:47:39,456:INFO:superset.common.utils:Cache key: 165593a0806194e5c6b40923f7e946b2
superset_app             | 2021-09-09 10:47:39,456:DEBUG:superset.stats_logger:[stats_logger] (incr) loading_from_cache
superset_app             | 2021-09-09 10:47:39,456:DEBUG:superset.stats_logger:[stats_logger] (incr) loaded_from_cache
superset_app             | 2021-09-09 10:47:39,456:INFO:superset.common.utils:Serving from cache
superset_app             | 2021-09-09 10:47:39,461:DEBUG:superset.stats_logger:[stats_logger] (incr) ChartRestApi.data_from_cache.success
superset_app             | 2021-09-09 10:47:39,461:DEBUG:superset.stats_logger:[stats_logger] (timing) ChartRestApi.data_from_cache.time | 45.62420000002021
superset_app             | 172.21.0.1 - - [09/Sep/2021 10:47:39] "GET /api/v1/chart/data/qc-3420381c3553925b0c069dc140aaca1a HTTP/1.1" 200 -
superset_app             | 2021-09-09 10:47:39,467:INFO:werkzeug:172.21.0.1 - - [09/Sep/2021 10:47:39] "GET /api/v1/chart/data/qc-3420381c3553925b0c069dc140aaca1a HTTP/1.1" 200 -
superset_app             | 2021-09-09 10:47:39,592:DEBUG:superset.sql_parse:Parsing with sqlparse statement: SELECT *
superset_app             | FROM "Flights"
superset_app             | WHERE "AIRLINE" = 'UA'

Just for reference, I used this query for this test.

SELECT *
FROM "Flights"
WHERE "AIRLINE" = '{{ url_param('test') }}'

How to reproduce the bug

1. Enable GAQ.
2. Use url_param function in jinja template.
3. Run any dataset with jinja templating.

Environment

(please complete the following information):

• superset version: 1.3.1rc1
• python version: 3.7.9
• node.js version: 14

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.

[kekwan]

+1. Noticing same behaviour.

The second time the chart is reloaded, the url_param gets passed in properly.

[zhaoyongjie]

Validated on latest master.

[kekwan]

@zhaoyongjie I am also encountering this 422 Error. Is your response also {"message":"Error loading data from cache"}? It seems to be related to the chart data cache.

[zhaoyongjie]

@zhaoyongjie I am also encountering this 422 Error. Is your response also {"message":"Error loading data from cache"}? It seems to be related to the chart data cache.

I am working on this. this is because the cache-key was incorrect calculation when it applied url_param.

[SanjaySharma]

Hi, I am facing the same kind of issue. I am using the Jinja template variable {{ current_aw_user_id() }} in my queries. my queries is very simple:

SELECT * FROM attendance where id ='{{current_aw_user_id()}}'; SELECT * FROM user where reporting_to_id ='{{current_aw_user_id()}}';

I am facing the same "Unexpected error" issue.

what could be the issue, I went through multiple posts but didn't find any solution.

[a-cid]

I believe I'm having the same issue in my deployment.

The dashboard/chart loads just fine after I refresh the page.

[rusackas]

Hi all... is this still an issue in 3.x that anyone can validate?

[rusackas]

Closing as stale, but happy to revisit/reopen if folks are facing this in 4.x