Bug #3738 SQL_CALC_FOUND_ROWS ignores WHERE if LIMIT used

• Submitted: 13 May 2004 9:11
• Modified: 28 May 2004 13:53
• Reporter: ladimir Kolpakov
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.0.20
• OS: Linux (Linux)
• Assigned to: Ramil Kalimullin
• CPU Architecture: Any

[13 May 2004 9:11] ladimir Kolpakov

Description:
SQL_CALC_FOUND_ROWS/FOUND_ROWS() completely ignores WHERE,
when UNIQUE index exists on table and LIMIT is used.
Result of FOUND_ROWS() becomes correct if LIMIT excluded,
or index dropped.

 

How to repeat:
#!/bin/bash
#   $Id$
#   "SQL_CALC_FOUND_ROWS with UNIQUE col" bug test
#
#--w 05/2004############################################################
 
mysql="mysql -hlocalhost -utest -ptest -tvvv test"
########################################################################
function FOUND_ROWS() {
    cat <<EoD
DROP   TABLE IF EXISTS t1;
CREATE TABLE t1 (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
 ,acc VARCHAR(16)
 ,UNIQUE INDEX acc (acc)
) TYPE = MyISAM;
INSERT INTO t1 (acc) VALUES ('1'),('2'),('3');
 
SELECT SQL_CALC_FOUND_ROWS
       id,acc
  FROM t1
 WHERE acc = '2'
 LIMIT 0,1
;
SELECT FOUND_ROWS();
 
SELECT SQL_CALC_FOUND_ROWS
       id,acc
  FROM t1
 WHERE acc = '2'
;
SELECT FOUND_ROWS();
 
DROP INDEX acc ON t1;
SELECT SQL_CALC_FOUND_ROWS
       id,acc
  FROM t1
 WHERE acc = '2'
 LIMIT 0,1
;
SELECT FOUND_ROWS();
 
EoD
}
 
FOUND_ROWS | $mysql
 
########################################################################
#   $Log$

Suggested fix:
Exclude SQL_CALC_FOUND_ROWS/FOUND_ROWS() from docs until fix,
because it does not make sense without LIMIT.

[13 May 2004 18:32] Dean Ellis

Verified.  Thank you for the report!

[28 May 2004 12:31] Ramil Kalimullin

ChangeSet
  1.1860 04/05/28 15:01:16 [e-mail] +3 -0

[28 May 2004 13:53] Ramil Kalimullin

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html