Debian Codegraph @

test/windowpushd.test

raw
1 # 2021 February 23
2 #
3 # The author disclaims copyright to this source code. In place of
4 # a legal notice, here is a blessing:
5 #
6 # May you do good and not evil.
7 # May you find forgiveness for yourself and forgive others.
8 # May you share freely, never taking more than you give.
9 #
10 #***********************************************************************
11 # This file implements regression tests for SQLite library. The
12 # focus of this file is testing the push-down optimization when
13 # WHERE constraints are pushed down into a sub-query that uses
14 # window functions.
15 #
16
17 set testdir [file dirname $argv0]
18 source $testdir/tester.tcl
19 set testprefix windowpushd
20
21 do_execsql_test 1.0 {
22 CREATE TABLE t1(id INTEGER PRIMARY KEY, grp_id);
23 CREATE INDEX i1 ON t1(grp_id);
24 CREATE VIEW lll AS SELECT
25 row_number() OVER (PARTITION BY grp_id),
26 grp_id, id
27 FROM t1
28 }
29
30 do_execsql_test 1.1 {
31 INSERT INTO t1 VALUES
32 (1, 2), (2, 3), (3, 3), (4, 1), (5, 1),
33 (6, 1), (7, 1), (8, 1), (9, 3), (10, 3),
34 (11, 2), (12, 3), (13, 3), (14, 2), (15, 1),
35 (16, 2), (17, 1), (18, 2), (19, 3), (20, 2)
36 }
37
38 do_execsql_test 1.2 {
39 SELECT * FROM lll
40 } {
41 1 1 4 2 1 5 3 1 6 4 1 7 5 1 8 6 1 15 7 1 17
42 1 2 1 2 2 11 3 2 14 4 2 16 5 2 18 6 2 20
43 1 3 2 2 3 3 3 3 9 4 3 10 5 3 12 6 3 13 7 3 19
44 }
45
46 do_execsql_test 1.3 {
47 SELECT * FROM lll WHERE grp_id=2
48 } {
49 1 2 1 2 2 11 3 2 14 4 2 16 5 2 18 6 2 20
50 }
51
52 do_eqp_test 1.4 {
53 SELECT * FROM lll WHERE grp_id=2
54 } {SEARCH t1 USING COVERING INDEX i1 (grp_id=?)}
55
56 #-------------------------------------------------------------------------
57 reset_db
58 do_execsql_test 2.0 {
59 CREATE TABLE t1(a, b, c, d);
60 INSERT INTO t1 VALUES('A', 'C', 1, 0.1);
61 INSERT INTO t1 VALUES('A', 'D', 2, 0.2);
62 INSERT INTO t1 VALUES('A', 'E', 3, 0.3);
63 INSERT INTO t1 VALUES('A', 'C', 4, 0.4);
64 INSERT INTO t1 VALUES('B', 'D', 5, 0.5);
65 INSERT INTO t1 VALUES('B', 'E', 6, 0.6);
66 INSERT INTO t1 VALUES('B', 'C', 7, 0.7);
67 INSERT INTO t1 VALUES('B', 'D', 8, 0.8);
68 INSERT INTO t1 VALUES('C', 'E', 9, 0.9);
69 INSERT INTO t1 VALUES('C', 'C', 10, 1.0);
70 INSERT INTO t1 VALUES('C', 'D', 11, 1.1);
71 INSERT INTO t1 VALUES('C', 'E', 12, 1.2);
72
73 CREATE INDEX i1 ON t1(a);
74 CREATE INDEX i2 ON t1(b);
75
76 CREATE VIEW v1 AS SELECT a, c, max(c) OVER (PARTITION BY a) FROM t1;
77
78 CREATE VIEW v2 AS SELECT a, c,
79 max(c) OVER (PARTITION BY a),
80 row_number() OVER ()
81 FROM t1;
82
83 CREATE VIEW v3 AS SELECT b, d,
84 max(d) OVER (PARTITION BY b),
85 row_number() OVER (PARTITION BY b)
86 FROM t1;
87
88 CREATE TABLE t2(x, y, z);
89 INSERT INTO t2 VALUES('W', 3, 1);
90 INSERT INTO t2 VALUES('W', 2, 2);
91 INSERT INTO t2 VALUES('X', 1, 4);
92 INSERT INTO t2 VALUES('X', 5, 7);
93 INSERT INTO t2 VALUES('Y', 1, 9);
94 INSERT INTO t2 VALUES('Y', 4, 2);
95 INSERT INTO t2 VALUES('Z', 3, 3);
96 INSERT INTO t2 VALUES('Z', 3, 4);
97 }
98
99 foreach tn {0 1} {
100 if {$tn} {
101 optimization_control db all on
102 } else {
103 optimization_control db push-down off
104 }
105
106 do_execsql_test 2.$tn.1.1 {
107 SELECT * FROM v1;
108 } {
109 A 1 4 A 2 4 A 3 4 A 4 4
110 B 5 8 B 6 8 B 7 8 B 8 8
111 C 9 12 C 10 12 C 11 12 C 12 12
112 }
113
114 do_execsql_test 2.$tn.1.2 {
115 SELECT * FROM v1 WHERE a IN ('A', 'B');
116 } {
117 A 1 4 A 2 4 A 3 4 A 4 4
118 B 5 8 B 6 8 B 7 8 B 8 8
119 }
120
121 do_execsql_test 2.$tn.1.3 {
122 SELECT * FROM v1 WHERE a IS 'C'
123 } {
124 C 9 12 C 10 12 C 11 12 C 12 12
125 }
126
127 if {$tn==1} {
128 do_eqp_test 2.$tn.1.4 {
129 SELECT * FROM v1 WHERE a IN ('A', 'B');
130 } {USING INDEX i1 (a=?)}
131
132 do_eqp_test 2.$tn.1.5 {
133 SELECT * FROM v1 WHERE a = 'c' COLLATE nocase
134 } {USING INDEX i1}
135 }
136
137 do_execsql_test 2.$tn.2.1 {
138 SELECT * FROM v2;
139 } {
140 A 1 4 1 A 2 4 2 A 3 4 3 A 4 4 4
141 B 5 8 5 B 6 8 6 B 7 8 7 B 8 8 8
142 C 9 12 9 C 10 12 10 C 11 12 11 C 12 12 12
143 }
144
145 do_execsql_test 2.$tn.2.2 {
146 SELECT * FROM v2 WHERE a = 'C';
147 } {
148 C 9 12 9 C 10 12 10 C 11 12 11 C 12 12 12
149 }
150
151 do_execsql_test 2.$tn.3.1 { SELECT * FROM v3; } {
152 C 0.1 1.0 1 C 0.4 1.0 2 C 0.7 1.0 3 C 1.0 1.0 4
153 D 0.2 1.1 1 D 0.5 1.1 2 D 0.8 1.1 3 D 1.1 1.1 4
154 E 0.3 1.2 1 E 0.6 1.2 2 E 0.9 1.2 3 E 1.2 1.2 4
155 }
156
157 do_execsql_test 2.$tn.3.2 { SELECT * FROM v3 WHERE b<'E' } {
158 C 0.1 1.0 1 C 0.4 1.0 2 C 0.7 1.0 3 C 1.0 1.0 4
159 D 0.2 1.1 1 D 0.5 1.1 2 D 0.8 1.1 3 D 1.1 1.1 4
160 }
161
162 if {$tn==1} {
163 do_eqp_test 2.$tn.3.3 {
164 SELECT * FROM v3 WHERE b='E'
165 } {SEARCH t1 USING INDEX i2 (b=?)}
166 do_eqp_test 2.$tn.3.4 {
167 SELECT * FROM v3 WHERE b>'C'
168 } {SEARCH t1 USING INDEX i2 (b>?)}
169 }
170
171 do_execsql_test 2.$tn.3.5 { SELECT * FROM v3 WHERE d<0.55; } {
172 C 0.1 1.0 1 C 0.4 1.0 2
173 D 0.2 1.1 1 D 0.5 1.1 2
174 E 0.3 1.2 1
175 }
176 if {$tn==1} {
177 do_eqp_test 2.$tn.3.6 {
178 SELECT * FROM v3 WHERE d<0.55
179 } {SCAN t1 USING INDEX i2}
180 }
181
182 do_execsql_test 2.$tn.4.1 {
183 SELECT * FROM (
184 SELECT x, sum(y) AS s, max(z) AS m
185 FROM t2 GROUP BY x
186 )
187 } {
188 W 5 2
189 X 6 7
190 Y 5 9
191 Z 6 4
192 }
193
194 do_execsql_test 2.$tn.4.1 {
195 SELECT * FROM (
196 SELECT x, sum(y) AS s, max(z) AS m,
197 max( max(z) ) OVER (PARTITION BY sum(y)
198 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
199 )
200 FROM t2 GROUP BY x
201 )
202 } {
203 W 5 2 9
204 Y 5 9 9
205 X 6 7 7
206 Z 6 4 7
207 }
208
209 do_execsql_test 2.$tn.4.2 {
210 SELECT * FROM (
211 SELECT x, sum(y) AS s, max(z) AS m,
212 max( max(z) ) OVER (PARTITION BY sum(y)
213 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
214 )
215 FROM t2 GROUP BY x
216 ) WHERE s=6
217 } {
218 X 6 7 7
219 Z 6 4 7
220 }
221
222 do_execsql_test 2.$tn.4.3 {
223 SELECT * FROM (
224 SELECT x, sum(y) AS s, max(z) AS m,
225 max( max(z) ) OVER (PARTITION BY sum(y)
226 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
227 )
228 FROM t2 GROUP BY x
229 ) WHERE s<6
230 } {
231 W 5 2 9
232 Y 5 9 9
233 }
234
235 }
236
237
238
239
240 finish_test