SQLite

Check-in [35477a3dcc]
Login

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

Overview
Comment:Fix a problem with (DELETE...LIMIT) statements against WITHOUT ROWID tables with a single column PK.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | update-delete-limit-fix
Files: files | file ages | folders
SHA3-256: 35477a3dcceadf5dade8e036d5a2ce91b9ca83c4b85d309db233bdbcf538b1cc
User & Date: dan 2017-11-10 15:42:21.918
Context
2017-11-10
16:14
Fix a problem involving "DELETE/UPDATE...LIMIT" statements that use an INDEXED BY clause. (check-in: 09f94c2c81 user: dan tags: update-delete-limit-fix)
15:42
Fix a problem with (DELETE...LIMIT) statements against WITHOUT ROWID tables with a single column PK. (check-in: 35477a3dcc user: dan tags: update-delete-limit-fix)
2017-11-09
19:53
Add SQLITE_ENABLE_UPDATE_DELETE_LIMIT for views and WITHOUT ROWID tables. (check-in: 584b88aaf8 user: dan tags: update-delete-limit-fix)
Changes
Side-by-Side Diff Ignore Whitespace Patch
Changes to src/delete.c.
︙
177
178
179
180
181
182
183

184


185
186
187
188
189
190
191

177
178
179
180
181
182
183
184

185
186
187
188
189
190
191
192
193








