Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Tweaks to error message text. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | common-table-expr |
| Files: | files | file ages | folders |
| SHA1: |
090a77d97808b86d1e9f5c63c743a2b1 |
| User & Date: | drh 2014-01-16 21:59:51 |
Context
|
2014-01-16
| ||
| 22:40 | Add the ability for the authorizer callback to disallow recursive queries. check-in: 9efc120a user: drh tags: common-table-expr | |
| 21:59 | Tweaks to error message text. check-in: 090a77d9 user: drh tags: common-table-expr | |
| 21:02 | Improve the error messages used to report illegal recursive cte references. check-in: 54eee9fe user: dan tags: common-table-expr | |
Changes
Changes to src/build.c.
4218 4219 4220 4221 4222 4223 4224 4225 4226 4227 4228 4229 4230 4231 4232 |
/* Check that the CTE name is unique within this WITH clause. If
** not, store an error in the Parse structure. */
zName = sqlite3NameFromToken(pParse->db, pName);
if( zName && pWith ){
int i;
for(i=0; i<pWith->nCte; i++){
if( sqlite3StrICmp(zName, pWith->a[i].zName)==0 ){
sqlite3ErrorMsg(pParse, "duplicate cte name: %s", zName);
}
}
}
if( pWith ){
int nByte = sizeof(*pWith) + (sizeof(pWith->a[1]) * pWith->nCte);
pNew = sqlite3DbRealloc(db, pWith, nByte);
|
| |
4218 4219 4220 4221 4222 4223 4224 4225 4226 4227 4228 4229 4230 4231 4232 |
/* Check that the CTE name is unique within this WITH clause. If
** not, store an error in the Parse structure. */
zName = sqlite3NameFromToken(pParse->db, pName);
if( zName && pWith ){
int i;
for(i=0; i<pWith->nCte; i++){
if( sqlite3StrICmp(zName, pWith->a[i].zName)==0 ){
sqlite3ErrorMsg(pParse, "duplicate WITH table name: %s", zName);
}
}
}
if( pWith ){
int nByte = sizeof(*pWith) + (sizeof(pWith->a[1]) * pWith->nCte);
pNew = sqlite3DbRealloc(db, pWith, nByte);
|
Changes to src/select.c.
3605 3606 3607 3608 3609 3610 3611 3612 3613 3614 3615 3616 3617 3618 3619 3620 3621 3622 3623 3624 3625 3626 3627 3628 3629 3630 3631 3632 3633 3634 3635 3636 3637 3638 3639 3640 3641 3642 3643 3644 3645 |
}
}
}
/* Only one recursive reference is permitted. */
if( pTab->nRef>2 ){
sqlite3ErrorMsg(
pParse, "multiple recursive references in cte: %s", pCte->zName
);
return WRC_Abort;
}
assert( pTab->nRef==1 || ((pSel->selFlags&SF_Recursive) && pTab->nRef==2 ));
pCte->zErr = "circular reference to cte: %s";
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, "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( bMayRecursive ){
if( pSel->selFlags & SF_Recursive ){
pCte->zErr = "multiple recursive references in cte: %s";
}else{
pCte->zErr = "recursive reference may not appear in sub-query: %s";
}
sqlite3WalkSelect(pWalker, pSel);
}
pCte->zErr = 0;
}
return SQLITE_OK;
|
| | | | | |
3605 3606 3607 3608 3609 3610 3611 3612 3613 3614 3615 3616 3617 3618 3619 3620 3621 3622 3623 3624 3625 3626 3627 3628 3629 3630 3631 3632 3633 3634 3635 3636 3637 3638 3639 3640 3641 3642 3643 3644 3645 |
}
}
}
/* Only one recursive reference is permitted. */
if( pTab->nRef>2 ){
sqlite3ErrorMsg(
pParse, "multiple references to recursive table: %s", pCte->zName
);
return WRC_Abort;
}
assert( pTab->nRef==1 || ((pSel->selFlags&SF_Recursive) && pTab->nRef==2 ));
pCte->zErr = "circular reference: %s";
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
);
return WRC_Abort;
}
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;
}
return SQLITE_OK;
|
Changes to test/with1.test.
75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 ... 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 210 211 212 213 ... 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 |
} {1 2}
#-------------------------------------------------------------------------
do_catchsql_test 3.1 {
WITH tmp2(x) AS ( SELECT * FROM tmp1 ),
tmp1(a) AS ( SELECT * FROM tmp2 )
SELECT * FROM tmp1;
} {1 {circular reference to cte: tmp1}}
do_catchsql_test 3.2 {
CREATE TABLE t2(x INTEGER);
WITH tmp(a) AS (SELECT * FROM t1),
tmp(a) AS (SELECT * FROM t1)
SELECT * FROM tmp;
} {1 {duplicate cte name: tmp}}
do_execsql_test 3.3 {
CREATE TABLE t3(x);
CREATE TABLE t4(x);
INSERT INTO t3 VALUES('T3');
INSERT INTO t4 VALUES('T4');
................................................................................
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
);
................................................................................
do_catchsql_test 7.4 {
WITH t(id) AS (
VALUES(2)
UNION ALL
SELECT i FROM tree WHERE p IN (SELECT id FROM t)
)
SELECT id FROM t;
} {1 {recursive reference may not appear in sub-query: t}}
do_catchsql_test 7.5 {
WITH t(id) AS (
VALUES(2)
UNION ALL
SELECT i FROM tree, t WHERE p = id AND p IN (SELECT id FROM t)
)
SELECT id FROM t;
} {1 {multiple recursive references in cte: t}}
do_catchsql_test 7.6 {
WITH t(id) AS (
SELECT i FROM tree WHERE 2 IN (SELECT id FROM t)
UNION ALL
SELECT i FROM tree, t WHERE p = id
)
SELECT id FROM t;
} {1 {circular reference to cte: t}}
finish_test
|
| | | | | | | | | | < < < |
75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 ... 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 210 211 212 213 ... 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 |
} {1 2}
#-------------------------------------------------------------------------
do_catchsql_test 3.1 {
WITH tmp2(x) AS ( SELECT * FROM tmp1 ),
tmp1(a) AS ( SELECT * FROM tmp2 )
SELECT * FROM tmp1;
} {1 {circular reference: tmp1}}
do_catchsql_test 3.2 {
CREATE TABLE t2(x INTEGER);
WITH tmp(a) AS (SELECT * FROM t1),
tmp(a) AS (SELECT * FROM t1)
SELECT * FROM tmp;
} {1 {duplicate WITH table name: tmp}}
do_execsql_test 3.3 {
CREATE TABLE t3(x);
CREATE TABLE t4(x);
INSERT INTO t3 VALUES('T3');
INSERT INTO t4 VALUES('T4');
................................................................................
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 {table i has 1 values for 2 columns}}
do_catchsql_test 5.6.2 {
WITH i(x) AS ( VALUES(1,2) )
SELECT * FROM i;
} {1 {table i has 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 {table i has 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 {table i has 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 {table i has 2 values for 1 columns}}
#-------------------------------------------------------------------------
#
do_execsql_test 6.1 {
CREATE TABLE f(
id INTEGER PRIMARY KEY, parentid REFERENCES f, name TEXT
);
................................................................................
do_catchsql_test 7.4 {
WITH t(id) AS (
VALUES(2)
UNION ALL
SELECT i FROM tree WHERE p IN (SELECT id FROM t)
)
SELECT id FROM t;
} {1 {recursive reference in a subquery: t}}
do_catchsql_test 7.5 {
WITH t(id) AS (
VALUES(2)
UNION ALL
SELECT i FROM tree, t WHERE p = id AND p IN (SELECT id FROM t)
)
SELECT id FROM t;
} {1 {multiple recursive references: t}}
do_catchsql_test 7.6 {
WITH t(id) AS (
SELECT i FROM tree WHERE 2 IN (SELECT id FROM t)
UNION ALL
SELECT i FROM tree, t WHERE p = id
)
SELECT id FROM t;
} {1 {circular reference: t}}
finish_test
|