github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/distsql_union (about) 1 # LogicTest: 5node-default-configs 2 3 statement ok 4 CREATE TABLE xyz ( 5 x INT, 6 y INT, 7 z TEXT 8 ) 9 10 statement ok 11 INSERT INTO xyz VALUES 12 (NULL, NULL, NULL), 13 (1, 1, NULL), 14 (2, 1, 'a'), 15 (3, 1, 'b'), 16 (4, 2, 'b'), 17 (5, 2, 'c') 18 19 statement ok 20 ALTER TABLE xyz SPLIT AT VALUES (2), (3), (4), (5) 21 22 statement ok 23 ALTER TABLE xyz EXPERIMENTAL_RELOCATE VALUES 24 (ARRAY[1], 1), 25 (ARRAY[2], 2), 26 (ARRAY[3], 3), 27 (ARRAY[4], 4), 28 (ARRAY[5], 5) 29 30 query TTTI colnames 31 SELECT start_key, end_key, replicas, lease_holder FROM [SHOW RANGES FROM TABLE xyz] 32 ---- 33 start_key end_key replicas lease_holder 34 NULL /2 {1} 1 35 /2 /3 {2} 2 36 /3 /4 {3} 3 37 /4 /5 {4} 4 38 /5 NULL {5} 5 39 40 subtest Union 41 42 # Simple UNION ALL and UNION. (The ORDER BY applies to the UNION, not the last select.) 43 query I 44 SELECT x FROM xyz UNION ALL SELECT x FROM xyz ORDER BY x 45 ---- 46 NULL 47 NULL 48 1 49 1 50 2 51 2 52 3 53 3 54 4 55 4 56 5 57 5 58 59 query I 60 SELECT x FROM xyz UNION SELECT x FROM xyz ORDER BY x 61 ---- 62 NULL 63 1 64 2 65 3 66 4 67 5 68 69 # UNION with no overlap. 70 query I 71 SELECT x FROM xyz WHERE x < 3 UNION SELECT x FROM xyz WHERE x >= 3 ORDER BY x 72 ---- 73 1 74 2 75 3 76 4 77 5 78 79 # UNION with partial overlap. 80 query I 81 SELECT x FROM xyz WHERE x <= 4 UNION SELECT x FROM xyz WHERE x > 1 ORDER BY x 82 ---- 83 1 84 2 85 3 86 4 87 5 88 89 # UNION ALL with swapped column orders. 90 query II rowsort 91 SELECT x, y FROM xyz UNION ALL SELECT y, x from xyz 92 ---- 93 NULL NULL 94 NULL NULL 95 1 1 96 1 1 97 1 2 98 1 3 99 2 1 100 2 4 101 2 5 102 3 1 103 4 2 104 5 2 105 106 # UNION ALL and UNION with different ORDER BY types. 107 query I 108 (SELECT x FROM xyz ORDER BY y) UNION ALL (SELECT x FROM xyz ORDER BY z) ORDER BY x 109 ---- 110 NULL 111 NULL 112 1 113 1 114 2 115 2 116 3 117 3 118 4 119 4 120 5 121 5 122 123 query I 124 (SELECT x FROM xyz ORDER BY y) UNION (SELECT x FROM xyz ORDER BY z) ORDER BY x 125 ---- 126 NULL 127 1 128 2 129 3 130 4 131 5 132 133 # UNION ALL with conflicting numbers of ORDER BY columns. 134 query I 135 (SELECT x FROM xyz ORDER BY y) UNION ALL (SELECT x FROM xyz ORDER BY y, z) ORDER BY x 136 ---- 137 NULL 138 NULL 139 1 140 1 141 2 142 2 143 3 144 3 145 4 146 4 147 5 148 5 149 150 query I rowsort 151 VALUES (1), (2) UNION VALUES (2), (3) 152 ---- 153 1 154 2 155 3 156 157 subtest Intersect 158 159 # Basic INTERSECT ALL and INTERSECT case -- should return every row. 160 query I 161 (SELECT y FROM xyz) INTERSECT ALL (SELECT y FROM xyz) ORDER BY y 162 ---- 163 NULL 164 1 165 1 166 1 167 2 168 2 169 170 query I 171 (SELECT y FROM xyz) INTERSECT (SELECT y FROM xyz) ORDER BY y 172 ---- 173 NULL 174 1 175 2 176 177 # INTERSECT ALL and INTERSECT with MergeJoiner. 178 query I rowsort 179 (SELECT y FROM xyz ORDER BY y) INTERSECT ALL (SELECT y FROM xyz ORDER BY y) 180 ---- 181 NULL 182 1 183 1 184 1 185 2 186 2 187 188 query I rowsort 189 (SELECT y FROM xyz ORDER BY y) INTERSECT (SELECT y FROM xyz ORDER BY y) 190 ---- 191 NULL 192 1 193 2 194 195 # INTERSECT ALL and INTERSECT with no overlap. 196 query I 197 (SELECT x FROM xyz WHERE x < 2) INTERSECT ALL (SELECT x FROM xyz WHERE x >= 2) ORDER BY x 198 ---- 199 200 201 query I 202 (SELECT x FROM xyz WHERE x < 2) INTERSECT (SELECT x FROM xyz WHERE x >= 2) ORDER BY x 203 ---- 204 205 206 # INTERSECT ALL and INTERSECT with some overlap. 207 query I 208 (SELECT y FROM xyz WHERE x < 3) INTERSECT ALL (SELECT y FROM xyz WHERE x >= 1) ORDER BY y 209 ---- 210 1 211 1 212 213 query I 214 (SELECT y FROM xyz WHERE x < 3) INTERSECT (SELECT y FROM xyz WHERE x >= 1) ORDER BY y 215 ---- 216 1 217 218 # INTERSECT ALL and INTERSECT with swapped column orders. 219 query II rowsort 220 SELECT x, y FROM xyz INTERSECT ALL SELECT y, x from xyz 221 ---- 222 NULL NULL 223 1 1 224 225 query II rowsort 226 SELECT x, y FROM xyz INTERSECT SELECT y, x from xyz 227 ---- 228 NULL NULL 229 1 1 230 231 # INTERSECT ALL and INTERSECT with different ORDER BY types. 232 query I 233 (SELECT x FROM xyz ORDER BY y) INTERSECT ALL (SELECT x FROM xyz ORDER BY z) ORDER BY x 234 ---- 235 NULL 236 1 237 2 238 3 239 4 240 5 241 242 query I 243 (SELECT x FROM xyz ORDER BY y) INTERSECT (SELECT x FROM xyz ORDER BY z) ORDER BY x 244 ---- 245 NULL 246 1 247 2 248 3 249 4 250 5 251 252 # INTERSECT ALL and INTERSECT with different numbers of ORDER BY columns. 253 query I 254 (SELECT x FROM xyz ORDER BY y) INTERSECT ALL (SELECT x FROM xyz ORDER BY y, z) ORDER BY x 255 ---- 256 NULL 257 1 258 2 259 3 260 4 261 5 262 263 query I 264 (SELECT x FROM xyz ORDER BY y) INTERSECT (SELECT x FROM xyz ORDER BY y, z) ORDER BY x 265 ---- 266 NULL 267 1 268 2 269 3 270 4 271 5 272 273 # INTERSECT ALL and INTERSECT with compatible ORDER BY columns that are not in the final result. 274 query I rowsort 275 (SELECT y FROM xyz ORDER BY z) INTERSECT ALL (SELECT y FROM xyz ORDER BY z) 276 ---- 277 NULL 278 1 279 1 280 1 281 2 282 2 283 284 query I rowsort 285 (SELECT y FROM xyz ORDER BY z) INTERSECT ALL (SELECT y FROM xyz ORDER BY z) 286 ---- 287 NULL 288 1 289 1 290 1 291 2 292 2 293 294 # INTERSECT ALL and INTERSECT with a projection on the result. 295 query I rowsort 296 SELECT x FROM ((SELECT x, y FROM xyz) INTERSECT ALL (SELECT x, y FROM xyz)) 297 ---- 298 NULL 299 1 300 2 301 3 302 4 303 5 304 305 query I rowsort 306 SELECT x FROM ((SELECT x, y FROM xyz) INTERSECT (SELECT x, y FROM xyz)) 307 ---- 308 NULL 309 1 310 2 311 3 312 4 313 5 314 315 subtest Except 316 317 # Basic EXCEPT ALL and EXCEPT case. 318 query I 319 (SELECT y FROM xyz) EXCEPT ALL (SELECT x AS y FROM xyz) ORDER BY y 320 ---- 321 1 322 1 323 2 324 325 query I 326 (SELECT y FROM xyz) EXCEPT (SELECT x AS y FROM xyz) ORDER BY y 327 ---- 328 329 330 # EXCEPT ALL and EXCEPT with MergeJoiner. 331 query I rowsort 332 (SELECT y FROM xyz ORDER BY y) EXCEPT ALL (SELECT y FROM xyz ORDER BY y) 333 ---- 334 335 336 query I rowsort 337 (SELECT y FROM xyz ORDER BY y) EXCEPT (SELECT y FROM xyz ORDER BY y) 338 ---- 339 340 341 # EXCEPT ALL and EXCEPT with no overlap. 342 query I 343 (SELECT x FROM xyz WHERE x < 2) EXCEPT ALL (SELECT x FROM xyz WHERE x >= 2) ORDER BY x 344 ---- 345 1 346 347 query I 348 (SELECT x FROM xyz WHERE x < 2) EXCEPT (SELECT x FROM xyz WHERE x >= 2) ORDER BY x 349 ---- 350 1 351 352 # EXCEPT ALL and EXCEPT with some overlap. 353 query I 354 (SELECT y FROM xyz WHERE x >= 1) EXCEPT ALL (SELECT y FROM xyz WHERE x < 3) ORDER BY y 355 ---- 356 1 357 2 358 2 359 360 query I 361 (SELECT y FROM xyz WHERE x >= 1) EXCEPT (SELECT y FROM xyz WHERE x < 3) ORDER BY y 362 ---- 363 2 364 365 # EXCEPT ALL and EXCEPT with swapped column orders. 366 query II rowsort 367 SELECT x, y FROM xyz EXCEPT ALL SELECT y, x from xyz 368 ---- 369 2 1 370 3 1 371 4 2 372 5 2 373 374 query II rowsort 375 SELECT x, y FROM xyz EXCEPT SELECT y, x from xyz 376 ---- 377 5 2 378 4 2 379 2 1 380 3 1 381 382 # EXCEPT ALL and EXCEPT with different ORDER BY types. 383 query I 384 (SELECT x FROM xyz ORDER BY y) EXCEPT ALL (SELECT y AS x FROM xyz ORDER BY z) ORDER BY x 385 ---- 386 3 387 4 388 5 389 390 query I 391 (SELECT x FROM xyz ORDER BY y) EXCEPT (SELECT y AS x FROM xyz ORDER BY z) ORDER BY x 392 ---- 393 3 394 4 395 5 396 397 # EXCEPT ALL and EXCEPT with different numbers of ORDER BY columns. 398 query I 399 (SELECT x FROM xyz ORDER BY y) EXCEPT ALL (SELECT x FROM xyz ORDER BY y, z) ORDER BY x 400 ---- 401 402 query I 403 (SELECT x FROM xyz ORDER BY y) EXCEPT (SELECT x FROM xyz ORDER BY y, z) ORDER BY x 404 ---- 405 406 # EXCEPT ALL and EXCEPT with compatible ORDER BY columns that are not in the final result. 407 query I rowsort 408 (SELECT y FROM xyz ORDER BY z) EXCEPT ALL (SELECT y FROM xyz ORDER BY z) 409 ---- 410 411 query I rowsort 412 (SELECT y FROM xyz ORDER BY z) EXCEPT (SELECT y FROM xyz ORDER BY z) 413 ---- 414 415 # EXCEPT ALL and EXCEPT with a projection on the result. 416 query I rowsort 417 SELECT x FROM ((SELECT x, y FROM xyz) EXCEPT ALL (SELECT x, y FROM xyz)) 418 ---- 419 420 query I rowsort 421 SELECT x FROM ((SELECT x, y FROM xyz) EXCEPT (SELECT x, y FROM xyz)) 422 ----