SQLite: Check-in [6f54ffd1]

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

Overview

Comment:	Allow a user column name to be used on the LHS of a MATCH operator in FTS5.
Downloads:	Tarball \| ZIP archive \| SQL archive
Timelines:	family \| ancestors \| descendants \| both \| trunk
Files:	files \| file ages \| folders
SHA3-256:	6f54ffd151b0eca6f9ef57ac54802584a839cfc7373f10c100fc18c855edcc0a
User & Date:	dan 2017-04-13 09:45:21

Context

2017-04-13
15:36		Update the code in test_delete.c to use the "win32" VFS if SQLITE_OS_WIN is defined. check-in: fa9bb7b7 user: dan tags: trunk
09:45		Allow a user column name to be used on the LHS of a MATCH operator in FTS5. check-in: 6f54ffd1 user: dan tags: trunk
00:12		Fix a regression caused by the fix for ticket [6c9b5514077fed34551f98e64c09a1] - control characters allowed in JSON. check-in: 8e7b6118 user: drh tags: trunk

Changes

Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to ext/fts5/fts5Int.h.

  const char *p;                  /* Token text (not NULL terminated) */
  int n;                          /* Size of buffer p in bytes */
};

/* Parse a MATCH expression. */
int sqlite3Fts5ExprNew(
  Fts5Config *pConfig, 

  const char *zExpr,
  Fts5Expr **ppNew, 
  char **pzErr
);

/*
** for(rc = sqlite3Fts5ExprFirst(pExpr, pIdx, bDesc);

  const char *p;                  /* Token text (not NULL terminated) */
  int n;                          /* Size of buffer p in bytes */
};

/* Parse a MATCH expression. */
int sqlite3Fts5ExprNew(
  Fts5Config *pConfig, 
  int iCol,                       /* Column on LHS of MATCH operator */
  const char *zExpr,
  Fts5Expr **ppNew, 
  char **pzErr
);

