SQLite

Check-in [3ebb2351e2]
Login

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

Overview
Comment:Fix an issue in incremental_index_check with indexes that use non-default collation sequences.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | checkindex
Files: files | file ages | folders
SHA3-256: 3ebb2351e2650d263029d2c0042683cba3529c9d3f76b5f994f2e737b84d3f67
User & Date: dan 2017-10-30 08:04:38.448
Context
2017-10-30
17:05
In checkindex.c, use C code instead of SQL/group_concat() to compose various SQL clauses. This is to make it easier to support indexes on expressions. (check-in: 940606b3af user: dan tags: checkindex)
08:04
Fix an issue in incremental_index_check with indexes that use non-default collation sequences. (check-in: 3ebb2351e2 user: dan tags: checkindex)
2017-10-28
20:31
Fix "after" parameter handling in the incremental_index_check code. (check-in: c40c3c62e9 user: dan tags: checkindex)
Changes
Unified Diff Ignore Whitespace Patch
Changes to ext/repair/checkindex.c.
︙︙
294
295
296
297
298
299
300
301

302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323

  }

  pGroup = cidxPrepare(&rc, pCsr,
      "SELECT group_concat("
      "  coalesce('quote(' || name || ')', 'rowid'), '|| '','' ||'"
      ") AS zCurrentKey,"
      "       group_concat("
      "  coalesce(name, 'rowid') || CASE WHEN desc THEN ' DESC' ELSE '' END,"

      "  ', '"
      ") AS zOrderBy,"
      "       group_concat("
      "         CASE WHEN key==1 THEN NULL ELSE "
      "  coalesce(name, 'rowid') || ' IS \"%w\".' || coalesce(name, 'rowid') "
      "         END,"
      "  ' AND '"
      ") AS zSubWhere,"
      "       group_concat("
      "         CASE WHEN key==0 THEN NULL ELSE "
      "  coalesce(name, 'rowid') || ' IS \"%w\".' || coalesce(name, 'rowid') "
      "         END,"
      "  ' AND '"
      ") AS zSubExpr,"
      "       count(*) AS nCol"
      " FROM pragma_index_xinfo(%Q);"
      , zIdx, zIdx, zIdx
  );
  if( rc==SQLITE_OK && sqlite3_step(pGroup)==SQLITE_ROW ){
    zCurrentKey = cidxStrdup(&rc, (const char*)sqlite3_column_text(pGroup, 0));
    zOrderBy = cidxStrdup(&rc, (const char*)sqlite3_column_text(pGroup, 1));
    zSubWhere = cidxStrdup(&rc, (const char*)sqlite3_column_text(pGroup, 2));








|
>














|








294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324

  }

  pGroup = cidxPrepare(&rc, pCsr,
      "SELECT group_concat("
      "  coalesce('quote(' || name || ')', 'rowid'), '|| '','' ||'"
      ") AS zCurrentKey,"
      "       group_concat("
      "  coalesce(name, 'rowid') || ' COLLATE ' || coll "
      "  || CASE WHEN desc THEN ' DESC' ELSE '' END,"
      "  ', '"
      ") AS zOrderBy,"
      "       group_concat("
      "         CASE WHEN key==1 THEN NULL ELSE "
      "  coalesce(name, 'rowid') || ' IS \"%w\".' || coalesce(name, 'rowid') "
      "         END,"
      "  ' AND '"
      ") AS zSubWhere,"
      "       group_concat("
      "         CASE WHEN key==0 THEN NULL ELSE "
      "  coalesce(name, 'rowid') || ' IS \"%w\".' || coalesce(name, 'rowid') "
      "         END,"
      "  ' AND '"
      ") AS zSubExpr,"
      "      count(*) AS nCol"
      " FROM pragma_index_xinfo(%Q);"
      , zIdx, zIdx, zIdx
  );
  if( rc==SQLITE_OK && sqlite3_step(pGroup)==SQLITE_ROW ){
    zCurrentKey = cidxStrdup(&rc, (const char*)sqlite3_column_text(pGroup, 0));
    zOrderBy = cidxStrdup(&rc, (const char*)sqlite3_column_text(pGroup, 1));
    zSubWhere = cidxStrdup(&rc, (const char*)sqlite3_column_text(pGroup, 2));
︙︙
Changes to test/checkindex.test.
︙︙
181
182
183
184
185
186
187

188
189
190
191
192
193
194

  {} 1,NULL,1,2 
  {} 2,2,2,5 
  {} 2,2,2,8 
  {} 3,2,2,6 
  {} 3,2,2,9
}


