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 offset 0) as foo; 120select foo from (select null offset 0) as foo; 121select foo from (select 'xyzzy',1,null offset 0) 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'; 198-- actually run the query with an analyze to use the partial index 199explain (costs off, analyze on, timing off, summary off) 200select * from onek2 where unique2 = 11 and stringu1 = 'ATAAAA'; 201explain (costs off) 202select unique2 from onek2 where unique2 = 11 and stringu1 = 'ATAAAA'; 203select unique2 from onek2 where unique2 = 11 and stringu1 = 'ATAAAA'; 204-- partial index predicate implies clause, so no need for retest 205explain (costs off) 206select * from onek2 where unique2 = 11 and stringu1 < 'B'; 207select * from onek2 where unique2 = 11 and stringu1 < 'B'; 208explain (costs off) 209select unique2 from onek2 where unique2 = 11 and stringu1 < 'B'; 210select unique2 from onek2 where unique2 = 11 and stringu1 < 'B'; 211-- but if it's an update target, must retest anyway 212explain (costs off) 213select unique2 from onek2 where unique2 = 11 and stringu1 < 'B' for update; 214select unique2 from onek2 where unique2 = 11 and stringu1 < 'B' for update; 215-- partial index is not applicable 216explain (costs off) 217select unique2 from onek2 where unique2 = 11 and stringu1 < 'C'; 218select unique2 from onek2 where unique2 = 11 and stringu1 < 'C'; 219-- partial index implies clause, but bitmap scan must recheck predicate anyway 220SET enable_indexscan TO off; 221explain (costs off) 222select unique2 from onek2 where unique2 = 11 and stringu1 < 'B'; 223select unique2 from onek2 where unique2 = 11 and stringu1 < 'B'; 224RESET enable_indexscan; 225-- check multi-index cases too 226explain (costs off) 227select unique1, unique2 from onek2 228 where (unique2 = 11 or unique1 = 0) and stringu1 < 'B'; 229select unique1, unique2 from onek2 230 where (unique2 = 11 or unique1 = 0) and stringu1 < 'B'; 231explain (costs off) 232select unique1, unique2 from onek2 233 where (unique2 = 11 and stringu1 < 'B') or unique1 = 0; 234select unique1, unique2 from onek2 235 where (unique2 = 11 and stringu1 < 'B') or unique1 = 0; 236 237-- 238-- Test some corner cases that have been known to confuse the planner 239-- 240 241-- ORDER BY on a constant doesn't really need any sorting 242SELECT 1 AS x ORDER BY x; 243 244-- But ORDER BY on a set-valued expression does 245create function sillysrf(int) returns setof int as 246 'values (1),(10),(2),($1)' language sql immutable; 247 248select sillysrf(42); 249select sillysrf(-1) order by 1; 250 251drop function sillysrf(int); 252 253-- X = X isn't a no-op, it's effectively X IS NOT NULL assuming = is strict 254-- (see bug #5084) 255select * from (values (2),(null),(1)) v(k) where k = k order by k; 256select * from (values (2),(null),(1)) v(k) where k = k; 257 258-- Test partitioned tables with no partitions, which should be handled the 259-- same as the non-inheritance case when expanding its RTE. 260create table list_parted_tbl (a int,b int) partition by list (a); 261create table list_parted_tbl1 partition of list_parted_tbl 262 for values in (1) partition by list(b); 263explain (costs off) select * from list_parted_tbl; 264drop table list_parted_tbl; 265