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

SQL_CALC_FOUND_ROWS yields wrong result

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.0.12, 10.0.15
    • 10.0.16
    • Optimizer
    • None
    • CentOS 6.6 x64

    Description

      SQL_CALC_FOUND_ROWS returns various results depending on table definition, KEY and ORDER BY.

      Attachments

        Issue Links

          relates to

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

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-5549 Wrong row counter in found_rows() result

          • Major - Major loss of function.
          • 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-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-6230 Error (too many FOUND_ROWS) for query when using order by

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

          Activity

            elenst Elena Stepanova added a comment -

            The problem (re-?)appeared on the 10.0 tree with this revision:

            revno: 4226
            		 
            revision-id: sergii@pisem.net-20140605135941-ytyt69i971b3syle
            		 
            parent: sergii@pisem.net-20140605135935-vlj5qhr4yr8lp2fn
            		 
            committer: Sergei Golubchik <sergii@pisem.net>
            		 
            branch nick: 10.0
            		 
            timestamp: Thu 2014-06-05 15:59:41 +0200
            		 
            message:
            		 
              revert the fix for MDEV-5898, restore the fix for MDEV-5549.
            		 
              simplify test case for MDEV-5898

            Test case (exactly the same as in the attachment, just made it MTR-like). For all SELECT FOUND_ROWS() queries the expected result is 75.

            DROP DATABASE IF EXISTS bugtest;
            		 
            CREATE DATABASE bugtest;
            		 
            USE bugtest;
            		 
             
            		 
            --delimiter //
            		 
             
            		 
              CREATE PROCEDURE fill_bugtable (len INT)
            		 
              BEGIN
            		 
                DECLARE i INT DEFAULT 1;
            		 
                WHILE i <= len DO
            		 
                  INSERT INTO bugtable (bugtable_varchar, bugtable_int) VALUES ("foo", i%2);
            		 
                  SET i = i + 1;
            		 
                END WHILE;
            		 
              END //
            		 
             
            		 
            --delimiter ;
            		 
             
            		 
            --echo # Tests to reproduce Bug #1 ------------------------------------------------
            		 
             
            		 
            CREATE TABLE `bugtable` (
            		 
              `bugtable_int` int,
            		 
              `bugtable_varchar` varchar(64),
            		 
              KEY (`bugtable_int`)
            		 
            ) ENGINE=MyISAM CHARSET=latin1 COLLATE=latin1_general_ci;
            		 
             
            		 
            CALL fill_bugtable(150);
            		 
             
            		 
            SELECT SQL_CALC_FOUND_ROWS * FROM bugtable WHERE `bugtable_int` = 0 ORDER BY bugtable_varchar LIMIT 59,2;
            		 
            SELECT FOUND_ROWS();
            		 
             
            		 
            SELECT SQL_CALC_FOUND_ROWS * FROM bugtable WHERE `bugtable_int` = 0 ORDER BY bugtable_varchar LIMIT 60,2;
            		 
            SELECT FOUND_ROWS();
            		 
             
            		 
            --echo # Tests to reproduce Bug #2 ------------------------------------------------
            		 
             
            		 
            ALTER TABLE `bugtable` DROP INDEX `bugtable_int`;
            		 
             
            		 
            SELECT SQL_CALC_FOUND_ROWS * FROM bugtable WHERE `bugtable_int` = 0 ORDER BY bugtable_varchar LIMIT 59,2;
            		 
            SELECT FOUND_ROWS();
            		 
             
            		 
            SELECT SQL_CALC_FOUND_ROWS * FROM bugtable WHERE `bugtable_int` = 0 ORDER BY bugtable_varchar LIMIT 60,2;
            		 
            SELECT FOUND_ROWS();
            		 
             
            		 
            --echo # Tests to reproduce Bug #3 ------------------------------------------------
            		 
             
            		 
            DROP TABLE `bugtable`;
            		 
            CREATE TABLE `bugtable` (
            		 
              `bugtable_int` int,
            		 
              `bugtable_varchar` varchar(64),
            		 
              KEY (`bugtable_int`)
            		 
            ) ENGINE=MyISAM CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
            		 
             
            		 
            CALL fill_bugtable(150);
            		 
             
            		 
            SELECT SQL_CALC_FOUND_ROWS * FROM bugtable WHERE `bugtable_int` = 0 ORDER BY bugtable_varchar LIMIT 59,2;
            		 
            SELECT FOUND_ROWS();
            		 
             
            		 
            SELECT SQL_CALC_FOUND_ROWS * FROM bugtable WHERE `bugtable_int` = 0 ORDER BY bugtable_varchar LIMIT 60,2;
            		 
            SELECT FOUND_ROWS();
            		 
             
            		 
            --echo # Tests to reproduce Bug #4 ------------------------------------------------
            		 
             
            		 
            ALTER TABLE `bugtable` DROP INDEX `bugtable_int`;
            		 
             
            		 
            SELECT SQL_CALC_FOUND_ROWS * FROM bugtable WHERE `bugtable_int` = 0 ORDER BY bugtable_varchar LIMIT 59,2;
            		 
            SELECT FOUND_ROWS();
            		 
             
            		 
            SELECT SQL_CALC_FOUND_ROWS * FROM bugtable WHERE `bugtable_int` = 0 ORDER BY bugtable_varchar LIMIT 60,2;
            		 
            SELECT FOUND_ROWS();
            		 
             
            		 
            drop database bugtest;

            elenst Elena Stepanova added a comment - The problem (re-?)appeared on the 10.0 tree with this revision: revno: 4226 revision-id: sergii@pisem.net-20140605135941-ytyt69i971b3syle parent: sergii@pisem.net-20140605135935-vlj5qhr4yr8lp2fn committer: Sergei Golubchik <sergii@pisem.net> branch nick: 10.0 timestamp: Thu 2014-06-05 15:59:41 +0200 message: revert the fix for MDEV-5898, restore the fix for MDEV-5549. simplify test case for MDEV-5898 Test case (exactly the same as in the attachment, just made it MTR-like). For all SELECT FOUND_ROWS() queries the expected result is 75 . DROP DATABASE IF EXISTS bugtest; CREATE DATABASE bugtest; USE bugtest;   --delimiter //   CREATE PROCEDURE fill_bugtable (len INT ) BEGIN DECLARE i INT DEFAULT 1; WHILE i <= len DO INSERT INTO bugtable (bugtable_varchar, bugtable_int) VALUES ( "foo" , i%2); SET i = i + 1; END WHILE; END //   --delimiter ;   --echo # Tests to reproduce Bug #1 ------------------------------------------------   CREATE TABLE `bugtable` ( `bugtable_int` int , `bugtable_varchar` varchar (64), KEY (`bugtable_int`) ) ENGINE=MyISAM CHARSET=latin1 COLLATE =latin1_general_ci;   CALL fill_bugtable(150);   SELECT SQL_CALC_FOUND_ROWS * FROM bugtable WHERE `bugtable_int` = 0 ORDER BY bugtable_varchar LIMIT 59,2; SELECT FOUND_ROWS();   SELECT SQL_CALC_FOUND_ROWS * FROM bugtable WHERE `bugtable_int` = 0 ORDER BY bugtable_varchar LIMIT 60,2; SELECT FOUND_ROWS();   --echo # Tests to reproduce Bug #2 ------------------------------------------------   ALTER TABLE `bugtable` DROP INDEX `bugtable_int`;   SELECT SQL_CALC_FOUND_ROWS * FROM bugtable WHERE `bugtable_int` = 0 ORDER BY bugtable_varchar LIMIT 59,2; SELECT FOUND_ROWS();   SELECT SQL_CALC_FOUND_ROWS * FROM bugtable WHERE `bugtable_int` = 0 ORDER BY bugtable_varchar LIMIT 60,2; SELECT FOUND_ROWS();   --echo # Tests to reproduce Bug #3 ------------------------------------------------   DROP TABLE `bugtable`; CREATE TABLE `bugtable` ( `bugtable_int` int , `bugtable_varchar` varchar (64), KEY (`bugtable_int`) ) ENGINE=MyISAM CHARSET=utf8mb4 COLLATE =utf8mb4_unicode_ci;   CALL fill_bugtable(150);   SELECT SQL_CALC_FOUND_ROWS * FROM bugtable WHERE `bugtable_int` = 0 ORDER BY bugtable_varchar LIMIT 59,2; SELECT FOUND_ROWS();   SELECT SQL_CALC_FOUND_ROWS * FROM bugtable WHERE `bugtable_int` = 0 ORDER BY bugtable_varchar LIMIT 60,2; SELECT FOUND_ROWS();   --echo # Tests to reproduce Bug #4 ------------------------------------------------   ALTER TABLE `bugtable` DROP INDEX `bugtable_int`;   SELECT SQL_CALC_FOUND_ROWS * FROM bugtable WHERE `bugtable_int` = 0 ORDER BY bugtable_varchar LIMIT 59,2; SELECT FOUND_ROWS();   SELECT SQL_CALC_FOUND_ROWS * FROM bugtable WHERE `bugtable_int` = 0 ORDER BY bugtable_varchar LIMIT 60,2; SELECT FOUND_ROWS();   drop database bugtest;

            People

              serg Sergei Golubchik
              lovette Lance
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.