github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/subquery (about) 1 # LogicTest: !3node-tenant 2 # Tests for subqueries (SELECT statements which are part of a bigger statement). 3 4 query I 5 SELECT (SELECT 1) 6 ---- 7 1 8 9 query B 10 SELECT 1 IN (SELECT 1) 11 ---- 12 true 13 14 query B 15 SELECT 1 IN ((((SELECT 1)))) 16 ---- 17 true 18 19 query I 20 SELECT ARRAY(((((VALUES (1), (2))))))[2] 21 ---- 22 2 23 24 query I 25 SELECT 1 + (SELECT 1) 26 ---- 27 2 28 29 query error unsupported binary operator: <int> \+ <tuple{int AS a, int AS b}> 30 SELECT 1 + (SELECT 1 AS a, 2 AS b) 31 32 query B 33 SELECT (1, 2, 3) IN (SELECT 1, 2, 3) 34 ---- 35 true 36 37 query B 38 SELECT (1, 2, 3) = (SELECT 1, 2, 3) 39 ---- 40 true 41 42 query B 43 SELECT (1, 2, 3) != (SELECT 1, 2, 3) 44 ---- 45 false 46 47 query B 48 SELECT (SELECT 1, 2, 3) = (SELECT 1, 2, 3) 49 ---- 50 true 51 52 query B 53 SELECT (SELECT 1) IN (SELECT 1) 54 ---- 55 true 56 57 query B 58 SELECT (SELECT 1) IN (1) 59 ---- 60 true 61 62 # NB: Cockroach has different behavior from Postgres on a few esoteric 63 # subqueries. The Cockroach behavior seems more sensical and 64 # supporting the specific Postgres behavior appears onerous. Fingers 65 # crossed this doesn't bite us down the road. 66 67 # Postgres cannot handle this query (but MySQL can), even though it 68 # seems sensical: 69 # ERROR: subquery must return only one column 70 # LINE 1: select (select 1, 2) IN (select 1, 2); 71 # ^ 72 query B 73 SELECT (SELECT 1, 2) IN (SELECT 1, 2) 74 ---- 75 true 76 77 # Postgres cannot handle this query, even though it seems sensical: 78 # ERROR: subquery must return only one column 79 # LINE 1: select (select 1, 2) IN ((1, 2)); 80 # ^ 81 query B 82 SELECT (SELECT 1, 2) IN ((1, 2)) 83 ---- 84 true 85 86 # Postgres cannot handle this query, even though it seems sensical: 87 # ERROR: subquery has too many columns 88 # LINE 1: select (select (1, 2)) IN (select 1, 2); 89 # ^ 90 query B 91 SELECT (SELECT (1, 2)) IN (SELECT 1, 2) 92 ---- 93 true 94 95 query B 96 SELECT (SELECT (1, 2)) IN ((1, 2)) 97 ---- 98 true 99 100 # Postgres cannot handle this query, even though it seems sensical: 101 # ERROR: subquery must return only one column 102 # LINE 1: select (select 1, 2) in (select (1, 2)); 103 # ^ 104 query B 105 SELECT (SELECT 1, 2) IN (SELECT (1, 2)) 106 ---- 107 true 108 109 query B 110 SELECT (SELECT (1, 2)) IN (SELECT (1, 2)) 111 ---- 112 true 113 114 query B 115 SELECT 1 = ANY(SELECT 1) 116 ---- 117 true 118 119 query B 120 SELECT (1, 2) = ANY(SELECT 1, 2) 121 ---- 122 true 123 124 query B 125 SELECT 1 = SOME(SELECT 1) 126 ---- 127 true 128 129 query B 130 SELECT (1, 2) = SOME(SELECT 1, 2) 131 ---- 132 true 133 134 query B 135 SELECT 1 = ALL(SELECT 1) 136 ---- 137 true 138 139 query B 140 SELECT (1, 2) = ALL(SELECT 1, 2) 141 ---- 142 true 143 144 query error pgcode 42601 subquery must return only one column, found 2 145 SELECT (SELECT 1, 2) 146 147 query error unsupported comparison operator: <int> IN <tuple{tuple{int AS a, int AS b}}> 148 SELECT 1 IN (SELECT 1 AS a, 2 AS b) 149 150 query error unsupported comparison operator: <tuple{int, int}> IN <tuple{int}> 151 SELECT (1, 2) IN (SELECT 1 AS a) 152 153 statement ok 154 CREATE TABLE abc (a INT PRIMARY KEY, b INT, c INT) 155 156 statement ok 157 INSERT INTO abc VALUES (1, 2, 3), (4, 5, 6) 158 159 statement ok 160 ALTER TABLE abc SPLIT AT VALUES ((SELECT 1)) 161 162 query error unsupported comparison operator: <tuple{int, int}> IN <tuple{tuple{int AS a, int AS b, int AS c}}> 163 SELECT (1, 2) IN (SELECT * FROM abc) 164 165 query B 166 SELECT (1, 2) IN (SELECT a, b FROM abc) 167 ---- 168 true 169 170 query B 171 SELECT (1, 2) IN (SELECT a, b FROM abc WHERE false) 172 ---- 173 false 174 175 query error subquery must return only one column 176 SELECT (SELECT * FROM abc) 177 178 query error more than one row returned by a subquery used as an expression 179 SELECT (SELECT a FROM abc) 180 181 query I 182 SELECT (SELECT a FROM abc WHERE false) 183 ---- 184 NULL 185 186 query II 187 VALUES (1, (SELECT (2))) 188 ---- 189 1 2 190 191 statement ok 192 INSERT INTO abc VALUES ((SELECT 7), (SELECT 8), (SELECT 9)) 193 194 query III 195 SELECT * FROM abc WHERE a = 7 196 ---- 197 7 8 9 198 199 statement error value type tuple{int, int, int} doesn't match type int of column "a" 200 INSERT INTO abc VALUES ((SELECT (10, 11, 12))) 201 202 statement error subquery must return only one column, found 3 203 INSERT INTO abc VALUES ((SELECT 10, 11, 12)) 204 205 statement ok 206 CREATE TABLE xyz (x INT PRIMARY KEY, y INT, z INT) 207 208 statement ok 209 INSERT INTO xyz SELECT * FROM abc 210 211 query III rowsort 212 SELECT * FROM xyz 213 ---- 214 1 2 3 215 4 5 6 216 7 8 9 217 218 statement ok 219 INSERT INTO xyz (x, y, z) VALUES (10, 11, 12) 220 221 statement ok 222 UPDATE xyz SET z = (SELECT 10) WHERE x = 7 223 224 query III rowsort 225 SELECT * FROM xyz 226 ---- 227 1 2 3 228 4 5 6 229 7 8 10 230 10 11 12 231 232 statement error value type tuple{int, int} doesn't match type int of column "z" 233 UPDATE xyz SET z = (SELECT (10, 11)) WHERE x = 7 234 235 statement error number of columns \(2\) does not match number of values \(1\) 236 UPDATE xyz SET (y, z) = (SELECT (11, 12)) WHERE x = 7 237 238 query B 239 SELECT 1 IN (SELECT x FROM xyz ORDER BY x DESC) 240 ---- 241 true 242 243 query III 244 SELECT * FROM xyz WHERE x = (SELECT min(x) FROM xyz) 245 ---- 246 1 2 3 247 248 query III 249 SELECT * FROM xyz WHERE x = (SELECT max(x) FROM xyz) 250 ---- 251 10 11 12 252 253 query III 254 SELECT * FROM xyz WHERE x = (SELECT max(x) FROM xyz WHERE EXISTS(SELECT * FROM xyz WHERE z=x+3)) 255 ---- 256 10 11 12 257 258 statement ok 259 UPDATE xyz SET (y, z) = (SELECT 11, 12) WHERE x = 7 260 261 query III rowsort 262 SELECT * FROM xyz 263 ---- 264 1 2 3 265 4 5 6 266 7 11 12 267 10 11 12 268 269 statement ok 270 CREATE TABLE kv (k INT PRIMARY KEY, v STRING) 271 272 statement ok 273 INSERT INTO kv VALUES (1, 'one') 274 275 query IT 276 SELECT * FROM kv WHERE k = (SELECT k FROM kv WHERE (k, v) = (1, 'one')) 277 ---- 278 1 one 279 280 query B 281 SELECT EXISTS(SELECT 1 FROM kv AS x WHERE x.k = 1) 282 ---- 283 true 284 285 query B 286 SELECT EXISTS(SELECT 1 FROM kv WHERE k = 2) 287 ---- 288 false 289 290 291 # Tests for subquery in the FROM part of a SELECT 292 293 query II colnames,rowsort 294 SELECT * FROM (VALUES (1, 2)) AS foo 295 ---- 296 column1 column2 297 1 2 298 299 query II colnames,rowsort 300 SELECT * FROM (VALUES (1, 2)) 301 ---- 302 column1 column2 303 1 2 304 305 query IT colnames,rowsort 306 SELECT * FROM (VALUES (1, 'one'), (2, 'two'), (3, 'three')) AS foo 307 ---- 308 column1 column2 309 1 one 310 2 two 311 3 three 312 313 query III colnames,rowsort 314 SELECT * FROM (VALUES (1, 2, 3), (4, 5, 6)) AS foo 315 ---- 316 column1 column2 column3 317 1 2 3 318 4 5 6 319 320 query III colnames,rowsort 321 SELECT * FROM (VALUES (1, 2, 3), (4, 5, 6)) AS foo (foo1, foo2, foo3) 322 ---- 323 foo1 foo2 foo3 324 1 2 3 325 4 5 6 326 327 query III colnames,rowsort 328 SELECT * FROM (VALUES (1, 2, 3), (4, 5, 6)) AS foo (foo1, foo2) 329 ---- 330 foo1 foo2 column3 331 1 2 3 332 4 5 6 333 334 query III colnames,rowsort 335 SELECT * FROM (SELECT * FROM xyz) AS foo WHERE x < 7 336 ---- 337 x y z 338 1 2 3 339 4 5 6 340 341 query III colnames,rowsort 342 SELECT * FROM (SELECT * FROM xyz) AS foo (foo1) WHERE foo1 < 7 343 ---- 344 foo1 y z 345 1 2 3 346 4 5 6 347 348 query III colnames,rowsort 349 SELECT * FROM (SELECT * FROM xyz AS moo (moo1, moo2, moo3)) as foo (foo1) WHERE foo1 < 7 350 ---- 351 foo1 moo2 moo3 352 1 2 3 353 4 5 6 354 355 query III colnames,rowsort 356 SELECT * FROM (SELECT * FROM xyz AS moo (moo1, moo2, moo3) ORDER BY moo1) as foo (foo1) WHERE foo1 < 7 357 ---- 358 foo1 moo2 moo3 359 1 2 3 360 4 5 6 361 362 query III colnames 363 SELECT * FROM (SELECT * FROM xyz AS moo (moo1, moo2, moo3) ORDER BY moo1) as foo (foo1) WHERE foo1 < 7 ORDER BY moo2 DESC 364 ---- 365 foo1 moo2 moo3 366 4 5 6 367 1 2 3 368 369 query III colnames 370 SELECT * FROM (SELECT * FROM (VALUES (1, 2, 3), (4, 5, 6)) AS moo (moo1, moo2, moo3) WHERE moo1 = 4) as foo (foo1) 371 ---- 372 foo1 moo2 moo3 373 4 5 6 374 375 query III colnames 376 SELECT * FROM (SELECT * FROM (VALUES (1, 8, 8), (3, 1, 1), (2, 4, 4)) AS moo (moo1, moo2, moo3) ORDER BY moo2) as foo (foo1) ORDER BY foo1 377 ---- 378 foo1 moo2 moo3 379 1 8 8 380 2 4 4 381 3 1 1 382 383 query II colnames 384 SELECT a, b FROM (VALUES (1, 2, 3), (3, 4, 7), (5, 6, 10)) AS foo (a, b, c) WHERE a + b = c 385 ---- 386 a b 387 1 2 388 3 4 389 390 query I colnames 391 SELECT foo.a FROM (VALUES (1), (2), (3)) AS foo (a) 392 ---- 393 a 394 1 395 2 396 3 397 398 query IITT colnames 399 SELECT foo.a, a, column2, foo.column2 FROM (VALUES (1, 'one'), (2, 'two'), (3, 'three')) AS foo (a) 400 ---- 401 a a column2 column2 402 1 1 one one 403 2 2 two two 404 3 3 three three 405 406 query I 407 SELECT x FROM xyz WHERE x IN (SELECT x FROM xyz WHERE x = 7) 408 ---- 409 7 410 411 query I 412 SELECT x FROM xyz WHERE x = 7 LIMIT (SELECT x FROM xyz WHERE x = 1) 413 ---- 414 7 415 416 query I 417 SELECT x FROM xyz ORDER BY x OFFSET (SELECT x FROM xyz WHERE x = 1) 418 ---- 419 4 420 7 421 10 422 423 query B 424 INSERT INTO xyz (x, y, z) VALUES (13, 11, 12) RETURNING (y IN (SELECT y FROM xyz)) 425 ---- 426 true 427 428 # This test checks that the double sub-query plan expansion caused by a 429 # sub-expression being shared by two or more plan nodes does not 430 # panic. 431 statement ok 432 CREATE TABLE tab4(col0 INTEGER, col1 FLOAT, col3 INTEGER, col4 FLOAT) 433 434 statement ok 435 INSERT INTO tab4 VALUES (1,1,1,1) 436 437 statement ok 438 CREATE INDEX idx_tab4_0 ON tab4 (col4,col0) 439 440 query I 441 SELECT col0 FROM tab4 WHERE (col0 <= 0 AND col4 <= 5.38) OR (col4 IN (SELECT col1 FROM tab4 WHERE col1 > 8.27)) AND (col3 <= 5 AND (col3 BETWEEN 7 AND 9)) 442 ---- 443 444 statement ok 445 CREATE TABLE z (z INT PRIMARY KEY) 446 447 # Regression test for #24171. 448 query I 449 SELECT * FROM z WHERE CAST(COALESCE((SELECT 'a' FROM crdb_internal.zones LIMIT 1 OFFSET 5), (SELECT 'b' FROM pg_catalog.pg_trigger)) AS BYTEA) <= 'a' 450 ---- 451 452 # Regression test for #24170. 453 query I 454 SELECT * FROM z WHERE CAST(COALESCE((SELECT 'a'), (SELECT 'a')) AS bytea) < 'a' 455 ---- 456 457 statement ok 458 CREATE TABLE test (a INT PRIMARY KEY) 459 460 statement ok 461 CREATE TABLE test2(b INT PRIMARY KEY) 462 463 # Regression test for #24225. 464 query I 465 SELECT * FROM test2 WHERE 0 = CASE WHEN true THEN (SELECT a FROM test LIMIT 1) ELSE 10 END 466 ---- 467 468 # Regression test for #28335. 469 query I 470 SELECT (SELECT ARRAY(SELECT 1))[1] 471 ---- 472 1 473 474 query B 475 SELECT (SELECT 123 IN (VALUES (1), (2))) 476 ---- 477 false 478 479 statement error pq: subqueryfail 480 SELECT * FROM xyz WHERE x IN (SELECT crdb_internal.force_error('', 'subqueryfail')) 481 482 statement ok 483 PREPARE a AS SELECT 1 = (SELECT $1:::int) 484 485 query B 486 EXECUTE a(1) 487 ---- 488 true 489 490 query B 491 EXECUTE a(2) 492 ---- 493 false 494 495 statement ok 496 PREPARE b AS SELECT EXISTS (SELECT $1:::int) 497 498 query B 499 EXECUTE b(3) 500 ---- 501 true 502 503 # Regression test for #29205 - make sure the memory account for wrapped local 504 # planNode within subqueries is properly hooked up. 505 506 statement ok 507 CREATE TABLE a (a TEXT PRIMARY KEY) 508 509 statement ok 510 SELECT (SELECT repeat(a::STRING, 2) FROM [INSERT INTO a VALUES('foo') RETURNING a]); 511 512 statement ok 513 UPDATE abc SET a = 2, (b, c) = (SELECT 5, 6) WHERE a = 1; 514 515 # Failure in outer query with mutations in the subquery do not take effect. 516 statement error pq: bar 517 SELECT crdb_internal.force_error('foo', 'bar') FROM [INSERT INTO abc VALUES (11,12,13) RETURNING a] 518 519 query III 520 SELECT * FROM abc WHERE a = 11 521 ---- 522 523 statement error pq: bar 524 INSERT INTO abc VALUES (1,2, (SELECT crdb_internal.force_error('foo', 'bar'))) 525 526 # Regression test for #37263. 527 query B 528 SELECT 3::decimal IN (SELECT 1) 529 ---- 530 false 531 532 query error unsupported comparison operator 533 SELECT 3::decimal IN (SELECT 1::int) 534 535 query B 536 SELECT 1 IN (SELECT '1'); 537 ---- 538 true 539 540 # Regression test for #14554. 541 query ITIIIII 542 SELECT 543 t.oid, t.typname, t.typsend, t.typreceive, t.typoutput, t.typinput, t.typelem 544 FROM 545 pg_type AS t 546 WHERE 547 t.oid 548 NOT IN (SELECT (ARRAY[704, 11676, 10005, 3912, 11765, 59410, 11397])[i] FROM generate_series(1, 376) AS i) 549 ----