[MariaDB developers] Re: 845fbe9ce01: MDEV-30820 slow log Rows_examined out of range

18 Jul 2023

Hi, Oleksandr,

But is it all necessary to show the bug? may be a test case could be
much simpler?

On Jul 17, Oleksandr Byelkin wrote:
...
Hi!
Sergei Golubchik  schrieb am Mo., 17. Juli 2023, 13:11:
...
Hi, Sanja,
The patch is ok, but the test case is rather weird.
What does it do?
Magic (I  don't digged deep, because it is very fast).
...
How long does it run?
As I told very fast, less then a second.
...
On Jul 17, Oleksandr Byelkin wrote:
...
revision-id: 845fbe9ce01 (mariadb-10.4.30-30-g845fbe9ce01)
parent(s): 02cd3675c4d
author: Oleksandr Byelkin
committer: Oleksandr Byelkin
timestamp: 2023-07-17 10:22:54 +0200
message:
MDEV-30820 slow log Rows_examined out of range
Fix row counters to be able to get any possible value.

diff --git a/mysql-test/main/log_slow.test b/mysql-test/main/log_slow.test
--- a/mysql-test/main/log_slow.test
+++ b/mysql-test/main/log_slow.test
@@ -119,3 +119,98 @@ drop table t;
 --echo #
 --echo # End of 10.3 tests
 --echo #
+
+
+--echo #
+--echo # MDEV-30820: slow log Rows_examined out of range
+--echo #
+
+set @log_output.save= @@global.log_output, @slow_log.save= @@global.slow_query_log;
+set global log_output= 'TABLE', slow_query_log= ON;
+set long_query_time= 0.000001;
+create database db;
+use db;
+
+--delimiter //
+CREATE OR REPLACE FUNCTION `get_id`(`INPUT_STRING` VARCHAR(50), `DECRYPT_KEY` CHAR(20)) RETURNS int(11)
+BEGIN
+    DECLARE
+        REQUEST_ID INT ;
+    SET
+        REQUEST_ID =
+                IF(
+            (
+                LOCATE('#$#$#$ LHP_ID_', INPUT_STRING)
+            ) > 0 AND
+            (
+                INPUT_STRING REGEXP DECRYPT_KEY
+            )>0,
+            (
+                SPLIT_STRING(
+                    SUBSTRING_INDEX(
+                        SUBSTRING_INDEX(INPUT_STRING, '#$#$#$ LHP_ID_', -1),
+                        '#$#$#$',
+                        1
+                    ),
+                    '-',
+                    2
+                )
+        ),
+        NULL
+        ) ;
+                SET
+                REQUEST_ID=IF(
+                ISNULL(REQUEST_ID)OR(REQUEST_ID='')OR(REQUEST_ID=0),
+                NULL,
+                REQUEST_ID
+
+                );
+                RETURN(REQUEST_ID) ;
+END
+//
+
+CREATE OR REPLACE FUNCTION `SPLIT_STRING`(`str` VARCHAR(255), `delim` VARCHAR(12), `pos` INT) RETURNS varchar(255) CHARSET utf8mb4
+RETURN REPLACE(
+        SUBSTRING(
+                SUBSTRING_INDEX(str , delim , pos) ,
+                CHAR_LENGTH(
+                        SUBSTRING_INDEX(str , delim , pos - 1)
+                ) + 1
+        ) ,
+        delim ,
+        ''
+)
+//
+
+CREATE TABLE `tab1` (
+`ID` int(11) NOT NULL AUTO_INCREMENT,
+`NAME_F` varchar(50) DEFAULT NULL,
+  PRIMARY KEY (`ID`)
+) DEFAULT CHARSET=utf8mb4 //
+
+ CREATE TABLE `tab2` (
+  `ID` int(11) NOT NULL AUTO_INCREMENT,
+  `TAB1_ID` int(11) DEFAULT NULL,
+  PRIMARY KEY (`id`)
+) DEFAULT CHARSET=utf8mb4 //
+
+for i in 1..100 do insert into tab1 values (null,'qwerty'); end for ; //
+for i in 1..1000 do insert into tab2 values (null,round(rand()*10000)); end for ; //
+
+--delimiter ;
+
+SELECT
+    get_id(CAST(aes_decrypt(tab1.NAME_F,'V41iNM0n4') AS char),'KM_ID_PL') as get_string,
+    (CASE WHEN (SELECT ID FROM tab2 where tab2.TAB1_ID = tab1.ID LIMIT 1) IS NULL THEN 0 ELSE 1 END) AS IS_ATT
+FROM
+    tab1
+ORDER BY 2 DESC
+LIMIT 2;
+
+set global log_output= @log_output.save, slow_query_log= @slow_log.save;
+drop database db;
+
+--echo #
+--echo # End of 10.4 tests
+--echo #
Regards,
Sergei
VP of MariaDB Server Engineering
and security@mariadb.org

    