SQLite

Check-in [40d3282e]
Login

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

Overview
Comment:Correctly generate pre-UPDATE content for virtual columns that are used by foreign key constraints. Ticket [b9befa4b83a660cc]
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 40d3282ec285d9f724f6548283f48b601510cf5284da17485723fd650a68f436
User & Date: drh 2019-10-31 20:54:20
Context
2019-11-01
02:30
Slightly faster keyword hash table. (check-in: f12e743e user: drh tags: trunk)
2019-10-31
20:54
Correctly generate pre-UPDATE content for virtual columns that are used by foreign key constraints. Ticket [b9befa4b83a660cc] (check-in: 40d3282e user: drh tags: trunk)
17:13
Add a few simple TCL test cases for generated columns. (Full test coverage of the generated column logic is provided separately by TH3.) (check-in: acedb5c7 user: drh tags: trunk)
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/update.c.

︙︙
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676

  /* Compute the old pre-UPDATE content of the row being changed, if that
  ** information is needed */
  if( chngPk || hasFK || pTrigger ){
    u32 oldmask = (hasFK ? sqlite3FkOldmask(pParse, pTab) : 0);
    oldmask |= sqlite3TriggerColmask(pParse, 
        pTrigger, pChanges, 0, TRIGGER_BEFORE|TRIGGER_AFTER, pTab, onError
    );
    for(i=0, k=regOld; i<pTab->nCol; i++, k++){
      u32 colFlags = pTab->aCol[i].colFlags;
      if( colFlags & COLFLAG_VIRTUAL ){
        k--;
        continue;
      }
      if( oldmask==0xffffffff
       || (i<32 && (oldmask & MASKBIT32(i))!=0)
       || (colFlags & COLFLAG_PRIMKEY)!=0
      ){
        testcase(  oldmask!=0xffffffff && i==31 );
        sqlite3ExprCodeGetColumnOfTable(v, pTab, iDataCur, i, k);
      }else{








|

|
<
<
<








657
658
659
660
661
662
663
664
665
666



667
668
669
670
671
672
673

  /* Compute the old pre-UPDATE content of the row being changed, if that
  ** information is needed */
  if( chngPk || 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++){
      u32 colFlags = pTab->aCol[i].colFlags;
      k = sqlite3TableColumnToStorage(pTab, i) + regOld;



      if( oldmask==0xffffffff
       || (i<32 && (oldmask & MASKBIT32(i))!=0)
       || (colFlags & COLFLAG_PRIMKEY)!=0
      ){
        testcase(  oldmask!=0xffffffff && i==31 );
        sqlite3ExprCodeGetColumnOfTable(v, pTab, iDataCur, i, k);
      }else{
︙︙

Changes to test/gencol1.test.

︙︙
141
142
143
144
145
146
147














148

  } {3 30 | 101 1010 |}
  do_execsql_test gencol1-2.$tn.150 {
    INSERT INTO t1 VALUES(4,'jambalaya','Chef John'),(15,87719874135,0);
    SELECT w, x, y, '|' FROM t1 ORDER BY w;
  } {30 null ntalo | 40 text balaya | 150 integer {} | 1010 real {} |}
}















finish_test








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


141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162

  } {3 30 | 101 1010 |}
  do_execsql_test gencol1-2.$tn.150 {
    INSERT INTO t1 VALUES(4,'jambalaya','Chef John'),(15,87719874135,0);
    SELECT w, x, y, '|' FROM t1 ORDER BY w;
  } {30 null ntalo | 40 text balaya | 150 integer {} | 1010 real {} |}
}

# 2019-10-31 ticket b9befa4b83a660cc
db close
sqlite3 db :memory:
do_execsql_test gencol1-3.100 {
  PRAGMA foreign_keys = true;
  CREATE TABLE t0(c0 PRIMARY KEY, c1, c2 AS (c0+c1-c3) REFERENCES t0, c3);
  INSERT INTO t0 VALUES (0, 0, 0), (11, 5, 5);
  UPDATE t0 SET c1 = c0, c3 = c0;
  SELECT *, '|' FROM t0 ORDER BY +c0;
} {0 0 0 0 | 11 11 11 11 |}
do_catchsql_test gencol1-3.110 {
  UPDATE t0 SET c1 = c0, c3 = c0+1;
} {1 {FOREIGN KEY constraint failed}}

finish_test