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

incorrect examined rows in case of stored function usage

    XMLWordPrintable

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

          Activity

            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.