From 69014393f87e6b89b8c32207ff1aee2202a665e2 Mon Sep 17 00:00:00 2001 From: dan Date: Sat, 4 Jan 2014 14:42:14 +0000 Subject: [PATCH] Instead of having the planner ignore plans with unusable MATCH constraints, have FTS assign extremely high costs to such plans in order to discourage the planner from using them. FossilOrigin-Name: 24f84b38131866f7b435ffe641bb2f6991a70db2 --- ext/fts3/fts3.c | 28 +++++++++++++++++++++++++++- manifest | 19 ++++++++----------- manifest.uuid | 2 +- src/where.c | 5 ----- test/fts3join.test | 2 +- 5 files changed, 37 insertions(+), 19 deletions(-) diff --git a/ext/fts3/fts3.c b/ext/fts3/fts3.c index 3b9efee54c..62ff1437b9 100644 --- a/ext/fts3/fts3.c +++ b/ext/fts3/fts3.c @@ -1472,6 +1472,19 @@ static int fts3CreateMethod( return fts3InitVtab(1, db, pAux, argc, argv, ppVtab, pzErr); } +/* +** Set the pIdxInfo->estimatedRows variable to nRow. Unless this +** extension is currently being used by a version of SQLite too old to +** support estimatedRows. In that case this function is a no-op. +*/ +static void setEstimatedRows(sqlite3_index_info *pIdxInfo, i64 nRow){ +#if SQLITE_VERSION_NUMBER>=3008002 + if( sqlite3_libversion_number()>=3008002 ){ + pIdxInfo->estimatedRows = nRow; + } +#endif +} + /* ** Implementation of the xBestIndex method for FTS3 tables. There ** are three possible strategies, in order of preference: @@ -1499,7 +1512,20 @@ static int fts3BestIndexMethod(sqlite3_vtab *pVTab, sqlite3_index_info *pInfo){ for(i=0; inConstraint; i++){ int bDocid; /* True if this constraint is on docid */ struct sqlite3_index_constraint *pCons = &pInfo->aConstraint[i]; - if( pCons->usable==0 ) continue; + if( pCons->usable==0 ){ + if( pCons->op==SQLITE_INDEX_CONSTRAINT_MATCH ){ + /* There exists an unusable MATCH constraint. This means that if + ** the planner does elect to use the results of this call as part + ** of the overall query plan the user will see an "unable to use + ** function MATCH in the requested context" error. To discourage + ** this, return a very high cost here. */ + pInfo->idxNum = FTS3_FULLSCAN_SEARCH; + pInfo->estimatedCost = 1e50; + setEstimatedRows(pInfo, ((sqlite3_int64)1) << 50); + return SQLITE_OK; + } + continue; + } bDocid = (pCons->iColumn<0 || pCons->iColumn==p->nColumn+1); diff --git a/manifest b/manifest index 32c49321f0..8e2e2e7378 100644 --- a/manifest +++ b/manifest @@ -1,5 +1,5 @@ -C Have\sthe\splanner\sdetect\scases\swhere\sa\sMATCH\sconstraint\sis\sapplied\sto\sa\svirtual\stable,\sand\signore\sany\splans\sthat\sdo\snot\sallow\sthe\svirtual\stable\simplementation\sto\simplement\sthe\sMATCH\sfiltering. -D 2014-01-04T14:16:06.902 +C Instead\sof\shaving\sthe\splanner\signore\splans\swith\sunusable\sMATCH\sconstraints,\shave\sFTS\sassign\sextremely\shigh\scosts\sto\ssuch\splans\sin\sorder\sto\sdiscourage\sthe\splanner\sfrom\susing\sthem. +D 2014-01-04T14:42:14.057 F Makefile.arm-wince-mingw32ce-gcc d6df77f1f48d690bd73162294bbba7f59507c72f F Makefile.in 2ef13430cd359f7b361bb863504e227b25cc7f81 F Makefile.linux-gcc 91d710bdc4998cb015f39edf3cb314ec4f4d7e23 @@ -78,7 +78,7 @@ F ext/fts3/README.content fdc666a70d5257a64fee209f97cf89e0e6e32b51 F ext/fts3/README.syntax a19711dc5458c20734b8e485e75fb1981ec2427a F ext/fts3/README.tokenizers e0a8b81383ea60d0334d274fadf305ea14a8c314 F ext/fts3/README.txt 8c18f41574404623b76917b9da66fcb0ab38328d -F ext/fts3/fts3.c 1e667eacb3fe4b4ad6f863920da4286f071f6e07 +F ext/fts3/fts3.c 2af2cb2e742461b79710c132c7969fc7d949a59a F ext/fts3/fts3.h 3a10a0af180d502cecc50df77b1b22df142817fe F ext/fts3/fts3Int.h eb5f8029589f3d8f1dc7fd50c773326a640388b1 F ext/fts3/fts3_aux.c 5c211e17a64885faeb16b9ba7772f9d5445c2365 @@ -293,7 +293,7 @@ F src/vtab.c 21b932841e51ebd7d075e2d0ad1415dce8d2d5fd F src/wal.c 7dc3966ef98b74422267e7e6e46e07ff6c6eb1b4 F src/wal.h df01efe09c5cb8c8e391ff1715cca294f89668a4 F src/walker.c e9e593d5bb798c3e67fc3893dfe7055c9e7d8d74 -F src/where.c 14403429f7ad11b13b6ae0e466d089b8875ba195 +F src/where.c 8e3cb2030eaa242defb804aa30115b5d870e5cd4 F src/whereInt.h 96a75c61f1d2b9d4a8e4bb17d89deb0cf7cba358 F test/8_3_names.test ebbb5cd36741350040fd28b432ceadf495be25b2 F test/aggerror.test a867e273ef9e3d7919f03ef4f0e8c0d2767944f2 @@ -547,7 +547,7 @@ F test/fts3expr3.test 9e91b8edbcb197bf2e92161aa7696446d96dce5f F test/fts3fault.test cb72dccb0a3b9f730f16c5240f3fcb9303eb1660 F test/fts3fault2.test 3198eef2804deea7cac8403e771d9cbcb752d887 F test/fts3first.test dbdedd20914c8d539aa3206c9b34a23775644641 -F test/fts3join.test bc8e5ca3e6b6577cf98e81b5271291e33e020e87 +F test/fts3join.test 53e66a0c21eb568580674a43b21c059acb26f499 F test/fts3malloc.test b0e4c133b8d61d4f6d112d8110f8320e9e453ef6 F test/fts3matchinfo.test ff423e73faab8fc6d7adeefedf74dd8e2b0b14e0 F test/fts3near.test 7e3354d46f155a822b59c0e957fd2a70c1d7e905 @@ -1148,10 +1148,7 @@ F tool/vdbe-compress.tcl 0cf56e9263a152b84da86e75a5c0cdcdb7a47891 F tool/warnings-clang.sh f6aa929dc20ef1f856af04a730772f59283631d4 F tool/warnings.sh d1a6de74685f360ab718efda6265994b99bbea01 F tool/win/sqlite.vsix 030f3eeaf2cb811a3692ab9c14d021a75ce41fff -P 429018b19cb525a4bb0843d20955457b53900d4b -R 6571bb57c7cea9164f2f0721e5f9106f -T *branch * avoid-unusable-match -T *sym-avoid-unusable-match * -T -sym-trunk * +P 19f3208b26597625728d1ef716d4e44407cf96ac +R 2d783783dbaa85fb88df6c2aa4454d7b U dan -Z 6e1a68d01b84e17020c8f255059d7b13 +Z 4059190156e7afb8f8755a31ed7148b9 diff --git a/manifest.uuid b/manifest.uuid index d3155e3c85..4eab7c326e 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -19f3208b26597625728d1ef716d4e44407cf96ac \ No newline at end of file +24f84b38131866f7b435ffe641bb2f6991a70db2 \ No newline at end of file diff --git a/src/where.c b/src/where.c index 83ec748679..0acc0db53f 100644 --- a/src/where.c +++ b/src/where.c @@ -4338,7 +4338,6 @@ static int whereLoopAddVirtual( int nConstraint; int seenIn = 0; /* True if an IN operator is seen */ int seenVar = 0; /* True if a non-constant constraint is seen */ - int seenVarMatch = 0; /* If a non-constant MATCH constraint is seen */ int iPhase; /* 0: const w/o IN, 1: const, 2: no IN, 2: IN */ WhereLoop *pNew; int rc = SQLITE_OK; @@ -4383,7 +4382,6 @@ static int whereLoopAddVirtual( } if( pTerm->prereqRight!=0 ){ seenVar = 1; - if( pTerm->eOperator & WO_MATCH ) seenVarMatch = 1; }else if( (pTerm->eOperator & WO_IN)==0 ){ pIdxCons->usable = 1; } @@ -4402,9 +4400,6 @@ static int whereLoopAddVirtual( break; } } - /* The following line ensures that, if there exists a MATCH constraint, - ** no plans for which the MATCH constraint is not usable are considered. */ - if( seenVarMatch && iPhase<=1 ) continue; memset(pUsage, 0, sizeof(pUsage[0])*pIdxInfo->nConstraint); if( pIdxInfo->needToFreeIdxStr ) sqlite3_free(pIdxInfo->idxStr); pIdxInfo->idxStr = 0; diff --git a/test/fts3join.test b/test/fts3join.test index f02e21513e..64363639db 100644 --- a/test/fts3join.test +++ b/test/fts3join.test @@ -59,7 +59,7 @@ do_execsql_test 2.4 { SELECT * FROM ft3, ft2 WHERE y MATCH x; } {abc abc} do_catchsql_test 2.5 { SELECT * FROM ft3, ft2 WHERE y MATCH x AND x MATCH y; -} {1 {no query solution}} +} {1 {unable to use function MATCH in the requested context}} finish_test -- 2.39.5