SQLite: Check-in [636f8609]

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview

Comment:	Make sure the value of an INTEGER PRIMARY KEY column supplied to triggers and especially to FK constraints really contains the ROWID and not the NULL that is stored in the column itself. Ticket [dd08e5a988d00dec].
Downloads:	Tarball \| ZIP archive \| SQL archive
Timelines:	family \| ancestors \| descendants \| both \| trunk
Files:	files \| file ages \| folders
SHA1:	636f86095eb1f4bdcfb0c9ed846c4c6b3589c10b
User & Date:	drh 2010-05-14 19:24:02

References

2010-07-29
01:55		• Ticket [ce7c133e] Foreign key constraint fails when it should succeed. status still Open with 4 other changes artifact: 6fb11fef user: drh
2010-07-28
20:57		• New ticket [ce7c133e]. artifact: 4202cdf3 user: drh

Context

2010-05-14
20:15		Fix a typo that (by bad luck) was not a syntax error but which caused some important lines of code to be skipped when SQLITE_DEBUG was not used. check-in: 9ef99d97 user: drh tags: trunk
19:24		Make sure the value of an INTEGER PRIMARY KEY column supplied to triggers and especially to FK constraints really contains the ROWID and not the NULL that is stored in the column itself. Ticket [dd08e5a988d00dec]. check-in: 636f8609 user: drh tags: trunk
14:52		Simplifications to the SHM implementation in os_unix.c, taking advantage of the removal of the LinuxThreads mess. check-in: d1debe5d user: drh tags: trunk

Changes

Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/delete.c.

    pParse->nMem += (1 + pTab->nCol);

    /* Populate the OLD.* pseudo-table register array. These values will be 
    ** used by any BEFORE and AFTER triggers that exist.  */
    sqlite3VdbeAddOp2(v, OP_Copy, iRowid, iOld);
    for(iCol=0; iCol<pTab->nCol; iCol++){
      if( mask==0xffffffff || mask&(1<<iCol) ){
        int iTarget = iOld + iCol + 1;
        sqlite3VdbeAddOp3(v, OP_Column, iCur, iCol, iTarget);
        sqlite3ColumnDefault(v, pTab, iCol, iTarget);
      }
    }

    /* Invoke BEFORE DELETE trigger programs. */
    sqlite3CodeRowTrigger(pParse, pTrigger, 
        TK_DELETE, 0, TRIGGER_BEFORE, pTab, iOld, onconf, iLabel
    );








|
<
<

    pParse->nMem += (1 + pTab->nCol);

    /* Populate the OLD.* pseudo-table register array. These values will be 
    ** used by any BEFORE and AFTER triggers that exist.  */
    sqlite3VdbeAddOp2(v, OP_Copy, iRowid, iOld);
    for(iCol=0; iCol<pTab->nCol; iCol++){
      if( mask==0xffffffff || mask&(1<<iCol) ){
        sqlite3ExprCodeGetColumnOfTable(v, pTab, iCur, iCol, iOld+iCol+1);


      }
    }

    /* Invoke BEFORE DELETE trigger programs. */
    sqlite3CodeRowTrigger(pParse, pTrigger, 
        TK_DELETE, 0, TRIGGER_BEFORE, pTab, iOld, onconf, iLabel
    );

Changes to src/expr.c.

  struct yColCache *p;
  for(i=0, p=pParse->aColCache; i<SQLITE_N_COLCACHE; i++, p++){
    if( p->iReg==iReg ){
      p->tempReg = 0;
    }
  }
}






















/*
** Generate code that will extract the iColumn-th column from
** table pTab and store the column value in a register.  An effort
** is made to store the column value in register iReg, but this is
** not guaranteed.  The location of the column value is returned.
**
................................................................................
    if( p->iReg>0 && p->iTable==iTable && p->iColumn==iColumn ){
      p->lru = pParse->iCacheCnt++;
      sqlite3ExprCachePinRegister(pParse, p->iReg);
      return p->iReg;
    }
  }  
  assert( v!=0 );
  if( iColumn<0 ){
    sqlite3VdbeAddOp2(v, OP_Rowid, iTable, iReg);
  }else if( ALWAYS(pTab!=0) ){
    int op = IsVirtual(pTab) ? OP_VColumn : OP_Column;
    sqlite3VdbeAddOp3(v, op, iTable, iColumn, iReg);
    sqlite3ColumnDefault(v, pTab, iColumn, iReg);
  }
  sqlite3ExprCacheStore(pParse, iTable, iColumn, iReg);
  return iReg;
}

/*
** Clear all column cache entries.
*/








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







 







