SQLite

**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This module contains C code that generates VDBE code used to process
** the WHERE clause of SQL statements.
**
** $Id: where.c,v 1.110 2004/07/19 19:14:01 drh Exp $
*/
#include "sqliteInt.h"

/*
** The query generator uses an array of instances of this structure to
** help it analyze the subexpressions of the WHERE clause.  Each WHERE
** clause subexpression is separated from the others by an AND operator.

    }
  }
  if( pMatch && pbRev ){
    *pbRev = sortOrder==SQLITE_SO_DESC;
  }
  return pMatch;
}

/*
** Disable a term in the WHERE clause.  Except, do not disable the term
** if it controls a LEFT OUTER JOIN and it did not originate in the ON
** or USING clause of that join.
**
** Consider the term t2.z='ok' in the following queries:
**
**   (1)  SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.x WHERE t2.z='ok'
**   (2)  SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.x AND t2.z='ok'
**   (3)  SELECT * FROM t1, t2 WHERE t1.a=t2.x AND t2.z='ok'
**
** The t2.z='ok' is disabled in the in (2) because it did not originate
** in the ON clause.  The term is disabled in (3) because it is not part
** of a LEFT OUTER JOIN.  In (1), the term is not disabled.
**
** Disabling a term causes that term to not be tested in the inner loop
** of the join.  Disabling is an optimization.  We would get the correct
** results if nothing were ever disabled, but joins might run a little
** slower.  The trick is to disable as much as we can without disabling
** too much.  If we disabled in (1), we'd get the wrong answer.
** See ticket #813.
*/
static void disableTerm(WhereLevel *pLevel, Expr **ppExpr){
  Expr *pExpr = *ppExpr;
  if( pLevel->iLeftJoin==0 || ExprHasProperty(pExpr, EP_FromJoin) ){
    *ppExpr = 0;
  }
}

