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
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: >=5.0.60,>=5.1.24,5.5.10,5.6.2
• OS: Any
• 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.