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
• Status: Closed
• 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.