With Mariadb 11.1.2 ordering of table is broken #290
Description
Copy from https://issues.joomla.org/tracker/joomla-cms/42333
joomla/joomla-cms#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.