github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/suboperators (about) 1 statement ok 2 CREATE TABLE abc (a INT, b INT, C INT) 3 4 statement ok 5 INSERT INTO abc VALUES (1, 10, 100), (2, 20, 200), (3, 30, 300), (NULL, NULL, NULL) 6 7 # ANY/SOME with arrays. 8 9 query B 10 SELECT 1 = ANY(ARRAY[1, 2]) 11 ---- 12 true 13 14 query B 15 SELECT 1 = ANY (((ARRAY[1, 2]))) 16 ---- 17 true 18 19 query B 20 SELECT 1 = SOME(ARRAY[1, 2]) 21 ---- 22 true 23 24 query B 25 SELECT 1 = ANY(ARRAY[3, 4]) 26 ---- 27 false 28 29 query B 30 SELECT 1 = ANY (((ARRAY[3, 4]))) 31 ---- 32 false 33 34 query B 35 SELECT 1 < ANY(ARRAY[0, 5]) 36 ---- 37 true 38 39 query B 40 SELECT 1 < ANY(ARRAY[0, 1]) 41 ---- 42 false 43 44 query B 45 SELECT 1 = ANY(ARRAY[1.0, 1.1]) 46 ---- 47 true 48 49 query B 50 SELECT 1 < ANY(ARRAY[1.0, 1.1]) 51 ---- 52 true 53 54 query B 55 SELECT 1 = ANY(ARRAY[1, NULL]) 56 ---- 57 true 58 59 query T 60 SELECT 1 = ANY(ARRAY[2, NULL]) 61 ---- 62 NULL 63 64 query T 65 SELECT 1 = ANY(ARRAY[NULL, NULL]) 66 ---- 67 NULL 68 69 query B 70 SELECT 1 = ANY(ARRAY[1,2] || 3) 71 ---- 72 true 73 74 query B 75 SELECT 1 = ANY(ARRAY[2,3] || 1) 76 ---- 77 true 78 79 query B 80 SELECT 1 = ANY(ARRAY[2,3] || 4) 81 ---- 82 false 83 84 query III 85 SELECT * FROM abc WHERE a = ANY(ARRAY[1,3]) ORDER BY a 86 ---- 87 1 10 100 88 3 30 300 89 90 query III 91 SELECT * FROM abc WHERE a = ANY(ARRAY[4, 5]) 92 ---- 93 94 query III 95 SELECT * FROM abc WHERE a = ANY(ARRAY[1, NULL]) 96 ---- 97 1 10 100 98 99 query III 100 SELECT * FROM abc WHERE a = ANY(ARRAY[4, NULL]) 101 ---- 102 103 query III 104 SELECT * FROM abc WHERE a = ANY(ARRAY[NULL, NULL]) 105 ---- 106 107 query error unsupported comparison operator: 1 = ANY ARRAY\['foo', 'bar'\] 108 SELECT 1 = ANY(ARRAY['foo', 'bar']) 109 110 query error unsupported comparison operator: <int> = ANY <string\[\]> 111 SELECT 1 = ANY(ARRAY['foo'] || 'bar'::string) 112 113 # Note that this relatively poor error message is caused by the fact that 114 # strings are constant castable to string arrays. Postgres also makes this 115 # same minor mistake in error generation. 116 query error unsupported binary operator: <string\[\]> || <string> (desired <int\[\]>) 117 SELECT 1 = ANY(ARRAY['foo'] || 'bar') 118 119 # ANY/SOME with subqueries. 120 121 query B 122 SELECT 1 = ANY(SELECT * FROM generate_series(1,3)) 123 ---- 124 true 125 126 query B 127 SELECT 1 = ANY(SELECT * FROM generate_series(2,4)) 128 ---- 129 false 130 131 query B 132 SELECT 1 < ANY(SELECT * FROM generate_series(1,3)) 133 ---- 134 true 135 136 query B 137 SELECT 1 < ANY(SELECT * FROM generate_series(0,1)) 138 ---- 139 false 140 141 query B 142 SELECT 1 = ANY(SELECT * FROM unnest(ARRAY[1.0, 1.1])) 143 ---- 144 true 145 146 query B 147 SELECT 1 = ANY(SELECT * FROM unnest(ARRAY[1.0, 1.1])) 148 ---- 149 true 150 151 query B 152 SELECT 1.0 < ANY(SELECT * FROM unnest(ARRAY[1.0, 1.1])) 153 ---- 154 true 155 156 query B 157 SELECT 1.0 = ANY(SELECT * FROM unnest(ARRAY[1.0001, 2])) 158 ---- 159 false 160 161 query B 162 SELECT 1 = ANY(SELECT * FROM unnest(ARRAY[1, NULL])) 163 ---- 164 true 165 166 query T 167 SELECT 1 = ANY(SELECT * FROM unnest(ARRAY[2, NULL])) 168 ---- 169 NULL 170 171 query T 172 SELECT 1 = ANY(SELECT * FROM unnest(ARRAY[NULL, NULL])) 173 ---- 174 NULL 175 176 query III 177 SELECT * FROM abc WHERE a = ANY(SELECT a FROM abc WHERE b = 10) 178 ---- 179 1 10 100 180 181 query III 182 SELECT * FROM abc WHERE a < ANY(SELECT a FROM abc WHERE b = 30) ORDER BY a 183 ---- 184 1 10 100 185 2 20 200 186 187 query III 188 SELECT * FROM abc WHERE a > ANY(SELECT a FROM abc WHERE b = 30) 189 ---- 190 191 # ANY predicate in disjunction. 192 query III rowsort 193 SELECT * FROM abc WHERE a > ANY(SELECT a FROM abc WHERE b = 20) OR b IS NULL 194 ---- 195 NULL NULL NULL 196 3 30 300 197 198 # ALL predicate in disjunction. 199 query III rowsort 200 SELECT * FROM abc WHERE a >= ALL(SELECT a FROM abc WHERE a IS NOT NULL) OR b=10 201 ---- 202 1 10 100 203 3 30 300 204 205 # ANY predicate in NOT NULL expression. 206 query III rowsort 207 SELECT * FROM abc WHERE a > ANY(SELECT a FROM abc WHERE b = 20) IS NULL 208 ---- 209 NULL NULL NULL 210 211 query III 212 SELECT * FROM abc WHERE a = ANY(SELECT * FROM unnest(ARRAY[1, NULL])) 213 ---- 214 1 10 100 215 216 query III 217 SELECT * FROM abc WHERE a = ANY(SELECT * FROM unnest(ARRAY[4, NULL])) 218 ---- 219 220 query III 221 SELECT * FROM abc WHERE a = ANY(SELECT * FROM unnest(ARRAY[NULL, NULL])) 222 ---- 223 224 query error unsupported comparison operator: <int> = ANY <tuple\{string\}> 225 SELECT 1 = ANY(SELECT * FROM unnest(ARRAY['foo', 'bar'])) 226 227 # ALL with arrays. 228 229 query B 230 SELECT 1 = ALL(ARRAY[1, 1, 1.0]) 231 ---- 232 true 233 234 query B 235 SELECT 1 = ALL(ARRAY[1, 1.001, 1.0]) 236 ---- 237 false 238 239 query B 240 SELECT 5 > ALL(ARRAY[1, 2, 3]) 241 ---- 242 true 243 244 query B 245 SELECT 5 > ALL(ARRAY[6, 7, 8]) 246 ---- 247 false 248 249 query B 250 SELECT 5 > ALL(ARRAY[4, 6, 7]) 251 ---- 252 false 253 254 query B 255 SELECT 1 = ALL(ARRAY[2, NULL]) 256 ---- 257 false 258 259 query T 260 SELECT 1 = ALL(ARRAY[1, NULL]) 261 ---- 262 NULL 263 264 query T 265 SELECT 1 = ALL(ARRAY[NULL, NULL]) 266 ---- 267 NULL 268 269 query B 270 SELECT 5 > ALL(ARRAY[1, 2] || 3) 271 ---- 272 true 273 274 query B 275 SELECT 5 > ALL(ARRAY[6, 7] || 8) 276 ---- 277 false 278 279 query III 280 SELECT * FROM abc WHERE a > ALL(ARRAY[0, 1]) ORDER BY a 281 ---- 282 2 20 200 283 3 30 300 284 285 query III 286 SELECT * FROM abc WHERE a > ALL(ARRAY[1, 4]) 287 ---- 288 289 query III 290 SELECT * FROM abc WHERE a > ALL(ARRAY[1, NULL]) 291 ---- 292 293 query III 294 SELECT * FROM abc WHERE a > ALL(ARRAY[NULL, NULL]) 295 ---- 296 297 query error unsupported comparison operator: 1 = ALL ARRAY\['foo', 'bar'\] 298 SELECT 1 = ALL(ARRAY['foo', 'bar']) 299 300 query error unsupported comparison operator: <int> = ALL <string\[\]> 301 SELECT 1 = ALL(ARRAY['foo'] || 'bar'::text) 302 303 # ALL with subqueries. 304 305 query B 306 SELECT 1 = ALL(SELECT * FROM unnest(ARRAY[1,1,1])) 307 ---- 308 true 309 310 query B 311 SELECT 1 = ALL(SELECT * FROM unnest(ARRAY[1,2,3])) 312 ---- 313 false 314 315 query B 316 SELECT 1 < ALL(SELECT * FROM generate_series(2,5)) 317 ---- 318 true 319 320 query B 321 SELECT 1 < ALL(SELECT * FROM generate_series(1,3)) 322 ---- 323 false 324 325 query B 326 SELECT 1 = ALL(SELECT * FROM unnest(ARRAY[2, NULL])) 327 ---- 328 false 329 330 query T 331 SELECT 1 = ALL(SELECT * FROM unnest(ARRAY[1, NULL])) 332 ---- 333 NULL 334 335 query T 336 SELECT 1 = ALL(SELECT * FROM unnest(ARRAY[NULL, NULL])) 337 ---- 338 NULL 339 340 query III 341 SELECT * FROM abc WHERE a < ALL(SELECT b FROM abc WHERE b IS NOT NULL) ORDER BY a 342 ---- 343 1 10 100 344 2 20 200 345 3 30 300 346 347 query III 348 SELECT * FROM abc WHERE a < ALL(SELECT a FROM abc WHERE a >= 2) 349 ---- 350 1 10 100 351 352 query III 353 SELECT * FROM abc WHERE a < ALL(SELECT a FROM abc) 354 ---- 355 356 query III 357 SELECT * FROM abc WHERE a > ALL(SELECT * FROM unnest(ARRAY[1, NULL])) 358 ---- 359 360 query III 361 SELECT * FROM abc WHERE a > ALL(SELECT * FROM unnest(ARRAY[NULL, NULL])) 362 ---- 363 364 query error unsupported comparison operator: <int> = ALL <tuple\{string\}> 365 SELECT 1 = ALL(SELECT * FROM unnest(ARRAY['foo', 'bar'])) 366 367 # ANY/ALL with tuples. 368 369 query B 370 SELECT 1 = ANY (1, 2, 3) 371 ---- 372 true 373 374 query error pq: could not parse "foo" as type int 375 SELECT 1 = ANY (1, 2, 3.3, 'foo') 376 377 query B 378 SELECT 1 = ANY (((1, 2, 3))) 379 ---- 380 true 381 382 query B 383 SELECT 1 = ANY (2, 3, 4) 384 ---- 385 false 386 387 query B 388 SELECT 1 = ANY (((2, 3, 4))) 389 ---- 390 false 391 392 query B 393 SELECT 1 = ANY (1, 1.1) 394 ---- 395 true 396 397 query B 398 SELECT 1::decimal = ANY (1, 1.1) 399 ---- 400 true 401 402 query B 403 SELECT 1 = ANY (1.0, 1.1) 404 ---- 405 true 406 407 query B 408 SELECT 1 = ANY (((1.0, 1.1))) 409 ---- 410 true 411 412 query B 413 SELECT 1::decimal = ANY (1.0, 1.1) 414 ---- 415 true 416 417 query B 418 SELECT 1::decimal = ANY (((1.0, 1.1))) 419 ---- 420 true 421 422 query error pq: could not parse "hello" as type int 423 SELECT 1 = ANY (1, 'hello', 3) 424 425 query B 426 SELECT 1 = ANY ROW() 427 ---- 428 false