SQLAlchemy error on exploring virtual dataset

[manavjaiswal]

How to reproduce the bug

Ran a query (redacted version) in SQL lab:
select xx1, xx2, cast(concat(day, ' ', hour, ':', minute, ':00') as TIMESTAMP) as activity_date from xxxx WHERE xx1 = 'abc' limit 1000;
Clicked on explore and saved it as new dataset. As the the chart creation view opened up with default values filled in for table type chart, I see the following as in the screenshot:

I see the following error in the docker logs:
`Bind parameter '00' without a renderable value not allowed here.
superset_app | Traceback (most recent call last):
superset_app | File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1950, in full_dispatch_request
superset_app | rv = self.dispatch_request()
superset_app | File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1936, in dispatch_request
superset_app | return self.view_functionsrule.endpoint
superset_app | File "/usr/local/lib/python3.8/site-packages/flask_appbuilder/security/decorators.py", line 67, in wraps
superset_app | return f(self, *args, **kwargs)
superset_app | File "/app/superset/views/base_api.py", line 85, in wraps
superset_app | raise ex
superset_app | File "/app/superset/views/base_api.py", line 82, in wraps
superset_app | duration, response = time_function(f, self, *args, **kwargs)
superset_app | File "/app/superset/utils/core.py", line 1461, in time_function
superset_app | response = func(*args, **kwargs)
superset_app | File "/app/superset/utils/log.py", line 242, in wrapper
superset_app | value = f(*args, **kwargs)
superset_app | File "/app/superset/charts/api.py", line 726, in data
superset_app | return self.get_data_response(command)
superset_app | File "/app/superset/charts/api.py", line 537, in get_data_response
superset_app | result = command.run(force_cached=force_cached)
superset_app | File "/app/superset/charts/commands/data.py", line 50, in run
superset_app | payload = self._query_context.get_payload(
superset_app | File "/app/superset/common/query_context.py", line 305, in get_payload
superset_app | query_results = [
superset_app | File "/app/superset/common/query_context.py", line 306, in
superset_app | get_query_results(
superset_app | File "/app/superset/common/query_actions.py", line 183, in get_query_results
superset_app | return result_func(query_context, query_obj, force_cached)
superset_app | File "/app/superset/common/query_actions.py", line 145, in _get_results
superset_app | payload = _get_full(query_context, query_obj, force_cached)
superset_app | File "/app/superset/common/query_actions.py", line 98, in _get_full
superset_app | payload = query_context.get_df_payload(query_obj, force_cached=force_cached)
superset_app | File "/app/superset/common/query_context.py", line 468, in get_df_payload
superset_app | query_result = self.get_query_result(query_obj)
superset_app | File "/app/superset/common/query_context.py", line 254, in get_query_result
superset_app | result = self.datasource.query(query_object.to_dict())
superset_app | File "/app/superset/connectors/sqla/models.py", line 1446, in query
superset_app | query_str_ext = self.get_query_str_extended(query_obj)
superset_app | File "/app/superset/connectors/sqla/models.py", line 754, in get_query_str_extended
superset_app | sql = self.database.compile_sqla_query(sqlaq.sqla_query)
superset_app | File "/app/superset/models/core.py", line 444, in compile_sqla_query
superset_app | sql = str(qry.compile(engine, compile_kwargs={"literal_binds": True}))
superset_app | File "", line 1, in
superset_app | File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 481, in compile
superset_app | return self._compiler(dialect, bind=bind, **kw)
superset_app | File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 487, in _compiler
superset_app | return dialect.statement_compiler(dialect, self, **kw)
superset_app | File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", line 592, in init
superset_app | Compiled.init(self, dialect, statement, **kwargs)
superset_app | File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", line 322, in init
superset_app | self.string = self.process(self.statement, **compile_kwargs)
superset_app | File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", line 352, in process
superset_app | return obj._compiler_dispatch(self, **kwargs)
superset_app | File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/visitors.py", line 96, in _compiler_dispatch
superset_app | return meth(self, **kw)
superset_app | File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", line 2201, in visit_select
superset_app | text = self._compose_select_body(
superset_app | File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", line 2292, in _compose_select_body
superset_app | [
superset_app | File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", line 2293, in
superset_app | f._compiler_dispatch(self, asfrom=True, **kwargs)
superset_app | File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/visitors.py", line 96, in _compiler_dispatch
superset_app | return meth(self, **kw)
superset_app | File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", line 1801, in visit_alias
superset_app | ret = alias.original._compiler_dispatch(
superset_app | File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/visitors.py", line 96, in _compiler_dispatch
superset_app | return meth(self, **kw)
superset_app | File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", line 1013, in visit_text_as_from
superset_app | text = self.process(taf.element, **kw)
superset_app | File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", line 352, in process
superset_app | return obj._compiler_dispatch(self, **kwargs)
superset_app | File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/visitors.py", line 96, in _compiler_dispatch
superset_app | return meth(self, **kw)
superset_app | File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", line 981, in visit_textclause
superset_app | BIND_PARAMS.sub(
superset_app | File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", line 971, in do_bindparam
superset_app | return self.process(textclause._bindparams[name], **kw)
superset_app | File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", line 352, in process
superset_app | return obj._compiler_dispatch(self, **kwargs)
superset_app | File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/visitors.py", line 96, in _compiler_dispatch
superset_app | return meth(self, **kw)
superset_app | File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", line 1549, in visit_bindparam
superset_app | raise exc.CompileError(
superset_app | sqlalchemy.exc.CompileError: Bind parameter '00' without a renderable value not allowed here.
superset_app | 2021-10-13 19:14:20,107:ERROR:superset.views.base:Bind parameter '00' without a renderable value not allowed

Environment

browser type and version: Chrome Version 94.0.4606.61 (Official Build) (x86_64)
superset version: GIT sha 27a40d2 Superset 0.0.0dev
python version: 3.7.5
node.js version: 14.18.0

[villebro]

@manavjaiswal thanks for opening this issue. The culprit here appears to be the way SQLAlchemy prefixes binding parameters with a colon character, and it ends up thinking there is a bind parameter called 00 due to the ':00' concatenation value. I was able to reproduce on Sqlite, I'll see if there's a simple way of escaping these reserved characters.

[manavjaiswal]

@villebro Thanks for looking into it. I tried the same on your merge commit. It still throws the same error.

[manavjaiswal]

My bad. I pruned the old images and built again. It works. Thanks!