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
|
CREATE TABLE t1 ( a INTEGER, b INTEGER, c INTEGER );
INSERT INTO t1 (a,b,c) VALUES (1,2,3);
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
INSERT INTO t1 SELECT * FROM t1;
ANALYZE TABLE t1;
Table Op Msg_type Msg_text
test.t1 analyze status OK
EXPLAIN FORMAT=tree SELECT SUM(c) OVER (PARTITION BY a ORDER BY b) FROM t1;
EXPLAIN
-> Window aggregate with buffering: sum(t1.c) OVER (PARTITION BY t1.a ORDER BY t1.b )
-> Sort: t1.a, t1.b
-> Table scan on t1
EXPLAIN FORMAT=tree SELECT SUM(c) OVER (PARTITION BY a ORDER BY b), SUM(c) OVER (ORDER BY a,b) FROM t1;
EXPLAIN
-> Window aggregate with buffering: sum(t1.c) OVER (ORDER BY t1.a,t1.b )
-> Window aggregate with buffering: sum(t1.c) OVER (PARTITION BY t1.a ORDER BY t1.b )
-> Sort: t1.a, t1.b
-> Table scan on t1
EXPLAIN FORMAT=tree SELECT SUM(b) OVER (PARTITION BY a), SUM(c) OVER (ORDER BY a,b) FROM t1;
EXPLAIN
-> Window aggregate with buffering: sum(t1.c) OVER (ORDER BY t1.a,t1.b )
-> Window aggregate with buffering: sum(t1.b) OVER (PARTITION BY t1.a )
-> Sort: t1.a, t1.b
-> Table scan on t1
EXPLAIN FORMAT=tree SELECT SUM(c) OVER (PARTITION BY a ORDER BY b), SUM(c) OVER (ORDER BY b,a) FROM t1;
EXPLAIN
-> Window aggregate with buffering: sum(t1.c) OVER (ORDER BY t1.b,t1.a )
-> Sort: t1.b, t1.a
-> Window aggregate with buffering: sum(t1.c) OVER (PARTITION BY t1.a ORDER BY t1.b )
-> Sort: t1.a, t1.b
-> Table scan on t1
CREATE INDEX idx ON t1 (a, b);
ANALYZE TABLE t1;
Table Op Msg_type Msg_text
test.t1 analyze status OK
EXPLAIN FORMAT=tree SELECT SUM(c) OVER (PARTITION BY a) FROM t1;
EXPLAIN
-> Window aggregate with buffering: sum(t1.c) OVER (PARTITION BY t1.a )
-> Index scan on t1 using idx
EXPLAIN FORMAT=tree SELECT SUM(c) OVER (PARTITION BY a ORDER BY b) FROM t1;
EXPLAIN
-> Window aggregate with buffering: sum(t1.c) OVER (PARTITION BY t1.a ORDER BY t1.b )
-> Index scan on t1 using idx
ALTER TABLE t1 DROP INDEX idx;
ANALYZE TABLE t1;
Table Op Msg_type Msg_text
test.t1 analyze status OK
EXPLAIN FORMAT=tree SELECT a, SUM(c) OVER (PARTITION BY a) FROM t1 ORDER BY a;
EXPLAIN
-> Window aggregate with buffering: sum(t1.c) OVER (PARTITION BY t1.a )
-> Sort: t1.a
-> Table scan on t1
EXPLAIN FORMAT=tree SELECT a, SUM(c) OVER (ORDER BY b), AVG(c) OVER (ORDER BY a), SUM(c) OVER (PARTITION BY a) AS x FROM t1 ORDER BY b, x;
EXPLAIN
-> Window aggregate with buffering: sum(t1.c) OVER (ORDER BY t1.b )
-> Sort: t1.b, x
-> Window aggregate with buffering: sum(t1.c) OVER (PARTITION BY t1.a )
-> Window aggregate with buffering: avg(t1.c) OVER (ORDER BY t1.a )
-> Sort: t1.a
-> Table scan on t1
EXPLAIN FORMAT=tree SELECT DISTINCT a, SUM(c) OVER (ORDER BY b) FROM t1;
EXPLAIN
-> Sort with duplicate removal: t1.a, `SUM(c) OVER (ORDER BY b)`
-> Window aggregate with buffering: sum(t1.c) OVER (ORDER BY t1.b )
-> Sort: t1.b
-> Table scan on t1
EXPLAIN FORMAT=tree SELECT DISTINCT a, b, SUM(b) OVER (PARTITION BY a) FROM t1 ORDER BY a;
EXPLAIN
-> Remove duplicates from input grouped on t1.a, t1.b (cost=3.65..5.05 rows=8)
-> Window aggregate with buffering: sum(t1.b) OVER (PARTITION BY t1.a ) (cost=3.55..4.25 rows=8)
-> Sort: t1.a, t1.b (cost=3.45..3.45 rows=8)
-> Table scan on t1 (cost=0.0312..0.25 rows=8)
EXPLAIN FORMAT=tree SELECT DISTINCT a, SUM(c) OVER (ORDER BY b) FROM t1 ORDER BY a;
EXPLAIN
-> Sort with duplicate removal: t1.a, `SUM(c) OVER (ORDER BY b)`
-> Window aggregate with buffering: sum(t1.c) OVER (ORDER BY t1.b )
-> Sort: t1.b
-> Table scan on t1
EXPLAIN FORMAT=tree SELECT SUM(a) OVER (ORDER BY b) FROM t1 WHERE b=3;
EXPLAIN
-> Window aggregate with buffering: sum(t1.a) OVER (ORDER BY t1.b )
-> Filter: (t1.b = 3)
-> Table scan on t1
CREATE INDEX idx ON t1 (a);
ANALYZE TABLE t1;
Table Op Msg_type Msg_text
test.t1 analyze status OK
EXPLAIN FORMAT=tree SELECT t1.a, SUM(t1.a) OVER (ORDER BY t1.a), SUM(t2.b) OVER (ORDER BY t2.b) FROM t1 JOIN t1 AS t2 ON t1.a = t2.b;
EXPLAIN
-> Window aggregate with buffering: sum(t1.b) OVER (ORDER BY t1.b )
-> Window aggregate with buffering: sum(t1.a) OVER (ORDER BY t1.a )
-> Nested loop inner join
-> Index scan on t1 using idx
-> Filter: (t1.a = t2.b)
-> Table scan on t2
DROP TABLE t1;
#
# Bug#34899286: Assertion `!OrderItemsReferenceUnavailableTables(
# path, used_tables_before_repla
#
CREATE TABLE t1 (pk INT PRIMARY KEY, x INT);
CREATE TABLE t2 (pk INT PRIMARY KEY);
ANALYZE TABLE t1, t2;
Table Op Msg_type Msg_text
test.t1 analyze status OK
test.t2 analyze status OK
EXPLAIN FORMAT=TREE
SELECT ROW_NUMBER() OVER (PARTITION BY t1.x)
FROM t1, t2 WHERE t1.x = t2.pk
GROUP BY t1.pk;
EXPLAIN
-> Window aggregate: row_number() OVER (PARTITION BY t1.x )
-> Sort: t1.x
-> Stream results
-> Group (no aggregates)
-> Nested loop inner join
-> Index scan on t1 using PRIMARY
-> Filter: (t1.x = t2.pk)
-> Index scan on t2 using PRIMARY
DROP TABLE t1, t2;
|