do_execsql_test 3.0 {

  CREATE TABLE t3(w, x, y, z PRIMARY KEY) WITHOUT ROWID;
  CREATE INDEX t3wxy ON t3(w, x, y);
  CREATE INDEX t3wxy2 ON t3(w DESC, x DESC, y DESC);

  INSERT INTO t3 VALUES(NULL, NULL, NULL, 1);








>








181
182
183
184
185
186
187
188
189
190
191
192
193
194
195

  {} 1,NULL,1,2 
  {} 2,2,2,5 
  {} 2,2,2,8 
  {} 3,2,2,6 
  {} 3,2,2,9
}

#--------------------------------------------------------------------------
do_execsql_test 3.0 {

  CREATE TABLE t3(w, x, y, z PRIMARY KEY) WITHOUT ROWID;
  CREATE INDEX t3wxy ON t3(w, x, y);
  CREATE INDEX t3wxy2 ON t3(w DESC, x DESC, y DESC);

  INSERT INTO t3 VALUES(NULL, NULL, NULL, 1);
︙︙
211
212
213
214
215
216
217
218











































219
220

  {} 'a','b',NULL,7   {} 'a','b',NULL,8   {} 'a','b',NULL,9 
}
do_index_check_test 3.2 t3wxy2 {
  {} 'a','b',NULL,7   {} 'a','b',NULL,8   {} 'a','b',NULL,9 
  {} 'a',NULL,NULL,4  {} 'a',NULL,NULL,5  {} 'a',NULL,NULL,6 
  {} NULL,NULL,NULL,1 {} NULL,NULL,NULL,2 {} NULL,NULL,NULL,3 
}












































finish_test










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



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
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264

  {} 'a','b',NULL,7   {} 'a','b',NULL,8   {} 'a','b',NULL,9 
}
do_index_check_test 3.2 t3wxy2 {
  {} 'a','b',NULL,7   {} 'a','b',NULL,8   {} 'a','b',NULL,9 
  {} 'a',NULL,NULL,4  {} 'a',NULL,NULL,5  {} 'a',NULL,NULL,6 
  {} NULL,NULL,NULL,1 {} NULL,NULL,NULL,2 {} NULL,NULL,NULL,3 
}

#--------------------------------------------------------------------------
# Test with an index that uses non-default collation sequences.
#
do_execsql_test 4.0 {
  CREATE TABLE t4(a INTEGER PRIMARY KEY, c1 TEXT, c2 TEXT);
  INSERT INTO t4 VALUES(1, 'aaa', 'bbb');
  INSERT INTO t4 VALUES(2, 'AAA', 'CCC');
  INSERT INTO t4 VALUES(3, 'aab', 'ddd');
  INSERT INTO t4 VALUES(4, 'AAB', 'EEE');

  CREATE INDEX t4cc ON t4(c1 COLLATE nocase, c2 COLLATE nocase);
}

do_index_check_test 4.1 t4cc {
  {} 'aaa','bbb',1 
  {} 'AAA','CCC',2 
  {} 'aab','ddd',3 
  {} 'AAB','EEE',4
}

do_test 4.2 {
  set tblroot [db one { SELECT rootpage FROM sqlite_master WHERE name='t4' }]
  sqlite3_test_control SQLITE_TESTCTRL_IMPOSTER db main 1 $tblroot
  db eval {CREATE TABLE xt4(a INTEGER PRIMARY KEY, c1 TEXT, c2 TEXT)}
  sqlite3_test_control SQLITE_TESTCTRL_IMPOSTER db main 0 0

  execsql {
    UPDATE xt4 SET c1='hello' WHERE rowid=2;
    DELETE FROM xt4 WHERE rowid = 3;
  }

  sqlite3_test_control SQLITE_TESTCTRL_IMPOSTER db main 0 1
} {}

do_index_check_test 4.3 t4cc {
  {} 'aaa','bbb',1 
  {row data mismatch} 'AAA','CCC',2 
  {row missing} 'aab','ddd',3 
  {} 'AAB','EEE',4
}



finish_test