From 19465e7380f725f9bada9c338e55d7e95bf46ef3 Mon Sep 17 00:00:00 2001 From: dan Date: Sat, 28 Oct 2017 20:31:25 +0000 Subject: [PATCH] Fix "after" parameter handling in the incremental_index_check code. FossilOrigin-Name: c40c3c62e996044f31ca49ffc2edb2cc0320e69956f7ee6fe3e9012200e0d9a0 --- ext/repair/checkindex.c | 238 ++++++++++++++++++++++++++++++++++++++-- manifest | 17 ++- manifest.uuid | 2 +- test/checkindex.test | 175 +++++++++++++++++++++++++---- 4 files changed, 389 insertions(+), 43 deletions(-) diff --git a/ext/repair/checkindex.c b/ext/repair/checkindex.c index a74d1d4b75..f86cfdae7a 100644 --- a/ext/repair/checkindex.c +++ b/ext/repair/checkindex.c @@ -45,6 +45,13 @@ struct CidxCursor { sqlite3_stmt *pStmt; }; +typedef struct CidxColumn CidxColumn; +struct CidxColumn { + char *zName; + char *zColl; + int bDesc; +}; + static void *cidxMalloc(int *pRc, int n){ void *pRet = 0; assert( n!=0 ); @@ -193,6 +200,23 @@ static int cidxEof(sqlite3_vtab_cursor *pCursor){ return pCsr->pStmt==0; } +static char *cidxMprintf(int *pRc, const char *zFmt, ...){ + char *zRet = 0; + va_list ap; + va_start(ap, zFmt); + zRet = sqlite3_vmprintf(zFmt, ap); + if( *pRc==SQLITE_OK ){ + if( zRet==0 ){ + *pRc = SQLITE_NOMEM; + } + }else{ + sqlite3_free(zRet); + zRet = 0; + } + va_end(ap); + return zRet; +} + static sqlite3_stmt *cidxPrepare( int *pRc, CidxCursor *pCsr, const char *zFmt, ... ){ @@ -237,6 +261,8 @@ char *cidxStrdup(int *pRc, const char *zStr){ static int cidxLookupIndex( CidxCursor *pCsr, /* Cursor object */ const char *zIdx, /* Name of index to look up */ + int *pnCol, /* OUT: Number of columns in index */ + CidxColumn **paCol, /* OUT: Columns */ char **pzTab, /* OUT: Table name */ char **pzCurrentKey, /* OUT: Expression for current_key */ char **pzOrderBy, /* OUT: ORDER BY expression list */ @@ -249,6 +275,7 @@ static int cidxLookupIndex( char *zOrderBy = 0; char *zSubWhere = 0; char *zSubExpr = 0; + CidxColumn *aCol = 0; sqlite3_stmt *pFindTab = 0; sqlite3_stmt *pGroup = 0; @@ -268,7 +295,7 @@ static int cidxLookupIndex( pGroup = cidxPrepare(&rc, pCsr, "SELECT group_concat(" - " coalesce(name, 'rowid'), '|| '','' ||'" + " coalesce('quote(' || name || ')', 'rowid'), '|| '','' ||'" ") AS zCurrentKey," " group_concat(" " coalesce(name, 'rowid') || CASE WHEN desc THEN ' DESC' ELSE '' END," @@ -278,14 +305,15 @@ static int cidxLookupIndex( " CASE WHEN key==1 THEN NULL ELSE " " coalesce(name, 'rowid') || ' IS \"%w\".' || coalesce(name, 'rowid') " " END," - " 'AND '" + " ' AND '" ") AS zSubWhere," " group_concat(" " CASE WHEN key==0 THEN NULL ELSE " " coalesce(name, 'rowid') || ' IS \"%w\".' || coalesce(name, 'rowid') " " END," - " 'AND '" - ") AS zSubExpr " + " ' AND '" + ") AS zSubExpr," + " count(*) AS nCol" " FROM pragma_index_xinfo(%Q);" , zIdx, zIdx, zIdx ); @@ -294,8 +322,49 @@ static int cidxLookupIndex( zOrderBy = cidxStrdup(&rc, (const char*)sqlite3_column_text(pGroup, 1)); zSubWhere = cidxStrdup(&rc, (const char*)sqlite3_column_text(pGroup, 2)); zSubExpr = cidxStrdup(&rc, (const char*)sqlite3_column_text(pGroup, 3)); + *pnCol = sqlite3_column_int(pGroup, 4); } cidxFinalize(&rc, pGroup); + + pGroup = cidxPrepare(&rc, pCsr, "PRAGMA index_xinfo(%Q)", zIdx); + if( rc==SQLITE_OK ){ + int nByte = 0; + int nCol = 0; + while( sqlite3_step(pGroup)==SQLITE_ROW ){ + const char *zName = (const char*)sqlite3_column_text(pGroup, 2); + const char *zColl = (const char*)sqlite3_column_text(pGroup, 4); + if( zName==0 ) zName = "rowid"; + nCol++; + nByte += strlen(zName)+1 + strlen(zColl)+1; + } + rc = sqlite3_reset(pGroup); + aCol = (CidxColumn*)cidxMalloc(&rc, sizeof(CidxColumn)*nCol + nByte); + + if( rc==SQLITE_OK ){ + int iCol = 0; + char *z = (char*)&aCol[nCol]; + while( sqlite3_step(pGroup)==SQLITE_ROW ){ + int nName, nColl; + const char *zName = (const char*)sqlite3_column_text(pGroup, 2); + const char *zColl = (const char*)sqlite3_column_text(pGroup, 4); + if( zName==0 ) zName = "rowid"; + + nName = strlen(zName); + nColl = strlen(zColl); + memcpy(z, zName, nName); + aCol[iCol].zName = z; + z += nName+1; + + memcpy(z, zColl, nColl); + aCol[iCol].zColl = z; + z += nColl+1; + + aCol[iCol].bDesc = sqlite3_column_int(pGroup, 3); + iCol++; + } + } + cidxFinalize(&rc, pGroup); + } if( rc!=SQLITE_OK ){ sqlite3_free(zTab); @@ -303,15 +372,126 @@ static int cidxLookupIndex( sqlite3_free(zOrderBy); sqlite3_free(zSubWhere); sqlite3_free(zSubExpr); + sqlite3_free(aCol); }else{ *pzTab = zTab; *pzCurrentKey = zCurrentKey; *pzOrderBy = zOrderBy; *pzSubWhere = zSubWhere; *pzSubExpr = zSubExpr; + *paCol = aCol; + } + + return rc; +} + +static int cidxDecodeAfter( + CidxCursor *pCsr, + int nCol, + const char *zAfterKey, + char ***pazAfter +){ + char **azAfter; + int rc = SQLITE_OK; + int nAfterKey = strlen(zAfterKey); + + azAfter = cidxMalloc(&rc, sizeof(char*)*nCol + nAfterKey+1); + if( rc==SQLITE_OK ){ + int i; + char *zCopy = (char*)&azAfter[nCol]; + char *p = zCopy; + memcpy(zCopy, zAfterKey, nAfterKey+1); + for(i=0; i='0' && *p<='9') + || *p=='.' || *p=='+' || *p=='-' || *p=='e' || *p=='E' + ){ + p++; + } + } + + while( *p==' ' ) p++; + if( *p!=(i==(nCol-1) ? '\0' : ',') ){ + goto parse_error; + } + *p++ = '\0'; + } } + *pazAfter = azAfter; return rc; + + parse_error: + sqlite3_free(azAfter); + *pazAfter = 0; + cidxCursorError(pCsr, "%s", "error parsing after value"); + return SQLITE_ERROR; +} + +static char *cidxWhere( + int *pRc, CidxColumn *aCol, char **azAfter, int iGt, int bLastIsNull +){ + char *zRet = 0; + const char *zSep = ""; + int i; + + for(i=0; i"), + azAfter[iGt] + ); + }else{ + zRet = cidxMprintf(pRc, "%z%s%s IS NOT NULL", zRet, zSep, aCol[iGt].zName); + } + + return zRet; +} + +static char *cidxColumnList(int *pRc, CidxColumn *aCol, int nCol){ + int i; + char *zRet = 0; + const char *zSep = ""; + for(i=0; ipStmt = cidxPrepare(&rc, pCsr, - "SELECT (SELECT %s FROM %Q WHERE %s), %s FROM %Q AS %Q ORDER BY %s", - zSubExpr, zTab, zSubWhere, zCurrentKey, zTab, zIdxName, zOrderBy + &nCol, &aCol, &zTab, &zCurrentKey, &zOrderBy, &zSubWhere, &zSubExpr ); + if( rc==SQLITE_OK && zAfterKey ){ + rc = cidxDecodeAfter(pCsr, nCol, zAfterKey, &azAfter); + } + + if( rc || zAfterKey==0 ){ + pCsr->pStmt = cidxPrepare(&rc, pCsr, + "SELECT (SELECT %s FROM %Q WHERE %s), %s FROM %Q AS %Q ORDER BY %s", + zSubExpr, zTab, zSubWhere, zCurrentKey, zTab, zIdxName, zOrderBy + ); + /* printf("SQL: %s\n", sqlite3_sql(pCsr->pStmt)); */ + }else{ + char *zList = cidxColumnList(&rc, aCol, nCol); + const char *zSep = ""; + char *zSql; + int i; + + zSql = cidxMprintf(&rc, "SELECT (SELECT %s FROM %Q WHERE %s), %s FROM (", + zSubExpr, zTab, zSubWhere, zCurrentKey + ); + for(i=nCol-1; i>=0; i--){ + int j; + if( aCol[i].bDesc && azAfter[i]==0 ) continue; + for(j=0; j<2; j++){ + char *zWhere = cidxWhere(&rc, aCol, azAfter, i, j); + zSql = cidxMprintf(&rc, + "%z%s SELECT * FROM (SELECT %s FROM %Q WHERE %z ORDER BY %s)", + zSql, zSep, zList, zTab, zWhere, zOrderBy + ); + zSep = " UNION ALL "; + if( aCol[i].bDesc==0 ) break; + } + } + zSql = cidxMprintf(&rc, "%z) AS %Q", zSql, zIdxName); + sqlite3_free(zList); + + /* printf("SQL: %s\n", zSql); */ + pCsr->pStmt = cidxPrepare(&rc, pCsr, "%z", zSql); + } + sqlite3_free(zTab); sqlite3_free(zCurrentKey); sqlite3_free(zOrderBy); sqlite3_free(zSubWhere); sqlite3_free(zSubExpr); + sqlite3_free(aCol); + sqlite3_free(azAfter); } if( pCsr->pStmt ){ diff --git a/manifest b/manifest index e0740cdd7b..8f56822a50 100644 --- a/manifest +++ b/manifest @@ -1,5 +1,5 @@ -C Add\sthe\sstart\sof\sthe\s"incremental_index_check"\svirtual\stable\sin\next/repair/checkindex.c.\sFor\sincremental\sverification\sof\sindex\scontents. -D 2017-10-27T20:53:16.451 +C Fix\s"after"\sparameter\shandling\sin\sthe\sincremental_index_check\scode. +D 2017-10-28T20:31:25.564 F Makefile.in e016061b23e60ac9ec27c65cb577292b6bde0307ca55abd874ab3487b3b1beb2 F Makefile.linux-gcc 7bc79876b875010e8c8f9502eb935ca92aa3c434 F Makefile.msc 37740aba9c4bb359c627eadccf1cfd7be4f5f847078723777ea7763969e533b1 @@ -328,7 +328,7 @@ F ext/rbu/sqlite3rbu.h b42bcd4d8357268c6c39ab2a60b29c091e89328fa8cc49c8fac5ab8d0 F ext/rbu/test_rbu.c 7073979b9cc80912bb03599ac8d85ab5d3bf03cfacd3463f2dcdd7822997533a F ext/repair/README.md 92f5e8aae749a4dae14f02eea8e1bb42d4db2b6ce5e83dbcdd6b1446997e0c15 F ext/repair/checkfreelist.c 0abb84b4545016d57ba1a2aa8884c72c73ed838968909858c03bc1f38fb6b054 -F ext/repair/checkindex.c 924432d01fabff8df8a758ef29d7124483653cd7874787564664e0eea8e267b1 +F ext/repair/checkindex.c 62df3d58ddf2988ab5bc9a7e3a9f16d16c3844a988df5cb702a2d0f4d2dd5bf4 F ext/rtree/README 6315c0d73ebf0ec40dedb5aa0e942bc8b54e3761 F ext/rtree/rtree.c cc91b6905bf55512c6ebc7dfdd37ac81c86f1753db8cfa6d62f0ee864464044f F ext/rtree/rtree.h 834dbcb82dc85b2481cde6a07cdadfddc99e9b9e @@ -654,7 +654,7 @@ F test/cast.test 4c275cbdc8202d6f9c54a3596701719868ac7dc3 F test/cffault.test 9d6b20606afe712374952eec4f8fd74b1a8097ef F test/check.test 33a698e8c63613449d85d624a38ef669bf20331daabebe3891c9405dd6df463a F test/checkfreelist.test 100283a3e6b8a3018c7fab7cfdaf03d1d6540fc66453114e248cf82b25784d3b -F test/checkindex.test 2dc7bd4c0de8ba7a8af0b6d3beaa6759d57b88c62e10ae4d158e9f544982d5d4 +F test/checkindex.test 48cf7b237756f4691e7a12be78e1719d5b0456e7e205b87da380f3a6bec11966 F test/close.test 799ea4599d2f5704b0a30f477d17c2c760d8523fa5d0c8be4a7df2a8cad787d8 F test/closure01.test b1703ba40639cfc9b295cf478d70739415eec6a4 F test/coalesce.test cee0dccb9fbd2d494b77234bccf9dc6c6786eb91 @@ -1668,10 +1668,7 @@ F vsixtest/vsixtest.tcl 6a9a6ab600c25a91a7acc6293828957a386a8a93 F vsixtest/vsixtest.vcxproj.data 2ed517e100c66dc455b492e1a33350c1b20fbcdc F vsixtest/vsixtest.vcxproj.filters 37e51ffedcdb064aad6ff33b6148725226cd608e F vsixtest/vsixtest_TemporaryKey.pfx e5b1b036facdb453873e7084e1cae9102ccc67a0 -P c8aaf37117ed4a23bbd15dc481788735efcb77bff98be423eca3521c0a9270de -R 38fb5f31777282a52c65b4e266007341 -T *branch * checkindex -T *sym-checkindex * -T -sym-trunk * +P d5b9dada471358a2864727759648b763bf6890fc2521fac53c0d8216017d39b7 +R 31f10dd93d0cbe363e20ad50a747a726 U dan -Z 12607f1e180da6aa96255bb6397017d4 +Z 019388109f5e5cd896dce5eabbc0252a diff --git a/manifest.uuid b/manifest.uuid index 9ee1c84b8f..45cec907d3 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -d5b9dada471358a2864727759648b763bf6890fc2521fac53c0d8216017d39b7 \ No newline at end of file +c40c3c62e996044f31ca49ffc2edb2cc0320e69956f7ee6fe3e9012200e0d9a0 \ No newline at end of file diff --git a/test/checkindex.test b/test/checkindex.test index 841dca75a2..bafa42334c 100644 --- a/test/checkindex.test +++ b/test/checkindex.test @@ -34,6 +34,8 @@ do_execsql_test 1.0 { INSERT INTO t1 VALUES('three', 6); INSERT INTO t1 VALUES('four', 8); INSERT INTO t1 VALUES('five', 10); + + CREATE INDEX i2 ON t1(a DESC); } db enable_load_extension 1 @@ -41,18 +43,61 @@ do_execsql_test 1.1 { SELECT load_extension('../checkindex.so'); } {{}} +proc incr_index_check {idx nStep} { + set Q { + SELECT errmsg, current_key FROM incremental_index_check($idx, $after) + LIMIT $nStep + } + + set res [list] + while {1} { + unset -nocomplain current_key + set res1 [db eval $Q] + if {[llength $res1]==0} break + set res [concat $res $res1] + set after [lindex $res end] + } + + return $res +} + +proc do_index_check_test {tn idx res} { + uplevel [list do_execsql_test $tn.1 " + SELECT errmsg, current_key FROM incremental_index_check('$idx'); + " $res] + + uplevel [list do_test $tn.2 "incr_index_check $idx 1" [list {*}$res]] + #uplevel [list do_test $tn.3 "incr_index_check $idx 2" [list {*}$res]] + #uplevel [list do_test $tn.4 "incr_index_check $idx 5" [list {*}$res]] +} + do_execsql_test 1.2 { SELECT errmsg IS NULL, current_key FROM incremental_index_check('i1'); } { - 1 five,5 - 1 four,4 - 1 one,1 - 1 three,3 - 1 two,2 + 1 'five',5 + 1 'four',4 + 1 'one',1 + 1 'three',3 + 1 'two',2 } +do_index_check_test 1.3 i1 { + {} 'five',5 + {} 'four',4 + {} 'one',1 + {} 'three',3 + {} 'two',2 +} + +do_index_check_test 1.4 i2 { + {} 'two',2 + {} 'three',3 + {} 'one',1 + {} 'four',4 + {} 'five',5 +} -do_test 1.3 { +do_test 1.5 { set tblroot [db one { SELECT rootpage FROM sqlite_master WHERE name='t1' }] sqlite3_test_control SQLITE_TESTCTRL_IMPOSTER db main 1 $tblroot db eval {CREATE TABLE xt1(a, b)} @@ -66,26 +111,110 @@ do_test 1.3 { sqlite3_test_control SQLITE_TESTCTRL_IMPOSTER db main 0 1 } {} -do_execsql_test 1.4 { - SELECT errmsg IS NULL, current_key FROM incremental_index_check('i1'); -} { - 0 five,5 - 1 four,4 - 1 one,1 - 0 three,3 - 1 two,2 -} -do_execsql_test 1.5 { - SELECT errmsg, current_key FROM incremental_index_check('i1'); -} { - {row missing} five,5 - {} four,4 - {} one,1 - {row data mismatch} three,3 - {} two,2 +do_index_check_test 1.6 i1 { + {row missing} 'five',5 + {} 'four',4 + {} 'one',1 + {row data mismatch} 'three',3 + {} 'two',2 +} + +do_index_check_test 1.7 i2 { + {} 'two',2 + {row data mismatch} 'three',3 + {} 'one',1 + {} 'four',4 + {row missing} 'five',5 } +#-------------------------------------------------------------------------- +do_execsql_test 2.0 { + + CREATE TABLE t2(a INTEGER PRIMARY KEY, b, c, d); + + INSERT INTO t2 VALUES(1, NULL, 1, 1); + INSERT INTO t2 VALUES(2, 1, NULL, 1); + INSERT INTO t2 VALUES(3, 1, 1, NULL); + INSERT INTO t2 VALUES(4, 2, 2, 1); + INSERT INTO t2 VALUES(5, 2, 2, 2); + INSERT INTO t2 VALUES(6, 2, 2, 3); + + INSERT INTO t2 VALUES(7, 2, 2, 1); + INSERT INTO t2 VALUES(8, 2, 2, 2); + INSERT INTO t2 VALUES(9, 2, 2, 3); + + CREATE INDEX i3 ON t2(b, c, d); + CREATE INDEX i4 ON t2(b DESC, c DESC, d DESC); + CREATE INDEX i5 ON t2(d, c DESC, b); +} + +do_index_check_test 2.1 i3 { + {} NULL,1,1,1 + {} 1,NULL,1,2 + {} 1,1,NULL,3 + {} 2,2,1,4 + {} 2,2,1,7 + {} 2,2,2,5 + {} 2,2,2,8 + {} 2,2,3,6 + {} 2,2,3,9 +} + +do_index_check_test 2.2 i4 { + {} 2,2,3,6 + {} 2,2,3,9 + {} 2,2,2,5 + {} 2,2,2,8 + {} 2,2,1,4 + {} 2,2,1,7 + {} 1,1,NULL,3 + {} 1,NULL,1,2 + {} NULL,1,1,1 +} + +do_index_check_test 2.3 i5 { + {} NULL,1,1,3 + {} 1,2,2,4 + {} 1,2,2,7 + {} 1,1,NULL,1 + {} 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); + INSERT INTO t3 VALUES(NULL, NULL, NULL, 2); + INSERT INTO t3 VALUES(NULL, NULL, NULL, 3); + + INSERT INTO t3 VALUES('a', NULL, NULL, 4); + INSERT INTO t3 VALUES('a', NULL, NULL, 5); + INSERT INTO t3 VALUES('a', NULL, NULL, 6); + + INSERT INTO t3 VALUES('a', 'b', NULL, 7); + INSERT INTO t3 VALUES('a', 'b', NULL, 8); + INSERT INTO t3 VALUES('a', 'b', NULL, 9); + +} + +do_index_check_test 3.1 t3wxy { + {} NULL,NULL,NULL,1 {} NULL,NULL,NULL,2 {} NULL,NULL,NULL,3 + {} 'a',NULL,NULL,4 {} 'a',NULL,NULL,5 {} 'a',NULL,NULL,6 + {} '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 -- 2.47.2