Alice Sherepa added a comment - 2022-02-04 16:08 changed after be093c81a7955aa8309e650 commit ( MDEV-24089 https://github.com/MariaDB/server/commit/be093c81a7 ) create table t1 (id1 int , id2 int , id3 float ) engine=innodb; INSERT INTO `t1` VALUES (1,1,4),(2,1,2.7),(1,1,1.6),(2,2,0.3),(3,2,2.3),(1,1,8.6),(1,1,2.5),(3,1,9.5),(1,1,6.9),(2,2,9.7); SELECT b.*, (@currank := @currank + 1) AS peringkat FROM ( select id1,id2,id3 from t1 a, ( SELECT @curRank := 0) r ) b ORDER BY id1 desc ,id2 desc ,id3 desc ; SELECT b.*, (@var:=@var+1) FROM ( SELECT a.* , (@var := 0) r from t1 a) b order by id1; explain extended SELECT b.*, (@var:=@var+1) FROM ( SELECT a.* , (@var := 0) r from t1 a) b order by id1; on 10.6 -Using temporary : 10.6 MariaDB [test]> explain extended SELECT b.*, (@var:=@var+1) FROM (SELECT a.* , (@var := 0) r from t1 a) b order by id1; +------+-------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using temporary; Using filesort | | 2 | DERIVED | a | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | | +------+-------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+ 2 rows in set, 1 warning (0.001 sec) 10.5 MariaDB [test]> explain extended SELECT b.*, (@var:=@var+1) FROM (SELECT a.* , (@var := 0) r from t1 a) b order by id1; +------+-------------+------------+------+---------------+------+---------+------+------+----------+----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+------------+------+---------------+------+---------+------+------+----------+----------------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using filesort | | 2 | DERIVED | a | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | | +------+-------------+------------+------+---------------+------+---------+------+------+----------+----------------+ 2 rows in set, 1 warning (0.002 sec)

Michael Widenius added a comment - 2022-10-06 13:30 - edited Hi! This is not a bug, the issue is that the query is assuming too much about how setting a variable in a query works. The columns in the select clause are evaluated when they are accessed. The cases are: When the 'final row' is calculated and sent to the end user. When the columns are stored in a temporary table that are later sorted. In 10.6 the optimization for the query has changed from using the results directly to first store the rows into a temporary table, sorting this and then sending the result to the end user. The following queries shows the working in 10.6 in more detail: MariaDB [test]> explain SELECT b.*, (@currank := @currank + 1) AS peringkat FROM ( select id1,id2,id3 from t1 a, (SELECT @curRank := 0) r ) b ORDER BY id1 desc,id2 desc,id3 desc; +------+-------------+------------+--------+---------------+------+---------+------+------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+------------+--------+---------------+------+---------+------+------+---------------------------------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 10 | Using temporary; Using filesort | | 2 | DERIVED | <derived3> | system | NULL | NULL | NULL | NULL | 1 | | | 2 | DERIVED | a | ALL | NULL | NULL | NULL | NULL | 10 | | | 3 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | +------+-------------+------------+--------+---------------+------+---------+------+------+---------------------------------+   MariaDB [test]> SELECT b.*, (@currank := @currank + 1) AS peringkat FROM ( select id1,id2,id3 from t1 a, (SELECT @curRank := 0) r ) b;+------+------+------+-----------+ | id1 | id2 | id3 | peringkat | +------+------+------+-----------+ | 3 | 1 | 5.8 | 1 | | 2 | 2 | 1 | 2 | | 2 | 2 | 5.5 | 3 | | 1 | 2 | 1.1 | 4 | | 2 | 1 | 3 | 5 | | 1 | 2 | 6.2 | 6 | | 3 | 2 | 7.2 | 7 | | 3 | 1 | 6.6 | 8 | | 1 | 2 | 2.1 | 9 | | 1 | 1 | 6.8 | 10 | +------+------+------+-----------+   MariaDB [test]> SELECT b.*, (@currank := @currank + 1) AS peringkat FROM ( select id1,id2,id3 from t1 a, (SELECT @curRank := 0) r ) b ORDER BY id1 desc,id2 desc,id3 desc; +------+------+------+-----------+ | id1 | id2 | id3 | peringkat | +------+------+------+-----------+ | 3 | 2 | 7.2 | 7 | | 3 | 1 | 6.6 | 8 | | 3 | 1 | 5.8 | 1 | | 2 | 2 | 5.5 | 3 | | 2 | 2 | 1 | 2 | | 2 | 1 | 3 | 5 | | 1 | 2 | 6.2 | 6 | | 1 | 2 | 2.1 | 9 | | 1 | 2 | 1.1 | 4 | | 1 | 1 | 6.8 | 10 | +------+------+------+-----------+ The last result is a sorted version of the second to last version, which is expected when temporary tables are used to hold the result. Using := to increment values should only be done when on is sure that the column is only accessed once. It is not usable for complex queries when one wants to get intermediate results. In this case it's better to use row_number() which is well defined.