Drill by pagination does not work with Elasticsearch

sowo commented 1 year ago

A clear and concise description of what the bug is.

Drill by any Elasticsearch based chart
Click on "2" or Next page

Expected results

Next set of details as per drill-by dimension

Actual results

Error: Error (parsing_exception): {'error': {'root_cause': [{'type': 'parsing_exception', 'reason': "line 64:1: mismatched input 'OFFSET' expecting "}], 'type': 'parsing_exception', 'reason': "line 64:1: mismatched input 'OFFSET' expecting ", 'caused_by': {'type': 'input_mismatch_exception', 'reason': None}}, 'status': 400}

Screenshots

N/A

Environment

browser type and version: Chrome, Version 114.0.5735.199 (Official Build) (64-bit)
superset version: 2.1.0
python version: Python 3.8.17
node.js version: v12.22.12
any feature flags active:

DASHBOARD_NATIVE_FILTERS=True DASHBOARD_CROSS_FILTERS=True DASHBOARD_NATIVE_FILTERS_SET=True DASHBOARD_FILTERS_EXPERIMENTAL=True ALERT_REPORTS=True OMNIBAR=True DASHBOARD_RBAC=True ENABLE_EXPLORE_DRAG_AND_DROP=True ENABLE_TEMPLATE_PROCESSING=True VERSIONED_EXPORT=True THUMBNAILS=True ENABLE_JAVASCRIPT_CONTROLS=True DRILL_TO_DETAIL=True GENERIC_CHART_AXES=True USE_ANALAGOUS_COLORS=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

N/A

rusackas commented 8 months ago

Is this still an issue in Superset 3.x? I don't have Elasticsearch to test :/

sowo commented 8 months ago

This is still an issue in 4.0.0rc1

The error thrown when scrolling forward to the next page is the same as in the opening comment.

rusackas commented 8 months ago

Thanks for that info. Since nobody I know has elasticsearch to test/debug/investigate with, please feel free to do some digging and open a PR if you and/or your team feel capable of doing so.

ambarishp commented 3 months ago

Is there any fix available for this. I am also facing same issue when click on pagination in drill to details page. Currently i am using superset version 4.0.2

Error: Error (parsing_exception): {'error': {'root_cause': [{'type': 'parsing_exception', 'reason': "line 45:1: mismatched input 'OFFSET' expecting "}], 'type': 'parsing_exception', 'reason': "line 45:1: mismatched input 'OFFSET' expecting ", 'caused_by': {'type': 'input_mismatch_exception', 'reason': None}}, 'status': 400}

sowo commented 3 months ago

@ambarishp I've researched this issue and came to the conclusion that pagination of result sets is not supported by Elastic.

Elastic SQL does not support the OFFSET clause and the only option it provides is cursor based pagination which only allows you to scroll forward by one page at a time (see https://discuss.elastic.co/t/how-to-use-pagination-in-elastic-search-sql/191135 and https://github.com/elastic/elasticsearch/issues/31549). This is insufficient in the "Drill to details" scenario as Superset allows to skip pages as well as return to previous pages.

I am not sure on how to proceed with this as throwing above error is not an acceptable solution either. Maybe @rusackas can chime in here?

apache / superset