github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/distinct (about) 1 statement ok 2 CREATE TABLE xyz ( 3 x INT PRIMARY KEY, 4 y INT, 5 z INT, 6 INDEX foo (z, y) 7 ) 8 9 statement ok 10 INSERT INTO xyz VALUES 11 (1, 2, 3), 12 (2, 5, 6), 13 (3, 2, 3), 14 (4, 5, 6), 15 (5, 2, 6), 16 (6, 3, 5), 17 (7, 2, 9) 18 19 query II rowsort 20 SELECT y, z FROM xyz 21 ---- 22 2 3 23 5 6 24 2 3 25 5 6 26 2 6 27 3 5 28 2 9 29 30 query II rowsort 31 SELECT DISTINCT y, z FROM xyz 32 ---- 33 2 3 34 5 6 35 2 6 36 3 5 37 2 9 38 39 query I rowsort 40 SELECT y FROM (SELECT DISTINCT y, z FROM xyz) 41 ---- 42 2 43 5 44 2 45 3 46 2 47 48 query II partialsort(2) 49 SELECT DISTINCT y, z FROM xyz ORDER BY z 50 ---- 51 2 3 52 3 5 53 2 6 54 5 6 55 2 9 56 57 query II partialsort(1) 58 SELECT DISTINCT y, z FROM xyz ORDER BY y 59 ---- 60 2 3 61 2 6 62 2 9 63 3 5 64 5 6 65 66 query II 67 SELECT DISTINCT y, z FROM xyz ORDER BY y, z 68 ---- 69 2 3 70 2 6 71 2 9 72 3 5 73 5 6 74 75 query I 76 SELECT DISTINCT y + z FROM xyz ORDER by (y + z) 77 ---- 78 5 79 8 80 11 81 82 query II 83 SELECT DISTINCT y AS w, z FROM xyz ORDER by z, w 84 ---- 85 2 3 86 3 5 87 2 6 88 5 6 89 2 9 90 91 query I 92 SELECT DISTINCT y AS w FROM xyz ORDER by y 93 ---- 94 2 95 3 96 5 97 98 # Insert NULL values for z. 99 statement ok 100 INSERT INTO xyz (x, y) VALUES (8, 2), (9, 2) 101 102 query II rowsort 103 SELECT DISTINCT y,z FROM xyz 104 ---- 105 2 3 106 5 6 107 2 6 108 3 5 109 2 9 110 2 NULL 111 112 query T rowsort 113 SELECT DISTINCT (y,z) FROM xyz 114 ---- 115 (2,3) 116 (5,6) 117 (2,6) 118 (3,5) 119 (2,9) 120 (2,) 121 122 query I 123 SELECT count(*) FROM (SELECT DISTINCT y FROM xyz) 124 ---- 125 3 126 127 statement ok 128 CREATE TABLE kv (k INT PRIMARY KEY, v INT, UNIQUE INDEX idx(v)) 129 130 statement ok 131 INSERT INTO kv VALUES (1, 1), (2, 2), (3, NULL), (4, NULL), (5, 5), (6, NULL) 132 133 query I rowsort 134 SELECT DISTINCT v FROM kv 135 ---- 136 NULL 137 1 138 2 139 5 140 141 query I rowsort 142 SELECT DISTINCT v FROM kv@idx 143 ---- 144 NULL 145 1 146 2 147 5 148 149 query I rowsort 150 SELECT DISTINCT v FROM kv@idx WHERE v > 0 151 ---- 152 1 153 2 154 5 155 156 # Regression test for #44296. 157 statement ok 158 CREATE TABLE t0(c0 INT UNIQUE); 159 160 statement ok 161 CREATE VIEW v0(c0) AS SELECT DISTINCT t0.c0 FROM t0; 162 163 statement ok 164 INSERT INTO t0 (c0) VALUES (NULL), (NULL); 165 166 query I 167 SELECT * FROM v0 WHERE v0.c0 IS NULL 168 ---- 169 NULL 170 171 # Regression test for #44079. 172 statement ok 173 CREATE TABLE t44079 (x INT[]); 174 INSERT INTO t44079 VALUES (NULL), (ARRAY[NULL]) 175 176 query T rowsort 177 SELECT DISTINCT * FROM t44079 178 ---- 179 NULL 180 {NULL} 181 182 statement ok 183 DROP TABLE IF EXISTS t; 184 CREATE TABLE t (x JSONB); 185 INSERT INTO t VALUES 186 ('{"foo" : "bar"}'), 187 ('{"foo" : "bar"}'), 188 ('[1, 2]'), 189 ('[2, 1]'), 190 ('[1, 2]'), 191 ('{"foo": {"bar" : "baz"}}') 192 193 query T rowsort 194 SELECT DISTINCT (x) FROM t 195 ---- 196 [2, 1] 197 [1, 2] 198 {"foo": {"bar": "baz"}} 199 {"foo": "bar"} 200 201 statement ok 202 DROP TABLE IF EXISTS t; 203 CREATE TABLE t (x DECIMAL); 204 INSERT INTO t VALUES (1.0), (1.00), (1.000) 205 206 # We want to ensure that this only returns 1 element. We don't 207 # check the element directly because it returns 1.0, 1.00, or 208 # 1.000 non-deterministically in a distributed setting. 209 query I 210 SELECT COUNT (*) FROM (SELECT DISTINCT (array[x]) FROM t) 211 ---- 212 1 213 214 # Regression for #46709. 215 statement ok 216 DROP TABLE IF EXISTS t; 217 CREATE TABLE t (i INT, x INT, y INT, z STRING); 218 INSERT INTO t VALUES 219 (1, 1, 2, 'hello'), 220 (2, 1, 2, 'hello'), 221 (3, 1, 2, 'hello there') 222 223 query IT 224 SELECT x, jsonb_agg(DISTINCT jsonb_build_object('y', y, 'z', z)) FROM (SELECT * FROM t ORDER BY i) GROUP BY x 225 ---- 226 1 [{"y": 2, "z": "hello"}, {"y": 2, "z": "hello there"}]