Pull Request for Issue # .

Summary of Changes

This pull request fixes the SQL error 1093 which happens on MySQL or MariaDB databases, i.e. not on PostgreSQL, when updating a 4.0 Beta 4 (or earlier) to current 4.0 nightly.

The error was introduced by me with my PR #30945 and has following reason:

In opposite to other database types, e.g. PostgreSQL, Oracle or PostgreSQL, MySQL or MariaDB don't allow to have a subquery on the same table in an UPDATE or a DELETE statement.

When testing my PR the SQL error hasn't been observed for some reason.

This PR here fixes it by using a join instead of a subquery.

Testing Instructions

Testers please report back which kind of database (MySQL, MariaDB or PostgreSQL) you have used for the tests.

With PostgreSQL only "Test 1" can be executed.

Test 1: Reproduce the issue

Test that there is an SQL error with MySQL databases (and very likely MariaDB, too) when updating from a previous 4.0 Beta version to the latest 4.0 nightly build.

If you have a PostgreSQL database instead or in addition, test that this SQL error doesn't happen so it's clear this PR here doesn't need to do anything for PostgreSQL.

1. On a Joomla 4 Beta 4 or an earlier Beta version, got to Global Configuration and switch on "Debug System" in the "System" tab and set "Error Reporting" to "Maximum" or "Development" in the "Server" tab and save the changes.

2. Update to the latest 4.0 nightly build. When starting with an earlier version than Beta 4, mind the extra SQL step described here, which also applies to later versions than Beta 4 to be updated to: https://docs.joomla.org/J4.x:Upgrade_to_4.0_Beta_4.

Result: SQL error with MySQL database (and very likely MariaDB, too), success on PostgreSQL database.

Test 2: Test the fix of this pull request (PR)

This test makes only sense to be executed with a MySQL database (or possibly MariaDB).

1. Start again at the same starting point as in step 1 of the previous test 1, i.e. same 4 Beta 4 or earlier, with "Debug System" = "Yes" and "Error Reporting" = "Maximum" or "Development" in Global Configuration.

2. Same as step 2 of the previous test 1, but this time update to a 4.0-dev which includes the fix from this PR.

• Custom update URL: https://ci.joomla.org/artifacts/joomla/joomla-cms/4.0-dev/31171/downloads/36747/pr_list.xml
• Update package for download if you can't use the live update for some reason: https://ci.joomla.org/artifacts/joomla/joomla-cms/4.0-dev/31171/downloads/36747/Joomla_4.0.0-beta5-dev+pr.31171-Development-Update_Package.zip

Result: No SQL error.

Test 3: Test that the fix from PR #30945 still works with this PR here

This test makes only sense to be executed with a MySQL database (or possibly MariaDB).

1. Update a clean installation (i.e. no 3rd party extensions) of current 3.10-dev or latest 3.10 nightly or 3.10-alpha2 to 4.0 Beta 4, using the following custom URL for Live Update or the following update package for Upload & Update:

• Custom update URL: https://update.joomla.org/core/test/310to4_list.xml
• Update package: https://github.com/joomla/joomla-cms/releases/download/4.0.0-beta4/Joomla_4.0.0-beta4-Beta-Update_Package.zip

2. After successful update, check in "System - Manage - Plugins" if there are plugins with name "Sample Data - Multilingual".

Result: There are two plugins with that name, one of them is disabled.

3. Repeat step 2 of the previous test 2, i.e. update to a 4.0-dev which includes the fix from this PR.

• Custom update URL: https://ci.joomla.org/artifacts/joomla/joomla-cms/4.0-dev/31171/downloads/36747/pr_list.xml
• Update package: https://ci.joomla.org/artifacts/joomla/joomla-cms/4.0-dev/31171/downloads/36747/Joomla_4.0.0-beta5-dev+pr.31171-Development-Update_Package.zip

Result: No SQL error.

4. Check again if there are plugins with name "Sample Data - Multilingual".

Result: Only one plugin with that name, which is enabled.

Actual result BEFORE applying this Pull Request

When using a MySQL (or MariaDB) database:

But when navigating in backend, all is ok because the SQL error happens at the very end of the update.

When using PostgreSQL, the update succeeds without an SQL error.

Expected result AFTER applying this Pull Request

The update update succeeds without an SQL error.

Deleting a duplicate plugin "Sample Data - Multilingual" due to previous update from 3.10 still works.

Documentation Changes Required

None.