|
<
<
<
<
<
<

  struct yColCache *p;
  for(i=0, p=pParse->aColCache; i<SQLITE_N_COLCACHE; i++, p++){
    if( p->iReg==iReg ){
      p->tempReg = 0;
    }
  }
}

/*
** Generate code to extract the value of the iCol-th column of a table.
*/
void sqlite3ExprCodeGetColumnOfTable(
  Vdbe *v,        /* The VDBE under construction */
  Table *pTab,    /* The table containing the value */
  int iTabCur,    /* The cursor for this table */
  int iCol,       /* Index of the column to extract */
  int regOut      /* Extract the valud into this register */
){
  if( iCol<0 || iCol==pTab->iPKey ){
    sqlite3VdbeAddOp2(v, OP_Rowid, iTabCur, regOut);
  }else{
    int op = IsVirtual(pTab) ? OP_VColumn : OP_Column;
    sqlite3VdbeAddOp3(v, op, iTabCur, iCol, regOut);
  }
  if( iCol>=0 ){
    sqlite3ColumnDefault(v, pTab, iCol, regOut);
  }
}

/*
** Generate code that will extract the iColumn-th column from
** table pTab and store the column value in a register.  An effort
** is made to store the column value in register iReg, but this is
** not guaranteed.  The location of the column value is returned.
**
................................................................................
    if( p->iReg>0 && p->iTable==iTable && p->iColumn==iColumn ){
      p->lru = pParse->iCacheCnt++;
      sqlite3ExprCachePinRegister(pParse, p->iReg);
      return p->iReg;
    }
  }  
  assert( v!=0 );
  sqlite3ExprCodeGetColumnOfTable(v, pTab, iTable, iColumn, iReg);






  sqlite3ExprCacheStore(pParse, iTable, iColumn, iReg);
  return iReg;
}

/*
** Clear all column cache entries.
*/

Changes to src/sqliteInt.h.

Expr *sqlite3LimitWhere(Parse *, SrcList *, Expr *, ExprList *, Expr *, Expr *, char *);
#endif
void sqlite3DeleteFrom(Parse*, SrcList*, Expr*);
void sqlite3Update(Parse*, SrcList*, ExprList*, Expr*, int);
WhereInfo *sqlite3WhereBegin(Parse*, SrcList*, Expr*, ExprList**, u16);
void sqlite3WhereEnd(WhereInfo*);
int sqlite3ExprCodeGetColumn(Parse*, Table*, int, int, int);

void sqlite3ExprCodeMove(Parse*, int, int, int);
void sqlite3ExprCodeCopy(Parse*, int, int, int);
void sqlite3ExprCacheStore(Parse*, int, int, int);
void sqlite3ExprCachePush(Parse*);
void sqlite3ExprCachePop(Parse*, int);
void sqlite3ExprCacheRemove(Parse*, int, int);
void sqlite3ExprCacheClear(Parse*);

Expr *sqlite3LimitWhere(Parse *, SrcList *, Expr *, ExprList *, Expr *, Expr *, char *);
#endif
void sqlite3DeleteFrom(Parse*, SrcList*, Expr*);
void sqlite3Update(Parse*, SrcList*, ExprList*, Expr*, int);
WhereInfo *sqlite3WhereBegin(Parse*, SrcList*, Expr*, ExprList**, u16);
void sqlite3WhereEnd(WhereInfo*);
int sqlite3ExprCodeGetColumn(Parse*, Table*, int, int, int);
void sqlite3ExprCodeGetColumnOfTable(Vdbe*, Table*, int, int, int);
void sqlite3ExprCodeMove(Parse*, int, int, int);
void sqlite3ExprCodeCopy(Parse*, int, int, int);
void sqlite3ExprCacheStore(Parse*, int, int, int);
void sqlite3ExprCachePush(Parse*);
void sqlite3ExprCachePop(Parse*, int);
void sqlite3ExprCacheRemove(Parse*, int, int);
void sqlite3ExprCacheClear(Parse*);

Changes to src/update.c.

** The author disclaims copyright to this source code.  In place of
** a legal notice, here is a blessing:
**
**    May you do good and not evil.
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle UPDATE statements.
*/
#include "sqliteInt.h"

