1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
|
#
# Bug #17059925 : UNIONS COMPUTES ROWS_EXAMINED INCORRECTLY
#
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;
CREATE TABLE t17059925 (a INT, KEY a(a));
CREATE TABLE t2 (b INT);
CREATE TABLE t3 (c INT);
INSERT INTO t17059925 VALUES (1);
INSERT INTO t2 VALUES (4), (5), (6);
INSERT INTO t3 VALUES (7), (8), (9);
TRUNCATE table mysql.slow_log;
SELECT sleep(0.5) FROM t17059925 UNION SELECT * FROM t2 UNION SELECT * FROM t3;
sleep(0.5)
0
4
5
6
7
8
9
SELECT sleep(0.5) FROM t17059925 UNION ALL SELECT * FROM t2 UNION ALL SELECT * FROM t3;
sleep(0.5)
0
4
5
6
7
8
9
CREATE FUNCTION t17059925_func1 (a INT)
RETURNS INT DETERMINISTIC
RETURN a;
SELECT t17059925_func1(1), sleep(0.5);
t17059925_func1(1) sleep(0.5)
1 0
SELECT sleep(0.5) from t17059925 UNION SELECT t17059925_func1(1);
sleep(0.5)
0
1
SELECT * FROM (SELECT t17059925_func1(1)) t WHERE 1= 0 UNION SELECT sleep(0.5);
t17059925_func1(1)
0
SELECT sleep(0.5) FROM t17059925 UNION SELECT * FROM (SELECT t17059925_func1(1)) t WHERE 1= 0 UNION SELECT t17059925_func1(1);
sleep(0.5)
0
1
SELECT 1 FROM dual WHERE 1= 0 UNION SELECT sleep(0.5);
1
0
SELECT sleep(0.5) FROM t17059925 UNION SELECT 1 FROM dual WHERE 1= 0 UNION SELECT * FROM t2;
sleep(0.5)
0
4
5
6
EXPLAIN SELECT t17059925_func1(1);
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL ROWS NULL NULL No tables used
Warnings:
Note 1003 /* select#1 */ select `t17059925_func1`(1) AS `t17059925_func1(1)`
EXPLAIN SELECT * FROM t17059925 UNION SELECT t17059925_func1(1);
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t17059925 NULL index NULL a 5 ROWS 1 100.00 Using index
2 UNION NULL NULL NULL NULL NULL NULL ROWS NULL NULL No tables used
3 UNION RESULT <union1,2> NULL ALL NULL NULL NULL ROWS NULL NULL Using temporary
Warnings:
Note 1003 /* select#1 */ select `test`.`t17059925`.`a` AS `a` from `test`.`t17059925` union /* select#2 */ select `t17059925_func1`(1) AS `t17059925_func1(1)`
SELECT * FROM t17059925 WHERE a= 10 AND a= 20 UNION SELECT sleep(0.5);
a
0
SELECT * FROM t17059925 WHERE a= 10 UNION SELECT sleep(0.5);
a
0
SELECT * FROM (SELECT * FROM t17059925 WHERE a= 10) t WHERE a = 10 UNION SELECT sleep(0.5);
a
0
SELECT sleep(0.5) FROM t17059925 UNION SELECT * FROM t17059925 WHERE a= 10 AND a= 20 UNION SELECT * FROM t2;
sleep(0.5)
0
4
5
6
SELECT sleep(0.5) FROM t17059925 UNION ALL SELECT * FROM t17059925 WHERE a= 10 AND a= 20 UNION ALL SELECT * FROM t2;
sleep(0.5)
0
4
5
6
SELECT sleep(0.5) FROM t17059925 UNION SELECT * FROM (SELECT * FROM t17059925 WHERE a= 10) t WHERE a = 10 UNION SELECT * from t2;
sleep(0.5)
0
4
5
6
EXPLAIN SELECT * FROM t17059925 WHERE a= 10 AND a= 20;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL ROWS NULL NULL Impossible WHERE
Warnings:
Note 1003 /* select#1 */ select `test`.`t17059925`.`a` AS `a` from `test`.`t17059925` where false
EXPLAIN SELECT * FROM t17059925 UNION SELECT * FROM t17059925 WHERE a= 10 AND a= 20;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t17059925 NULL index NULL a 5 ROWS 1 100.00 Using index
2 UNION NULL NULL NULL NULL NULL NULL ROWS NULL NULL Impossible WHERE
3 UNION RESULT <union1,2> NULL ALL NULL NULL NULL ROWS NULL NULL Using temporary
Warnings:
Note 1003 /* select#1 */ select `test`.`t17059925`.`a` AS `a` from `test`.`t17059925` union /* select#2 */ select `test`.`t17059925`.`a` AS `a` from `test`.`t17059925` where false
SELECT sql_text, rows_examined FROM mysql.slow_log WHERE (sql_text LIKE '%SELECT%t17059925%'
AND sql_text NOT LIKE '%EXPLAIN%') OR sql_text LIKE '%dual%';
sql_text rows_examined
SELECT * FROM (SELECT * FROM t17059925 WHERE a= 10) t WHERE a = 10 UNION SELECT sleep(0.5) 2
SELECT * FROM (SELECT t17059925_func1(1)) t WHERE 1= 0 UNION SELECT sleep(0.5) 3
SELECT * FROM t17059925 WHERE a= 10 AND a= 20 UNION SELECT sleep(0.5) 2
SELECT * FROM t17059925 WHERE a= 10 UNION SELECT sleep(0.5) 2
SELECT 1 FROM dual WHERE 1= 0 UNION SELECT sleep(0.5) 2
SELECT sleep(0.5) FROM t17059925 UNION ALL SELECT * FROM t17059925 WHERE a= 10 AND a= 20 UNION ALL SELECT * FROM t2 4
SELECT sleep(0.5) FROM t17059925 UNION ALL SELECT * FROM t2 UNION ALL SELECT * FROM t3 7
SELECT sleep(0.5) FROM t17059925 UNION SELECT * FROM (SELECT * FROM t17059925 WHERE a= 10) t WHERE a = 10 UNION SELECT * from t2 8
SELECT sleep(0.5) FROM t17059925 UNION SELECT * FROM (SELECT t17059925_func1(1)) t WHERE 1= 0 UNION SELECT t17059925_func1(1) 5
SELECT sleep(0.5) FROM t17059925 UNION SELECT * FROM t17059925 WHERE a= 10 AND a= 20 UNION SELECT * FROM t2 8
SELECT sleep(0.5) FROM t17059925 UNION SELECT * FROM t2 UNION SELECT * FROM t3 14
SELECT sleep(0.5) FROM t17059925 UNION SELECT 1 FROM dual WHERE 1= 0 UNION SELECT * FROM t2 8
SELECT sleep(0.5) from t17059925 UNION SELECT t17059925_func1(1) 4
SELECT t17059925_func1(1), sleep(0.5) 1
DROP FUNCTION t17059925_func1;
DROP TABLE t17059925, t2, t3;
SET @@long_query_time= @old_long_query_time;
SET @@global.log_output= @old_log_output;
SET @@global.slow_query_log= @old_slow_query_log;
#
# Bug #18335504 MISSING INFORMATION IN SLOW QUERY LOG FOR SLOW HANDLER STATEMENTS
#
#Setup
CREATE TABLE tbl_18335504(a INT, b INT, KEY i1(a));
INSERT INTO tbl_18335504 VALUES( 30, 1);
INSERT INTO tbl_18335504 VALUES( 20, 2);
INSERT INTO tbl_18335504 VALUES( 10, 3);
SET @old_slow_query_log=@@global.slow_query_log;
SET @old_log_output=@@global.log_output;
SET @old_long_query_time=@@session.long_query_time;
SET GLOBAL slow_query_log='on';
SET GLOBAL log_output='table';
SET SESSION long_query_time=1;
#Execution
HANDLER tbl_18335504 OPEN;
HANDLER tbl_18335504 READ FIRST WHERE b = 2 AND 1 > sleep(2);
a b
20 2
HANDLER tbl_18335504 READ i1 FIRST WHERE a = 20 AND 1 > sleep(2);
a b
20 2
HANDLER tbl_18335504 READ i1 FIRST WHERE a > 10 AND 1 > sleep(2) LIMIT 5;
a b
20 2
30 1
HANDLER tbl_18335504 READ FIRST WHERE b > 1 AND 1 > sleep(2) LIMIT 5;
a b
20 2
10 3
# rows_sent, rows_examined should be non-zero value
SELECT sql_text, rows_sent, rows_examined FROM mysql.slow_log WHERE sql_text LIKE '%tbl_18335504%';
sql_text rows_sent rows_examined
HANDLER tbl_18335504 READ FIRST WHERE b = 2 AND 1 > sleep(2) 1 2
HANDLER tbl_18335504 READ i1 FIRST WHERE a = 20 AND 1 > sleep(2) 1 2
HANDLER tbl_18335504 READ i1 FIRST WHERE a > 10 AND 1 > sleep(2) LIMIT 5 2 3
HANDLER tbl_18335504 READ FIRST WHERE b > 1 AND 1 > sleep(2) LIMIT 5 2 3
HANDLER tbl_18335504 CLOSE;
#Cleanup
DROP TABLE tbl_18335504;
SET @@global.slow_query_log=@old_slow_query_log;
SET @@global.log_output=@old_log_output;
SET @@session.long_query_time=@old_long_query_time;
truncate table mysql.general_log;
truncate table mysql.slow_log;
|
