github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/views (about) 1 # LogicTest: !3node-tenant 2 # NOTE: Keep this table at the beginning of the file to ensure that its numeric 3 # reference is 53 (the numeric reference of the first table). If the 4 # numbering scheme in cockroach changes, this test will break. 5 statement ok 6 CREATE TABLE t (a INT PRIMARY KEY, b INT) 7 8 statement ok 9 INSERT INTO t VALUES (1, 99), (2, 98), (3, 97) 10 11 statement ok 12 CREATE VIEW v1 AS SELECT a, b FROM t 13 14 statement error pgcode 42P07 relation \"v1\" already exists 15 CREATE VIEW v1 AS SELECT a, b FROM t 16 17 statement error pgcode 42P07 relation \"t\" already exists 18 CREATE VIEW t AS SELECT a, b FROM t 19 20 # view statement ignored if other way around. 21 statement ok 22 CREATE VIEW IF NOT EXISTS v1 AS SELECT b, a FROM v1 23 24 statement ok 25 CREATE VIEW IF NOT EXISTS v2 (x, y) AS SELECT a, b FROM t 26 27 statement error pgcode 42601 CREATE VIEW specifies 1 column name, but data source has 2 columns 28 CREATE VIEW v3 (x) AS SELECT a, b FROM t 29 30 statement error pgcode 42601 CREATE VIEW specifies 3 column names, but data source has 2 columns 31 CREATE VIEW v4 (x, y, z) AS SELECT a, b FROM t 32 33 statement error pgcode 42P01 relation "dne" does not exist 34 CREATE VIEW v5 AS SELECT a, b FROM dne 35 36 statement ok 37 CREATE VIEW v6 (x, y) AS SELECT a, b FROM v1 38 39 statement ok 40 CREATE VIEW v7 (x, y) AS SELECT a, b FROM v1 ORDER BY a DESC LIMIT 2 41 42 query II colnames,rowsort 43 SELECT * FROM v1 44 ---- 45 a b 46 1 99 47 2 98 48 3 97 49 50 query II colnames,rowsort 51 SELECT * FROM v2 52 ---- 53 x y 54 1 99 55 2 98 56 3 97 57 58 query II colnames,rowsort 59 SELECT * FROM v6 60 ---- 61 x y 62 1 99 63 2 98 64 3 97 65 66 query II colnames 67 SELECT * FROM v7 68 ---- 69 x y 70 3 97 71 2 98 72 73 query II colnames 74 SELECT * FROM v7 ORDER BY x LIMIT 1 75 ---- 76 x y 77 2 98 78 79 query II 80 SELECT * FROM v2 ORDER BY x DESC LIMIT 1 81 ---- 82 3 97 83 84 query I rowsort 85 SELECT x FROM v2 86 ---- 87 1 88 2 89 3 90 91 query I rowsort 92 SELECT y FROM v2 93 ---- 94 99 95 98 96 97 97 98 query I 99 SELECT x FROM v7 100 ---- 101 3 102 2 103 104 query I 105 SELECT x FROM v7 ORDER BY x LIMIT 1 106 ---- 107 2 108 109 query I 110 SELECT y FROM v7 111 ---- 112 97 113 98 114 115 query I 116 SELECT y FROM v7 ORDER BY x LIMIT 1 117 ---- 118 98 119 120 query IIII rowsort 121 SELECT * FROM v1 AS v1 INNER JOIN v2 AS v2 ON v1.a = v2.x 122 ---- 123 1 99 1 99 124 2 98 2 98 125 3 97 3 97 126 127 statement ok 128 CREATE DATABASE test2 129 130 statement ok 131 SET DATABASE = test2 132 133 query II colnames,rowsort 134 SELECT * FROM test.v1 135 ---- 136 a b 137 1 99 138 2 98 139 3 97 140 141 query II colnames,rowsort 142 SELECT * FROM test.v2 143 ---- 144 x y 145 1 99 146 2 98 147 3 97 148 149 query II colnames,rowsort 150 SELECT * FROM test.v6 151 ---- 152 x y 153 1 99 154 2 98 155 3 97 156 157 query II colnames 158 SELECT * FROM test.v7 159 ---- 160 x y 161 3 97 162 2 98 163 164 query II colnames 165 SELECT * FROM test.v7 ORDER BY x LIMIT 1 166 ---- 167 x y 168 2 98 169 170 statement ok 171 CREATE VIEW v1 AS SELECT x, y FROM test.v2 172 173 statement ok 174 SET DATABASE = test 175 176 query II colnames,rowsort 177 SELECT * FROM test2.v1 178 ---- 179 x y 180 1 99 181 2 98 182 3 97 183 184 query TT 185 SHOW CREATE VIEW v1 186 ---- 187 v1 CREATE VIEW v1 (a, b) AS SELECT a, b FROM test.public.t 188 189 query TT 190 SHOW CREATE VIEW v2 191 ---- 192 v2 CREATE VIEW v2 (x, y) AS SELECT a, b FROM test.public.t 193 194 query TT 195 SHOW CREATE VIEW v6 196 ---- 197 v6 CREATE VIEW v6 (x, y) AS SELECT a, b FROM test.public.v1 198 199 query TT 200 SHOW CREATE VIEW v7 201 ---- 202 v7 CREATE VIEW v7 (x, y) AS SELECT a, b FROM test.public.v1 ORDER BY a DESC LIMIT 2 203 204 query TT 205 SHOW CREATE VIEW test2.v1 206 ---- 207 test2.public.v1 CREATE VIEW v1 (x, y) AS SELECT x, y FROM test.public.v2 208 209 statement ok 210 GRANT SELECT ON t TO testuser 211 212 user testuser 213 214 query II rowsort 215 SELECT * FROM t 216 ---- 217 1 99 218 2 98 219 3 97 220 221 query error user testuser does not have SELECT privilege on relation v1 222 SELECT * FROM v1 223 224 query error user testuser does not have SELECT privilege on relation v6 225 SELECT * FROM v6 226 227 query error user testuser has no privileges on relation v1 228 SHOW CREATE VIEW v1 229 230 user root 231 232 statement ok 233 REVOKE SELECT ON t FROM testuser 234 235 statement ok 236 GRANT SELECT ON v1 TO testuser 237 238 user testuser 239 240 query error user testuser does not have SELECT privilege on relation t 241 SELECT * FROM t 242 243 query II rowsort 244 SELECT * FROM v1 245 ---- 246 1 99 247 2 98 248 3 97 249 250 query error user testuser does not have SELECT privilege on relation v6 251 SELECT * FROM v6 252 253 query TT 254 SHOW CREATE VIEW v1 255 ---- 256 v1 CREATE VIEW v1 (a, b) AS SELECT a, b FROM test.public.t 257 258 user root 259 260 statement ok 261 REVOKE SELECT ON v1 FROM testuser 262 263 statement ok 264 GRANT SELECT ON v6 TO testuser 265 266 user testuser 267 268 query error user testuser does not have SELECT privilege on relation t 269 SELECT * FROM t 270 271 query error user testuser does not have SELECT privilege on relation v1 272 SELECT * FROM v1 273 274 query II rowsort 275 SELECT * FROM v6 276 ---- 277 1 99 278 2 98 279 3 97 280 281 user root 282 283 # Ensure that views work over tables identified by numeric reference. 284 statement ok 285 CREATE VIEW num_ref_view AS SELECT a, b FROM [53 AS t] 286 287 statement ok 288 GRANT SELECT ON num_ref_view TO testuser 289 290 user testuser 291 292 query II rowsort 293 SELECT * FROM num_ref_view 294 ---- 295 1 99 296 2 98 297 3 97 298 299 user root 300 301 statement ok 302 DROP VIEW num_ref_view 303 304 statement error pgcode 42809 "v1" is not a table 305 DROP TABLE v1 306 307 statement error pgcode 42809 "t" is not a view 308 DROP VIEW t 309 310 statement error cannot drop relation "v1" because view "v6" depends on it 311 DROP VIEW v1 312 313 statement error cannot drop relation "v2" because view "test2.public.v1" depends on it 314 DROP VIEW v2 315 316 statement ok 317 DROP VIEW test2.v1 318 319 statement ok 320 DROP VIEW v7 321 322 statement ok 323 DROP VIEW v6 324 325 statement ok 326 DROP VIEW v2 327 328 statement ok 329 DROP VIEW v1 330 331 statement error pgcode 42P01 relation "v1" does not exist 332 DROP VIEW v1 333 334 # Verify that we can depend on virtual tables. 335 statement ok 336 CREATE VIEW virt1 AS SELECT table_schema FROM information_schema.columns 337 338 statement ok 339 DROP VIEW virt1 340 341 # Verify that we can depend on virtual views. 342 statement ok 343 CREATE VIEW virt2 AS SELECT range_id, lease_holder FROM crdb_internal.ranges 344 345 statement ok 346 DROP VIEW virt2 347 348 # Verify correct rejection of star expressions 349 # TODO(a-robinson): Support star expressions as soon as we can (#10028) 350 351 statement error views do not currently support \* expressions 352 create view s1 AS SELECT * FROM t 353 354 statement error views do not currently support \* expressions 355 create view s1 AS SELECT t.* FROM t 356 357 statement error views do not currently support \* expressions 358 create view s1 AS SELECT a FROM t ORDER BY t.* 359 360 statement error views do not currently support \* expressions 361 create view s1 AS SELECT count(1) FROM t GROUP BY t.* 362 363 statement error views do not currently support \* expressions 364 create view s1 AS SELECT alias.* FROM t AS alias 365 366 statement error views do not currently support \* expressions 367 create view s1 AS TABLE t 368 369 statement error views do not currently support \* expressions 370 create view s1 AS SELECT a FROM (SELECT * FROM t) 371 372 statement error views do not currently support \* expressions 373 create view s1 AS SELECT a FROM t WHERE NOT a IN (SELECT a FROM (SELECT * FROM t)) 374 375 statement error views do not currently support \* expressions 376 create view s1 AS SELECT a FROM t GROUP BY a HAVING a IN (SELECT a FROM (SELECT * FROM t)) 377 378 statement error views do not currently support \* expressions 379 create view s1 AS SELECT t1.*, t2.a FROM t AS t1 JOIN t AS t2 ON t1.a = t2.a 380 381 statement error views do not currently support \* expressions 382 create view s1 AS SELECT t1.a, t2.* FROM t AS t1 JOIN t AS t2 ON t1.a = t2.a 383 384 statement error views do not currently support \* expressions 385 create view s1 AS SELECT t1.*, t2.* FROM t AS t1 JOIN t AS t2 ON t1.a = t2.a 386 387 statement error views do not currently support \* expressions 388 create view s1 AS SELECT * FROM t AS t1 JOIN t AS t2 ON t1.a = t2.a 389 390 statement error views do not currently support \* expressions 391 create view s1 AS SELECT t1.a, t2.a FROM (SELECT * FROM t) AS t1 JOIN t AS t2 ON t1.a = t2.a 392 393 statement error views do not currently support \* expressions 394 create view s1 AS SELECT t1.a, t2.a FROM t AS t1 JOIN (SELECT * FROM t) AS t2 ON t1.a = t2.a 395 396 statement error views do not currently support \* expressions 397 create view s1 AS SELECT t1.a, t2.a FROM t AS t1 JOIN t AS t2 ON t1.a IN (SELECT a FROM (SELECT * FROM t)) 398 399 statement ok 400 create view s1 AS SELECT count(*) FROM t 401 402 statement ok 403 create view s2 AS SELECT a FROM t WHERE a IN (SELECT count(*) FROM t) 404 405 statement ok 406 create view s3 AS SELECT a, count(*) FROM t GROUP BY a 407 408 statement ok 409 create view s4 AS SELECT a, count(*) FROM t GROUP BY a HAVING a > (SELECT count(*) FROM t) 410 411 statement ok 412 DROP VIEW s4 413 414 statement ok 415 DROP VIEW s3 416 417 statement ok 418 DROP VIEW s2 419 420 statement ok 421 DROP VIEW s1 422 423 statement ok 424 DROP TABLE t 425 426 # Check for memory leak (#10466) 427 statement ok 428 CREATE VIEW foo AS SELECT catalog_name, schema_name, sql_path FROM information_schema.schemata 429 430 statement error pq: relation "foo" already exists 431 CREATE VIEW foo AS SELECT catalog_name, schema_name, sql_path FROM information_schema.schemata 432 433 # Ensure views work with dates/timestamps (#12420) 434 statement ok 435 CREATE TABLE t (d DATE, t TIMESTAMP) 436 437 statement ok 438 CREATE VIEW dt AS SELECT d, t FROM t WHERE d > DATE '1988-11-12' AND t < TIMESTAMP '2017-01-01' 439 440 statement ok 441 SELECT * FROM dt 442 443 statement ok 444 CREATE VIEW dt2 AS SELECT d, t FROM t WHERE d > d + INTERVAL '10h' 445 446 statement ok 447 SELECT * FROM dt2 448 449 # Ensure that creating a view doesn't leak any session-level settings that 450 # could affect subsequent AS OF SYSTEM TIME queries (#13547). 451 statement ok 452 CREATE VIEW v AS SELECT d, t FROM t 453 454 statement error pq: AS OF SYSTEM TIME must be provided on a top-level statement 455 CREATE TABLE t2 AS SELECT d, t FROM t AS OF SYSTEM TIME '2017-02-13 21:30:00' 456 457 statement ok 458 DROP TABLE t CASCADE 459 460 statement ok 461 CREATE TABLE t (a INT[]) 462 463 statement ok 464 INSERT INTO t VALUES (array[1,2,3]) 465 466 statement ok 467 CREATE VIEW b AS SELECT a[1] FROM t 468 469 query I 470 SELECT * FROM b 471 ---- 472 1 473 474 statement ok 475 DROP TABLE t CASCADE 476 477 statement ok 478 CREATE VIEW arr(a) AS SELECT ARRAY[3] 479 480 query TI 481 SELECT *, a[1] FROM arr 482 ---- 483 {3} 3 484 485 # Regression for #15951 486 487 statement ok 488 CREATE TABLE t15951 (a int, b int) 489 490 statement ok 491 CREATE VIEW Caps15951 AS SELECT a, b FROM t15951 492 493 statement ok 494 INSERT INTO t15951 VALUES (1, 1), (1, 2), (1, 3), (2, 2), (2, 3), (3, 3) 495 496 query R 497 SELECT sum (Caps15951. a) FROM Caps15951 GROUP BY b ORDER BY b 498 ---- 499 1 500 3 501 6 502 503 query R 504 SELECT sum ("caps15951". a) FROM "caps15951" GROUP BY b ORDER BY b 505 ---- 506 1 507 3 508 6 509 510 statement ok 511 CREATE VIEW "QuotedCaps15951" AS SELECT a, b FROM t15951 512 513 query R 514 SELECT sum ("QuotedCaps15951". a) FROM "QuotedCaps15951" GROUP BY b ORDER BY b 515 ---- 516 1 517 3 518 6 519 520 # Regression tests for #23833 521 522 statement ok 523 CREATE VIEW w AS WITH a AS (SELECT 1 AS x) SELECT x FROM a 524 525 query T 526 SELECT create_statement FROM [SHOW CREATE w] 527 ---- 528 CREATE VIEW w (x) AS WITH a AS (SELECT 1 AS x) SELECT x FROM a 529 530 statement ok 531 CREATE VIEW w2 AS WITH t AS (SELECT x FROM w) SELECT x FROM t 532 533 query T 534 SELECT create_statement FROM [SHOW CREATE w2] 535 ---- 536 CREATE VIEW w2 (x) AS WITH t AS (SELECT x FROM test.public.w) SELECT x FROM t 537 538 statement ok 539 CREATE VIEW w3 AS (WITH t AS (SELECT x FROM w) SELECT x FROM t) 540 541 query T 542 SELECT create_statement FROM [SHOW CREATE w3] 543 ---- 544 CREATE VIEW w3 (x) AS (WITH t AS (SELECT x FROM test.public.w) SELECT x FROM t) 545 546 statement ok 547 CREATE TABLE ab (a INT PRIMARY KEY, b INT) 548 549 statement error INSERT cannot be used inside a view definition 550 CREATE VIEW crud_view AS SELECT a, b FROM [INSERT INTO ab VALUES (100, 100) RETURNING a, b] 551 552 statement ok 553 CREATE TABLE cd (c INT PRIMARY KEY, b INT) 554 555 statement ok 556 INSERT INTO ab VALUES (1, 1), (2, 2), (3, 3) 557 558 statement ok 559 INSERT INTO cd VALUES (2, 2), (3, 3), (4, 4) 560 561 # View containing a correlated subquery. 562 statement ok 563 CREATE VIEW v1 AS SELECT a, b, EXISTS(SELECT c FROM cd WHERE cd.c=ab.a) FROM ab; 564 565 query IIB rowsort 566 SELECT * FROM v1 567 ---- 568 1 1 false 569 2 2 true 570 3 3 true 571 572 # Regression test for #47704: the columns inside PARITION BY and ORDER BY were 573 # losing their qualification. 574 statement ok 575 CREATE TABLE a47704 (foo UUID); 576 CREATE TABLE b47704 (foo UUID) 577 578 statement ok 579 CREATE VIEW v47704 AS 580 SELECT first_value(a47704.foo) OVER (PARTITION BY a47704.foo ORDER BY a47704.foo) 581 FROM a47704 JOIN b47704 ON a47704.foo = b47704.foo 582 583 # Verify that the descriptor did not "lose" the column qualification inside 584 # PARITION BY and ORDER BY. 585 query T 586 SELECT create_statement FROM [ SHOW CREATE VIEW v47704 ] 587 ---- 588 CREATE VIEW v47704 (first_value) AS SELECT first_value(a47704.foo) OVER (PARTITION BY a47704.foo ORDER BY a47704.foo) FROM test.public.a47704 JOIN test.public.b47704 ON a47704.foo = b47704.foo 589 590 statement ok 591 SELECT * FROM v47704 592 593 subtest create_or_replace 594 595 user root 596 597 statement ok 598 DROP TABLE IF EXISTS t, t2; 599 CREATE TABLE t (x INT); 600 INSERT INTO t VALUES (1), (2); 601 CREATE TABLE t2 (x INT); 602 INSERT INTO t2 VALUES (3), (4); 603 604 # Test some error cases. 605 606 statement error pq: \"t\" is not a view 607 CREATE OR REPLACE VIEW t AS VALUES (1) 608 609 statement ok 610 CREATE OR REPLACE VIEW tview AS SELECT x AS x, x+1 AS x1, x+2 AS x2 FROM t 611 612 # Test cases where new columns don't line up. 613 614 statement error pq: cannot drop columns from view 615 CREATE OR REPLACE VIEW tview AS SELECT x AS x, x+1 AS x1 FROM t 616 617 statement error pq: cannot change name of view column \"x\" to \"xy\" 618 CREATE OR REPLACE VIEW tview AS SELECT x AS xy, x+1 AS x1, x+2 AS x2 FROM t 619 620 statement error pq: cannot change type of view column "x1" from int to string 621 CREATE OR REPLACE VIEW tview AS SELECT x AS x, (x+1)::STRING AS x1, x+2 AS x2 FROM t 622 623 statement ok 624 CREATE OR REPLACE VIEW tview AS SELECT x AS x, x+1 AS x1, x+2 AS x2, x+3 AS x3 FROM t 625 626 query IIII rowsort 627 SELECT * FROM tview 628 ---- 629 1 2 3 4 630 2 3 4 5 631 632 # Test cases where back references get updated. 633 statement ok 634 CREATE OR REPLACE VIEW tview AS SELECT x AS x, x+1 AS x1, x+2 AS x2, x+3 AS x3 FROM t2 635 636 query IIII rowsort 637 SELECT * FROM tview 638 ---- 639 3 4 5 6 640 4 5 6 7 641 642 # After remaking tview, it no longer depends on t. 643 statement ok 644 DROP TABLE t 645 646 # However, we now depend on t2. 647 statement error cannot drop relation "t2" because view "tview" depends on it 648 DROP TABLE t2 649 650 # Test that if we add a reference to something in t2 and use it when replacing 651 # the view that we now reference that object as well. 652 statement ok 653 CREATE INDEX i ON t2 (x); 654 CREATE INDEX i2 ON t2 (x); 655 CREATE OR REPLACE VIEW tview AS SELECT x AS x, x+1 AS x1, x+2 AS x2, x+3 AS x3 FROM t2@i 656 657 statement error pq: cannot drop index \"i\" because view \"tview\" depends on it 658 DROP INDEX t2@i 659 660 # However, if we change the view, we should be able to drop i. 661 statement ok 662 CREATE OR REPLACE VIEW tview AS SELECT x AS x, x+1 AS x1, x+2 AS x2, x+3 AS x3 FROM t2@i2; 663 DROP INDEX t2@i 664 665 # ... and not i2. 666 statement error pq: cannot drop index \"i2\" because view \"tview\" depends on it 667 DROP INDEX t2@i2 668 669 # Ensure that users can't replace views they don't have privilege to. 670 statement ok 671 GRANT CREATE ON DATABASE test TO testuser; 672 GRANT CREATE, SELECT ON TABLE tview, t2 TO testuser 673 674 user testuser 675 676 statement error pq: user testuser does not have DROP privilege on relation tview 677 CREATE OR REPLACE VIEW tview AS SELECT x AS x, x+1 AS x1, x+2 AS x2, x+3 AS x3 FROM t2 678 679 # Give privilege now. 680 user root 681 682 statement ok 683 GRANT DROP ON TABLE tview TO testuser 684 685 user testuser 686 687 statement ok 688 CREATE OR REPLACE VIEW tview AS SELECT x AS x, x+1 AS x1, x+2 AS x2, x+3 AS x3 FROM t2 689 690 user root