| Bug #31255 | User variables evaluated twice when used with control flow functions and join. | ||
|---|---|---|---|
| Submitted: | 27 Sep 2007 18:28 | Modified: | 23 Jul 2008 4:59 |
| Reporter: | Benoit Heinrich | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: General | Severity: | S1 (Critical) |
| Version: | ALL | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | user variables control flow functions join twice | ||
[27 Sep 2007 20:33]
Benoit Heinrich
Another interesting test to complete the report.
Because I'm trying to find a workaround for that kind of queries, I think I found one replacing the 'where' by a 'having':
SET @count :=0;
select
a.ID, b.ID as bID, @count
from
a inner join b on a.ID=b.IDA
having
if(mod(bID,7) = 0 and @count < 10 and @count := @count+1, 1, 0);
/*
outputs:
+----+-----+--------+
| ID | bID | @count |
+----+-----+--------+
| 7 | 7 | 1 |
| 5 | 14 | 2 |
| 3 | 21 | 3 |
| 1 | 28 | 4 |
| 8 | 35 | 5 |
| 6 | 42 | 6 |
| 4 | 49 | 7 |
| 2 | 56 | 8 |
| 9 | 63 | 9 |
| 7 | 70 | 10 |
+----+-----+--------+
10 rows in set (0.00 sec)
*/
Hope it will help
/Benoit
[23 Jul 2008 4:59]
Valeriy Kravchuk
Thank you for a problem report. I was able to repeat your findings on recent 5.1.26-rc and 5.1.28 versions. But according to the manual, http://dev.mysql.com/doc/refman/5.1/en/user-variables.html: "The order of evaluation for user variables is undefined and may change based on the elements contained within a given query. In SELECT @a, @a := @a+1 ..., you might think that MySQL will evaluate @a first and then do an assignment second, but changing the query (for example, by adding a GROUP BY, HAVING, or ORDER BY clause) may change the order of evaluation. The general rule is never to assign a value to a user variable in one part of a statement and use the same variable in some other part of the same statement. You might get the results you expect, but this is not guaranteed." So, this is not a bug formally.
Description: Hi, When you use control flow functions and user variables in a select statement, then the user variables are interpreted twice. !! This is true only if use joined tables !! /Benoit How to repeat: /* First is to create the two tables */ create table a (ID int unsigned not null auto_increment, PRIMARY KEY (ID)); create table b (ID int unsigned not null auto_increment, IDA int unsigned not null, PRIMARY KEY (ID)); /* and to add some content */ insert into a values (); insert into a values (); insert into a values (); insert into a values (); insert into a values (); insert into a values (); insert into a values (); insert into a values (); insert into a values (); insert into b (IDA) select * from a; insert into b (IDA) select * from a; insert into b (IDA) select * from a; insert into b (IDA) select * from a; insert into b (IDA) select * from a; insert into b (IDA) select * from a; insert into b (IDA) select * from a; insert into b (IDA) select * from a; /* Then the select with only one table works perfectly */ SET @count :=0; select b.ID, @count from b where case when (mod(b.ID,7) = 0 and @count < 10 and @count := @count+1) then 1 else 0 end; /* The result is the one we expect: +----+--------+ | ID | @count | +----+--------+ | 7 | 1 | | 14 | 2 | | 21 | 3 | | 28 | 4 | | 35 | 5 | | 42 | 6 | | 49 | 7 | | 56 | 8 | | 63 | 9 | | 70 | 10 | +----+--------+ 10 rows in set (0.00 sec) */ /* Now we do the same but with the two table joined */ SET @count :=0; select a.ID, @count from a inner join b on a.ID=b.IDA where case when (mod(b.ID,7) = 0 and @count < 10 and @count := @count+1) then 1 else 0 end; /* Then here we see that is missing information, the @count seems to be interpreted twice for each row: +----+--------+ | ID | @count | +----+--------+ | 7 | 2 | | 5 | 4 | | 3 | 6 | | 1 | 8 | | 8 | 10 | +----+--------+ 5 rows in set (0.00 sec) */ /* So just to be sure we haven't made an error somewhere with the join, I perform more or less the same statement except that I removed the 'case' */ SET @count :=0; select a.ID, @count from a inner join b on a.ID=b.IDA where mod(b.ID,7) = 0 and @count < 10 and @count := @count+1; /* Here it works: +----+--------+ | ID | @count | +----+--------+ | 7 | 1 | | 5 | 2 | | 3 | 3 | | 1 | 4 | | 8 | 5 | | 6 | 6 | | 4 | 7 | | 2 | 8 | | 9 | 9 | | 7 | 10 | +----+--------+ 10 rows in set (0.00 sec) */ /* I also tried the if function, just to be sure it does not come only from the case */ SET @count :=0; select a.ID, @count from a inner join b on a.ID=b.IDA where if(mod(b.ID,7) = 0 and @count < 10 and @count := @count+1, 1, 0); /* And here we are, same problem: +----+--------+ | ID | @count | +----+--------+ | 7 | 2 | | 5 | 4 | | 3 | 6 | | 1 | 8 | | 8 | 10 | +----+--------+ 5 rows in set (0.00 sec) */ Suggested fix: Interpret only once the function expression per row.