MySQL Bugs: #1547: "SELECT ... WHERE field = var" sometimes give the wrong result in SPs

Bug #1547	"SELECT ... WHERE field = var" sometimes give the wrong result in SPs
Submitted:	14 Oct 2003 6:27	Modified:	15 Oct 2003 10:07
Reporter:	Per-Erik Martin	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0	OS:	Any (All)
Assigned to:	Per-Erik Martin	CPU Architecture:	Any

Description:
In some cases queries "SELECT ... INTO ... WHERE field = var" fails to match the
correct row when executed in a stored procedure (where "var" is a local
variable).
It seems this is because the WHERE clause returns true when it shouldn't.
However, in other test cases similar selects works, so it's somewhat
intermittent.

How to repeat:
Run this:
---
delimiter |;
create procedure frob(s char(16))
begin
  declare x int;

  select data into x from t1 where id = s limit 1;
  if x > 10 then
    insert into t1 values ("less", x-10);
  else
    insert into t1 values ("more", x+10);
  end if;
end|

insert into t1 values ("foo", 12), ("bar", 7)|
call frob("foo")|
call frob("bar")|
select * from t1|
---

The result is:
+------+------+
| id   | data |
+------+------+
| foo  |   12 |
| bar  |    7 |
| less |    2 |
| less |    2 |
+------+------+

but there should be only one "less" row, and one row with "more 17".
Apparently, the select always picks the first (foo) row, despite the
where condition.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html