Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | CTEs have never had working rowids. So disallow the use of the "rowid" column within CTEs. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | no-rowid-in-cte |
| Files: | files | file ages | folders |
| SHA1: |
0055df0445932a43e42b318ef88672dc |
| User & Date: | drh 2015-05-27 13:06:55 |
| Original Comment: | CTEs have never add working rowids. So disallow the use of the "rowid" column within CTEs. |
Context
|
2015-05-27
| ||
| 15:10 | Disallow the use of "rowid" in CTEs - it has never worked correctly and it makes no sense, so we might as well make it an explicit error. Also: add the PRAGMA cell_size_check=ON command. check-in: 19e2cebc user: drh tags: trunk | |
| 13:06 | CTEs have never had working rowids. So disallow the use of the "rowid" column within CTEs. Closed-Leaf check-in: 0055df04 user: drh tags: no-rowid-in-cte | |
|
2015-05-26
| ||
| 20:31 | Avoid a buffer overread when comparing against a corrupt record that spans at least one overflow page. check-in: 62a5b363 user: dan tags: trunk | |
Changes
Changes to src/build.c.
1840 1840 sqlite3ErrorMsg(pParse, 1841 1841 "AUTOINCREMENT not allowed on WITHOUT ROWID tables"); 1842 1842 return; 1843 1843 } 1844 1844 if( (p->tabFlags & TF_HasPrimaryKey)==0 ){ 1845 1845 sqlite3ErrorMsg(pParse, "PRIMARY KEY missing on table %s", p->zName); 1846 1846 }else{ 1847 - p->tabFlags |= TF_WithoutRowid; 1847 + p->tabFlags |= TF_WithoutRowid | TF_NoVisibleRowid; 1848 1848 convertToWithoutRowidTable(pParse, p); 1849 1849 } 1850 1850 } 1851 1851 1852 1852 iDb = sqlite3SchemaToIndex(db, p->pSchema); 1853 1853 1854 1854 #ifndef SQLITE_OMIT_CHECK
Changes to src/parse.y.
162 162 sqlite3EndTable(pParse,0,0,0,S); 163 163 sqlite3SelectDelete(pParse->db, S); 164 164 } 165 165 %type table_options {u8} 166 166 table_options(A) ::= . {A = 0;} 167 167 table_options(A) ::= WITHOUT nm(X). { 168 168 if( X.n==5 && sqlite3_strnicmp(X.z,"rowid",5)==0 ){ 169 - A = TF_WithoutRowid; 169 + A = TF_WithoutRowid | TF_NoVisibleRowid; 170 170 }else{ 171 171 A = 0; 172 172 sqlite3ErrorMsg(pParse, "unknown table option: %.*s", X.n, X.z); 173 173 } 174 174 } 175 175 columnlist ::= columnlist COMMA column. 176 176 columnlist ::= column.
Changes to src/resolve.c.
354 354 if( sqlite3StrICmp(pCol->zName, zCol)==0 ){ 355 355 if( iCol==pTab->iPKey ){ 356 356 iCol = -1; 357 357 } 358 358 break; 359 359 } 360 360 } 361 - if( iCol>=pTab->nCol && sqlite3IsRowid(zCol) && HasRowid(pTab) ){ 361 + if( iCol>=pTab->nCol && sqlite3IsRowid(zCol) && VisibleRowid(pTab) ){ 362 362 /* IMP: R-51414-32910 */ 363 363 /* IMP: R-44911-55124 */ 364 364 iCol = -1; 365 365 } 366 366 if( iCol<pTab->nCol ){ 367 367 cnt++; 368 368 if( iCol<0 ){ ................................................................................ 384 384 } 385 385 #endif /* !defined(SQLITE_OMIT_TRIGGER) */ 386 386 387 387 /* 388 388 ** Perhaps the name is a reference to the ROWID 389 389 */ 390 390 if( cnt==0 && cntTab==1 && pMatch && sqlite3IsRowid(zCol) 391 - && HasRowid(pMatch->pTab) ){ 391 + && VisibleRowid(pMatch->pTab) ){ 392 392 cnt = 1; 393 393 pExpr->iColumn = -1; /* IMP: R-44911-55124 */ 394 394 pExpr->affinity = SQLITE_AFF_INTEGER; 395 395 } 396 396 397 397 /* 398 398 ** If the input is of the form Z (not Y.Z or X.Y.Z) then the name Z
Changes to src/select.c.
3987 3987 assert( pFrom->pTab==0 ); 3988 3988 pFrom->pTab = pTab = sqlite3DbMallocZero(db, sizeof(Table)); 3989 3989 if( pTab==0 ) return WRC_Abort; 3990 3990 pTab->nRef = 1; 3991 3991 pTab->zName = sqlite3DbStrDup(db, pCte->zName); 3992 3992 pTab->iPKey = -1; 3993 3993 pTab->nRowLogEst = 200; assert( 200==sqlite3LogEst(1048576) ); 3994 - pTab->tabFlags |= TF_Ephemeral; 3994 + pTab->tabFlags |= TF_Ephemeral | TF_NoVisibleRowid; 3995 3995 pFrom->pSelect = sqlite3SelectDup(db, pCte->pSelect, 0); 3996 3996 if( db->mallocFailed ) return SQLITE_NOMEM; 3997 3997 assert( pFrom->pSelect ); 3998 3998 3999 3999 /* Check if this is a recursive CTE. */ 4000 4000 pSel = pFrom->pSelect; 4001 4001 bMayRecursive = ( pSel->op==TK_ALL || pSel->op==TK_UNION );
Changes to src/sqliteInt.h.
1630 1630 ** special handling during INSERT processing. 1631 1631 */ 1632 1632 #define TF_Readonly 0x01 /* Read-only system table */ 1633 1633 #define TF_Ephemeral 0x02 /* An ephemeral table */ 1634 1634 #define TF_HasPrimaryKey 0x04 /* Table has a primary key */ 1635 1635 #define TF_Autoincrement 0x08 /* Integer primary key is autoincrement */ 1636 1636 #define TF_Virtual 0x10 /* Is a virtual table */ 1637 -#define TF_WithoutRowid 0x20 /* No rowid used. PRIMARY KEY is the key */ 1638 -#define TF_OOOHidden 0x40 /* Out-of-Order hidden columns */ 1637 +#define TF_WithoutRowid 0x20 /* No rowid. PRIMARY KEY is the key */ 1638 +#define TF_NoVisibleRowid 0x40 /* No user-visible "rowid" column */ 1639 +#define TF_OOOHidden 0x80 /* Out-of-Order hidden columns */ 1639 1640 1640 1641 1641 1642 /* 1642 1643 ** Test to see whether or not a table is a virtual table. This is 1643 1644 ** done as a macro so that it will be optimized out when virtual 1644 1645 ** table support is omitted from the build. 1645 1646 */ ................................................................................ 1649 1650 #else 1650 1651 # define IsVirtual(X) 0 1651 1652 # define IsHiddenColumn(X) 0 1652 1653 #endif 1653 1654 1654 1655 /* Does the table have a rowid */ 1655 1656 #define HasRowid(X) (((X)->tabFlags & TF_WithoutRowid)==0) 1657 +#define VisibleRowid(X) (((X)->tabFlags & TF_NoVisibleRowid)==0) 1656 1658 1657 1659 /* 1658 1660 ** Each foreign key constraint is an instance of the following structure. 1659 1661 ** 1660 1662 ** A foreign key is associated with two tables. The "from" table is 1661 1663 ** the table that contains the REFERENCES clause that creates the foreign 1662 1664 ** key. The "to" table is the table that is named in the REFERENCES clause.
Changes to test/with1.test.
844 844 } {1 {table c has 1 values for 2 columns}} 845 845 846 846 # 2015-04-12 847 847 # 848 848 do_execsql_test 14.1 { 849 849 WITH x AS (SELECT * FROM t) SELECT 0 EXCEPT SELECT 0 ORDER BY 1 COLLATE binary; 850 850 } {} 851 + 852 +# 2015-05-27: Do not allow rowid usage within a CTE 853 +# 854 +do_catchsql_test 15.1 { 855 + WITH RECURSIVE 856 + d(x) AS (VALUES(1) UNION ALL SELECT rowid+1 FROM d WHERE rowid<10) 857 + SELECT x FROM d; 858 +} {1 {no such column: rowid}} 859 + 851 860 852 861 finish_test
Changes to test/without_rowid1.test.
304 304 VACUUM; 305 305 PRAGMA integrity_check; 306 306 SELECT name FROM sqlite_master WHERE tbl_name = 't48'; 307 307 } { 308 308 ok t48 sqlite_autoindex_t48_2 309 309 } 310 310 311 +# 2015-05-28: CHECK constraints can refer to the rowid in a 312 +# rowid table, but not in a WITHOUT ROWID table. 313 +# 314 +do_execsql_test 7.1 { 315 + CREATE TABLE t70a( 316 + a INT CHECK( rowid!=33 ), 317 + b TEXT PRIMARY KEY 318 + ); 319 + INSERT INTO t70a(a,b) VALUES(99,'hello'); 320 +} {} 321 +do_catchsql_test 7.2 { 322 + INSERT INTO t70a(rowid,a,b) VALUES(33,99,'xyzzy'); 323 +} {1 {CHECK constraint failed: t70a}} 324 +do_catchsql_test 7.3 { 325 + CREATE TABLE t70b( 326 + a INT CHECK( rowid!=33 ), 327 + b TEXT PRIMARY KEY 328 + ) WITHOUT ROWID; 329 +} {1 {no such column: rowid}} 311 330 312 331 313 332 finish_test