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

Wrong row counter in found_rows() result

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.0.6, 10.0.7
    • 10.0.8
    • None
    • None

    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.

      Attachments

        Issue Links

          relates to

          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-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-7219 SQL_CALC_FOUND_ROWS yields wrong result

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            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.

            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.
            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

            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

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

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.