Bug #59736 select distinct.. incorrect result with deterministic function in where clause
Submitted: 26 Jan 2011 10:33 Modified: 30 Sep 2011 18:13
Reporter: Arnaud Adant Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:>=5.0.60,>=5.1.24,5.5.10,5.6.2 OS:Any
Assigned to: CPU Architecture:Any
Tags: DETERMINISTIC, distinct, FUNCTION, where

[26 Jan 2011 10:33] Arnaud Adant
Description:
Since the fix for bug #32942 ((now() - interval '7200' second is NOT pre-calculated, causing "full table scan")),

select distinct queries with joins and deterministic function calls 

(using SQL, select into or cursor) in the where clause  return incorrect results. Occurs only when an index is used to join.

mysql> select distinct `d`  from `t1`,`t2`  where `a`=`c` and `b`=`f1`();
+------+
| d    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> select count(distinct `d`) from `t1`,`t2`  where `a`=`c` and `b`=`f1`();
+---------------------+
| count(distinct `d`) |
+---------------------+
|                   2 |
+---------------------+
1 row in set (0.00 sec)

How to repeat:
drop function if exists `f1`;
drop table if exists `t1`,`t2`;
create table `t1`(`a` int,`b` int,key(`b`))engine=myisam;
create table `t2`(`c` int,`d` int,key(`c`))engine=myisam;
insert into `t1` values (1,1),(1,1),(1,2);
insert into `t2` values (1,1),(1,2);

delimiter $

create function `f1`() returns int deterministic
begin
  declare `a` int;
  -- SQL statement inside
  select 1 into `a`;
  return `a`;
end $
delimiter ;

-- 
select distinct `d`        from `t1`,`t2`  where `a`=`c` and `b`=`f1`();
select count(distinct `d`) from `t1`,`t2`  where `a`=`c` and `b`=`f1`();

Suggested fix:
Rewrite the query.

1. use a subselect :

select distinct `d`   from (select d     from `t1`,`t2`  where `a`=`c` and `b`=`f1`()) Z;

2. use the equivalent group by 

select d     from `t1`,`t2`  where `a`=`c` and `b`=`f1`() group by d
[26 Jan 2011 10:48] MySQL Verification Team
Verified as described.
[30 Sep 2011 18:13] Paul DuBois
Noted in 5.1.59, 5.5.16, 5.6.3 changelogs.

SELECT DISTINCT with a deterministic stored function in the WHERE
clause could produce incorrect results.