joomla-framework
With Mariadb 11.1.2 ordering of table is broken
Copy from https://issues.joomla.org/tracker/joomla-cms/42333 https://github.com/joomla/joomla-cms/issues/42333
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
selectRowNumberinMysqlQueryBuilder.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.
See also my alternative PR #300 which still supports older databases, as the minimum database requirements of the database framework have not been changed in the recent years so they are still the same on all 3 branches.
