Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Fix some problems to do with WITH clauses and name resolution. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | common-table-expr |
Files: | files | file ages | folders |
SHA1: |
6a549187ed8b5ed50daefa676ff666ae |
User & Date: | dan 2014-01-17 14:59:27.898 |
Context
2014-01-17
| ||
15:15 | Add support for common table expressions (WITH clauses). (check-in: 0171e3bb4f user: dan tags: trunk) | |
14:59 | Fix some problems to do with WITH clauses and name resolution. (Closed-Leaf check-in: 6a549187ed user: dan tags: common-table-expr) | |
11:48 | Remove some code from resolve.c that was only required for recursive cte references in sub-queries. Also a stray "finish_test" command in pagerfault.test. (check-in: f68c6c4d36 user: dan tags: common-table-expr) | |
Changes
Changes to src/parse.y.
︙ | ︙ | |||
657 658 659 660 661 662 663 | {A.pOffset = X.pExpr; A.pLimit = Y.pExpr;} /////////////////////////// The DELETE statement ///////////////////////////// // %ifdef SQLITE_ENABLE_UPDATE_DELETE_LIMIT cmd ::= with(C) DELETE FROM fullname(X) indexed_opt(I) where_opt(W) orderby_opt(O) limit_opt(L). { | | | | | | 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 701 702 703 704 705 706 | {A.pOffset = X.pExpr; A.pLimit = Y.pExpr;} /////////////////////////// The DELETE statement ///////////////////////////// // %ifdef SQLITE_ENABLE_UPDATE_DELETE_LIMIT cmd ::= with(C) DELETE FROM fullname(X) indexed_opt(I) where_opt(W) orderby_opt(O) limit_opt(L). { sqlite3WithPush(pParse, C, 1); sqlite3SrcListIndexedBy(pParse, X, &I); W = sqlite3LimitWhere(pParse, X, W, O, L.pLimit, L.pOffset, "DELETE"); sqlite3DeleteFrom(pParse,X,W); } %endif %ifndef SQLITE_ENABLE_UPDATE_DELETE_LIMIT cmd ::= with(C) DELETE FROM fullname(X) indexed_opt(I) where_opt(W). { sqlite3WithPush(pParse, C, 1); sqlite3SrcListIndexedBy(pParse, X, &I); sqlite3DeleteFrom(pParse,X,W); } %endif %type where_opt {Expr*} %destructor where_opt {sqlite3ExprDelete(pParse->db, $$);} where_opt(A) ::= . {A = 0;} where_opt(A) ::= WHERE expr(X). {A = X.pExpr;} ////////////////////////// The UPDATE command //////////////////////////////// // %ifdef SQLITE_ENABLE_UPDATE_DELETE_LIMIT cmd ::= with(C) UPDATE orconf(R) fullname(X) indexed_opt(I) SET setlist(Y) where_opt(W) orderby_opt(O) limit_opt(L). { sqlite3WithPush(pParse, C, 1); sqlite3SrcListIndexedBy(pParse, X, &I); sqlite3ExprListCheckLength(pParse,Y,"set list"); W = sqlite3LimitWhere(pParse, X, W, O, L.pLimit, L.pOffset, "UPDATE"); sqlite3Update(pParse,X,Y,W,R); } %endif %ifndef SQLITE_ENABLE_UPDATE_DELETE_LIMIT cmd ::= with(C) UPDATE orconf(R) fullname(X) indexed_opt(I) SET setlist(Y) where_opt(W). { sqlite3WithPush(pParse, C, 1); sqlite3SrcListIndexedBy(pParse, X, &I); sqlite3ExprListCheckLength(pParse,Y,"set list"); sqlite3Update(pParse,X,Y,W,R); } %endif %type setlist {ExprList*} |
︙ | ︙ | |||
714 715 716 717 718 719 720 | A = sqlite3ExprListAppend(pParse, 0, Y.pExpr); sqlite3ExprListSetName(pParse, A, &X, 1); } ////////////////////////// The INSERT command ///////////////////////////////// // cmd ::= with(W) insert_cmd(R) INTO fullname(X) inscollist_opt(F) select(S). { | | | | 714 715 716 717 718 719 720 721 722 723 724 725 726 727 728 729 730 731 732 733 | A = sqlite3ExprListAppend(pParse, 0, Y.pExpr); sqlite3ExprListSetName(pParse, A, &X, 1); } ////////////////////////// The INSERT command ///////////////////////////////// // cmd ::= with(W) insert_cmd(R) INTO fullname(X) inscollist_opt(F) select(S). { sqlite3WithPush(pParse, W, 1); sqlite3Insert(pParse, X, S, F, R); } cmd ::= with(W) insert_cmd(R) INTO fullname(X) inscollist_opt(F) DEFAULT VALUES. { sqlite3WithPush(pParse, W, 1); sqlite3Insert(pParse, X, 0, F, R); } %type insert_cmd {u8} insert_cmd(A) ::= INSERT orconf(R). {A = R;} insert_cmd(A) ::= REPLACE. {A = OE_Replace;} |
︙ | ︙ |
Changes to src/select.c.
︙ | ︙ | |||
3523 3524 3525 3526 3527 3528 3529 | } return 0; } /* The code generator maintains a stack of active WITH clauses ** with the inner-most WITH clause being at the top of the stack. ** | > > | > > > | > > | 3523 3524 3525 3526 3527 3528 3529 3530 3531 3532 3533 3534 3535 3536 3537 3538 3539 3540 3541 3542 3543 3544 3545 3546 3547 3548 3549 | } return 0; } /* The code generator maintains a stack of active WITH clauses ** with the inner-most WITH clause being at the top of the stack. ** ** This routine pushes the WITH clause passed as the second argument ** onto the top of the stack. If argument bFree is true, then this ** WITH clause will never be popped from the stack. In this case it ** should be freed along with the Parse object. In other cases, when ** bFree==0, the With object will be freed along with the SELECT ** statement with which it is associated. */ void sqlite3WithPush(Parse *pParse, With *pWith, u8 bFree){ assert( bFree==0 || pParse->pWith==0 ); if( pWith ){ pWith->pOuter = pParse->pWith; pParse->pWith = pWith; pParse->bFreeWith = bFree; } } /* ** This function checks if argument pFrom refers to a CTE declared by ** a WITH clause on the stack currently maintained by the parser. And, ** if currently processing a CTE expression, if it is a recursive |
︙ | ︙ | |||
3645 3646 3647 3648 3649 3650 3651 3652 3653 3654 3655 3656 3657 3658 | pCte->zErr = 0; } return SQLITE_OK; } #endif /* ** This routine is a Walker callback for "expanding" a SELECT statement. ** "Expanding" means to do the following: ** ** (1) Make sure VDBE cursor numbers have been assigned to every ** element of the FROM clause. ** | > > > > > > > > > > > > > | 3652 3653 3654 3655 3656 3657 3658 3659 3660 3661 3662 3663 3664 3665 3666 3667 3668 3669 3670 3671 3672 3673 3674 3675 3676 3677 3678 | pCte->zErr = 0; } return SQLITE_OK; } #endif #ifndef SQLITE_OMIT_CTE static void selectPopWith(Walker *pWalker, Select *p){ Parse *pParse = pWalker->pParse; if( p->pWith ){ assert( pParse->pWith==p->pWith ); pParse->pWith = p->pWith->pOuter; } return WRC_Continue; } #else #define selectPopWith 0 #endif /* ** This routine is a Walker callback for "expanding" a SELECT statement. ** "Expanding" means to do the following: ** ** (1) Make sure VDBE cursor numbers have been assigned to every ** element of the FROM clause. ** |
︙ | ︙ | |||
3688 3689 3690 3691 3692 3693 3694 3695 3696 3697 3698 3699 3700 3701 3702 3703 3704 3705 3706 3707 3708 3709 3710 3711 3712 3713 3714 3715 3716 3717 3718 3719 | return WRC_Abort; } if( NEVER(p->pSrc==0) || (selFlags & SF_Expanded)!=0 ){ return WRC_Prune; } pTabList = p->pSrc; pEList = p->pEList; /* Make sure cursor numbers have been assigned to all entries in ** the FROM clause of the SELECT statement. */ sqlite3SrcListAssignCursors(pParse, pTabList); /* Look up every table named in the FROM clause of the select. If ** an entry of the FROM clause is a subquery instead of a table or view, ** then create a transient table structure to describe the subquery. */ for(i=0, pFrom=pTabList->a; i<pTabList->nSrc; i++, pFrom++){ Table *pTab; assert( pFrom->isRecursive==0 || pFrom->pTab ); if( pFrom->isRecursive ) continue; if( pFrom->pTab!=0 ){ /* This statement has already been prepared. There is no need ** to go further. */ assert( i==0 ); return WRC_Prune; } #ifndef SQLITE_OMIT_CTE if( withExpand(pWalker, pFrom) ) return WRC_Abort; if( pFrom->pTab ) {} else #endif if( pFrom->zName==0 ){ | > > > > | 3708 3709 3710 3711 3712 3713 3714 3715 3716 3717 3718 3719 3720 3721 3722 3723 3724 3725 3726 3727 3728 3729 3730 3731 3732 3733 3734 3735 3736 3737 3738 3739 3740 3741 3742 3743 | return WRC_Abort; } if( NEVER(p->pSrc==0) || (selFlags & SF_Expanded)!=0 ){ return WRC_Prune; } pTabList = p->pSrc; pEList = p->pEList; sqlite3WithPush(pParse, p->pWith, 0); /* Make sure cursor numbers have been assigned to all entries in ** the FROM clause of the SELECT statement. */ sqlite3SrcListAssignCursors(pParse, pTabList); /* Look up every table named in the FROM clause of the select. If ** an entry of the FROM clause is a subquery instead of a table or view, ** then create a transient table structure to describe the subquery. */ for(i=0, pFrom=pTabList->a; i<pTabList->nSrc; i++, pFrom++){ Table *pTab; assert( pFrom->isRecursive==0 || pFrom->pTab ); if( pFrom->isRecursive ) continue; if( pFrom->pTab!=0 ){ /* This statement has already been prepared. There is no need ** to go further. */ assert( i==0 ); #ifndef SQLITE_OMIT_CTE selectPopWith(pWalker, p); #endif return WRC_Prune; } #ifndef SQLITE_OMIT_CTE if( withExpand(pWalker, pFrom) ) return WRC_Abort; if( pFrom->pTab ) {} else #endif if( pFrom->zName==0 ){ |
︙ | ︙ | |||
3937 3938 3939 3940 3941 3942 3943 | if( p->pEList && p->pEList->nExpr>db->aLimit[SQLITE_LIMIT_COLUMN] ){ sqlite3ErrorMsg(pParse, "too many columns in result set"); } #endif return WRC_Continue; } | < < < < < < < < < < < < < < < < < < < < < < < < | 3961 3962 3963 3964 3965 3966 3967 3968 3969 3970 3971 3972 3973 3974 | if( p->pEList && p->pEList->nExpr>db->aLimit[SQLITE_LIMIT_COLUMN] ){ sqlite3ErrorMsg(pParse, "too many columns in result set"); } #endif return WRC_Continue; } /* ** No-op routine for the parse-tree walker. ** ** When this routine is the Walker.xExprCallback then expression trees ** are walked without any actions being taken at each node. Presumably, ** when this routine is used for Walker.xExprCallback then ** Walker.xSelectCallback is set to do something useful for every |
︙ | ︙ | |||
3997 3998 3999 4000 4001 4002 4003 | memset(&w, 0, sizeof(w)); w.xExprCallback = exprWalkNoop; w.pParse = pParse; if( pParse->hasCompound ){ w.xSelectCallback = convertCompoundSelectToSubquery; sqlite3WalkSelect(&w, pSelect); } | | > | | 3997 3998 3999 4000 4001 4002 4003 4004 4005 4006 4007 4008 4009 4010 4011 4012 4013 4014 4015 4016 4017 4018 4019 4020 4021 4022 4023 4024 4025 4026 4027 4028 4029 4030 4031 | memset(&w, 0, sizeof(w)); w.xExprCallback = exprWalkNoop; w.pParse = pParse; if( pParse->hasCompound ){ w.xSelectCallback = convertCompoundSelectToSubquery; sqlite3WalkSelect(&w, pSelect); } w.xSelectCallback = selectExpander; w.xSelectCallback2 = selectPopWith; sqlite3WalkSelect(&w, pSelect); } #ifndef SQLITE_OMIT_SUBQUERY /* ** This is a Walker.xSelectCallback callback for the sqlite3SelectTypeInfo() ** interface. ** ** For each FROM-clause subquery, add Column.zType and Column.zColl ** information to the Table structure that represents the result set ** of that subquery. ** ** The Table structure that represents the result set was constructed ** by selectExpander() but the type and collation information was omitted ** at that point because identifiers had not yet been resolved. This ** routine is called after identifier resolution. */ static void selectAddSubqueryTypeInfo(Walker *pWalker, Select *p){ Parse *pParse; int i; SrcList *pTabList; struct SrcList_item *pFrom; assert( p->selFlags & SF_Resolved ); if( (p->selFlags & SF_HasTypeInfo)==0 ){ |
︙ | ︙ | |||
4039 4040 4041 4042 4043 4044 4045 | if( pSel ){ while( pSel->pPrior ) pSel = pSel->pPrior; selectAddColumnTypeAndCollation(pParse, pTab, pSel); } } } } | < | < | 4040 4041 4042 4043 4044 4045 4046 4047 4048 4049 4050 4051 4052 4053 4054 4055 4056 4057 4058 4059 4060 4061 4062 4063 4064 4065 4066 4067 4068 4069 4070 4071 | if( pSel ){ while( pSel->pPrior ) pSel = pSel->pPrior; selectAddColumnTypeAndCollation(pParse, pTab, pSel); } } } } } #endif /* ** This routine adds datatype and collating sequence information to ** the Table structures of all FROM-clause subqueries in a ** SELECT statement. ** ** Use this routine after name resolution. */ static void sqlite3SelectAddTypeInfo(Parse *pParse, Select *pSelect){ #ifndef SQLITE_OMIT_SUBQUERY Walker w; memset(&w, 0, sizeof(w)); w.xSelectCallback2 = selectAddSubqueryTypeInfo; w.xExprCallback = exprWalkNoop; w.pParse = pParse; sqlite3WalkSelect(&w, pSelect); #endif } /* ** This routine sets up a SELECT statement for processing. The |
︙ | ︙ |
Changes to src/sqliteInt.h.
︙ | ︙ | |||
2367 2368 2369 2370 2371 2372 2373 2374 2375 2376 2377 2378 2379 2380 | #ifndef SQLITE_OMIT_VIRTUALTABLE Token sArg; /* Complete text of a module argument */ Table **apVtabLock; /* Pointer to virtual tables needing locking */ #endif Table *pZombieTab; /* List of Table objects to delete after code gen */ TriggerPrg *pTriggerPrg; /* Linked list of coded triggers */ With *pWith; /* Current WITH clause, or NULL */ }; /* ** Return true if currently inside an sqlite3_declare_vtab() call. */ #ifdef SQLITE_OMIT_VIRTUALTABLE #define IN_DECLARE_VTAB 0 | > | 2367 2368 2369 2370 2371 2372 2373 2374 2375 2376 2377 2378 2379 2380 2381 | #ifndef SQLITE_OMIT_VIRTUALTABLE Token sArg; /* Complete text of a module argument */ Table **apVtabLock; /* Pointer to virtual tables needing locking */ #endif Table *pZombieTab; /* List of Table objects to delete after code gen */ TriggerPrg *pTriggerPrg; /* Linked list of coded triggers */ With *pWith; /* Current WITH clause, or NULL */ u8 bFreeWith; /* True if pWith should be freed with parser */ }; /* ** Return true if currently inside an sqlite3_declare_vtab() call. */ #ifdef SQLITE_OMIT_VIRTUALTABLE #define IN_DECLARE_VTAB 0 |
︙ | ︙ | |||
2608 2609 2610 2611 2612 2613 2614 2615 2616 | /* ** Context pointer passed down through the tree-walk. */ struct Walker { int (*xExprCallback)(Walker*, Expr*); /* Callback for expressions */ int (*xSelectCallback)(Walker*,Select*); /* Callback for SELECTs */ Parse *pParse; /* Parser context. */ int walkerDepth; /* Number of subqueries */ | > < | 2609 2610 2611 2612 2613 2614 2615 2616 2617 2618 2619 2620 2621 2622 2623 2624 2625 | /* ** Context pointer passed down through the tree-walk. */ struct Walker { int (*xExprCallback)(Walker*, Expr*); /* Callback for expressions */ int (*xSelectCallback)(Walker*,Select*); /* Callback for SELECTs */ void (*xSelectCallback2)(Walker*,Select*);/* Second callback for SELECTs */ Parse *pParse; /* Parser context. */ int walkerDepth; /* Number of subqueries */ union { /* Extra data for callback */ NameContext *pNC; /* Naming context */ int i; /* Integer value */ SrcList *pSrcList; /* FROM clause */ struct SrcCount *pSrcCount; /* Counting column references */ } u; }; |
︙ | ︙ | |||
3350 3351 3352 3353 3354 3355 3356 | #ifndef SQLITE_OMIT_WAL int sqlite3Checkpoint(sqlite3*, int, int, int*, int*); int sqlite3WalDefaultHook(void*,sqlite3*,const char*,int); #endif #ifndef SQLITE_OMIT_CTE With *sqlite3WithAdd(Parse*,With*,Token*,ExprList*,Select*); void sqlite3WithDelete(sqlite3*,With*); | | | | 3351 3352 3353 3354 3355 3356 3357 3358 3359 3360 3361 3362 3363 3364 3365 3366 3367 | #ifndef SQLITE_OMIT_WAL int sqlite3Checkpoint(sqlite3*, int, int, int*, int*); int sqlite3WalDefaultHook(void*,sqlite3*,const char*,int); #endif #ifndef SQLITE_OMIT_CTE With *sqlite3WithAdd(Parse*,With*,Token*,ExprList*,Select*); void sqlite3WithDelete(sqlite3*,With*); void sqlite3WithPush(Parse*, With*, u8); #else #define sqlite3WithPush(x,y,z) #define sqlite3WithDelete(x,y) #endif /* Declarations for functions in fkey.c. All of these are replaced by ** no-op macros if OMIT_FOREIGN_KEY is defined. In this case no foreign ** key functionality is available. If OMIT_TRIGGER is defined but ** OMIT_FOREIGN_KEY is not, only some of the functions are no-oped. In |
︙ | ︙ |
Changes to src/tokenize.c.
︙ | ︙ | |||
490 491 492 493 494 495 496 | /* If the pParse->declareVtab flag is set, do not delete any table ** structure built up in pParse->pNewTable. The calling code (see vtab.c) ** will take responsibility for freeing the Table structure. */ sqlite3DeleteTable(db, pParse->pNewTable); } | < | | 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 | /* If the pParse->declareVtab flag is set, do not delete any table ** structure built up in pParse->pNewTable. The calling code (see vtab.c) ** will take responsibility for freeing the Table structure. */ sqlite3DeleteTable(db, pParse->pNewTable); } if( pParse->bFreeWith ) sqlite3WithDelete(db, pParse->pWith); sqlite3DeleteTrigger(db, pParse->pNewTrigger); for(i=pParse->nzVar-1; i>=0; i--) sqlite3DbFree(db, pParse->azVar[i]); sqlite3DbFree(db, pParse->azVar); while( pParse->pAinc ){ AutoincInfo *p = pParse->pAinc; pParse->pAinc = p->pNext; sqlite3DbFree(db, p); |
︙ | ︙ |
Changes to src/walker.c.
︙ | ︙ | |||
109 110 111 112 113 114 115 | } return WRC_Continue; } /* ** Call sqlite3WalkExpr() for every expression in Select statement p. ** Invoke sqlite3WalkSelect() for subqueries in the FROM clause and | | > > > > | < > | > | | | < < < < | 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 | } return WRC_Continue; } /* ** Call sqlite3WalkExpr() for every expression in Select statement p. ** Invoke sqlite3WalkSelect() for subqueries in the FROM clause and ** on the compound select chain, p->pPrior. ** ** If it is not NULL, the xSelectCallback() callback is invoked before ** the walk of the expressions and FROM clause. The xSelectCallback2() ** method, if it is not NULL, is invoked following the walk of the ** expressions and FROM clause. ** ** Return WRC_Continue under normal conditions. Return WRC_Abort if ** there is an abort request. ** ** If the Walker does not have an xSelectCallback() then this routine ** is a no-op returning WRC_Continue. */ int sqlite3WalkSelect(Walker *pWalker, Select *p){ int rc; if( p==0 || (pWalker->xSelectCallback==0 && pWalker->xSelectCallback2==0) ){ return WRC_Continue; } rc = WRC_Continue; pWalker->walkerDepth++; while( p ){ if( pWalker->xSelectCallback ){ rc = pWalker->xSelectCallback(pWalker, p); if( rc ) break; } if( sqlite3WalkSelectExpr(pWalker, p) || sqlite3WalkSelectFrom(pWalker, p) ){ pWalker->walkerDepth--; return WRC_Abort; } if( pWalker->xSelectCallback2 ){ pWalker->xSelectCallback2(pWalker, p); } p = p->pPrior; } pWalker->walkerDepth--; return rc & WRC_Abort; } |
Added test/with2.test.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 | # 2014 January 11 # # 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 the WITH clause. # set testdir [file dirname $argv0] source $testdir/tester.tcl set ::testprefix with2 do_execsql_test 1.0 { CREATE TABLE t1(a); INSERT INTO t1 VALUES(1); INSERT INTO t1 VALUES(2); } do_execsql_test 1.1 { WITH x1 AS (SELECT * FROM t1) SELECT sum(a) FROM x1; } {3} do_execsql_test 1.2 { WITH x1 AS (SELECT * FROM t1) SELECT (SELECT sum(a) FROM x1); } {3} do_execsql_test 1.3 { WITH x1 AS (SELECT * FROM t1) SELECT (SELECT sum(a) FROM x1); } {3} do_execsql_test 1.4 { CREATE TABLE t2(i); INSERT INTO t2 VALUES(2); INSERT INTO t2 VALUES(3); INSERT INTO t2 VALUES(5); WITH x1 AS (SELECT i FROM t2), i(a) AS ( SELECT min(i)-1 FROM x1 UNION SELECT a+1 FROM i WHERE a<10 ) SELECT a FROM i WHERE a NOT IN x1 } {1 4 6 7 8 9 10} finish_test |