/ Check-in [0055df04]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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 Unified Diffs Ignore Whitespace Patch

Changes to src/build.c.

1840
1841
1842
1843
1844
1845
1846
1847
1848
1849
1850
1851
1852
1853
1854
      sqlite3ErrorMsg(pParse,
          "AUTOINCREMENT not allowed on WITHOUT ROWID tables");
      return;
    }
    if( (p->tabFlags & TF_HasPrimaryKey)==0 ){
      sqlite3ErrorMsg(pParse, "PRIMARY KEY missing on table %s", p->zName);
    }else{
      p->tabFlags |= TF_WithoutRowid;
      convertToWithoutRowidTable(pParse, p);
    }
  }

  iDb = sqlite3SchemaToIndex(db, p->pSchema);

#ifndef SQLITE_OMIT_CHECK







|







1840
1841
1842
1843
1844
1845
1846
1847
1848
1849
1850
1851
1852
1853
1854
      sqlite3ErrorMsg(pParse,
          "AUTOINCREMENT not allowed on WITHOUT ROWID tables");
      return;
    }
    if( (p->tabFlags & TF_HasPrimaryKey)==0 ){
      sqlite3ErrorMsg(pParse, "PRIMARY KEY missing on table %s", p->zName);
    }else{
      p->tabFlags |= TF_WithoutRowid | TF_NoVisibleRowid;
      convertToWithoutRowidTable(pParse, p);
    }
  }

  iDb = sqlite3SchemaToIndex(db, p->pSchema);

#ifndef SQLITE_OMIT_CHECK

Changes to src/parse.y.

162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
  sqlite3EndTable(pParse,0,0,0,S);
  sqlite3SelectDelete(pParse->db, S);
}
%type table_options {u8}
table_options(A) ::= .    {A = 0;}
table_options(A) ::= WITHOUT nm(X). {
  if( X.n==5 && sqlite3_strnicmp(X.z,"rowid",5)==0 ){
    A = TF_WithoutRowid;
  }else{
    A = 0;
    sqlite3ErrorMsg(pParse, "unknown table option: %.*s", X.n, X.z);
  }
}
columnlist ::= columnlist COMMA column.
columnlist ::= column.







|







162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
  sqlite3EndTable(pParse,0,0,0,S);
  sqlite3SelectDelete(pParse->db, S);
}
%type table_options {u8}
table_options(A) ::= .    {A = 0;}
table_options(A) ::= WITHOUT nm(X). {
  if( X.n==5 && sqlite3_strnicmp(X.z,"rowid",5)==0 ){
    A = TF_WithoutRowid | TF_NoVisibleRowid;
  }else{
    A = 0;
    sqlite3ErrorMsg(pParse, "unknown table option: %.*s", X.n, X.z);
  }
}
columnlist ::= columnlist COMMA column.
columnlist ::= column.

Changes to src/resolve.c.

