Description:
Using sql_calc_found_rows in conjunction with distinct returns incorrect result set. Also noted that when removing LIMIT from the query, correct result is found.
4.0.31 produces the erroneous result.
4.1.23-debug, 5.0.40-debug, 5.1.18-beta-debug from bk do not.
4.0.31-debug-log
mysql> select sql_calc_found_rows distinct t1_id,t1_v1 from t1 left join t2 on t1_v1 = t2_v1 where t1_id = 1 limit 2;
+-------+-------+
| t1_id | t1_v1 |
+-------+-------+
| 1 | 1 |
| 1 | 1 |
+-------+-------+
mysql> select sql_calc_found_rows distinct t1_id,t1_v1 from t1 left join t2 on t1_v1 = t2_v1 where t1_id = 1;
+-------+-------+
| t1_id | t1_v1 |
+-------+-------+
| 1 | 1 |
+-------+-------+
----- other versions found to be working correctly.
4.1.23-debug
mysql> select sql_calc_found_rows distinct t1_id,t1_v1 from t1 left join t2 on t1_v1 = t2_v1 where t1_id = 1 LIMIT 2;
+-------+-------+
| t1_id | t1_v1 |
+-------+-------+
| 1 | 1 |
+-------+-------+
mysql> select sql_calc_found_rows distinct t1_id,t1_v1 from t1 left join t2 on t1_v1 = t2_v1 where t1_id = 1;
+-------+-------+
| t1_id | t1_v1 |
+-------+-------+
| 1 | 1 |
+-------+-------+
5.0.40-debug
mysql> select sql_calc_found_rows distinct t1_id,t1_v1 from t1 left join t2 on t1_v1 = t2_v1 where t1_id = 1 limit 2;
+-------+-------+
| t1_id | t1_v1 |
+-------+-------+
| 1 | 1 |
+-------+-------+
5.1.18-beta-debug
mysql> select sql_calc_found_rows distinct t1_id,t1_v1 from t1 left join t2 on t1_v1 = t2_v1 where t1_id = 1 limit 2;
+-------+-------+
| t1_id | t1_v1 |
+-------+-------+
| 1 | 1 |
+-------+-------+
How to repeat:
use test;
drop table if exists t1;
create table `t1` (`t1_id` int(11) NOT NULL default '0',`t1_v1` int(11) NOT NULL default '0',PRIMARY KEY (`t1_id`)) TYPE=InnoDB;
drop table if exists t2;
create table `t2` (`t2_id` int(11) NOT NULL default '0',`t2_v1` int(11) NOT NULL default '0',PRIMARY KEY (`t2_id`)) TYPE=InnoDB;
insert into t1 values (1,1),(2,1);
insert into t2 values (1,1),(2,1);
select sql_calc_found_rows distinct t1_id,t1_v1 from t1 left join t2 on t1_v1 = t2_v1 where t1_id = 1 limit 2;
