Row numbering difference between mariadb and mysql

Question

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

Answer 1

Mysql keeps the order fro the subquery, mariadb "optimizes it away, because that what the standard tells.

You can use for MAriadb a LIMIT so that the order will be kept

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
ORDER BY `price` LIMIT 18446744073709551615) AS qry
CROSS JOIN
  (SELECT @row:=0) AS r
ORDER BY qry.used_present DESC,
         qry.brand ASC

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

db<>fiddle here

Answer 2

From a logical perspective, ORDER BY is applied after (@row := @row + 1), so which row gets which number is non-deterministic. However, there may be something in the documentation, that guarantees that assignment of @row respects the ORDER BY. In that case, you should file a bug, if not I would not trust that it does.

I really don't understand why you are developing against an ancient DBMS, and running a much newer version in production, I think you should upgrade your local DBMS so that you have the same version.

Upgrading and using window function will get you out of trouble:

SELECT row_number() over (order by qry.used_present DESC, qry.brand ASC) 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
ORDER BY qry.used_present DESC
       , qry.brand
;