| blob | f4267c002d787f1b93ae06aeca3bad1345fbdb0e |
1 --
2 -- LIMIT
3 -- Check the LIMIT/OFFSET feature of SELECT
4 --
5 SELECT ''::text AS two, unique1, unique2, stringu1
6 FROM onek WHERE unique1 > 50
7 ORDER BY unique1 LIMIT 2;
8 two | unique1 | unique2 | stringu1
9 -----+---------+---------+----------
10 | 51 | 76 | ZBAAAA
11 | 52 | 985 | ACAAAA
12 (2 rows)
14 SELECT ''::text AS five, unique1, unique2, stringu1
15 FROM onek WHERE unique1 > 60
16 ORDER BY unique1 LIMIT 5;
17 five | unique1 | unique2 | stringu1
18 ------+---------+---------+----------
19 | 61 | 560 | JCAAAA
20 | 62 | 633 | KCAAAA
21 | 63 | 296 | LCAAAA
22 | 64 | 479 | MCAAAA
23 | 65 | 64 | NCAAAA
24 (5 rows)
26 SELECT ''::text AS two, unique1, unique2, stringu1
27 FROM onek WHERE unique1 > 60 AND unique1 < 63
28 ORDER BY unique1 LIMIT 5;
29 two | unique1 | unique2 | stringu1
30 -----+---------+---------+----------
31 | 61 | 560 | JCAAAA
32 | 62 | 633 | KCAAAA
33 (2 rows)
35 SELECT ''::text AS three, unique1, unique2, stringu1
36 FROM onek WHERE unique1 > 100
37 ORDER BY unique1 LIMIT 3 OFFSET 20;
38 three | unique1 | unique2 | stringu1
39 -------+---------+---------+----------
40 | 121 | 700 | REAAAA
41 | 122 | 519 | SEAAAA
42 | 123 | 777 | TEAAAA
43 (3 rows)
45 SELECT ''::text AS zero, unique1, unique2, stringu1
46 FROM onek WHERE unique1 < 50
47 ORDER BY unique1 DESC LIMIT 8 OFFSET 99;
48 zero | unique1 | unique2 | stringu1
49 ------+---------+---------+----------
50 (0 rows)
52 SELECT ''::text AS eleven, unique1, unique2, stringu1
53 FROM onek WHERE unique1 < 50
54 ORDER BY unique1 DESC LIMIT 20 OFFSET 39;
55 eleven | unique1 | unique2 | stringu1
56 --------+---------+---------+----------
57 | 10 | 520 | KAAAAA
58 | 9 | 49 | JAAAAA
59 | 8 | 653 | IAAAAA
60 | 7 | 647 | HAAAAA
61 | 6 | 978 | GAAAAA
62 | 5 | 541 | FAAAAA
63 | 4 | 833 | EAAAAA
64 | 3 | 431 | DAAAAA
65 | 2 | 326 | CAAAAA
66 | 1 | 214 | BAAAAA
67 | 0 | 998 | AAAAAA
68 (11 rows)
70 SELECT ''::text AS ten, unique1, unique2, stringu1
71 FROM onek
72 ORDER BY unique1 OFFSET 990;
73 ten | unique1 | unique2 | stringu1
74 -----+---------+---------+----------
75 | 990 | 369 | CMAAAA
76 | 991 | 426 | DMAAAA
77 | 992 | 363 | EMAAAA
78 | 993 | 661 | FMAAAA
79 | 994 | 695 | GMAAAA
80 | 995 | 144 | HMAAAA
81 | 996 | 258 | IMAAAA
82 | 997 | 21 | JMAAAA
83 | 998 | 549 | KMAAAA
84 | 999 | 152 | LMAAAA
85 (10 rows)
87 SELECT ''::text AS five, unique1, unique2, stringu1
88 FROM onek
89 ORDER BY unique1 OFFSET 990 LIMIT 5;
90 five | unique1 | unique2 | stringu1
91 ------+---------+---------+----------
92 | 990 | 369 | CMAAAA
93 | 991 | 426 | DMAAAA
94 | 992 | 363 | EMAAAA
95 | 993 | 661 | FMAAAA
96 | 994 | 695 | GMAAAA
97 (5 rows)
99 SELECT ''::text AS five, unique1, unique2, stringu1
100 FROM onek
101 ORDER BY unique1 LIMIT 5 OFFSET 900;
102 five | unique1 | unique2 | stringu1
103 ------+---------+---------+----------
104 | 900 | 913 | QIAAAA
105 | 901 | 931 | RIAAAA
106 | 902 | 702 | SIAAAA
107 | 903 | 641 | TIAAAA
108 | 904 | 793 | UIAAAA
109 (5 rows)
111 -- Test null limit and offset. The planner would discard a simple null
112 -- constant, so to ensure executor is exercised, do this:
113 select * from int8_tbl limit (case when random() < 0.5 then null::bigint end);
114 q1 | q2
115 ------------------+-------------------
116 123 | 456
117 123 | 4567890123456789
118 4567890123456789 | 123
119 4567890123456789 | 4567890123456789
120 4567890123456789 | -4567890123456789
121 (5 rows)
123 select * from int8_tbl offset (case when random() < 0.5 then null::bigint end);
124 q1 | q2
125 ------------------+-------------------
126 123 | 456
127 123 | 4567890123456789
128 4567890123456789 | 123
129 4567890123456789 | 4567890123456789
130 4567890123456789 | -4567890123456789
131 (5 rows)
133 -- Test assorted cases involving backwards fetch from a LIMIT plan node
134 begin;
135 declare c1 cursor for select * from int8_tbl limit 10;
136 fetch all in c1;
137 q1 | q2
138 ------------------+-------------------
139 123 | 456
140 123 | 4567890123456789
141 4567890123456789 | 123
142 4567890123456789 | 4567890123456789
143 4567890123456789 | -4567890123456789
144 (5 rows)
146 fetch 1 in c1;
147 q1 | q2
148 ----+----
149 (0 rows)
151 fetch backward 1 in c1;
152 q1 | q2
153 ------------------+-------------------
154 4567890123456789 | -4567890123456789
155 (1 row)
157 fetch backward all in c1;
158 q1 | q2
159 ------------------+------------------
160 4567890123456789 | 4567890123456789
161 4567890123456789 | 123
162 123 | 4567890123456789
163 123 | 456
164 (4 rows)
166 fetch backward 1 in c1;
167 q1 | q2
168 ----+----
169 (0 rows)
171 fetch all in c1;
172 q1 | q2
173 ------------------+-------------------
174 123 | 456
175 123 | 4567890123456789
176 4567890123456789 | 123
177 4567890123456789 | 4567890123456789
178 4567890123456789 | -4567890123456789
179 (5 rows)
181 declare c2 cursor for select * from int8_tbl limit 3;
182 fetch all in c2;
183 q1 | q2
184 ------------------+------------------
185 123 | 456
186 123 | 4567890123456789
187 4567890123456789 | 123
188 (3 rows)
190 fetch 1 in c2;
191 q1 | q2
192 ----+----
193 (0 rows)
195 fetch backward 1 in c2;
196 q1 | q2
197 ------------------+-----
198 4567890123456789 | 123
199 (1 row)
201 fetch backward all in c2;
202 q1 | q2
203 -----+------------------
204 123 | 4567890123456789
205 123 | 456
206 (2 rows)
208 fetch backward 1 in c2;
209 q1 | q2
210 ----+----
211 (0 rows)
213 fetch all in c2;
214 q1 | q2
215 ------------------+------------------
216 123 | 456
217 123 | 4567890123456789
218 4567890123456789 | 123
219 (3 rows)
221 declare c3 cursor for select * from int8_tbl offset 3;
222 fetch all in c3;
223 q1 | q2
224 ------------------+-------------------
225 4567890123456789 | 4567890123456789
226 4567890123456789 | -4567890123456789
227 (2 rows)
229 fetch 1 in c3;
230 q1 | q2
231 ----+----
232 (0 rows)
234 fetch backward 1 in c3;
235 q1 | q2
236 ------------------+-------------------
237 4567890123456789 | -4567890123456789
238 (1 row)
240 fetch backward all in c3;
241 q1 | q2
242 ------------------+------------------
243 4567890123456789 | 4567890123456789
244 (1 row)
246 fetch backward 1 in c3;
247 q1 | q2
248 ----+----
249 (0 rows)
251 fetch all in c3;
252 q1 | q2
253 ------------------+-------------------
254 4567890123456789 | 4567890123456789
255 4567890123456789 | -4567890123456789
256 (2 rows)
258 declare c4 cursor for select * from int8_tbl offset 10;
259 fetch all in c4;
260 q1 | q2
261 ----+----
262 (0 rows)
264 fetch 1 in c4;
265 q1 | q2
266 ----+----
267 (0 rows)
269 fetch backward 1 in c4;
270 q1 | q2
271 ----+----
272 (0 rows)
274 fetch backward all in c4;
275 q1 | q2
276 ----+----
277 (0 rows)
279 fetch backward 1 in c4;
280 q1 | q2
281 ----+----
282 (0 rows)
284 fetch all in c4;
285 q1 | q2
286 ----+----
287 (0 rows)
289 declare c5 cursor for select * from int8_tbl order by q1 fetch first 2 rows with ties;
290 fetch all in c5;
291 q1 | q2
292 -----+------------------
293 123 | 456
294 123 | 4567890123456789
295 (2 rows)
297 fetch 1 in c5;
298 q1 | q2
299 ----+----
300 (0 rows)
302 fetch backward 1 in c5;
303 q1 | q2
304 -----+------------------
305 123 | 4567890123456789
306 (1 row)
308 fetch backward 1 in c5;
309 q1 | q2
310 -----+-----
311 123 | 456
312 (1 row)
314 fetch all in c5;
315 q1 | q2
316 -----+------------------
317 123 | 4567890123456789
318 (1 row)
320 fetch backward all in c5;
321 q1 | q2
322 -----+------------------
323 123 | 4567890123456789
324 123 | 456
325 (2 rows)
327 fetch all in c5;
328 q1 | q2
329 -----+------------------
330 123 | 456
331 123 | 4567890123456789
332 (2 rows)
334 fetch backward all in c5;
335 q1 | q2
336 -----+------------------
337 123 | 4567890123456789
338 123 | 456
339 (2 rows)
341 rollback;
342 -- Stress test for variable LIMIT in conjunction with bounded-heap sorting
343 SELECT
344 (SELECT n
345 FROM (VALUES (1)) AS x,
346 (SELECT n FROM generate_series(1,10) AS n
347 ORDER BY n LIMIT 1 OFFSET s-1) AS y) AS z
348 FROM generate_series(1,10) AS s;
349 z
350 ----
351 1
352 2
353 3
354 4
355 5
356 6
357 7
358 8
359 9
360 10
361 (10 rows)
363 --
364 -- Test behavior of volatile and set-returning functions in conjunction
365 -- with ORDER BY and LIMIT.
366 --
367 create temp sequence testseq;
368 explain (verbose, costs off)
369 select unique1, unique2, nextval('testseq')
370 from tenk1 order by unique2 limit 10;
371 QUERY PLAN
372 ----------------------------------------------------------------
373 Limit
374 Output: unique1, unique2, (nextval('testseq'::regclass))
375 -> Index Scan using tenk1_unique2 on public.tenk1
376 Output: unique1, unique2, nextval('testseq'::regclass)
377 (4 rows)
379 select unique1, unique2, nextval('testseq')
380 from tenk1 order by unique2 limit 10;
381 unique1 | unique2 | nextval
382 ---------+---------+---------
383 8800 | 0 | 1
384 1891 | 1 | 2
385 3420 | 2 | 3
386 9850 | 3 | 4
387 7164 | 4 | 5
388 8009 | 5 | 6
389 5057 | 6 | 7
390 6701 | 7 | 8
391 4321 | 8 | 9
392 3043 | 9 | 10
393 (10 rows)
395 select currval('testseq');
396 currval
397 ---------
398 10
399 (1 row)
401 explain (verbose, costs off)
402 select unique1, unique2, nextval('testseq')
403 from tenk1 order by tenthous limit 10;
404 QUERY PLAN
405 --------------------------------------------------------------------------
406 Limit
407 Output: unique1, unique2, (nextval('testseq'::regclass)), tenthous
408 -> Result
409 Output: unique1, unique2, nextval('testseq'::regclass), tenthous
410 -> Sort
411 Output: unique1, unique2, tenthous
412 Sort Key: tenk1.tenthous
413 -> Seq Scan on public.tenk1
414 Output: unique1, unique2, tenthous
415 (9 rows)
417 select unique1, unique2, nextval('testseq')
418 from tenk1 order by tenthous limit 10;
419 unique1 | unique2 | nextval
420 ---------+---------+---------
421 0 | 9998 | 11
422 1 | 2838 | 12
423 2 | 2716 | 13
424 3 | 5679 | 14
425 4 | 1621 | 15
426 5 | 5557 | 16
427 6 | 2855 | 17
428 7 | 8518 | 18
429 8 | 5435 | 19
430 9 | 4463 | 20
431 (10 rows)
433 select currval('testseq');
434 currval
435 ---------
436 20
437 (1 row)
439 explain (verbose, costs off)
440 select unique1, unique2, generate_series(1,10)
441 from tenk1 order by unique2 limit 7;
442 QUERY PLAN
443 -------------------------------------------------------------------------------------------------------------------------------------------------------------
444 Limit
445 Output: unique1, unique2, (generate_series(1, 10))
446 -> ProjectSet
447 Output: unique1, unique2, generate_series(1, 10)
448 -> Index Scan using tenk1_unique2 on public.tenk1
449 Output: unique1, unique2, two, four, ten, twenty, hundred, thousand, twothousand, fivethous, tenthous, odd, even, stringu1, stringu2, string4
450 (6 rows)
452 select unique1, unique2, generate_series(1,10)
453 from tenk1 order by unique2 limit 7;
454 unique1 | unique2 | generate_series
455 ---------+---------+-----------------
456 8800 | 0 | 1
457 8800 | 0 | 2
458 8800 | 0 | 3
459 8800 | 0 | 4
460 8800 | 0 | 5
461 8800 | 0 | 6
462 8800 | 0 | 7
463 (7 rows)
465 explain (verbose, costs off)
466 select unique1, unique2, generate_series(1,10)
467 from tenk1 order by tenthous limit 7;
468 QUERY PLAN
469 --------------------------------------------------------------------
470 Limit
471 Output: unique1, unique2, (generate_series(1, 10)), tenthous
472 -> ProjectSet
473 Output: unique1, unique2, generate_series(1, 10), tenthous
474 -> Sort
475 Output: unique1, unique2, tenthous
476 Sort Key: tenk1.tenthous
477 -> Seq Scan on public.tenk1
478 Output: unique1, unique2, tenthous
479 (9 rows)
481 select unique1, unique2, generate_series(1,10)
482 from tenk1 order by tenthous limit 7;
483 unique1 | unique2 | generate_series
484 ---------+---------+-----------------
485 0 | 9998 | 1
486 0 | 9998 | 2
487 0 | 9998 | 3
488 0 | 9998 | 4
489 0 | 9998 | 5
490 0 | 9998 | 6
491 0 | 9998 | 7
492 (7 rows)
494 -- use of random() is to keep planner from folding the expressions together
495 explain (verbose, costs off)
496 select generate_series(0,2) as s1, generate_series((random()*.1)::int,2) as s2;
497 QUERY PLAN
498 ------------------------------------------------------------------------------------------------------
499 ProjectSet
500 Output: generate_series(0, 2), generate_series(((random() * '0.1'::double precision))::integer, 2)
501 -> Result
502 (3 rows)
504 select generate_series(0,2) as s1, generate_series((random()*.1)::int,2) as s2;
505 s1 | s2
506 ----+----
507 0 | 0
508 1 | 1
509 2 | 2
510 (3 rows)
512 explain (verbose, costs off)
513 select generate_series(0,2) as s1, generate_series((random()*.1)::int,2) as s2
514 order by s2 desc;
515 QUERY PLAN
516 ------------------------------------------------------------------------------------------------------------
517 Sort
518 Output: (generate_series(0, 2)), (generate_series(((random() * '0.1'::double precision))::integer, 2))
519 Sort Key: (generate_series(((random() * '0.1'::double precision))::integer, 2)) DESC
520 -> ProjectSet
521 Output: generate_series(0, 2), generate_series(((random() * '0.1'::double precision))::integer, 2)
522 -> Result
523 (6 rows)
525 select generate_series(0,2) as s1, generate_series((random()*.1)::int,2) as s2
526 order by s2 desc;
527 s1 | s2
528 ----+----
529 2 | 2
530 1 | 1
531 0 | 0
532 (3 rows)
534 -- test for failure to set all aggregates' aggtranstype
535 explain (verbose, costs off)
536 select sum(tenthous) as s1, sum(tenthous) + random()*0 as s2
537 from tenk1 group by thousand order by thousand limit 3;
538 QUERY PLAN
539 -------------------------------------------------------------------------------------------------------------------
540 Limit
541 Output: (sum(tenthous)), (((sum(tenthous))::double precision + (random() * '0'::double precision))), thousand
542 -> GroupAggregate
543 Output: sum(tenthous), ((sum(tenthous))::double precision + (random() * '0'::double precision)), thousand
544 Group Key: tenk1.thousand
545 -> Index Only Scan using tenk1_thous_tenthous on public.tenk1
546 Output: thousand, tenthous
547 (7 rows)
549 select sum(tenthous) as s1, sum(tenthous) + random()*0 as s2
550 from tenk1 group by thousand order by thousand limit 3;
551 s1 | s2
552 -------+-------
553 45000 | 45000
554 45010 | 45010
555 45020 | 45020
556 (3 rows)
558 --
559 -- FETCH FIRST
560 -- Check the WITH TIES clause
561 --
562 SELECT thousand
563 FROM onek WHERE thousand < 5
564 ORDER BY thousand FETCH FIRST 2 ROW WITH TIES;
565 thousand
566 ----------
567 0
568 0
569 0
570 0
571 0
572 0
573 0
574 0
575 0
576 0
577 (10 rows)
579 SELECT thousand
580 FROM onek WHERE thousand < 5
581 ORDER BY thousand FETCH FIRST ROWS WITH TIES;
582 thousand
583 ----------
584 0
585 0
586 0
587 0
588 0
589 0
590 0
591 0
592 0
593 0
594 (10 rows)
596 SELECT thousand
597 FROM onek WHERE thousand < 5
598 ORDER BY thousand FETCH FIRST 1 ROW WITH TIES;
599 thousand
600 ----------
601 0
602 0
603 0
604 0
605 0
606 0
607 0
608 0
609 0
610 0
611 (10 rows)
613 SELECT thousand
614 FROM onek WHERE thousand < 5
615 ORDER BY thousand FETCH FIRST 2 ROW ONLY;
616 thousand
617 ----------
618 0
619 0
620 (2 rows)
622 -- SKIP LOCKED and WITH TIES are incompatible
623 SELECT thousand
624 FROM onek WHERE thousand < 5
625 ORDER BY thousand FETCH FIRST 1 ROW WITH TIES FOR UPDATE SKIP LOCKED;
626 ERROR: SKIP LOCKED and WITH TIES options cannot be used together
627 LINE 3: ORDER BY thousand FETCH FIRST 1 ROW WITH TIES FOR UPDATE S...
628 ^
629 -- should fail
630 SELECT ''::text AS two, unique1, unique2, stringu1
631 FROM onek WHERE unique1 > 50
632 FETCH FIRST 2 ROW WITH TIES;
633 ERROR: WITH TIES cannot be specified without ORDER BY clause
634 LINE 3: FETCH FIRST 2 ROW WITH TIES;
635 ^
636 -- test ruleutils
637 CREATE VIEW limit_thousand_v_1 AS SELECT thousand FROM onek WHERE thousand < 995
638 ORDER BY thousand FETCH FIRST 5 ROWS WITH TIES OFFSET 10;
639 \d+ limit_thousand_v_1
640 View "public.limit_thousand_v_1"
641 Column | Type | Collation | Nullable | Default | Storage | Description
642 ----------+---------+-----------+----------+---------+---------+-------------
643 thousand | integer | | | | plain |
644 View definition:
645 SELECT thousand
646 FROM onek
647 WHERE thousand < 995
648 ORDER BY thousand
649 OFFSET 10
650 FETCH FIRST 5 ROWS WITH TIES;
652 CREATE VIEW limit_thousand_v_2 AS SELECT thousand FROM onek WHERE thousand < 995
653 ORDER BY thousand OFFSET 10 FETCH FIRST 5 ROWS ONLY;
654 \d+ limit_thousand_v_2
655 View "public.limit_thousand_v_2"
656 Column | Type | Collation | Nullable | Default | Storage | Description
657 ----------+---------+-----------+----------+---------+---------+-------------
658 thousand | integer | | | | plain |
659 View definition:
660 SELECT thousand
661 FROM onek
662 WHERE thousand < 995
663 ORDER BY thousand
664 OFFSET 10
665 LIMIT 5;
667 CREATE VIEW limit_thousand_v_3 AS SELECT thousand FROM onek WHERE thousand < 995
668 ORDER BY thousand FETCH FIRST NULL ROWS WITH TIES; -- fails
669 ERROR: row count cannot be null in FETCH FIRST ... WITH TIES clause
670 CREATE VIEW limit_thousand_v_3 AS SELECT thousand FROM onek WHERE thousand < 995
671 ORDER BY thousand FETCH FIRST (NULL+1) ROWS WITH TIES;
672 \d+ limit_thousand_v_3
673 View "public.limit_thousand_v_3"
674 Column | Type | Collation | Nullable | Default | Storage | Description
675 ----------+---------+-----------+----------+---------+---------+-------------
676 thousand | integer | | | | plain |
677 View definition:
678 SELECT thousand
679 FROM onek
680 WHERE thousand < 995
681 ORDER BY thousand
682 FETCH FIRST (NULL::integer + 1) ROWS WITH TIES;
684 CREATE VIEW limit_thousand_v_4 AS SELECT thousand FROM onek WHERE thousand < 995
685 ORDER BY thousand FETCH FIRST NULL ROWS ONLY;
686 \d+ limit_thousand_v_4
687 View "public.limit_thousand_v_4"
688 Column | Type | Collation | Nullable | Default | Storage | Description
689 ----------+---------+-----------+----------+---------+---------+-------------
690 thousand | integer | | | | plain |
691 View definition:
692 SELECT thousand
693 FROM onek
694 WHERE thousand < 995
695 ORDER BY thousand
696 LIMIT ALL;
698 -- leave these views