Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Return an error if a CTE specifies a different number of columns than its SELECT statement returns. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | common-table-expr |
| Files: | files | file ages | folders |
| SHA1: | 9a514b50e4b01f109fbdb0aabcbfe1dd |
| User & Date: | dan 2014-01-15 15:27:51 |
Context
|
2014-01-15
| ||
| 18:12 | When resolving names, consider a reference to a recursive CTE column as equivalent to a reference to the outermost name-context. This ensures that correlated sub-queries are correctly identified as such. check-in: 61be2da0 user: dan tags: common-table-expr | |
| 15:27 | Return an error if a CTE specifies a different number of columns than its SELECT statement returns. check-in: 9a514b50 user: dan tags: common-table-expr | |
| 14:40 | Don't try to verify the schema of transient table (such as generated inside a WITH clause) when generating code for "IN table" operators. check-in: 860aa936 user: drh tags: common-table-expr | |
Changes
Changes to src/select.c.
3552
3553
3554
3555
3556
3557
3558
3559
3560
3561
3562
3563
3564
3565
....
3575
3576
3577
3578
3579
3580
3581
3582
3583
3584
3585
3586
3587
3588
3589
3590
3591
3592
3593
3594
3595
|
if( pCte==pParse->pCte && (pTab = pCte->pTab) ){
/* This is the recursive part of a recursive CTE */
pFrom->pTab = pTab;
pTab->nRef++;
}else{
ExprList *pEList;
Select *pSel;
int bRecursive;
pFrom->pTab = pTab = sqlite3DbMallocZero(db, sizeof(Table));
if( pTab==0 ) return WRC_Abort;
pTab->nRef = 1;
pTab->zName = sqlite3MPrintf(db, "%s", pCte->zName);
pTab->iPKey = -1;
................................................................................
if( bRecursive ){
assert( pSel->pPrior );
sqlite3WalkSelect(pWalker, pSel->pPrior);
}else{
sqlite3WalkSelect(pWalker, pSel);
}
if( pCte->pCols ){
pEList = pCte->pCols;
}else{
Select *pLeft;
for(pLeft=pSel; pLeft->pPrior; pLeft=pLeft->pPrior);
pEList = pLeft->pEList;
}
selectColumnsFromExprList(pParse, pEList, &pTab->nCol, &pTab->aCol);
if( bRecursive ){
int nRef = pTab->nRef;
pCte->pTab = pTab;
sqlite3WalkSelect(pWalker, pSel);
pCte->pTab = 0;
|
>
<
<
<
<
|
|
>
>
>
>
>
>
|
>
>
>
|
3552
3553
3554
3555
3556
3557
3558
3559
3560
3561
3562
3563
3564
3565
3566
....
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
|
if( pCte==pParse->pCte && (pTab = pCte->pTab) ){
/* This is the recursive part of a recursive CTE */
pFrom->pTab = pTab;
pTab->nRef++;
}else{
ExprList *pEList;
Select *pSel;
Select *pLeft; /* Left-most SELECT statement */
int bRecursive;
pFrom->pTab = pTab = sqlite3DbMallocZero(db, sizeof(Table));
if( pTab==0 ) return WRC_Abort;
pTab->nRef = 1;
pTab->zName = sqlite3MPrintf(db, "%s", pCte->zName);
pTab->iPKey = -1;
................................................................................
if( bRecursive ){
assert( pSel->pPrior );
sqlite3WalkSelect(pWalker, pSel->pPrior);
}else{
sqlite3WalkSelect(pWalker, pSel);
}
for(pLeft=pSel; pLeft->pPrior; pLeft=pLeft->pPrior);
pEList = pLeft->pEList;
if( pCte->pCols ){
if( pEList->nExpr!=pCte->pCols->nExpr ){
sqlite3ErrorMsg(pParse, "cte \"%s\" returns %d values for %d columns",
pCte->zName, pEList->nExpr, pCte->pCols->nExpr
);
return WRC_Abort;
}
pEList = pCte->pCols;
}
selectColumnsFromExprList(pParse, pEList, &pTab->nCol, &pTab->aCol);
if( bRecursive ){
int nRef = pTab->nRef;
pCte->pTab = pTab;
sqlite3WalkSelect(pWalker, pSel);
pCte->pTab = 0;
|
Changes to test/with1.test.
160 161 162 163 164 165 166 167 168 169 170 171 172 173 |
} {1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0}
do_execsql_test 5.5 {
WITH i(x) AS ( VALUES(1) UNION SELECT (x+1)%10 FROM i)
SELECT x FROM i LIMIT 20;
} {1 2 3 4 5 6 7 8 9 0}
#-------------------------------------------------------------------------
#
do_execsql_test 6.1 {
CREATE TABLE f(
id INTEGER PRIMARY KEY, parentid REFERENCES f, name TEXT
);
|
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > |
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 206 207 208 209 |
} {1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0}
do_execsql_test 5.5 {
WITH i(x) AS ( VALUES(1) UNION SELECT (x+1)%10 FROM i)
SELECT x FROM i LIMIT 20;
} {1 2 3 4 5 6 7 8 9 0}
do_catchsql_test 5.6.1 {
WITH i(x, y) AS ( VALUES(1) )
SELECT * FROM i;
} {1 {cte "i" returns 1 values for 2 columns}}
do_catchsql_test 5.6.2 {
WITH i(x) AS ( VALUES(1,2) )
SELECT * FROM i;
} {1 {cte "i" returns 2 values for 1 columns}}
do_catchsql_test 5.6.3 {
CREATE TABLE t5(a, b);
WITH i(x) AS ( SELECT * FROM t5 )
SELECT * FROM i;
} {1 {cte "i" returns 2 values for 1 columns}}
do_catchsql_test 5.6.4 {
WITH i(x) AS ( SELECT 1, 2 UNION ALL SELECT 1 )
SELECT * FROM i;
} {1 {cte "i" returns 2 values for 1 columns}}
do_catchsql_test 5.6.5 {
WITH i(x) AS ( SELECT 1 UNION ALL SELECT 1, 2 )
SELECT * FROM i;
} {1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}}
do_catchsql_test 5.6.6 {
WITH i(x) AS ( SELECT 1 UNION ALL SELECT x+1, x*2 FROM i )
SELECT * FROM i;
} {1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}}
do_catchsql_test 5.6.7 {
WITH i(x) AS ( SELECT 1, 2 UNION SELECT x+1 FROM i )
SELECT * FROM i;
} {1 {cte "i" returns 2 values for 1 columns}}
#-------------------------------------------------------------------------
#
do_execsql_test 6.1 {
CREATE TABLE f(
id INTEGER PRIMARY KEY, parentid REFERENCES f, name TEXT
);
|