/ Check-in [0055df04]
Login

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:0055df0445932a43e42b318ef88672dcbe312c3a
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
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

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