+
-
+
+








    pLhs = sqlite3PExpr(pParse, TK_ROW, 0, 0);
    pEList = sqlite3ExprListAppend(
        pParse, 0, sqlite3PExpr(pParse, TK_ROW, 0, 0)
    );
  }else{
    Index *pPk = sqlite3PrimaryKeyIndex(pTab);
    if( pPk->nKeyCol==1 ){
      const char *zName = pTab->aCol[pPk->aiColumn[0]].zName;
      pLhs = sqlite3Expr(db, TK_ID, pTab->aCol[pPk->aiColumn[0]].zName);
      pLhs = sqlite3Expr(db, TK_ID, zName);
      pEList = sqlite3ExprListAppend(pParse, 0, sqlite3Expr(db, TK_ID, zName));
    }else{
      int i;
      for(i=0; i<pPk->nKeyCol; i++){
        Expr *p = sqlite3Expr(db, TK_ID, pTab->aCol[pPk->aiColumn[i]].zName);
        pEList = sqlite3ExprListAppend(pParse, pEList, p);
      }
      pLhs = sqlite3PExpr(pParse, TK_VECTOR, 0, 0);
︙
Changes to src/parse.y.
︙
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790

774
775
776
777
778
779
780



781
782
783
784
785
786
787








-
-
-








//
%ifdef SQLITE_ENABLE_UPDATE_DELETE_LIMIT
cmd ::= with(C) UPDATE orconf(R) fullname(X) indexed_opt(I) SET setlist(Y)
        where_opt(W) orderby_opt(O) limit_opt(L).  {
  sqlite3WithPush(pParse, C, 1);
  sqlite3SrcListIndexedBy(pParse, X, &I);
  sqlite3ExprListCheckLength(pParse,Y,"set list"); 
#if 0
  W = sqlite3LimitWhere(pParse, X, W, O, L.pLimit, L.pOffset, "UPDATE");
#endif
  sqlite3UpdateLimit(pParse,X,Y,W,R,O,L.pLimit,L.pOffset);
}
%endif
%ifndef SQLITE_ENABLE_UPDATE_DELETE_LIMIT
cmd ::= with(C) UPDATE orconf(R) fullname(X) indexed_opt(I) SET setlist(Y)
        where_opt(W).  {
  sqlite3WithPush(pParse, C, 1);
︙
Changes to test/wherelimit2.test.
︙
18
19
20
21
22
23
24



25
26
27
28
29
30
31

18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34








+
+
+








set testprefix wherelimit2

ifcapable !update_delete_limit {
  finish_test
  return
}

#-------------------------------------------------------------------------
# Test with views and INSTEAD OF triggers.
#
do_execsql_test 1.0 {
  CREATE TABLE t1(a, b);
  INSERT INTO t1 VALUES(1, 'f');
  INSERT INTO t1 VALUES(2, 'e');
  INSERT INTO t1 VALUES(3, 'd');
  INSERT INTO t1 VALUES(4, 'c');
  INSERT INTO t1 VALUES(5, 'b');
︙
64
65
66
67
68
69
70



71
72
73
74
75
76
77

67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83








+
+
+








do_execsql_test 1.4 {
  UPDATE v1 SET b = 555 ORDER BY b LIMIT 3;
  SELECT * FROM log; DELETE FROM log;
} {
  update 6 update 5 update 4
}

#-------------------------------------------------------------------------
# Simple test using WITHOUT ROWID table.
#
do_execsql_test 2.0 {
  CREATE TABLE t2(a, b, c, PRIMARY KEY(a, b)) WITHOUT ROWID;
  INSERT INTO t2 VALUES(1, 1, 'h');
  INSERT INTO t2 VALUES(1, 2, 'g');
  INSERT INTO t2 VALUES(2, 1, 'f');
  INSERT INTO t2 VALUES(2, 2, 'e');
  INSERT INTO t2 VALUES(3, 1, 'd');
︙
98
99
100
101
102
103
104
105


106
107



















































































108
109

104
105
106
107
108
109
110
111
112
113


114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198









+
+
-
-
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+



  2 1 {} 
  2 2 {} 
  3 1 d 
  3 2 c 
  4 1 b 
  4 2 a
}

#-------------------------------------------------------------------------
# Test using a virtual table


#
ifcapable fts5 {
  do_execsql_test 3.0 {
    CREATE VIRTUAL TABLE ft USING fts5(x);
    INSERT INTO ft(rowid, x) VALUES(-45,   'a a');
    INSERT INTO ft(rowid, x) VALUES(12,    'a b');
    INSERT INTO ft(rowid, x) VALUES(444,   'a c');
    INSERT INTO ft(rowid, x) VALUES(12300, 'a d');
    INSERT INTO ft(rowid, x) VALUES(25400, 'a c');
    INSERT INTO ft(rowid, x) VALUES(25401, 'a b');
    INSERT INTO ft(rowid, x) VALUES(50000, 'a a');
  }

  do_execsql_test 3.1.1 {
    BEGIN;
      DELETE FROM ft ORDER BY rowid LIMIT 3;
      SELECT x FROM ft;
    ROLLBACK;
  } {{a d} {a c} {a b} {a a}}

  do_execsql_test 3.1.2 {
    BEGIN;
      DELETE FROM ft WHERE ft MATCH 'a' ORDER BY rowid LIMIT 3;
      SELECT x FROM ft;
    ROLLBACK;
  } {{a d} {a c} {a b} {a a}}
  
  do_execsql_test 3.1.3 {
    BEGIN;
      DELETE FROM ft WHERE ft MATCH 'b' ORDER BY rowid ASC LIMIT 1 OFFSET 1;
      SELECT rowid FROM ft;
    ROLLBACK;
  } {-45 12 444 12300 25400 50000}

  do_execsql_test 3.2.1 {
    BEGIN;
      UPDATE ft SET x='hello' ORDER BY rowid LIMIT 2 OFFSET 2;
      SELECT x FROM ft;
    ROLLBACK;
  } {{a a} {a b} hello hello {a c} {a b} {a a}}

  do_execsql_test 3.2.2 {
    BEGIN;
      UPDATE ft SET x='hello' WHERE ft MATCH 'a' 
          ORDER BY rowid DESC LIMIT 2 OFFSET 2;
      SELECT x FROM ft;
    ROLLBACK;
  } {{a a} {a b} {a c} hello hello {a b} {a a}}
} ;# fts5

#-------------------------------------------------------------------------
# Test using INDEXED BY clauses.
#
foreach {tn t} {1 "" 2 "WITHOUT ROWID"} {
  execsql "DROP TABLE IF EXISTS x1"
  execsql "CREATE TABLE x1(a INTEGER PRIMARY KEY, b, c, d) $t"
  do_execsql_test 4.$tn.0 {
    CREATE INDEX x1bc ON x1(b, c);
    INSERT INTO x1 VALUES(1,1,1,1);
    INSERT INTO x1 VALUES(2,1,2,2);
    INSERT INTO x1 VALUES(3,2,1,3);
    INSERT INTO x1 VALUES(4,2,2,3);
    INSERT INTO x1 VALUES(5,3,1,2);
    INSERT INTO x1 VALUES(6,3,2,1);
  }

  do_execsql_test 4.$tn.1 {
    BEGIN;
      DELETE FROM x1 ORDER BY a LIMIT 2;
      SELECT a FROM x1;
    ROLLBACK;
  } {3 4 5 6}
}




#-------------------------------------------------------------------------
# Test using object names that require quoting.
#



finish_test