1--
2-- SELECT_DISTINCT_ON
3--
4SELECT DISTINCT ON (string4) string4, two, ten
5   FROM tmp
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
16SELECT DISTINCT ON (string4, ten) string4, two, ten
17   FROM tmp
18   ORDER BY string4 using <, two using <, ten using <;
19ERROR:  SELECT DISTINCT ON expressions must match initial ORDER BY expressions
20LINE 1: SELECT DISTINCT ON (string4, ten) string4, two, ten
21                                     ^
22SELECT DISTINCT ON (string4, ten) string4, ten, two
23   FROM tmp
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
70select 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