/*
** for(rc = sqlite3Fts5ExprFirst(pExpr, pIdx, bDesc);

Changes to ext/fts5/fts5_expr.c.

}

static void *fts5ParseAlloc(u64 t){ return sqlite3_malloc((int)t); }
static void fts5ParseFree(void *p){ sqlite3_free(p); }

int sqlite3Fts5ExprNew(
  Fts5Config *pConfig,            /* FTS5 Configuration */

  const char *zExpr,              /* Expression text */
  Fts5Expr **ppNew, 
  char **pzErr
){
  Fts5Parse sParse;
  Fts5Token token;
  const char *z = zExpr;
................................................................................
  sParse.pConfig = pConfig;

  do {
    t = fts5ExprGetToken(&sParse, &z, &token);
    sqlite3Fts5Parser(pEngine, t, token, &sParse);
  }while( sParse.rc==SQLITE_OK && t!=FTS5_EOF );
  sqlite3Fts5ParserFree(pEngine, fts5ParseFree);













  assert( sParse.rc!=SQLITE_OK || sParse.zErr==0 );
  if( sParse.rc==SQLITE_OK ){
    *ppNew = pNew = sqlite3_malloc(sizeof(Fts5Expr));
    if( pNew==0 ){
      sParse.rc = SQLITE_NOMEM;
      sqlite3Fts5ParseNodeFree(sParse.pExpr);
................................................................................
    azConfig[i++] = (const char*)sqlite3_value_text(apVal[iArg]);
  }

  zExpr = (const char*)sqlite3_value_text(apVal[0]);

  rc = sqlite3Fts5ConfigParse(pGlobal, db, nConfig, azConfig, &pConfig, &zErr);
  if( rc==SQLITE_OK ){
    rc = sqlite3Fts5ExprNew(pConfig, zExpr, &pExpr, &zErr);
  }
  if( rc==SQLITE_OK ){
    char *zText;
    if( pExpr->pRoot->xNext==0 ){
      zText = sqlite3_mprintf("");
    }else if( bTcl ){
      zText = fts5ExprPrintTcl(pConfig, zNearsetCmd, pExpr->pRoot);








>







 







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







 







|

}

static void *fts5ParseAlloc(u64 t){ return sqlite3_malloc((int)t); }
static void fts5ParseFree(void *p){ sqlite3_free(p); }

int sqlite3Fts5ExprNew(
  Fts5Config *pConfig,            /* FTS5 Configuration */
  int iCol,
  const char *zExpr,              /* Expression text */
  Fts5Expr **ppNew, 
  char **pzErr
){
  Fts5Parse sParse;
  Fts5Token token;
  const char *z = zExpr;
................................................................................
  sParse.pConfig = pConfig;

  do {
    t = fts5ExprGetToken(&sParse, &z, &token);
    sqlite3Fts5Parser(pEngine, t, token, &sParse);
  }while( sParse.rc==SQLITE_OK && t!=FTS5_EOF );
  sqlite3Fts5ParserFree(pEngine, fts5ParseFree);

  /* If the LHS of the MATCH expression was a user column, apply the
  ** implicit column-filter.  */
  if( iCol<pConfig->nCol && sParse.pExpr && sParse.rc==SQLITE_OK ){
    int n = sizeof(Fts5Colset);
    Fts5Colset *pColset = (Fts5Colset*)sqlite3Fts5MallocZero(&sParse.rc, n);
    if( pColset ){
      pColset->nCol = 1;
      pColset->aiCol[0] = iCol;
      sqlite3Fts5ParseSetColset(&sParse, sParse.pExpr, pColset);
    }
  }

  assert( sParse.rc!=SQLITE_OK || sParse.zErr==0 );
  if( sParse.rc==SQLITE_OK ){
    *ppNew = pNew = sqlite3_malloc(sizeof(Fts5Expr));
    if( pNew==0 ){
      sParse.rc = SQLITE_NOMEM;
      sqlite3Fts5ParseNodeFree(sParse.pExpr);
................................................................................
    azConfig[i++] = (const char*)sqlite3_value_text(apVal[iArg]);
  }

  zExpr = (const char*)sqlite3_value_text(apVal[0]);

  rc = sqlite3Fts5ConfigParse(pGlobal, db, nConfig, azConfig, &pConfig, &zErr);
  if( rc==SQLITE_OK ){
    rc = sqlite3Fts5ExprNew(pConfig, pConfig->nCol, zExpr, &pExpr, &zErr);
  }
  if( rc==SQLITE_OK ){
    char *zText;
    if( pExpr->pRoot->xNext==0 ){
      zText = sqlite3_mprintf("");
    }else if( bTcl ){
      zText = fts5ExprPrintTcl(pConfig, zNearsetCmd, pExpr->pRoot);

Changes to ext/fts5/fts5_main.c.

**       * An == rowid constraint:       cost=10.0
**
** Costs are not modified by the ORDER BY clause.
*/
static int fts5BestIndexMethod(sqlite3_vtab *pVTab, sqlite3_index_info *pInfo){
  Fts5Table *pTab = (Fts5Table*)pVTab;
  Fts5Config *pConfig = pTab->pConfig;

  int idxFlags = 0;               /* Parameter passed through to xFilter() */
  int bHasMatch;
  int iNext;
  int i;

  struct Constraint {
    int op;                       /* Mask against sqlite3_index_constraint.op */
................................................................................
                                    FTS5_BI_ROWID_LE, 0, 0, -1},
    {SQLITE_INDEX_CONSTRAINT_GT|SQLITE_INDEX_CONSTRAINT_GE, 
                                    FTS5_BI_ROWID_GE, 0, 0, -1},
  };

  int aColMap[3];
  aColMap[0] = -1;
  aColMap[1] = pConfig->nCol;
  aColMap[2] = pConfig->nCol+1;

  /* Set idxFlags flags for all WHERE clause terms that will be used. */
  for(i=0; i<pInfo->nConstraint; i++){
    struct sqlite3_index_constraint *p = &pInfo->aConstraint[i];
    int j;
    for(j=0; j<ArraySize(aConstraint); j++){

      struct Constraint *pC = &aConstraint[j];
      if( p->iColumn==aColMap[pC->iCol] && p->op & pC->op ){


        if( p->usable ){

          pC->iConsIndex = i;
          idxFlags |= pC->fts5op;
        }else if( j==0 ){
          /* As there exists an unusable MATCH constraint this is an 
          ** unusable plan. Set a prohibitively high cost. */
          pInfo->estimatedCost = 1e50;
          return SQLITE_OK;








        }
      }
    }
  }

  /* Set idxFlags flags for the ORDER BY clause */
  if( pInfo->nOrderBy==1 ){
................................................................................
  int bDesc;                      /* True if ORDER BY [rank|rowid] DESC */
  int bOrderByRank;               /* True if ORDER BY rank */
  sqlite3_value *pMatch = 0;      /* <tbl> MATCH ? expression (or NULL) */
  sqlite3_value *pRank = 0;       /* rank MATCH ? expression (or NULL) */
  sqlite3_value *pRowidEq = 0;    /* rowid = ? expression (or NULL) */
  sqlite3_value *pRowidLe = 0;    /* rowid <= ? expression (or NULL) */
  sqlite3_value *pRowidGe = 0;    /* rowid >= ? expression (or NULL) */

  char **pzErrmsg = pConfig->pzErrmsg;

  UNUSED_PARAM(zUnused);
  UNUSED_PARAM(nVal);

  if( pCsr->ePlan ){
    fts5FreeCursorComponents(pCsr);
................................................................................
  ** order as the corresponding entries in the struct at the top of
  ** fts5BestIndexMethod().  */
  if( BitFlagTest(idxNum, FTS5_BI_MATCH) ) pMatch = apVal[iVal++];
  if( BitFlagTest(idxNum, FTS5_BI_RANK) ) pRank = apVal[iVal++];
  if( BitFlagTest(idxNum, FTS5_BI_ROWID_EQ) ) pRowidEq = apVal[iVal++];
  if( BitFlagTest(idxNum, FTS5_BI_ROWID_LE) ) pRowidLe = apVal[iVal++];
  if( BitFlagTest(idxNum, FTS5_BI_ROWID_GE) ) pRowidGe = apVal[iVal++];


  assert( iVal==nVal );
  bOrderByRank = ((idxNum & FTS5_BI_ORDER_RANK) ? 1 : 0);
  pCsr->bDesc = bDesc = ((idxNum & FTS5_BI_ORDER_DESC) ? 1 : 0);

  /* Set the cursor upper and lower rowid limits. Only some strategies 
  ** actually use them. This is ok, as the xBestIndex() method leaves the
  ** sqlite3_index_constraint.omit flag clear for range constraints
................................................................................
      if( zExpr[0]=='*' ){
        /* The user has issued a query of the form "MATCH '*...'". This
        ** indicates that the MATCH expression is not a full text query,
        ** but a request for an internal parameter.  */
        rc = fts5SpecialMatch(pTab, pCsr, &zExpr[1]);
      }else{
        char **pzErr = &pTab->base.zErrMsg;
        rc = sqlite3Fts5ExprNew(pConfig, zExpr, &pCsr->pExpr, pzErr);
        if( rc==SQLITE_OK ){
          if( bOrderByRank ){
            pCsr->ePlan = FTS5_PLAN_SORTED_MATCH;
            rc = fts5CursorFirstSorted(pTab, pCsr, bDesc);
          }else{
            pCsr->ePlan = FTS5_PLAN_MATCH;
            rc = fts5CursorFirst(pTab, pCsr, bDesc);








>







 







|
|




|
<
>
|
|
>
>
|
>
|
<
|
|
|
|
|
>
>
>
>
>
>
>
>







 







>







 







>
>







 







|

**       * An == rowid constraint:       cost=10.0
**
** Costs are not modified by the ORDER BY clause.
*/
static int fts5BestIndexMethod(sqlite3_vtab *pVTab, sqlite3_index_info *pInfo){
  Fts5Table *pTab = (Fts5Table*)pVTab;
  Fts5Config *pConfig = pTab->pConfig;
  const int nCol = pConfig->nCol;
  int idxFlags = 0;               /* Parameter passed through to xFilter() */
  int bHasMatch;
  int iNext;
  int i;

  struct Constraint {
    int op;                       /* Mask against sqlite3_index_constraint.op */
................................................................................
                                    FTS5_BI_ROWID_LE, 0, 0, -1},
    {SQLITE_INDEX_CONSTRAINT_GT|SQLITE_INDEX_CONSTRAINT_GE, 
                                    FTS5_BI_ROWID_GE, 0, 0, -1},
  };

  int aColMap[3];
  aColMap[0] = -1;
  aColMap[1] = nCol;
  aColMap[2] = nCol+1;

  /* Set idxFlags flags for all WHERE clause terms that will be used. */
  for(i=0; i<pInfo->nConstraint; i++){
    struct sqlite3_index_constraint *p = &pInfo->aConstraint[i];
    int iCol = p->iColumn;


    if( (p->op==SQLITE_INDEX_CONSTRAINT_MATCH && iCol>=0 && iCol<=nCol)
     || (p->op==SQLITE_INDEX_CONSTRAINT_EQ && iCol==nCol)
    ){
      /* A MATCH operator or equivalent */
      if( p->usable ){
        idxFlags = (idxFlags & 0xFFFF) | FTS5_BI_MATCH | (iCol << 16);
        aConstraint[0].iConsIndex = i;

      }else{
        /* As there exists an unusable MATCH constraint this is an 
        ** unusable plan. Set a prohibitively high cost. */
        pInfo->estimatedCost = 1e50;
        return SQLITE_OK;
      }
    }else{
      int j;
      for(j=1; j<ArraySize(aConstraint); j++){
        struct Constraint *pC = &aConstraint[j];
        if( iCol==aColMap[pC->iCol] && p->op & pC->op && p->usable ){
          pC->iConsIndex = i;
          idxFlags |= pC->fts5op;
        }
      }
    }
  }

  /* Set idxFlags flags for the ORDER BY clause */
  if( pInfo->nOrderBy==1 ){
................................................................................
  int bDesc;                      /* True if ORDER BY [rank|rowid] DESC */
  int bOrderByRank;               /* True if ORDER BY rank */
  sqlite3_value *pMatch = 0;      /* <tbl> MATCH ? expression (or NULL) */
  sqlite3_value *pRank = 0;       /* rank MATCH ? expression (or NULL) */
  sqlite3_value *pRowidEq = 0;    /* rowid = ? expression (or NULL) */
  sqlite3_value *pRowidLe = 0;    /* rowid <= ? expression (or NULL) */
  sqlite3_value *pRowidGe = 0;    /* rowid >= ? expression (or NULL) */
  int iCol;                       /* Column on LHS of MATCH operator */
  char **pzErrmsg = pConfig->pzErrmsg;

  UNUSED_PARAM(zUnused);
  UNUSED_PARAM(nVal);

  if( pCsr->ePlan ){
    fts5FreeCursorComponents(pCsr);
................................................................................
  ** order as the corresponding entries in the struct at the top of
  ** fts5BestIndexMethod().  */
  if( BitFlagTest(idxNum, FTS5_BI_MATCH) ) pMatch = apVal[iVal++];
  if( BitFlagTest(idxNum, FTS5_BI_RANK) ) pRank = apVal[iVal++];
  if( BitFlagTest(idxNum, FTS5_BI_ROWID_EQ) ) pRowidEq = apVal[iVal++];
  if( BitFlagTest(idxNum, FTS5_BI_ROWID_LE) ) pRowidLe = apVal[iVal++];
  if( BitFlagTest(idxNum, FTS5_BI_ROWID_GE) ) pRowidGe = apVal[iVal++];
  iCol = (idxNum>>16);
  assert( iCol>=0 && iCol<=pConfig->nCol );
  assert( iVal==nVal );
  bOrderByRank = ((idxNum & FTS5_BI_ORDER_RANK) ? 1 : 0);
  pCsr->bDesc = bDesc = ((idxNum & FTS5_BI_ORDER_DESC) ? 1 : 0);

  /* Set the cursor upper and lower rowid limits. Only some strategies 
  ** actually use them. This is ok, as the xBestIndex() method leaves the
  ** sqlite3_index_constraint.omit flag clear for range constraints
................................................................................
      if( zExpr[0]=='*' ){
        /* The user has issued a query of the form "MATCH '*...'". This
        ** indicates that the MATCH expression is not a full text query,
        ** but a request for an internal parameter.  */
        rc = fts5SpecialMatch(pTab, pCsr, &zExpr[1]);
      }else{
        char **pzErr = &pTab->base.zErrMsg;
        rc = sqlite3Fts5ExprNew(pConfig, iCol, zExpr, &pCsr->pExpr, pzErr);
        if( rc==SQLITE_OK ){
          if( bOrderByRank ){
            pCsr->ePlan = FTS5_PLAN_SORTED_MATCH;
            rc = fts5CursorFirstSorted(pTab, pCsr, bDesc);
          }else{
            pCsr->ePlan = FTS5_PLAN_MATCH;
            rc = fts5CursorFirst(pTab, pCsr, bDesc);

Changes to ext/fts5/test/fts5colset.test.

    6 "{a} : ( {b a} : ( {c b} : ( {d b c a} : ( d OR c ) ) ) )" {}
    7 "{a b c} : (b:a AND c:b)" {2}
  } {
    do_execsql_test 2.$tn {
      SELECT rowid FROM t1($q)
    } $res
  }

















}


finish_test








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

    6 "{a} : ( {b a} : ( {c b} : ( {d b c a} : ( d OR c ) ) ) )" {}
    7 "{a b c} : (b:a AND c:b)" {2}
  } {
    do_execsql_test 2.$tn {
      SELECT rowid FROM t1($q)
    } $res
  }

  foreach {tn w res} {
    0 "a MATCH 'a'" {1}
    1 "b MATCH 'a'" {2}
    2 "b MATCH '{a b c} : a'" {2}
    3 "b MATCH 'a OR b'"      {1 2}
    4 "b MATCH 'a OR a:b'"    {2}
    5 "b MATCH 'a OR b:b'"    {1 2}
  } {
    do_execsql_test 3.$tn "
      SELECT rowid FROM t1 WHERE $w
    " $res
  }

  do_catchsql_test 4.1 {
    SELECT * FROM t1 WHERE rowid MATCH 'a'
  } {1 {unable to use function MATCH in the requested context}}
}


finish_test

Changes to ext/fts5/test/fts5plan.test.

  CREATE VIRTUAL TABLE f1 USING fts5(ff);
}

do_eqp_test 1.1 {
  SELECT * FROM t1, f1 WHERE f1 MATCH t1.x
} {
  0 0 0 {SCAN TABLE t1} 
  0 1 1 {SCAN TABLE f1 VIRTUAL TABLE INDEX 1:}
}

do_eqp_test 1.2 {
  SELECT * FROM t1, f1 WHERE f1 > t1.x
} {
  0 0 1 {SCAN TABLE f1 VIRTUAL TABLE INDEX 0:}
  0 1 0 {SCAN TABLE t1} 
}

do_eqp_test 1.3 {
  SELECT * FROM f1 WHERE f1 MATCH ? ORDER BY ff
} {
  0 0 0 {SCAN TABLE f1 VIRTUAL TABLE INDEX 1:}
  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
}

do_eqp_test 1.4 {
  SELECT * FROM f1 ORDER BY rank
} {
  0 0 0 {SCAN TABLE f1 VIRTUAL TABLE INDEX 0:}

  CREATE VIRTUAL TABLE f1 USING fts5(ff);
}

do_eqp_test 1.1 {
  SELECT * FROM t1, f1 WHERE f1 MATCH t1.x
} {
  0 0 0 {SCAN TABLE t1} 
  0 1 1 {SCAN TABLE f1 VIRTUAL TABLE INDEX 65537:}
}

do_eqp_test 1.2 {
  SELECT * FROM t1, f1 WHERE f1 > t1.x
} {
  0 0 1 {SCAN TABLE f1 VIRTUAL TABLE INDEX 0:}
  0 1 0 {SCAN TABLE t1} 
}

do_eqp_test 1.3 {
  SELECT * FROM f1 WHERE f1 MATCH ? ORDER BY ff
} {
  0 0 0 {SCAN TABLE f1 VIRTUAL TABLE INDEX 65537:}
  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
}

do_eqp_test 1.4 {
  SELECT * FROM f1 ORDER BY rank
} {
  0 0 0 {SCAN TABLE f1 VIRTUAL TABLE INDEX 0:}