query with json_value using returing clause no longer works #36954
Description
Bug description
I have a dataset defined that extracts the data from a json structure in druid with an unnest operation. This statement used to work in superset version 4, but now it fails with the error message:
Unable to parse SQL: Error parsing near 'returning' at line 2:62
json_value(Histogram, concat('$[''',duration,''']') returning BIGINT) as aantal,
However, if I submit my query directly to druid, it delivers the data just fine.
If I leave the "returning BIGINT" clause out, the query also works in superset, but now the data type is incorrectly set to STRING.
The only way to get now the correct data type is by surrounding the json_value function with an explicit cast.
Why is the "returning BIGINT" clause in json_value not working any more?
Screenshots/recordings
No response
Superset version
5.0.0
Python version
3.10
Node version
18 or greater
Browser
Chrome
Additional context
2026-01-07 13:22:31,714:WARNING:superset.views.error_handling:SupersetErrorException
Traceback (most recent call last):
File "/app/superset/sql/parse.py", line 267, in _parse
return sqlglot.parse(script, dialect=dialect)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/app/.venv/lib/python3.11/site-packages/sqlglot/init.py", line 102, in parse
return Dialect.get_or_raise(read or dialect).parse(sql, **opts)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/app/.venv/lib/python3.11/site-packages/sqlglot/dialects/dialect.py", line 991, in parse
return self.parser(**opts).parse(self.tokenize(sql), sql)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/app/.venv/lib/python3.11/site-packages/sqlglot/parser.py", line 1507, in parse
return self._parse(
^^^^^^^^^^^^
File "/app/.venv/lib/python3.11/site-packages/sqlglot/parser.py", line 1576, in _parse
expressions.append(parse_method(self))
^^^^^^^^^^^^^^^^^^
File "/app/.venv/lib/python3.11/site-packages/sqlglot/parser.py", line 1817, in _parse_statement
expression = self._parse_set_operations(expression) if expression else self._parse_select()
^^^^^^^^^^^^^^^^^^^^
File "/app/.venv/lib/python3.11/site-packages/sqlglot/parser.py", line 3118, in _parse_select
projections = self._parse_projections()
^^^^^^^^^^^^^^^^^^^^^^^^^
File "/app/.venv/lib/python3.11/site-packages/sqlglot/parser.py", line 3058, in _parse_projections
return self._parse_expressions()
^^^^^^^^^^^^^^^^^^^^^^^^^
File "/app/.venv/lib/python3.11/site-packages/sqlglot/parser.py", line 6997, in _parse_expressions
return self._parse_csv(self._parse_expression)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/app/.venv/lib/python3.11/site-packages/sqlglot/parser.py", line 6956, in _parse_csv
parse_result = parse_method()
^^^^^^^^^^^^^^
File "/app/.venv/lib/python3.11/site-packages/sqlglot/parser.py", line 4636, in _parse_expression
return self._parse_alias(self._parse_assignment())
^^^^^^^^^^^^^^^^^^^^^^^^
File "/app/.venv/lib/python3.11/site-packages/sqlglot/parser.py", line 4639, in _parse_assignment
this = self._parse_disjunction()
^^^^^^^^^^^^^^^^^^^^^^^^^
File "/app/.venv/lib/python3.11/site-packages/sqlglot/parser.py", line 4660, in _parse_disjunction
return self._parse_tokens(self._parse_conjunction, self.DISJUNCTION)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/app/.venv/lib/python3.11/site-packages/sqlglot/parser.py", line 6965, in _parse_tokens
this = parse_method()
^^^^^^^^^^^^^^
File "/app/.venv/lib/python3.11/site-packages/sqlglot/parser.py", line 4663, in _parse_conjunction
return self._parse_tokens(self._parse_equality, self.CONJUNCTION)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/app/.venv/lib/python3.11/site-packages/sqlglot/parser.py", line 6965, in _parse_tokens
this = parse_method()
^^^^^^^^^^^^^^
File "/app/.venv/lib/python3.11/site-packages/sqlglot/parser.py", line 4666, in _parse_equality
return self._parse_tokens(self._parse_comparison, self.EQUALITY)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/app/.venv/lib/python3.11/site-packages/sqlglot/parser.py", line 6965, in _parse_tokens
this = parse_method()
^^^^^^^^^^^^^^
File "/app/.venv/lib/python3.11/site-packages/sqlglot/parser.py", line 4669, in _parse_comparison
return self._parse_tokens(self._parse_range, self.COMPARISON)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/app/.venv/lib/python3.11/site-packages/sqlglot/parser.py", line 6965, in _parse_tokens
this = parse_method()
^^^^^^^^^^^^^^
File "/app/.venv/lib/python3.11/site-packages/sqlglot/parser.py", line 4672, in _parse_range
this = this or self._parse_bitwise()
^^^^^^^^^^^^^^^^^^^^^
File "/app/.venv/lib/python3.11/site-packages/sqlglot/parser.py", line 4828, in _parse_bitwise
this = self._parse_term()
^^^^^^^^^^^^^^^^^^
File "/app/.venv/lib/python3.11/site-packages/sqlglot/parser.py", line 4862, in _parse_term
this = self._parse_factor()
^^^^^^^^^^^^^^^^^^^^
File "/app/.venv/lib/python3.11/site-packages/sqlglot/parser.py", line 4885, in _parse_factor
this = parse_method()
^^^^^^^^^^^^^^
File "/app/.venv/lib/python3.11/site-packages/sqlglot/parser.py", line 4910, in _parse_unary
return self._parse_at_time_zone(self._parse_type())
^^^^^^^^^^^^^^^^^^
File "/app/.venv/lib/python3.11/site-packages/sqlglot/parser.py", line 4962, in _parse_type
this = self._parse_column()
^^^^^^^^^^^^^^^^^^^^
File "/app/.venv/lib/python3.11/site-packages/sqlglot/parser.py", line 5255, in _parse_column
this = self._parse_column_reference()
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/app/.venv/lib/python3.11/site-packages/sqlglot/parser.py", line 5264, in _parse_column_reference
this = self._parse_field()
^^^^^^^^^^^^^^^^^^^
File "/app/.venv/lib/python3.11/site-packages/sqlglot/parser.py", line 5461, in _parse_field
field = self._parse_primary() or self._parse_function(
^^^^^^^^^^^^^^^^^^^^^
File "/app/.venv/lib/python3.11/site-packages/sqlglot/parser.py", line 5484, in _parse_function
func = self._parse_function_call(
^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/app/.venv/lib/python3.11/site-packages/sqlglot/parser.py", line 5587, in _parse_function_call
self._match_r_paren(this)
File "/app/.venv/lib/python3.11/site-packages/sqlglot/parser.py", line 7768, in _match_r_paren
self.raise_error("Expecting )")
File "/app/.venv/lib/python3.11/site-packages/sqlglot/parser.py", line 1620, in raise_error
raise error
sqlglot.errors.ParseError: Expecting ). Line 2, Col: 62.
llocationApproachId, SignaalGroep, duration,
json_value(Histogram, concat('$[''',duration,''']') �[4mreturning�[0m BIGINT) as aantal, AantalVertrek,
safe_divide(json_value(Histogram, concat('$[''',duration,''']')
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/app/.venv/lib/python3.11/site-packages/flask/app.py", line 1484, in full_dispatch_request
rv = self.dispatch_request()
^^^^^^^^^^^^^^^^^^^^^^^
File "/app/.venv/lib/python3.11/site-packages/flask/app.py", line 1469, in dispatch_request
return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/app/.venv/lib/python3.11/site-packages/flask_appbuilder/security/decorators.py", line 109, in wraps
return f(self, *args, **kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^
File "/app/superset/views/base_api.py", line 120, in wraps
duration, response = time_function(f, self, *args, **kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/app/superset/utils/core.py", line 1369, in time_function
response = func(*args, **kwargs)
^^^^^^^^^^^^^^^^^^^^^
File "/app/superset/utils/log.py", line 304, in wrapper
value = f(*args, **kwargs)
^^^^^^^^^^^^^^^^^^
File "/app/superset/charts/data/api.py", line 260, in data
return self._get_data_response(
^^^^^^^^^^^^^^^^^^^^^^^^
File "/app/superset/utils/log.py", line 304, in wrapper
value = f(*args, **kwargs)
^^^^^^^^^^^^^^^^^^
File "/app/superset/charts/data/api.py", line 423, in _get_data_response
result = command.run(force_cached=force_cached)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/app/superset/commands/chart/data/get_data_command.py", line 45, in run
payload = self._query_context.get_payload(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/app/superset/common/query_context.py", line 102, in get_payload
return self._processor.get_payload(cache_query_context, force_cached)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/app/superset/common/query_context_processor.py", line 752, in get_payload
query_results = [
^
File "/app/superset/common/query_context_processor.py", line 753, in
get_query_results(
File "/app/superset/common/query_actions.py", line 227, in get_query_results
return result_func(query_context, query_obj, force_cached)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/app/superset/common/query_actions.py", line 103, in _get_full
payload = query_context.get_df_payload(query_obj, force_cached=force_cached)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/app/superset/common/query_context.py", line 123, in get_df_payload
return self._processor.get_df_payload(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/app/superset/common/query_context_processor.py", line 162, in get_df_payload
query_result = self.get_query_result(query_obj)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/app/superset/common/query_context_processor.py", line 265, in get_query_result
result = query_context.datasource.query(query_object.to_dict())
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/app/superset/connectors/sqla/models.py", line 1716, in query
query_str_ext = self.get_query_str_extended(query_obj)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/app/superset/models/helpers.py", line 887, in get_query_str_extended
sqlaq = self.get_sqla_query(**query_obj)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/app/superset/models/helpers.py", line 1742, in get_sqla_query
tbl, cte = self.get_from_clause(template_processor)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/app/superset/connectors/sqla/models.py", line 1482, in get_from_clause
return super().get_from_clause(template_processor)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/app/superset/models/helpers.py", line 1090, in get_from_clause
from_sql = self.get_rendered_sql(template_processor) + "\n"
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/app/superset/models/helpers.py", line 1069, in get_rendered_sql
script = SQLScript(sql, engine=self.db_engine_spec.engine)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/app/superset/sql/parse.py", line 670, in init
self.statements = statement_class.split_script(script, engine)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/app/superset/sql/parse.py", line 327, in split_script
ast = cls._parse(remainder, engine)[0]
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/app/superset/sql/parse.py", line 270, in _parse
raise SupersetParseError(
superset.exceptions.SupersetParseError: Error parsing near 'returning' at line 2:62
Checklist
- I have searched Superset docs and Slack and didn't find a solution to my problem.
- I have searched the GitHub issue tracker and didn't find a similar bug report.
- 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.