Debian Codegraph @

src/test/modules/test_predtest/sql/test_predtest.sql

raw
1 CREATE EXTENSION test_predtest;
2
3 -- Make output more legible
4 \pset expanded on
5
6 -- Test data
7
8 -- all combinations of four boolean values
9 create table booleans as
10 select
11 case i%3 when 0 then true when 1 then false else null end as x,
12 case (i/3)%3 when 0 then true when 1 then false else null end as y,
13 case (i/9)%3 when 0 then true when 1 then false else null end as z,
14 case (i/27)%3 when 0 then true when 1 then false else null end as w
15 from generate_series(0, 3*3*3*3-1) i;
16
17 -- all combinations of two integers 0..9, plus null
18 create table integers as
19 select
20 case i%11 when 10 then null else i%11 end as x,
21 case (i/11)%11 when 10 then null else (i/11)%11 end as y
22 from generate_series(0, 11*11-1) i;
23
24 -- and a simple strict function that's opaque to the optimizer
25 create function strictf(bool, bool) returns bool
26 language plpgsql as $$begin return $1 and not $2; end$$ strict;
27
28 -- a simple function to make arrays opaque to the optimizer
29 create function opaque_array(int[]) returns int[]
30 language plpgsql as $$begin return $1; end$$ strict;
31
32 -- Basic proof rules for single boolean variables
33
34 select * from test_predtest($$
35 select x, x
36 from booleans
37 $$);
38
39 select * from test_predtest($$
40 select x, not x
41 from booleans
42 $$);
43
44 select * from test_predtest($$
45 select not x, x
46 from booleans
47 $$);
48
49 select * from test_predtest($$
50 select not x, not x
51 from booleans
52 $$);
53
54 select * from test_predtest($$
55 select x is not null, x
56 from booleans
57 $$);
58
59 select * from test_predtest($$
60 select x is not null, x is null
61 from integers
62 $$);
63
64 select * from test_predtest($$
65 select x is null, x is not null
66 from integers
67 $$);
68
69 select * from test_predtest($$
70 select x is not true, x
71 from booleans
72 $$);
73
74 select * from test_predtest($$
75 select x, x is not true
76 from booleans
77 $$);
78
79 select * from test_predtest($$
80 select x is false, x
81 from booleans
82 $$);
83
84 select * from test_predtest($$
85 select x, x is false
86 from booleans
87 $$);
88
89 select * from test_predtest($$
90 select x is unknown, x
91 from booleans
92 $$);
93
94 select * from test_predtest($$
95 select x, x is unknown
96 from booleans
97 $$);
98
99 -- Assorted not-so-trivial refutation rules
100
101 select * from test_predtest($$
102 select x is null, x
103 from booleans
104 $$);
105
106 select * from test_predtest($$
107 select x, x is null
108 from booleans
109 $$);
110
111 select * from test_predtest($$
112 select strictf(x,y), x is null
113 from booleans
114 $$);
115
116 select * from test_predtest($$
117 select (x is not null) is not true, x
118 from booleans
119 $$);
120
121 select * from test_predtest($$
122 select strictf(x,y), (x is not null) is false
123 from booleans
124 $$);
125
126 select * from test_predtest($$
127 select x > y, (y < x) is false
128 from integers
129 $$);
130
131 -- Tests involving AND/OR constructs
132
133 select * from test_predtest($$
134 select x, x and y
135 from booleans
136 $$);
137
138 select * from test_predtest($$
139 select not x, x and y
140 from booleans
141 $$);
142
143 select * from test_predtest($$
144 select x, not x and y
145 from booleans
146 $$);
147
148 select * from test_predtest($$
149 select x or y, x
150 from booleans
151 $$);
152
153 select * from test_predtest($$
154 select x and y, x
155 from booleans
156 $$);
157
158 select * from test_predtest($$
159 select x and y, not x
160 from booleans
161 $$);
162
163 select * from test_predtest($$
164 select x and y, y and x
165 from booleans
166 $$);
167
168 select * from test_predtest($$
169 select not y, y and x
170 from booleans
171 $$);
172
173 select * from test_predtest($$
174 select x or y, y or x
175 from booleans
176 $$);
177
178 select * from test_predtest($$
179 select x or y or z, x or z
180 from booleans
181 $$);
182
183 select * from test_predtest($$
184 select x and z, x and y and z
185 from booleans
186 $$);
187
188 select * from test_predtest($$
189 select z or w, x or y
190 from booleans
191 $$);
192
193 select * from test_predtest($$
194 select z and w, x or y
195 from booleans
196 $$);
197
198 select * from test_predtest($$
199 select x, (x and y) or (x and z)
200 from booleans
201 $$);
202
203 select * from test_predtest($$
204 select (x and y) or z, y and x
205 from booleans
206 $$);
207
208 select * from test_predtest($$
209 select (not x or not y) and z, y and x
210 from booleans
211 $$);
212
213 select * from test_predtest($$
214 select y or x, (x or y) and z
215 from booleans
216 $$);
217
218 select * from test_predtest($$
219 select not x and not y, (x or y) and z
220 from booleans
221 $$);
222
223 -- Tests using btree operator knowledge
224
225 select * from test_predtest($$
226 select x <= y, x < y
227 from integers
228 $$);
229
230 select * from test_predtest($$
231 select x <= y, x > y
232 from integers
233 $$);
234
235 select * from test_predtest($$
236 select x <= y, y >= x
237 from integers
238 $$);
239
240 select * from test_predtest($$
241 select x <= y, y > x and y < x+2
242 from integers
243 $$);
244
245 select * from test_predtest($$
246 select x <= 5, x <= 7
247 from integers
248 $$);
249
250 select * from test_predtest($$
251 select x <= 5, x > 7
252 from integers
253 $$);
254
255 select * from test_predtest($$
256 select x <= 5, 7 > x
257 from integers
258 $$);
259
260 select * from test_predtest($$
261 select 5 >= x, 7 > x
262 from integers
263 $$);
264
265 select * from test_predtest($$
266 select 5 >= x, x > 7
267 from integers
268 $$);
269
270 select * from test_predtest($$
271 select 5 = x, x = 7
272 from integers
273 $$);
274
275 select * from test_predtest($$
276 select x is not null, x > 7
277 from integers
278 $$);
279
280 select * from test_predtest($$
281 select x is not null, int4lt(x,8)
282 from integers
283 $$);
284
285 select * from test_predtest($$
286 select x is null, x > 7
287 from integers
288 $$);
289
290 select * from test_predtest($$
291 select x is null, int4lt(x,8)
292 from integers
293 $$);
294
295 select * from test_predtest($$
296 select x is not null, x < 'foo'
297 from (values
298 ('aaa'::varchar), ('zzz'::varchar), (null)) as v(x)
299 $$);
300
301 -- Cases using ScalarArrayOpExpr
302
303 select * from test_predtest($$
304 select x <= 5, x in (1,3,5)
305 from integers
306 $$);
307
308 select * from test_predtest($$
309 select x <= 5, x in (1,3,5,7)
310 from integers
311 $$);
312
313 select * from test_predtest($$
314 select x <= 5, x in (1,3,5,null)
315 from integers
316 $$);
317
318 select * from test_predtest($$
319 select x in (null,1,3,5,7), x in (1,3,5)
320 from integers
321 $$);
322
323 select * from test_predtest($$
324 select x <= 5, x < all(array[1,3,5])
325 from integers
326 $$);
327
328 select * from test_predtest($$
329 select x <= y, x = any(array[1,3,y])
330 from integers
331 $$);
332
333 -- In these tests, we want to prevent predtest.c from breaking down the
334 -- ScalarArrayOpExpr into an AND/OR tree, so as to exercise the logic
335 -- that handles ScalarArrayOpExpr directly. We use opaque_array() if
336 -- possible, otherwise an array longer than MAX_SAOP_ARRAY_SIZE.
337
338 -- ScalarArrayOpExpr implies scalar IS NOT NULL
339 select * from test_predtest($$
340 select x is not null, x = any(opaque_array(array[1]))
341 from integers
342 $$);
343
344 -- but for ALL, we have to be able to prove the array nonempty
345 select * from test_predtest($$
346 select x is not null, x <> all(opaque_array(array[1]))
347 from integers
348 $$);
349
350 select * from test_predtest($$
351 select x is not null, x <> all(array[
352 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,
353 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,
354 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,
355 79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101
356 ])
357 from integers
358 $$);
359
360 select * from test_predtest($$
361 select x is not null, x <> all(array[
362 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,
363 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,
364 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,
365 79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,y
366 ])
367 from integers
368 $$);
369
370 -- check empty-array cases
371 select * from test_predtest($$
372 select x is not null, x = any(opaque_array(array[]::int[]))
373 from integers
374 $$);
375
376 select * from test_predtest($$
377 select x is not null, x <> all(opaque_array(array[]::int[]))
378 from integers
379 $$);
380
381 -- same thing under a strict function doesn't prove it
382 select * from test_predtest($$
383 select x is not null, strictf(true, x = any(opaque_array(array[]::int[])))
384 from integers
385 $$);
386
387 -- ScalarArrayOpExpr refutes scalar IS NULL
388 select * from test_predtest($$
389 select x is null, x = any(opaque_array(array[1]))
390 from integers
391 $$);
392
393 -- but for ALL, we have to be able to prove the array nonempty
394 select * from test_predtest($$
395 select x is null, x <> all(opaque_array(array[1]))
396 from integers
397 $$);
398
399 select * from test_predtest($$
400 select x is null, x <> all(array[
401 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,
402 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,
403 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,
404 79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101
405 ])
406 from integers
407 $$);
408
409 -- check empty-array cases
410 select * from test_predtest($$
411 select x is null, x = any(opaque_array(array[]::int[]))
412 from integers
413 $$);
414
415 select * from test_predtest($$
416 select x is null, x <> all(opaque_array(array[]::int[]))
417 from integers
418 $$);
419
420 -- same thing under a strict function doesn't prove it
421 select * from test_predtest($$
422 select x is null, strictf(true, x = any(opaque_array(array[]::int[])))
423 from integers
424 $$);
425
426 -- Also, nullness of the scalar weakly refutes a SAOP
427 select * from test_predtest($$
428 select x = any(opaque_array(array[1])), x is null
429 from integers
430 $$);
431
432 -- as does nullness of the array
433 select * from test_predtest($$
434 select x = any(opaque_array(array[y])), array[y] is null
435 from integers
436 $$);
437
438 -- ... unless we need to prove array empty
439 select * from test_predtest($$
440 select x = all(opaque_array(array[1])), x is null
441 from integers
442 $$);