1-- 2-- SELECT 3-- 4 5-- btree index 6-- awk '{if($1<10){print;}else{next;}}' onek.data | sort +0n -1 7-- 8SELECT * FROM onek 9 WHERE onek.unique1 < 10 10 ORDER BY onek.unique1; 11 12-- 13-- awk '{if($1<20){print $1,$14;}else{next;}}' onek.data | sort +0nr -1 14-- 15SELECT onek.unique1, onek.stringu1 FROM onek 16 WHERE onek.unique1 < 20 17 ORDER BY unique1 using >; 18 19-- 20-- awk '{if($1>980){print $1,$14;}else{next;}}' onek.data | sort +1d -2 21-- 22SELECT onek.unique1, onek.stringu1 FROM onek 23 WHERE onek.unique1 > 980 24 ORDER BY stringu1 using <; 25 26-- 27-- awk '{if($1>980){print $1,$16;}else{next;}}' onek.data | 28-- sort +1d -2 +0nr -1 29-- 30SELECT onek.unique1, onek.string4 FROM onek 31 WHERE onek.unique1 > 980 32 ORDER BY string4 using <, unique1 using >; 33 34-- 35-- awk '{if($1>980){print $1,$16;}else{next;}}' onek.data | 36-- sort +1dr -2 +0n -1 37-- 38SELECT onek.unique1, onek.string4 FROM onek 39 WHERE onek.unique1 > 980 40 ORDER BY string4 using >, unique1 using <; 41 42-- 43-- awk '{if($1<20){print $1,$16;}else{next;}}' onek.data | 44-- sort +0nr -1 +1d -2 45-- 46SELECT onek.unique1, onek.string4 FROM onek 47 WHERE onek.unique1 < 20 48 ORDER BY unique1 using >, string4 using <; 49 50-- 51-- awk '{if($1<20){print $1,$16;}else{next;}}' onek.data | 52-- sort +0n -1 +1dr -2 53-- 54SELECT onek.unique1, onek.string4 FROM onek 55 WHERE onek.unique1 < 20 56 ORDER BY unique1 using <, string4 using >; 57 58-- 59-- test partial btree indexes 60-- 61-- As of 7.2, planner probably won't pick an indexscan without stats, 62-- so ANALYZE first. Also, we want to prevent it from picking a bitmapscan 63-- followed by sort, because that could hide index ordering problems. 64-- 65ANALYZE onek2; 66 67SET enable_seqscan TO off; 68SET enable_bitmapscan TO off; 69SET enable_sort TO off; 70 71-- 72-- awk '{if($1<10){print $0;}else{next;}}' onek.data | sort +0n -1 73-- 74SELECT onek2.* FROM onek2 WHERE onek2.unique1 < 10; 75 76-- 77-- awk '{if($1<20){print $1,$14;}else{next;}}' onek.data | sort +0nr -1 78-- 79SELECT onek2.unique1, onek2.stringu1 FROM onek2 80 WHERE onek2.unique1 < 20 81 ORDER BY unique1 using >; 82 83-- 84-- awk '{if($1>980){print $1,$14;}else{next;}}' onek.data | sort +1d -2 85-- 86SELECT onek2.unique1, onek2.stringu1 FROM onek2 87 WHERE onek2.unique1 > 980; 88 89RESET enable_seqscan; 90RESET enable_bitmapscan; 91RESET enable_sort; 92 93 94SELECT two, stringu1, ten, string4 95 INTO TABLE tmp 96 FROM onek; 97 98-- 99-- awk '{print $1,$2;}' person.data | 100-- awk '{if(NF!=2){print $3,$2;}else{print;}}' - emp.data | 101-- awk '{if(NF!=2){print $3,$2;}else{print;}}' - student.data | 102-- awk 'BEGIN{FS=" ";}{if(NF!=2){print $4,$5;}else{print;}}' - stud_emp.data 103-- 104-- SELECT name, age FROM person*; ??? check if different 105SELECT p.name, p.age FROM person* p; 106 107-- 108-- awk '{print $1,$2;}' person.data | 109-- awk '{if(NF!=2){print $3,$2;}else{print;}}' - emp.data | 110-- awk '{if(NF!=2){print $3,$2;}else{print;}}' - student.data | 111-- awk 'BEGIN{FS=" ";}{if(NF!=1){print $4,$5;}else{print;}}' - stud_emp.data | 112-- sort +1nr -2 113-- 114SELECT p.name, p.age FROM person* p ORDER BY age using >, name; 115 116-- 117-- Test some cases involving whole-row Var referencing a subquery 118-- 119select foo from (select 1) as foo; 120select foo from (select null) as foo; 121select foo from (select 'xyzzy',1,null) as foo; 122 123-- 124-- Test VALUES lists 125-- 126select * from onek, (values(147, 'RFAAAA'), (931, 'VJAAAA')) as v (i, j) 127 WHERE onek.unique1 = v.i and onek.stringu1 = v.j; 128 129-- a more complex case 130-- looks like we're coding lisp :-) 131select * from onek, 132 (values ((select i from 133 (values(10000), (2), (389), (1000), (2000), ((select 10029))) as foo(i) 134 order by i asc limit 1))) bar (i) 135 where onek.unique1 = bar.i; 136 137-- try VALUES in a subquery 138select * from onek 139 where (unique1,ten) in (values (1,1), (20,0), (99,9), (17,99)) 140 order by unique1; 141 142-- VALUES is also legal as a standalone query or a set-operation member 143VALUES (1,2), (3,4+4), (7,77.7); 144 145VALUES (1,2), (3,4+4), (7,77.7) 146UNION ALL 147SELECT 2+2, 57 148UNION ALL 149TABLE int8_tbl; 150 151-- 152-- Test ORDER BY options 153-- 154 155CREATE TEMP TABLE foo (f1 int); 156 157INSERT INTO foo VALUES (42),(3),(10),(7),(null),(null),(1); 158 159SELECT * FROM foo ORDER BY f1; 160SELECT * FROM foo ORDER BY f1 ASC; -- same thing 161SELECT * FROM foo ORDER BY f1 NULLS FIRST; 162SELECT * FROM foo ORDER BY f1 DESC; 163SELECT * FROM foo ORDER BY f1 DESC NULLS LAST; 164 165-- check if indexscans do the right things 166CREATE INDEX fooi ON foo (f1); 167SET enable_sort = false; 168 169SELECT * FROM foo ORDER BY f1; 170SELECT * FROM foo ORDER BY f1 NULLS FIRST; 171SELECT * FROM foo ORDER BY f1 DESC; 172SELECT * FROM foo ORDER BY f1 DESC NULLS LAST; 173 174DROP INDEX fooi; 175CREATE INDEX fooi ON foo (f1 DESC); 176 177SELECT * FROM foo ORDER BY f1; 178SELECT * FROM foo ORDER BY f1 NULLS FIRST; 179SELECT * FROM foo ORDER BY f1 DESC; 180SELECT * FROM foo ORDER BY f1 DESC NULLS LAST; 181 182DROP INDEX fooi; 183CREATE INDEX fooi ON foo (f1 DESC NULLS LAST); 184 185SELECT * FROM foo ORDER BY f1; 186SELECT * FROM foo ORDER BY f1 NULLS FIRST; 187SELECT * FROM foo ORDER BY f1 DESC; 188SELECT * FROM foo ORDER BY f1 DESC NULLS LAST; 189 190-- 191-- Test planning of some cases with partial indexes 192-- 193 194-- partial index is usable 195explain (costs off) 196select * from onek2 where unique2 = 11 and stringu1 = 'ATAAAA'; 197select * from onek2 where unique2 = 11 and stringu1 = 'ATAAAA'; 198explain (costs off) 199select unique2 from onek2 where unique2 = 11 and stringu1 = 'ATAAAA'; 200select unique2 from onek2 where unique2 = 11 and stringu1 = 'ATAAAA'; 201-- partial index predicate implies clause, so no need for retest 202explain (costs off) 203select * from onek2 where unique2 = 11 and stringu1 < 'B'; 204select * from onek2 where unique2 = 11 and stringu1 < 'B'; 205explain (costs off) 206select unique2 from onek2 where unique2 = 11 and stringu1 < 'B'; 207select unique2 from onek2 where unique2 = 11 and stringu1 < 'B'; 208-- but if it's an update target, must retest anyway 209explain (costs off) 210select unique2 from onek2 where unique2 = 11 and stringu1 < 'B' for update; 211select unique2 from onek2 where unique2 = 11 and stringu1 < 'B' for update; 212-- partial index is not applicable 213explain (costs off) 214select unique2 from onek2 where unique2 = 11 and stringu1 < 'C'; 215select unique2 from onek2 where unique2 = 11 and stringu1 < 'C'; 216-- partial index implies clause, but bitmap scan must recheck predicate anyway 217SET enable_indexscan TO off; 218explain (costs off) 219select unique2 from onek2 where unique2 = 11 and stringu1 < 'B'; 220select unique2 from onek2 where unique2 = 11 and stringu1 < 'B'; 221RESET enable_indexscan; 222-- check multi-index cases too 223explain (costs off) 224select unique1, unique2 from onek2 225 where (unique2 = 11 or unique1 = 0) and stringu1 < 'B'; 226select unique1, unique2 from onek2 227 where (unique2 = 11 or unique1 = 0) and stringu1 < 'B'; 228explain (costs off) 229select unique1, unique2 from onek2 230 where (unique2 = 11 and stringu1 < 'B') or unique1 = 0; 231select unique1, unique2 from onek2 232 where (unique2 = 11 and stringu1 < 'B') or unique1 = 0; 233 234-- 235-- Test some corner cases that have been known to confuse the planner 236-- 237 238-- ORDER BY on a constant doesn't really need any sorting 239SELECT 1 AS x ORDER BY x; 240 241-- But ORDER BY on a set-valued expression does 242create function sillysrf(int) returns setof int as 243 'values (1),(10),(2),($1)' language sql immutable; 244 245select sillysrf(42); 246select sillysrf(-1) order by 1; 247 248drop function sillysrf(int); 249 250-- X = X isn't a no-op, it's effectively X IS NOT NULL assuming = is strict 251-- (see bug #5084) 252select * from (values (2),(null),(1)) v(k) where k = k order by k; 253select * from (values (2),(null),(1)) v(k) where k = k; 254