Debian Codegraph @

test/joinE.test

raw
1 # 2022-05-13
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 #
12 # This file implements tests for JOINs that use Bloom filters.
13 #
14 # The test case output is (mostly) all generated by PostgreSQL 14. This
15 # test module was created as follows:
16 #
17 # 1. Run a TCL script (included at the bottom of this file) that
18 # generates an input script for "psql" that will run man
19 # diverse tests on joins.
20 #
21 # 2. Run the script from step (1) through psql and collect the
22 # output.
23 #
24 # 3. Make a few minor global search-and-replace operations to convert
25 # the psql output into a form suitable for this test module.
26 #
27 # 4. Add this header, and the script content at the footer.
28 #
29 set testdir [file dirname $argv0]
30 source $testdir/tester.tcl
31 db nullvalue -
32 db eval {
33 CREATE TABLE t1(a INT);
34 INSERT INTO t1 VALUES(1),(NULL);
35 CREATE TABLE t2(b INT);
36 INSERT INTO t2 VALUES(2),(NULL);
37 }
38 do_execsql_test joinE-1 {
39 SELECT a, b
40 FROM t1 INNER JOIN t2 ON true
41 ORDER BY coalesce(a,b,3);
42 } {
43 1 2
44 1 -
45 - 2
46 - -
47 }
48 do_execsql_test joinE-2 {
49 SELECT a, b
50 FROM t1 INNER JOIN t2 ON true WHERE a IS NULL
51 ORDER BY coalesce(a,b,3);
52 } {
53 - 2
54 - -
55 }
56 do_execsql_test joinE-3 {
57 SELECT a, b
58 FROM t1 INNER JOIN t2 ON a IS NULL
59 ORDER BY coalesce(a,b,3);
60 } {
61 - 2
62 - -
63 }
64 do_execsql_test joinE-4 {
65 SELECT a, b
66 FROM t1 INNER JOIN t2 ON true WHERE b IS NULL
67 ORDER BY coalesce(a,b,3);
68 } {
69 1 -
70 - -
71 }
72 do_execsql_test joinE-5 {
73 SELECT a, b
74 FROM t1 INNER JOIN t2 ON b IS NULL
75 ORDER BY coalesce(a,b,3);
76 } {
77 1 -
78 - -
79 }
80 do_execsql_test joinE-6 {
81 SELECT a, b
82 FROM t1 LEFT JOIN t2 ON true
83 ORDER BY coalesce(a,b,3);
84 } {
85 1 2
86 1 -
87 - 2
88 - -
89 }
90 do_execsql_test joinE-7 {
91 SELECT a, b
92 FROM t1 LEFT JOIN t2 ON true WHERE a IS NULL
93 ORDER BY coalesce(a,b,3);
94 } {
95 - 2
96 - -
97 }
98 do_execsql_test joinE-8 {
99 SELECT a, b
100 FROM t1 LEFT JOIN t2 ON a IS NULL
101 ORDER BY coalesce(a,b,3);
102 } {
103 1 -
104 - 2
105 - -
106 }
107 do_execsql_test joinE-9 {
108 SELECT a, b
109 FROM t1 LEFT JOIN t2 ON true WHERE b IS NULL
110 ORDER BY coalesce(a,b,3);
111 } {
112 1 -
113 - -
114 }
115 do_execsql_test joinE-10 {
116 SELECT a, b
117 FROM t1 LEFT JOIN t2 ON b IS NULL
118 ORDER BY coalesce(a,b,3);
119 } {
120 1 -
121 - -
122 }
123 do_execsql_test joinE-11 {
124 SELECT a, b
125 FROM t1 RIGHT JOIN t2 ON true
126 ORDER BY coalesce(a,b,3);
127 } {
128 1 2
129 1 -
130 - 2
131 - -
132 }
133 do_execsql_test joinE-12 {
134 SELECT a, b
135 FROM t1 RIGHT JOIN t2 ON true WHERE a IS NULL
136 ORDER BY coalesce(a,b,3);
137 } {
138 - 2
139 - -
140 }
141 do_execsql_test joinE-13 {
142 SELECT a, b
143 FROM t1 RIGHT JOIN t2 ON a IS NULL
144 ORDER BY coalesce(a,b,3);
145 } {
146 - 2
147 - -
148 }
149 do_execsql_test joinE-14 {
150 SELECT a, b
151 FROM t1 RIGHT JOIN t2 ON true WHERE b IS NULL
152 ORDER BY coalesce(a,b,3);
153 } {
154 1 -
155 - -
156 }
157 do_execsql_test joinE-15 {
158 SELECT a, b
159 FROM t1 RIGHT JOIN t2 ON b IS NULL
160 ORDER BY coalesce(a,b,3);
161 } {
162 1 -
163 - 2
164 - -
165 }
166 do_execsql_test joinE-16 {
167 SELECT a, b
168 FROM t1 FULL JOIN t2 ON true
169 ORDER BY coalesce(a,b,3);
170 } {
171 1 2
172 1 -
173 - 2
174 - -
175 }
176 do_execsql_test joinE-17 {
177 SELECT a, b
178 FROM t1 FULL JOIN t2 ON true WHERE a IS NULL
179 ORDER BY coalesce(a,b,3);
180 } {
181 - 2
182 - -
183 }
184
185 # PG-14 is unable to perform this join. It says: FULL JOIN is only
186 # supported with merge-joinable or hash-joinable join conditions
187 #
188 # do_execsql_test joinE-18 {
189 # SELECT a, b
190 # FROM t1 FULL JOIN t2 ON a IS NULL
191 # ORDER BY coalesce(a,b,3);
192 # } {
193 # }
194
195 do_execsql_test joinE-19 {
196 SELECT a, b
197 FROM t1 FULL JOIN t2 ON true WHERE b IS NULL
198 ORDER BY coalesce(a,b,3);
199 } {
200 1 -
201 - -
202 }
203
204 # PG-14 is unable to perform this join. It says: FULL JOIN is only
205 # supported with merge-joinable or hash-joinable join conditions
206 #
207 # do_execsql_test joinE-20 {
208 # SELECT a, b
209 # FROM t1 FULL JOIN t2 ON b IS NULL
210 # ORDER BY coalesce(a,b,3);
211 # } {
212 # }
213
214 db eval {
215 DELETE FROM t1;
216 INSERT INTO t1 VALUES(1);
217 DELETE FROM t2;
218 INSERT INTO t2 VALUES(NULL);
219 }
220
221 do_execsql_test joinE-21 {
222 SELECT a, b
223 FROM t1 INNER JOIN t2 ON true
224 ORDER BY coalesce(a,b,3);
225 } {
226 1 -
227 }
228 do_execsql_test joinE-22 {
229 SELECT a, b
230 FROM t1 INNER JOIN t2 ON true WHERE a IS NULL
231 ORDER BY coalesce(a,b,3);
232 } {
233 }
234 do_execsql_test joinE-23 {
235 SELECT a, b
236 FROM t1 INNER JOIN t2 ON a IS NULL
237 ORDER BY coalesce(a,b,3);
238 } {
239 }
240 do_execsql_test joinE-24 {
241 SELECT a, b
242 FROM t1 INNER JOIN t2 ON true WHERE b IS NULL
243 ORDER BY coalesce(a,b,3);
244 } {
245 1 -
246 }
247 do_execsql_test joinE-25 {
248 SELECT a, b
249 FROM t1 INNER JOIN t2 ON b IS NULL
250 ORDER BY coalesce(a,b,3);
251 } {
252 1 -
253 }
254 do_execsql_test joinE-26 {
255 SELECT a, b
256 FROM t1 LEFT JOIN t2 ON true
257 ORDER BY coalesce(a,b,3);
258 } {
259 1 -
260 }
261 do_execsql_test joinE-27 {
262 SELECT a, b
263 FROM t1 LEFT JOIN t2 ON true WHERE a IS NULL
264 ORDER BY coalesce(a,b,3);
265 } {
266 }
267 do_execsql_test joinE-28 {
268 SELECT a, b
269 FROM t1 LEFT JOIN t2 ON a IS NULL
270 ORDER BY coalesce(a,b,3);
271 } {
272 1 -
273 }
274 do_execsql_test joinE-29 {
275 SELECT a, b
276 FROM t1 LEFT JOIN t2 ON true WHERE b IS NULL
277 ORDER BY coalesce(a,b,3);
278 } {
279 1 -
280 }
281 do_execsql_test joinE-30 {
282 SELECT a, b
283 FROM t1 LEFT JOIN t2 ON b IS NULL
284 ORDER BY coalesce(a,b,3);
285 } {
286 1 -
287 }
288 do_execsql_test joinE-31 {
289 SELECT a, b
290 FROM t1 RIGHT JOIN t2 ON true
291 ORDER BY coalesce(a,b,3);
292 } {
293 1 -
294 }
295
296 do_execsql_test joinE-32 {
297 SELECT a, b
298 FROM t1 RIGHT JOIN t2 ON true WHERE a IS NULL
299 ORDER BY coalesce(a,b,3);
300 } {
301 }
302
303 do_execsql_test joinE-33 {
304 SELECT a, b
305 FROM t1 RIGHT JOIN t2 ON a IS NULL
306 ORDER BY coalesce(a,b,3);
307 } {
308 - -
309 }
310 do_execsql_test joinE-34 {
311 SELECT a, b
312 FROM t1 RIGHT JOIN t2 ON true WHERE b IS NULL
313 ORDER BY coalesce(a,b,3);
314 } {
315 1 -
316 }
317 do_execsql_test joinE-35 {
318 SELECT a, b
319 FROM t1 RIGHT JOIN t2 ON b IS NULL
320 ORDER BY coalesce(a,b,3);
321 } {
322 1 -
323 }
324 do_execsql_test joinE-36 {
325 SELECT a, b
326 FROM t1 FULL JOIN t2 ON true
327 ORDER BY coalesce(a,b,3);
328 } {
329 1 -
330 }
331 do_execsql_test joinE-37 {
332 SELECT a, b
333 FROM t1 FULL JOIN t2 ON true WHERE a IS NULL
334 ORDER BY coalesce(a,b,3);
335 } {
336 }
337
338 # PG-14 is unable
339 #
340 # do_execsql_test joinE-38 {
341 # SELECT a, b
342 # FROM t1 FULL JOIN t2 ON a IS NULL
343 # ORDER BY coalesce(a,b,3);
344 # } {
345 # }
346
347 do_execsql_test joinE-39 {
348 SELECT a, b
349 FROM t1 FULL JOIN t2 ON true WHERE b IS NULL
350 ORDER BY coalesce(a,b,3);
351 } {
352 1 -
353 }
354
355 # PG-14 is unable
356 # do_execsql_test joinE-40 {
357 # SELECT a, b
358 # FROM t1 FULL JOIN t2 ON b IS NULL
359 # ORDER BY coalesce(a,b,3);
360 # } {
361 # }
362
363 finish_test
364
365 ##############################################################################
366 # This is the PG-14 test script generator
367 #
368 # puts "
369 # \\pset border off
370 # \\pset tuples_only on
371 # \\pset null -
372 #
373 # DROP TABLE IF EXISTS t1;
374 # DROP TABLE IF EXISTS t2;
375 # CREATE TABLE t1(a INT);
376 # INSERT INTO t1 VALUES(1),(NULL);
377 # CREATE TABLE t2(b INT);
378 # INSERT INTO t2 VALUES(2),(NULL);
379 # "
380 #
381 # proc echo {prefix txt} {
382 # regsub -all {\n} $txt \n$prefix txt
383 # puts "$prefix$txt"
384 # }
385 #
386 # set n 0
387 # set k 0
388 # foreach j1 {INNER LEFT RIGHT FULL} {
389 # foreach on1 {
390 # true
391 # {true WHERE a IS NULL}
392 # {a IS NULL}
393 # {true WHERE b IS NULL}
394 # {b IS NULL}
395 # } {
396 #
397 # incr n
398 # incr k
399 # set q1 ""
400 # append q1 "SELECT a, b\n"
401 # append q1 " FROM t1 $j1 JOIN t2 ON $on1\n"
402 # append q1 " ORDER BY coalesce(a,b,3);"
403 #
404 # echo "\\qecho " "do_execsql_test joinE-$n \{"
405 # echo "\\qecho X " $q1
406 # echo "\\qecho " "\} \{"
407 # puts $q1
408 # echo "\\qecho " "\}"
409 #
410 # }
411 # }
412 #
413 # puts "
414 # DELETE FROM t1;
415 # INSERT INTO t1 VALUES(1);
416 # DELETE FROM t2;
417 # INSERT INTO t2 VALUES(NULL);
418 # "
419 #
420 # foreach j1 {INNER LEFT RIGHT FULL} {
421 # foreach on1 {
422 # true
423 # {true WHERE a IS NULL}
424 # {a IS NULL}
425 # {true WHERE b IS NULL}
426 # {b IS NULL}
427 # } {
428 #
429 # incr n
430 # incr k
431 # set q1 ""
432 # append q1 "SELECT a, b\n"
433 # append q1 " FROM t1 $j1 JOIN t2 ON $on1\n"
434 # append q1 " ORDER BY coalesce(a,b,3);"
435 #
436 # echo "\\qecho " "do_execsql_test joinE-$n \{"
437 # echo "\\qecho X " $q1
438 # echo "\\qecho " "\} \{"
439 # puts $q1
440 # echo "\\qecho " "\}"
441 #
442 # }
443 # }