Fatal error while saving query as dataset with Doris and {{ from_dttm }} & {{ to_dttm }}

bionexit commented 5 months ago

Bug description

Deploy superset with docker compose, and successfully connected Doris. But When I save the query as dataset. An Fatal error showed as failed.

The following query could be run smoothly.

SELECT asset_id,asset_code,asset_type_name,created_date
FROM doris_asset
WHERE 1=1 
  {% if filter_values('asset_type_name')|length %}
    AND asset_type_name IN {{ filter_values('asset_type_name')|where_in}}
  {% endif %}

  {% if from_dttm is not none %}
     AND created_date > '{{ from_dttm }}' 
  {% endif %}

  {% if to_dttm is not none %}
     AND created_date < '{{ to_dttm }}' 
  {% endif %}

also i fill the Template parameters as following

{
  "from_dttm": "2020-01-01",
  "to_dttm": "2099-01-01"
}

How to reproduce the bug

Run pip install pydoris inside the superset_app
Connect a Doris FE.
Go to Sql lab.
create a query with {{ from_dttm }}or {{ to_dttm }}
click save -> save dataset
error occurred.

Screenshots/recordings

Error log in sudo docker logs -f --tail=300 superset_app

2024-05-20 19:57:03,982:DEBUG:superset.sql_parse:Parsing with sqlparse statement: SELECT asset_id,asset_code,asset_type_name,created_date
FROM doris_asset
WHERE 1=1

     AND created_date > '{{ from_dttm }}'

     AND created_date < '{{ to_dttm }}'
2024-05-20 19:57:03,989:DEBUG:superset.stats_logger:[stats_logger] (incr) DatasetRestApi.post.warning
2024-05-20 19:57:03,989:ERROR:flask_appbuilder.api:(1105, "errCode = 2, detailMessage = Incorrect datetime value: CAST('{{ from_dttm }}' AS DATETIMEV2(0)) in expression: (`created_date` > CAST('{{ from_dttm }}' AS DATETIMEV2(0)))")
Traceback (most recent call last):
  File "/app/superset/connectors/sqla/utils.py", line 147, in get_columns_description
    cursor.execute(query)
  File "/usr/local/lib/python3.10/site-packages/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
  File "/usr/local/lib/python3.10/site-packages/MySQLdb/cursors.py", line 319, in _query
    db.query(q)
  File "/usr/local/lib/python3.10/site-packages/MySQLdb/connections.py", line 254, in query
    _mysql.connection.query(self, query)
MySQLdb._exceptions.OperationalError: (1105, "errCode = 2, detailMessage = Incorrect datetime value: CAST('{{ from_dttm }}' AS DATETIMEV2(0)) in expression: (`created_date` > CAST('{{ from_dttm }}' AS DATETIMEV2(0)))")

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/local/lib/python3.10/site-packages/flask_appbuilder/api/__init__.py", line 110, in wraps
    return f(self, *args, **kwargs)
  File "/app/superset/views/base_api.py", line 127, in wraps
    raise ex
  File "/app/superset/views/base_api.py", line 121, in wraps
    duration, response = time_function(f, self, *args, **kwargs)
  File "/app/superset/utils/core.py", line 1463, in time_function
    response = func(*args, **kwargs)
  File "/app/superset/utils/log.py", line 255, in wrapper
    value = f(*args, **kwargs)
  File "/app/superset/views/base_api.py", line 93, in wraps
    return f(self, *args, **kwargs)
  File "/app/superset/datasets/api.py", line 329, in post
    new_model = CreateDatasetCommand(item).run()
  File "/app/superset/commands/dataset/create.py", line 52, in run
    dataset.fetch_metadata(commit=False)
  File "/app/superset/connectors/sqla/models.py", line 1830, in fetch_metadata
    new_columns = self.external_metadata()
  File "/app/superset/connectors/sqla/models.py", line 1322, in external_metadata
    return get_virtual_table_metadata(dataset=self)
  File "/app/superset/connectors/sqla/utils.py", line 132, in get_virtual_table_metadata
    return get_columns_description(dataset.database, dataset.schema, statements[0])
  File "/app/superset/connectors/sqla/utils.py", line 153, in get_columns_description
    raise SupersetGenericDBErrorException(message=str(ex)) from ex
superset.exceptions.SupersetGenericDBErrorException: (1105, "errCode = 2, detailMessage = Incorrect datetime value: CAST('{{ from_dttm }}' AS DATETIMEV2(0)) in expression: (`created_date` > CAST('{{ from_dttm }}' AS DATETIMEV2(0)))")
2024-05-20 19:57:03,991:INFO:sqlalchemy.engine.Engine:ROLLBACK
2024-05-20 19:57:03,991 INFO sqlalchemy.engine.Engine ROLLBACK