#ifndef SQLITE_OMIT_VIRTUALTABLE
/* Forward declaration */
................................................................................
  if( hasFK || pTrigger ){
    u32 oldmask = (hasFK ? sqlite3FkOldmask(pParse, pTab) : 0);
    oldmask |= sqlite3TriggerColmask(pParse, 
        pTrigger, pChanges, 0, TRIGGER_BEFORE|TRIGGER_AFTER, pTab, onError
    );
    for(i=0; i<pTab->nCol; i++){
      if( aXRef[i]<0 || oldmask==0xffffffff || (oldmask & (1<<i)) ){
        sqlite3VdbeAddOp3(v, OP_Column, iCur, i, regOld+i);
        sqlite3ColumnDefault(v, pTab, i, regOld+i);
      }else{
        sqlite3VdbeAddOp2(v, OP_Null, 0, regOld+i);
      }
    }
    if( chngRowid==0 ){
      sqlite3VdbeAddOp2(v, OP_Copy, regOldRowid, regNewRowid);
    }

** The author disclaims copyright to this source code.  In place of
** a legal notice, here is a blessing:
**
**    May you do good and not evil.
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
sqlite*************************************************************************
** This file contains C code routines that are called by the parser
** to handle UPDATE statements.
*/
#include "sqliteInt.h"

#ifndef SQLITE_OMIT_VIRTUALTABLE
/* Forward declaration */
................................................................................
  if( hasFK || pTrigger ){
    u32 oldmask = (hasFK ? sqlite3FkOldmask(pParse, pTab) : 0);
    oldmask |= sqlite3TriggerColmask(pParse, 
        pTrigger, pChanges, 0, TRIGGER_BEFORE|TRIGGER_AFTER, pTab, onError
    );
    for(i=0; i<pTab->nCol; i++){
      if( aXRef[i]<0 || oldmask==0xffffffff || (oldmask & (1<<i)) ){
        sqlite3ExprCodeGetColumnOfTable(v, pTab, iCur, i, regOld+i);

      }else{
        sqlite3VdbeAddOp2(v, OP_Null, 0, regOld+i);
      }
    }
    if( chngRowid==0 ){
      sqlite3VdbeAddOp2(v, OP_Copy, regOldRowid, regNewRowid);
    }

Changes to test/fkey2.test.

# 
# fkey2-18.*: Test that the authorization callback is invoked when processing
#             FK constraints.
#
# fkey2-genfkey.*: Tests that were used with the shell tool .genfkey
#            command. Recycled to test the built-in implementation.
#





execsql { PRAGMA foreign_keys = on }

set FkeySimpleSchema {
  PRAGMA foreign_keys = on;
  CREATE TABLE t1(a PRIMARY KEY, b);
................................................................................
do_test fkey2-genfkey.3.6 {
  execsql {
    UPDATE t3 SET h = 2, i = 2;
    DELETE FROM t1;
    SELECT * FROM t3;
  }
} {hello {} {}}











































finish_test








>
>
>







 








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

# 
# fkey2-18.*: Test that the authorization callback is invoked when processing
#             FK constraints.
#
# fkey2-genfkey.*: Tests that were used with the shell tool .genfkey
#            command. Recycled to test the built-in implementation.
#
# fkey2-dd08e5.*:  Tests to verify that ticket dd08e5a988d00decc4a543daa8d
#                  has been fixed.
#


execsql { PRAGMA foreign_keys = on }

set FkeySimpleSchema {
  PRAGMA foreign_keys = on;
  CREATE TABLE t1(a PRIMARY KEY, b);
................................................................................
do_test fkey2-genfkey.3.6 {
  execsql {
    UPDATE t3 SET h = 2, i = 2;
    DELETE FROM t1;
    SELECT * FROM t3;
  }
} {hello {} {}}

#-------------------------------------------------------------------------
# Verify that ticket dd08e5a988d00decc4a543daa8dbbfab9c577ad8 has been
# fixed.
#
do_test fkey2-dd08e5.1.1 {
  execsql {
    PRAGMA foreign_keys=ON;
    CREATE TABLE tdd08(a INTEGER PRIMARY KEY, b);
    CREATE UNIQUE INDEX idd08 ON tdd08(a,b);
    INSERT INTO tdd08 VALUES(200,300);

    CREATE TABLE tdd08_b(w,x,y, FOREIGN KEY(x,y) REFERENCES tdd08(a,b));
    INSERT INTO tdd08_b VALUES(100,200,300);
  }
} {}
do_test fkey2-dd08e5.1.2 {
  catchsql {
    DELETE FROM tdd08;
  }
} {1 {foreign key constraint failed}}
do_test fkey2-dd08e5.1.3 {
  execsql {
    SELECT * FROM tdd08;
  }
} {200 300}
do_test fkey2-dd08e5.1.4 {
  catchsql {
    INSERT INTO tdd08_b VALUES(400,500,300);
  }
} {1 {foreign key constraint failed}}
do_test fkey2-dd08e5.1.5 {
  catchsql {
    UPDATE tdd08_b SET x=x+1;
  }
} {1 {foreign key constraint failed}}
do_test fkey2-dd08e5.1.6 {
  catchsql {
    UPDATE tdd08 SET a=a+1;
  }
} {1 {foreign key constraint failed}}


finish_test