Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Additional test cases added. The following bug fixed: A segfault was occurring if a VIEW consisted of a join with a USING clause. (CVS 651) |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
96515b813eb57e1f48c28d357d1f3863 |
User & Date: | drh 2002-06-28 12:18:47.000 |
Context
2002-06-29
| ||
02:20 | Add a few more tests and fix a few bugs that the tests uncovered. (CVS 652) (check-in: 91c0db66c8 user: drh tags: trunk) | |
2002-06-28
| ||
12:18 | Additional test cases added. The following bug fixed: A segfault was occurring if a VIEW consisted of a join with a USING clause. (CVS 651) (check-in: 96515b813e user: drh tags: trunk) | |
01:02 | Fix for ticket #84: If the WHERE clause is too complex, issue an error message and refuse to do the SELECT. The cutoff is a WHERE clause with 100 terms. (CVS 650) (check-in: c07e493b62 user: drh tags: trunk) | |
Changes
Changes to src/build.c.
︙ | ︙ | |||
21 22 23 24 25 26 27 | ** COPY ** VACUUM ** BEGIN TRANSACTION ** COMMIT ** ROLLBACK ** PRAGMA ** | | | 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | ** COPY ** VACUUM ** BEGIN TRANSACTION ** COMMIT ** ROLLBACK ** PRAGMA ** ** $Id: build.c,v 1.100 2002/06/28 12:18:47 drh Exp $ */ #include "sqliteInt.h" #include <ctype.h> /* ** This routine is called when a new SQL statement is beginning to ** be parsed. Check to see if the schema for the database needs |
︙ | ︙ | |||
894 895 896 897 898 899 900 901 902 903 904 905 906 907 | /* A negative nCol is a special marker meaning that we are currently ** trying to compute the column names. If we enter this routine with ** a negative nCol, it means two or more views form a loop, like this: ** ** CREATE VIEW one AS SELECT * FROM two; ** CREATE VIEW two AS SELECT * FROM one; */ if( pTable->nCol<0 ){ sqliteSetString(&pParse->zErrMsg, "view ", pTable->zName, " is circularly defined", 0); pParse->nErr++; return 1; } | > > > | 894 895 896 897 898 899 900 901 902 903 904 905 906 907 908 909 910 | /* A negative nCol is a special marker meaning that we are currently ** trying to compute the column names. If we enter this routine with ** a negative nCol, it means two or more views form a loop, like this: ** ** CREATE VIEW one AS SELECT * FROM two; ** CREATE VIEW two AS SELECT * FROM one; ** ** Actually, this error is caught previously and so the following test ** should always fail. But we will leave it in place just to be safe. */ if( pTable->nCol<0 ){ sqliteSetString(&pParse->zErrMsg, "view ", pTable->zName, " is circularly defined", 0); pParse->nErr++; return 1; } |
︙ | ︙ |
Changes to src/expr.c.
︙ | ︙ | |||
8 9 10 11 12 13 14 | ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* ** This file contains routines used for analyzing expressions and ** for generating VDBE code that evaluates expressions in SQLite. ** | | | 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* ** This file contains routines used for analyzing expressions and ** for generating VDBE code that evaluates expressions in SQLite. ** ** $Id: expr.c,v 1.75 2002/06/28 12:18:47 drh Exp $ */ #include "sqliteInt.h" #include <ctype.h> /* ** Construct a new expression node and return a pointer to it. Memory ** for this node is obtained from sqliteMalloc(). The calling function |
︙ | ︙ | |||
117 118 119 120 121 122 123 | ** expression tree and shift all tokens by "offset" amount. ** ** The work of figuring out the appropriate "offset" and making the ** presistent copy of the input buffer is done by the calling routine. */ void sqliteExprMoveStrings(Expr *p, int offset){ if( p==0 ) return; | > | | > | 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 | ** expression tree and shift all tokens by "offset" amount. ** ** The work of figuring out the appropriate "offset" and making the ** presistent copy of the input buffer is done by the calling routine. */ void sqliteExprMoveStrings(Expr *p, int offset){ if( p==0 ) return; if( !p->staticToken ){ if( p->token.z ) p->token.z += offset; if( p->span.z ) p->span.z += offset; } if( p->pLeft ) sqliteExprMoveStrings(p->pLeft, offset); if( p->pRight ) sqliteExprMoveStrings(p->pRight, offset); if( p->pList ) sqliteExprListMoveStrings(p->pList, offset); if( p->pSelect ) sqliteSelectMoveStrings(p->pSelect, offset); } void sqliteExprListMoveStrings(ExprList *pList, int offset){ int i; |
︙ | ︙ | |||
162 163 164 165 166 167 168 | ** Any tables that the SrcList might point to are not duplicated. */ Expr *sqliteExprDup(Expr *p){ Expr *pNew; if( p==0 ) return 0; pNew = sqliteMalloc( sizeof(*p) ); if( pNew==0 ) return 0; | | < < < < < < | 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 | ** Any tables that the SrcList might point to are not duplicated. */ Expr *sqliteExprDup(Expr *p){ Expr *pNew; if( p==0 ) return 0; pNew = sqliteMalloc( sizeof(*p) ); if( pNew==0 ) return 0; memcpy(pNew, p, sizeof(*pNew)); pNew->pLeft = sqliteExprDup(p->pLeft); pNew->pRight = sqliteExprDup(p->pRight); pNew->pList = sqliteExprListDup(p->pList); pNew->pSelect = sqliteSelectDup(p->pSelect); return pNew; } ExprList *sqliteExprListDup(ExprList *p){ ExprList *pNew; int i; if( p==0 ) return 0; |
︙ | ︙ |
Changes to src/select.c.
︙ | ︙ | |||
8 9 10 11 12 13 14 | ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* ** This file contains C code routines that are called by the parser ** to handle SELECT statements in SQLite. ** | | | 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* ** This file contains C code routines that are called by the parser ** to handle SELECT statements in SQLite. ** ** $Id: select.c,v 1.101 2002/06/28 12:18:47 drh Exp $ */ #include "sqliteInt.h" /* ** Allocate a new Select structure and return a pointer to that ** structure. */ |
︙ | ︙ | |||
153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 | Expr *pE1a, *pE1b, *pE1c; Expr *pE2a, *pE2b, *pE2c; Expr *pE; dummy.z = zCol; dummy.n = strlen(zCol); pE1a = sqliteExpr(TK_ID, 0, 0, &dummy); pE2a = sqliteExpr(TK_ID, 0, 0, &dummy); dummy.z = pTab1->zName; dummy.n = strlen(dummy.z); pE1b = sqliteExpr(TK_ID, 0, 0, &dummy); dummy.z = pTab2->zName; dummy.n = strlen(dummy.z); pE2b = sqliteExpr(TK_ID, 0, 0, &dummy); pE1c = sqliteExpr(TK_DOT, pE1b, pE1a, 0); pE2c = sqliteExpr(TK_DOT, pE2b, pE2a, 0); pE = sqliteExpr(TK_EQ, pE1c, pE2c, 0); pE->isJoinExpr = 1; if( *ppExpr ){ *ppExpr = sqliteExpr(TK_AND, *ppExpr, pE, 0); }else{ | > > > > | 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 | Expr *pE1a, *pE1b, *pE1c; Expr *pE2a, *pE2b, *pE2c; Expr *pE; dummy.z = zCol; dummy.n = strlen(zCol); pE1a = sqliteExpr(TK_ID, 0, 0, &dummy); pE1a->staticToken = 1; pE2a = sqliteExpr(TK_ID, 0, 0, &dummy); pE2a->staticToken = 1; dummy.z = pTab1->zName; dummy.n = strlen(dummy.z); pE1b = sqliteExpr(TK_ID, 0, 0, &dummy); pE1b->staticToken = 1; dummy.z = pTab2->zName; dummy.n = strlen(dummy.z); pE2b = sqliteExpr(TK_ID, 0, 0, &dummy); pE2b->staticToken = 1; pE1c = sqliteExpr(TK_DOT, pE1b, pE1a, 0); pE2c = sqliteExpr(TK_DOT, pE2b, pE2a, 0); pE = sqliteExpr(TK_EQ, pE1c, pE2c, 0); pE->isJoinExpr = 1; if( *ppExpr ){ *ppExpr = sqliteExpr(TK_AND, *ppExpr, pE, 0); }else{ |
︙ | ︙ |
Changes to src/sqliteInt.h.
1 2 3 4 5 6 7 8 9 10 11 12 13 | /* ** 2001 September 15 ** ** The author disclaims copyright to this source code. In place of ** a legal notice, here is a blessing: ** ** May you do good and not evil. ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* ** Internal interface definitions for SQLite. ** | | | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | /* ** 2001 September 15 ** ** The author disclaims copyright to this source code. In place of ** a legal notice, here is a blessing: ** ** May you do good and not evil. ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* ** Internal interface definitions for SQLite. ** ** @(#) $Id: sqliteInt.h,v 1.132 2002/06/28 12:18:47 drh Exp $ */ #include "sqlite.h" #include "hash.h" #include "vdbe.h" #include "parse.h" #include "btree.h" #include <stdio.h> |
︙ | ︙ | |||
428 429 430 431 432 433 434 435 436 437 438 439 440 441 | ** in an expression the opcode is TK_SELECT and Expr.pSelect is the only ** operand. */ struct Expr { u8 op; /* Operation performed by this node */ u8 dataType; /* Either SQLITE_SO_TEXT or SQLITE_SO_NUM */ u8 isJoinExpr; /* Origina is the ON or USING phrase of a join */ Expr *pLeft, *pRight; /* Left and right subnodes */ ExprList *pList; /* A list of expressions used as function arguments ** or in "<expr> IN (<expr-list)" */ Token token; /* An operand token */ Token span; /* Complete text of the expression */ int iTable, iColumn; /* When op==TK_COLUMN, then this expr node means the ** iColumn-th field of the iTable-th table. */ | > | 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 | ** in an expression the opcode is TK_SELECT and Expr.pSelect is the only ** operand. */ struct Expr { u8 op; /* Operation performed by this node */ u8 dataType; /* Either SQLITE_SO_TEXT or SQLITE_SO_NUM */ u8 isJoinExpr; /* Origina is the ON or USING phrase of a join */ u8 staticToken; /* Expr.token.z points to static memory */ Expr *pLeft, *pRight; /* Left and right subnodes */ ExprList *pList; /* A list of expressions used as function arguments ** or in "<expr> IN (<expr-list)" */ Token token; /* An operand token */ Token span; /* Complete text of the expression */ int iTable, iColumn; /* When op==TK_COLUMN, then this expr node means the ** iColumn-th field of the iTable-th table. */ |
︙ | ︙ |
Changes to src/where.c.
︙ | ︙ | |||
9 10 11 12 13 14 15 | ** May you share freely, never taking more than you give. ** ************************************************************************* ** This module contains C code that generates VDBE code used to process ** the WHERE clause of SQL statements. Also found here are subroutines ** to generate VDBE code to evaluate expressions. ** | | | 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | ** May you share freely, never taking more than you give. ** ************************************************************************* ** This module contains C code that generates VDBE code used to process ** the WHERE clause of SQL statements. Also found here are subroutines ** to generate VDBE code to evaluate expressions. ** ** $Id: where.c,v 1.58 2002/06/28 12:18:47 drh Exp $ */ #include "sqliteInt.h" /* ** The query generator uses an array of instances of this structure to ** help it analyze the subexpressions of the WHERE clause. Each WHERE ** clause subexpression is separated from the others by an AND operator. |
︙ | ︙ | |||
1029 1030 1031 1032 1033 1034 1035 1036 1037 1038 1039 1040 1041 1042 | pLevel->top = sqliteVdbeCurrentAddr(v); sqliteVdbeAddOp(v, OP_Integer, 1, 0); sqliteVdbeAddOp(v, OP_MemStore, pLevel->iLeftJoin, 1); for(j=0; j<nExpr; j++){ if( aExpr[j].p==0 ) continue; if( (aExpr[j].prereqAll & loopMask)!=aExpr[j].prereqAll ) continue; if( haveKey ){ haveKey = 0; sqliteVdbeAddOp(v, OP_MoveTo, base+idx, 0); } sqliteExprIfFalse(pParse, aExpr[j].p, cont, 1); aExpr[j].p = 0; } } | > > > > | 1029 1030 1031 1032 1033 1034 1035 1036 1037 1038 1039 1040 1041 1042 1043 1044 1045 1046 | pLevel->top = sqliteVdbeCurrentAddr(v); sqliteVdbeAddOp(v, OP_Integer, 1, 0); sqliteVdbeAddOp(v, OP_MemStore, pLevel->iLeftJoin, 1); for(j=0; j<nExpr; j++){ if( aExpr[j].p==0 ) continue; if( (aExpr[j].prereqAll & loopMask)!=aExpr[j].prereqAll ) continue; if( haveKey ){ /* Cannot happen. "haveKey" can only be true if pushKey is true ** an pushKey can only be true for DELETE and UPDATE and there are ** no outer joins with DELETE and UPDATE. */ haveKey = 0; sqliteVdbeAddOp(v, OP_MoveTo, base+idx, 0); } sqliteExprIfFalse(pParse, aExpr[j].p, cont, 1); aExpr[j].p = 0; } } |
︙ | ︙ |
Changes to test/conflict.test.
︙ | ︙ | |||
9 10 11 12 13 14 15 | # #*********************************************************************** # This file implements regression tests for SQLite library. # # This file implements tests for the conflict resolution extension # to SQLite. # | | | 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | # #*********************************************************************** # This file implements regression tests for SQLite library. # # This file implements tests for the conflict resolution extension # to SQLite. # # $Id: conflict.test,v 1.13 2002/06/28 12:18:48 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Create tables for the first group of tests. # do_test conflict-1.0 { |
︙ | ︙ | |||
487 488 489 490 491 492 493 | INSERT OR IGNORE INTO t1 SELECT a+3,b+3 FROM t1; } } {3} do_test conflict-8.6.1 { db changes } {3} | | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 663 | INSERT OR IGNORE INTO t1 SELECT a+3,b+3 FROM t1; } } {3} do_test conflict-8.6.1 { db changes } {3} integrity_check conflict-8.99 do_test conflict-9.1 { execsql { PRAGMA count_changes=0; CREATE TABLE t2( a INTEGER UNIQUE ON CONFLICT IGNORE, b INTEGER UNIQUE ON CONFLICT FAIL, c INTEGER UNIQUE ON CONFLICT REPLACE, d INTEGER UNIQUE ON CONFLICT ABORT, e INTEGER UNIQUE ON CONFLICT ROLLBACK ); CREATE TABLE t3(x); INSERT INTO t3 VALUES(1); SELECT * FROM t3; } } {1} do_test conflict-9.2 { catchsql { INSERT INTO t2 VALUES(1,1,1,1,1); INSERT INTO t2 VALUES(2,2,2,2,2); SELECT * FROM t2; } } {0 {1 1 1 1 1 2 2 2 2 2}} do_test conflict-9.3 { catchsql { INSERT INTO t2 VALUES(1,3,3,3,3); SELECT * FROM t2; } } {0 {1 1 1 1 1 2 2 2 2 2}} do_test conflict-9.4 { catchsql { UPDATE t2 SET a=a+1 WHERE a=1; SELECT * FROM t2; } } {0 {1 1 1 1 1 2 2 2 2 2}} do_test conflict-9.5 { catchsql { INSERT INTO t2 VALUES(3,1,3,3,3); SELECT * FROM t2; } } {1 {constraint failed}} do_test conflict-9.6 { catchsql { UPDATE t2 SET b=b+1 WHERE b=1; SELECT * FROM t2; } } {1 {constraint failed}} do_test conflict-9.7 { catchsql { BEGIN; UPDATE t3 SET x=x+1; INSERT INTO t2 VALUES(3,1,3,3,3); SELECT * FROM t2; } } {1 {constraint failed}} do_test conflict-9.8 { execsql {COMMIT} execsql {SELECT * FROM t3} } {2} do_test conflict-9.6 { catchsql { BEGIN; UPDATE t3 SET x=x+1; UPDATE t2 SET b=b+1 WHERE b=1; SELECT * FROM t2; } } {1 {constraint failed}} do_test conflict-9.10 { execsql {COMMIT} execsql {SELECT * FROM t3} } {3} do_test conflict-9.11 { catchsql { INSERT INTO t2 VALUES(3,3,3,1,3); SELECT * FROM t2; } } {1 {constraint failed}} do_test conflict-9.12 { catchsql { UPDATE t2 SET d=d+1 WHERE d=1; SELECT * FROM t2; } } {1 {constraint failed}} do_test conflict-9.13 { catchsql { BEGIN; UPDATE t3 SET x=x+1; INSERT INTO t2 VALUES(3,3,3,1,3); SELECT * FROM t2; } } {1 {constraint failed}} do_test conflict-9.14 { execsql {COMMIT} execsql {SELECT * FROM t3} } {4} do_test conflict-9.15 { catchsql { BEGIN; UPDATE t3 SET x=x+1; UPDATE t2 SET d=d+1 WHERE d=1; SELECT * FROM t2; } } {1 {constraint failed}} do_test conflict-9.16 { execsql {COMMIT} execsql {SELECT * FROM t3} } {5} do_test conflict-9.17 { catchsql { INSERT INTO t2 VALUES(3,3,3,3,1); SELECT * FROM t2; } } {1 {constraint failed}} do_test conflict-9.18 { catchsql { UPDATE t2 SET e=e+1 WHERE e=1; SELECT * FROM t2; } } {1 {constraint failed}} do_test conflict-9.19 { catchsql { BEGIN; UPDATE t3 SET x=x+1; INSERT INTO t2 VALUES(3,3,3,3,1); SELECT * FROM t2; } } {1 {constraint failed}} do_test conflict-9.20 { execsql {COMMIT} execsql {SELECT * FROM t3} } {5} do_test conflict-9.21 { catchsql { BEGIN; UPDATE t3 SET x=x+1; UPDATE t2 SET e=e+1 WHERE e=1; SELECT * FROM t2; } } {1 {constraint failed}} do_test conflict-9.22 { execsql {COMMIT} execsql {SELECT * FROM t3} } {5} do_test conflict-9.23 { catchsql { INSERT INTO t2 VALUES(3,3,1,3,3); SELECT * FROM t2; } } {0 {2 2 2 2 2 3 3 1 3 3}} do_test conflict-9.24 { catchsql { UPDATE t2 SET c=c-1 WHERE c=2; SELECT * FROM t2; } } {0 {2 2 1 2 2}} do_test conflict-9.25 { catchsql { BEGIN; UPDATE t3 SET x=x+1; INSERT INTO t2 VALUES(3,3,1,3,3); SELECT * FROM t2; } } {0 {3 3 1 3 3}} do_test conflict-9.26 { execsql {COMMIT} execsql {SELECT * FROM t3} } {6} finish_test |
Changes to test/join.test.
︙ | ︙ | |||
8 9 10 11 12 13 14 | # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. # # This file implements tests for joins, including outer joins. # | | | 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. # # This file implements tests for joins, including outer joins. # # $Id: join.test,v 1.3 2002/06/28 12:18:48 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl do_test join-1.1 { execsql { CREATE TABLE t1(a,b,c); |
︙ | ︙ | |||
160 161 162 163 164 165 166 167 168 169 170 171 172 173 | } } {1 2 3 {} 2 3 4 1 3 4 5 2} do_test join-2.3 { catchsql { SELECT * FROM t1 NATURAL RIGHT OUTER JOIN t2; } } {1 {RIGHT and FULL OUTER JOINs are not currently supported}} do_test join-3.1 { catchsql { SELECT * FROM t1 NATURAL JOIN t2 ON t1.a=t2.b; } } {1 {a NATURAL join may not have an ON or USING clause}} do_test join-3.2 { | > > > > > > > > > > > > > > > | 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 | } } {1 2 3 {} 2 3 4 1 3 4 5 2} do_test join-2.3 { catchsql { SELECT * FROM t1 NATURAL RIGHT OUTER JOIN t2; } } {1 {RIGHT and FULL OUTER JOINs are not currently supported}} do_test join-2.4 { execsql { SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.d } } {1 2 3 {} {} {} 2 3 4 {} {} {} 3 4 5 1 2 3} do_test join-2.5 { execsql { SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.d WHERE t1.a>1 } } {2 3 4 {} {} {} 3 4 5 1 2 3} do_test join-2.6 { execsql { SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.d WHERE t2.b IS NULL OR t2.b>1 } } {1 2 3 {} {} {} 2 3 4 {} {} {}} do_test join-3.1 { catchsql { SELECT * FROM t1 NATURAL JOIN t2 ON t1.a=t2.b; } } {1 {a NATURAL join may not have an ON or USING clause}} do_test join-3.2 { |
︙ | ︙ |
Changes to test/view.test.
1 2 3 4 5 6 7 8 9 10 11 12 13 | # 2002 February 26 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing VIEW statements. # | | | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | # 2002 February 26 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing VIEW statements. # # $Id: view.test,v 1.6 2002/06/28 12:18:48 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl do_test view-1.0 { execsql { CREATE TABLE t1(a,b,c); INSERT INTO t1 VALUES(1,2,3); |
︙ | ︙ | |||
153 154 155 156 157 158 159 160 161 | UNION SELECT b AS 'x', a AS 'y' FROM t1 ORDER BY x, y; SELECT y FROM v4 ORDER BY y LIMIT 4; } } {y 2 y 3 y 5 y 6} finish_test | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 | UNION SELECT b AS 'x', a AS 'y' FROM t1 ORDER BY x, y; SELECT y FROM v4 ORDER BY y LIMIT 4; } } {y 2 y 3 y 5 y 6} do_test view-4.1 { catchsql { DROP VIEW t1; } } {1 {use DROP TABLE to delete table t1}} do_test view-4.2 { execsql { SELECT 1 FROM t1 LIMIT 1; } } 1 do_test view-4.3 { catchsql { DROP TABLE v1; } } {1 {use DROP VIEW to delete view v1}} do_test view-4.4 { execsql { SELECT 1 FROM v1 LIMIT 1; } } {1} do_test view-4.5 { catchsql { CREATE INDEX i1v1 ON v1(xyz); } } {1 {views may not be indexed}} do_test view-5.1 { execsql { CREATE TABLE t2(y,a); INSERT INTO t2 VALUES(22,2); INSERT INTO t2 VALUES(33,3); INSERT INTO t2 VALUES(44,4); INSERT INTO t2 VALUES(55,5); SELECT * FROM t2; } } {22 2 33 3 44 4 55 5} do_test view-5.2 { execsql { CREATE VIEW v5 AS SELECT t1.x, t2.y FROM t1 JOIN t2 USING(a); SELECT * FROM v5; } } {1 22 4 55} finish_test |