sowo opened a new issue, #24003:
URL: https://github.com/apache/superset/issues/24003
When trying to drill by a time dimension in a chart based on an
Elasticsearch data set, the following error is thrown:
`Error: Error (verification_exception): {'error': {'root_cause': [{'type':
'verification_exception', 'reason': 'Found 1 problem\nline 25:7: Cannot filter
on grouping function [HISTOGRAM("DATE", INTERVAL 1 YEAR)], use its argument
instead'}], 'type': 'verification_exception', 'reason': 'Found 1 problem\nline
25:7: Cannot filter on grouping function [HISTOGRAM("DATE", INTERVAL 1 YEAR)],
use its argument instead'}, 'status': 400}`
#### How to reproduce the bug
1. Right-click on an Elasticsearch based chart an choose "Drill to detail
by->Drill to detail by 2010'
### Expected results
A list of records matching the drill by criterion
### Actual results
The error in the screenshot below. And here's the stack trace from the log
```
2023-05-10 11:53:31,047:WARNING:superset.connectors.sqla.models:Query SELECT
"TRAINING_TYPE" AS "TRAINING_TYPE",
"REGION" AS "REGION",
"DATE" AS "DATE",
"FUNDING_STATUS" AS "FUNDING_STATUS",
"TITLE" AS "TITLE",
"FEMALE" AS "FEMALE",
"ID" AS "ID",
"PARTICIPANT" AS "PARTICIPANT",
"STATE" AS "STATE",
"EVENT_COUNTRY" AS "EVENT_COUNTRY",
"STATE_NAME" AS "STATE_NAME"
FROM
(SELECT ID,
TITLE.keyword AS "TITLE",
TRAINING_TYPE.keyword AS "TRAINING_TYPE",
"DATE",
PARTICIPANT,
FEMALE,
"CODE_3" AS "STATE",
"STATE" AS "STATE_NAME",
"EVENT_COUNTRY",
"REGION",
FUNDING_STATUS.keyword AS "FUNDING_STATUS"
FROM "pmt4.cbt_events") AS virtual_table
WHERE HISTOGRAM("DATE", INTERVAL 1 YEAR) = 1262304000000
LIMIT 50 on schema default failed
Traceback (most recent call last):
File "/usr/local/lib/python3.8/site-packages/es/baseapi.py", line 318, in
elastic_query
response = self.es.transport.perform_request("POST", path, body=payload)
File "/usr/local/lib/python3.8/site-packages/elasticsearch/transport.py",
line 415, in perform_request
raise e
File "/usr/local/lib/python3.8/site-packages/elasticsearch/transport.py",
line 381, in perform_request
status, headers_response, data = connection.perform_request(
File
"/usr/local/lib/python3.8/site-packages/elasticsearch/connection/http_urllib3.py",
line 277, in perform_request
self._raise_error(response.status, raw_data)
File
"/usr/local/lib/python3.8/site-packages/elasticsearch/connection/base.py", line
330, in _raise_error
raise HTTP_EXCEPTIONS.get(status_code, TransportError)(
elasticsearch.exceptions.RequestError: RequestError(400,
'verification_exception', 'Found 1 problem\nline 25:7: Cannot filter on
grouping function [HISTOGRAM("DATE", INTERVAL 1 YEAR)], use its argument
instead')
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File
"/usr/local/lib/python3.8/site-packages/superset/db_engine_specs/base.py", line
1366, in execute
cursor.execute(query)
File "/usr/local/lib/python3.8/site-packages/es/baseapi.py", line 36, in
wrap
return f(self, *args, **kwargs)
File "/usr/local/lib/python3.8/site-packages/es/elastic/api.py", line 158,
in execute
results = self.elastic_query(query)
File "/usr/local/lib/python3.8/site-packages/es/baseapi.py", line 322, in
elastic_query
raise exceptions.ProgrammingError(f"Error ({ex.error}): {ex.info}")
es.exceptions.ProgrammingError: Error (verification_exception): {'error':
{'root_cause': [{'type': 'verification_exception', 'reason': 'Found 1
problem\nline 25:7: Cannot filter on grouping function [HIS
TOGRAM("DATE", INTERVAL 1 YEAR)], use its argument instead'}], 'type':
'verification_exception', 'reason': 'Found 1 problem\nline 25:7: Cannot filter
on grouping function [HISTOGRAM("DATE", INTERVAL 1 YE
AR)], use its argument instead'}, 'status': 400}
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File
"/usr/local/lib/python3.8/site-packages/superset/connectors/sqla/models.py",
line 1944, in query
df = self.database.get_df(sql, self.schema, mutator=assign_column_label)
File "/usr/local/lib/python3.8/site-packages/superset/models/core.py",
line 549, in get_df
self.db_engine_spec.execute(cursor, sqls[-1])
File
"/usr/local/lib/python3.8/site-packages/superset/db_engine_specs/elasticsearch.py",
line 106, in execute
return super().execute(cursor, query, **kwargs)
File
"/usr/local/lib/python3.8/site-packages/superset/db_engine_specs/base.py", line
1368, in execute
raise cls.get_dbapi_mapped_exception(ex) from ex
superset.db_engine_specs.exceptions.SupersetDBAPIProgrammingError: Error
(verification_exception): {'error': {'root_cause': [{'type':
'verification_exception', 'reason': 'Found 1 problem\nline 25:7: Cann
ot filter on grouping function [HISTOGRAM("DATE", INTERVAL 1 YEAR)], use its
argument instead'}], 'type': 'verification_exception', 'reason': 'Found 1
problem\nline 25:7: Cannot filter on grouping function [HISTOGRAM("DATE",
INTERVAL 1 YEAR)], use its argument instead'}, 'status': 400}
```
#### Screenshots
### Environment
(please complete the following information):
- browser type and version: Chrome
- superset version: `2.1`
- python version: `Python 3.8.12`
- node.js version: `v12.22.12`
- any feature flags active:
DRILL_BY=True
DRILL_TO_DETAIL=True
### 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
Add any other context about the problem here.
--
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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
