SQLite

  struct sqlite3_index_constraint_usage *pUsage;
  WhereTerm *pTerm;
  int i, j;
  int iTerm, mxTerm;
  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;

  pWInfo = pBuilder->pWInfo;
  pParse = pWInfo->pParse;
  db = pParse->db;

        case 0:    /* Constants without IN operator */
          pIdxCons->usable = 0;
          if( (pTerm->eOperator & WO_IN)!=0 ){
            seenIn = 1;
          }
          if( pTerm->prereqRight!=0 ){
            seenVar = 1;
            if( pTerm->eOperator & WO_MATCH ) seenVarMatch = 1;
          }else if( (pTerm->eOperator & WO_IN)==0 ){
            pIdxCons->usable = 1;
          }
          break;
        case 1:    /* Constants with IN operators */
          assert( seenIn );
          pIdxCons->usable = (pTerm->prereqRight==0);
          break;
        case 2:    /* Variables without IN */
          assert( seenVar );
          pIdxCons->usable = (pTerm->eOperator & WO_IN)==0;
          break;
        default:   /* Variables with IN */
          assert( seenVar && seenIn );
          pIdxCons->usable = 1;
          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;
    pIdxInfo->idxNum = 0;
    pIdxInfo->needToFreeIdxStr = 0;
    pIdxInfo->orderByConsumed = 0;
    pIdxInfo->estimatedCost = SQLITE_BIG_DBL / (double)2;

# 2014 January 4
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#*************************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script is testing the FTS3 module.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set ::testprefix fts3join

# If SQLITE_ENABLE_FTS3 is defined, omit this file.
ifcapable !fts3 {
  finish_test
  return
}

do_execsql_test 1.0 {
  CREATE VIRTUAL TABLE ft1 USING fts4(x);
  INSERT INTO ft1 VALUES('aaa aaa');
  INSERT INTO ft1 VALUES('aaa bbb');
  INSERT INTO ft1 VALUES('bbb aaa');
  INSERT INTO ft1 VALUES('bbb bbb');

  CREATE TABLE t1(id, y);
  INSERT INTO t1 VALUES(1, 'aaa');
  INSERT INTO t1 VALUES(2, 'bbb');
}

do_execsql_test 1.1 {
  SELECT docid FROM ft1, t1 WHERE ft1 MATCH y AND id=1;
} {1 2 3}

do_execsql_test 1.2 {
  SELECT docid FROM ft1, t1 WHERE ft1 MATCH y AND id=1 ORDER BY docid;
} {1 2 3}

do_execsql_test 2.0 {
  CREATE VIRTUAL TABLE ft2 USING fts4(x);
  CREATE VIRTUAL TABLE ft3 USING fts4(y);

  INSERT INTO ft2 VALUES('abc');
  INSERT INTO ft2 VALUES('def');
  INSERT INTO ft3 VALUES('ghi');
  INSERT INTO ft3 VALUES('abc');
}

do_execsql_test 2.1 { SELECT * FROM ft2, ft3 WHERE x MATCH y; } {abc abc}
do_execsql_test 2.2 { SELECT * FROM ft2, ft3 WHERE y MATCH x; } {abc abc}
do_execsql_test 2.3 { SELECT * FROM ft3, ft2 WHERE x MATCH y; } {abc abc}
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}}

finish_test