| blob | 47c3e67b230f0ba8abbdbefec61539ea4140212a |
1 --
2 -- SELECT
3 --
4 -- btree index
5 -- awk '{if($1<10){print;}else{next;}}' onek.data | sort +0n -1
6 --
7 SELECT * FROM onek
8 WHERE onek.unique1 < 10
9 ORDER BY onek.unique1;
10 unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4
11 ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------
12 0 | 998 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | AAAAAA | KMBAAA | OOOOxx
13 1 | 214 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 3 | BAAAAA | GIAAAA | OOOOxx
14 2 | 326 | 0 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 4 | 5 | CAAAAA | OMAAAA | OOOOxx
15 3 | 431 | 1 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 6 | 7 | DAAAAA | PQAAAA | VVVVxx
16 4 | 833 | 0 | 0 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 8 | 9 | EAAAAA | BGBAAA | HHHHxx
17 5 | 541 | 1 | 1 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 10 | 11 | FAAAAA | VUAAAA | HHHHxx
18 6 | 978 | 0 | 2 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 12 | 13 | GAAAAA | QLBAAA | OOOOxx
19 7 | 647 | 1 | 3 | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 14 | 15 | HAAAAA | XYAAAA | VVVVxx
20 8 | 653 | 0 | 0 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 16 | 17 | IAAAAA | DZAAAA | HHHHxx
21 9 | 49 | 1 | 1 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 18 | 19 | JAAAAA | XBAAAA | HHHHxx
22 (10 rows)
24 --
25 -- awk '{if($1<20){print $1,$14;}else{next;}}' onek.data | sort +0nr -1
26 --
27 SELECT onek.unique1, onek.stringu1 FROM onek
28 WHERE onek.unique1 < 20
29 ORDER BY unique1 using >;
30 unique1 | stringu1
31 ---------+----------
32 19 | TAAAAA
33 18 | SAAAAA
34 17 | RAAAAA
35 16 | QAAAAA
36 15 | PAAAAA
37 14 | OAAAAA
38 13 | NAAAAA
39 12 | MAAAAA
40 11 | LAAAAA
41 10 | KAAAAA
42 9 | JAAAAA
43 8 | IAAAAA
44 7 | HAAAAA
45 6 | GAAAAA
46 5 | FAAAAA
47 4 | EAAAAA
48 3 | DAAAAA
49 2 | CAAAAA
50 1 | BAAAAA
51 0 | AAAAAA
52 (20 rows)
54 --
55 -- awk '{if($1>980){print $1,$14;}else{next;}}' onek.data | sort +1d -2
56 --
57 SELECT onek.unique1, onek.stringu1 FROM onek
58 WHERE onek.unique1 > 980
59 ORDER BY stringu1 using <;
60 unique1 | stringu1
61 ---------+----------
62 988 | AMAAAA
63 989 | BMAAAA
64 990 | CMAAAA
65 991 | DMAAAA
66 992 | EMAAAA
67 993 | FMAAAA
68 994 | GMAAAA
69 995 | HMAAAA
70 996 | IMAAAA
71 997 | JMAAAA
72 998 | KMAAAA
73 999 | LMAAAA
74 981 | TLAAAA
75 982 | ULAAAA
76 983 | VLAAAA
77 984 | WLAAAA
78 985 | XLAAAA
79 986 | YLAAAA
80 987 | ZLAAAA
81 (19 rows)
84 --
85 -- awk '{if($1>980){print $1,$16;}else{next;}}' onek.data |
86 -- sort +1d -2 +0nr -1
87 --
88 SELECT onek.unique1, onek.string4 FROM onek
89 WHERE onek.unique1 > 980
90 ORDER BY string4 using <, unique1 using >;
91 unique1 | string4
92 ---------+---------
93 999 | AAAAxx
94 995 | AAAAxx
95 983 | AAAAxx
96 982 | AAAAxx
97 981 | AAAAxx
98 998 | HHHHxx
99 997 | HHHHxx
100 993 | HHHHxx
101 990 | HHHHxx
102 986 | HHHHxx
103 996 | OOOOxx
104 991 | OOOOxx
105 988 | OOOOxx
106 987 | OOOOxx
107 985 | OOOOxx
108 994 | VVVVxx
109 992 | VVVVxx
110 989 | VVVVxx
111 984 | VVVVxx
112 (19 rows)
115 --
116 -- awk '{if($1>980){print $1,$16;}else{next;}}' onek.data |
117 -- sort +1dr -2 +0n -1
118 --
119 SELECT onek.unique1, onek.string4 FROM onek
120 WHERE onek.unique1 > 980
121 ORDER BY string4 using >, unique1 using <;
122 unique1 | string4
123 ---------+---------
124 984 | VVVVxx
125 989 | VVVVxx
126 992 | VVVVxx
127 994 | VVVVxx
128 985 | OOOOxx
129 987 | OOOOxx
130 988 | OOOOxx
131 991 | OOOOxx
132 996 | OOOOxx
133 986 | HHHHxx
134 990 | HHHHxx
135 993 | HHHHxx
136 997 | HHHHxx
137 998 | HHHHxx
138 981 | AAAAxx
139 982 | AAAAxx
140 983 | AAAAxx
141 995 | AAAAxx
142 999 | AAAAxx
143 (19 rows)
146 --
147 -- awk '{if($1<20){print $1,$16;}else{next;}}' onek.data |
148 -- sort +0nr -1 +1d -2
149 --
150 SELECT onek.unique1, onek.string4 FROM onek
151 WHERE onek.unique1 < 20
152 ORDER BY unique1 using >, string4 using <;
153 unique1 | string4
154 ---------+---------
155 19 | OOOOxx
156 18 | VVVVxx
157 17 | HHHHxx
158 16 | OOOOxx
159 15 | VVVVxx
160 14 | AAAAxx
161 13 | OOOOxx
162 12 | AAAAxx
163 11 | OOOOxx
164 10 | AAAAxx
165 9 | HHHHxx
166 8 | HHHHxx
167 7 | VVVVxx
168 6 | OOOOxx
169 5 | HHHHxx
170 4 | HHHHxx
171 3 | VVVVxx
172 2 | OOOOxx
173 1 | OOOOxx
174 0 | OOOOxx
175 (20 rows)
177 --
178 -- awk '{if($1<20){print $1,$16;}else{next;}}' onek.data |
179 -- sort +0n -1 +1dr -2
180 --
181 SELECT onek.unique1, onek.string4 FROM onek
182 WHERE onek.unique1 < 20
183 ORDER BY unique1 using <, string4 using >;
184 unique1 | string4
185 ---------+---------
186 0 | OOOOxx
187 1 | OOOOxx
188 2 | OOOOxx
189 3 | VVVVxx
190 4 | HHHHxx
191 5 | HHHHxx
192 6 | OOOOxx
193 7 | VVVVxx
194 8 | HHHHxx
195 9 | HHHHxx
196 10 | AAAAxx
197 11 | OOOOxx
198 12 | AAAAxx
199 13 | OOOOxx
200 14 | AAAAxx
201 15 | VVVVxx
202 16 | OOOOxx
203 17 | HHHHxx
204 18 | VVVVxx
205 19 | OOOOxx
206 (20 rows)
208 --
209 -- test partial btree indexes
210 --
211 -- As of 7.2, planner probably won't pick an indexscan without stats,
212 -- so ANALYZE first. Also, we want to prevent it from picking a bitmapscan
213 -- followed by sort, because that could hide index ordering problems.
214 --
215 ANALYZE onek2;
216 SET enable_seqscan TO off;
217 SET enable_bitmapscan TO off;
218 SET enable_sort TO off;
219 --
220 -- awk '{if($1<10){print $0;}else{next;}}' onek.data | sort +0n -1
221 --
222 SELECT onek2.* FROM onek2 WHERE onek2.unique1 < 10;
223 unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4
224 ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------
225 0 | 998 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | AAAAAA | KMBAAA | OOOOxx
226 1 | 214 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 3 | BAAAAA | GIAAAA | OOOOxx
227 2 | 326 | 0 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 4 | 5 | CAAAAA | OMAAAA | OOOOxx
228 3 | 431 | 1 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 6 | 7 | DAAAAA | PQAAAA | VVVVxx
229 4 | 833 | 0 | 0 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 8 | 9 | EAAAAA | BGBAAA | HHHHxx
230 5 | 541 | 1 | 1 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 10 | 11 | FAAAAA | VUAAAA | HHHHxx
231 6 | 978 | 0 | 2 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 12 | 13 | GAAAAA | QLBAAA | OOOOxx
232 7 | 647 | 1 | 3 | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 14 | 15 | HAAAAA | XYAAAA | VVVVxx
233 8 | 653 | 0 | 0 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 16 | 17 | IAAAAA | DZAAAA | HHHHxx
234 9 | 49 | 1 | 1 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 18 | 19 | JAAAAA | XBAAAA | HHHHxx
235 (10 rows)
237 --
238 -- awk '{if($1<20){print $1,$14;}else{next;}}' onek.data | sort +0nr -1
239 --
240 SELECT onek2.unique1, onek2.stringu1 FROM onek2
241 WHERE onek2.unique1 < 20
242 ORDER BY unique1 using >;
243 unique1 | stringu1
244 ---------+----------
245 19 | TAAAAA
246 18 | SAAAAA
247 17 | RAAAAA
248 16 | QAAAAA
249 15 | PAAAAA
250 14 | OAAAAA
251 13 | NAAAAA
252 12 | MAAAAA
253 11 | LAAAAA
254 10 | KAAAAA
255 9 | JAAAAA
256 8 | IAAAAA
257 7 | HAAAAA
258 6 | GAAAAA
259 5 | FAAAAA
260 4 | EAAAAA
261 3 | DAAAAA
262 2 | CAAAAA
263 1 | BAAAAA
264 0 | AAAAAA
265 (20 rows)
267 --
268 -- awk '{if($1>980){print $1,$14;}else{next;}}' onek.data | sort +1d -2
269 --
270 SELECT onek2.unique1, onek2.stringu1 FROM onek2
271 WHERE onek2.unique1 > 980;
272 unique1 | stringu1
273 ---------+----------
274 981 | TLAAAA
275 982 | ULAAAA
276 983 | VLAAAA
277 984 | WLAAAA
278 985 | XLAAAA
279 986 | YLAAAA
280 987 | ZLAAAA
281 988 | AMAAAA
282 989 | BMAAAA
283 990 | CMAAAA
284 991 | DMAAAA
285 992 | EMAAAA
286 993 | FMAAAA
287 994 | GMAAAA
288 995 | HMAAAA
289 996 | IMAAAA
290 997 | JMAAAA
291 998 | KMAAAA
292 999 | LMAAAA
293 (19 rows)
295 RESET enable_seqscan;
296 RESET enable_bitmapscan;
297 RESET enable_sort;
298 SELECT two, stringu1, ten, string4
299 INTO TABLE tmp
300 FROM onek;
301 --
302 -- awk '{print $1,$2;}' person.data |
303 -- awk '{if(NF!=2){print $3,$2;}else{print;}}' - emp.data |
304 -- awk '{if(NF!=2){print $3,$2;}else{print;}}' - student.data |
305 -- awk 'BEGIN{FS=" ";}{if(NF!=2){print $4,$5;}else{print;}}' - stud_emp.data
306 --
307 -- SELECT name, age FROM person*; ??? check if different
308 SELECT p.name, p.age FROM person* p;
309 name | age
310 ---------+-----
311 mike | 40
312 joe | 20
313 sally | 34
314 sandra | 19
315 alex | 30
316 sue | 50
317 denise | 24
318 sarah | 88
319 teresa | 38
320 nan | 28
321 leah | 68
322 wendy | 78
323 melissa | 28
324 joan | 18
325 mary | 8
326 jane | 58
327 liza | 38
328 jean | 28
329 jenifer | 38
330 juanita | 58
331 susan | 78
332 zena | 98
333 martie | 88
334 chris | 78
335 pat | 18
336 zola | 58
337 louise | 98
338 edna | 18
339 bertha | 88
340 sumi | 38
341 koko | 88
342 gina | 18
343 rean | 48
344 sharon | 78
345 paula | 68
346 julie | 68
347 belinda | 38
348 karen | 48
349 carina | 58
350 diane | 18
351 esther | 98
352 trudy | 88
353 fanny | 8
354 carmen | 78
355 lita | 25
356 pamela | 48
357 sandy | 38
358 trisha | 88
359 uma | 78
360 velma | 68
361 sharon | 25
362 sam | 30
363 bill | 20
364 fred | 28
365 larry | 60
366 jeff | 23
367 cim | 30
368 linda | 19
369 (58 rows)
371 --
372 -- awk '{print $1,$2;}' person.data |
373 -- awk '{if(NF!=2){print $3,$2;}else{print;}}' - emp.data |
374 -- awk '{if(NF!=2){print $3,$2;}else{print;}}' - student.data |
375 -- awk 'BEGIN{FS=" ";}{if(NF!=1){print $4,$5;}else{print;}}' - stud_emp.data |
376 -- sort +1nr -2
377 --
378 SELECT p.name, p.age FROM person* p ORDER BY age using >, name;
379 name | age
380 ---------+-----
381 esther | 98
382 louise | 98
383 zena | 98
384 bertha | 88
385 koko | 88
386 martie | 88
387 sarah | 88
388 trisha | 88
389 trudy | 88
390 carmen | 78
391 chris | 78
392 sharon | 78
393 susan | 78
394 uma | 78
395 wendy | 78
396 julie | 68
397 leah | 68
398 paula | 68
399 velma | 68
400 larry | 60
401 carina | 58
402 jane | 58
403 juanita | 58
404 zola | 58
405 sue | 50
406 karen | 48
407 pamela | 48
408 rean | 48
409 mike | 40
410 belinda | 38
411 jenifer | 38
412 liza | 38
413 sandy | 38
414 sumi | 38
415 teresa | 38
416 sally | 34
417 alex | 30
418 cim | 30
419 sam | 30
420 fred | 28
421 jean | 28
422 melissa | 28
423 nan | 28
424 lita | 25
425 sharon | 25
426 denise | 24
427 jeff | 23
428 bill | 20
429 joe | 20
430 linda | 19
431 sandra | 19
432 diane | 18
433 edna | 18
434 gina | 18
435 joan | 18
436 pat | 18
437 fanny | 8
438 mary | 8
439 (58 rows)
441 --
442 -- Test some cases involving whole-row Var referencing a subquery
443 --
444 select foo from (select 1) as foo;
445 foo
446 -----
447 (1)
448 (1 row)
450 select foo from (select null) as foo;
451 foo
452 -----
453 ()
454 (1 row)
456 select foo from (select 'xyzzy',1,null) as foo;
457 foo
458 ------------
459 (xyzzy,1,)
460 (1 row)
462 --
463 -- Test VALUES lists
464 --
465 select * from onek, (values(147, 'RFAAAA'), (931, 'VJAAAA')) as v (i, j)
466 WHERE onek.unique1 = v.i and onek.stringu1 = v.j;
467 unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 | i | j
468 ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------+-----+--------
469 147 | 0 | 1 | 3 | 7 | 7 | 7 | 47 | 147 | 147 | 147 | 14 | 15 | RFAAAA | AAAAAA | AAAAxx | 147 | RFAAAA
470 931 | 1 | 1 | 3 | 1 | 11 | 1 | 31 | 131 | 431 | 931 | 2 | 3 | VJAAAA | BAAAAA | HHHHxx | 931 | VJAAAA
471 (2 rows)
473 -- a more complex case
474 -- looks like we're coding lisp :-)
475 select * from onek,
476 (values ((select i from
477 (values(10000), (2), (389), (1000), (2000), ((select 10029))) as foo(i)
478 order by i asc limit 1))) bar (i)
479 where onek.unique1 = bar.i;
480 unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 | i
481 ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------+---
482 2 | 326 | 0 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 4 | 5 | CAAAAA | OMAAAA | OOOOxx | 2
483 (1 row)
485 -- try VALUES in a subquery
486 select * from onek
487 where (unique1,ten) in (values (1,1), (20,0), (99,9), (17,99))
488 order by unique1;
489 unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4
490 ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------
491 1 | 214 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 3 | BAAAAA | GIAAAA | OOOOxx
492 20 | 306 | 0 | 0 | 0 | 0 | 0 | 20 | 20 | 20 | 20 | 0 | 1 | UAAAAA | ULAAAA | OOOOxx
493 99 | 101 | 1 | 3 | 9 | 19 | 9 | 99 | 99 | 99 | 99 | 18 | 19 | VDAAAA | XDAAAA | HHHHxx
494 (3 rows)
496 -- VALUES is also legal as a standalone query or a set-operation member
497 VALUES (1,2), (3,4+4), (7,77.7);
498 column1 | column2
499 ---------+---------
500 1 | 2
501 3 | 8
502 7 | 77.7
503 (3 rows)
505 VALUES (1,2), (3,4+4), (7,77.7)
506 UNION ALL
507 SELECT 2+2, 57
508 UNION ALL
509 TABLE int8_tbl;
510 column1 | column2
511 ------------------+-------------------
512 1 | 2
513 3 | 8
514 7 | 77.7
515 4 | 57
516 123 | 456
517 123 | 4567890123456789
518 4567890123456789 | 123
519 4567890123456789 | 4567890123456789
520 4567890123456789 | -4567890123456789
521 (9 rows)
523 --
524 -- Test ORDER BY options
525 --
526 CREATE TEMP TABLE foo (f1 int);
527 INSERT INTO foo VALUES (42),(3),(10),(7),(null),(null),(1);
528 SELECT * FROM foo ORDER BY f1;
529 f1
530 ----
531 1
532 3
533 7
534 10
535 42
538 (7 rows)
540 SELECT * FROM foo ORDER BY f1 ASC; -- same thing
541 f1
542 ----
543 1
544 3
545 7
546 10
547 42
550 (7 rows)
552 SELECT * FROM foo ORDER BY f1 NULLS FIRST;
553 f1
554 ----
557 1
558 3
559 7
560 10
561 42
562 (7 rows)
564 SELECT * FROM foo ORDER BY f1 DESC;
565 f1
566 ----
569 42
570 10
571 7
572 3
573 1
574 (7 rows)
576 SELECT * FROM foo ORDER BY f1 DESC NULLS LAST;
577 f1
578 ----
579 42
580 10
581 7
582 3
583 1
586 (7 rows)
588 -- check if indexscans do the right things
589 CREATE INDEX fooi ON foo (f1);
590 SET enable_sort = false;
591 SELECT * FROM foo ORDER BY f1;
592 f1
593 ----
594 1
595 3
596 7
597 10
598 42
601 (7 rows)
603 SELECT * FROM foo ORDER BY f1 NULLS FIRST;
604 f1
605 ----
608 1
609 3
610 7
611 10
612 42
613 (7 rows)
615 SELECT * FROM foo ORDER BY f1 DESC;
616 f1
617 ----
620 42
621 10
622 7
623 3
624 1
625 (7 rows)
627 SELECT * FROM foo ORDER BY f1 DESC NULLS LAST;
628 f1
629 ----
630 42
631 10
632 7
633 3
634 1
637 (7 rows)
639 DROP INDEX fooi;
640 CREATE INDEX fooi ON foo (f1 DESC);
641 SELECT * FROM foo ORDER BY f1;
642 f1
643 ----
644 1
645 3
646 7
647 10
648 42
651 (7 rows)
653 SELECT * FROM foo ORDER BY f1 NULLS FIRST;
654 f1
655 ----
658 1
659 3
660 7
661 10
662 42
663 (7 rows)
665 SELECT * FROM foo ORDER BY f1 DESC;
666 f1
667 ----
670 42
671 10
672 7
673 3
674 1
675 (7 rows)
677 SELECT * FROM foo ORDER BY f1 DESC NULLS LAST;
678 f1
679 ----
680 42
681 10
682 7
683 3
684 1
687 (7 rows)
689 DROP INDEX fooi;
690 CREATE INDEX fooi ON foo (f1 DESC NULLS LAST);
691 SELECT * FROM foo ORDER BY f1;
692 f1
693 ----
694 1
695 3
696 7
697 10
698 42
701 (7 rows)
703 SELECT * FROM foo ORDER BY f1 NULLS FIRST;
704 f1
705 ----
708 1
709 3
710 7
711 10
712 42
713 (7 rows)
715 SELECT * FROM foo ORDER BY f1 DESC;
716 f1
717 ----
720 42
721 10
722 7
723 3
724 1
725 (7 rows)
727 SELECT * FROM foo ORDER BY f1 DESC NULLS LAST;
728 f1
729 ----
730 42
731 10
732 7
733 3
734 1
737 (7 rows)
739 --
740 -- Test some corner cases that have been known to confuse the planner
741 --
742 -- ORDER BY on a constant doesn't really need any sorting
743 SELECT 1 AS x ORDER BY x;
744 x
745 ---
746 1
747 (1 row)
749 -- But ORDER BY on a set-valued expression does
750 create function sillysrf(int) returns setof int as
751 'values (1),(10),(2),($1)' language sql immutable;
752 select sillysrf(42);
753 sillysrf
754 ----------
755 1
756 10
757 2
758 42
759 (4 rows)
761 select sillysrf(-1) order by 1;
762 sillysrf
763 ----------
764 -1
765 1
766 2
767 10
768 (4 rows)
770 drop function sillysrf(int);