1# 2# Bug #17059925 : UNIONS COMPUTES ROWS_EXAMINED INCORRECTLY 3# 4SET @old_slow_query_log= @@global.slow_query_log; 5SET @old_log_output= @@global.log_output; 6SET @old_long_query_time= @@long_query_time; 7SET GLOBAL log_output= "TABLE"; 8SET GLOBAL slow_query_log= ON; 9SET SESSION long_query_time= 0; 10CREATE TABLE t17059925 (a INT, KEY a(a)); 11CREATE TABLE t2 (b INT); 12CREATE TABLE t3 (c INT); 13INSERT INTO t17059925 VALUES (1); 14INSERT INTO t2 VALUES (4), (5), (6); 15INSERT INTO t3 VALUES (7), (8), (9); 16TRUNCATE table mysql.slow_log; 17SELECT sleep(0.5) FROM t17059925 UNION SELECT * FROM t2 UNION SELECT * FROM t3; 18sleep(0.5) 190 204 215 226 237 248 259 26SELECT sleep(0.5) FROM t17059925 UNION ALL SELECT * FROM t2 UNION ALL SELECT * FROM t3; 27sleep(0.5) 280 294 305 316 327 338 349 35CREATE FUNCTION t17059925_func1 (a INT) 36RETURNS INT DETERMINISTIC 37RETURN a; 38SELECT t17059925_func1(1), sleep(0.5); 39t17059925_func1(1) sleep(0.5) 401 0 41SELECT sleep(0.5) from t17059925 UNION SELECT t17059925_func1(1); 42sleep(0.5) 430 441 45SELECT * FROM (SELECT t17059925_func1(1)) t WHERE 1= 0 UNION SELECT sleep(0.5); 46t17059925_func1(1) 470 48SELECT sleep(0.5) FROM t17059925 UNION SELECT * FROM (SELECT t17059925_func1(1)) t WHERE 1= 0 UNION SELECT t17059925_func1(1); 49sleep(0.5) 500 511 52SELECT 1 FROM dual WHERE 1= 0 UNION SELECT sleep(0.5); 531 540 55SELECT sleep(0.5) FROM t17059925 UNION SELECT 1 FROM dual WHERE 1= 0 UNION SELECT * FROM t2; 56sleep(0.5) 570 584 595 606 61EXPLAIN SELECT t17059925_func1(1); 62id select_type table partitions type possible_keys key key_len ref rows filtered Extra 631 SIMPLE NULL NULL NULL NULL NULL NULL ROWS NULL NULL No tables used 64Warnings: 65Note 1003 /* select#1 */ select `t17059925_func1`(1) AS `t17059925_func1(1)` 66EXPLAIN SELECT * FROM t17059925 UNION SELECT t17059925_func1(1); 67id select_type table partitions type possible_keys key key_len ref rows filtered Extra 681 PRIMARY t17059925 NULL index NULL a 5 ROWS 1 100.00 Using index 692 UNION NULL NULL NULL NULL NULL NULL ROWS NULL NULL No tables used 70NULL UNION RESULT <union1,2> NULL ALL NULL NULL NULL ROWS NULL NULL Using temporary 71Warnings: 72Note 1003 /* select#1 */ select `test`.`t17059925`.`a` AS `a` from `test`.`t17059925` union /* select#2 */ select `t17059925_func1`(1) AS `t17059925_func1(1)` 73SELECT * FROM t17059925 WHERE a= 10 AND a= 20 UNION SELECT sleep(0.5); 74a 750 76SELECT * FROM t17059925 WHERE a= 10 UNION SELECT sleep(0.5); 77a 780 79SELECT * FROM (SELECT * FROM t17059925 WHERE a= 10) t WHERE a = 10 UNION SELECT sleep(0.5); 80a 810 82SELECT sleep(0.5) FROM t17059925 UNION SELECT * FROM t17059925 WHERE a= 10 AND a= 20 UNION SELECT * FROM t2; 83sleep(0.5) 840 854 865 876 88SELECT sleep(0.5) FROM t17059925 UNION ALL SELECT * FROM t17059925 WHERE a= 10 AND a= 20 UNION ALL SELECT * FROM t2; 89sleep(0.5) 900 914 925 936 94SELECT sleep(0.5) FROM t17059925 UNION SELECT * FROM (SELECT * FROM t17059925 WHERE a= 10) t WHERE a = 10 UNION SELECT * from t2; 95sleep(0.5) 960 974 985 996 100EXPLAIN SELECT * FROM t17059925 WHERE a= 10 AND a= 20; 101id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1021 SIMPLE NULL NULL NULL NULL NULL NULL ROWS NULL NULL Impossible WHERE 103Warnings: 104Note 1003 /* select#1 */ select `test`.`t17059925`.`a` AS `a` from `test`.`t17059925` where false 105EXPLAIN SELECT * FROM t17059925 UNION SELECT * FROM t17059925 WHERE a= 10 AND a= 20; 106id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1071 PRIMARY t17059925 NULL index NULL a 5 ROWS 1 100.00 Using index 1082 UNION NULL NULL NULL NULL NULL NULL ROWS NULL NULL Impossible WHERE 109NULL UNION RESULT <union1,2> NULL ALL NULL NULL NULL ROWS NULL NULL Using temporary 110Warnings: 111Note 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 112SELECT sql_text, rows_examined FROM mysql.slow_log WHERE (sql_text LIKE '%SELECT%t17059925%' 113 AND sql_text NOT LIKE '%EXPLAIN%') OR sql_text LIKE '%dual%'; 114sql_text rows_examined 115SELECT * FROM (SELECT * FROM t17059925 WHERE a= 10) t WHERE a = 10 UNION SELECT sleep(0.5) 2 116SELECT * FROM (SELECT t17059925_func1(1)) t WHERE 1= 0 UNION SELECT sleep(0.5) 3 117SELECT * FROM t17059925 WHERE a= 10 AND a= 20 UNION SELECT sleep(0.5) 2 118SELECT * FROM t17059925 WHERE a= 10 UNION SELECT sleep(0.5) 2 119SELECT 1 FROM dual WHERE 1= 0 UNION SELECT sleep(0.5) 2 120SELECT sleep(0.5) FROM t17059925 UNION ALL SELECT * FROM t17059925 WHERE a= 10 AND a= 20 UNION ALL SELECT * FROM t2 4 121SELECT sleep(0.5) FROM t17059925 UNION ALL SELECT * FROM t2 UNION ALL SELECT * FROM t3 7 122SELECT sleep(0.5) FROM t17059925 UNION SELECT * FROM (SELECT * FROM t17059925 WHERE a= 10) t WHERE a = 10 UNION SELECT * from t2 8 123SELECT sleep(0.5) FROM t17059925 UNION SELECT * FROM (SELECT t17059925_func1(1)) t WHERE 1= 0 UNION SELECT t17059925_func1(1) 5 124SELECT sleep(0.5) FROM t17059925 UNION SELECT * FROM t17059925 WHERE a= 10 AND a= 20 UNION SELECT * FROM t2 8 125SELECT sleep(0.5) FROM t17059925 UNION SELECT * FROM t2 UNION SELECT * FROM t3 14 126SELECT sleep(0.5) FROM t17059925 UNION SELECT 1 FROM dual WHERE 1= 0 UNION SELECT * FROM t2 8 127SELECT sleep(0.5) from t17059925 UNION SELECT t17059925_func1(1) 4 128SELECT t17059925_func1(1), sleep(0.5) 1 129DROP FUNCTION t17059925_func1; 130DROP TABLE t17059925, t2, t3; 131SET @@long_query_time= @old_long_query_time; 132SET @@global.log_output= @old_log_output; 133SET @@global.slow_query_log= @old_slow_query_log; 134# 135# Bug #18335504 MISSING INFORMATION IN SLOW QUERY LOG FOR SLOW HANDLER STATEMENTS 136# 137#Setup 138CREATE TABLE tbl_18335504(a INT, b INT, KEY i1(a)); 139INSERT INTO tbl_18335504 VALUES( 30, 1); 140INSERT INTO tbl_18335504 VALUES( 20, 2); 141INSERT INTO tbl_18335504 VALUES( 10, 3); 142SET @old_slow_query_log=@@global.slow_query_log; 143SET @old_log_output=@@global.log_output; 144SET @old_long_query_time=@@session.long_query_time; 145SET GLOBAL slow_query_log='on'; 146SET GLOBAL log_output='table'; 147SET SESSION long_query_time=1; 148#Execution 149HANDLER tbl_18335504 OPEN; 150HANDLER tbl_18335504 READ FIRST WHERE b = 2 AND 1 > sleep(2); 151a b 15220 2 153HANDLER tbl_18335504 READ i1 FIRST WHERE a = 20 AND 1 > sleep(2); 154a b 15520 2 156HANDLER tbl_18335504 READ i1 FIRST WHERE a > 10 AND 1 > sleep(2) LIMIT 5; 157a b 15820 2 15930 1 160HANDLER tbl_18335504 READ FIRST WHERE b > 1 AND 1 > sleep(2) LIMIT 5; 161a b 16220 2 16310 3 164# rows_sent, rows_examined should be non-zero value 165SELECT sql_text, rows_sent, rows_examined FROM mysql.slow_log WHERE sql_text LIKE '%tbl_18335504%'; 166sql_text rows_sent rows_examined 167HANDLER tbl_18335504 READ FIRST WHERE b = 2 AND 1 > sleep(2) 1 2 168HANDLER tbl_18335504 READ i1 FIRST WHERE a = 20 AND 1 > sleep(2) 1 2 169HANDLER tbl_18335504 READ i1 FIRST WHERE a > 10 AND 1 > sleep(2) LIMIT 5 2 3 170HANDLER tbl_18335504 READ FIRST WHERE b > 1 AND 1 > sleep(2) LIMIT 5 2 3 171HANDLER tbl_18335504 CLOSE; 172#Cleanup 173DROP TABLE tbl_18335504; 174SET @@global.slow_query_log=@old_slow_query_log; 175SET @@global.log_output=@old_log_output; 176SET @@session.long_query_time=@old_long_query_time; 177