1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
|
--
-- SELECT_DISTINCT_ON
--
SELECT DISTINCT ON (string4) string4, two, ten
FROM onek
ORDER BY string4 using <, two using >, ten using <;
string4 | two | ten
---------+-----+-----
AAAAxx | 1 | 1
HHHHxx | 1 | 1
OOOOxx | 1 | 1
VVVVxx | 1 | 1
(4 rows)
-- this will fail due to conflict of ordering requirements
SELECT DISTINCT ON (string4, ten) string4, two, ten
FROM onek
ORDER BY string4 using <, two using <, ten using <;
ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions
LINE 1: SELECT DISTINCT ON (string4, ten) string4, two, ten
^
SELECT DISTINCT ON (string4, ten) string4, ten, two
FROM onek
ORDER BY string4 using <, ten using >, two using <;
string4 | ten | two
---------+-----+-----
AAAAxx | 9 | 1
AAAAxx | 8 | 0
AAAAxx | 7 | 1
AAAAxx | 6 | 0
AAAAxx | 5 | 1
AAAAxx | 4 | 0
AAAAxx | 3 | 1
AAAAxx | 2 | 0
AAAAxx | 1 | 1
AAAAxx | 0 | 0
HHHHxx | 9 | 1
HHHHxx | 8 | 0
HHHHxx | 7 | 1
HHHHxx | 6 | 0
HHHHxx | 5 | 1
HHHHxx | 4 | 0
HHHHxx | 3 | 1
HHHHxx | 2 | 0
HHHHxx | 1 | 1
HHHHxx | 0 | 0
OOOOxx | 9 | 1
OOOOxx | 8 | 0
OOOOxx | 7 | 1
OOOOxx | 6 | 0
OOOOxx | 5 | 1
OOOOxx | 4 | 0
OOOOxx | 3 | 1
OOOOxx | 2 | 0
OOOOxx | 1 | 1
OOOOxx | 0 | 0
VVVVxx | 9 | 1
VVVVxx | 8 | 0
VVVVxx | 7 | 1
VVVVxx | 6 | 0
VVVVxx | 5 | 1
VVVVxx | 4 | 0
VVVVxx | 3 | 1
VVVVxx | 2 | 0
VVVVxx | 1 | 1
VVVVxx | 0 | 0
(40 rows)
-- bug #5049: early 8.4.x chokes on volatile DISTINCT ON clauses
select distinct on (1) floor(random()) as r, f1 from int4_tbl order by 1,2;
r | f1
---+-------------
0 | -2147483647
(1 row)
--
-- Test the planner's ability to use a LIMIT 1 instead of a Unique node when
-- all of the distinct_pathkeys have been marked as redundant
--
-- Ensure we also get a LIMIT plan with DISTINCT ON
EXPLAIN (COSTS OFF)
SELECT DISTINCT ON (four) four,two
FROM tenk1 WHERE four = 0 ORDER BY 1;
QUERY PLAN
----------------------------------
Result
-> Limit
-> Seq Scan on tenk1
Filter: (four = 0)
(4 rows)
-- and check the result of the above query is correct
SELECT DISTINCT ON (four) four,two
FROM tenk1 WHERE four = 0 ORDER BY 1;
four | two
------+-----
0 | 0
(1 row)
-- Ensure a Sort -> Limit is used when the ORDER BY contains additional cols
EXPLAIN (COSTS OFF)
SELECT DISTINCT ON (four) four,two
FROM tenk1 WHERE four = 0 ORDER BY 1,2;
QUERY PLAN
----------------------------------
Limit
-> Sort
Sort Key: two
-> Seq Scan on tenk1
Filter: (four = 0)
(5 rows)
-- Same again but use a column that is indexed so that we get an index scan
-- then a limit
EXPLAIN (COSTS OFF)
SELECT DISTINCT ON (four) four,hundred
FROM tenk1 WHERE four = 0 ORDER BY 1,2;
QUERY PLAN
-----------------------------------------------------
Result
-> Limit
-> Index Scan using tenk1_hundred on tenk1
Filter: (four = 0)
(4 rows)
|
