gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/view.test (about) 1 # 2002 February 26 2 # 3 # The author disclaims copyright to this source code. In place of 4 # a legal notice, here is a blessing: 5 # 6 # May you do good and not evil. 7 # May you find forgiveness for yourself and forgive others. 8 # May you share freely, never taking more than you give. 9 # 10 #*********************************************************************** 11 # This file implements regression tests for SQLite library. The 12 # focus of this file is testing VIEW statements. 13 # 14 # $Id: view.test,v 1.39 2008/12/14 14:45:21 danielk1977 Exp $ 15 set testdir [file dirname $argv0] 16 source $testdir/tester.tcl 17 18 # Omit this entire file if the library is not configured with views enabled. 19 ifcapable !view { 20 finish_test 21 return 22 } 23 24 do_test view-1.0 { 25 execsql { 26 CREATE TABLE t1(a,b,c); 27 INSERT INTO t1 VALUES(1,2,3); 28 INSERT INTO t1 VALUES(4,5,6); 29 INSERT INTO t1 VALUES(7,8,9); 30 SELECT * FROM t1; 31 } 32 } {1 2 3 4 5 6 7 8 9} 33 34 do_test view-1.1 { 35 execsql { 36 BEGIN; 37 CREATE VIEW IF NOT EXISTS v1 AS SELECT a,b FROM t1; 38 SELECT * FROM v1 ORDER BY a; 39 } 40 } {1 2 4 5 7 8} 41 do_test view-1.1.100 { 42 db config enable_view off 43 catchsql { 44 SELECT * FROM v1 ORDER BY a; 45 } 46 } {1 {access to view "v1" prohibited}} 47 do_execsql_test view-1.1.101 { 48 CREATE TEMP VIEW v1temp AS SELECT a, b FROM t1; 49 SELECT * FROM v1temp ORDER BY a; 50 } {1 2 4 5 7 8} 51 do_test view-1.1.110 { 52 db config enable_view on 53 catchsql { 54 SELECT * FROM v1 ORDER BY a; 55 SELECT * FROM v1temp ORDER BY a; 56 } 57 } {0 {1 2 4 5 7 8 1 2 4 5 7 8}} 58 ifcapable vtab { 59 do_execsql_test view-1.1.120 { 60 SELECT name, type FROM pragma_table_list('v1'); 61 } {v1 view} 62 } 63 do_test view-1.2 { 64 catchsql { 65 ROLLBACK; 66 SELECT * FROM v1 ORDER BY a; 67 } 68 } {1 {no such table: v1}} 69 do_test view-1.3 { 70 execsql { 71 CREATE VIEW v1 AS SELECT a,b FROM t1; 72 SELECT * FROM v1 ORDER BY a; 73 } 74 } {1 2 4 5 7 8} 75 do_test view-1.3.1 { 76 db close 77 sqlite3 db test.db 78 execsql { 79 SELECT * FROM v1 ORDER BY a; 80 } 81 } {1 2 4 5 7 8} 82 do_test view-1.4 { 83 catchsql { 84 DROP VIEW IF EXISTS v1; 85 SELECT * FROM v1 ORDER BY a; 86 } 87 } {1 {no such table: v1}} 88 do_test view-1.5 { 89 execsql { 90 CREATE VIEW v1 AS SELECT a,b FROM t1; 91 SELECT * FROM v1 ORDER BY a; 92 } 93 } {1 2 4 5 7 8} 94 do_test view-1.6 { 95 catchsql { 96 DROP TABLE t1; 97 SELECT * FROM v1 ORDER BY a; 98 } 99 } {1 {no such table: main.t1}} 100 do_test view-1.7 { 101 execsql { 102 CREATE TABLE t1(x,a,b,c); 103 INSERT INTO t1 VALUES(1,2,3,4); 104 INSERT INTO t1 VALUES(4,5,6,7); 105 INSERT INTO t1 VALUES(7,8,9,10); 106 SELECT * FROM v1 ORDER BY a; 107 } 108 } {2 3 5 6 8 9} 109 do_test view-1.8 { 110 db close 111 sqlite3 db test.db 112 execsql { 113 SELECT * FROM v1 ORDER BY a; 114 } 115 } {2 3 5 6 8 9} 116 117 do_execsql_test view-1.10 { 118 CREATE TABLE t9(x INTEGER); 119 CREATE VIEW v9a AS SELECT x FROM t9; 120 CREATE VIEW v9b AS SELECT * FROM t9; 121 CREATE VIEW v9c(x) AS SELECT x FROM t9; 122 CREATE VIEW v9d(x) AS SELECT * FROM t9; 123 } {} 124 do_execsql_test view-1.11 { 125 PRAGMA table_info(v9a); 126 } {0 x INTEGER 0 {} 0} 127 do_execsql_test view-1.12 { 128 PRAGMA table_info(v9b); 129 } {0 x INTEGER 0 {} 0} 130 do_execsql_test view-1.13 { 131 PRAGMA table_info(v9c); 132 } {0 x INTEGER 0 {} 0} 133 do_execsql_test view-1.14 { 134 PRAGMA table_info(v9d); 135 } {0 x INTEGER 0 {} 0} 136 137 do_test view-2.1 { 138 execsql { 139 CREATE VIEW v2 AS SELECT * FROM t1 WHERE a>5 140 }; # No semicolon 141 execsql2 { 142 SELECT * FROM v2; 143 } 144 } {x 7 a 8 b 9 c 10} 145 do_test view-2.2 { 146 catchsql { 147 INSERT INTO v2 VALUES(1,2,3,4); 148 } 149 } {1 {cannot modify v2 because it is a view}} 150 do_test view-2.3 { 151 catchsql { 152 UPDATE v2 SET a=10 WHERE a=5; 153 } 154 } {1 {cannot modify v2 because it is a view}} 155 do_test view-2.4 { 156 catchsql { 157 DELETE FROM v2; 158 } 159 } {1 {cannot modify v2 because it is a view}} 160 do_test view-2.5 { 161 execsql { 162 INSERT INTO t1 VALUES(11,12,13,14); 163 SELECT * FROM v2 ORDER BY x; 164 } 165 } {7 8 9 10 11 12 13 14} 166 do_test view-2.6 { 167 execsql { 168 SELECT x FROM v2 WHERE a>10 169 } 170 } {11} 171 172 # Test that column name of views are generated correctly. 173 # 174 do_test view-3.1 { 175 execsql2 { 176 SELECT * FROM v1 LIMIT 1 177 } 178 } {a 2 b 3} 179 do_test view-3.2 { 180 execsql2 { 181 SELECT * FROM v2 LIMIT 1 182 } 183 } {x 7 a 8 b 9 c 10} 184 do_test view-3.3.1 { 185 execsql2 { 186 DROP VIEW v1; 187 CREATE VIEW v1 AS SELECT a AS 'xyz', b+c AS 'pqr', c-b FROM t1; 188 SELECT * FROM v1 LIMIT 1 189 } 190 } {xyz 2 pqr 7 c-b 1} 191 do_test view-3.3.2 { 192 execsql2 { 193 CREATE VIEW v1b AS SELECT t1.a, b+c, t1.c FROM t1; 194 SELECT * FROM v1b LIMIT 1 195 } 196 } {a 2 b+c 7 c 4} 197 do_test view-3.3.3 { 198 execsql2 { 199 CREATE VIEW v1c(x,y,z) AS SELECT a, b+c, c-b FROM t1; 200 SELECT * FROM v1c LIMIT 1; 201 } 202 } {x 2 y 7 z 1} 203 do_catchsql_test view-3.3.4 { 204 CREATE VIEW v1err(x,y DESC,z) AS SELECT a, b+c, c-b FROM t1; 205 } {1 {syntax error after column name "y"}} 206 do_catchsql_test view-3.3.5 { 207 DROP VIEW IF EXISTS v1err; 208 CREATE VIEW v1err(x,y) AS SELECT a, b+c, c-b FROM t1; 209 SELECT * FROM v1err; 210 } {1 {expected 2 columns for 'v1err' but got 3}} 211 do_catchsql_test view-3.3.6 { 212 DROP VIEW IF EXISTS v1err; 213 CREATE VIEW v1err(w,x,y,z) AS SELECT a, b+c, c-b FROM t1; 214 SELECT * FROM v1err; 215 } {1 {expected 4 columns for 'v1err' but got 3}} 216 217 ifcapable compound { 218 do_test view-3.4 { 219 execsql2 { 220 CREATE VIEW v3 AS SELECT a FROM t1 UNION SELECT b FROM t1 ORDER BY b; 221 SELECT * FROM v3 LIMIT 4; 222 } 223 } {a 2 a 3 a 5 a 6} 224 do_test view-3.5 { 225 execsql2 { 226 CREATE VIEW v4 AS 227 SELECT a, b FROM t1 228 UNION 229 SELECT b AS 'x', a AS 'y' FROM t1 230 ORDER BY x, y; 231 SELECT b FROM v4 ORDER BY b LIMIT 4; 232 } 233 } {b 2 b 3 b 5 b 6} 234 } ;# ifcapable compound 235 236 237 do_test view-4.1 { 238 catchsql { 239 DROP VIEW t1; 240 } 241 } {1 {use DROP TABLE to delete table t1}} 242 do_test view-4.2 { 243 execsql { 244 SELECT 1 FROM t1 LIMIT 1; 245 } 246 } 1 247 do_test view-4.3 { 248 catchsql { 249 DROP TABLE v1; 250 } 251 } {1 {use DROP VIEW to delete view v1}} 252 do_test view-4.4 { 253 execsql { 254 SELECT 1 FROM v1 LIMIT 1; 255 } 256 } {1} 257 do_test view-4.5 { 258 catchsql { 259 CREATE INDEX i1v1 ON v1(xyz); 260 } 261 } {1 {views may not be indexed}} 262 263 do_test view-5.1 { 264 execsql { 265 CREATE TABLE t2(y,a); 266 INSERT INTO t2 VALUES(22,2); 267 INSERT INTO t2 VALUES(33,3); 268 INSERT INTO t2 VALUES(44,4); 269 INSERT INTO t2 VALUES(55,5); 270 SELECT * FROM t2; 271 } 272 } {22 2 33 3 44 4 55 5} 273 do_test view-5.2 { 274 execsql { 275 CREATE VIEW v5 AS 276 SELECT t1.x AS v, t2.y AS w FROM t1 JOIN t2 USING(a); 277 SELECT * FROM v5; 278 } 279 } {1 22 4 55} 280 281 # Verify that the view v5 gets flattened. see sqliteFlattenSubquery(). 282 # This will only work if EXPLAIN is enabled. 283 # Ticket #272 284 # 285 ifcapable {explain} { 286 do_test view-5.3 { 287 lsearch [execsql { 288 EXPLAIN SELECT * FROM v5; 289 }] OpenEphemeral 290 } {-1} 291 do_test view-5.4 { 292 execsql { 293 SELECT * FROM v5 AS a, t2 AS b WHERE a.w=b.y; 294 } 295 } {1 22 22 2 4 55 55 5} 296 do_test view-5.5 { 297 lsearch [execsql { 298 EXPLAIN SELECT * FROM v5 AS a, t2 AS b WHERE a.w=b.y; 299 }] OpenEphemeral 300 } {-1} 301 do_test view-5.6 { 302 execsql { 303 SELECT * FROM t2 AS b, v5 AS a WHERE a.w=b.y; 304 } 305 } {22 2 1 22 55 5 4 55} 306 do_test view-5.7 { 307 lsearch [execsql { 308 EXPLAIN SELECT * FROM t2 AS b, v5 AS a WHERE a.w=b.y; 309 }] OpenEphemeral 310 } {-1} 311 do_test view-5.8 { 312 execsql { 313 SELECT * FROM t1 AS a, v5 AS b, t2 AS c WHERE a.x=b.v AND b.w=c.y; 314 } 315 } {1 2 3 4 1 22 22 2 4 5 6 7 4 55 55 5} 316 do_test view-5.9 { 317 lsearch [execsql { 318 EXPLAIN SELECT * FROM t1 AS a, v5 AS b, t2 AS c WHERE a.x=b.v AND b.w=c.y; 319 }] OpenEphemeral 320 } {-1} 321 } ;# endif explain 322 323 do_test view-6.1 { 324 execsql { 325 SELECT min(x), min(a), min(b), min(c), min(a+b+c) FROM v2; 326 } 327 } {7 8 9 10 27} 328 do_test view-6.2 { 329 execsql { 330 SELECT max(x), max(a), max(b), max(c), max(a+b+c) FROM v2; 331 } 332 } {11 12 13 14 39} 333 334 do_test view-7.1 { 335 execsql { 336 CREATE TABLE test1(id integer primary key, a); 337 CREATE TABLE test2(id integer, b); 338 INSERT INTO test1 VALUES(1,2); 339 INSERT INTO test2 VALUES(1,3); 340 CREATE VIEW test AS 341 SELECT test1.id, a, b 342 FROM test1 JOIN test2 ON test2.id=test1.id; 343 SELECT * FROM test; 344 } 345 } {1 2 3} 346 do_test view-7.2 { 347 db close 348 sqlite3 db test.db 349 execsql { 350 SELECT * FROM test; 351 } 352 } {1 2 3} 353 do_test view-7.3 { 354 execsql { 355 DROP VIEW test; 356 CREATE VIEW test AS 357 SELECT test1.id, a, b 358 FROM test1 JOIN test2 USING(id); 359 SELECT * FROM test; 360 } 361 } {1 2 3} 362 do_test view-7.4 { 363 db close 364 sqlite3 db test.db 365 execsql { 366 SELECT * FROM test; 367 } 368 } {1 2 3} 369 do_test view-7.5 { 370 execsql { 371 DROP VIEW test; 372 CREATE VIEW test AS 373 SELECT test1.id, a, b 374 FROM test1 NATURAL JOIN test2; 375 SELECT * FROM test; 376 } 377 } {1 2 3} 378 do_test view-7.6 { 379 db close 380 sqlite3 db test.db 381 execsql { 382 SELECT * FROM test; 383 } 384 } {1 2 3} 385 386 do_test view-8.1 { 387 execsql { 388 CREATE VIEW v6 AS SELECT pqr, xyz FROM v1; 389 SELECT * FROM v6 ORDER BY xyz; 390 } 391 } {7 2 13 5 19 8 27 12} 392 do_test view-8.2 { 393 db close 394 sqlite3 db test.db 395 execsql { 396 SELECT * FROM v6 ORDER BY xyz; 397 } 398 } {7 2 13 5 19 8 27 12} 399 do_test view-8.3 { 400 execsql { 401 CREATE VIEW v7(a) AS SELECT pqr+xyz FROM v6; 402 SELECT * FROM v7 ORDER BY a; 403 } 404 } {9 18 27 39} 405 406 ifcapable subquery { 407 do_test view-8.4 { 408 execsql { 409 CREATE VIEW v8 AS SELECT max(cnt) AS mx FROM 410 (SELECT a%2 AS eo, count(*) AS cnt FROM t1 GROUP BY eo); 411 SELECT * FROM v8; 412 } 413 } 3 414 do_test view-8.5 { 415 execsql { 416 SELECT mx+10, mx*2 FROM v8; 417 } 418 } {13 6} 419 do_test view-8.6 { 420 execsql { 421 SELECT mx+10, pqr FROM v6, v8 WHERE xyz=2; 422 } 423 } {13 7} 424 do_test view-8.7 { 425 execsql { 426 SELECT mx+10, pqr FROM v6, v8 WHERE xyz>2; 427 } 428 } {13 13 13 19 13 27} 429 } ;# ifcapable subquery 430 431 # Tests for a bug found by Michiel de Wit involving ORDER BY in a VIEW. 432 # 433 do_test view-9.1 { 434 execsql { 435 INSERT INTO t2 SELECT * FROM t2 WHERE a<5; 436 INSERT INTO t2 SELECT * FROM t2 WHERE a<4; 437 INSERT INTO t2 SELECT * FROM t2 WHERE a<3; 438 SELECT DISTINCT count(*) FROM t2 GROUP BY a ORDER BY 1; 439 } 440 } {1 2 4 8} 441 do_test view-9.2 { 442 execsql { 443 SELECT DISTINCT count(*) FROM t2 GROUP BY a ORDER BY 1 LIMIT 3; 444 } 445 } {1 2 4} 446 do_test view-9.3 { 447 execsql { 448 CREATE VIEW v9 AS 449 SELECT DISTINCT count(*) FROM t2 GROUP BY a ORDER BY 1 LIMIT 3; 450 SELECT * FROM v9; 451 } 452 } {1 2 4} 453 do_test view-9.4 { 454 execsql { 455 SELECT * FROM v9 ORDER BY 1 DESC; 456 } 457 } {4 2 1} 458 do_test view-9.5 { 459 execsql { 460 CREATE VIEW v10 AS 461 SELECT DISTINCT a, count(*) FROM t2 GROUP BY a ORDER BY 2 LIMIT 3; 462 SELECT * FROM v10; 463 } 464 } {5 1 4 2 3 4} 465 do_test view-9.6 { 466 execsql { 467 SELECT * FROM v10 ORDER BY 1; 468 } 469 } {3 4 4 2 5 1} 470 471 # Tables with columns having peculiar quoted names used in views 472 # Ticket #756. 473 # 474 do_test view-10.1 { 475 execsql { 476 CREATE TABLE t3("9" integer, [4] text); 477 INSERT INTO t3 VALUES(1,2); 478 CREATE VIEW v_t3_a AS SELECT a.[9] FROM t3 AS a; 479 CREATE VIEW v_t3_b AS SELECT "4" FROM t3; 480 SELECT * FROM v_t3_a; 481 } 482 } {1} 483 do_test view-10.2 { 484 execsql { 485 SELECT * FROM v_t3_b; 486 } 487 } {2} 488 489 do_test view-11.1 { 490 execsql { 491 CREATE TABLE t4(a COLLATE NOCASE); 492 INSERT INTO t4 VALUES('This'); 493 INSERT INTO t4 VALUES('this'); 494 INSERT INTO t4 VALUES('THIS'); 495 SELECT * FROM t4 WHERE a = 'THIS'; 496 } 497 } {This this THIS} 498 ifcapable subquery { 499 do_test view-11.2 { 500 execsql { 501 SELECT * FROM (SELECT * FROM t4) WHERE a = 'THIS'; 502 } 503 } {This this THIS} 504 } 505 do_test view-11.3 { 506 execsql { 507 CREATE VIEW v11 AS SELECT * FROM t4; 508 SELECT * FROM v11 WHERE a = 'THIS'; 509 } 510 } {This this THIS} 511 512 # Ticket #1270: Do not allow parameters in view definitions. 513 # 514 do_test view-12.1 { 515 catchsql { 516 CREATE VIEW v12 AS SELECT a FROM t1 WHERE b=? 517 } 518 } {1 {parameters are not allowed in views}} 519 do_test view-12.2 { 520 catchsql { 521 CREATE VIEW v12(x) AS SELECT a FROM t1 WHERE b=? 522 } 523 } {1 {parameters are not allowed in views}} 524 525 ifcapable attach { 526 do_test view-13.1 { 527 forcedelete test2.db 528 catchsql { 529 ATTACH 'test2.db' AS two; 530 CREATE TABLE two.t2(x,y); 531 CREATE VIEW v13 AS SELECT y FROM two.t2; 532 } 533 } {1 {view v13 cannot reference objects in database two}} 534 } 535 536 # Ticket #1658 537 # 538 do_test view-14.1 { 539 catchsql { 540 CREATE TEMP VIEW t1 AS SELECT a,b FROM t1; 541 SELECT * FROM temp.t1; 542 } 543 } {1 {view t1 is circularly defined}} 544 do_test view-14.2 { 545 catchsql { 546 DROP VIEW IF EXISTS temp.t1; 547 CREATE TEMP VIEW t1(a,b) AS SELECT a,b FROM t1; 548 SELECT * FROM temp.t1; 549 } 550 } {1 {view t1 is circularly defined}} 551 552 # Tickets #1688, #1709 553 # 554 do_test view-15.1 { 555 execsql2 { 556 CREATE VIEW v15 AS SELECT a AS x, b AS y FROM t1; 557 SELECT * FROM v15 LIMIT 1; 558 } 559 } {x 2 y 3} 560 do_test view-15.2 { 561 execsql2 { 562 SELECT x, y FROM v15 LIMIT 1 563 } 564 } {x 2 y 3} 565 566 do_test view-16.1 { 567 catchsql { 568 CREATE VIEW IF NOT EXISTS v1 AS SELECT * FROM t1; 569 } 570 } {0 {}} 571 do_test view-16.2 { 572 execsql { 573 SELECT sql FROM sqlite_master WHERE name='v1' 574 } 575 } {{CREATE VIEW v1 AS SELECT a AS 'xyz', b+c AS 'pqr', c-b FROM t1}} 576 do_test view-16.3 { 577 catchsql { 578 DROP VIEW IF EXISTS nosuchview 579 } 580 } {0 {}} 581 582 # correct error message when attempting to drop a view that does not 583 # exist. 584 # 585 do_test view-17.1 { 586 catchsql { 587 DROP VIEW nosuchview 588 } 589 } {1 {no such view: nosuchview}} 590 do_test view-17.2 { 591 catchsql { 592 DROP VIEW main.nosuchview 593 } 594 } {1 {no such view: main.nosuchview}} 595 596 do_test view-18.1 { 597 execsql { 598 DROP VIEW t1; 599 DROP TABLE t1; 600 CREATE TABLE t1(a, b, c); 601 INSERT INTO t1 VALUES(1, 2, 3); 602 INSERT INTO t1 VALUES(4, 5, 6); 603 604 CREATE VIEW vv1 AS SELECT * FROM t1; 605 CREATE VIEW vv2 AS SELECT * FROM vv1; 606 CREATE VIEW vv3 AS SELECT * FROM vv2; 607 CREATE VIEW vv4 AS SELECT * FROM vv3; 608 CREATE VIEW vv5 AS SELECT * FROM vv4; 609 610 SELECT * FROM vv5; 611 } 612 } {1 2 3 4 5 6} 613 614 # Ticket #3308 615 # Make sure "rowid" columns in a view are named correctly. 616 # 617 do_test view-19.1 { 618 execsql { 619 CREATE VIEW v3308a AS SELECT rowid, * FROM t1; 620 } 621 execsql2 { 622 SELECT * FROM v3308a 623 } 624 } {rowid 1 a 1 b 2 c 3 rowid 2 a 4 b 5 c 6} 625 do_test view-19.2 { 626 execsql { 627 CREATE VIEW v3308b AS SELECT t1.rowid, t1.a, t1.b+t1.c FROM t1; 628 } 629 execsql2 { 630 SELECT * FROM v3308b 631 } 632 } {rowid 1 a 1 t1.b+t1.c 5 rowid 2 a 4 t1.b+t1.c 11} 633 do_test view-19.3 { 634 execsql { 635 CREATE VIEW v3308c AS SELECT t1.oid, A, t1.b+t1.c AS x FROM t1; 636 } 637 execsql2 { 638 SELECT * FROM v3308c 639 } 640 } {rowid 1 a 1 x 5 rowid 2 a 4 x 11} 641 642 # Ticket #3539 had this crashing (see commit [5940]). 643 do_test view-20.1 { 644 execsql { 645 DROP TABLE IF EXISTS t1; 646 DROP VIEW IF EXISTS v1; 647 CREATE TABLE t1(c1); 648 CREATE VIEW v1 AS SELECT c1 FROM (SELECT t1.c1 FROM t1); 649 } 650 } {} 651 652 # Ticket [d58ccbb3f1b]: Prevent Table.nRef overflow. 653 db close 654 sqlite3 db :memory: 655 do_test view-21.1 { 656 catchsql { 657 CREATE TABLE t1(x); 658 INSERT INTO t1 VALUES(5); 659 CREATE VIEW v1 AS SELECT x*2 FROM t1; 660 CREATE VIEW v2 AS SELECT * FROM v1 UNION SELECT * FROM v1; 661 CREATE VIEW v4 AS SELECT * FROM v2 UNION SELECT * FROM v2; 662 CREATE VIEW v8 AS SELECT * FROM v4 UNION SELECT * FROM v4; 663 CREATE VIEW v16 AS SELECT * FROM v8 UNION SELECT * FROM v8; 664 CREATE VIEW v32 AS SELECT * FROM v16 UNION SELECT * FROM v16; 665 CREATE VIEW v64 AS SELECT * FROM v32 UNION SELECT * FROM v32; 666 CREATE VIEW v128 AS SELECT * FROM v64 UNION SELECT * FROM v64; 667 CREATE VIEW v256 AS SELECT * FROM v128 UNION SELECT * FROM v128; 668 CREATE VIEW v512 AS SELECT * FROM v256 UNION SELECT * FROM v256; 669 CREATE VIEW v1024 AS SELECT * FROM v512 UNION SELECT * FROM v512; 670 CREATE VIEW v2048 AS SELECT * FROM v1024 UNION SELECT * FROM v1024; 671 CREATE VIEW v4096 AS SELECT * FROM v2048 UNION SELECT * FROM v2048; 672 CREATE VIEW v8192 AS SELECT * FROM v4096 UNION SELECT * FROM v4096; 673 CREATE VIEW v16384 AS SELECT * FROM v8192 UNION SELECT * FROM v8192; 674 CREATE VIEW v32768 AS SELECT * FROM v16384 UNION SELECT * FROM v16384; 675 SELECT * FROM v32768 UNION SELECT * FROM v32768; 676 } 677 } {1 {too many references to "v1": max 65535}} 678 ifcapable progress { 679 do_test view-21.2 { 680 db progress 1000 {expr 1} 681 catchsql { 682 SELECT * FROM v32768; 683 } 684 } {1 interrupted} 685 } 686 687 db close 688 sqlite3 db :memory: 689 do_execsql_test view-22.1 { 690 CREATE VIEW x1 AS SELECT 123 AS '', 234 AS '', 345 AS ''; 691 SELECT * FROM x1; 692 } {123 234 345} 693 do_test view-22.2 { 694 unset -nocomplain x 695 db eval {SELECT * FROM x1} x break 696 lsort [array names x] 697 } {{} * :1 :2} 698 699 do_test view-25.1 { 700 db eval { 701 CREATE TABLE t25 (x); 702 INSERT INTO t25 (x) VALUES (1); 703 ANALYZE; 704 } 705 proc authLogDelete {code arg1 arg2 arg3 arg4 args} { 706 if {$code=="SQLITE_DELETE" && [string match sqlite_stat* $arg1]} { 707 # lappend ::log [list $code $arg1 $arg2 $arg3 $arg4 $args] 708 lappend ::log [list $code $arg1 $arg2 $arg3 $arg4] 709 } 710 return SQLITE_OK 711 } 712 set log "" 713 db authorizer ::authLogDelete 714 db eval {DROP VIEW x1;} 715 set log 716 } {} 717 718 set res [list {SQLITE_DELETE sqlite_stat1 {} main {}}] 719 ifcapable stat4 { lappend res {SQLITE_DELETE sqlite_stat4 {} main {}} } 720 do_test view-25.2 { 721 set log "" 722 db eval {DROP TABLE t25;} 723 set log 724 } $res 725 726 #------------------------------------------------------------------------- 727 do_execsql_test view-26.0 { 728 CREATE TABLE t16(a, b, c UNIQUE); 729 INSERT INTO t16 VALUES(1, 1, 1); 730 INSERT INTO t16 VALUES(2, 2, 2); 731 INSERT INTO t16 VALUES(3, 3, 3); 732 CREATE VIEW v16 AS SELECT max(a) AS mx, min(b) AS mn FROM t16 GROUP BY c; 733 734 SELECT * FROM v16 AS one, v16 AS two WHERE one.mx=1; 735 } { 736 1 1 1 1 737 1 1 2 2 738 1 1 3 3 739 } 740 do_execsql_test view-26.1 { 741 WITH v17(x,y) AS (SELECT max(a), min(b) FROM t16 GROUP BY c) 742 SELECT * FROM v17 AS one, v17 AS two WHERE one.x=1; 743 } { 744 1 1 1 1 745 1 1 2 2 746 1 1 3 3 747 } 748 749 #------------------------------------------------------------------------- 750 reset_db 751 do_execsql_test view-27.0 { 752 CREATE TABLE t0(c0 TEXT, c1); 753 INSERT INTO t0(c0, c1) VALUES (-1, 0); 754 CREATE VIEW v0(c0, c1) AS SELECT t0.c0, AVG(t0.c1) FROM t0; 755 } 756 757 do_execsql_test view-27.1 { 758 SELECT c0, typeof(c0), c1, typeof(c1) FROM v0; 759 } { 760 -1 text 761 0.0 real 762 } 763 764 do_execsql_test view-27.2 { SELECT c0<c1 FROM v0 } 1 765 do_execsql_test view-27.3 { SELECT c1<c0 FROM v0 } 0 766 do_execsql_test view-27.4 { 767 SELECT 1 FROM v0 WHERE c1<c0 768 } {} 769 do_execsql_test view-27.5 { 770 SELECT 1 FROM v0 WHERE c0<c1 771 } {1} 772 773 do_execsql_test view-27.6 { 774 SELECT c0<c1 FROM (SELECT t0.c0 AS c0, AVG(t0.c1) AS c1 FROM t0) 775 } 1 776 do_execsql_test view-27.7 { 777 SELECT c1<c0 FROM (SELECT t0.c0 AS c0, AVG(t0.c1) AS c1 FROM t0) 778 } 0 779 do_execsql_test view-27.8 { 780 SELECT 1 FROM (SELECT t0.c0 AS c0, AVG(t0.c1) AS c1 FROM t0) WHERE c1<c0 781 } {} 782 do_execsql_test view-27.9 { 783 SELECT 1 FROM (SELECT t0.c0 AS c0, AVG(t0.c1) AS c1 FROM t0) WHERE c0<c1 784 } {1} 785 786 #------------------------------------------------------------------------- 787 reset_db 788 do_execsql_test view-28.0 { 789 CREATE TABLE t0(c0 TEXT); 790 CREATE VIEW v0(c0) AS SELECT t0.c0 FROM t0; 791 INSERT INTO t0(c0) VALUES ('0'); 792 } 793 do_execsql_test view-28.1 { 794 SELECT 0 IN (c0) FROM t0; 795 } {0} 796 do_execsql_test view-28.2 { 797 SELECT 0 IN (c0) FROM (SELECT c0 FROM t0); 798 } {0} 799 800 #------------------------------------------------------------------------- 801 # 2020-10-26. https://sqlite.org/forum/forumpost/daa2c728cc 802 # 803 reset_db 804 do_catchsql_test view-29.0 { 805 CREATE TABLE t1(a,b,c); 806 CREATE VIEW IF NOT EXISTS IF AS SELECT null; 807 } {1 {malformed database schema (IF) - near "AS": syntax error}} 808 do_catchsql_test view-29.1 { 809 CREATE TABLE t2(c,d,e); 810 SELECT name FROM sqlite_schema ORDER BY name; 811 } {0 {t1 t2}} 812 813 814 finish_test