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

Comment From: rusackas

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

Comment From: 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.

Comment From: 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.

Comment From: 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}

Comment From: 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 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?

Comment From: rusackas

Hey, sorry I missed the ping, but this has gone silent for 250+ days now! Eeek! It doesn't seem like there's been any update on the ElasticSearch side, so I think we'd be open to finding some other workaround, i.e.: • Coming up with a more graceful error message • Finding some way (I'm not sure right now) of having certain DB engine specs that don't support Offsets, propagate that as a flag somehow, so pagination is simply not displaced when it's problematic.

Comment From: rusackas

Curious if anyone is interested in looking into this, or at least validating that it's still happening in 5.0. If not, we may close the issue as inactive in time.