Debian squeeze (x64)

Hi,

When you are saying that it works fine on MySQL, which version do you have in mind? From all I see the last version that was this lucky is 5.0, is it really what you are using?

I'm getting the "misordered" result on all of MySQL 5.1-5.7 as well as MariaDB 5.1-10.0:

create table rank (position INT, petId INT, userId INT, oldPosition INT, PRIMARY KEY (userId, petId)) engine=MyISAM;
create table pet (experience INT, id INT, userId INT, PRIMARY KEY(id)) engine=MyISAM;
create table user (id INT, lastConnection DATETIME, PRIMARY KEY(id)) engine=MyISAM;
INSERT INTO pet VALUES (10, 15, 1), (2, 20, 1), (12, 7, 2);
INSERT INTO user VALUES (1, NOW()), (2, NOW());
SET @var=0;
INSERT INTO rank (position,petId,userId) SELECT (@var:=@var+1), id, userId FROM pet ORDER BY experience DESC ON DUPLICATE KEY UPDATE oldPosition=position, position=VALUES(position);
select * from rank order by position;
position petId userId oldPosition
1 7 2 NULL
2 15 1 NULL
3 20 1 NULL
truncate rank;
SET @var=0;
INSERT INTO rank (position,petId,userId) SELECT (@var:=@var+1), p.id, p.userId FROM pet AS p JOIN user AS u ON p.userId = u.id AND u.lastConnection >= ADDDATE(NOW(), INTERVAL -1 MONTH) ORDER BY p.experience DESC ON DUPLICATE KEY UPDATE oldPosition=position, position=VALUES(position);
select * from rank order by position;
position petId userId oldPosition
1 15 1 NULL
2 20 1 NULL
3 7 2 NULL
select @@version;
@@version
5.7.2-m12-debug-log

MySQL thinks it's not a bug, here is the example:
http://bugs.mysql.com/bug.php?id=47517

However, I haven't found anything in documentation that says so, thus I'll go for the expert opinion. Assigning to Sanja for this purpose.

Hello Elena,

We are using Mysql 5.1.61-0+squeeze1. The main difference with your test is that we are not using MyISAM but InnoDB engine.
I have processed the same test on the twice engine and it works only on InnoDB with Mysql 5.1.61.
Sorry, I forgot to point out the engine used.

SELECT list (and so variables) calculated on unsorted data, and then ORDER BY applied to the result.

So if above query was working somewhere then it is just good luck, result of it very depends on execution plan, used storage engine and for some engines even in which order the tables was filled.

You should use some other (more reliable) mechanistam to get what you want, the first what came to my mind is intermidiate (temporary?) table with autoincrement. Maybe there is some other solution also...