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
