Debian Codegraph @

test/joinA.test

raw
1 # 2022-04-18
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, c INT, d INT);
21 CREATE TABLE t2(c INT, d INT, e INT, f INT);
22 CREATE TABLE t3(a INT, b INT, e INT, f INT);
23 CREATE TABLE t4(a INT, c INT, d INT, f INT);
24 INSERT INTO t1 VALUES(11,21,31,41),(12,22,32,42),(15,25,35,45),(18,28,38,48);
25 INSERT INTO t2 VALUES(12,22,32,42),(13,23,33,43),(15,25,35,45),(17,27,37,47);
26 INSERT INTO t3 VALUES(14,24,34,44),(15,25,35,45),(16,26,36,46);
27 INSERT INTO t4 VALUES(11,21,31,41),(13,23,33,43),(16,26,36,46),(19,29,39,49);
28 }
29 2 {
30 CREATE TABLE t1(a INTEGER PRIMARY KEY, b INT, c INT, d INT);
31 CREATE TABLE t2(c INT, d INTEGER PRIMARY KEY, e INT, f INT);
32 CREATE TABLE t3(a INT, b INT, e INTEGER PRIMARY KEY, f INT);
33 CREATE TABLE t4(a INT, c INT, d INT, f INT PRIMARY KEY) WITHOUT ROWID;
34 INSERT INTO t1 VALUES(11,21,31,41),(12,22,32,42),(15,25,35,45),(18,28,38,48);
35 INSERT INTO t2 VALUES(12,22,32,42),(13,23,33,43),(15,25,35,45),(17,27,37,47);
36 INSERT INTO t3 VALUES(14,24,34,44),(15,25,35,45),(16,26,36,46);
37 INSERT INTO t4 VALUES(11,21,31,41),(13,23,33,43),(16,26,36,46),(19,29,39,49);
38 }
39 3 {
40 CREATE TABLE t1a(a INT, b INT, c INT, d INT);
41 CREATE TABLE t2a(c INT, d INT, e INT, f INT);
42 CREATE TABLE t3a(a INT, b INT, e INT, f INT);
43 CREATE TABLE t4a(a INT, c INT, d INT, f INT);
44 INSERT INTO t1a VALUES(11,21,31,41),(12,22,32,42);
45 INSERT INTO t2a VALUES(12,22,32,42),(13,23,33,43);
46 INSERT INTO t3a VALUES(14,24,34,44),(15,25,35,45);
47 INSERT INTO t4a VALUES(11,21,31,41),(13,23,33,43);
48 CREATE TABLE t1b(a INT, b INT, c INT, d INT);
49 CREATE TABLE t2b(c INT, d INT, e INT, f INT);
50 CREATE TABLE t3b(a INT, b INT, e INT, f INT);
51 CREATE TABLE t4b(a INT, c INT, d INT, f INT);
52 INSERT INTO t1b VALUES(15,25,35,45),(18,28,38,48);
53 INSERT INTO t2b VALUES(15,25,35,45),(17,27,37,47);
54 INSERT INTO t3b VALUES(15,25,35,45),(16,26,36,46);
55 INSERT INTO t4b VALUES(16,26,36,46),(19,29,39,49);
56 CREATE VIEW t1 AS SELECT * FROM t1a UNION SELECT * FROM t1b;
57 CREATE VIEW t2 AS SELECT * FROM t2a UNION SELECT * FROM t2b;
58 CREATE VIEW t3 AS SELECT * FROM t3a UNION SELECT * FROM t3b;
59 CREATE VIEW t4 AS SELECT * FROM t4a UNION SELECT * FROM t4b;
60 }
61 } {
62 reset_db
63 db nullvalue -
64 do_execsql_test joinA-$id.setup $schema {}
65
66 # Verified by PG-14
67 do_execsql_test joinA-$id.100 {
68 SELECT a,b,c,d,t2.e,f,t3.e
69 FROM t1
70 INNER JOIN t2 USING(c,d)
71 INNER JOIN t3 USING(a,b,f)
72 INNER JOIN t4 USING(a,c,d,f)
73 ORDER BY 1 nulls first, 3 nulls first;
74 } {}
75
76
77 # Verified by PG-14
78 do_execsql_test joinA-$id.110 {
79 SELECT a,b,c,d,t2.e,f,t3.e
80 FROM t1
81 LEFT JOIN t2 USING(c,d)
82 LEFT JOIN t3 USING(a,b,f)
83 LEFT JOIN t4 USING(a,c,d,f)
84 ORDER BY 1 nulls first, 3 nulls first;
85 } {
86 11 21 31 41 - - -
87 12 22 32 42 - - -
88 15 25 35 45 - - -
89 18 28 38 48 - - -
90 }
91
92 # Verified by PG-14
93 do_execsql_test joinA-$id.120 {
94 SELECT a,b,c,d,t2.e,f,t3.e
95 FROM t1
96 LEFT JOIN t2 USING(c,d)
97 RIGHT JOIN t3 USING(a,b,f)
98 LEFT JOIN t4 USING(a,c,d,f)
99 ORDER BY 1 nulls first, 3 nulls first;
100 } {
101 14 24 - - - 44 34
102 15 25 - - - 45 35
103 16 26 - - - 46 36
104 }
105
106 # Verified by PG-14
107 do_execsql_test joinA-$id.130 {
108 SELECT a,b,c,d,t2.e,f,t3.e
109 FROM t1
110 RIGHT JOIN t2 USING(c,d)
111 LEFT JOIN t3 USING(a,b,f)
112 RIGHT JOIN t4 USING(a,c,d,f)
113 ORDER BY 1 nulls first, 3 nulls first;
114 } {
115 11 - 21 31 - 41 -
116 13 - 23 33 - 43 -
117 16 - 26 36 - 46 -
118 19 - 29 39 - 49 -
119 }
120
121 # Verified by PG-14
122 do_execsql_test joinA-$id.140 {
123 SELECT a,b,c,d,t2.e,f,t3.e
124 FROM t1
125 FULL JOIN t2 USING(c,d)
126 LEFT JOIN t3 USING(a,b,f)
127 RIGHT JOIN t4 USING(a,c,d,f)
128 ORDER BY 1 nulls first, 3 nulls first;
129 } {
130 11 - 21 31 - 41 -
131 13 - 23 33 - 43 -
132 16 - 26 36 - 46 -
133 19 - 29 39 - 49 -
134 }
135
136 # Verified by PG-14
137 do_execsql_test joinA-$id.150 {
138 SELECT a,b,c,d,t2.e,f,t3.e
139 FROM t1
140 RIGHT JOIN t2 USING(c,d)
141 FULL JOIN t3 USING(a,b,f)
142 RIGHT JOIN t4 USING(a,c,d,f)
143 ORDER BY 1 nulls first, 3 nulls first;
144 } {
145 11 - 21 31 - 41 -
146 13 - 23 33 - 43 -
147 16 - 26 36 - 46 -
148 19 - 29 39 - 49 -
149 }
150
151 # Verified by PG-14
152 do_execsql_test joinA-$id.160 {
153 SELECT a,b,c,d,t2.e,f,t3.e
154 FROM t1
155 RIGHT JOIN t2 USING(c,d)
156 LEFT JOIN t3 USING(a,b,f)
157 FULL JOIN t4 USING(a,c,d,f)
158 ORDER BY 1 nulls first, 3 nulls first;
159 } {
160 - - 12 22 32 42 -
161 - - 13 23 33 43 -
162 - - 15 25 35 45 -
163 - - 17 27 37 47 -
164 11 - 21 31 - 41 -
165 13 - 23 33 - 43 -
166 16 - 26 36 - 46 -
167 19 - 29 39 - 49 -
168 }
169
170 # Verified by PG-14
171 do_execsql_test joinA-$id.170 {
172 SELECT a,b,c,d,t2.e,f,t3.e
173 FROM t1
174 LEFT JOIN t2 USING(c,d)
175 RIGHT JOIN t3 USING(a,b,f)
176 FULL JOIN t4 USING(a,c,d,f)
177 ORDER BY 1 nulls first, 3 nulls first;
178 } {
179 11 - 21 31 - 41 -
180 13 - 23 33 - 43 -
181 14 24 - - - 44 34
182 15 25 - - - 45 35
183 16 26 - - - 46 36
184 16 - 26 36 - 46 -
185 19 - 29 39 - 49 -
186 }
187
188 # Verified by PG-14
189 do_execsql_test joinA-$id.200 {
190 SELECT a,b,c,d,t2.e,f,t3.e
191 FROM t1
192 FULL JOIN t2 USING(c,d)
193 FULL JOIN t3 USING(a,b,f)
194 FULL JOIN t4 USING(a,c,d,f)
195 ORDER BY 1 nulls first, 3 nulls first;
196 } {
197 - - 12 22 32 42 -
198 - - 13 23 33 43 -
199 - - 15 25 35 45 -
200 - - 17 27 37 47 -
201 11 - 21 31 - 41 -
202 11 21 31 41 - - -
203 12 22 32 42 - - -
204 13 - 23 33 - 43 -
205 14 24 - - - 44 34
206 15 25 - - - 45 35
207 15 25 35 45 - - -
208 16 26 - - - 46 36
209 16 - 26 36 - 46 -
210 18 28 38 48 - - -
211 19 - 29 39 - 49 -
212 }
213
214 # Verified by PG-14
215 do_execsql_test joinA-$id.201 {
216 SELECT a,b,c,d,t2.e,f,t3.e,t1.a
217 FROM t1
218 FULL JOIN t2 USING(c,d)
219 FULL JOIN t3 USING(a,b,f)
220 FULL JOIN t4 USING(a,c,d,f)
221 WHERE t1.a!=0
222 ORDER BY 1 nulls first, 3 nulls first;
223 } {
224 11 21 31 41 - - - 11
225 12 22 32 42 - - - 12
226 15 25 35 45 - - - 15
227 18 28 38 48 - - - 18
228 }
229
230 # Verified by PG-14
231 do_execsql_test joinA-$id.202 {
232 SELECT a,b,c,d,t2.e,f,t3.e,t3.a
233 FROM t1
234 FULL JOIN t2 USING(c,d)
235 FULL JOIN t3 USING(a,b,f)
236 FULL JOIN t4 USING(a,c,d,f)
237 WHERE t3.a!=0
238 ORDER BY 1 nulls first, 3 nulls first;
239 } {
240 14 24 - - - 44 34 14
241 15 25 - - - 45 35 15
242 16 26 - - - 46 36 16
243 }
244
245 # Verified by PG-14
246 do_execsql_test joinA-$id.203 {
247 SELECT a,b,c,d,t2.e,f,t3.e,t4.a
248 FROM t1
249 FULL JOIN t2 USING(c,d)
250 FULL JOIN t3 USING(a,b,f)
251 FULL JOIN t4 USING(a,c,d,f)
252 WHERE t4.a!=0
253 ORDER BY 1 nulls first, 3 nulls first;
254 } {
255 11 - 21 31 - 41 - 11
256 13 - 23 33 - 43 - 13
257 16 - 26 36 - 46 - 16
258 19 - 29 39 - 49 - 19
259 }
260
261 # Verified by PG-14
262 do_execsql_test joinA-$id.204 {
263 SELECT a,b,c,d,t2.e,f,t3.e
264 FROM t1
265 FULL JOIN t2 USING(c,d)
266 FULL JOIN t3 USING(a,b,f)
267 FULL JOIN t4 USING(a,c,d,f)
268 WHERE t2.e!=0
269 ORDER BY 1 nulls first, 3 nulls first;
270 } {
271 - - 12 22 32 42 -
272 - - 13 23 33 43 -
273 - - 15 25 35 45 -
274 - - 17 27 37 47 -
275 }
276 }
277 finish_test