Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Resolve table names within CTEs in the context in which the CTE is declared, not the context in which it is used. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
a7323838bbd354a1c2f339e5e0f164f0 |
User & Date: | dan 2014-01-17 17:40:46.168 |
Context
2014-01-17
| ||
18:34 | Minor simplification of error message text for a couple of errors associated with WITH clause processing. (check-in: 2031004d96 user: drh tags: trunk) | |
17:40 | Resolve table names within CTEs in the context in which the CTE is declared, not the context in which it is used. (check-in: a7323838bb user: dan tags: trunk) | |
16:19 | Add tests that verify that keywords WITH, WITHOUT, and RECURSIVE can still be used as table and column names. (check-in: 9ca18a0191 user: drh tags: trunk) | |
Changes
Changes to src/select.c.
︙ | ︙ | |||
3503 3504 3505 3506 3507 3508 3509 3510 | #ifndef SQLITE_OMIT_CTE /* ** Argument pWith (which may be NULL) points to a linked list of nested ** WITH contexts, from inner to outermost. If the table identified by ** FROM clause element pItem is really a common-table-expression (CTE) ** then return a pointer to the CTE definition for that table. Otherwise ** return NULL. */ | > > > | > > > > > | 3503 3504 3505 3506 3507 3508 3509 3510 3511 3512 3513 3514 3515 3516 3517 3518 3519 3520 3521 3522 3523 3524 3525 3526 3527 3528 3529 3530 3531 3532 3533 | #ifndef SQLITE_OMIT_CTE /* ** Argument pWith (which may be NULL) points to a linked list of nested ** WITH contexts, from inner to outermost. If the table identified by ** FROM clause element pItem is really a common-table-expression (CTE) ** then return a pointer to the CTE definition for that table. Otherwise ** return NULL. ** ** If a non-NULL value is returned, set *ppContext to point to the With ** object that the returned CTE belongs to. */ static struct Cte *searchWith( With *pWith, /* Current outermost WITH clause */ struct SrcList_item *pItem, /* FROM clause element to resolve */ With **ppContext /* OUT: WITH clause return value belongs to */ ){ const char *zName; if( pItem->zDatabase==0 && (zName = pItem->zName)!=0 ){ With *p; for(p=pWith; p; p=p->pOuter){ int i; for(i=0; i<p->nCte; i++){ if( sqlite3StrICmp(zName, p->a[i].zName)==0 ){ *ppContext = p; return &p->a[i]; } } } } return 0; } |
︙ | ︙ | |||
3558 3559 3560 3561 3562 3563 3564 | ** occurs. If an error does occur, an error message is stored in the ** parser and some error code other than SQLITE_OK returned. */ static int withExpand( Walker *pWalker, struct SrcList_item *pFrom ){ | < | > | > > | | 3566 3567 3568 3569 3570 3571 3572 3573 3574 3575 3576 3577 3578 3579 3580 3581 3582 3583 3584 3585 3586 3587 3588 3589 3590 3591 3592 3593 3594 3595 3596 3597 3598 3599 3600 3601 3602 | ** occurs. If an error does occur, an error message is stored in the ** parser and some error code other than SQLITE_OK returned. */ static int withExpand( Walker *pWalker, struct SrcList_item *pFrom ){ Parse *pParse = pWalker->pParse; sqlite3 *db = pParse->db; struct Cte *pCte; /* Matched CTE (or NULL if no match) */ With *pWith; /* WITH clause that pCte belongs to */ assert( pFrom->pTab==0 ); pCte = searchWith(pParse->pWith, pFrom, &pWith); if( pCte ){ Table *pTab; ExprList *pEList; Select *pSel; Select *pLeft; /* Left-most SELECT statement */ int bMayRecursive; /* True if compound joined by UNION [ALL] */ With *pSavedWith; /* Initial value of pParse->pWith */ /* If pCte->zErr is non-NULL at this point, then this is an illegal ** recursive reference to CTE pCte. Leave an error in pParse and return ** early. If pCte->zErr is NULL, then this is not a recursive reference. ** In this case, proceed. */ if( pCte->zErr ){ sqlite3ErrorMsg(pParse, pCte->zErr, pCte->zName); return SQLITE_ERROR; } pFrom->pTab = pTab = sqlite3DbMallocZero(db, sizeof(Table)); if( pTab==0 ) return WRC_Abort; pTab->nRef = 1; pTab->zName = sqlite3DbStrDup(db, pCte->zName); pTab->iPKey = -1; |
︙ | ︙ | |||
3617 3618 3619 3620 3621 3622 3623 | } /* Only one recursive reference is permitted. */ if( pTab->nRef>2 ){ sqlite3ErrorMsg( pParse, "multiple references to recursive table: %s", pCte->zName ); | | > > > | < > > | 3627 3628 3629 3630 3631 3632 3633 3634 3635 3636 3637 3638 3639 3640 3641 3642 3643 3644 3645 3646 3647 3648 3649 3650 3651 3652 3653 3654 3655 3656 3657 3658 3659 3660 3661 3662 3663 3664 3665 3666 3667 3668 3669 3670 3671 3672 3673 | } /* Only one recursive reference is permitted. */ if( pTab->nRef>2 ){ sqlite3ErrorMsg( pParse, "multiple references to recursive table: %s", pCte->zName ); return SQLITE_ERROR; } assert( pTab->nRef==1 || ((pSel->selFlags&SF_Recursive) && pTab->nRef==2 )); pCte->zErr = "circular reference: %s"; pSavedWith = pParse->pWith; pParse->pWith = pWith; sqlite3WalkSelect(pWalker, bMayRecursive ? pSel->pPrior : pSel); for(pLeft=pSel; pLeft->pPrior; pLeft=pLeft->pPrior); pEList = pLeft->pEList; if( pCte->pCols ){ if( pEList->nExpr!=pCte->pCols->nExpr ){ sqlite3ErrorMsg(pParse, "table %s has %d values for %d columns", pCte->zName, pEList->nExpr, pCte->pCols->nExpr ); pParse->pWith = pSavedWith; return SQLITE_ERROR; } pEList = pCte->pCols; } selectColumnsFromExprList(pParse, pEList, &pTab->nCol, &pTab->aCol); if( bMayRecursive ){ if( pSel->selFlags & SF_Recursive ){ pCte->zErr = "multiple recursive references: %s"; }else{ pCte->zErr = "recursive reference in a subquery: %s"; } sqlite3WalkSelect(pWalker, pSel); } pCte->zErr = 0; pParse->pWith = pSavedWith; } return SQLITE_OK; } #endif #ifndef SQLITE_OMIT_CTE |
︙ | ︙ |
Changes to test/with2.test.
︙ | ︙ | |||
46 47 48 49 50 51 52 | 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} | > > > > > > | > > > > > > > | > > > > | 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 | 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} do_execsql_test 1.5 { WITH x1 AS (SELECT a FROM t1), x2 AS (SELECT i FROM t2), x3 AS (SELECT * FROM x1, x2 WHERE x1.a IN x2 AND x2.i IN x1) SELECT * FROM x3 } {2 2} do_execsql_test 1.6 { CREATE TABLE t3 AS SELECT 3 AS x; CREATE TABLE t4 AS SELECT 4 AS x; WITH x1 AS (SELECT * FROM t3), x2 AS ( WITH t3 AS (SELECT * FROM t4) SELECT * FROM x1 ) SELECT * FROM x2; } {3} finish_test |