** "=", "<", ">", "<=", ">=", "IN", and "IS NULL"
*/
static int allowedOp(int op){
  assert( TK_GT>TK_EQ && TK_GT<TK_GE );
  assert( TK_LT>TK_EQ && TK_LT<TK_GE );
  assert( TK_LE>TK_EQ && TK_LE<TK_GE );
  assert( TK_GE==TK_EQ+4 );
  return op==TK_IN || (op>=TK_EQ && op<=TK_GE) || op==TK_ISNULL || op==TK_IS;
}

/*
** Commute a comparison operator.  Expressions of the form "X op Y"
** are converted into "Y op X".
**
** If left/right precedence rules come into play when determining the

static u16 operatorMask(int op){
  u16 c;
  assert( allowedOp(op) );
  if( op==TK_IN ){
    c = WO_IN;
  }else if( op==TK_ISNULL ){
    c = WO_ISNULL;
  }else if( op==TK_IS ){
    c = WO_IS;
  }else{
    assert( (WO_EQ<<(op-TK_EQ)) < 0x7fff );
    c = (u16)(WO_EQ<<(op-TK_EQ));
  }
  assert( op!=TK_ISNULL || c==WO_ISNULL );
  assert( op!=TK_IN || c==WO_IN );
  assert( op!=TK_EQ || c==WO_EQ );
  assert( op!=TK_LT || c==WO_LT );
  assert( op!=TK_LE || c==WO_LE );
  assert( op!=TK_GT || c==WO_GT );
  assert( op!=TK_GE || c==WO_GE );
  assert( op!=TK_IS || c==WO_IS );
  return c;
}

/*
** Advance to the next WhereTerm that matches according to the criteria
** established when the pScan object was initialized by whereScanInit().
** Return NULL if there are no more matching WhereTerms.

              pColl = sqlite3BinaryCompareCollSeq(pParse,
                                                  pX->pLeft, pX->pRight);
              if( pColl==0 ) pColl = pParse->db->pDfltColl;
              if( sqlite3StrICmp(pColl->zName, pScan->zCollName) ){
                continue;
              }
            }
            if( (pTerm->eOperator & (WO_EQ|WO_IS))!=0
             && (pX = pTerm->pExpr->pRight)->op==TK_COLUMN
             && pX->iTable==pScan->aEquiv[0]
             && pX->iColumn==pScan->aEquiv[1]
            ){
              testcase( pTerm->eOperator & WO_IS );
              continue;
            }
            pScan->k = k+1;
            return pTerm;
          }
        }
      }

  Index *pIdx           /* Must be compatible with this index, if not NULL */
){
  WhereTerm *pResult = 0;
  WhereTerm *p;
  WhereScan scan;

  p = whereScanInit(&scan, pWC, iCur, iColumn, op, pIdx);
  op &= WO_EQ|WO_IS;
  while( p ){
    if( (p->prereqRight & notReady)==0 ){
      if( p->prereqRight==0 && (p->eOperator&op)!=0 ){
        testcase( p->eOperator & WO_IS );
        return p;
      }
      if( pResult==0 ) pResult = p;
    }
    p = whereScanNext(&scan);
  }
  return pResult;

    Expr *pRight = sqlite3ExprSkipCollate(pExpr->pRight);
    u16 opMask = (pTerm->prereqRight & prereqLeft)==0 ? WO_ALL : WO_EQUIV;
    if( pLeft->op==TK_COLUMN ){
      pTerm->leftCursor = pLeft->iTable;
      pTerm->u.leftColumn = pLeft->iColumn;
      pTerm->eOperator = operatorMask(op) & opMask;
    }
    if( op==TK_IS ) pTerm->wtFlags |= TERM_IS;
    if( pRight && pRight->op==TK_COLUMN ){
      WhereTerm *pNew;
      Expr *pDup;
      u16 eExtraOp = 0;        /* Extra bits for pNew->eOperator */
      if( pTerm->leftCursor>=0 ){
        int idxNew;
        pDup = sqlite3ExprDup(db, pExpr, 0);
        if( db->mallocFailed ){
          sqlite3ExprDelete(db, pDup);
          return;
        }
        idxNew = whereClauseInsert(pWC, pDup, TERM_VIRTUAL|TERM_DYNAMIC);
        if( idxNew==0 ) return;
        pNew = &pWC->a[idxNew];
        markTermAsChild(pWC, idxNew, idxTerm);
        pTerm = &pWC->a[idxTerm];
        pTerm->wtFlags |= TERM_COPIED;
        if( (op==TK_EQ || op==TK_IS)
         && !ExprHasProperty(pExpr, EP_FromJoin)
         && OptimizationEnabled(db, SQLITE_Transitive)
        ){
          pTerm->eOperator |= WO_EQUIV;
          eExtraOp = WO_EQUIV;
        }
        if( op==TK_IS ) pNew->wtFlags |= TERM_IS;
      }else{
        pDup = pExpr;
        pNew = pTerm;
      }
      exprCommute(pParse, pDup);
      pLeft = sqlite3ExprSkipCollate(pDup->pLeft);
      pNew->leftCursor = pLeft->iTable;

#ifdef SQLITE_ENABLE_STAT3_OR_STAT4
  /* When sqlite_stat3 histogram data is available an operator of the
  ** form "x IS NOT NULL" can sometimes be evaluated more efficiently
  ** as "x>NULL" if x is not an INTEGER PRIMARY KEY.  So construct a
  ** virtual term of that form.
  **
  ** Note that the virtual term must be tagged with TERM_VNULL.



  */
  if( pExpr->op==TK_NOTNULL
   && pExpr->pLeft->op==TK_COLUMN
   && pExpr->pLeft->iColumn>=0
   && OptimizationEnabled(db, SQLITE_Stat34)
  ){
    Expr *pNewExpr;

static int termCanDriveIndex(
  WhereTerm *pTerm,              /* WHERE clause term to check */
  struct SrcList_item *pSrc,     /* Table we are trying to access */
  Bitmask notReady               /* Tables in outer loops of the join */
){
  char aff;
  if( pTerm->leftCursor!=pSrc->iCursor ) return 0;
  if( (pTerm->eOperator & (WO_EQ|WO_IS))==0 ) return 0;
  if( (pTerm->prereqRight & notReady)!=0 ) return 0;
  if( pTerm->u.leftColumn<0 ) return 0;
  aff = pSrc->pTab->aCol[pTerm->u.leftColumn].affinity;
  if( !sqlite3IndexAffinityOk(pTerm->pExpr, aff) ) return 0;
  testcase( pTerm->pExpr->op==TK_IS );
  return 1;
}
#endif


#ifndef SQLITE_OMIT_AUTOMATIC_INDEX
/*

  /* Count the number of possible WHERE clause constraints referring
  ** to this virtual table */
  for(i=nTerm=0, pTerm=pWC->a; i<pWC->nTerm; i++, pTerm++){
    if( pTerm->leftCursor != pSrc->iCursor ) continue;
    assert( IsPowerOfTwo(pTerm->eOperator & ~WO_EQUIV) );
    testcase( pTerm->eOperator & WO_IN );
    testcase( pTerm->eOperator & WO_ISNULL );
    testcase( pTerm->eOperator & WO_IS );
    testcase( pTerm->eOperator & WO_ALL );
    if( (pTerm->eOperator & ~(WO_ISNULL|WO_EQUIV|WO_IS))==0 ) continue;
    if( pTerm->wtFlags & TERM_VNULL ) continue;
    nTerm++;
  }

  /* If the ORDER BY clause contains only columns in the current 
  ** virtual table then allocate space for the aOrderBy part of
  ** the sqlite3_index_info structure.

                                                                   pUsage;

  for(i=j=0, pTerm=pWC->a; i<pWC->nTerm; i++, pTerm++){
    u8 op;
    if( pTerm->leftCursor != pSrc->iCursor ) continue;
    assert( IsPowerOfTwo(pTerm->eOperator & ~WO_EQUIV) );
    testcase( pTerm->eOperator & WO_IN );
    testcase( pTerm->eOperator & WO_IS );
    testcase( pTerm->eOperator & WO_ISNULL );
    testcase( pTerm->eOperator & WO_ALL );
    if( (pTerm->eOperator & ~(WO_ISNULL|WO_EQUIV|WO_IS))==0 ) continue;
    if( pTerm->wtFlags & TERM_VNULL ) continue;
    pIdxCons[j].iColumn = pTerm->u.leftColumn;
    pIdxCons[j].iTermOffset = i;
    op = (u8)pTerm->eOperator & WO_ALL;
    if( op==WO_IN ) op = WO_EQ;
    pIdxCons[j].op = op;
    /* The direct assignment in the previous line is possible only because

  int iTarget         /* Attempt to leave results in this register */
){
  Expr *pX = pTerm->pExpr;
  Vdbe *v = pParse->pVdbe;
  int iReg;                  /* Register holding results */

  assert( iTarget>0 );
  if( pX->op==TK_EQ || pX->op==TK_IS ){
    iReg = sqlite3ExprCodeTarget(pParse, pX->pRight, iTarget);
  }else if( pX->op==TK_ISNULL ){
    iReg = iTarget;
    sqlite3VdbeAddOp2(v, OP_Null, 0, iReg);
#ifndef SQLITE_OMIT_SUBQUERY
  }else{
    int eType;

        sqlite3VdbeAddOp2(v, OP_SCopy, r1, regBase+j);
      }
    }
    testcase( pTerm->eOperator & WO_ISNULL );
    testcase( pTerm->eOperator & WO_IN );
    if( (pTerm->eOperator & (WO_ISNULL|WO_IN))==0 ){
      Expr *pRight = pTerm->pExpr->pRight;
      if( (pTerm->wtFlags & TERM_IS)==0 && sqlite3ExprCanBeNull(pRight) ){
        sqlite3VdbeAddOp2(v, OP_IsNull, regBase+j, pLevel->addrBrk);
        VdbeCoverage(v);
      }
      if( zAff ){
        if( sqlite3CompareAffinity(pRight, zAff[j])==SQLITE_AFF_NONE ){
          zAff[j] = SQLITE_AFF_NONE;
        }

  ** then we cannot use the "t1.a=t2.b" constraint, but we can code
  ** the implied "t1.a=123" constraint.
  */
  for(pTerm=pWC->a, j=pWC->nTerm; j>0; j--, pTerm++){
    Expr *pE, *pEAlt;
    WhereTerm *pAlt;
    if( pTerm->wtFlags & (TERM_VIRTUAL|TERM_CODED) ) continue;
    if( (pTerm->eOperator & (WO_EQ|WO_IS))==0 ) continue;
    if( (pTerm->eOperator & WO_EQUIV)==0 ) continue;
    if( pTerm->leftCursor!=iCur ) continue;
    if( pLevel->iLeftJoin ) continue;
    pE = pTerm->pExpr;
    assert( !ExprHasProperty(pE, EP_FromJoin) );
    assert( (pTerm->prereqRight & pLevel->notReady)!=0 );
    pAlt = findTerm(pWC, iCur, pTerm->u.leftColumn, notReady,
                    WO_EQ|WO_IN|WO_IS, 0);
    if( pAlt==0 ) continue;
    if( pAlt->wtFlags & (TERM_CODED) ) continue;
    testcase( pAlt->eOperator & WO_EQ );
    testcase( pAlt->eOperator & WO_IS );
    testcase( pAlt->eOperator & WO_IN );
    VdbeModuleComment((v, "begin transitive constraint"));
    pEAlt = sqlite3StackAllocRaw(db, sizeof(*pEAlt));
    if( pEAlt ){
      *pEAlt = *pAlt->pExpr;
      pEAlt->pLeft = pE->pLeft;
      sqlite3ExprIfFalse(pParse, pEAlt, addrCont, SQLITE_JUMPIFNULL);

    sqlite3DebugPrintf("TERM-%-3d NULL\n", iTerm);
  }else{
    char zType[4];
    memcpy(zType, "...", 4);
    if( pTerm->wtFlags & TERM_VIRTUAL ) zType[0] = 'V';
    if( pTerm->eOperator & WO_EQUIV  ) zType[1] = 'E';
    if( ExprHasProperty(pTerm->pExpr, EP_FromJoin) ) zType[2] = 'L';
    sqlite3DebugPrintf(
       "TERM-%-3d %p %s cursor=%-3d prob=%-3d op=0x%03x wtFlags=0x%04x\n",
       iTerm, pTerm, zType, pTerm->leftCursor, pTerm->truthProb,
       pTerm->eOperator, pTerm->wtFlags);
    sqlite3TreeViewExpr(0, pTerm->pExpr, 0);
  }
}
#endif

#ifdef WHERETRACE_ENABLED
/*

        /* If a truth probability is specified using the likelihood() hints,
        ** then use the probability provided by the application. */
        pLoop->nOut += pTerm->truthProb;
      }else{
        /* In the absence of explicit truth probabilities, use heuristics to
        ** guess a reasonable truth probability. */
        pLoop->nOut--;
        if( pTerm->eOperator&(WO_EQ|WO_IS) ){
          Expr *pRight = pTerm->pExpr->pRight;
          testcase( pTerm->pExpr->op==TK_IS );
          if( sqlite3ExprIsInteger(pRight, &k) && k>=(-1) && k<=1 ){
            k = 10;
          }else{
            k = 20;
          }
          if( iReduce<k ) iReduce = k;
        }

  pNew = pBuilder->pNew;
  if( db->mallocFailed ) return SQLITE_NOMEM;

  assert( (pNew->wsFlags & WHERE_VIRTUALTABLE)==0 );
  assert( (pNew->wsFlags & WHERE_TOP_LIMIT)==0 );
  if( pNew->wsFlags & WHERE_BTM_LIMIT ){
    opMask = WO_LT|WO_LE;
  }else if( /*pProbe->tnum<=0 ||*/ (pSrc->jointype & JT_LEFT)!=0 ){
    opMask = WO_EQ|WO_IN|WO_GT|WO_GE|WO_LT|WO_LE;
  }else{
    opMask = WO_EQ|WO_IN|WO_GT|WO_GE|WO_LT|WO_LE|WO_ISNULL|WO_IS;
  }
  if( pProbe->bUnordered ) opMask &= ~(WO_GT|WO_GE|WO_LT|WO_LE);

  assert( pNew->u.btree.nEq<pProbe->nColumn );
  iCol = pProbe->aiColumn[pNew->u.btree.nEq];

  pTerm = whereScanInit(&scan, pBuilder->pWC, pSrc->iCursor, iCol,

      }else if( ALWAYS(pExpr->x.pList && pExpr->x.pList->nExpr) ){
        /* "x IN (value, value, ...)" */
        nIn = sqlite3LogEst(pExpr->x.pList->nExpr);
      }
      assert( nIn>0 );  /* RHS always has 2 or more terms...  The parser
                        ** changes "x IN (?)" into "x=?". */

    }else if( eOp & (WO_EQ|WO_IS) ){
      pNew->wsFlags |= WHERE_COLUMN_EQ;
      if( iCol<0 || (nInMul==0 && pNew->u.btree.nEq==pProbe->nKeyCol-1) ){
        if( iCol>=0 && pProbe->uniqNotNull==0 ){
          pNew->wsFlags |= WHERE_UNQ_WANTED;
        }else{
          pNew->wsFlags |= WHERE_ONEROW;
        }

    assert( pNew->nOut==saved_nOut );
    if( pNew->wsFlags & WHERE_COLUMN_RANGE ){
      /* Adjust nOut using stat3/stat4 data. Or, if there is no stat3/stat4
      ** data, using some other estimate.  */
      whereRangeScanEst(pParse, pBuilder, pBtm, pTop, pNew);
    }else{
      int nEq = ++pNew->u.btree.nEq;
      assert( eOp & (WO_ISNULL|WO_EQ|WO_IN|WO_IS) );

      assert( pNew->nOut==saved_nOut );
      if( pTerm->truthProb<=0 && iCol>=0 ){
        assert( (eOp & WO_IN) || nIn==0 );
        testcase( eOp & WO_IN );
        pNew->nOut += pTerm->truthProb;
        pNew->nOut -= nIn;
      }else{
#ifdef SQLITE_ENABLE_STAT3_OR_STAT4
        tRowcnt nOut = 0;
        if( nInMul==0 
         && pProbe->nSample 
         && pNew->u.btree.nEq<=pProbe->nSampleCol
         && ((eOp & WO_IN)==0 || !ExprHasProperty(pTerm->pExpr, EP_xIsSelect))
        ){
          Expr *pExpr = pTerm->pExpr;
          if( (eOp & (WO_EQ|WO_ISNULL|WO_IS))!=0 ){
            testcase( eOp & WO_EQ );
            testcase( eOp & WO_IS );
            testcase( eOp & WO_ISNULL );
            rc = whereEqualScanEst(pParse, pBuilder, pExpr->pRight, &nOut);
          }else{
            rc = whereInScanEst(pParse, pBuilder, pExpr->x.pList, &nOut);
          }
          if( rc==SQLITE_NOTFOUND ) rc = SQLITE_OK;
          if( rc!=SQLITE_OK ) break;          /* Jump out of the pTerm loop */

    */
    for(i=0; i<nOrderBy; i++){
      if( MASKBIT(i) & obSat ) continue;
      pOBExpr = sqlite3ExprSkipCollate(pOrderBy->a[i].pExpr);
      if( pOBExpr->op!=TK_COLUMN ) continue;
      if( pOBExpr->iTable!=iCur ) continue;
      pTerm = findTerm(&pWInfo->sWC, iCur, pOBExpr->iColumn,
                       ~ready, WO_EQ|WO_ISNULL|WO_IS, 0);
      if( pTerm==0 ) continue;
      if( (pTerm->eOperator&(WO_EQ|WO_IS))!=0 && pOBExpr->iColumn>=0 ){
        const char *z1, *z2;
        pColl = sqlite3ExprCollSeq(pWInfo->pParse, pOrderBy->a[i].pExpr);
        if( !pColl ) pColl = db->pDfltColl;
        z1 = pColl->zName;
        pColl = sqlite3ExprCollSeq(pWInfo->pParse, pTerm->pExpr);
        if( !pColl ) pColl = db->pDfltColl;
        z2 = pColl->zName;
        if( sqlite3StrICmp(z1, z2)!=0 ) continue;
        testcase( pTerm->pExpr->op==TK_IS );
      }
      obSat |= MASKBIT(i);
    }

    if( (pLoop->wsFlags & WHERE_ONEROW)==0 ){
      if( pLoop->wsFlags & WHERE_IPK ){
        pIndex = 0;

      distinctColumns = 0;
      for(j=0; j<nColumn; j++){
        u8 bOnce;   /* True to run the ORDER BY search loop */

        /* Skip over == and IS NULL terms */
        if( j<pLoop->u.btree.nEq
         && pLoop->nSkip==0
         && ((i = pLoop->aLTerm[j]->eOperator) & (WO_EQ|WO_ISNULL|WO_IS))!=0
        ){
          if( i & WO_ISNULL ){
            testcase( isOrderDistinct );
            isOrderDistinct = 0;
          }
          continue;  
        }

  if( IsVirtual(pTab) ) return 0;
  if( pItem->zIndex ) return 0;
  iCur = pItem->iCursor;
  pWC = &pWInfo->sWC;
  pLoop = pBuilder->pNew;
  pLoop->wsFlags = 0;
  pLoop->nSkip = 0;
  pTerm = findTerm(pWC, iCur, -1, 0, WO_EQ|WO_IS, 0);
  if( pTerm ){
    testcase( pTerm->eOperator & WO_IS );
    pLoop->wsFlags = WHERE_COLUMN_EQ|WHERE_IPK|WHERE_ONEROW;
    pLoop->aLTerm[0] = pTerm;
    pLoop->nLTerm = 1;
    pLoop->u.btree.nEq = 1;
    /* TUNING: Cost of a rowid lookup is 10 */
    pLoop->rRun = 33;  /* 33==sqlite3LogEst(10) */
  }else{
    for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
      assert( pLoop->aLTermSpace==pLoop->aLTerm );
      if( !IsUniqueIndex(pIdx)
       || pIdx->pPartIdxWhere!=0 
       || pIdx->nKeyCol>ArraySize(pLoop->aLTermSpace) 
      ) continue;
      for(j=0; j<pIdx->nKeyCol; j++){
        pTerm = findTerm(pWC, iCur, pIdx->aiColumn[j], 0, WO_EQ|WO_IS, pIdx);
        if( pTerm==0 ) break;
         testcase( pTerm->eOperator & WO_IS );
        pLoop->aLTerm[j] = pTerm;
      }
      if( j!=pIdx->nKeyCol ) continue;
      pLoop->wsFlags = WHERE_COLUMN_EQ|WHERE_ONEROW|WHERE_INDEXED;
      if( pIdx->isCovering || (pItem->colUsed & ~columnsInIndex(pIdx))==0 ){
        pLoop->wsFlags |= WHERE_IDX_ONLY;
      }

#  define TERM_VNULL    0x80   /* Manufactured x>NULL or x<=NULL term */
#else
#  define TERM_VNULL    0x00   /* Disabled if not using stat3 */
#endif
#define TERM_LIKEOPT    0x100  /* Virtual terms from the LIKE optimization */
#define TERM_LIKECOND   0x200  /* Conditionally this LIKE operator term */
#define TERM_LIKE       0x400  /* The original LIKE operator */
#define TERM_IS         0x800  /* Term.pExpr is an IS operator */

/*
** An instance of the WhereScan object is used as an iterator for locating
** terms in the WHERE clause that are useful to the query planner.
*/
struct WhereScan {
  WhereClause *pOrigWC;      /* Original, innermost WhereClause */

/*
** Bitmasks for the operators on WhereTerm objects.  These are all
** operators that are of interest to the query planner.  An
** OR-ed combination of these values can be used when searching for
** particular WhereTerms within a WhereClause.
*/
#define WO_IN     0x0001
#define WO_EQ     0x0002
#define WO_LT     (WO_EQ<<(TK_LT-TK_EQ))
#define WO_LE     (WO_EQ<<(TK_LE-TK_EQ))
#define WO_GT     (WO_EQ<<(TK_GT-TK_EQ))
#define WO_GE     (WO_EQ<<(TK_GE-TK_EQ))
#define WO_MATCH  0x0040
#define WO_IS     0x0080
#define WO_ISNULL 0x0100
#define WO_OR     0x0200       /* Two or more OR-connected terms */
#define WO_AND    0x0400       /* Two or more AND-connected terms */
#define WO_EQUIV  0x0800       /* Of the form A==B, both columns */
#define WO_NOOP   0x1000       /* This term does not restrict search space */

#define WO_ALL    0x1fff       /* Mask of all possible WO_* values */
#define WO_SINGLE 0x01ff       /* Mask of all non-compound WO_* values */

/*
** These are definitions of bits in the WhereLoop.wsFlags field.
** The particular combination of bits in each WhereLoop help to
** determine the algorithm that WhereLoop represents.
*/
#define WHERE_COLUMN_EQ    0x00000001  /* x=EXPR */

   ORDER BY +w;
} {1 2 1 3 3 4 3 6 5 6 5 7}
do_execsql_test transitive1-301 {
  SELECT *
    FROM t301 CROSS JOIN t302
   WHERE w=y AND y IS NOT NULL
   ORDER BY w;
} {1 2 1 3 3 4 3 6 5 6 5 7}
do_execsql_test transitive1-302 {
  SELECT *
    FROM t301 CROSS JOIN t302
   WHERE w IS y AND y IS NOT NULL
   ORDER BY w;
} {1 2 1 3 3 4 3 6 5 6 5 7}
do_execsql_test transitive1-310 {
  SELECT *
    FROM t301 CROSS JOIN t302 ON w=y
   WHERE y>1
   ORDER BY +w
} {3 4 3 6 5 6 5 7}

  SELECT *
    FROM t301 CROSS JOIN t302 ON w=y
   WHERE y BETWEEN 1 AND 4
   ORDER BY w DESC;
} {3 4 3 6 1 2 1 3}

# Ticket [c620261b5b5dc] circa 2013-10-28.
# Make sure constraints are not used with LEFT JOINs.
#
# The next case is from the ticket report.  It outputs no rows in 3.8.1
# prior to the bug-fix.
#
do_execsql_test transitive1-400 {
  CREATE TABLE t401(a);
  CREATE TABLE t402(b);
  CREATE TABLE t403(c INTEGER PRIMARY KEY);
  INSERT INTO t401 VALUES(1);
  INSERT INTO t403 VALUES(1);
  SELECT '1-row' FROM t401 LEFT JOIN t402 ON b=a JOIN t403 ON c=a;
} {1-row}
do_execsql_test transitive1-401 {
  SELECT '1-row' FROM t401 LEFT JOIN t402 ON b IS a JOIN t403 ON c=a;
} {1-row}
do_execsql_test transitive1-402 {
  SELECT '1-row' FROM t401 LEFT JOIN t402 ON b=a JOIN t403 ON c IS a;
} {1-row}
do_execsql_test transitive1-403 {
  SELECT '1-row' FROM t401 LEFT JOIN t402 ON b IS a JOIN t403 ON c IS a;
} {1-row}


# The following is a script distilled from the XBMC project where the
# bug was originally encountered.  The correct answer is a single row
# of output.  Before the bug was fixed, zero rows were generated.
#
do_execsql_test transitive1-410 {
  CREATE TABLE bookmark ( idBookmark integer primary key, idFile integer, timeInSeconds double, totalTimeInSeconds double, thumbNailImage text, player text, playerState text, type integer);

} {{} 15 16}
do_test vtab1.13-3 {
  execsql { 
    INSERT INTO c VALUES(15, NULL, 16);
    SELECT * FROM echo_c WHERE b IS NULL 
  }
} {15 {} 16}
do_test vtab1.13-4 {
  unset -nocomplain null
  execsql { 
    SELECT * FROM echo_c WHERE b IS $null
  }
} {15 {} 16}
do_test vtab1.13-5 {
  execsql { 
    SELECT * FROM echo_c WHERE b IS NULL AND a = 15;
  }
} {15 {} 16}
do_test vtab1.13-6 {
  execsql { 
    SELECT * FROM echo_c WHERE NULL IS b AND a IS 15;
  }
} {15 {} 16}


do_test vtab1-14.001 {
  execsql {SELECT rowid, * FROM echo_c WHERE +rowid IN (1,2,3)}
} {1 3 G H 2 {} 15 16 3 15 {} 16}
do_test vtab1-14.002 {

do_test vtab2-3.2 {
  execsql {
    SELECT *, b.rowid
      FROM schema a LEFT JOIN schema b ON a.dflt_value=b.dflt_value
     WHERE a.rowid=1
  }
} {main schema 0 database {} 0 {} 0 {} {} {} {} {} {} {} {} {}}
do_test vtab2-3.3 {
  execsql {
    SELECT *, b.rowid
      FROM schema a LEFT JOIN schema b ON a.dflt_value IS b.dflt_value
                                      AND a.dflt_value IS NOT NULL
     WHERE a.rowid=1
  }
} {main schema 0 database {} 0 {} 0 {} {} {} {} {} {} {} {} {}}

do_test vtab2-4.1 {
  execsql {
    BEGIN TRANSACTION;
    CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(b, c));
    CREATE TABLE fkey(
      to_tbl,

    sqlite3_exec_hex db { CREATE VIRTUAL TABLE %C9 USING s }
  } {/1 {malformed database schema.* already exists}/}
}



finish_test

    SELECT * FROM t1 NATURAL RIGHT OUTER JOIN t2;
  }
} {1 {RIGHT and FULL OUTER JOINs are not currently supported}}
do_test vtab6-2.4 {
  execsql {
    SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.d
  }
} {1 2 3 {} {} {} 2 3 4 {} {} {} 3 4 5 1 2 3}
do_test vtab6-2.4.1 {
  execsql {
    SELECT * FROM t1 LEFT JOIN t2 ON t1.a IS t2.d
  }
} {1 2 3 {} {} {} 2 3 4 {} {} {} 3 4 5 1 2 3}
do_test vtab6-2.5 {
  execsql {
    SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.d WHERE t1.a>1
  }
} {2 3 4 {} {} {} 3 4 5 1 2 3}
do_test vtab6-2.6 {

# "sqlite_search_count" which tallys the number of executions of MoveTo
# and Next operators in the VDBE.  By verifing that the search count is
# small we can be assured that indices are being used properly.
#
do_test where-1.1.1 {
  count {SELECT x, y, w FROM t1 WHERE w=10}
} {3 121 10 3}
do_test where-1.1.1b {
  count {SELECT x, y, w FROM t1 WHERE w IS 10}
} {3 121 10 3}
do_eqp_test where-1.1.2 {
  SELECT x, y, w FROM t1 WHERE w=10
} {*SEARCH TABLE t1 USING INDEX i1w (w=?)*}
do_eqp_test where-1.1.2b {
  SELECT x, y, w FROM t1 WHERE w IS 10
} {*SEARCH TABLE t1 USING INDEX i1w (w=?)*}
do_test where-1.1.3 {
  db status step
} {0}
do_test where-1.1.4 {
  db eval {SELECT x, y, w FROM t1 WHERE +w=10}
} {3 121 10}
do_test where-1.1.5 {

} {3 144 11 3}
do_test where-1.3.1 {
  count {SELECT x, y, w AS abc FROM t1 WHERE 11=w}
} {3 144 11 3}
do_test where-1.3.2 {
  count {SELECT x, y, w AS abc FROM t1 WHERE 11=abc}
} {3 144 11 3}
do_test where-1.3.3 {
  count {SELECT x, y, w AS abc FROM t1 WHERE 11 IS abc}
} {3 144 11 3}
do_test where-1.4.1 {
  count {SELECT w, x, y FROM t1 WHERE 11=w AND x>2}
} {11 3 144 3}
do_test where-1.4.1b {
  count {SELECT w, x, y FROM t1 WHERE 11 IS w AND x>2}
} {11 3 144 3}
do_eqp_test where-1.4.2 {
  SELECT w, x, y FROM t1 WHERE 11=w AND x>2
} {*SEARCH TABLE t1 USING INDEX i1w (w=?)*}
do_eqp_test where-1.4.2b {
  SELECT w, x, y FROM t1 WHERE 11 IS w AND x>2
} {*SEARCH TABLE t1 USING INDEX i1w (w=?)*}
do_test where-1.4.3 {
  count {SELECT w AS a, x AS b, y FROM t1 WHERE 11=a AND b>2}
} {11 3 144 3}
do_eqp_test where-1.4.4 {
  SELECT w AS a, x AS b, y FROM t1 WHERE 11=a AND b>2
} {*SEARCH TABLE t1 USING INDEX i1w (w=?)*}
do_test where-1.5 {

} {3 144 3}
do_test where-1.10 {
  count {SELECT x, y FROM t1 WHERE x=3 AND w>=10 AND y=121}
} {3 121 3}
do_test where-1.11 {
  count {SELECT x, y FROM t1 WHERE x=3 AND y=100 AND w<10}
} {3 100 3}
do_test where-1.11b {
  count {SELECT x, y FROM t1 WHERE x IS 3 AND y IS 100 AND w<10}
} {3 100 3}

# New for SQLite version 2.1: Verify that that inequality constraints
# are used correctly.
#
do_test where-1.12 {
  count {SELECT w FROM t1 WHERE x=3 AND y<100}
} {8 3}
do_test where-1.12b {
  count {SELECT w FROM t1 WHERE x IS 3 AND y<100}
} {8 3}
do_test where-1.13 {
  count {SELECT w FROM t1 WHERE x=3 AND 100>y}
} {8 3}
do_test where-1.14 {
  count {SELECT w FROM t1 WHERE 3=x AND y<100}
} {8 3}
do_test where-1.14b {
  count {SELECT w FROM t1 WHERE 3 IS x AND y<100}
} {8 3}
do_test where-1.15 {
  count {SELECT w FROM t1 WHERE 3=x AND 100>y}
} {8 3}
do_test where-1.16 {
  count {SELECT w FROM t1 WHERE x=3 AND y<=100}
} {8 9 5}
do_test where-1.17 {
  count {SELECT w FROM t1 WHERE x=3 AND 100>=y}
} {8 9 5}
do_test where-1.18 {
  count {SELECT w FROM t1 WHERE x=3 AND y>225}
} {15 3}
do_test where-1.18b {
  count {SELECT w FROM t1 WHERE x IS 3 AND y>225}
} {15 3}
do_test where-1.19 {
  count {SELECT w FROM t1 WHERE x=3 AND 225<y}
} {15 3}
do_test where-1.20 {
  count {SELECT w FROM t1 WHERE x=3 AND y>=225}
} {14 15 5}
do_test where-1.21 {
  count {SELECT w FROM t1 WHERE x=3 AND 225<=y}
} {14 15 5}
do_test where-1.22 {
  count {SELECT w FROM t1 WHERE x=3 AND y>121 AND y<196}
} {11 12 5}
do_test where-1.22b {
  count {SELECT w FROM t1 WHERE x IS 3 AND y>121 AND y<196}
} {11 12 5}
do_test where-1.23 {
  count {SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<=196}
} {10 11 12 13 9}
do_test where-1.24 {
  count {SELECT w FROM t1 WHERE x=3 AND 121<y AND 196>y}
} {11 12 5}
do_test where-1.25 {

# "sqlite_search_count" which tallys the number of executions of MoveTo
# and Next operators in the VDBE.  By verifing that the search count is
# small we can be assured that indices are being used properly.
#
do_test where4-1.1 {
  count {SELECT rowid FROM t1 WHERE w IS NULL}
} {7 2}
do_test where4-1.1b {
  unset -nocomplain null
  count {SELECT rowid FROM t1 WHERE w IS $null}
} {7 2}
do_test where4-1.2 {
  count {SELECT rowid FROM t1 WHERE +w IS NULL}
} {7 6}
do_test where4-1.3 {
  count {SELECT rowid FROM t1 WHERE w=1 AND x IS NULL}
} {2 2}
do_test where4-1.4 {

    SELECT * FROM t2 LEFT JOIN t3 ON a=x WHERE +y IS NULL;
  }
} {2 2 {} 3 {} {}}
do_test where4-3.2 {
  execsql {
    SELECT * FROM t2 LEFT JOIN t3 ON a=x WHERE y IS NULL;
  }
} {2 2 {} 3 {} {}}
do_test where4-3.3 {
  execsql {
    SELECT * FROM t2 LEFT JOIN t3 ON a=x WHERE NULL is y;
  }
} {2 2 {} 3 {} {}}
do_test where4-3.4 {
  unset -nocomplain null
  execsql {
    SELECT * FROM t2 LEFT JOIN t3 ON a=x WHERE y IS $null;
  }
} {2 2 {} 3 {} {}}

# Ticket #2189.  Probably the same bug as #2177.
#
do_test where4-4.1 {
  execsql {
    CREATE TABLE test(col1 TEXT PRIMARY KEY);

  8   "SELECT i FROM t1 WHERE a=2 AND b=2 AND i BETWEEN 10 AND 12" {10 11 12}
  9   "SELECT i FROM t1 WHERE a=2 AND b=2 AND i BETWEEN 11 AND 12" {11 12}
 10   "SELECT i FROM t1 WHERE a=2 AND b=2 AND i BETWEEN 10 AND 11" {10 11}
 11   "SELECT i FROM t1 WHERE a=2 AND b=2 AND i BETWEEN 12 AND 10" {}
 12   "SELECT i FROM t1 WHERE a=2 AND b=2 AND i<NULL"      {}
 13   "SELECT i FROM t1 WHERE a=2 AND b=2 AND i>=NULL"     {}
 14   "SELECT i FROM t1 WHERE a=1 AND b='2' AND i<4.5"     {3 4}
 15   "SELECT i FROM t1 WHERE rowid IS '12'"               {12}
} {
  do_execsql_test 1.$tn.1 $sql $res
  do_execsql_test 1.$tn.2 "$sql ORDER BY i ASC"  [lsort -integer -inc  $res]
  do_execsql_test 1.$tn.3 "$sql ORDER BY i DESC" [lsort -integer -dec  $res]
}