[MDEV-31522] Cursor protocol returns rows_sent=0 from mysql.slow_log (v. 10.4, 10.5) and from v. 10.6 empty result for all colums Created: 2023-06-22  Updated: 2023-11-28

Status: Open
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.4, 10.5, 10.6, 10.9, 10.10, 11.0, 11.1, 11.2
Fix Version/s: 10.4, 10.5, 10.6, 11.0, 11.1, 11.2

Type: Bug Priority: Major
Reporter: Lena Startseva Assignee: Oleksandr Byelkin
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-31005 Make working cursor-protocol Stalled

 Description   

Information is incorrectly written when executing queries "SELECT " from table t1.

Testcase:
Options for MTR test (.opt file):

--log-output=table,file --slow-query-log --general-log

Case:

SET SESSION long_query_time = 0;
 
SET GLOBAL slow_query_log = ON;
 
FLUSH LOGS;
 
TRUNCATE TABLE mysql.slow_log;
 
 
 
# Let there be three columns, unique, non-unique, and non-indexed:
 
CREATE TABLE t1 (f1 SERIAL,f2 INT, f3 INT, PRIMARY KEY(f1), KEY(f2));
 
INSERT INTO t1 VALUES (1,1,1);
 
INSERT INTO t1 VALUES (2,2,2);
 
INSERT INTO t1 VALUES (3,3,3);
 
INSERT INTO t1 VALUES (4,4,4);
 
 
 
SELECT SQL_NO_CACHE 'Bug#31700 - SCAN',f1,f2,f3,SLEEP(1.1) FROM t1 WHERE f3=4;
 
SELECT SQL_NO_CACHE 'Bug#31700 - KEY', f1,f2,f3,SLEEP(1.1) FROM t1 WHERE f2=3;
 
SELECT SQL_NO_CACHE 'Bug#31700 - PK',  f1,f2,f3,SLEEP(1.1) FROM t1 WHERE f1=2;
 
 
 
--replace_column 1 TIMESTAMP
 
SELECT start_time, rows_examined, rows_sent, sql_text FROM mysql.slow_log WHERE sql_text LIKE '%Bug#31700%' ORDER BY start_time;
 
 
 
DROP TABLE t1;
 
 
 
TRUNCATE TABLE mysql.slow_log;

Expected result:

start_time	rows_examined	rows_sent	sql_text
 
TIMESTAMP	4	1	SELECT SQL_NO_CACHE 'Bug#31700 - SCAN',f1,f2,f3,SLEEP(1.1) FROM t1 WHERE f3=4
 
TIMESTAMP	1	1	SELECT SQL_NO_CACHE 'Bug#31700 - KEY', f1,f2,f3,SLEEP(1.1) FROM t1 WHERE f2=3
 
TIMESTAMP	1	1	SELECT SQL_NO_CACHE 'Bug#31700 - PK',  f1,f2,f3,SLEEP(1.1) FROM t1 WHERE f1=2

Actual result (v. 10.4, 10.4):

start_time	rows_examined	rows_sent	sql_text
 
TIMESTAMP	4	0	SELECT SQL_NO_CACHE 'Bug#31700 - SCAN',f1,f2,f3,SLEEP(1.1) FROM t1 WHERE f3=4
 
TIMESTAMP	1	0	SELECT SQL_NO_CACHE 'Bug#31700 - KEY', f1,f2,f3,SLEEP(1.1) FROM t1 WHERE f2=3
 
TIMESTAMP	1	0	SELECT SQL_NO_CACHE 'Bug#31700 - PK',  f1,f2,f3,SLEEP(1.1) FROM t1 WHERE f1=2

Actual result (from v.10.6):

start_time	rows_examined	rows_sent	sql_text



 Comments   
Comment by Lena Startseva [ 2023-06-23 ]

The same problem with found_rows() and aggregate function:

Case:

CREATE TABLE t1 (a int);
 
INSERT INTO t1 VALUES (1), (2), (3);
 
select count(*) from t1;
 
select found_rows();
 
DROP TABLE t1;

Expected result:

found_rows()
 
1

Actual result: (with --cursor-protocol)

found_rows()
 
0

Generated at Thu Feb 08 10:24:30 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.