SQLite

Check-in [1a0e5fa9f0]
Login

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

Overview
Comment:Within the special new.* and old.* tables of a trigger, recognize all the original table names even if those names overload the "rowid", "oid", or "_rowid_" special names. Ticket [34d2ae1c6d0].
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 1a0e5fa9f0ff792c678c99d49960601a0cef925b
User & Date: drh 2009-12-29 23:39:04.000
References
2009-12-29
23:39 Fixed ticket [34d2ae1c6d]: Overloaded column named "ROWID" mishandled in triggers. plus 3 other changes (artifact: e1cacfb412 user: drh)
Context
2009-12-30
00:12
Remove obsolete comment text from expr.c. No functional code changes. (check-in: 84058be0be user: drh tags: trunk)
2009-12-29
23:39
Within the special new.* and old.* tables of a trigger, recognize all the original table names even if those names overload the "rowid", "oid", or "_rowid_" special names. Ticket [34d2ae1c6d0]. (check-in: 1a0e5fa9f0 user: drh tags: trunk)
10:32
Fix a bug triggered by optimizing an FTS3 table when there are no segments on disk but pending terms in the hash table. (check-in: a3b50e4f80 user: dan tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/resolve.c.
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257


258
259
260
261
262
263
264
        pTab = pParse->pTriggerTab;
      }

      if( pTab ){ 
        int iCol;
        pSchema = pTab->pSchema;
        cntTab++;
        if( sqlite3IsRowid(zCol) ){
          iCol = -1;
        }else{
          for(iCol=0; iCol<pTab->nCol; iCol++){
            Column *pCol = &pTab->aCol[iCol];
            if( sqlite3StrICmp(pCol->zName, zCol)==0 ){
              if( iCol==pTab->iPKey ){
                iCol = -1;
              }
              break;
            }
          }


        }
        if( iCol<pTab->nCol ){
          cnt++;
          if( iCol<0 ){
            pExpr->affinity = SQLITE_AFF_INTEGER;
          }else if( pExpr->iTable==0 ){
            testcase( iCol==31 );







<
<
<
|
|
|
|
|
|
|
|
|
>
>







239
240
241
242
243
244
245



246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
        pTab = pParse->pTriggerTab;
      }

      if( pTab ){ 
        int iCol;
        pSchema = pTab->pSchema;
        cntTab++;



        for(iCol=0; iCol<pTab->nCol; iCol++){
          Column *pCol = &pTab->aCol[iCol];
          if( sqlite3StrICmp(pCol->zName, zCol)==0 ){
            if( iCol==pTab->iPKey ){
              iCol = -1;
            }
            break;
          }
        }
        if( iCol>=pTab->nCol && sqlite3IsRowid(zCol) ){
          iCol = -1;
        }
        if( iCol<pTab->nCol ){
          cnt++;
          if( iCol<0 ){
            pExpr->affinity = SQLITE_AFF_INTEGER;
          }else if( pExpr->iTable==0 ){
            testcase( iCol==31 );
Added test/triggerD.test.




























































































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
# 2009 December 29
#
# 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.
#
#***********************************************************************
#
# Verify that when columns named "rowid", "oid", and "_rowid_" appear
# in a table as ordinary columns (not as the INTEGER PRIMARY KEY) then
# the use of these columns in triggers will refer to the column and not
# to the actual ROWID.  Ticket [34d2ae1c6d08b5271ba5e5592936d4a1d913ffe3]
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
ifcapable {!trigger} {
  finish_test
  return
}

# Triggers on tables where the table has ordinary columns named
# rowid, oid, and _rowid_.
#
do_test triggerD-1.1 {
  db eval {
    CREATE TABLE t1(rowid, oid, _rowid_, x);
    CREATE TABLE log(a,b,c,d,e);
    CREATE TRIGGER r1 BEFORE INSERT ON t1 BEGIN
      INSERT INTO log VALUES('r1', new.rowid, new.oid, new._rowid_, new.x);
    END;
    CREATE TRIGGER r2 AFTER INSERT ON t1 BEGIN
      INSERT INTO log VALUES('r2', new.rowid, new.oid, new._rowid_, new.x);
    END;
    CREATE TRIGGER r3 BEFORE UPDATE ON t1 BEGIN
      INSERT INTO log VALUES('r3.old', old.rowid, old.oid, old._rowid_, old.x);
      INSERT INTO log VALUES('r3.new', new.rowid, new.oid, new._rowid_, new.x);
    END;
    CREATE TRIGGER r4 AFTER UPDATE ON t1 BEGIN
      INSERT INTO log VALUES('r4.old', old.rowid, old.oid, old._rowid_, old.x);
      INSERT INTO log VALUES('r4.new', new.rowid, new.oid, new._rowid_, new.x);
    END;
    CREATE TRIGGER r5 BEFORE DELETE ON t1 BEGIN
      INSERT INTO log VALUES('r5', old.rowid, old.oid, old._rowid_, old.x);
    END;
    CREATE TRIGGER r6 AFTER DELETE ON t1 BEGIN
      INSERT INTO log VALUES('r6', old.rowid, old.oid, old._rowid_, old.x);
    END;
  }
} {}
do_test triggerD-1.2 {
  db eval {
    INSERT INTO t1 VALUES(100,200,300,400);
    SELECT * FROM log
  }
} {r1 100 200 300 400 r2 100 200 300 400}
do_test triggerD-1.3 {
  db eval {
    DELETE FROM log;
    UPDATE t1 SET rowid=rowid+1;
    SELECT * FROM log
  }
} {r3.old 100 200 300 400 r3.new 101 200 300 400 r4.old 100 200 300 400 r4.new 101 200 300 400}
do_test triggerD-1.4 {
  db eval {
    DELETE FROM log;
    DELETE FROM t1;
    SELECT * FROM log
  }
} {r5 101 200 300 400 r6 101 200 300 400}

# Triggers on tables where the table does not have ordinary columns named
# rowid, oid, and _rowid_.
#
do_test triggerD-2.1 {
  db eval {
    DROP TABLE t1;
    CREATE TABLE t1(w,x,y,z);
    CREATE TRIGGER r1 BEFORE INSERT ON t1 BEGIN
      INSERT INTO log VALUES('r1', new.rowid, new.oid, new._rowid_, new.x);
    END;
    CREATE TRIGGER r2 AFTER INSERT ON t1 BEGIN
      INSERT INTO log VALUES('r2', new.rowid, new.oid, new._rowid_, new.x);
    END;
    CREATE TRIGGER r3 BEFORE UPDATE ON t1 BEGIN
      INSERT INTO log VALUES('r3.old', old.rowid, old.oid, old._rowid_, old.x);
      INSERT INTO log VALUES('r3.new', new.rowid, new.oid, new._rowid_, new.x);
    END;
    CREATE TRIGGER r4 AFTER UPDATE ON t1 BEGIN
      INSERT INTO log VALUES('r4.old', old.rowid, old.oid, old._rowid_, old.x);
      INSERT INTO log VALUES('r4.new', new.rowid, new.oid, new._rowid_, new.x);
    END;
    CREATE TRIGGER r5 BEFORE DELETE ON t1 BEGIN
      INSERT INTO log VALUES('r5', old.rowid, old.oid, old._rowid_, old.x);
    END;
    CREATE TRIGGER r6 AFTER DELETE ON t1 BEGIN
      INSERT INTO log VALUES('r6', old.rowid, old.oid, old._rowid_, old.x);
    END;
  }
} {}
do_test triggerD-2.2 {
  db eval {
    DELETE FROM log;
    INSERT INTO t1 VALUES(100,200,300,400);
    SELECT * FROM log;
  }
} {r1 -1 -1 -1 200 r2 1 1 1 200}
do_test triggerD-2.3 {
  db eval {
    DELETE FROM log;
    UPDATE t1 SET x=x+1;
    SELECT * FROM log
  }
} {r3.old 1 1 1 200 r3.new 1 1 1 201 r4.old 1 1 1 200 r4.new 1 1 1 201}
do_test triggerD-2.4 {
  db eval {
    DELETE FROM log;
    DELETE FROM t1;
    SELECT * FROM log
  }
} {r5 1 1 1 201 r6 1 1 1 201}

finish_test