SQLite4  Check-in [cc7bc86da5]

  if( iIdxCol<pIdx->nColumn ){
    zColl = pIdx->azColl[iIdxCol];
  }else if( pPk && iIdxCol<(pIdx->nColumn + pPk->nColumn) ){
    zColl = pPk->azColl[iIdxCol - pIdx->nColumn];
  }
  return zColl;
}

/*
** Return the sort order (SQLITE4_SO_ASC or DESC) used by the iIdxCol'th 
** field in index pIdx, including any appended PRIMARY KEY fields.
*/
static int idxColumnSortOrder(Index *pIdx, Index *pPk, int iIdxCol){
  int iRet = SQLITE4_SO_ASC;
  if( iIdxCol<pIdx->nColumn ){
    iRet = pIdx->aSortOrder[iIdxCol];
  }
  return iRet;
}

/*
** Return the total number of fields in the index pIdx, including any
** trailing primary key fields.
*/
static int idxColumnCount(Index *pIdx, Index *pPk){
  return (pIdx->nColumn + (pIdx==pPk ? 0 : pPk->nColumn));

  WhereLoop *pLast,     /* Add this WhereLoop to the end of pPath->aLoop[] */
  Bitmask *pRevMask     /* OUT: Mask of WhereLoops to run in reverse order */
){
  u8 revSet;            /* True if rev is known */
  u8 rev;               /* Composite sort order */
  u8 revIdx;            /* Index sort order */
  u8 isOrderDistinct;   /* All prior WhereLoops are order-distinct */

  u8 isMatch;           /* iColumn matches a term of the ORDER BY clause */
  u16 nColumn;          /* Number of columns in pIndex */
  u16 nOrderBy;         /* Number terms in the ORDER BY clause */
  int iLoop;            /* Index of WhereLoop in pPath being processed */
  int i, j;             /* Loop counters */
  int iCur;             /* Cursor number for current WhereLoop */
  int iColumn;          /* A column number within table iCur */

        z2 = pColl->zName;
        if( sqlite4_stricmp(z1, z2)!=0 ) continue;
      }
      obSat |= MASKBIT(i);
    }

    if( (pLoop->wsFlags & WHERE_ONEROW)==0 ){
      Index *pPk = 0;
      if( pLoop->wsFlags & WHERE_IPK ){
        pIndex = 0;
        nColumn = 0;
      }else if( (pIndex = pLoop->u.btree.pIndex)==0 || pIndex->bUnordered ){
        return 0;
      }else{

        isOrderDistinct = pIndex->onError!=OE_None;
        pPk = sqlite4FindPrimaryKey(pIndex->pTable, 0);
        nColumn = idxColumnCount(pIndex, pPk);
      }

      /* Loop through all columns of the index and deal with the ones
      ** that are not constrained by == or IN.
      */
      rev = revSet = 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
         && ((i = pLoop->aLTerm[j]->eOperator) & (WO_EQ|WO_ISNULL))!=0
        ){
          if( i & WO_ISNULL ){
            testcase( isOrderDistinct );
            isOrderDistinct = 0;
          }
          continue;  
        }

        /* Get the column number in the table (iColumn) and sort order
        ** (revIdx) for the j-th column of the index.
        */
        if( j<nColumn ){
          /* Normal index columns */
          iColumn = idxColumnNumber(pIndex, pPk, j);
          revIdx = idxColumnSortOrder(pIndex, pPk, j);



        }else{
          /* The ROWID column at the end */
          assert( j==nColumn );
          iColumn = -1;
          revIdx = 0;
        }

          testcase( wctrlFlags & WHERE_GROUPBY );
          testcase( wctrlFlags & WHERE_DISTINCTBY );
          if( (wctrlFlags & (WHERE_GROUPBY|WHERE_DISTINCTBY))==0 ) bOnce = 0;
          if( pOBExpr->op!=TK_COLUMN ) continue;
          if( pOBExpr->iTable!=iCur ) continue;
          if( pOBExpr->iColumn!=iColumn ) continue;
          if( iColumn>=0 ){
            const char *zIdxColl;
            pColl = sqlite4ExprCollSeq(pWInfo->pParse, pOrderBy->a[i].pExpr);
            if( !pColl ) pColl = db->pDfltColl;
            zIdxColl = idxColumnCollation(pPk, pIndex, j);
            if( sqlite4_stricmp(pColl->zName, zIdxColl)!=0 ) continue;
          }
          isMatch = 1;
          break;
        }
        if( isMatch ){




          obSat |= MASKBIT(i);
          if( (pWInfo->wctrlFlags & WHERE_GROUPBY)==0 ){
            /* Make sure the sort order is compatible in an ORDER BY clause.
            ** Sort order is irrelevant for a GROUP BY clause. */
            if( revSet ){
              if( (rev ^ revIdx)!=pOrderBy->a[i].sortOrder ) return 0;
            }else{

          if( j==0 || j<nColumn ){
            testcase( isOrderDistinct!=0 );
            isOrderDistinct = 0;
          }
          break;
        }
      } /* end Loop over all index columns */

      /* If (j==nColumn), then each column of the index, including any 
      ** appended PK columns, corresponds to either an ORDER BY term or 
      ** equality constraint. Since the PK columns are collectively UNIQUE
      ** and NOT NULL, consider the loop order-distinct.  */
      if( j==nColumn ){
        testcase( isOrderDistinct==0 );
        isOrderDistinct = 1;
      }
    } /* end-if not one-row */

    /* Mark off any other ORDER BY terms that reference pLoop */
    if( isOrderDistinct ){

#***********************************************************************
# The tests in this file were used while developing the SQLite 4 code. 
#
set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix simple



do_execsql_test 1.0 { 
  PRAGMA table_info = sqlite_master
} {
    0 type text        0 {} 0 
    1 name text        0 {} 0 
    2 tbl_name text    0 {} 0 

do_execsql_test 86.3 { 
  SELECT * FROM t1;
} {1 one}
do_execsql_test 86.4 { 
  SELECT * FROM t1 WHERE a = 1;
} {1 one}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 87.1 {
  CREATE TABLE t6(a INTEGER PRIMARY KEY, b TEXT);
  CREATE INDEX t6i1 ON t6(b);
} {}
do_eqp_test 87.2 {
  SELECT * FROM t6 ORDER BY b, a;
} {0 0 0 {SCAN TABLE t6 USING INDEX t6i1}}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 88.1 {
  CREATE TABLE t8(a INTEGER PRIMARY KEY, b TEXT);
  CREATE UNIQUE INDEX t8i ON t8(b);
}
do_eqp_test 88.2 {
  SELECT * FROM t8 x ORDER BY x.b, x.a, x.b||x.a
} {0 0 0 {SCAN TABLE t8 AS x USING INDEX t8i}}

finish_test

  if {![info exists ::G(match)] || [string match $::G(match) $name]} {
    if {[catch {uplevel #0 "$cmd;\n"} result]} {
      puts "\nError: $result"
      fail_test $name
    } else {
      if {[regexp {^~?/.*/$} $expected]} {
        # "expected" is of the form "/PATTERN/" then the result if correct if
        # regular expression PATTERN matches the result.  "~/PATTERN/" means
        # the regular expression must not match.
        if {[string index $expected 0]=="~"} {
          set re [string map {# {[-0-9.]+}} [string range $expected 2 end-1]]
          set ok [expr {![regexp $re $result]}]
        } else {
          set re [string map {# {[-0-9.]+}} [string range $expected 1 end-1]]
          set ok [regexp $re $result]
        }
      } elseif {[regexp {^~?\*.*\*$} $expected]} {
        # "expected" is of the form "*GLOB*" then the result if correct if
        # glob pattern GLOB matches the result.  "~/GLOB/" means
        # the glob must not match.
        if {[string index $expected 0]=="~"} {
          set e [string range $expected 1 end]
          set ok [expr {![string match $e $result]}]
        } else {
          set ok [string match $expected $result]
        }
      } else {
        set ok [expr {[string compare $result $expected]==0}]
      }
      if {!$ok} {
        # if {![info exists ::testprefix] || $::testprefix eq ""} {
        #   error "no test prefix"

proc count sql {
  kvwrap reset
  set res [execsql $sql]
  #puts "sql={$sql} seek=[kvwrap seek] step=[kvwrap step]"
  return [concat $res [expr [kvwrap step] + [kvwrap seek]]]
}

# Verify that queries use an index. By verifing that the KVWrap layer

# xNext/xPrev/xSeek 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_eqp_test where-1.1.2 {
  SELECT x, y, w FROM t1 WHERE w=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 {
  db status step
} {99}
do_eqp_test where-1.1.6 {
  SELECT x, y, w FROM t1 WHERE +w=10
} {*SCAN TABLE t1*}
do_test where-1.1.7 {
  count {SELECT x, y, w AS abc FROM t1 WHERE abc=10}
} {3 121 10 3}
do_eqp_test where-1.1.8 {
  SELECT x, y, w AS abc FROM t1 WHERE abc=10
} {*SEARCH TABLE t1 USING INDEX i1w (w=?)*}
do_test where-1.1.9 {
  db status step
} {0}
do_test where-1.2.1 {
  count {SELECT x, y, w FROM t1 WHERE w=11}
} {3 144 11 3}
do_test where-1.2.2 {
  count {SELECT x, y, w AS abc FROM t1 WHERE abc=11}
} {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.4.1 {
  count {SELECT w, x, y FROM t1 WHERE 11=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_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 {
  count {SELECT x, y FROM t1 WHERE y<200 AND w=11 AND x>2}
} {3 144 3}
do_eqp_test where-1.5.2 {
  SELECT x, y FROM t1 WHERE y<200 AND w=11 AND x>2
} {*SEARCH TABLE t1 USING INDEX i1w (w=?)*}
do_test where-1.6 {
  count {SELECT x, y FROM t1 WHERE y<200 AND x>2 AND w=11}
} {3 144 3}
do_test where-1.7 {
  count {SELECT x, y FROM t1 WHERE w=11 AND y<200 AND x>2}
} {3 144 3}
do_test where-1.8 {
  count {SELECT x, y FROM t1 WHERE w>10 AND y=144 AND x=3}
} {3 144 3}
do_eqp_test where-1.8.2 {
  SELECT x, y FROM t1 WHERE w>10 AND y=144 AND x=3
} {*SEARCH TABLE t1 USING INDEX i1xy (x=? AND y=?)*}
do_eqp_test where-1.8.3 {
  SELECT x, y FROM t1 WHERE y=144 AND x=3

} {*SEARCH TABLE t1 USING INDEX i1xy (x=? AND y=?)*}
do_test where-1.9 {
  count {SELECT x, y FROM t1 WHERE y=144 AND w>10 AND x=3}
} {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}


# 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-6.6 {
  cksort {
    SELECT * FROM t3 WHERE a>0 ORDER BY a LIMIT 3
  }
} {1 100 4 2 99 9 3 98 16 nosort}

do_test where-6.7 {


  cksort {
    SELECT * FROM t3 WHERE b>0 ORDER BY a LIMIT 3
  }
} {1 100 4 2 99 9 3 98 16 nosort}

ifcapable subquery {
  do_test where-6.8 {
    cksort {
      SELECT * FROM t3 WHERE a IN (3,5,7,1,9,4,2) ORDER BY a LIMIT 3
    }
  } {1 100 4 2 99 9 3 98 16 nosort}
}
do_test where-6.9.1 {
  cksort {
    SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a LIMIT 3
  }
} {1 100 4 nosort}
do_test where-6.9.1.1 {

    CREATE TABLE t8(a INTEGER PRIMARY KEY, b TEXT UNIQUE);
    INSERT INTO t8 VALUES(1,'one');
    INSERT INTO t8 VALUES(4,'four');
  }
  cksort {
    SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, y.b
  } 
} {1/4 1/1 4/4 4/1 nosort}
do_test where-14.2 {
  cksort {
    SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, y.b DESC
  } 
} {1/1 1/4 4/1 4/4 nosort}
do_test where-14.3 {
  cksort {
    SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, x.b
  } 
} {1/1 1/4 4/1 4/4 nosort}
do_test where-14.4 {
  cksort {