Bug #27354 stored function in where condition was always treated as const
Submitted: 21 Mar 2007 19:33 Modified: 24 Apr 2007 1:32
Reporter: Andrei Elkin Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.38 OS:Any
Assigned to: Andrei Elkin CPU Architecture:Any

[21 Mar 2007 19:33] Andrei Elkin
Description:
If top level query table is empty a strored function in

update t2 set a=1 where a=bug()

still executes and leaves possible side effects.

Another side of the problem is in that stored functions were treated as constants which could cause not-determinism in statements execution.

How to repeat:
CREATE TABLE t1 (a int(11) NOT NULL auto_increment primary key) engine=MyISAM;
delimiter |;
CREATE TABLE t2 (a int auto_increment primary key) engine=MyISAM;

CREATE stored function bug RETURNS int 
begin  insert into t1 values (null);  return 1;  end|
update t2 set a=1 where a=bug() /* must return 0 updated  and w/o the patch table t1 would contain the insert by bug() */;
[22 Mar 2007 14:54] Andrei Elkin
Another more advanced test case can be found in a cset just going for committing.
[22 Mar 2007 17:05] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/22667

ChangeSet@1.2487, 2007-03-22 19:03:08+02:00, aelkin@andrepl.(none) +3 -0
  Bug #27354 stored function in where condition was always treated as const
  
  Possible problems: function call could be eliminated from where class and only
  be evaluated once; function can be evaluated during table and item setup phase which could
  cause side effects not to be registered in binlog.
  
  Fixed with introducing func_item_sp::used_tables() returning the correct table_bit
[22 Mar 2007 17:19] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/22669

ChangeSet@1.2487, 2007-03-22 19:17:15+02:00, aelkin@andrepl.(none) +3 -0
  Bug #27354 stored function in where condition was always treated as const
  
  Possible problems: function call could be eliminated from where class and only
  be evaluated once; function can be evaluated during table and item setup phase which could
  cause side effects not to be registered in binlog.
  
  Fixed with introducing func_item_sp::used_tables() returning the correct table_map constant.
[2 Apr 2007 7:43] Andrei Elkin
pushed to 5.0-marvel
[18 Apr 2007 15:46] Bugs System
Pushed into 5.1.18-beta
[18 Apr 2007 15:48] Bugs System
Pushed into 5.0.42
[24 Apr 2007 1:32] Paul Dubois
Noted in 5.0.42, 5.1.18 changelogs.

A stored function invocation in the WHERE clause was treated as a
constant.