src/test/regress/expected/select_distinct_on.out

   1 --
   2 -- SELECT_DISTINCT_ON
   3 --
   4 SELECT DISTINCT ON (string4) string4, two, ten
   5    FROM onek
   6    ORDER BY string4 using <, two using >, ten using <;
   7  string4 | two | ten
   8 ---------+-----+-----
   9  AAAAxx  |   1 |   1
  10  HHHHxx  |   1 |   1
  11  OOOOxx  |   1 |   1
  12  VVVVxx  |   1 |   1
  13 (4 rows)
  14
  15 -- this will fail due to conflict of ordering requirements
  16 SELECT DISTINCT ON (string4, ten) string4, two, ten
  17    FROM onek
  18    ORDER BY string4 using <, two using <, ten using <;
  19 ERROR:  SELECT DISTINCT ON expressions must match initial ORDER BY expressions
  20 LINE 1: SELECT DISTINCT ON (string4, ten) string4, two, ten
  21                                      ^
  22 SELECT DISTINCT ON (string4, ten) string4, ten, two
  23    FROM onek
  24    ORDER BY string4 using <, ten using >, two using <;
  25  string4 | ten | two
  26 ---------+-----+-----
  27  AAAAxx  |   9 |   1
  28  AAAAxx  |   8 |   0
  29  AAAAxx  |   7 |   1
  30  AAAAxx  |   6 |   0
  31  AAAAxx  |   5 |   1
  32  AAAAxx  |   4 |   0
  33  AAAAxx  |   3 |   1
  34  AAAAxx  |   2 |   0
  35  AAAAxx  |   1 |   1
  36  AAAAxx  |   0 |   0
  37  HHHHxx  |   9 |   1
  38  HHHHxx  |   8 |   0
  39  HHHHxx  |   7 |   1
  40  HHHHxx  |   6 |   0
  41  HHHHxx  |   5 |   1
  42  HHHHxx  |   4 |   0
  43  HHHHxx  |   3 |   1
  44  HHHHxx  |   2 |   0
  45  HHHHxx  |   1 |   1
  46  HHHHxx  |   0 |   0
  47  OOOOxx  |   9 |   1
  48  OOOOxx  |   8 |   0
  49  OOOOxx  |   7 |   1
  50  OOOOxx  |   6 |   0
  51  OOOOxx  |   5 |   1
  52  OOOOxx  |   4 |   0
  53  OOOOxx  |   3 |   1
  54  OOOOxx  |   2 |   0
  55  OOOOxx  |   1 |   1
  56  OOOOxx  |   0 |   0
  57  VVVVxx  |   9 |   1
  58  VVVVxx  |   8 |   0
  59  VVVVxx  |   7 |   1
  60  VVVVxx  |   6 |   0
  61  VVVVxx  |   5 |   1
  62  VVVVxx  |   4 |   0
  63  VVVVxx  |   3 |   1
  64  VVVVxx  |   2 |   0
  65  VVVVxx  |   1 |   1
  66  VVVVxx  |   0 |   0
  67 (40 rows)
  68
  69 -- bug #5049: early 8.4.x chokes on volatile DISTINCT ON clauses
  70 select distinct on (1) floor(random()) as r, f1 from int4_tbl order by 1,2;
  71  r |     f1
  72 ---+-------------
  73  0 | -2147483647
  74 (1 row)
  75
  76 --
  77 -- Test the planner's ability to use a LIMIT 1 instead of a Unique node when
  78 -- all of the distinct_pathkeys have been marked as redundant
  79 --
  80 -- Ensure we also get a LIMIT plan with DISTINCT ON
  81 EXPLAIN (COSTS OFF)
  82 SELECT DISTINCT ON (four) four,two
  83    FROM tenk1 WHERE four = 0 ORDER BY 1;
  84          QUERY PLAN
  85 ----------------------------
  86  Limit
  87    ->  Seq Scan on tenk1
  88          Filter: (four = 0)
  89 (3 rows)
  90
  91 -- and check the result of the above query is correct
  92 SELECT DISTINCT ON (four) four,two
  93    FROM tenk1 WHERE four = 0 ORDER BY 1;
  94  four | two
  95 ------+-----
  96     0 |   0
  97 (1 row)
  98
  99 -- Ensure a Sort -> Limit is used when the ORDER BY contains additional cols
 100 EXPLAIN (COSTS OFF)
 101 SELECT DISTINCT ON (four) four,two
 102    FROM tenk1 WHERE four = 0 ORDER BY 1,2;
 103             QUERY PLAN
 104 ----------------------------------
 105  Limit
 106    ->  Sort
 107          Sort Key: two
 108          ->  Seq Scan on tenk1
 109                Filter: (four = 0)
 110 (5 rows)
 111
 112 -- Same again but use a column that is indexed so that we get an index scan
 113 -- then a limit
 114 EXPLAIN (COSTS OFF)
 115 SELECT DISTINCT ON (four) four,hundred
 116    FROM tenk1 WHERE four = 0 ORDER BY 1,2;
 117                   QUERY PLAN
 118 -----------------------------------------------
 119  Limit
 120    ->  Index Scan using tenk1_hundred on tenk1
 121          Filter: (four = 0)
 122 (3 rows)
 123
 124 --
 125 -- Test the planner's ability to reorder the distinctClause Pathkeys to match
 126 -- the input path's ordering
 127 --
 128 CREATE TABLE distinct_on_tbl (x int, y int, z int);
 129 INSERT INTO distinct_on_tbl SELECT i%10, i%10, i%10 FROM generate_series(1, 1000) AS i;
 130 CREATE INDEX distinct_on_tbl_x_y_idx ON distinct_on_tbl (x, y);
 131 ANALYZE distinct_on_tbl;
 132 -- Produce results with sorting.
 133 SET enable_hashagg TO OFF;
 134 -- Ensure we avoid the need to re-sort by reordering the distinctClause
 135 -- Pathkeys to match the ordering of the input path
 136 EXPLAIN (COSTS OFF)
 137 SELECT DISTINCT ON (y, x) x, y FROM distinct_on_tbl;
 138                                QUERY PLAN
 139 ------------------------------------------------------------------------
 140  Unique
 141    ->  Index Only Scan using distinct_on_tbl_x_y_idx on distinct_on_tbl
 142 (2 rows)
 143
 144 SELECT DISTINCT ON (y, x) x, y FROM distinct_on_tbl;
 145  x | y
 146 ---+---
 147  0 | 0
 148  1 | 1
 149  2 | 2
 150  3 | 3
 151  4 | 4
 152  5 | 5
 153  6 | 6
 154  7 | 7
 155  8 | 8
 156  9 | 9
 157 (10 rows)
 158
 159 -- Ensure we leverage incremental-sort by reordering the distinctClause
 160 -- Pathkeys to partially match the ordering of the input path
 161 EXPLAIN (COSTS OFF)
 162 SELECT DISTINCT ON (y, x) x, y FROM (SELECT * FROM distinct_on_tbl ORDER BY x) s;
 163                                      QUERY PLAN
 164 ------------------------------------------------------------------------------------
 165  Unique
 166    ->  Incremental Sort
 167          Sort Key: s.x, s.y
 168          Presorted Key: s.x
 169          ->  Subquery Scan on s
 170                ->  Index Only Scan using distinct_on_tbl_x_y_idx on distinct_on_tbl
 171 (6 rows)
 172
 173 SELECT DISTINCT ON (y, x) x, y FROM (SELECT * FROM distinct_on_tbl ORDER BY x) s;
 174  x | y
 175 ---+---
 176  0 | 0
 177  1 | 1
 178  2 | 2
 179  3 | 3
 180  4 | 4
 181  5 | 5
 182  6 | 6
 183  7 | 7
 184  8 | 8
 185  9 | 9
 186 (10 rows)
 187
 188 -- Ensure we reorder the distinctClause Pathkeys to match the ordering of the
 189 -- input path even if there is ORDER BY clause
 190 EXPLAIN (COSTS OFF)
 191 SELECT DISTINCT ON (y, x) x, y FROM distinct_on_tbl ORDER BY y;
 192                                   QUERY PLAN
 193 ------------------------------------------------------------------------------
 194  Sort
 195    Sort Key: y
 196    ->  Unique
 197          ->  Index Only Scan using distinct_on_tbl_x_y_idx on distinct_on_tbl
 198 (4 rows)
 199
 200 SELECT DISTINCT ON (y, x) x, y FROM distinct_on_tbl ORDER BY y;
 201  x | y
 202 ---+---
 203  0 | 0
 204  1 | 1
 205  2 | 2
 206  3 | 3
 207  4 | 4
 208  5 | 5
 209  6 | 6
 210  7 | 7
 211  8 | 8
 212  9 | 9
 213 (10 rows)
 214
 215 -- Ensure the resulting pathkey list matches the initial distinctClause Pathkeys
 216 EXPLAIN (COSTS OFF)
 217 SELECT DISTINCT ON (y, x) x, y FROM (select * from distinct_on_tbl order by x, z, y) s ORDER BY y, x, z;
 218                                          QUERY PLAN
 219 ---------------------------------------------------------------------------------------------
 220  Sort
 221    Sort Key: s.y, s.x, s.z
 222    ->  Unique
 223          ->  Incremental Sort
 224                Sort Key: s.x, s.y, s.z
 225                Presorted Key: s.x
 226                ->  Subquery Scan on s
 227                      ->  Sort
 228                            Sort Key: distinct_on_tbl.x, distinct_on_tbl.z, distinct_on_tbl.y
 229                            ->  Seq Scan on distinct_on_tbl
 230 (10 rows)
 231
 232 SELECT DISTINCT ON (y, x) x, y FROM (select * from distinct_on_tbl order by x, z, y) s ORDER BY y, x, z;
 233  x | y
 234 ---+---
 235  0 | 0
 236  1 | 1
 237  2 | 2
 238  3 | 3
 239  4 | 4
 240  5 | 5
 241  6 | 6
 242  7 | 7
 243  8 | 8
 244  9 | 9
 245 (10 rows)
 246
 247 RESET enable_hashagg;
 248 DROP TABLE distinct_on_tbl;