| blob | d47b5f6ec5745b139da58c98853b0815ad591721 |
1 --
2 -- tsrf - targetlist set returning function tests
3 --
4 -- simple srf
5 SELECT generate_series(1, 3);
6 generate_series
7 -----------------
8 1
9 2
10 3
11 (3 rows)
13 -- parallel iteration
14 SELECT generate_series(1, 3), generate_series(3,5);
15 generate_series | generate_series
16 -----------------+-----------------
17 1 | 3
18 2 | 4
19 3 | 5
20 (3 rows)
22 -- parallel iteration, different number of rows
23 SELECT generate_series(1, 2), generate_series(1,4);
24 generate_series | generate_series
25 -----------------+-----------------
26 1 | 1
27 2 | 2
28 | 3
29 | 4
30 (4 rows)
32 -- srf, with SRF argument
33 SELECT generate_series(1, generate_series(1, 3));
34 generate_series
35 -----------------
36 1
37 1
38 2
39 1
40 2
41 3
42 (6 rows)
44 -- but we've traditionally rejected the same in FROM
45 SELECT * FROM generate_series(1, generate_series(1, 3));
46 ERROR: set-returning functions must appear at top level of FROM
47 LINE 1: SELECT * FROM generate_series(1, generate_series(1, 3));
48 ^
49 -- srf, with two SRF arguments
50 SELECT generate_series(generate_series(1,3), generate_series(2, 4));
51 generate_series
52 -----------------
53 1
54 2
55 2
56 3
57 3
58 4
59 (6 rows)
61 -- check proper nesting of SRFs in different expressions
62 explain (verbose, costs off)
63 SELECT generate_series(1, generate_series(1, 3)), generate_series(2, 4);
64 QUERY PLAN
65 --------------------------------------------------------------------------------
66 ProjectSet
67 Output: generate_series(1, (generate_series(1, 3))), (generate_series(2, 4))
68 -> ProjectSet
69 Output: generate_series(1, 3), generate_series(2, 4)
70 -> Result
71 (5 rows)
73 SELECT generate_series(1, generate_series(1, 3)), generate_series(2, 4);
74 generate_series | generate_series
75 -----------------+-----------------
76 1 | 2
77 1 | 3
78 2 | 3
79 1 | 4
80 2 | 4
81 3 | 4
82 (6 rows)
84 CREATE TABLE few(id int, dataa text, datab text);
85 INSERT INTO few VALUES(1, 'a', 'foo'),(2, 'a', 'bar'),(3, 'b', 'bar');
86 -- SRF with a provably-dummy relation
87 explain (verbose, costs off)
88 SELECT unnest(ARRAY[1, 2]) FROM few WHERE false;
89 QUERY PLAN
90 --------------------------------------
91 ProjectSet
92 Output: unnest('{1,2}'::integer[])
93 -> Result
94 One-Time Filter: false
95 (4 rows)
97 SELECT unnest(ARRAY[1, 2]) FROM few WHERE false;
98 unnest
99 --------
100 (0 rows)
102 -- SRF shouldn't prevent upper query from recognizing lower as dummy
103 explain (verbose, costs off)
104 SELECT * FROM few f1,
105 (SELECT unnest(ARRAY[1,2]) FROM few f2 WHERE false OFFSET 0) ss;
106 QUERY PLAN
107 ------------------------------------------------
108 Result
109 Output: f1.id, f1.dataa, f1.datab, ss.unnest
110 One-Time Filter: false
111 (3 rows)
113 SELECT * FROM few f1,
114 (SELECT unnest(ARRAY[1,2]) FROM few f2 WHERE false OFFSET 0) ss;
115 id | dataa | datab | unnest
116 ----+-------+-------+--------
117 (0 rows)
119 -- SRF output order of sorting is maintained, if SRF is not referenced
120 SELECT few.id, generate_series(1,3) g FROM few ORDER BY id DESC;
121 id | g
122 ----+---
123 3 | 1
124 3 | 2
125 3 | 3
126 2 | 1
127 2 | 2
128 2 | 3
129 1 | 1
130 1 | 2
131 1 | 3
132 (9 rows)
134 -- but SRFs can be referenced in sort
135 SELECT few.id, generate_series(1,3) g FROM few ORDER BY id, g DESC;
136 id | g
137 ----+---
138 1 | 3
139 1 | 2
140 1 | 1
141 2 | 3
142 2 | 2
143 2 | 1
144 3 | 3
145 3 | 2
146 3 | 1
147 (9 rows)
149 SELECT few.id, generate_series(1,3) g FROM few ORDER BY id, generate_series(1,3) DESC;
150 id | g
151 ----+---
152 1 | 3
153 1 | 2
154 1 | 1
155 2 | 3
156 2 | 2
157 2 | 1
158 3 | 3
159 3 | 2
160 3 | 1
161 (9 rows)
163 -- it's weird to have ORDER BYs that increase the number of results
164 SELECT few.id FROM few ORDER BY id, generate_series(1,3) DESC;
165 id
166 ----
167 1
168 1
169 1
170 2
171 2
172 2
173 3
174 3
175 3
176 (9 rows)
178 -- SRFs are computed after aggregation
179 SET enable_hashagg TO 0; -- stable output order
180 SELECT few.dataa, count(*), min(id), max(id), unnest('{1,1,3}'::int[]) FROM few WHERE few.id = 1 GROUP BY few.dataa;
181 dataa | count | min | max | unnest
182 -------+-------+-----+-----+--------
183 a | 1 | 1 | 1 | 1
184 a | 1 | 1 | 1 | 1
185 a | 1 | 1 | 1 | 3
186 (3 rows)
188 -- unless referenced in GROUP BY clause
189 SELECT few.dataa, count(*), min(id), max(id), unnest('{1,1,3}'::int[]) FROM few WHERE few.id = 1 GROUP BY few.dataa, unnest('{1,1,3}'::int[]);
190 dataa | count | min | max | unnest
191 -------+-------+-----+-----+--------
192 a | 2 | 1 | 1 | 1
193 a | 1 | 1 | 1 | 3
194 (2 rows)
196 SELECT few.dataa, count(*), min(id), max(id), unnest('{1,1,3}'::int[]) FROM few WHERE few.id = 1 GROUP BY few.dataa, 5;
197 dataa | count | min | max | unnest
198 -------+-------+-----+-----+--------
199 a | 2 | 1 | 1 | 1
200 a | 1 | 1 | 1 | 3
201 (2 rows)
203 RESET enable_hashagg;
204 -- check HAVING works when GROUP BY does [not] reference SRF output
205 SELECT dataa, generate_series(1,1), count(*) FROM few GROUP BY 1 HAVING count(*) > 1;
206 dataa | generate_series | count
207 -------+-----------------+-------
208 a | 1 | 2
209 (1 row)
211 SELECT dataa, generate_series(1,1), count(*) FROM few GROUP BY 1, 2 HAVING count(*) > 1;
212 dataa | generate_series | count
213 -------+-----------------+-------
214 a | 1 | 2
215 (1 row)
217 -- it's weird to have GROUP BYs that increase the number of results
218 SELECT few.dataa, count(*) FROM few WHERE dataa = 'a' GROUP BY few.dataa ORDER BY 2;
219 dataa | count
220 -------+-------
221 a | 2
222 (1 row)
224 SELECT few.dataa, count(*) FROM few WHERE dataa = 'a' GROUP BY few.dataa, unnest('{1,1,3}'::int[]) ORDER BY 2;
225 dataa | count
226 -------+-------
227 a | 2
228 a | 4
229 (2 rows)
231 -- SRFs are not allowed if they'd need to be conditionally executed
232 SELECT q1, case when q1 > 0 then generate_series(1,3) else 0 end FROM int8_tbl;
233 ERROR: set-returning functions are not allowed in CASE
234 LINE 1: SELECT q1, case when q1 > 0 then generate_series(1,3) else 0...
235 ^
236 HINT: You might be able to move the set-returning function into a LATERAL FROM item.
237 SELECT q1, coalesce(generate_series(1,3), 0) FROM int8_tbl;
238 ERROR: set-returning functions are not allowed in COALESCE
239 LINE 1: SELECT q1, coalesce(generate_series(1,3), 0) FROM int8_tbl;
240 ^
241 HINT: You might be able to move the set-returning function into a LATERAL FROM item.
242 -- SRFs are not allowed in aggregate arguments
243 SELECT min(generate_series(1, 3)) FROM few;
244 ERROR: aggregate function calls cannot contain set-returning function calls
245 LINE 1: SELECT min(generate_series(1, 3)) FROM few;
246 ^
247 HINT: You might be able to move the set-returning function into a LATERAL FROM item.
248 -- ... unless they're within a sub-select
249 SELECT sum((3 = ANY(SELECT generate_series(1,4)))::int);
250 sum
251 -----
252 1
253 (1 row)
255 SELECT sum((3 = ANY(SELECT lag(x) over(order by x)
256 FROM generate_series(1,4) x))::int);
257 sum
258 -----
259 1
260 (1 row)
262 -- SRFs are not allowed in window function arguments, either
263 SELECT min(generate_series(1, 3)) OVER() FROM few;
264 ERROR: window function calls cannot contain set-returning function calls
265 LINE 1: SELECT min(generate_series(1, 3)) OVER() FROM few;
266 ^
267 HINT: You might be able to move the set-returning function into a LATERAL FROM item.
268 -- SRFs are normally computed after window functions
269 SELECT id,lag(id) OVER(), count(*) OVER(), generate_series(1,3) FROM few;
270 id | lag | count | generate_series
271 ----+-----+-------+-----------------
272 1 | | 3 | 1
273 1 | | 3 | 2
274 1 | | 3 | 3
275 2 | 1 | 3 | 1
276 2 | 1 | 3 | 2
277 2 | 1 | 3 | 3
278 3 | 2 | 3 | 1
279 3 | 2 | 3 | 2
280 3 | 2 | 3 | 3
281 (9 rows)
283 -- unless referencing SRFs
284 SELECT SUM(count(*)) OVER(PARTITION BY generate_series(1,3) ORDER BY generate_series(1,3)), generate_series(1,3) g FROM few GROUP BY g;
285 sum | g
286 -----+---
287 3 | 1
288 3 | 2
289 3 | 3
290 (3 rows)
292 -- sorting + grouping
293 SELECT few.dataa, count(*), min(id), max(id), generate_series(1,3) FROM few GROUP BY few.dataa ORDER BY 5, 1;
294 dataa | count | min | max | generate_series
295 -------+-------+-----+-----+-----------------
296 a | 2 | 1 | 2 | 1
297 b | 1 | 3 | 3 | 1
298 a | 2 | 1 | 2 | 2
299 b | 1 | 3 | 3 | 2
300 a | 2 | 1 | 2 | 3
301 b | 1 | 3 | 3 | 3
302 (6 rows)
304 -- grouping sets are a bit special, they produce NULLs in columns not actually NULL
305 set enable_hashagg = false;
306 SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(dataa, datab);
307 dataa | b | g | count
308 -------+-----+---+-------
309 a | bar | 1 | 1
310 a | bar | 2 | 1
311 a | foo | 1 | 1
312 a | foo | 2 | 1
313 a | | 1 | 2
314 a | | 2 | 2
315 b | bar | 1 | 1
316 b | bar | 2 | 1
317 b | | 1 | 1
318 b | | 2 | 1
319 | | 1 | 3
320 | | 2 | 3
321 | bar | 1 | 2
322 | bar | 2 | 2
323 | foo | 1 | 1
324 | foo | 2 | 1
325 (16 rows)
327 SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(dataa, datab) ORDER BY dataa;
328 dataa | b | g | count
329 -------+-----+---+-------
330 a | bar | 1 | 1
331 a | bar | 2 | 1
332 a | foo | 1 | 1
333 a | foo | 2 | 1
334 a | | 1 | 2
335 a | | 2 | 2
336 b | bar | 1 | 1
337 b | bar | 2 | 1
338 b | | 1 | 1
339 b | | 2 | 1
340 | | 1 | 3
341 | | 2 | 3
342 | bar | 1 | 2
343 | bar | 2 | 2
344 | foo | 1 | 1
345 | foo | 2 | 1
346 (16 rows)
348 SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(dataa, datab) ORDER BY g;
349 dataa | b | g | count
350 -------+-----+---+-------
351 a | bar | 1 | 1
352 a | foo | 1 | 1
353 a | | 1 | 2
354 b | bar | 1 | 1
355 b | | 1 | 1
356 | | 1 | 3
357 | bar | 1 | 2
358 | foo | 1 | 1
359 | foo | 2 | 1
360 a | bar | 2 | 1
361 b | | 2 | 1
362 a | foo | 2 | 1
363 | bar | 2 | 2
364 a | | 2 | 2
365 | | 2 | 3
366 b | bar | 2 | 1
367 (16 rows)
369 SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(dataa, datab, g);
370 dataa | b | g | count
371 -------+-----+---+-------
372 a | bar | 1 | 1
373 a | bar | 2 | 1
374 a | bar | | 2
375 a | foo | 1 | 1
376 a | foo | 2 | 1
377 a | foo | | 2
378 a | | | 4
379 b | bar | 1 | 1
380 b | bar | 2 | 1
381 b | bar | | 2
382 b | | | 2
383 | | | 6
384 | bar | 1 | 2
385 | bar | 2 | 2
386 | bar | | 4
387 | foo | 1 | 1
388 | foo | 2 | 1
389 | foo | | 2
390 a | | 1 | 2
391 b | | 1 | 1
392 | | 1 | 3
393 a | | 2 | 2
394 b | | 2 | 1
395 | | 2 | 3
396 (24 rows)
398 SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(dataa, datab, g) ORDER BY dataa;
399 dataa | b | g | count
400 -------+-----+---+-------
401 a | foo | | 2
402 a | | | 4
403 a | | 2 | 2
404 a | bar | 1 | 1
405 a | bar | 2 | 1
406 a | bar | | 2
407 a | foo | 1 | 1
408 a | foo | 2 | 1
409 a | | 1 | 2
410 b | bar | 1 | 1
411 b | | | 2
412 b | | 1 | 1
413 b | bar | 2 | 1
414 b | bar | | 2
415 b | | 2 | 1
416 | | 2 | 3
417 | | | 6
418 | bar | 1 | 2
419 | bar | 2 | 2
420 | bar | | 4
421 | foo | 1 | 1
422 | foo | 2 | 1
423 | foo | | 2
424 | | 1 | 3
425 (24 rows)
427 SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(dataa, datab, g) ORDER BY g;
428 dataa | b | g | count
429 -------+-----+---+-------
430 a | bar | 1 | 1
431 a | foo | 1 | 1
432 b | bar | 1 | 1
433 | bar | 1 | 2
434 | foo | 1 | 1
435 a | | 1 | 2
436 b | | 1 | 1
437 | | 1 | 3
438 a | | 2 | 2
439 b | | 2 | 1
440 | bar | 2 | 2
441 | | 2 | 3
442 | foo | 2 | 1
443 a | bar | 2 | 1
444 a | foo | 2 | 1
445 b | bar | 2 | 1
446 a | | | 4
447 b | bar | | 2
448 b | | | 2
449 | | | 6
450 a | foo | | 2
451 a | bar | | 2
452 | bar | | 4
453 | foo | | 2
454 (24 rows)
456 reset enable_hashagg;
457 -- case with degenerate ORDER BY
458 explain (verbose, costs off)
459 select 'foo' as f, generate_series(1,2) as g from few order by 1;
460 QUERY PLAN
461 ----------------------------------------------
462 ProjectSet
463 Output: 'foo'::text, generate_series(1, 2)
464 -> Seq Scan on public.few
465 Output: id, dataa, datab
466 (4 rows)
468 select 'foo' as f, generate_series(1,2) as g from few order by 1;
469 f | g
470 -----+---
471 foo | 1
472 foo | 2
473 foo | 1
474 foo | 2
475 foo | 1
476 foo | 2
477 (6 rows)
479 -- data modification
480 CREATE TABLE fewmore AS SELECT generate_series(1,3) AS data;
481 INSERT INTO fewmore VALUES(generate_series(4,5));
482 SELECT * FROM fewmore;
483 data
484 ------
485 1
486 2
487 3
488 4
489 5
490 (5 rows)
492 -- SRFs are not allowed in UPDATE (they once were, but it was nonsense)
493 UPDATE fewmore SET data = generate_series(4,9);
494 ERROR: set-returning functions are not allowed in UPDATE
495 LINE 1: UPDATE fewmore SET data = generate_series(4,9);
496 ^
497 -- SRFs are not allowed in RETURNING
498 INSERT INTO fewmore VALUES(1) RETURNING generate_series(1,3);
499 ERROR: set-returning functions are not allowed in RETURNING
500 LINE 1: INSERT INTO fewmore VALUES(1) RETURNING generate_series(1,3)...
501 ^
502 -- nor standalone VALUES (but surely this is a bug?)
503 VALUES(1, generate_series(1,2));
504 ERROR: set-returning functions are not allowed in VALUES
505 LINE 1: VALUES(1, generate_series(1,2));
506 ^
507 -- We allow tSRFs that are not at top level
508 SELECT int4mul(generate_series(1,2), 10);
509 int4mul
510 ---------
511 10
512 20
513 (2 rows)
515 SELECT generate_series(1,3) IS DISTINCT FROM 2;
516 ?column?
517 ----------
518 t
519 f
520 t
521 (3 rows)
523 -- but SRFs in function RTEs must be at top level (annoying restriction)
524 SELECT * FROM int4mul(generate_series(1,2), 10);
525 ERROR: set-returning functions must appear at top level of FROM
526 LINE 1: SELECT * FROM int4mul(generate_series(1,2), 10);
527 ^
528 -- DISTINCT ON is evaluated before tSRF evaluation if SRF is not
529 -- referenced either in ORDER BY or in the DISTINCT ON list. The ORDER
530 -- BY reference can be implicitly generated, if there's no other ORDER BY.
531 -- implicit reference (via implicit ORDER) to all columns
532 SELECT DISTINCT ON (a) a, b, generate_series(1,3) g
533 FROM (VALUES (3, 2), (3,1), (1,1), (1,4), (5,3), (5,1)) AS t(a, b);
534 a | b | g
535 ---+---+---
536 1 | 1 | 1
537 3 | 2 | 1
538 5 | 3 | 1
539 (3 rows)
541 -- unreferenced in DISTINCT ON or ORDER BY
542 SELECT DISTINCT ON (a) a, b, generate_series(1,3) g
543 FROM (VALUES (3, 2), (3,1), (1,1), (1,4), (5,3), (5,1)) AS t(a, b)
544 ORDER BY a, b DESC;
545 a | b | g
546 ---+---+---
547 1 | 4 | 1
548 1 | 4 | 2
549 1 | 4 | 3
550 3 | 2 | 1
551 3 | 2 | 2
552 3 | 2 | 3
553 5 | 3 | 1
554 5 | 3 | 2
555 5 | 3 | 3
556 (9 rows)
558 -- referenced in ORDER BY
559 SELECT DISTINCT ON (a) a, b, generate_series(1,3) g
560 FROM (VALUES (3, 2), (3,1), (1,1), (1,4), (5,3), (5,1)) AS t(a, b)
561 ORDER BY a, b DESC, g DESC;
562 a | b | g
563 ---+---+---
564 1 | 4 | 3
565 3 | 2 | 3
566 5 | 3 | 3
567 (3 rows)
569 -- referenced in ORDER BY and DISTINCT ON
570 SELECT DISTINCT ON (a, b, g) a, b, generate_series(1,3) g
571 FROM (VALUES (3, 2), (3,1), (1,1), (1,4), (5,3), (5,1)) AS t(a, b)
572 ORDER BY a, b DESC, g DESC;
573 a | b | g
574 ---+---+---
575 1 | 4 | 3
576 1 | 4 | 2
577 1 | 4 | 1
578 1 | 1 | 3
579 1 | 1 | 2
580 1 | 1 | 1
581 3 | 2 | 3
582 3 | 2 | 2
583 3 | 2 | 1
584 3 | 1 | 3
585 3 | 1 | 2
586 3 | 1 | 1
587 5 | 3 | 3
588 5 | 3 | 2
589 5 | 3 | 1
590 5 | 1 | 3
591 5 | 1 | 2
592 5 | 1 | 1
593 (18 rows)
595 -- only SRF mentioned in DISTINCT ON
596 SELECT DISTINCT ON (g) a, b, generate_series(1,3) g
597 FROM (VALUES (3, 2), (3,1), (1,1), (1,4), (5,3), (5,1)) AS t(a, b);
598 a | b | g
599 ---+---+---
600 3 | 2 | 1
601 5 | 1 | 2
602 3 | 1 | 3
603 (3 rows)
605 -- LIMIT / OFFSET is evaluated after SRF evaluation
606 SELECT a, generate_series(1,2) FROM (VALUES(1),(2),(3)) r(a) LIMIT 2 OFFSET 2;
607 a | generate_series
608 ---+-----------------
609 2 | 1
610 2 | 2
611 (2 rows)
613 -- SRFs are not allowed in LIMIT.
614 SELECT 1 LIMIT generate_series(1,3);
615 ERROR: set-returning functions are not allowed in LIMIT
616 LINE 1: SELECT 1 LIMIT generate_series(1,3);
617 ^
618 -- tSRF in correlated subquery, referencing table outside
619 SELECT (SELECT generate_series(1,3) LIMIT 1 OFFSET few.id) FROM few;
620 generate_series
621 -----------------
622 2
623 3
625 (3 rows)
627 -- tSRF in correlated subquery, referencing SRF outside
628 SELECT (SELECT generate_series(1,3) LIMIT 1 OFFSET g.i) FROM generate_series(0,3) g(i);
629 generate_series
630 -----------------
631 1
632 2
633 3
635 (4 rows)
637 -- Operators can return sets too
638 CREATE OPERATOR |@| (PROCEDURE = unnest, RIGHTARG = ANYARRAY);
639 SELECT |@|ARRAY[1,2,3];
640 ?column?
641 ----------
642 1
643 2
644 3
645 (3 rows)
647 -- Some fun cases involving duplicate SRF calls
648 explain (verbose, costs off)
649 select generate_series(1,3) as x, generate_series(1,3) + 1 as xp1;
650 QUERY PLAN
651 ------------------------------------------------------------------
652 Result
653 Output: (generate_series(1, 3)), ((generate_series(1, 3)) + 1)
654 -> ProjectSet
655 Output: generate_series(1, 3)
656 -> Result
657 (5 rows)
659 select generate_series(1,3) as x, generate_series(1,3) + 1 as xp1;
660 x | xp1
661 ---+-----
662 1 | 2
663 2 | 3
664 3 | 4
665 (3 rows)
667 explain (verbose, costs off)
668 select generate_series(1,3)+1 order by generate_series(1,3);
669 QUERY PLAN
670 ------------------------------------------------------------------------
671 Sort
672 Output: (((generate_series(1, 3)) + 1)), (generate_series(1, 3))
673 Sort Key: (generate_series(1, 3))
674 -> Result
675 Output: ((generate_series(1, 3)) + 1), (generate_series(1, 3))
676 -> ProjectSet
677 Output: generate_series(1, 3)
678 -> Result
679 (8 rows)
681 select generate_series(1,3)+1 order by generate_series(1,3);
682 ?column?
683 ----------
684 2
685 3
686 4
687 (3 rows)
689 -- Check that SRFs of same nesting level run in lockstep
690 explain (verbose, costs off)
691 select generate_series(1,3) as x, generate_series(3,6) + 1 as y;
692 QUERY PLAN
693 ------------------------------------------------------------------
694 Result
695 Output: (generate_series(1, 3)), ((generate_series(3, 6)) + 1)
696 -> ProjectSet
697 Output: generate_series(1, 3), generate_series(3, 6)
698 -> Result
699 (5 rows)
701 select generate_series(1,3) as x, generate_series(3,6) + 1 as y;
702 x | y
703 ---+---
704 1 | 4
705 2 | 5
706 3 | 6
707 | 7
708 (4 rows)
710 -- Clean up
711 DROP TABLE few;
712 DROP TABLE fewmore;