354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
...
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
          if( sqlite3StrICmp(pCol->zName, zCol)==0 ){
            if( iCol==pTab->iPKey ){
              iCol = -1;
            }
            break;
          }
        }
        if( iCol>=pTab->nCol && sqlite3IsRowid(zCol) && HasRowid(pTab) ){
          /* IMP: R-51414-32910 */
          /* IMP: R-44911-55124 */
          iCol = -1;
        }
        if( iCol<pTab->nCol ){
          cnt++;
          if( iCol<0 ){
................................................................................
    }
#endif /* !defined(SQLITE_OMIT_TRIGGER) */

    /*
    ** Perhaps the name is a reference to the ROWID
    */
    if( cnt==0 && cntTab==1 && pMatch && sqlite3IsRowid(zCol)
     && HasRowid(pMatch->pTab) ){
      cnt = 1;
      pExpr->iColumn = -1;     /* IMP: R-44911-55124 */
      pExpr->affinity = SQLITE_AFF_INTEGER;
    }

    /*
    ** If the input is of the form Z (not Y.Z or X.Y.Z) then the name Z







|







 







|







354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
...
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
          if( sqlite3StrICmp(pCol->zName, zCol)==0 ){
            if( iCol==pTab->iPKey ){
              iCol = -1;
            }
            break;
          }
        }
        if( iCol>=pTab->nCol && sqlite3IsRowid(zCol) && VisibleRowid(pTab) ){
          /* IMP: R-51414-32910 */
          /* IMP: R-44911-55124 */
          iCol = -1;
        }
        if( iCol<pTab->nCol ){
          cnt++;
          if( iCol<0 ){
................................................................................
    }
#endif /* !defined(SQLITE_OMIT_TRIGGER) */

    /*
    ** Perhaps the name is a reference to the ROWID
    */
    if( cnt==0 && cntTab==1 && pMatch && sqlite3IsRowid(zCol)
     && VisibleRowid(pMatch->pTab) ){
      cnt = 1;
      pExpr->iColumn = -1;     /* IMP: R-44911-55124 */
      pExpr->affinity = SQLITE_AFF_INTEGER;
    }

    /*
    ** 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
3988
3989
3990
3991
3992
3993
3994
3995
3996
3997
3998
3999
4000
4001
    assert( pFrom->pTab==0 );
    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;
    pTab->nRowLogEst = 200; assert( 200==sqlite3LogEst(1048576) );
    pTab->tabFlags |= TF_Ephemeral;
    pFrom->pSelect = sqlite3SelectDup(db, pCte->pSelect, 0);
    if( db->mallocFailed ) return SQLITE_NOMEM;
    assert( pFrom->pSelect );

    /* Check if this is a recursive CTE. */
    pSel = pFrom->pSelect;
    bMayRecursive = ( pSel->op==TK_ALL || pSel->op==TK_UNION );







|







3987
3988
3989
3990
3991
3992
3993
3994
3995
3996
3997
3998
3999
4000
4001
    assert( pFrom->pTab==0 );
    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;
    pTab->nRowLogEst = 200; assert( 200==sqlite3LogEst(1048576) );
    pTab->tabFlags |= TF_Ephemeral | TF_NoVisibleRowid;
    pFrom->pSelect = sqlite3SelectDup(db, pCte->pSelect, 0);
    if( db->mallocFailed ) return SQLITE_NOMEM;
    assert( pFrom->pSelect );

    /* Check if this is a recursive CTE. */
    pSel = pFrom->pSelect;
    bMayRecursive = ( pSel->op==TK_ALL || pSel->op==TK_UNION );

Changes to src/sqliteInt.h.

1630
1631
1632
1633
1634
1635
1636
1637

1638
1639
1640
1641
1642
1643
1644
1645
....
1649
1650
1651
1652
1653
1654
1655

1656
1657
1658
1659
1660
1661
1662
** special handling during INSERT processing.
*/
#define TF_Readonly        0x01    /* Read-only system table */
#define TF_Ephemeral       0x02    /* An ephemeral table */
#define TF_HasPrimaryKey   0x04    /* Table has a primary key */
#define TF_Autoincrement   0x08    /* Integer primary key is autoincrement */
#define TF_Virtual         0x10    /* Is a virtual table */
#define TF_WithoutRowid    0x20    /* No rowid used. PRIMARY KEY is the key */

#define TF_OOOHidden       0x40    /* Out-of-Order hidden columns */


/*
** Test to see whether or not a table is a virtual table.  This is
** done as a macro so that it will be optimized out when virtual
** table support is omitted from the build.
*/
................................................................................
#else
#  define IsVirtual(X)      0
#  define IsHiddenColumn(X) 0
#endif

/* Does the table have a rowid */
#define HasRowid(X)     (((X)->tabFlags & TF_WithoutRowid)==0)


/*
** Each foreign key constraint is an instance of the following structure.
**
** A foreign key is associated with two tables.  The "from" table is
** the table that contains the REFERENCES clause that creates the foreign
** key.  The "to" table is the table that is named in the REFERENCES clause.







|
>
|







 







>







1630
1631
1632
1633
1634
1635
1636
1637
1638
1639
1640
1641
1642
1643
1644
1645
1646
....
1650
1651
1652
1653
1654
1655
1656
1657
1658
1659
1660
1661
1662
1663
1664
** special handling during INSERT processing.
*/
#define TF_Readonly        0x01    /* Read-only system table */
#define TF_Ephemeral       0x02    /* An ephemeral table */
#define TF_HasPrimaryKey   0x04    /* Table has a primary key */
#define TF_Autoincrement   0x08    /* Integer primary key is autoincrement */
#define TF_Virtual         0x10    /* Is a virtual table */
#define TF_WithoutRowid    0x20    /* No rowid.  PRIMARY KEY is the key */
#define TF_NoVisibleRowid  0x40    /* No user-visible "rowid" column */
#define TF_OOOHidden       0x80    /* Out-of-Order hidden columns */


/*
** Test to see whether or not a table is a virtual table.  This is
** done as a macro so that it will be optimized out when virtual
** table support is omitted from the build.
*/
................................................................................
#else
#  define IsVirtual(X)      0
#  define IsHiddenColumn(X) 0
#endif

/* Does the table have a rowid */
#define HasRowid(X)     (((X)->tabFlags & TF_WithoutRowid)==0)
#define VisibleRowid(X) (((X)->tabFlags & TF_NoVisibleRowid)==0)

/*
** Each foreign key constraint is an instance of the following structure.
**
** A foreign key is associated with two tables.  The "from" table is
** the table that contains the REFERENCES clause that creates the foreign
** key.  The "to" table is the table that is named in the REFERENCES clause.

Changes to test/with1.test.

844
845
846
847
848
849
850
851









852
} {1 {table c has 1 values for 2 columns}}

# 2015-04-12
#
do_execsql_test 14.1 {
  WITH x AS (SELECT * FROM t) SELECT 0 EXCEPT SELECT 0 ORDER BY 1 COLLATE binary;
} {}










finish_test








>
>
>
>
>
>
>
>
>

844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
} {1 {table c has 1 values for 2 columns}}

# 2015-04-12
#
do_execsql_test 14.1 {
  WITH x AS (SELECT * FROM t) SELECT 0 EXCEPT SELECT 0 ORDER BY 1 COLLATE binary;
} {}

# 2015-05-27:  Do not allow rowid usage within a CTE
#
do_catchsql_test 15.1 {
  WITH RECURSIVE
    d(x) AS (VALUES(1) UNION ALL SELECT rowid+1 FROM d WHERE rowid<10)
  SELECT x FROM d;
} {1 {no such column: rowid}}


finish_test

Changes to test/without_rowid1.test.

304
305
306
307
308
309
310



















311
312
313
  VACUUM;
  PRAGMA integrity_check;
  SELECT name FROM sqlite_master WHERE tbl_name = 't48';
} {
  ok  t48   sqlite_autoindex_t48_2
}





















  
finish_test







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>



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
  VACUUM;
  PRAGMA integrity_check;
  SELECT name FROM sqlite_master WHERE tbl_name = 't48';
} {
  ok  t48   sqlite_autoindex_t48_2
}

# 2015-05-28: CHECK constraints can refer to the rowid in a
# rowid table, but not in a WITHOUT ROWID table.
#
do_execsql_test 7.1 {
  CREATE TABLE t70a(
     a INT CHECK( rowid!=33 ),
     b TEXT PRIMARY KEY
  );
  INSERT INTO t70a(a,b) VALUES(99,'hello');
} {}
do_catchsql_test 7.2 {
  INSERT INTO t70a(rowid,a,b) VALUES(33,99,'xyzzy');
} {1 {CHECK constraint failed: t70a}}
do_catchsql_test 7.3 {
  CREATE TABLE t70b(
     a INT CHECK( rowid!=33 ),
     b TEXT PRIMARY KEY
  ) WITHOUT ROWID;
} {1 {no such column: rowid}}

  
finish_test