With Mariadb 11.1.2 ordering of table is broken about joomla-cms HOT 3 CLOSED

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.

from joomla-cms.

joomla-framework/database#291

from joomla-cms.

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

from joomla-cms.