With Mariadb 11.1.2 ordering of table is broken

[tkuschel]

Steps to reproduce the issue

1. Installation of MariaDB Version 11.1.2 (active Arch Linux),
2. Goto backend (Administrator),
3. Open Components > Banners > Banners,
4. Create three new banners named 1st_banner, 2nd_banner, 3rd_banner,
5. Click on Sort By "Ordering ascending",
6. "Drag and Drop" the second banner to the first position - ordering now: 2 - 1 - 3,
7. Click on the search magnifying glass to reload the banner or reload page.

Expected result

like with MariaDB 10.11.4 (active Debian Bookworm, etc.)

the ordering of the banners is 2 - 1 - 3

the ordering is retained.

Actual result

Ordering of the banners is reordered to 1 - 2 - 3

You cannot change the ordering with "Drag and Drop".

System information (as much as possible)

Joomla! 5.0.0, 4.4.0, 4.3.4

PHP Versions 8.2.12, 8.1.25, 8.3.0RC5

MariaDB 11.1.2

Additional comments

I am currently developing a component and have several tables with the field "ordering". I have noticed that drag 'n drop does not work on my developer system under Arch Linux, but it does work on the production system under Debian and also on an older BSD system. To cut a long story short, I have tried all possible PHP versions and Joomla versions. However, the version of the MariaDB database plays a role:
The function in /libraries/src/table.php calls a function public function reorder($where = ''), which is processed differently.
It forms a query such as

UPDATE `j5_banners`
INNER JOIN (
SELECT * FROM (
SELECT (SELECT @rownum := @rownum + 1 FROM (SELECT @rownum := 0) AS r) AS new_ordering,`id` AS `pk__0`
FROM `j5_banners`
WHERE `ordering` >= 0
ORDER BY `ordering`
) w) AS sq 
SET `ordering` = sq.new_ordering
WHERE `ordering` >= 0 AND `id` = sq.`pk__0`;

This query does reorder the table in MariaDB 10.11.4 but equals the id with ordering in MariaDB 11.1.2

Perhaps, there is also correlation to https://jira.mariadb.org/browse/MDEV-27745

[tkuschel]

FURTHER BUG REPORT

I took a closer look at the function reorder and found out that the query is created with a windows function which is defined in /libraries/vendor/joomla/database/src/DatabaseQuery.php with the following line in

public function selectRowNumber($orderBy, $orderColumnAlias)

return $this->select("ROW_NUMBER() OVER (ORDER BY $orderBy) AS $orderColumnAlias");

but with
MysqlQueryBuilder.php it is overwritten to

return $this->select("(SELECT @rownum := @rownum + 1 FROM (SELECT @rownum := 0) AS r) AS $orderColumnAlias");

This no longer works, so the first variant does it quite correctly.
Since the MysqlQueryBuilder.php is just a "traited" class of the former class DatabaseQuery in DatabaseQuery.php, we can simply remove this function in the MysqlQueryBuilder.php: (here it is commented out)

/*
  public function selectRowNumber($orderBy, $orderColumnAlias)
  {
     $this->validateRowNumber($orderBy, $orderColumnAlias);

     return $this->select("(SELECT @rownum := @rownum + 1 FROM (SELECT @rownum := 0) AS r) AS $orderColumnAlias");
  }
*/

It works for:

• MariaDB Version 11.1.2, MariaDB Version 10.11.4,
• Both connections via MySQLi and PDO
• Joomla 5.0.0 and 4.4.0 with removed function selectRowNumber in MysqlQueryBuilder.php

Good to know:
The used SQL function ROW_NUMBER(), is introduced in MYSQL since version 8.0 (2016/09)
So the requirements for Joomla 5 with MySQL 8.1 minimum 8.0.13, and
MariaDB 11.1.0 minimum 10.4.0 should work.

---

_{This comment was created with the J!Tracker Application at issues.joomla.org/tracker/joomla-cms/42333.}

[tkuschel]

joomla-framework/database#291

---

_{This comment was created with the J!Tracker Application at issues.joomla.org/tracker/joomla-cms/42333.}

[alikon]

thanks for reporting and to propose a fix please follow upstream joomla-framework/database#291

[nielsnuebel]

It's effect each component, not only com_banners. I have test successfully the PR 291.

---

_{This comment was created with the J!Tracker Application at issues.joomla.org/tracker/joomla-cms/42333.}

[richard67]

Alternative PR see joomla-framework/database#300 . Please test.

[richard67]

See also joomla-framework/database#308 for another alternative way to do it. Please test. No, joomla-framework/database#300 is ok.