/ Check-in [3ebb2351]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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 | SQL archive
Timelines: family | ancestors | descendants | both | checkindex
Files: files | file ages | folders
SHA3-256: 3ebb2351e2650d263029d2c0042683cba3529c9d3f76b5f994f2e737b84d3f67
User & Date: dan 2017-10-30 08:04:38
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: 940606b3 user: dan tags: checkindex
08:04
Fix an issue in incremental_index_check with indexes that use non-default collation sequences. check-in: 3ebb2351 user: dan tags: checkindex
2017-10-28
20:31
Fix "after" parameter handling in the incremental_index_check code. check-in: c40c3c62 user: dan tags: checkindex
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to ext/repair/checkindex.c.

   294    294     }
   295    295   
   296    296     pGroup = cidxPrepare(&rc, pCsr,
   297    297         "SELECT group_concat("
   298    298         "  coalesce('quote(' || name || ')', 'rowid'), '|| '','' ||'"
   299    299         ") AS zCurrentKey,"
   300    300         "       group_concat("
   301         -      "  coalesce(name, 'rowid') || CASE WHEN desc THEN ' DESC' ELSE '' END,"
          301  +      "  coalesce(name, 'rowid') || ' COLLATE ' || coll "
          302  +      "  || CASE WHEN desc THEN ' DESC' ELSE '' END,"
   302    303         "  ', '"
   303    304         ") AS zOrderBy,"
   304    305         "       group_concat("
   305    306         "         CASE WHEN key==1 THEN NULL ELSE "
   306    307         "  coalesce(name, 'rowid') || ' IS \"%w\".' || coalesce(name, 'rowid') "
   307    308         "         END,"
   308    309         "  ' AND '"
................................................................................
   309    310         ") AS zSubWhere,"
   310    311         "       group_concat("
   311    312         "         CASE WHEN key==0 THEN NULL ELSE "
   312    313         "  coalesce(name, 'rowid') || ' IS \"%w\".' || coalesce(name, 'rowid') "
   313    314         "         END,"
   314    315         "  ' AND '"
   315    316         ") AS zSubExpr,"
   316         -      "       count(*) AS nCol"
          317  +      "      count(*) AS nCol"
   317    318         " FROM pragma_index_xinfo(%Q);"
   318    319         , zIdx, zIdx, zIdx
   319    320     );
   320    321     if( rc==SQLITE_OK && sqlite3_step(pGroup)==SQLITE_ROW ){
   321    322       zCurrentKey = cidxStrdup(&rc, (const char*)sqlite3_column_text(pGroup, 0));
   322    323       zOrderBy = cidxStrdup(&rc, (const char*)sqlite3_column_text(pGroup, 1));
   323    324       zSubWhere = cidxStrdup(&rc, (const char*)sqlite3_column_text(pGroup, 2));

Changes to test/checkindex.test.

   181    181     {} 1,NULL,1,2 
   182    182     {} 2,2,2,5 
   183    183     {} 2,2,2,8 
   184    184     {} 3,2,2,6 
   185    185     {} 3,2,2,9
   186    186   }
   187    187   
          188  +#--------------------------------------------------------------------------
   188    189   do_execsql_test 3.0 {
   189    190   
   190    191     CREATE TABLE t3(w, x, y, z PRIMARY KEY) WITHOUT ROWID;
   191    192     CREATE INDEX t3wxy ON t3(w, x, y);
   192    193     CREATE INDEX t3wxy2 ON t3(w DESC, x DESC, y DESC);
   193    194   
   194    195     INSERT INTO t3 VALUES(NULL, NULL, NULL, 1);
................................................................................
   211    212     {} 'a','b',NULL,7   {} 'a','b',NULL,8   {} 'a','b',NULL,9 
   212    213   }
   213    214   do_index_check_test 3.2 t3wxy2 {
   214    215     {} 'a','b',NULL,7   {} 'a','b',NULL,8   {} 'a','b',NULL,9 
   215    216     {} 'a',NULL,NULL,4  {} 'a',NULL,NULL,5  {} 'a',NULL,NULL,6 
   216    217     {} NULL,NULL,NULL,1 {} NULL,NULL,NULL,2 {} NULL,NULL,NULL,3 
   217    218   }
          219  +
          220  +#--------------------------------------------------------------------------
          221  +# Test with an index that uses non-default collation sequences.
          222  +#
          223  +do_execsql_test 4.0 {
          224  +  CREATE TABLE t4(a INTEGER PRIMARY KEY, c1 TEXT, c2 TEXT);
          225  +  INSERT INTO t4 VALUES(1, 'aaa', 'bbb');
          226  +  INSERT INTO t4 VALUES(2, 'AAA', 'CCC');
          227  +  INSERT INTO t4 VALUES(3, 'aab', 'ddd');
          228  +  INSERT INTO t4 VALUES(4, 'AAB', 'EEE');
          229  +
          230  +  CREATE INDEX t4cc ON t4(c1 COLLATE nocase, c2 COLLATE nocase);
          231  +}
          232  +
          233  +do_index_check_test 4.1 t4cc {
          234  +  {} 'aaa','bbb',1 
          235  +  {} 'AAA','CCC',2 
          236  +  {} 'aab','ddd',3 
          237  +  {} 'AAB','EEE',4
          238  +}
          239  +
          240  +do_test 4.2 {
          241  +  set tblroot [db one { SELECT rootpage FROM sqlite_master WHERE name='t4' }]
          242  +  sqlite3_test_control SQLITE_TESTCTRL_IMPOSTER db main 1 $tblroot
          243  +  db eval {CREATE TABLE xt4(a INTEGER PRIMARY KEY, c1 TEXT, c2 TEXT)}
          244  +  sqlite3_test_control SQLITE_TESTCTRL_IMPOSTER db main 0 0
          245  +
          246  +  execsql {
          247  +    UPDATE xt4 SET c1='hello' WHERE rowid=2;
          248  +    DELETE FROM xt4 WHERE rowid = 3;
          249  +  }
          250  +
          251  +  sqlite3_test_control SQLITE_TESTCTRL_IMPOSTER db main 0 1
          252  +} {}
          253  +
          254  +do_index_check_test 4.3 t4cc {
          255  +  {} 'aaa','bbb',1 
          256  +  {row data mismatch} 'AAA','CCC',2 
          257  +  {row missing} 'aab','ddd',3 
          258  +  {} 'AAB','EEE',4
          259  +}
          260  +
          261  +
   218    262   
   219    263   finish_test
   220    264