Debian Codegraph @

test/join7.test

raw
1 # 2022-04-09
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.
12 #
13 # This file implements tests for RIGHT and FULL OUTER JOINs.
14
15 set testdir [file dirname $argv0]
16 source $testdir/tester.tcl
17
18 foreach {id schema} {
19 1 {
20 CREATE TABLE t1(a INT, b INT);
21 INSERT INTO t1 VALUES(1,2),(1,3),(1,4);
22 CREATE INDEX t1a ON t1(a);
23 CREATE TABLE t2(c INT, d INT);
24 INSERT INTO t2 VALUES(3,33),(4,44),(5,55);
25 CREATE INDEX t2c ON t2(c);
26 CREATE VIEW dual(dummy) AS VALUES('x');
27 }
28 2 {
29 CREATE TABLE t1(a INT, b INT);
30 INSERT INTO t1 VALUES(1,2),(1,3),(1,4);
31 CREATE INDEX t1ab ON t1(a,b);
32 CREATE TABLE t2(c INT, d INT);
33 INSERT INTO t2 VALUES(3,33),(4,44),(5,55);
34 CREATE INDEX t2cd ON t2(c,d);
35 CREATE VIEW dual(dummy) AS VALUES('x');
36 }
37 3 {
38 CREATE TABLE t1(a INT, b INT);
39 INSERT INTO t1 VALUES(1,2),(1,3),(1,4);
40 CREATE INDEX t1a ON t1(a);
41 CREATE TABLE t2(c INT, d INT PRIMARY KEY) WITHOUT ROWID;
42 INSERT INTO t2 VALUES(3,33),(4,44),(5,55);
43 CREATE INDEX t2c ON t2(c);
44 CREATE VIEW dual(dummy) AS VALUES('x');
45 }
46 4 {
47 CREATE TABLE t1(a INT, b INT);
48 INSERT INTO t1 VALUES(1,2),(1,3),(1,4);
49 CREATE TABLE t2(c INTEGER PRIMARY KEY, d INT);
50 INSERT INTO t2 VALUES(3,33),(4,44),(5,55);
51 CREATE VIEW dual(dummy) AS VALUES('x');
52 }
53 5 {
54 CREATE TABLE t1(a INT, b INT);
55 INSERT INTO t1 VALUES(1,2),(1,3),(1,4);
56 CREATE TABLE t2(c INT PRIMARY KEY, d INT) WITHOUT ROWID;
57 INSERT INTO t2 VALUES(3,33),(4,44),(5,55);
58 CREATE VIEW dual(dummy) AS VALUES('x');
59 }
60 6 {
61 CREATE TABLE t1(a INT, b INT);
62 INSERT INTO t1 VALUES(1,2),(1,3),(1,4);
63 CREATE VIEW t2(c,d) AS VALUES(3,33),(4,44),(5,55);
64 CREATE VIEW dual(dummy) AS VALUES('x');
65 }
66 7 {
67 CREATE VIEW t1(a,b) AS VALUES(1,2),(1,3),(1,4);
68 CREATE TABLE t2(c INTEGER PRIMARY KEY, d INT);
69 INSERT INTO t2 VALUES(3,33),(4,44),(5,55);
70 CREATE VIEW dual(dummy) AS VALUES('x');
71 }
72 8 {
73 CREATE TABLE t1(a INT, b INT);
74 INSERT INTO t1 VALUES(1,2),(1,3),(1,4);
75 CREATE TABLE t2(c INT, d INT);
76 INSERT INTO t2 VALUES(3,33),(4,44),(5,55);
77 CREATE VIEW dual(dummy) AS VALUES('x');
78 }
79 9 {
80 CREATE TABLE t1(a INT, b INT);
81 INSERT INTO t1 VALUES(1,2),(1,3),(1,4);
82 CREATE TABLE t2a(c INTEGER PRIMARY KEY, i1 INT);
83 CREATE TABLE t2b(i1 INTEGER PRIMARY KEY, d INT);
84 CREATE VIEW t2(c,d) AS SELECT c, d FROM t2a NATURAL JOIN t2b;
85 INSERT INTO t2a VALUES(3,93),(4,94),(5,95),(6,96),(7,97);
86 INSERT INTO t2b VALUES(91,11),(92,22),(93,33),(94,44),(95,55);
87 CREATE TABLE dual(dummy TEXT);
88 INSERT INTO dual(dummy) VALUES('x');
89 }
90 10 {
91 CREATE TABLE t1(a INT, b INT, PRIMARY KEY(a,b)) WITHOUT ROWID;
92 INSERT INTO t1 VALUES(1,2),(1,3),(1,4);
93 CREATE TABLE t2a(c INTEGER PRIMARY KEY, i1 INT);
94 CREATE TABLE t2b(i1 INTEGER PRIMARY KEY, d INT);
95 CREATE VIEW t2(c,d) AS SELECT c, d FROM t2a NATURAL JOIN t2b;
96 INSERT INTO t2a VALUES(3,93),(4,94),(5,95),(6,96),(7,97);
97 INSERT INTO t2b VALUES(91,11),(92,22),(93,33),(94,44),(95,55);
98 CREATE TABLE dual(dummy TEXT);
99 INSERT INTO dual(dummy) VALUES('x');
100 }
101 } {
102 reset_db
103 db nullvalue NULL
104 do_execsql_test join7-$id.setup $schema {}
105
106 # Verified against PG-14 for case 1
107 do_execsql_test join7-$id.10 {
108 SELECT b, d FROM t1 FULL OUTER JOIN t2 ON b=c ORDER BY +b;
109 } {
110 NULL 55
111 2 NULL
112 3 33
113 4 44
114 }
115
116 # Verified against PG-14 for case 1
117 do_execsql_test join7-$id.20 {
118 SELECT a, c FROM t1 FULL OUTER JOIN t2 ON b=c ORDER BY +b;
119 } {
120 NULL 5
121 1 NULL
122 1 3
123 1 4
124 }
125
126 do_execsql_test join7-$id.30 {
127 SELECT * FROM t1 FULL OUTER JOIN t2 ON b=c ORDER BY +b;
128 } {
129 NULL NULL 5 55
130 1 2 NULL NULL
131 1 3 3 33
132 1 4 4 44
133 }
134 do_execsql_test join7-$id.31 {
135 SELECT t1.*, t2.* FROM t2 FULL OUTER JOIN t1 ON b=c ORDER BY +b;
136 } {
137 NULL NULL 5 55
138 1 2 NULL NULL
139 1 3 3 33
140 1 4 4 44
141 }
142 do_execsql_test join7-$id.32 {
143 SELECT t1.*, t2.* FROM t2 FULL OUTER JOIN t1 ON b=c
144 WHERE b=c
145 ORDER BY +b;
146 } {
147 1 3 3 33
148 1 4 4 44
149 }
150 do_execsql_test join7-$id.33 {
151 SELECT t1.*, t2.* FROM t2 FULL OUTER JOIN t1 ON b=c
152 WHERE b>0
153 ORDER BY +b;
154 } {
155 1 2 NULL NULL
156 1 3 3 33
157 1 4 4 44
158 }
159 do_execsql_test join7-$id.34 {
160 SELECT t1.*, t2.* FROM t2 FULL OUTER JOIN t1 ON b=c
161 WHERE b>0 OR b IS NULL
162 ORDER BY +b;
163 } {
164 NULL NULL 5 55
165 1 2 NULL NULL
166 1 3 3 33
167 1 4 4 44
168 }
169 do_execsql_test join7-$id.35 {
170 SELECT t1.*, t2.* FROM t2 FULL OUTER JOIN t1 ON b=c AND b>3 AND c>4
171 ORDER BY coalesce(b,c,0);
172 } {
173 1 2 NULL NULL
174 NULL NULL 3 33
175 1 3 NULL NULL
176 NULL NULL 4 44
177 1 4 NULL NULL
178 NULL NULL 5 55
179 }
180 do_execsql_test join7-$id.36 {
181 SELECT t1.*, t2.* FROM t2 FULL OUTER JOIN t1 ON b=c AND b>3 WHERE c>4
182 ORDER BY coalesce(b,c,0);
183 } {
184 NULL NULL 5 55
185 }
186 do_execsql_test join7-$id.37 {
187 SELECT t1.*, t2.* FROM t2 FULL OUTER JOIN t1 ON b=c WHERE b>3 AND c>4
188 ORDER BY coalesce(b,c,0);
189 } {
190 }
191 do_execsql_test join7-$id.38 {
192 SELECT t1.*, t2.* FROM t2 FULL OUTER JOIN t1 ON b=c WHERE b>3 OR c>4
193 ORDER BY coalesce(b,c,0);
194 } {
195 1 4 4 44
196 NULL NULL 5 55
197 }
198 do_execsql_test join7-$id.39 {
199 SELECT t1.*, t2.* FROM t2 FULL OUTER JOIN t1 ON b=c AND (b>3 OR c>4)
200 ORDER BY coalesce(b,c,0);
201 } {
202 1 2 NULL NULL
203 NULL NULL 3 33
204 1 3 NULL NULL
205 1 4 4 44
206 NULL NULL 5 55
207 }
208 do_execsql_test join7-$id.40 {
209 SELECT * FROM t1 RIGHT OUTER JOIN t2 ON b=c ORDER BY +b;
210 } {
211 NULL NULL 5 55
212 1 3 3 33
213 1 4 4 44
214 }
215 do_execsql_test join7-$id.50 {
216 SELECT t1.*, t2.* FROM t2 LEFT OUTER JOIN t1 ON b=c ORDER BY +b;
217 } {
218 NULL NULL 5 55
219 1 3 3 33
220 1 4 4 44
221 }
222 do_execsql_test join7-$id.60 {
223 SELECT * FROM dual JOIN t1 ON true RIGHT OUTER JOIN t2 ON b=c ORDER BY +b;
224 } {
225 NULL NULL NULL 5 55
226 x 1 3 3 33
227 x 1 4 4 44
228 }
229 do_execsql_test join7-$id.70 {
230 SELECT t1.*, t2.*
231 FROM t2 LEFT JOIN (dual JOIN t1 ON true) ON b=c ORDER BY +b;
232 } {
233 NULL NULL 5 55
234 1 3 3 33
235 1 4 4 44
236 }
237 do_execsql_test join7-$id.80 {
238 SELECT * FROM dual CROSS JOIN t1 RIGHT OUTER JOIN t2 ON b=c ORDER BY +b;
239 } {
240 NULL NULL NULL 5 55
241 x 1 3 3 33
242 x 1 4 4 44
243 }
244 do_execsql_test join7-$id.81 {
245 SELECT dual.*, t1.*, t2.*
246 FROM t1 CROSS JOIN dual RIGHT OUTER JOIN t2 ON b=c ORDER BY +b;
247 } {
248 NULL NULL NULL 5 55
249 x 1 3 3 33
250 x 1 4 4 44
251 }
252 do_execsql_test join7-$id.90 {
253 SELECT * FROM t1 LEFT OUTER JOIN t2 ON b=c ORDER BY +b;
254 } {
255 1 2 NULL NULL
256 1 3 3 33
257 1 4 4 44
258 }
259 do_execsql_test join7-$id.100 {
260 SELECT * FROM t1 FULL OUTER JOIN t2 ON b=c AND a=1 ORDER BY +b;
261 } {
262 NULL NULL 5 55
263 1 2 NULL NULL
264 1 3 3 33
265 1 4 4 44
266 }
267 do_execsql_test join7-$id.101 {
268 SELECT t1.*, t2.* FROM t2 FULL OUTER JOIN t1 ON b=c AND a=1 ORDER BY +b;
269 } {
270 NULL NULL 5 55
271 1 2 NULL NULL
272 1 3 3 33
273 1 4 4 44
274 }
275
276 # Verified against PG-14 for case 1
277 do_execsql_test join7-$id.110 {
278 SELECT * FROM t1 FULL OUTER JOIN t2 ON b=c WHERE a=1 ORDER BY +b;
279 } {
280 1 2 NULL NULL
281 1 3 3 33
282 1 4 4 44
283 }
284
285 do_execsql_test join7-$id.111 {
286 SELECT t1.*, t2.* FROM t2 FULL OUTER JOIN t1 ON b=c WHERE a=1 ORDER BY +b;
287 } {
288 1 2 NULL NULL
289 1 3 3 33
290 1 4 4 44
291 }
292
293 # Verified against PG-14 for case 1
294 do_execsql_test join7-$id.115 {
295 SELECT * FROM t1 FULL OUTER JOIN t2 ON b=c
296 WHERE a=1 OR a IS NULL ORDER BY +b;
297 } {
298 NULL NULL 5 55
299 1 2 NULL NULL
300 1 3 3 33
301 1 4 4 44
302 }
303
304 do_execsql_test join7-$id.116 {
305 SELECT t1.*, t2.* FROM t2 FULL OUTER JOIN t1 ON b=c
306 WHERE a=1 OR a IS NULL ORDER BY +b;
307 } {
308 NULL NULL 5 55
309 1 2 NULL NULL
310 1 3 3 33
311 1 4 4 44
312 }
313
314 # Verified against PG-14 for case 1:
315 do_execsql_test join7-$id.120 {
316 SELECT * FROM t1 FULL OUTER JOIN t2 ON b=c WHERE a IS NULL ORDER BY +d;
317 } {
318 NULL NULL 5 55
319 }
320
321 # Verified against PG-14 for case 1:
322 do_execsql_test join7-$id.130 {
323 SELECT * FROM t1 FULL OUTER JOIN t2 ON b=c AND d<=0 ORDER BY +b, +d;
324 } {
325 NULL NULL 3 33
326 NULL NULL 4 44
327 NULL NULL 5 55
328 1 2 NULL NULL
329 1 3 NULL NULL
330 1 4 NULL NULL
331 }
332
333 # Verified against PG-14 for case 1:
334 do_execsql_test join7-$id.140 {
335 SELECT a, b, c, d
336 FROM t2 FULL OUTER JOIN t1 ON b=c AND d<=0 ORDER BY +b, +d;
337 } {
338 NULL NULL 3 33
339 NULL NULL 4 44
340 NULL NULL 5 55
341 1 2 NULL NULL
342 1 3 NULL NULL
343 1 4 NULL NULL
344 }
345
346 do_execsql_test join7-$id.141 {
347 SELECT a, b, c, d
348 FROM t2 FULL OUTER JOIN t1 ON b=c AND d<=0
349 ORDER BY +b, +d LIMIT 2 OFFSET 2
350 } {
351 NULL NULL 5 55
352 1 2 NULL NULL
353 }
354 }
355 finish_test