Drill by pagination does not work with Elasticsearch

[sowo]

A clear and concise description of what the bug is.

1. Drill by any Elasticsearch based chart
2. 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]

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

[sowo]

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]

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]

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]

@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 elastic/elasticsearch#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?