Superset version

3.1.3

Python version

3.9

Node version

I don't know

Browser

Chrome

Additional context

No response

Checklist

[X] I have searched Superset docs and Slack and didn't find a solution to my problem.
[X] I have searched the GitHub issue tracker and didn't find a similar bug report.
[X] I have checked Superset's logs for errors and if I found a relevant Python stacktrace, I included it here as text in the "additional context" section.

dosubot[bot] commented 5 months ago

The error you're encountering suggests that the template variables {{ from_dttm }} and {{ to_dttm }} are not being correctly replaced with their intended datetime values in your SQL query. This issue could stem from a few different areas:

Template Rendering: Ensure that the datetime format for from_dttm and to_dttm is correctly recognized and that these variables are properly defined in the template's context. If the variables are missing or incorrectly formatted, they won't be replaced in the query.
Jinja Syntax: Verify the Jinja syntax in your SQL query. Incorrect syntax could prevent the template from rendering correctly, leaving the variables unreplaced.
Superset Configuration: Check if ENABLE_TEMPLATE_PROCESSING is enabled in your Superset configuration, as disabling this feature could affect template rendering.
Query Logic: To handle cases where from_dttm and to_dttm might not be defined (e.g., if the time range filter is cleared), consider adding conditional logic in your Jinja template to include these conditions only when the variables are not None.

For more detailed troubleshooting, consider enabling more verbose logging in Superset to capture detailed error messages or stack traces. This can provide further insights into the root cause of the issue, whether it's related to the query, template rendering, or database interaction.

If after these checks the issue remains unresolved, it might be beneficial to review the Superset documentation on SQL templating and the configuration of Jinja context variables for additional guidance.

#### Details

**Sources** - [superset/commands/dataset/create.py](https://github.com/apache/superset/blob/master/superset/commands/dataset/create.py) - [superset/jinja_context.py](https://github.com/apache/superset/blob/master/superset/jinja_context.py) - [docs/docs/configuration/sql-templating.mdx](https://github.com/apache/superset/blob/master/docs/docs/configuration/sql-templating.mdx) - [superset/sqllab/query_render.py](https://github.com/apache/superset/blob/master/superset/sqllab/query_render.py) - [superset/sqllab/sqllab_execution_context.py](https://github.com/apache/superset/blob/master/superset/sqllab/sqllab_execution_context.py) - [superset/errors.py](https://github.com/apache/superset/blob/master/superset/errors.py) - [superset/db_engine_specs/doris.py](https://github.com/apache/superset/blob/master/superset/db_engine_specs/doris.py) - [superset/db_engine_specs/databend.py](https://github.com/apache/superset/blob/master/superset/db_engine_specs/databend.py) - [superset/db_engine_specs/mssql.py](https://github.com/apache/superset/blob/master/superset/db_engine_specs/mssql.py) - [superset/commands/query/exceptions.py](https://github.com/apache/superset/blob/master/superset/commands/query/exceptions.py) - [tests/unit_tests/db_engine_specs/test_doris.py](https://github.com/apache/superset/blob/master/tests/unit_tests/db_engine_specs/test_doris.py) - [superset-frontend/packages/superset-ui-core/src/time-format/factories/createMultiFormatter.ts](https://github.com/apache/superset/blob/master/superset-frontend/packages/superset-ui-core/src/time-format/factories/createMultiFormatter.ts) - [superset-frontend/packages/superset-ui-core/test/time-format/factories/createD3TimeFormatter.test.ts](https://github.com/apache/superset/blob/master/superset-frontend/packages/superset-ui-core/test/time-format/factories/createD3TimeFormatter.test.ts) - [superset/db_engine_specs/elasticsearch.py](https://github.com/apache/superset/blob/master/superset/db_engine_specs/elasticsearch.py)
**About Dosu** This response is meant to be useful and save you time. It is **not meant to be a precise solution**, but rather a starting point for your own research. Help me be more useful! Please leave a 👍 if this is helpful and 👎 if it is irrelevant.

To continue the conversation, mention @dosu.

rusackas commented 5 months ago

FYI @liujiwen-up, in case you know anything about this :)

rusackas commented 2 months ago

I don't think this is specifically Doris related, actually. I think this applies to saving queries with Jinja in general.

rusackas commented 2 months ago

Closing as a duplicate of https://github.com/apache/superset/issues/25786... let's take the conversation (and hopefully a solution!) there.

apache / superset