5 -- awk '{if($1<10){print;}else{next;}}' onek.data | sort +0n -1
8 WHERE onek.unique1 < 10
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
25 -- awk '{if($1<20){print $1,$14;}else{next;}}' onek.data | sort +0nr -1
27 SELECT onek.unique1, onek.stringu1 FROM onek
28 WHERE onek.unique1 < 20
29 ORDER BY unique1 using >;
55 -- awk '{if($1>980){print $1,$14;}else{next;}}' onek.data | sort +1d -2
57 SELECT onek.unique1, onek.stringu1 FROM onek
58 WHERE onek.unique1 > 980
59 ORDER BY stringu1 using <;
84 -- awk '{if($1>980){print $1,$16;}else{next;}}' onek.data |
85 -- sort +1d -2 +0nr -1
87 SELECT onek.unique1, onek.string4 FROM onek
88 WHERE onek.unique1 > 980
89 ORDER BY string4 using <, unique1 using >;
114 -- awk '{if($1>980){print $1,$16;}else{next;}}' onek.data |
115 -- sort +1dr -2 +0n -1
117 SELECT onek.unique1, onek.string4 FROM onek
118 WHERE onek.unique1 > 980
119 ORDER BY string4 using >, unique1 using <;
144 -- awk '{if($1<20){print $1,$16;}else{next;}}' onek.data |
145 -- sort +0nr -1 +1d -2
147 SELECT onek.unique1, onek.string4 FROM onek
148 WHERE onek.unique1 < 20
149 ORDER BY unique1 using >, string4 using <;
175 -- awk '{if($1<20){print $1,$16;}else{next;}}' onek.data |
176 -- sort +0n -1 +1dr -2
178 SELECT onek.unique1, onek.string4 FROM onek
179 WHERE onek.unique1 < 20
180 ORDER BY unique1 using <, string4 using >;
206 -- test partial btree indexes
208 -- As of 7.2, planner probably won't pick an indexscan without stats,
209 -- so ANALYZE first. Also, we want to prevent it from picking a bitmapscan
210 -- followed by sort, because that could hide index ordering problems.
213 SET enable_seqscan TO off;
214 SET enable_bitmapscan TO off;
215 SET enable_sort TO off;
217 -- awk '{if($1<10){print $0;}else{next;}}' onek.data | sort +0n -1
219 SELECT onek2.* FROM onek2 WHERE onek2.unique1 < 10;
220 unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4
221 ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------
222 0 | 998 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | AAAAAA | KMBAAA | OOOOxx
223 1 | 214 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 3 | BAAAAA | GIAAAA | OOOOxx
224 2 | 326 | 0 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 4 | 5 | CAAAAA | OMAAAA | OOOOxx
225 3 | 431 | 1 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 6 | 7 | DAAAAA | PQAAAA | VVVVxx
226 4 | 833 | 0 | 0 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 8 | 9 | EAAAAA | BGBAAA | HHHHxx
227 5 | 541 | 1 | 1 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 10 | 11 | FAAAAA | VUAAAA | HHHHxx
228 6 | 978 | 0 | 2 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 12 | 13 | GAAAAA | QLBAAA | OOOOxx
229 7 | 647 | 1 | 3 | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 14 | 15 | HAAAAA | XYAAAA | VVVVxx
230 8 | 653 | 0 | 0 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 16 | 17 | IAAAAA | DZAAAA | HHHHxx
231 9 | 49 | 1 | 1 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 18 | 19 | JAAAAA | XBAAAA | HHHHxx
235 -- awk '{if($1<20){print $1,$14;}else{next;}}' onek.data | sort +0nr -1
237 SELECT onek2.unique1, onek2.stringu1 FROM onek2
238 WHERE onek2.unique1 < 20
239 ORDER BY unique1 using >;
265 -- awk '{if($1>980){print $1,$14;}else{next;}}' onek.data | sort +1d -2
267 SELECT onek2.unique1, onek2.stringu1 FROM onek2
268 WHERE onek2.unique1 > 980;
292 RESET enable_seqscan;
293 RESET enable_bitmapscan;
295 SELECT two, stringu1, ten, string4
299 -- awk '{print $1,$2;}' person.data |
300 -- awk '{if(NF!=2){print $3,$2;}else{print;}}' - emp.data |
301 -- awk '{if(NF!=2){print $3,$2;}else{print;}}' - student.data |
302 -- awk 'BEGIN{FS=" ";}{if(NF!=2){print $4,$5;}else{print;}}' - stud_emp.data
304 -- SELECT name, age FROM person*; ??? check if different
305 SELECT p.name, p.age FROM person* p;
369 -- awk '{print $1,$2;}' person.data |
370 -- awk '{if(NF!=2){print $3,$2;}else{print;}}' - emp.data |
371 -- awk '{if(NF!=2){print $3,$2;}else{print;}}' - student.data |
372 -- awk 'BEGIN{FS=" ";}{if(NF!=1){print $4,$5;}else{print;}}' - stud_emp.data |
375 SELECT p.name, p.age FROM person* p ORDER BY age using >, name;
439 -- Test some cases involving whole-row Var referencing a subquery
441 select foo from (select 1) as foo;
447 select foo from (select null) as foo;
453 select foo from (select 'xyzzy',1,null) as foo;
462 select * from onek, (values(147, 'RFAAAA'), (931, 'VJAAAA')) as v (i, j)
463 WHERE onek.unique1 = v.i and onek.stringu1 = v.j;
464 unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 | i | j
465 ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------+-----+--------
466 147 | 0 | 1 | 3 | 7 | 7 | 7 | 47 | 147 | 147 | 147 | 14 | 15 | RFAAAA | AAAAAA | AAAAxx | 147 | RFAAAA
467 931 | 1 | 1 | 3 | 1 | 11 | 1 | 31 | 131 | 431 | 931 | 2 | 3 | VJAAAA | BAAAAA | HHHHxx | 931 | VJAAAA
470 -- a more complex case
471 -- looks like we're coding lisp :-)
473 (values ((select i from
474 (values(10000), (2), (389), (1000), (2000), ((select 10029))) as foo(i)
475 order by i asc limit 1))) bar (i)
476 where onek.unique1 = bar.i;
477 unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 | i
478 ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------+---
479 2 | 326 | 0 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 4 | 5 | CAAAAA | OMAAAA | OOOOxx | 2
482 -- try VALUES in a subquery
484 where (unique1,ten) in (values (1,1), (20,0), (99,9), (17,99))
486 unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4
487 ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------
488 1 | 214 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 3 | BAAAAA | GIAAAA | OOOOxx
489 20 | 306 | 0 | 0 | 0 | 0 | 0 | 20 | 20 | 20 | 20 | 0 | 1 | UAAAAA | ULAAAA | OOOOxx
490 99 | 101 | 1 | 3 | 9 | 19 | 9 | 99 | 99 | 99 | 99 | 18 | 19 | VDAAAA | XDAAAA | HHHHxx
493 -- VALUES is also legal as a standalone query or a set-operation member
494 VALUES (1,2), (3,4+4), (7,77.7);
502 VALUES (1,2), (3,4+4), (7,77.7)
508 ------------------+-------------------
514 123 | 4567890123456789
515 4567890123456789 | 123
516 4567890123456789 | 4567890123456789
517 4567890123456789 | -4567890123456789
521 -- Test ORDER BY options
523 CREATE TEMP TABLE foo (f1 int);
524 INSERT INTO foo VALUES (42),(3),(10),(7),(null),(null),(1);
525 SELECT * FROM foo ORDER BY f1;
537 SELECT * FROM foo ORDER BY f1 ASC; -- same thing
549 SELECT * FROM foo ORDER BY f1 NULLS FIRST;
561 SELECT * FROM foo ORDER BY f1 DESC;
573 SELECT * FROM foo ORDER BY f1 DESC NULLS LAST;
585 -- check if indexscans do the right things
586 CREATE INDEX fooi ON foo (f1);
587 SET enable_sort = false;
588 SELECT * FROM foo ORDER BY f1;
600 SELECT * FROM foo ORDER BY f1 NULLS FIRST;
612 SELECT * FROM foo ORDER BY f1 DESC;
624 SELECT * FROM foo ORDER BY f1 DESC NULLS LAST;
637 CREATE INDEX fooi ON foo (f1 DESC);
638 SELECT * FROM foo ORDER BY f1;
650 SELECT * FROM foo ORDER BY f1 NULLS FIRST;
662 SELECT * FROM foo ORDER BY f1 DESC;
674 SELECT * FROM foo ORDER BY f1 DESC NULLS LAST;
687 CREATE INDEX fooi ON foo (f1 DESC NULLS LAST);
688 SELECT * FROM foo ORDER BY f1;
700 SELECT * FROM foo ORDER BY f1 NULLS FIRST;
712 SELECT * FROM foo ORDER BY f1 DESC;
724 SELECT * FROM foo ORDER BY f1 DESC NULLS LAST;
737 -- Test some corner cases that have been known to confuse the planner
739 -- ORDER BY on a constant doesn't really need any sorting
740 SELECT 1 AS x ORDER BY x;
746 -- But ORDER BY on a set-valued expression does
747 create function sillysrf(int) returns setof int as
748 'values (1),(10),(2),($1)' language sql immutable;
758 select sillysrf(-1) order by 1;
767 drop function sillysrf(int);
768 -- X = X isn't a no-op, it's effectively X IS NOT NULL assuming = is strict
770 select * from (values (2),(null),(1)) v(k) where k = k order by k;
777 select * from (values (2),(null),(1)) v(k) where k = k;