Reverse Entity Webform Submission Relationship is slow on MariaDB <= 10.5

Problem/Motivation

Using a reverse webform submission relationship results in extremely slow query execution on MariaDB < 10.5. I don't know if this specifically can be fixed, but I figured it was worth reporting, as it may help others running into the issue.

While using the relationship isn't noticeably slow with small amounts of submissions (like 5 for each webform), when there are hundreds or thousands of submissions, the join is very slow, even when using a paged query returning only a few results per page.

Not only that, but when running the resulting SQL directly on the DB from a SQL shell, the query generates lots of warnings like:

mariadb truncated incorrect DOUBLE value ''
mariadb truncated incorrect INTEGER value ''

Steps to reproduce

1. Set up a site with MariaDB 10.3 (this can be done easily using DDEV)
2. Install/Enable Views, Views UI, Webform modules
3. Enable the Views setting to show the executed SQL query during preview in the Views UI
4. Create two webforms, the second one having a field to select a submission from the first webform. Make the "submission" element not required, with multiple selections allowed.
5. Also add a textfield on both webforms, maybe even auto-populate it with any string value, such as "TEST STRING"
6. Generate a lot of submissions for each webform, with lots of Webform 2 submissions that reference Webform 1 submissions, many reference a few, some reference none.
7. Create a view on Webform 1 submissions
8. Add a reverse relationship on the Webform 2 "submission" element referencing Webform 1.
9. Add a sort by Webform 2 submission date
10. Preview the results
11. Copy the executed SQL query
12. Modify the query to be pure SQL (remove occurrences of "{})
13. Using Drush drush sql:cli or some other method, run the SQL on the DB.
14. In SQL, run SHOW WARNINGS;

The warnings occur due to the ordering of JOIN conditions. The generated SQL for the reverse relationship is like this:

LEFT JOIN webform_submission_data webform_submission_data
  ON webform_submission.sid = webform_submission_data.value
  AND webform_submission_data.webform_id = 'webform_2'
  AND webform_submission_data.name = 'related_webform_1'
LEFT JOIN webform_submission webform_views_entity_reverse_webform_submission
  ON webform_submission_data.sid = webform_views_entity_reverse_webform_submission.sid

This is a problem because of ON webform_submission.sid = webform_submission_data.value. The sid is an integer. However, webform_submission_data.value is not always, at least not until you limit the submissions by webform_id and name. JOIN conditions are applied in order, so your string submission values (such as "TEST STRING" listed in the steps above) are joined to sid, resulting in lots and lots of warnings.

If you manually modify the SQL to be like this:

LEFT JOIN webform_submission_data webform_submission_data
  ON webform_submission_data.webform_id = 'webform_2'
  AND webform_submission_data.name = 'related_webform_1'
  AND webform_submission.sid = webform_submission_data.value
LEFT JOIN webform_submission webform_views_entity_reverse_webform_submission
  ON webform_submission_data.sid = webform_views_entity_reverse_webform_submission.sid

There will be no warnings, because by the time that webform_submission.sid = webform_submission_data.value is checked, the values are only the correct ones.

However, it should be noted that even after "correcting" the SQL to remove all warnings, the performance will not be any different.

Proposed resolution

Regarding the warnings, I'm not sure if it's possible to create this SQL with code in WebformViewsEntityReverse::query(), or if you'd need to alter the query in a hook to get this.

Workaround

As a solution to the performance issue, the user can upgrade the database to MariaDB >= 10.5, as the performance was improved. When running a query on 10.3 with a few thousand submissions of each webform, it ran in ~45 seconds, while running on 10.5 it ran in ~4.8 seconds. So it runs about 9 or 10 times faster.

Another small performance gain can be obtained by using a Mini Pager instead of a Full Pager.

Remaining tasks

User interface changes

API changes

Data model changes