User-Defined Variable Assignment difference between MySQL and MariaDB

Question

Of course, we do our job before and searched this community or the web for similar posts but we found nothing.

Issue:

We are not happy with our current hosting provider and have to make change. In the middle of our database switch from MySQL 5.7 to MariaDB 10.3, we have recognized a SET handling difference, which works perfect in MySQL.

SQL code:

SET @sb = 0;

SELECT art,sb
FROM ARTICLE
WHERE(@sb:=sb) > 700 AND @sb <= 1000 >AND art = 'AM';

MySQL result:

art	sb
AM	900.00
AM	960.00
AM	1000.00
AM	770.00
AM	800.00
the list is much longer...

MariaDB result:

art	sb
AM	770.00
AM	960.00
AM	1200.00
AM	3000.00
only 10 rows...

In fact there is a difference between them.

But we want to know:

Can we configure this in MariaDB, that we get the same result in MySQL?
Should we replace the Set handling in MariaDB with another one?

The order of operations within a `SELECT` list or `WHERE` clause has never been defined to be left-to-right, so you shouldn't depend on the assignment happening before reading it a second time. — Barmar, Dec 16 '20 at 16:27
Why do you need to use `@sb`? Why can't you use `WHERE sb > 700 AND sm <= 1000`? — Barmar, Dec 16 '20 at 16:28
it's just an example. it's historical grown of the Statements. We just want to know if or how we can handle it in MariaDB. — JonProloco, Dec 16 '20 at 16:32
https://stackoverflow.com/questions/38923660/why-the-order-of-evaluation-for-expressions-involving-user-variables-is-undefine — Barmar, Dec 16 '20 at 16:33

score 1 · Accepted Answer · answered Dec 16 '20 at 16:30

No, this is not possible.

In fact, the MySQL documentation warns about the use of variable assignments inside SQL statements:

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.

What's more, this is a "feature" that is subject to removal:

Previous releases of MySQL made it possible to assign a value to a user variable in statements other than SET. This functionality is supported in MySQL 8.0 for backward compatibility but is subject to removal in a future release of MySQL.

So you should not rely on that, and rewrite your queries.

score 0 · Answer 2 · answered Dec 16 '20 at 16:32

The MySQL documentation is quite clear on this point:

As a general rule, other than in SET statements, you should never assign a value to a user variable and read the value within the same statement.

That your code ever worked was something of a coincidence. The reason for such warnings is that behavior may change between releases; or even that the results may be inconsistent between runs on the same database.

You should replace the variables with window functions. The exact function is a bit unclear. Sample data, desired results, and a clear explanation of the results always helps.

User-Defined Variable Assignment difference between MySQL and MariaDB

2 Answers2