/*
** Generate the beginning of the loop used for WHERE clause processing.
** The return value is a pointer to an (opaque) structure that contains
** information needed to terminate the loop.  Later, the calling routine
** should invoke sqlite3WhereEnd() with the return value of this function
** in order to complete the WHERE clause processing.

          pLevel->inP2 = sqlite3VdbeAddOp(v, OP_IdxColumn, pX->iTable, 0);
          pLevel->inOp = OP_Next;
          pLevel->inP1 = pX->iTable;
        }
      }else{
        sqlite3ExprCode(pParse, aExpr[k].p->pLeft);
      }
      disableTerm(pLevel, &aExpr[k].p);
      cont = pLevel->cont = sqlite3VdbeMakeLabel(v);
      sqlite3VdbeAddOp(v, OP_MustBeInt, 1, brk);
      haveKey = 0;
      sqlite3VdbeAddOp(v, OP_NotExists, iCur, brk);
      pLevel->op = OP_Noop;
    }else if( pIdx!=0 && pLevel->score>0 && pLevel->score%4==0 ){
      /* Case 2:  There is an index and all terms of the WHERE clause that

             && (aExpr[k].prereqRight & loopMask)==aExpr[k].prereqRight 
             && pX->pLeft->iColumn==pIdx->aiColumn[j]
          ){
            char idxaff = pIdx->pTable->aCol[pX->pLeft->iColumn].affinity;
            if( sqlite3IndexAffinityOk(aExpr[k].p, idxaff) ){
              if( pX->op==TK_EQ ){
                sqlite3ExprCode(pParse, pX->pRight);
                disableTerm(pLevel, &aExpr[k].p);
                break;
              }
              if( pX->op==TK_IN && nColumn==1 ){
                sqlite3VdbeAddOp(v, OP_Rewind, pX->iTable, brk);
                sqlite3VdbeAddOp(v, OP_KeyAsData, pX->iTable, 1);
                pLevel->inP2 = sqlite3VdbeAddOp(v, OP_IdxColumn, pX->iTable, 0);
                pLevel->inOp = OP_Next;
                pLevel->inP1 = pX->iTable;
                disableTerm(pLevel, &aExpr[k].p);
                break;
              }
            }
          }
          if( aExpr[k].idxRight==iCur
             && aExpr[k].p->op==TK_EQ
             && (aExpr[k].prereqLeft & loopMask)==aExpr[k].prereqLeft
             && aExpr[k].p->pRight->iColumn==pIdx->aiColumn[j]
          ){
            char idxaff = pIdx->pTable->aCol[pX->pRight->iColumn].affinity;
            if( sqlite3IndexAffinityOk(aExpr[k].p, idxaff) ){
              sqlite3ExprCode(pParse, aExpr[k].p->pLeft);
              disableTerm(pLevel, &aExpr[k].p);
              break;
            }
          }
        }
      }
      pLevel->iMem = pParse->nMem++;
      cont = pLevel->cont = sqlite3VdbeMakeLabel(v);

          sqlite3ExprCode(pParse, aExpr[k].p->pRight);
        }else{
          sqlite3ExprCode(pParse, aExpr[k].p->pLeft);
        }
        sqlite3VdbeAddOp(v, OP_ForceInt,
          aExpr[k].p->op==TK_LT || aExpr[k].p->op==TK_GT, brk);
        sqlite3VdbeAddOp(v, OP_MoveGe, iCur, brk);
        disableTerm(pLevel, &aExpr[k].p);
      }else{
        sqlite3VdbeAddOp(v, OP_Rewind, iCur, brk);
      }
      if( iDirectLt[i]>=0 ){
        k = iDirectLt[i];
        assert( k<nExpr );
        assert( aExpr[k].p!=0 );

        pLevel->iMem = pParse->nMem++;
        sqlite3VdbeAddOp(v, OP_MemStore, pLevel->iMem, 1);
        if( aExpr[k].p->op==TK_LT || aExpr[k].p->op==TK_GT ){
          testOp = OP_Ge;
        }else{
          testOp = OP_Gt;
        }
        disableTerm(pLevel, &aExpr[k].p);
      }
      start = sqlite3VdbeCurrentAddr(v);
      pLevel->op = OP_Next;
      pLevel->p1 = iCur;
      pLevel->p2 = start;
      if( testOp!=OP_Noop ){
        sqlite3VdbeAddOp(v, OP_Recno, iCur, 0);

          if( aExpr[k].p==0 ) continue;
          if( aExpr[k].idxLeft==iCur
             && aExpr[k].p->op==TK_EQ
             && (aExpr[k].prereqRight & loopMask)==aExpr[k].prereqRight 
             && aExpr[k].p->pLeft->iColumn==pIdx->aiColumn[j]
          ){
            sqlite3ExprCode(pParse, aExpr[k].p->pRight);
            disableTerm(pLevel, &aExpr[k].p);
            break;
          }
          if( aExpr[k].idxRight==iCur
             && aExpr[k].p->op==TK_EQ
             && (aExpr[k].prereqLeft & loopMask)==aExpr[k].prereqLeft
             && aExpr[k].p->pRight->iColumn==pIdx->aiColumn[j]
          ){
            sqlite3ExprCode(pParse, aExpr[k].p->pLeft);
            disableTerm(pLevel, &aExpr[k].p);
            break;
          }
        }
      }

      /* Duplicate the equality term values because they will all be
      ** used twice: once to make the termination key and once to make the

          if( aExpr[k].idxLeft==iCur
             && (pExpr->op==TK_LT || pExpr->op==TK_LE)
             && (aExpr[k].prereqRight & loopMask)==aExpr[k].prereqRight 
             && pExpr->pLeft->iColumn==pIdx->aiColumn[j]
          ){
            sqlite3ExprCode(pParse, pExpr->pRight);
            leFlag = pExpr->op==TK_LE;
            disableTerm(pLevel, &aExpr[k].p);
            break;
          }
          if( aExpr[k].idxRight==iCur
             && (pExpr->op==TK_GT || pExpr->op==TK_GE)
             && (aExpr[k].prereqLeft & loopMask)==aExpr[k].prereqLeft
             && pExpr->pRight->iColumn==pIdx->aiColumn[j]
          ){
            sqlite3ExprCode(pParse, pExpr->pLeft);
            leFlag = pExpr->op==TK_GE;
            disableTerm(pLevel, &aExpr[k].p);
            break;
          }
        }
        testOp = OP_IdxGE;
      }else{
        testOp = nEqColumn>0 ? OP_IdxGE : OP_Noop;
        leFlag = 1;

          if( aExpr[k].idxLeft==iCur
             && (pExpr->op==TK_GT || pExpr->op==TK_GE)
             && (aExpr[k].prereqRight & loopMask)==aExpr[k].prereqRight 
             && pExpr->pLeft->iColumn==pIdx->aiColumn[j]
          ){
            sqlite3ExprCode(pParse, pExpr->pRight);
            geFlag = pExpr->op==TK_GE;
            disableTerm(pLevel, &aExpr[k].p);
            break;
          }
          if( aExpr[k].idxRight==iCur
             && (pExpr->op==TK_LT || pExpr->op==TK_LE)
             && (aExpr[k].prereqLeft & loopMask)==aExpr[k].prereqLeft
             && pExpr->pRight->iColumn==pIdx->aiColumn[j]
          ){
            sqlite3ExprCode(pParse, pExpr->pLeft);
            geFlag = pExpr->op==TK_LE;
            disableTerm(pLevel, &aExpr[k].p);
            break;
          }
        }
      }else{
        geFlag = 1;
      }
      if( nEqColumn>0 || (score&2)!=0 ){

# 2002 May 24
#
# 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.
#
# This file implements tests for left outer joins containing WHERE
# clauses that restrict the scope of the left term of the join.
#
# $Id: join4.test,v 1.1 2004/07/19 19:14:01 drh Exp $

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

do_test join4-1.1 {
  execsql {
    create temp table t1(a integer, b varchar(10));
    insert into t1 values(1,'one');
    insert into t1 values(2,'two');
    insert into t1 values(3,'three');
    insert into t1 values(4,'four');

    create temp table t2(x integer, y varchar(10), z varchar(10));
    insert into t2 values(2,'niban','ok');
    insert into t2 values(4,'yonban','err');
  }
  execsql {
    select * from t1 left outer join t2 on t1.a=t2.x where t2.z='ok'
  }
} {2 two 2 niban ok}
do_test join4-1.2 {
  execsql {
    select * from t1 left outer join t2 on t1.a=t2.x and t2.z='ok'
  }
} {1 one {} {} {} 2 two 2 niban ok 3 three {} {} {} 4 four {} {} {}}
do_test join4-1.3 {
  execsql {
    create index i2 on t2(z);
  }
btree_breakpoint
  execsql {
    select * from t1 left outer join t2 on t1.a=t2.x where t2.z='ok'
  }
} {2 two 2 niban ok}
do_test join4-1.4 {
  execsql {
    select * from t1 left outer join t2 on t1.a=t2.x and t2.z='ok'
  }
} {1 one {} {} {} 2 two 2 niban ok 3 three {} {} {} 4 four {} {} {}}
do_test join4-1.5 {
  execsql {
    select * from t1 left outer join t2 on t1.a=t2.x where t2.z>='ok'
  }
} {2 two 2 niban ok}
do_test join4-1.4 {
  execsql {
    select * from t1 left outer join t2 on t1.a=t2.x and t2.z>='ok'
  }
} {1 one {} {} {} 2 two 2 niban ok 3 three {} {} {} 4 four {} {} {}}
do_test join4-1.6 {
  execsql {
    select * from t1 left outer join t2 on t1.a=t2.x where t2.z IN ('ok')
  }
} {2 two 2 niban ok}
do_test join4-1.7 {
  execsql {
    select * from t1 left outer join t2 on t1.a=t2.x and t2.z IN ('ok')
  }
} {1 one {} {} {} 2 two 2 niban ok 3 three {} {} {} 4 four {} {} {}}


finish_test