Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-5549

Wrong row counter in found_rows() result

        Details

        • Type: Bug
        • Status: Closed
        • Priority: Major
        • Resolution: Fixed
        • Affects Version/s: 10.0.6, 10.0.7
        • Fix Version/s: 10.0.8
        • Component/s: None
        • Labels:
          None
        • Environment:

          Description

          Hi!

          I'v tested the following query:

          mysql> select version();
+-------------------------+
| version()               |
+-------------------------+
| 10.0.6-MariaDB-1~wheezy |
+-------------------------+
1 row in set (0.00 sec)
 
mysql>

mysql> select SQL_CALC_FOUND_ROWS a.alkid from testa a,testb b where a.alkid=b.alkid and a.csoportid=3 and b.nev like'%4000%'order by a.partnumber asc limit 0,20;select found_rows();
+-------+
| alkid |
+-------+
| 33445 |
| 34060 |
| 35587 |
| 34089 |
| 34088 |
| 32390 |
| 34097 |
| 35763 |
| 14846 |
+-------+
9 rows in set (0.01 sec)
 
+--------------+
| found_rows() |
+--------------+
|          887 |
+--------------+
1 row in set (0.00 sec)
 
mysql>

          The found rows result should be 9, not 887.

          I attached the database dump.

            Gliffy Diagrams

              Attachments

                Issue Links

                relates to

                Bug - A problem which impairs or prevents the functions of the product. MDEV-6221 SQL_CALC_FOUND_ROWS yields wrong result again

                • Critical - Crashes, loss of data, severe memory leak.
                • Closed

                Bug - A problem which impairs or prevents the functions of the product. MDEV-5898 FOUND_ROWS() return incorrect value when using DISTINCT

                • Major - Major loss of function.
                • Closed

                Bug - A problem which impairs or prevents the functions of the product. MDEV-7219 SQL_CALC_FOUND_ROWS yields wrong result

                • Major - Major loss of function.
                • Closed

                  Activity

                  Ascending order - Click to sort in descending order
                  Hide
                  Permalink
                  elenst Elena Stepanova added a comment -

                  Smaller test case:

                  DROP TABLE IF EXISTS testa;
CREATE TABLE testa (
  alkid INT NOT NULL AUTO_INCREMENT,
  csoportid tinyint(3) DEFAULT NULL,
  PRIMARY KEY (alkid)
) ENGINE=MyISAM;

INSERT INTO testa VALUES (10,3),(11,2),(12,3);

DROP TABLE IF EXISTS testb;
CREATE TABLE testb (alkid INT PRIMARY KEY) ENGINE=MyISAM;

INSERT INTO `testb` VALUES (11),(12),(13);

select a.alkid from testa a,testb b where a.alkid=b.alkid and a.csoportid=3 order by a.alkid;
select found_rows();

DROP TABLE testa, testb;

                  Actual result:

                  select a.alkid from testa a,testb b where a.alkid=b.alkid and a.csoportid=3 order by a.alkid;
alkid
12
select found_rows();
found_rows()
2

                  With so few rows, the test case shows the problem only on MyISAM tables. With somewhat more rows, same happens with InnoDB/XtraDB as well.
                  Not reproducible on 5.5.
                  Not reproducible on MySQL 5.6.

                  Show
                  elenst Elena Stepanova added a comment - Smaller test case: DROP TABLE IF EXISTS testa; CREATE TABLE testa ( alkid INT NOT NULL AUTO_INCREMENT, csoportid tinyint(3) DEFAULT NULL, PRIMARY KEY (alkid) ) ENGINE=MyISAM; INSERT INTO testa VALUES (10,3),(11,2),(12,3); DROP TABLE IF EXISTS testb; CREATE TABLE testb (alkid INT PRIMARY KEY) ENGINE=MyISAM; INSERT INTO `testb` VALUES (11),(12),(13); select a.alkid from testa a,testb b where a.alkid=b.alkid and a.csoportid=3 order by a.alkid; select found_rows(); DROP TABLE testa, testb; Actual result: select a.alkid from testa a,testb b where a.alkid=b.alkid and a.csoportid=3 order by a.alkid; alkid 12 select found_rows(); found_rows() 2 With so few rows, the test case shows the problem only on MyISAM tables. With somewhat more rows, same happens with InnoDB/XtraDB as well. Not reproducible on 5.5. Not reproducible on MySQL 5.6.
                  Hide
                  Permalink
                  sebres Serg G. Brester added a comment -

                  New "test case" example was added to MDEV-4711, what proofs that this is not completely fixed.
                  See comment 49501

                  Show
                  sebres Serg G. Brester added a comment - New "test case" example was added to MDEV-4711 , what proofs that this is not completely fixed. See comment 49501

                    People

                    • Assignee:
                      serg Sergei Golubchik
                      Reporter:
                      LaySoft Lay András
                    • Votes:
                      0 Vote for this issue
                      Watchers:
                      5 Start watching this issue

                      Dates

                      • Created:
                        Updated:
                        Resolved:

                        Time Tracking

                        Estimated:
                        Original Estimate - Not Specified
                        Not Specified
                        Remaining:
                        Remaining Estimate - 0 minutes
                        0m
                        Logged:
                        Time Spent - 1 hour, 30 minutes
                        1h 30m