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:
Category:MySQL Server Severity:S1 (Critical)
Version:5.0 OS:Any (All)
Assigned to: Bugs System CPU Architecture:Any

[14 Oct 2003 6:27] Per-Erik Martin
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
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

How to repeat:
Run this:
delimiter |;
create procedure frob(s char(16))
  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);
    insert into t1 values ("more", x+10);
  end if;

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.
[15 Oct 2003 10:07] Per-Erik Martin
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