MySQL Bugs: #47517: Wrong result with a User-Defined Variable, ORDER BY, and JOIN

Bug #47517	Wrong result with a User-Defined Variable, ORDER BY, and JOIN
Submitted:	22 Sep 2009 11:48	Modified:	22 Sep 2009 15:01
Reporter:	Christian Kaps	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: General	Severity:	S1 (Critical)
Version:	5.1.39	OS:	Linux (RHEL5, Gentoo)
Assigned to:		CPU Architecture:	Any
Tags:	join, order by, User-Defined Variables

Description:
MySQL gives me a wrong result in combination with a User-Defined Variable, JOIN and ORDER BY statement.

How to repeat:
CREATE TABLE `vartest` (
   id INT NOT NULL
);
INSERT INTO `vartest` VALUES (5), (1), (3), (7), (2), (9), (6), (8), (4);

CREATE TABLE `varjointest` (
   id INT NOT NULL
);
INSERT INTO `varjointest` VALUES (5), (1), (3), (7), (2), (9), (6), (8), (4);

SET @row = 0;
SELECT 
	`t1`.`id`, 
	@row := @row + 1 as `position` 
FROM `vartest` t1 
JOIN `varjointest` t2 ON `t1`.`id` = `t2`.`id`
ORDER BY `t1`.`id`;

Given result:
+----+----------+
| id | position |
+----+----------+
|  1 |        2 |
|  2 |        5 |
|  3 |        3 |
|  4 |        9 |
|  5 |        1 |
|  6 |        7 |
|  7 |        4 |
|  8 |        8 |
|  9 |        6 |
+----+----------+
9 rows in set (0.00 sec)

Expected result:
+----+----------+
| id | position |
+----+----------+
|  1 |        1 |
|  2 |        2 |
|  3 |        3 |
|  4 |        4 |
|  5 |        5 |
|  6 |        6 |
|  7 |        7 |
|  8 |        8 |
|  9 |        9 |
+----+----------+
9 rows in set (0.00 sec)

ORDER BY is executed as a last step, when all data are already got from the database and put into a temporary table (check with EXPLAIN). So, why do you expect anything different from what you really got? I think this is NOT a bug.

Your explanation is plausible, but i am confused. We have upgraded our MySQL Server from 5.0.27 to 5.1.39. And the result what I expected was the result from MySQL 5.0.27. Which version has the bug?

I agree with your opinion that when I look into explain this is the right behavior. But without the explain it is counter-intuitively. (o;

Well, maybe you had indexes on some columns in 5.0.27. If you still have it around, check EXPLAIN results there. But, in general, current behavior of 5.1.39 is OK and you should never rely on SQL like this.

There are the same tables, with same data and indexes on both systems. I checked the explain and it was the same.