Bug #7945 SQL_CALC_FOUND_ROWS with distinct constant and group by
Submitted: 16 Jan 2005 18:50 Modified: 7 Mar 2005 0:03
Reporter: Martin Friebe (Gold Quality Contributor) (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.9 OS:Any (all)
Assigned to: Igor Babaev CPU Architecture:Any

[16 Jan 2005 18:50] Martin Friebe 
Description:
under some conditions SQL_CALC_FOUN_ROWS returns the wrong count,
see how to repeat

How to repeat:
create table t1 (a integer, b integer); insert into t1 values (1,2), (1,3), (1,4), (1,5);

# the next query is limited to 2, but returns only 1 row. so the result set does not have more than 1 row.
select SQL_CALC_FOUND_ROWS  distinct "a"  from t1  group by b limit 2 ;
+---+
| a |
+---+
| a |
+---+
1 row in set (0.00 sec)

select found_ROWS() ; 
+--------------+
| found_ROWS() |
+--------------+
|            4 |
+--------------+

# show 4 instead of 1

Suggested fix:
-
[22 Jan 2005 9:30] Igor Babaev 
I easily reproduced the problem for the current 4.0 tree as well.
[23 Jan 2005 0:09] Igor Babaev 
I fixed the bug in 4.0 and merged the patch into 4.1 and 5.0.

ChangeSet
  1.2037 05/01/22 02:40:27 igor@rurik.mysql.com +3 -0
  select_found.result, select_found.test:
    Added a test case for bug #7945.
  sql_select.cc:
    Fixed bug #7945. If DISTINCT is used only with constants
    in a query with GROUP BY, we can apply an optimization
    that set LIMIT to 1 only in the case when there is 
    no SQL_CALC_FOUND_ROWS.
[7 Mar 2005 0:03] Paul DuBois 
Noted in 4.0.24, 4.1.10, 5.0.3 changelogs.