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
|
--source include/elide_costs.inc
--echo #
--echo # Bug 20219846: EXPLAIN FOR LIMIT QUERY SHOWS FILESORT BUT EXECUTION
--echo # IS DONE WITH INDEX ONLY
--echo #
CREATE TABLE t0 (
i0 INTEGER NOT NULL
);
INSERT INTO t0 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
CREATE TABLE t1 (
pk INTEGER PRIMARY KEY,
i1 INTEGER NOT NULL,
i2 INTEGER NOT NULL,
INDEX k1 (i1)
) ENGINE=InnoDB;
INSERT INTO t1
SELECT a0.i0 + 10*a1.i0 + 100*a2.i0,
(a0.i0 + 10*a1.i0 + 100*a2.i0) % 50,
a0.i0 + 10*a1.i0 + 100*a2.i0
FROM t0 AS a0, t0 AS a1, t0 AS a2;
CREATE TABLE t2 (
pk INTEGER PRIMARY KEY,
i1 INTEGER NOT NULL,
i2 INTEGER NOT NULL,
INDEX k1 (i1)
) ENGINE=InnoDB;
INSERT INTO t2
SELECT a0.i0 + 10*a1.i0 + 100*a2.i0,
(a0.i0 + 10*a1.i0 + 100*a2.i0) % 500,
a0.i0 + 10*a1.i0 + 100*a2.i0
FROM t0 AS a0, t0 AS a1, t0 AS a2;
ANALYZE TABLE t1,t2;
let query=
SELECT *
FROM t1 JOIN t2 ON t1.i1=t2.i1
WHERE t2.i2 > 3
ORDER BY t1.i1 LIMIT 20;
--echo # Explain should show "Using filesort"
eval EXPLAIN $query;
FLUSH STATUS;
eval $query;
--echo # Status from execution should show that filesort was used
--skip_if_hypergraph # The hypergraph optimizer avoids the sort completely.
SHOW STATUS LIKE 'Sort%';
DROP TABLE t0, t1, t2;
--echo #
--echo # Bug#30321546: OPTIMIZER TRACE SHOWS DIFFERENT ACTIONS FOR
--echo # A QUERY IF ANOTHER QUERY RUN BEFORE
--echo #
CREATE TABLE t1(a INT);
CREATE TABLE t2(a INT);
INSERT INTO t1 VALUES (1),(2),(3),(4);
INSERT INTO t2 VALUES (1),(2);
ANALYZE TABLE t1, t2;
# The following query should not trigger calculation of condition filter
# in the later query.
--sorted_result
SELECT t1.a, t2.a FROM t1 JOIN t2
ON t1.a+t2.a = (SELECT COUNT(*) FROM t1);
SET optimizer_trace="enabled=on";
SELECT a + (SELECT SUM(a) + (SELECT COUNT(a) FROM t1) FROM t1) AS cnt
FROM t2;
SELECT TRACE NOT RLIKE '"final_filtering_effect": 1' AS OK
FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
SET optimizer_trace="enabled=off";
DROP TABLE t1, t2;
--echo #
--echo # Bug#36918913 INCORRECT QUERY RESULT FOR INDEX_SUBQUERY TYPE IN NULL
--echo # SUBQUERY
--echo #
SET @save_optimizer_switch= @@optimizer_switch;
SET @@optimizer_switch= 'semijoin=off';
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1), (2), (3);
CREATE TABLE t2 (b INT);
INSERT INTO t2 VALUES (1), (2), (3);
ANALYZE TABLE t1, t2;
SELECT * FROM t1 WHERE a IN (SELECT * FROM (
SELECT b FROM t2 ORDER BY b LIMIT 2) x where b > 1);
--skip_if_hypergraph # Depends on the query plan.
--replace_regex $elide_costs
EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE a IN (SELECT * FROM (
SELECT b FROM t2 ORDER BY b LIMIT 2) x where b > 1);
DROP TABLE t1,t2;
SET @@optimizer_switch= @save_optimizer_switch;
|
