server-side sorting initiated by a client

[antonio-antuan]

I have a dataset (a virtual table) with thousands of records. The dataset is shown via Table chart. Server-side pagination is enabled there. Chart is added to a dashboard.
When I observe the dashboard I click on some column for sorting and it looks like sorting is performed only on client side. Is there any way to perform user-defined sorting on server-side?

How to reproduce the bug

1. Go to dashboard that have chart table with server-side pagination.
2. Click on any column to make it sort

Expected results

Sorting is performed on server side

Actual results

Sorting is performed on client side

Screenshots

If applicable, add screenshots to help explain your problem.

Environment

(please complete the following information):

• browser type and version: Firefox 118.0.2 (64-bit)
• superset version: docker image apache/superset:3.0.0-py310
• python version: py310 (see superset version)
• node.js version: see superset version
• any feature flags active: ALERT_REPORTS, EMBEDDED_SUPERSET, DASHBOARD_FILTERS_EXPERIMENTAL, DASHBOARD_NATIVE_FILTERS_SET, DASHBOARD_NATIVE_FILTERS, DASHBOARD_CROSS_FILTERS, ENABLE_TEMPLATE_PROCESSING, HORIZONTAL_FILTER_BAR, ALLOW_FULL_CSV_EXPORT, ENABLE_JAVASCRIPT_CONTROLS

Checklist

Make sure to follow these steps before submitting your issue - thank you!

• [*] I have checked the superset logs for python stacktraces and included it here as text if there are any.
• [*] I have reproduced the issue with at least the latest released version of superset.
• [*] 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.

[eram]

ref https://github.com/apache/superset/issues/9527

[rusackas]

Here's a quick way to spin up a test dataset for anyone that wants to validate this:

WITH RECURSIVE NumberSequence AS (
  SELECT 1 AS AscendingNum, 1000 AS DescendingNum
  UNION ALL
  SELECT AscendingNum + 1, DescendingNum - 1
  FROM NumberSequence
  WHERE AscendingNum < 2000 -- Change this limit to generate desired number of rows
)
SELECT *
FROM NumberSequence;

[MclellandG]

This issue also applies to the text filter for the table as well. Entering a value in the search field applies only to the current page. My expectation would be that it would re-query the entire data set, re-execute the query and apply the filter on the server-side, and then re-paginate the results on the client. Unless I'm missing something there doesn't seem to be a way to search/text filter the entire data set. To me this is a huge issue and severely limits the usefulness of the raw table chart for larger data sets.

[MclellandG]

I noticed that the Table component uses react-window, which supports react-window-infinite-loader. Would this not be a alternate approach to server side paging? In this manner you'd be able to ditch page sizes, row limits, and treat the dataset in its entirety but only display the first X number of results visible on the page. The expectation would be that filters would then be applied on the data as a whole, re-query the database with the filter and again only display the top X results, fetching more as the user scrolls down the page. I think the desired behaviour would be similar to how AgGrid does it's server side rendering