/ Check-in [09f94c2c]
Login
Overview
Comment:Fix a problem involving "DELETE/UPDATE...LIMIT" statements that use an INDEXED BY clause.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | update-delete-limit-fix
Files: files | file ages | folders
SHA3-256:09f94c2c8199b0d23a45cc062ca9561f9e5ddfcba117100e41889ce199d21bdb
User & Date: dan 2017-11-10 16:14:26
Context
2017-11-10
17:47
Add further tests for the code on this branch. check-in: f8c4e33f user: dan tags: update-delete-limit-fix
16:14
Fix a problem involving "DELETE/UPDATE...LIMIT" statements that use an INDEXED BY clause. check-in: 09f94c2c 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: 35477a3d user: dan tags: update-delete-limit-fix
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/delete.c.

198
199
200
201
202
203
204

205
206
207
208
209
210
211
  }

  /* duplicate the FROM clause as it is needed by both the DELETE/UPDATE tree
  ** and the SELECT subtree. */
  pSrc->a[0].pTab = 0;
  pSelectSrc = sqlite3SrcListDup(pParse->db, pSrc, 0);
  pSrc->a[0].pTab = pTab;


  /* generate the SELECT expression tree. */
  pSelect = sqlite3SelectNew(pParse, pEList, pSelectSrc, pWhere, 0 ,0, 
      pOrderBy,0,pLimit,pOffset
  );

  /* now generate the new WHERE rowid IN clause for the DELETE/UDPATE */







>







198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
  }

  /* duplicate the FROM clause as it is needed by both the DELETE/UPDATE tree
  ** and the SELECT subtree. */
  pSrc->a[0].pTab = 0;
  pSelectSrc = sqlite3SrcListDup(pParse->db, pSrc, 0);
  pSrc->a[0].pTab = pTab;
  pSrc->a[0].pIBIndex = 0;

  /* generate the SELECT expression tree. */
  pSelect = sqlite3SelectNew(pParse, pEList, pSelectSrc, pWhere, 0 ,0, 
      pOrderBy,0,pLimit,pOffset
  );

  /* now generate the new WHERE rowid IN clause for the DELETE/UDPATE */

Changes to test/wherelimit2.test.

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
...
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
} {
  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');
  INSERT INTO t2 VALUES(3, 2, 'c');
  INSERT INTO t2 VALUES(4, 1, 'b');
  INSERT INTO t2 VALUES(4, 2, 'a');
}

do_execsql_test 2.1 {
  BEGIN;
    DELETE FROM t2 WHERE b=1 ORDER BY c LIMIT 2;
    SELECT c FROM t2 ORDER BY 1;
  ROLLBACK;
} {a c e f g h}

do_execsql_test 2.2 {
  BEGIN;
    UPDATE t2 SET c=NULL ORDER BY a, b DESC LIMIT 3 OFFSET 1;
    SELECT a, b, c FROM t2;
  ROLLBACK;
} {
  1 1 {} 
  1 2 g 
................................................................................
  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);
................................................................................
    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








|











|






|







 







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







 







|
<
|
<
|
|
|
|
|
|
|
|

|
|
|
|
|
|
|
>
>
>

>
>
>
>

>
>
>

>
>
>
>









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
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
...
196
197
198
199
200
201
202
203

204

205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
} {
  update 6 update 5 update 4
}

#-------------------------------------------------------------------------
# Simple test using WITHOUT ROWID table.
#
do_execsql_test 2.1.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');
  INSERT INTO t2 VALUES(3, 2, 'c');
  INSERT INTO t2 VALUES(4, 1, 'b');
  INSERT INTO t2 VALUES(4, 2, 'a');
}

do_execsql_test 2.1.1 {
  BEGIN;
    DELETE FROM t2 WHERE b=1 ORDER BY c LIMIT 2;
    SELECT c FROM t2 ORDER BY 1;
  ROLLBACK;
} {a c e f g h}

do_execsql_test 2.1.2 {
  BEGIN;
    UPDATE t2 SET c=NULL ORDER BY a, b DESC LIMIT 3 OFFSET 1;
    SELECT a, b, c FROM t2;
  ROLLBACK;
} {
  1 1 {} 
  1 2 g 
................................................................................
  2 1 {} 
  2 2 {} 
  3 1 d 
  3 2 c 
  4 1 b 
  4 2 a
}

do_execsql_test 2.2.0 {
  DROP TABLE t2;
  CREATE TABLE t2(a INTEGER PRIMARY KEY, b, c) WITHOUT ROWID;
  INSERT INTO t2 VALUES(1, 1, 'h');
  INSERT INTO t2 VALUES(2, 2, 'g');
  INSERT INTO t2 VALUES(3, 1, 'f');
  INSERT INTO t2 VALUES(4, 2, 'e');
  INSERT INTO t2 VALUES(5, 1, 'd');
  INSERT INTO t2 VALUES(6, 2, 'c');
  INSERT INTO t2 VALUES(7, 1, 'b');
  INSERT INTO t2 VALUES(8, 2, 'a');
}

do_execsql_test 2.2.1 {
  BEGIN;
    DELETE FROM t2 WHERE b=1 ORDER BY c LIMIT 2;
    SELECT c FROM t2 ORDER BY 1;
  ROLLBACK;
} {a c e f g h}

do_execsql_test 2.2.2 {
  BEGIN;
    UPDATE t2 SET c=NULL ORDER BY a DESC LIMIT 3 OFFSET 1;
    SELECT a, b, c FROM t2;
  ROLLBACK;
} {
  1 1 h
  2 2 g 
  3 1 f
  4 2 e
  5 1 {}
  6 2 {} 
  7 1 {} 
  8 2 a
}

#-------------------------------------------------------------------------
# Test using a virtual table
#
ifcapable fts5 {
  do_execsql_test 3.0 {
    CREATE VIRTUAL TABLE ft USING fts5(x);
................................................................................
    ROLLBACK;
  } {{a a} {a b} {a c} hello hello {a b} {a a}}
} ;# fts5

#-------------------------------------------------------------------------
# Test using INDEXED BY clauses.
#
do_execsql_test 4.0 {

  CREATE TABLE x1(a INTEGER PRIMARY KEY, b, c, d);

  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.1 {
  BEGIN;
    DELETE FROM x1 ORDER BY a LIMIT 2;
    SELECT a FROM x1;
  ROLLBACK;
} {3 4 5 6}

do_catchsql_test 4.2 {
  DELETE FROM x1 INDEXED BY x1bc WHERE d=3 LIMIT 1;
} {1 {no query solution}}

do_execsql_test 4.3 {
  DELETE FROM x1 INDEXED BY x1bc WHERE b=3 LIMIT 1;
  SELECT a FROM x1;
} {1 2 3 4 6}

do_catchsql_test 4.4 {
  UPDATE x1 INDEXED BY x1bc SET d=5 WHERE d=3 LIMIT 1;
} {1 {no query solution}}

do_execsql_test 4.5 {
  UPDATE x1 INDEXED BY x1bc SET d=5 WHERE b=2 LIMIT 1;
  SELECT a, d FROM x1;
} {1 1 2 2 3 5 4 3 6 1}

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



finish_test