Pull Request for Issue #41156 .

See also issues #17580 and #39479 , discussion #42198 and support forum thread https://forum.joomla.org/viewtopic.php?f=811&t=995872 .

Summary of Changes

This pull request (PR) changes the SQL query in the extension helper to get the extension_ids of all core extensions from using a giant "WHERE" clause with a lot of "( ... AND ... AND ...) OR ( ... AND ... AND ...) OR ..." conditions to a query which uses the concatenation of the 4 relevant columns and one single "WHERE ... IN (...)" condition, which is much shorter.

Currently the SQL query looks like this on MySQL/MariaDB (with ... for "and so on"):

SELECT `extension_id`
  FROM `#__extensions`
 WHERE (`type` = :preparedArray1 AND `element` = :preparedArray2 AND `folder` = :preparedArray3 AND `client_id` = :preparedArray4)
    OR (`type` = :preparedArray5 AND `element` = :preparedArray6 AND `folder` = :preparedArray7 AND `client_id` = :preparedArray8)
...
    OR (`type` = :preparedArray929 AND `element` = :preparedArray930 AND `folder` = :preparedArray931 AND `client_id` = :preparedArray932);

with :preparedArray1='component', :preparedArray2='com_actionlogs', :preparedArray3='', :preparedArray4=1, :preparedArray5='component', :preparedArray6='com_admin', :preparedArray7='', :preparedArray8=1 and so on.

With this PR applied the query looks like this on MySQL/MariaDB (with ... for "and so on"):

SELECT `extension_id`
  FROM `#__extensions`
 WHERE CONCAT_WS('|', `type`, `element`, `folder`, `client_id`)
    IN (:preparedArray1,
        :preparedArray2,
...
        :preparedArray233);

with :preparedArray1='component|com_actionlogs||1' and :preparedArray2='component|com_admin||1' and so on.

On PostgreSQL it looks like this;

SELECT "extension_id"
  FROM "j3ux0_extensions"
 WHERE "type" || '|' || "element" || '|' || "folder" || '|' || "client_id"
    IN (:preparedArray1,
        :preparedArray2,
...
        :preparedArray233);

This solves the SQL error "1104 The SELECT would examine more than MAX_JOIN_SIZE rows" happening on MariaDB databases where the sql_big_select server variable is set to "OFF" and the max_join_size has a value like e.g. 67108864, which seems to be the default on some hosting environments, when a core update has been found so the pre-update check is shown or when you are using the filter for core or non-core extensions in the Extensions Manager.

And I have the feeling that the new query is also faster.

Testing Instructions

Requirements: It needs a MariaDB database to reproduce the issue. I could reproduce it on a MariaDB 10.4.32, and the issue was also reported for MariaDB 10.6.12. With MySQL 8 I was not able to reproduce it.

In addition to testing with MariaDB, it needs to test with MySQL and PostgreSQL that nothing is broken and everything works as before.

Packages for new installation and update with this PR applied and a custom update URL can be found here: https://artifacts.joomla.org/drone/joomla/joomla-cms/4.4-dev/42576/downloads/72566 .

Test 1: Reproduce the issue and test the fix

This test needs a MariaDB database to reproduce the issue and an installation with a clean, current 4.4-dev branch or recent 4.4 nightly build or a 4.4.0 or 4.4.1 stable, preferably without any 3rd party extensions to rule out any side effects coming from those.

In addition it needs the "sql_big_selects" variable to be set to "OFF" (0) and the "max_join_size" to "67108864" in the session. If you don't have that you can set these variables when using a database user with administrator privileges, e.g. user "root", with the following 2 SQL statements in a client like e.g. phpMyAdmin:

SET GLOBAL sql_big_selects=0;
SET GLOBAL max_join_size=67108864;

This will persist until the next restart of the database server and result in these values be set for every session.

1. Without the changes from this PR applied, go to the options of the Joomla Update component in the administrator and change the update source to "Custom URL" and use the URL https://update.joomla.org/core/nightlies/next_major_list.xml of the 5.0 nightly builds so that for sure an update will be found and the pre-update check will be shown.
2. Now go to the Joomla Update component.
   Result: SQL error "1104 The SELECT would examine more than MAX_JOIN_SIZE rows".
3. Now go to "System -> Manage -> Extensions" and use the last filter option "-- Select Extensions" to filter either by core or by non-core extensions.
   Result: SQL error "1104 The SELECT would examine more than MAX_JOIN_SIZE rows".
4. Apply the changes from this PR.
5. Go again to "System -> Manage -> Extensions" or if still there reload the page.
   Result: No SQL error. The extensions list is shown with the filter selected in step 3.
6. Go to the Joomla Update component.
   Result: An update to 5.0.2-dev is found, and the pre-update checks are shown. All works as expected.

Test 2: Check that nothing is broken

This test needs to be done on all possible kinds of databases (MySQL, MariaDB and PostgreSQL).

If using MariaDB, make sure to have the sql_big_selects server variable switched "ON" (1) so you don't get the SQL error tested with the previous test 1.
This can be done as a database user with administrator privileges (e.g. user "root") with the following SQL statement in a client like e.g. phpMyAdmin: SET GLOBAL sql_big_selects=1;.

1. Without the changes from this PR applied, add the following code to the top of the "index.php" file of your frontend template directly below the defined('_JEXEC') or die;, e.g. in case of Cassiopeia at line 12 of the "templates/cassiopeia/index.php" file:

var_dump(\Joomla\CMS\Extension\ExtensionHelper::getCoreExtensionIds());
die;

3. Go to the frontend (site) and copy the result of the var_dump statement into a text editor and save that in a text file.
4. Apply the changes from this PR.
5. Go again to the frontend (site), or if still there reload the page, or better use a new browser windows just in case caching is enabled.
6. Compare the result of the var_dump statement with the one saved in step 2.
   Result: The results from steps 2 and 5 are identical.

Actual result BEFORE applying this Pull Request

When a MariaDb database is used which has the "sql_big_selects" variable set to "OFF" (0) and the "max_join_size" to "67108864" or lower, and there is a core update available for which the pre-update checks are shown, you get an SQL error "1104 The SELECT would examine more than MAX_JOIN_SIZE rows" when going to the Joomla Update component in backend.

The same error happens when you are using the filter for core or non-core extensions in the Extensions Manager.

Expected result AFTER applying this Pull Request

No SQL error "1104 The SELECT would examine more than MAX_JOIN_SIZE rows" for the cases mentioned above.

Everything works as well as before for all other cases.

Link to documentations

Please select:

• Documentation link for docs.joomla.org:

• No documentation changes for docs.joomla.org needed

• Pull Request link for manual.joomla.org:

• No documentation changes for manual.joomla.org needed