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

incorrect examined rows in case of stored function usage

Details

    Description

      Both SSELECTs should produce the same (or similar) examined rows numer but it is 95179 vs 17716591112119384090

      CREATE TABLE `tab_MDEV_30820` (
      		 
      `ID` int(11) NOT NULL AUTO_INCREMENT,
      		 
      `NAME_F` varchar(50) DEFAULT NULL,
      		 
        PRIMARY KEY (`ID`)
      		 
      );
      		 
       
      		 
       CREATE TABLE `tab2` (
      		 
        `ID` int(11) NOT NULL AUTO_INCREMENT,
      		 
        `TAB1_ID` int(11) DEFAULT NULL,
      		 
        PRIMARY KEY (`id`)
      		 
      );
      		 
       
      		 
      --delimiter //
      		 
      CREATE FUNCTION `get_zero`() RETURNS int(11)
      		 
      BEGIN
      		 
          RETURN(0) ;
      		 
      END
      		 
      //
      		 
       
      		 
      for i in 1..100 do insert into tab_MDEV_30820 values (null,'qwerty'); end for ; //
      		 
      for i in 1..1000 do insert into tab2 values (null,round(rand()*10000)); end for ; //
      		 
       
      		 
      --delimiter ;
      		 
       
      		 
      SET @old_slow_query_log= @@global.slow_query_log;
      		 
      SET @old_log_output= @@global.log_output;
      		 
      SET @old_long_query_time= @@long_query_time;
      		 
      SET GLOBAL log_output= "TABLE";
      		 
      SET GLOBAL slow_query_log= ON;
      		 
      SET SESSION long_query_time= 0;
      		 
       
      		 
      SELECT 0 as zero, (SELECT ID FROM tab2 where tab2.TAB1_ID = tab_MDEV_30820.ID LIMIT 1) AS F1 FROM tab_MDEV_30820 ORDER BY 2 DESC LIMIT 2;
      		 
       
      		 
      SELECT get_zero() as zero, (SELECT ID FROM tab2 where tab2.TAB1_ID = tab_MDEV_30820.ID LIMIT 1) AS F1 FROM tab_MDEV_30820 ORDER BY 2 DESC LIMIT 2;
      		 
       
      		 
      SELECT rows_examined FROM mysql.slow_log WHERE sql_text LIKE '%SELECT%tab_MDEV_30820%';
      		 
       
      		 
      ## Reset to initial values
      		 
      SET @@long_query_time= @old_long_query_time;
      		 
      SET @@global.log_output= @old_log_output;
      		 
      SET @@global.slow_query_log= @old_slow_query_log;
      		 
       
      		 
      drop table tab_MDEV_30820, tab2;
      		 
      drop function get_zero;

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30820 slow log Rows_examined out of range

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

          Activity

            Ascending order - Click to sort in descending order
            sanja Oleksandr Byelkin added a comment - 

            commit 64ac6ed3e357af743c70ea1d6ddf8fb699d51589 (HEAD -> bb-10.4-MDEV-30820, origin/bb-10.4-MDEV-30820)
            		 
            Author: Oleksandr Byelkin <sanja@mariadb.com>
            		 
            Date:   Wed Jul 19 16:14:39 2023 +0200
            		 
             
            		 
                MDEV-31742 incorrect examined rows in case of stored function usage
            		 
                
                The counter is global so we do not need add backup to it
            		 
                if we do not zero it after taking the backup.

            sanja Oleksandr Byelkin added a comment - commit 64ac6ed3e357af743c70ea1d6ddf8fb699d51589 (HEAD -> bb-10.4-MDEV-30820, origin/bb-10.4-MDEV-30820) Author: Oleksandr Byelkin <sanja@mariadb.com> Date: Wed Jul 19 16:14:39 2023 +0200   MDEV-31742 incorrect examined rows in case of stored function usage The counter is global so we do not need add backup to it if we do not zero it after taking the backup.
            lstartseva Lena Startseva added a comment -

            sanja, plz, fix test main.log_slow, it fails with view-protocol:

            main.log_slow                            w4 [ fail ]
            		 
                    Test ended at 2023-09-27 04:39:30
            		 
            CURRENT_TEST: main.log_slow
            		 
            --- /home/buildbot/amd64-ubuntu-2004-fulltest/build/mysql-test/main/log_slow.result	2023-09-27 03:32:01.000000000 +0000
            		 
            +++ /home/buildbot/amd64-ubuntu-2004-fulltest/build/mysql-test/main/log_slow.reject	2023-09-27 04:39:30.112633891 +0000
            		 
            @@ -173,8 +173,6 @@
            		 
             # should be the same rows_examined
            		 
             SELECT rows_examined FROM mysql.slow_log WHERE sql_text LIKE '%SELECT%tab_MDEV_30820%';
            		 
             rows_examined
            		 
            -100202
            		 
            -100202
            		 
             SET @@long_query_time= @old_long_query_time;
            		 
             SET @@global.log_output= @old_log_output;
            		 
             SET @@global.slow_query_log= @old_slow_query_log;
            		 
            mysqltest: Result length mismatch

            lstartseva Lena Startseva added a comment - sanja , plz, fix test main.log_slow, it fails with view-protocol: main.log_slow w4 [ fail ] Test ended at 2023-09-27 04:39:30 CURRENT_TEST: main.log_slow --- /home/buildbot/amd64-ubuntu-2004-fulltest/build/mysql-test/main/log_slow.result 2023-09-27 03:32:01.000000000 +0000 +++ /home/buildbot/amd64-ubuntu-2004-fulltest/build/mysql-test/main/log_slow.reject 2023-09-27 04:39:30.112633891 +0000 @@ -173,8 +173,6 @@ # should be the same rows_examined SELECT rows_examined FROM mysql.slow_log WHERE sql_text LIKE '%SELECT%tab_MDEV_30820%' ; rows_examined -100202 -100202 SET @@long_query_time= @old_long_query_time; SET @@ global .log_output= @old_log_output; SET @@ global .slow_query_log= @old_slow_query_log; mysqltest: Result length mismatch
            sanja Oleksandr Byelkin added a comment -

            fixed

            sanja Oleksandr Byelkin added a comment - fixed

            People

              sanja Oleksandr Byelkin
              sanja Oleksandr Byelkin
              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.