User-Defined Variable Assignment difference between MySQL and MariaDB

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:

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

Answer

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.