github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/select (about) 1 # SELECT with no table. 2 3 query I 4 SELECT 1 5 ---- 6 1 7 8 query T 9 SELECT NULL 10 ---- 11 NULL 12 13 query II colnames 14 SELECT 1+1 AS two, 2+2 AS four 15 ---- 16 two four 17 2 4 18 19 # SELECT expression tests. 20 21 statement ok 22 CREATE TABLE abc (a INT PRIMARY KEY, b INT, c INT) 23 24 query error at or near "from": syntax error 25 SELECT FROM abc 26 27 query error could not parse "hello" as type bool 28 SELECT * FROM abc WHERE 'hello' 29 30 statement ok 31 INSERT INTO abc VALUES (1, 2, 3) 32 33 query III colnames 34 SELECT * FROM abc 35 ---- 36 a b c 37 1 2 3 38 39 query TIII colnames 40 SELECT NULL AS z, * FROM abc 41 ---- 42 z a b c 43 NULL 1 2 3 44 45 # synonym for SELECT * FROM abc 46 query III 47 TABLE abc 48 ---- 49 1 2 3 50 51 query error at or near "\*": syntax error 52 TABLE abc.* 53 54 query III colnames 55 SELECT * FROM abc WHERE NULL 56 ---- 57 a b c 58 59 query III colnames 60 SELECT * FROM abc WHERE a = NULL 61 ---- 62 a b c 63 64 query IIIIII colnames 65 SELECT *,* FROM abc 66 ---- 67 a b c a b c 68 1 2 3 1 2 3 69 70 query IIII colnames 71 SELECT a,a,a,a FROM abc 72 ---- 73 a a a a 74 1 1 1 1 75 76 query II colnames 77 SELECT a,c FROM abc 78 ---- 79 a c 80 1 3 81 82 query I colnames 83 SELECT a+b+c AS foo FROM abc 84 ---- 85 foo 86 6 87 88 # Contradiction 89 query III 90 SELECT * FROM abc WHERE a > 5 AND a < 5 91 ---- 92 93 # Contradiction with remainder filter 94 query III 95 SELECT * FROM abc WHERE a > 5 AND a < 5 AND b>=100 96 ---- 97 98 statement ok 99 INSERT INTO abc VALUES (0, 1, 2) 100 101 query II 102 SELECT a,b FROM abc WHERE CASE WHEN a != 0 THEN b/a > 1.5 ELSE false END 103 ---- 104 1 2 105 106 # SELECT of NULL value. 107 108 statement ok 109 CREATE TABLE kv (k CHAR PRIMARY KEY, v CHAR) 110 111 statement ok 112 INSERT INTO kv (k) VALUES ('a') 113 114 query TT 115 SELECT * FROM kv 116 ---- 117 a NULL 118 119 query TT 120 SELECT k,v FROM kv 121 ---- 122 a NULL 123 124 query T 125 SELECT v||'foo' FROM kv 126 ---- 127 NULL 128 129 query T 130 SELECT lower(v) FROM kv 131 ---- 132 NULL 133 134 query T 135 SELECT k FROM kv 136 ---- 137 1 value hashing to 60b725f10c9c85c70d97880dfe8191b3 138 139 query TT 140 SELECT kv.K,KV.v FROM kv 141 ---- 142 a NULL 143 144 query TT 145 SELECT kv.* FROM kv 146 ---- 147 a NULL 148 149 # Regression tests for #24169 150 query TT 151 SELECT test.kv.* FROM kv 152 ---- 153 a NULL 154 155 query TT 156 SELECT test.public.kv.* FROM kv 157 ---- 158 a NULL 159 160 query TT 161 SELECT test.public.kv.* FROM test.kv 162 ---- 163 a NULL 164 165 query TT 166 SELECT test.kv.* FROM test.public.kv 167 ---- 168 a NULL 169 170 query error no data source matches pattern: foo.\* 171 SELECT foo.* FROM kv 172 173 query error cannot use "\*" without a FROM clause 174 SELECT * 175 176 query error "kv.*" cannot be aliased 177 SELECT kv.* AS foo FROM kv 178 179 query error no data source matches pattern: bar.kv.\* 180 SELECT bar.kv.* FROM kv 181 182 # Don't panic with invalid names (#8024) 183 query error cannot subscript type tuple\{char AS k, char AS v\} because it is not an array 184 SELECT kv.*[1] FROM kv 185 186 query T colnames 187 SELECT FOO.k FROM kv AS foo WHERE foo.k = 'a' 188 ---- 189 k 190 a 191 192 query T 193 SELECT "foo"."v" FROM kv AS foo WHERE foo.k = 'a' 194 ---- 195 NULL 196 197 statement ok 198 CREATE TABLE kw ("from" INT PRIMARY KEY) 199 200 statement ok 201 INSERT INTO kw VALUES (1) 202 203 query III colnames 204 SELECT *, "from", kw."from" FROM kw 205 ---- 206 from from from 207 1 1 1 208 209 # SELECT from index. 210 211 statement ok 212 CREATE TABLE xyzw ( 213 x INT PRIMARY KEY, 214 y INT, 215 z INT, 216 w INT, 217 INDEX foo (z, y) 218 ) 219 220 statement ok 221 INSERT INTO xyzw VALUES (4, 5, 6, 7), (1, 2, 3, 4) 222 223 query error pq: column "x" does not exist 224 SELECT * FROM xyzw LIMIT x 225 226 query error pq: column "y" does not exist 227 SELECT * FROM xyzw OFFSET 1 + y 228 229 query error argument of LIMIT must be type int, not type decimal 230 SELECT * FROM xyzw LIMIT 3.3 231 232 query IIII 233 SELECT * FROM xyzw ORDER BY 1 LIMIT '1' 234 ---- 235 1 2 3 4 236 237 query error argument of OFFSET must be type int, not type decimal 238 SELECT * FROM xyzw OFFSET 1.5 239 240 query error negative value for LIMIT 241 SELECT * FROM xyzw LIMIT -100 242 243 query error negative value for OFFSET 244 SELECT * FROM xyzw OFFSET -100 245 246 query error numeric constant out of int64 range 247 SELECT * FROM xyzw LIMIT 9223372036854775808 248 249 query error numeric constant out of int64 range 250 SELECT * FROM xyzw OFFSET 9223372036854775808 251 252 query IIII 253 SELECT * FROM xyzw ORDER BY x OFFSET 1 + 0.0 254 ---- 255 4 5 6 7 256 257 query T rowsort 258 SELECT (x,y) FROM xyzw 259 ---- 260 (1,2) 261 (4,5) 262 263 query IIII 264 SELECT * FROM xyzw LIMIT 0 265 ---- 266 267 query IIII 268 SELECT * FROM xyzw ORDER BY x LIMIT 1 269 ---- 270 1 2 3 4 271 272 query IIII 273 SELECT * FROM xyzw ORDER BY x LIMIT 1 OFFSET 1 274 ---- 275 4 5 6 7 276 277 query IIII 278 SELECT * FROM xyzw ORDER BY y OFFSET 1 279 ---- 280 4 5 6 7 281 282 query IIII 283 SELECT * FROM xyzw ORDER BY y OFFSET 1 LIMIT 1 284 ---- 285 4 5 6 7 286 287 # Multiplying by zero so the result is deterministic. 288 query IIII 289 SELECT * FROM xyzw LIMIT (random() * 0.0)::int OFFSET (random() * 0.0)::int 290 ---- 291 292 query error pgcode 42601 multiple LIMIT clauses not allowed 293 ((SELECT a FROM t LIMIT 1)) LIMIT 1 294 295 query IIII 296 SELECT * FROM (SELECT * FROM xyzw LIMIT 5) OFFSET 5 297 ---- 298 299 query II rowsort 300 SELECT z, y FROM xyzw@foo 301 ---- 302 3 2 303 6 5 304 305 query I 306 SELECT z FROM test.xyzw@foo WHERE y = 5 307 ---- 308 6 309 310 query I 311 SELECT xyzw.y FROM test.xyzw@foo WHERE z = 3 312 ---- 313 2 314 315 query error pgcode 42P01 relation "test.unknown" does not exist 316 SELECT z FROM test.unknown@foo WHERE y = 5 317 318 query error index "unknown" not found 319 SELECT z FROM test.xyzw@unknown WHERE y = 5 320 321 query I 322 SELECT w FROM test.xyzw@foo WHERE y = 5 323 ---- 324 7 325 326 statement ok 327 CREATE TABLE boolean_table ( 328 id INTEGER PRIMARY KEY NOT NULL, 329 value BOOLEAN 330 ) 331 332 statement ok 333 INSERT INTO boolean_table (id, value) VALUES (1, NULL) 334 335 query I 336 SELECT value FROM boolean_table 337 ---- 338 NULL 339 340 query I 341 SELECT CASE WHEN NULL THEN 1 ELSE 2 END 342 ---- 343 2 344 345 statement ok 346 INSERT INTO abc VALUES (42, NULL, NULL) 347 348 query III rowsort 349 SELECT 0 * b, b % 1, 0 % b from abc 350 ---- 351 0 0 0 352 0 0 0 353 NULL NULL NULL 354 355 # Doing an index lookup by MaxInt used to not work. 356 # https://github.com/cockroachdb/cockroach/issues/3587 357 statement ok 358 CREATE TABLE MaxIntTest (a INT PRIMARY KEY) 359 360 statement ok 361 INSERT INTO MaxIntTest VALUES (9223372036854775807) 362 363 query I 364 SELECT a FROM MaxIntTest WHERE a = 9223372036854775807 365 ---- 366 9223372036854775807 367 368 query error no value provided for placeholder 369 SELECT $1::int 370 371 # Regression tests for #22670. 372 query B 373 SELECT 1 IN (1, 2) 374 ---- 375 true 376 377 query B 378 SELECT NULL IN (1, 2) 379 ---- 380 NULL 381 382 query B 383 SELECT 1 IN (1, NULL) 384 ---- 385 true 386 387 query B 388 SELECT 1 IN (NULL, 2) 389 ---- 390 NULL 391 392 query B 393 SELECT (1, NULL) IN ((1, 1)) 394 ---- 395 NULL 396 397 query B 398 SELECT (2, NULL) IN ((1, 1)) 399 ---- 400 false 401 402 query B 403 SELECT (1, 1) IN ((1, NULL)) 404 ---- 405 NULL 406 407 query B 408 SELECT (1, 1) IN ((2, NULL)) 409 ---- 410 false 411 412 # Tests with a tuple coming from a subquery. 413 query B 414 SELECT NULL IN (SELECT * FROM (VALUES (1)) AS t(a)) 415 ---- 416 NULL 417 418 query B 419 SELECT (1, NULL) IN (SELECT * FROM (VALUES (1, 1)) AS t(a, b)) 420 ---- 421 NULL 422 423 query B 424 SELECT (2, NULL) IN (SELECT * FROM (VALUES (1, 1)) AS t(a, b)) 425 ---- 426 false 427 428 query B 429 SELECT (NULL, 1) IN (SELECT * FROM (VALUES (1, 1)) AS t(a, b)) 430 ---- 431 NULL 432 433 query B 434 SELECT (NULL, 2) IN (SELECT * FROM (VALUES (1, 1)) AS t(a, b)) 435 ---- 436 false 437 438 query B 439 SELECT (NULL, NULL) IN (SELECT * FROM (VALUES (1, 1)) AS t(a, b)) 440 ---- 441 NULL 442 443 query B 444 SELECT NULL NOT IN (SELECT * FROM (VALUES (1)) AS t(a)) 445 ---- 446 NULL 447 448 query B 449 SELECT (1, NULL) NOT IN (SELECT * FROM (VALUES (1, 1)) AS t(a, b)) 450 ---- 451 NULL 452 453 query B 454 SELECT (2, NULL) NOT IN (SELECT * FROM (VALUES (1, 1)) AS t(a, b)) 455 ---- 456 true 457 458 query B 459 SELECT (NULL, 1) NOT IN (SELECT * FROM (VALUES (1, 1)) AS t(a, b)) 460 ---- 461 NULL 462 463 query B 464 SELECT (NULL, 2) NOT IN (SELECT * FROM (VALUES (1, 1)) AS t(a, b)) 465 ---- 466 true 467 468 query B 469 SELECT (NULL, NULL) NOT IN (SELECT * FROM (VALUES (1, 1)) AS t(a, b)) 470 ---- 471 NULL 472 473 # Tests with an empty IN tuple. 474 query B 475 SELECT NULL IN (SELECT * FROM (VALUES (1)) AS t(a) WHERE a > 1) 476 ---- 477 false 478 479 query B 480 SELECT (1, NULL) IN (SELECT * FROM (VALUES (1, 1)) AS t(a, b) WHERE a > 1) 481 ---- 482 false 483 484 query B 485 SELECT (NULL, 1) IN (SELECT * FROM (VALUES (1, 1)) AS t(a, b) WHERE a > 1) 486 ---- 487 false 488 489 query B 490 SELECT (NULL, NULL) IN (SELECT * FROM (VALUES (1, 1)) AS t(a, b) WHERE a > 1) 491 ---- 492 false 493 494 query B 495 SELECT NULL NOT IN (SELECT * FROM (VALUES (1)) AS t(a) WHERE a > 1) 496 ---- 497 true 498 499 query B 500 SELECT (1, NULL) NOT IN (SELECT * FROM (VALUES (1, 1)) AS t(a, b) WHERE a > 1) 501 ---- 502 true 503 504 query B 505 SELECT (NULL, 1) NOT IN (SELECT * FROM (VALUES (1, 1)) AS t(a, b) WHERE a > 1) 506 ---- 507 true 508 509 query B 510 SELECT (NULL, NULL) NOT IN (SELECT * FROM (VALUES (1, 1)) AS t(a, b) WHERE a > 1) 511 ---- 512 true 513 514 statement ok 515 CREATE TABLE a (x INT PRIMARY KEY, y INT) 516 517 statement ok 518 INSERT INTO a VALUES (1, 10), (2, 20), (3, 30) 519 520 query II rowsort 521 SELECT * FROM a WHERE x > 1 522 ---- 523 2 20 524 3 30 525 526 query II rowsort 527 SELECT * FROM a WHERE y > 1 528 ---- 529 1 10 530 2 20 531 3 30 532 533 query II 534 SELECT * FROM a WHERE x > 1 AND x < 3 535 ---- 536 2 20 537 538 query II 539 SELECT * FROM a WHERE x > 1 AND y < 30 540 ---- 541 2 20 542 543 query I rowsort 544 SELECT x + 1 FROM a 545 ---- 546 2 547 3 548 4 549 550 query IIIII rowsort 551 SELECT x, x + 1, y, y + 1, x + y FROM a 552 ---- 553 1 2 10 11 11 554 2 3 20 21 22 555 3 4 30 31 33 556 557 query I rowsort 558 SELECT u + v FROM (SELECT x + 3, y + 10 FROM a) AS foo(u, v) 559 ---- 560 24 561 35 562 46 563 564 query IIII rowsort 565 SELECT x, x, y, x FROM a 566 ---- 567 1 1 10 1 568 2 2 20 2 569 3 3 30 3 570 571 query II rowsort 572 SELECT x + 1, x + y FROM a WHERE x + y > 20 573 ---- 574 3 22 575 4 33 576 577 # ------------------------------------------------------------------------------ 578 # Test with a hidden column. 579 # ------------------------------------------------------------------------------ 580 statement ok 581 CREATE TABLE b (x INT, y INT); 582 INSERT INTO b VALUES (1, 10), (2, 20), (3, 30) 583 584 query II rowsort 585 SELECT * FROM b 586 ---- 587 1 10 588 2 20 589 3 30 590 591 query I rowsort 592 SELECT x FROM b WHERE rowid > 0 593 ---- 594 1 595 2 596 3 597 598 # ------------------------------------------------------------------------------ 599 # String inequality filter. 600 # ------------------------------------------------------------------------------ 601 statement ok 602 CREATE TABLE c (n INT PRIMARY KEY, str STRING, INDEX str(str DESC)); 603 INSERT INTO c SELECT i, to_english(i) FROM generate_series(1, 10) AS g(i) 604 605 query IT rowsort 606 SELECT * FROM c WHERE str >= 'moo' 607 ---- 608 1 one 609 2 two 610 3 three 611 6 six 612 7 seven 613 9 nine 614 10 one-zero 615 616 # ------------------------------------------------------------------------------ 617 # "*" must expand to zero columns if there are zero columns to select. 618 # ------------------------------------------------------------------------------ 619 statement ok 620 CREATE TABLE nocols(x INT); ALTER TABLE nocols DROP COLUMN x 621 622 query I 623 SELECT 1, * FROM nocols 624 ---- 625 626 # ------------------------------------------------------------------------------ 627 # Wide tables can tickle edge cases. 628 # ------------------------------------------------------------------------------ 629 630 statement ok 631 CREATE TABLE wide (id INT4 NOT NULL, a INT4, b VARCHAR(255), c INT4, d VARCHAR(255), e VARCHAR(255), f INT4, g VARCHAR(255), h VARCHAR(255), i VARCHAR(255), j VARCHAR(255), k INT4, 632 l FLOAT4, m FLOAT8, n INT2, PRIMARY KEY (id)) 633 634 statement ok 635 INSERT INTO wide(id, n) VALUES(0, 10) 636 637 query IITITTITTTTIFFI 638 SELECT * FROM wide 639 ---- 640 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 10 641 642 # Regression test for #44203 (filter that is not folded inside the optimizer, 643 # but is statically evaluated to true when building the filterNode). 644 statement ok 645 CREATE TABLE t44203(c0 BOOL) 646 647 statement ok 648 INSERT INTO t44203(c0) VALUES (false) 649 650 statement ok 651 CREATE VIEW v44203(c0) AS SELECT c0 FROM t44203 WHERE t44203.c0 OFFSET NULL 652 653 query B 654 SELECT * FROM v44203 WHERE current_user() != '' 655 ---- 656 657 # Regression test for #44132 - generated column causes incorrect scan. 658 statement ok 659 CREATE TABLE t44132(c0 BOOL UNIQUE, c1 INT AS (NULL) STORED) 660 661 statement ok 662 INSERT INTO t44132 (c0) VALUES (true) 663 664 query BI 665 SELECT * FROM t44132 WHERE c0 666 ---- 667 true NULL