granicus.if.org Git - postgresql/blob - src/test/regress/sql/select.sql

   1 --
   2 -- SELECT
   3 --
   4
   5 -- btree index
   6 -- awk '{if($1<10){print;}else{next;}}' onek.data | sort +0n -1
   7 --
   8 SELECT * 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 --
  15 SELECT 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 --
  22 SELECT 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 --
  30 SELECT 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 --
  38 SELECT 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 --
  46 SELECT 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 --
  54 SELECT 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 --
  65 ANALYZE onek2;
  66
  67 SET enable_seqscan TO off;
  68 SET enable_bitmapscan TO off;
  69 SET enable_sort TO off;
  70
  71 --
  72 -- awk '{if($1<10){print $0;}else{next;}}' onek.data | sort +0n -1
  73 --
  74 SELECT 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 --
  79 SELECT 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 --
  86 SELECT onek2.unique1, onek2.stringu1 FROM onek2
  87    WHERE onek2.unique1 > 980;
  88
  89 RESET enable_seqscan;
  90 RESET enable_bitmapscan;
  91 RESET enable_sort;
  92
  93
  94 SELECT 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
 105 SELECT 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 --
 114 SELECT 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 --
 119 select foo from (select 1 offset 0) as foo;
 120 select foo from (select null offset 0) as foo;
 121 select foo from (select 'xyzzy',1,null offset 0) as foo;
 122
 123 --
 124 -- Test VALUES lists
 125 --
 126 select * 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 :-)
 131 select * 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
 138 select * 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
 143 VALUES (1,2), (3,4+4), (7,77.7);
 144
 145 VALUES (1,2), (3,4+4), (7,77.7)
 146 UNION ALL
 147 SELECT 2+2, 57
 148 UNION ALL
 149 TABLE int8_tbl;
 150
 151 --
 152 -- Test ORDER BY options
 153 --
 154
 155 CREATE TEMP TABLE foo (f1 int);
 156
 157 INSERT INTO foo VALUES (42),(3),(10),(7),(null),(null),(1);
 158
 159 SELECT * FROM foo ORDER BY f1;
 160 SELECT * FROM foo ORDER BY f1 ASC;      -- same thing
 161 SELECT * FROM foo ORDER BY f1 NULLS FIRST;
 162 SELECT * FROM foo ORDER BY f1 DESC;
 163 SELECT * FROM foo ORDER BY f1 DESC NULLS LAST;
 164
 165 -- check if indexscans do the right things
 166 CREATE INDEX fooi ON foo (f1);
 167 SET enable_sort = false;
 168
 169 SELECT * FROM foo ORDER BY f1;
 170 SELECT * FROM foo ORDER BY f1 NULLS FIRST;
 171 SELECT * FROM foo ORDER BY f1 DESC;
 172 SELECT * FROM foo ORDER BY f1 DESC NULLS LAST;
 173
 174 DROP INDEX fooi;
 175 CREATE INDEX fooi ON foo (f1 DESC);
 176
 177 SELECT * FROM foo ORDER BY f1;
 178 SELECT * FROM foo ORDER BY f1 NULLS FIRST;
 179 SELECT * FROM foo ORDER BY f1 DESC;
 180 SELECT * FROM foo ORDER BY f1 DESC NULLS LAST;
 181
 182 DROP INDEX fooi;
 183 CREATE INDEX fooi ON foo (f1 DESC NULLS LAST);
 184
 185 SELECT * FROM foo ORDER BY f1;
 186 SELECT * FROM foo ORDER BY f1 NULLS FIRST;
 187 SELECT * FROM foo ORDER BY f1 DESC;
 188 SELECT * 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
 195 explain (costs off)
 196 select * from onek2 where unique2 = 11 and stringu1 = 'ATAAAA';
 197 select * from onek2 where unique2 = 11 and stringu1 = 'ATAAAA';
 198 -- actually run the query with an analyze to use the partial index
 199 explain (costs off, analyze on, timing off, summary off)
 200 select * from onek2 where unique2 = 11 and stringu1 = 'ATAAAA';
 201 explain (costs off)
 202 select unique2 from onek2 where unique2 = 11 and stringu1 = 'ATAAAA';
 203 select unique2 from onek2 where unique2 = 11 and stringu1 = 'ATAAAA';
 204 -- partial index predicate implies clause, so no need for retest
 205 explain (costs off)
 206 select * from onek2 where unique2 = 11 and stringu1 < 'B';
 207 select * from onek2 where unique2 = 11 and stringu1 < 'B';
 208 explain (costs off)
 209 select unique2 from onek2 where unique2 = 11 and stringu1 < 'B';
 210 select unique2 from onek2 where unique2 = 11 and stringu1 < 'B';
 211 -- but if it's an update target, must retest anyway
 212 explain (costs off)
 213 select unique2 from onek2 where unique2 = 11 and stringu1 < 'B' for update;
 214 select unique2 from onek2 where unique2 = 11 and stringu1 < 'B' for update;
 215 -- partial index is not applicable
 216 explain (costs off)
 217 select unique2 from onek2 where unique2 = 11 and stringu1 < 'C';
 218 select unique2 from onek2 where unique2 = 11 and stringu1 < 'C';
 219 -- partial index implies clause, but bitmap scan must recheck predicate anyway
 220 SET enable_indexscan TO off;
 221 explain (costs off)
 222 select unique2 from onek2 where unique2 = 11 and stringu1 < 'B';
 223 select unique2 from onek2 where unique2 = 11 and stringu1 < 'B';
 224 RESET enable_indexscan;
 225 -- check multi-index cases too
 226 explain (costs off)
 227 select unique1, unique2 from onek2
 228   where (unique2 = 11 or unique1 = 0) and stringu1 < 'B';
 229 select unique1, unique2 from onek2
 230   where (unique2 = 11 or unique1 = 0) and stringu1 < 'B';
 231 explain (costs off)
 232 select unique1, unique2 from onek2
 233   where (unique2 = 11 and stringu1 < 'B') or unique1 = 0;
 234 select 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
 242 SELECT 1 AS x ORDER BY x;
 243
 244 -- But ORDER BY on a set-valued expression does
 245 create function sillysrf(int) returns setof int as
 246   'values (1),(10),(2),($1)' language sql immutable;
 247
 248 select sillysrf(42);
 249 select sillysrf(-1) order by 1;
 250
 251 drop 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)
 255 select * from (values (2),(null),(1)) v(k) where k = k order by k;
 256 select * 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.
 260 create table list_parted_tbl (a int,b int) partition by list (a);
 261 create table list_parted_tbl1 partition of list_parted_tbl
 262   for values in (1) partition by list(b);
 263 explain (costs off) select * from list_parted_tbl;
 264 drop table list_parted_tbl;