MariaDB vs MySQL – Row Numbering Differences Explained

I've recently updated my server into a docker setup and switched from mysql to mariadb. No I've run into an issue with different behavior between Mysql 5.7 on my local machine and MariaDB 10.5.6 on the server.

The problem is a different result for a query which should return row numbers.
I'll explain using a minimal working example

Consider the following two tables:

>SHOW CREATE TABLE cars\G
*************************** 1. row ***************************
       Table: cars
Create Table: CREATE TABLE `cars` (
  `brand` varchar(128) DEFAULT NULL,
  `model` varchar(128) DEFAULT NULL,
  `value` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

>SHOW CREATE TABLE used_cars\G
*************************** 1. row ***************************
       Table: used_cars
Create Table: CREATE TABLE `used_cars` (
  `brand` varchar(128) DEFAULT NULL,
  `model` varchar(128) DEFAULT NULL,
  `price` int(10) unsigned DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

And some example data:

INSERT INTO `cars` (`brand`, `model`, `value`) VALUES
('Volvo', 'S40', 1),
('Porsche', 'Carrera', 4),
('Opel', 'Corsa', 3),
('Renault', 'Clio', 1),
('Renault', 'Megane', 3),
('Volvo', 'V40', 2);
INSERT INTO `used_cars` (`brand`, `model`, `price`) VALUES
('Volvo', 'S40', 2500),
('Porsche', 'Carrera', 7500);

Let's say I want to run a query which lists all cars and returns whether there is a used car in stock. And I want these to be ordered in a certain way:

SELECT (@row := @row + 1) AS score,
   qry.brand,
   qry.model,
   qry.used_present
FROM
  (SELECT cars.brand,
          cars.model,
          (used_cars.price IS NOT NULL) AS used_present
   FROM cars
   LEFT JOIN used_cars ON used_cars.model = cars.model
                          AND used_cars.brand = cars.brand) AS qry
CROSS JOIN
  (SELECT @row:=0) AS r
ORDER BY qry.used_present DESC,
         qry.brand ASC

this returns the following in MySQL

+-------+---------+---------+--------------+
| score | brand   | model   | used_present |
+-------+---------+---------+--------------+
|     1 | Porsche | Carrera |            1 |
|     2 | Volvo   | S40     |            1 |
|     3 | Opel    | Corsa   |            0 |
|     4 | Renault | Clio    |            0 |
|     5 | Renault | Megane  |            0 |
|     6 | Volvo   | V40     |            0 |
+-------+---------+---------+--------------+

While I get the following in MariaDB

+-------+---------+---------+--------------+
| score | brand   | model   | used_present |
+-------+---------+---------+--------------+
|     2 | Porsche | Carrera |            1 |
|     1 | Volvo   | S40     |            1 |
|     3 | Opel    | Corsa   |            0 |
|     4 | Renault | Clio    |            0 |
|     5 | Renault | Megane  |            0 |
|     6 | Volvo   | V40     |            0 |
+-------+---------+---------+--------------+

Can anyone help me how to fix this? In reality the innerquery qry is much more elaborate, but the results are similar as this MWE. Adding the ORDER BY to the innerquery doesn't change the results.

db<>fiddle