MySQL Bugs: #29881: stored routine function in where clause

Bug #29881	stored routine function in where clause
Submitted:	18 Jul 2007 21:11	Modified:	21 Jul 2007 21:37
Reporter:	Matthew Kent	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:	5.0.45, 5.0 BK, 5.1 BK	OS:	Linux
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	regression

Description:
I have a query calling a stored routine function in its where clause. This worked fine in 5.0.27, but upon upgrading to 5.0.45 its decided to try and evaluate the result of the function against every row in the table which has proved quite slow and raised my queries per second to ~32000 from 1000 :)

In my test case the function is deterministic and modifies no sql data, so it's my understanding it should only be invoked once, and its result be matched against the index thus only involving one row.

good 5.0.27-max-log
mysql> explain select x from demo where x = demofunc()\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: demo
         type: ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 3
          ref: const
         rows: 1
        Extra: Using where; Using index
1 row in set (0.00 sec)

mysql> explain select x from demo where x = "e"\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: demo
         type: ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 3
          ref: const
         rows: 1
        Extra: Using where; Using index
1 row in set (0.00 sec)

not so good 5.0.45-community-log:
mysql> explain select x from demo where x = demofunc()\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: demo
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 3
          ref: NULL
         rows: 5
        Extra: Using where; Using index
1 row in set (0.00 sec)

mysql> explain select x from demo where x = "e"\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: demo
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 3
          ref: const
         rows: 1
        Extra: Using index
1 row in set (0.00 sec)

but perhaps I'm missing something in my understanding of this issue.

Interestingly enough doing the same test case with ints instead of varchars throughout produces a correct result in both versions, but I don't pretend to understand the differences in how varchars would be handled versus integers in this case.

How to repeat:
Test case:

delimiter //

drop function if exists `demofunc`; //
create function demofunc() returns varchar(1)
    deterministic
    contains sql
    begin
        return "e";
end; //

delimiter ;

drop table if exists `demo`;
create table demo (primary key (x))
 select "a" as x union
 select "b" as x union
 select "c" as x union
 select "d" as x union
 select "e" as x;

explain select x from demo where x = demofunc()\G
explain select x from demo where x = "e"\G

Suggested fix:
evaluate the demofunc() only once and match against it

expanded the demo table a bit, here's some proof of the actual extra questions: same data for each

5.0.45-community-log
mysql> show status like 'Questions'\G select x from demo where x = demofunc()\G show status like 'Questions'\G
*************************** 1. row ***************************
Variable_name: Questions
        Value: 45
1 row in set (0.00 sec)

*************************** 1. row ***************************
x: q
1 row in set (0.00 sec)

*************************** 1. row ***************************
Variable_name: Questions
        Value: 70
1 row in set (0.01 sec)

14 questions to the db

5.0.27-max-log
mysql> show status like 'Questions'\G select x from demo where x = demofunc()\G show status like 'Questions'\G
*************************** 1. row ***************************
Variable_name: Questions
        Value: 44
1 row in set (0.01 sec)

*************************** 1. row ***************************
x: q
1 row in set (0.00 sec)

*************************** 1. row ***************************
Variable_name: Questions
        Value: 50
1 row in set (0.00 sec)

5 questions to the db

Thank you for the report.

Verified as described. Bug occured firs time in version 5.0.42.

Duplicate of #29338.

Duplicate of bug #29338.