github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/union (about) 1 query I rowsort 2 VALUES (1), (1), (1), (2), (2) UNION VALUES (1), (3), (1) 3 ---- 4 1 5 2 6 3 7 8 query I rowsort 9 VALUES (1), (1), (1), (2), (2) UNION ALL VALUES (1), (3), (1) 10 ---- 11 1 12 1 13 1 14 1 15 1 16 2 17 2 18 3 19 20 query I rowsort 21 VALUES (1), (1), (1), (2), (2) INTERSECT VALUES (1), (3), (1) 22 ---- 23 1 24 25 query I rowsort 26 VALUES (1), (1), (1), (2), (2) INTERSECT ALL VALUES (1), (3), (1) 27 ---- 28 1 29 1 30 31 query I rowsort 32 VALUES (1), (1), (1), (2), (2) EXCEPT VALUES (1), (3), (1) 33 ---- 34 2 35 36 query I rowsort 37 VALUES (1), (1), (1), (2), (2) EXCEPT ALL VALUES (1), (3), (1) 38 ---- 39 1 40 2 41 2 42 43 query II rowsort 44 VALUES (1, 2), (1, 1), (1, 2), (2, 1), (2, 1) UNION VALUES (1, 3), (3, 4), (1, 1) 45 ---- 46 1 1 47 1 2 48 1 3 49 2 1 50 3 4 51 52 # The ORDER BY and LIMIT apply to the UNION, not the last VALUES. 53 query I 54 VALUES (1), (1), (1), (2), (2) UNION ALL VALUES (1), (3), (1) ORDER BY 1 DESC LIMIT 2 55 ---- 56 3 57 2 58 59 # UNION with NULL columns in operands works. 60 query I 61 VALUES (1) UNION ALL VALUES (NULL) ORDER BY 1 62 ---- 63 NULL 64 1 65 66 query I 67 VALUES (NULL) UNION ALL VALUES (1) ORDER BY 1 68 ---- 69 NULL 70 1 71 72 query I 73 VALUES (NULL) UNION ALL VALUES (NULL) 74 ---- 75 NULL 76 NULL 77 78 query IT rowsort 79 SELECT x, pg_typeof(y) FROM (SELECT 1, NULL UNION ALL SELECT 2, 4) AS t(x, y) 80 ---- 81 1 unknown 82 2 bigint 83 84 query IT rowsort 85 SELECT x, pg_typeof(y) FROM (SELECT 1, 3 UNION ALL SELECT 2, NULL) AS t(x, y) 86 ---- 87 1 bigint 88 2 unknown 89 90 # INTERSECT with NULL columns in operands works. 91 query I 92 VALUES (1) INTERSECT VALUES (NULL) ORDER BY 1 93 ---- 94 95 query I 96 VALUES (NULL) INTERSECT VALUES (1) ORDER BY 1 97 ---- 98 99 query I 100 VALUES (NULL) INTERSECT VALUES (NULL) 101 ---- 102 NULL 103 104 # EXCEPT with NULL columns in operands works. 105 query I 106 VALUES (1) EXCEPT VALUES (NULL) ORDER BY 1 107 ---- 108 1 109 110 query I 111 VALUES (NULL) EXCEPT VALUES (1) ORDER BY 1 112 ---- 113 NULL 114 115 query I 116 VALUES (NULL) EXCEPT VALUES (NULL) 117 ---- 118 119 statement ok 120 CREATE TABLE uniontest ( 121 k INT, 122 v INT 123 ) 124 125 statement OK 126 INSERT INTO uniontest VALUES 127 (1, 1), 128 (1, 1), 129 (1, 1), 130 (1, 2), 131 (1, 2), 132 (2, 1), 133 (2, 3), 134 (2, 1) 135 136 query I rowsort 137 SELECT v FROM uniontest WHERE k = 1 UNION SELECT v FROM uniontest WHERE k = 2 138 ---- 139 1 140 2 141 3 142 143 query I rowsort 144 SELECT v FROM uniontest WHERE k = 1 UNION ALL SELECT v FROM uniontest WHERE k = 2 145 ---- 146 1 147 1 148 1 149 1 150 1 151 2 152 2 153 3 154 155 query I rowsort 156 SELECT v FROM uniontest WHERE k = 1 INTERSECT SELECT v FROM uniontest WHERE k = 2 157 ---- 158 1 159 160 query I rowsort 161 SELECT v FROM uniontest WHERE k = 1 INTERSECT ALL SELECT v FROM uniontest WHERE k = 2 162 ---- 163 1 164 1 165 166 query I rowsort 167 SELECT v FROM uniontest WHERE k = 1 EXCEPT SELECT v FROM uniontest WHERE k = 2 168 ---- 169 2 170 171 query I rowsort 172 SELECT v FROM uniontest WHERE k = 1 EXCEPT ALL SELECT v FROM uniontest WHERE k = 2 173 ---- 174 1 175 2 176 2 177 178 query I 179 (SELECT v FROM uniontest WHERE k = 1 UNION ALL SELECT v FROM uniontest WHERE k = 2) ORDER BY 1 DESC LIMIT 2 180 ---- 181 3 182 2 183 184 # The ORDER BY and LIMIT apply to the UNION, not the last SELECT. 185 query I 186 SELECT v FROM uniontest WHERE k = 1 UNION ALL SELECT v FROM uniontest WHERE k = 2 ORDER BY 1 DESC LIMIT 2 187 ---- 188 3 189 2 190 191 query II 192 SELECT * FROM (SELECT * FROM (VALUES (1)) a LEFT JOIN (VALUES (1) UNION VALUES (2)) b on a.column1 = b.column1); 193 ---- 194 1 1 195 196 query II 197 SELECT * FROM (VALUES (1)) a LEFT JOIN (VALUES (1) UNION VALUES (2)) b on a.column1 = b.column1; 198 ---- 199 1 1 200 201 query error pgcode 42601 each UNION query must have the same number of columns: 2 vs 1 202 SELECT 1, 2 UNION SELECT 3 203 204 query error pgcode 42601 each INTERSECT query must have the same number of columns: 2 vs 1 205 SELECT 1, 2 INTERSECT SELECT 3 206 207 query error pgcode 42601 each EXCEPT query must have the same number of columns: 2 vs 1 208 SELECT 1, 2 EXCEPT SELECT 3 209 210 # These work with the optimizer on, but not with it off. Skip for now. 211 # TODO(jordan,radu): re-enable when optimizer=off goes away. 212 213 # query error pgcode 42804 UNION types int and string cannot be matched 214 # SELECT 1 UNION SELECT '3' 215 # 216 # query error pgcode 42804 INTERSECT types int and string cannot be matched 217 # SELECT 1 INTERSECT SELECT '3' 218 # 219 # query error pgcode 42804 EXCEPT types int and string cannot be matched 220 # SELECT 1 EXCEPT SELECT '3' 221 # 222 # query error UNION types int\[] and string\[] cannot be matched 223 # SELECT ARRAY[1] UNION ALL SELECT ARRAY['foo'] 224 225 query error pgcode 42703 column \"z\" does not exist 226 SELECT 1 UNION SELECT 3 ORDER BY z 227 228 # Check that UNION permits columns of different visible types 229 230 statement ok 231 CREATE TABLE a (a INT PRIMARY KEY) 232 233 statement ok 234 CREATE TABLE b (a INTEGER PRIMARY KEY) 235 236 query I 237 SELECT * FROM a UNION ALL SELECT * FROM b 238 239 240 # Make sure that UNION ALL doesn't crash when its two children have different 241 # post-processing stages. 242 243 statement ok 244 CREATE TABLE c (a INT PRIMARY KEY, b INT) 245 246 query I 247 SELECT a FROM a WHERE a > 2 UNION ALL (SELECT a FROM c WHERE b > 2) LIMIT 1; 248 ---- 249 250 query III 251 select *,1 from (values(1,2) union all select 2,2 from c); 252 ---- 253 1 2 1 254 255 statement ok 256 INSERT INTO a VALUES (1) 257 258 statement ok 259 INSERT INTO c VALUES (1,2) 260 261 statement ok 262 INSERT INTO c VALUES (3,4) 263 264 # Check that UNION ALL columns are mapped correctly - even if one side gets optimized out 265 query I 266 SELECT a FROM (SELECT a FROM a UNION ALL SELECT a FROM c) ORDER BY a 267 ---- 268 1 269 1 270 3 271 272 query I 273 SELECT a FROM (SELECT a FROM a WHERE a > 3 AND a < 1 UNION ALL SELECT a FROM c) ORDER BY a 274 ---- 275 1 276 3 277 278 query I 279 SELECT a FROM (SELECT a FROM c UNION ALL SELECT a FROM a WHERE a > 3 AND a < 1) ORDER BY a 280 ---- 281 1 282 3 283 284 query I 285 SELECT a FROM (SELECT a FROM c UNION ALL SELECT a FROM a) WHERE a > 0 AND a < 3 286 ---- 287 1 288 1 289 290 query I 291 SELECT 1 FROM (SELECT a FROM a WHERE a > 3 UNION ALL SELECT a FROM c) 292 ---- 293 1 294 1 295 296 query R 297 SELECT * from ((values (1.0::decimal)) EXCEPT (values (1.00::decimal))) WHERE column1::string != '1.00'; 298 ---- 299 300 # Regression test for 41973 (union operator with no output columns). 301 statement ok 302 CREATE TABLE tab41973 () 303 304 statement ok 305 INSERT INTO tab41973 (rowid) VALUES (1), (2), (3) 306 307 query I 308 SELECT 1 FROM ((SELECT * FROM tab41973) UNION ALL (SELECT * FROM tab41973)) 309 ---- 310 1 311 1 312 1 313 1 314 1 315 1 316 317 statement ok 318 DROP TABLE IF EXISTS t1, t2; 319 CREATE TABLE t1 (j JSONB); 320 CREATE TABLE t2 (j JSONB); 321 INSERT INTO t1 VALUES ('{"a": "b"}'), ('{"foo": "bar"}'), (NULL); 322 INSERT INTO t2 VALUES ('{"c": "d"}'), ('{"foo": "bar"}'), (NULL); 323 324 query T rowsort 325 (SELECT j FROM t1) UNION (SELECT j FROM t2) 326 ---- 327 {"a": "b"} 328 {"c": "d"} 329 {"foo": "bar"} 330 NULL 331 332 statement ok 333 DROP TABLE IF EXISTS t1, t2; 334 CREATE TABLE t1 (a INT[]); 335 CREATE TABLE t2 (b INT[]); 336 INSERT INTO t1 VALUES (ARRAY[1]), (ARRAY[2]), (NULL); 337 INSERT INTO t2 VALUES (ARRAY[2]), (ARRAY[3]), (NULL); 338 339 query T rowsort 340 (SELECT a FROM t1) UNION (SELECT b FROM t2) 341 ---- 342 {1} 343 {2} 344 NULL 345 {3} 346 347 # Allow UNION of hidden and non-hidden columns. 348 statement ok 349 CREATE TABLE ab (a INT, b INT); 350 SELECT a, b, rowid FROM ab UNION VALUES (1, 2, 3);