134 lines
5.2 KiB
Plaintext
134 lines
5.2 KiB
Plaintext
#
|
|
# 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 # # Using index
|
|
2 UNION NULL NULL NULL NULL NULL NULL ROWS # # No tables used
|
|
NULL UNION RESULT <union1,2> NULL ALL NULL NULL NULL ROWS # # 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 # # Using index
|
|
2 UNION NULL NULL NULL NULL NULL NULL ROWS # # Impossible WHERE
|
|
NULL UNION RESULT <union1,2> NULL ALL NULL NULL NULL ROWS # # 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;
|