Confusing ordering of user variable counter in MariaDB

Question

Recently I've updated a server and switched from MySQL to MariaDB. One query behaves now differently and I do not understand why. Please enlighten me.

This is my current version

mariadb --version
mariadb  Ver 15.1 Distrib 10.6.11-MariaDB, for debian-linux-gnu (x86_64) using  EditLine wrapper

The actual query is very long, but here is a condensed form of my problem. I need to update a variable test, which is updated for each row after all rows have been ordered.

The actual update is more complex, but should not matter here:

@stockMass := @stockMass +
        CASE
                WHEN `u`.`context` = 'purchase' AND `u`.`status` != 'canceled' THEN `u`.`mass`
                WHEN `u`.`context` = 'sale' AND `u`.`status` != 'canceled' THEN -`u`.`mass`
                WHEN `u`.`context` = 'massAdjustment' THEN `u`.`mass`
                WHEN `u`.`context` = 'splitIn' THEN `u`.`mass`
                WHEN `u`.`context` = 'splitOut' THEN -`u`.`mass`
                ELSE 0
        END AS `stock`

SET @test := 0;
SELECT
    *,
    @test := @test + 1 AS `test`
FROM (
    SELECT
        `g_sales`.`sale`,
        `g_sales`.`date`
    FROM
        `g_sales`
    ORDER BY
        `g_sales`.`date`
) AS `t` ORDER BY `t`.`date`;

results in

+------+------------+------+
| sale | date       | test |
+------+------------+------+
|  106 | 2019-06-19 | 2703 |
|   85 | 2019-10-11 | 2685 |
|   81 | 2019-11-12 | 2681 |
|   96 | 2019-12-09 | 2695 |
|  104 | 2020-03-26 | 2701 |
|   87 | 2020-04-06 | 2687 |
|   94 | 2020-05-15 | 2693 |
|  107 | 2020-05-18 | 2704 |
|   98 | 2020-05-28 | 2697 |
|  103 | 2020-05-28 | 2700 |
|  ... | .......... | .... |
+------+------------+------+

In MySQL test started at 1 and was incremented by one in each row. Adding a limit to the inner SELECT gets me a similar result in MariaDB.

SET @test := 0;
SELECT
    *,
    @test := @test + 1 AS `test`
FROM (
    SELECT
        `g_sales`.`sale`,
        `g_sales`.`date`
    FROM
        `g_sales`
    ORDER BY
        `g_sales`.`date`
    LIMIT 10 OFFSET 0
) AS `t`;

which results in

+------+------------+------+
| sale | date       | test |
+------+------------+------+
|  106 | 2019-06-19 |    1 |
|   85 | 2019-10-11 |    2 |
|   81 | 2019-11-12 |    3 |
|   96 | 2019-12-09 |    4 |
|  104 | 2020-03-26 |    5 |
|   87 | 2020-04-06 |    6 |
|   94 | 2020-05-15 |    7 |
|  107 | 2020-05-18 |    8 |
|   98 | 2020-05-28 |    9 |
|  103 | 2020-05-28 |   10 |
+------+------------+------+

How can I get this result in MariaDB without adding a limit to the inner SELECT?

And why do I get this result when adding the LIMIT?

Why not use row_number window function.https://mariadb.com/kb/en/row_number/ — P.Salmon, Jan 23 '23 at 11:05
Because I do not need the row number. As written above, the actual calculation is more complex based on different column values. — piece, Jan 23 '23 at 11:12
It behaves on this fiddle just like on my system: https://dbfiddle.uk/yCRAOTtU You can even change to MySQL and see my expected result. — piece, Jan 23 '23 at 12:32
I don't think the published question accurately reflects what you are trying to achieve which if the link is anything to go by is to amend the table to include an auto_increment id but before doing so allocate an id based on date. Unfortunately if there are multiple entries for the same date mysql/mariadb then the order of those items is non determinate, to force an order within date you would need another order by column that makes the order by clause unique. — P.Salmon, Jan 23 '23 at 13:08
And again, I do not need an incrementing value. This is just a proof of concept. My question is perfectly fine as far as I see it. You can add the sale column to the order to make it unique. The problem persists. — piece, Jan 23 '23 at 14:34
Fair enough , but the fact that you aren't attracting any other contributors indicates otherwise. In any case I don't get it and I'm out. — P.Salmon, Jan 23 '23 at 14:39

giordano · Answer 1 · 2023-05-17T06:03:32.957

I had similar problems (see here) when changing from MariaDB V10.4.12 to V10.6.8.

The commenter in my post mentioned that

MySQL is deprecating the assigning of @variables in SELECTs.

I'm not sure if this is correct but it seems that defining a user defined variable in a SELECT is not well defined considering the order. See for example in MySQL manual:

The order of evaluation for expressions involving user variables is undefined. For example, there is no guarantee that SELECT @a, @a:=@a+1 evaluates @a first and then performs the assignment.

or in MariaDB manual:

It is unsafe to read a user-defined variable and set its value in the same statement (unless the command is SET), because the order of these actions is undefined.

You could use Windows function, for example, ROW_NUMBER but as you mentioned it does not work if you have complex conditions.

The nice thing about using and defining user defined variables in SELECT is that it is a powerful method to define rows using any condition you need:

SELECT @x := IF(id = @previous_id AND @y=1 AND other conditions, 1,100)
     , @previous_id := id
     , @y := something
     , ....

Since newer versions of MariaDB/MySQL this kind of queries seems not to work. This is pitty and I'm wondering why this is the case and what are the alternatives.

Confusing ordering of user variable counter in MariaDB

1 Answers1

Linked