whereLoopDelete(db, p);
    }
    sqlite3DbFree(db, pWInfo);
  }
}

/*
** Compare every WhereLoop X on the list p against pTemplate.  If:
**
**    (1) both X and pTemplate refer to the same table, and
**    (2) both X and pTemplate use a single index, and
**    (3) pTemplate uses all the same WHERE clause terms as X plus
**        at least one more term,
**
** then make sure the cost pTemplate is less than the cost of X, adjusting
** the cost of pTemplate downward if necessary.
**
** Example: When computing the query plan for the SELECT below:
**
**     CREATE TABLE t1(a,b,c,d);
**     CREATE INDEX t1abc ON t1(a,b,c);
**     CREATE INDEX t1bc ON t1(b,c);
**     SELECT d FROM t1 WHERE a=? AND b=? AND c>=? ORDER BY c;
** 
** Make sure the cost of using three three columns of index t1abc is less
** than the cost of using both columns of t1bc.
*/
static void whereLoopAdjustCost(const WhereLoop *p, WhereLoop *pTemplate){
  if( (pTemplate->wsFlags & WHERE_INDEXED)==0 ) return;
  if( pTemplate->nLTerm==0 ) return;
  for(; p; p=p->pNextLoop){
    if( p->iTab==pTemplate->iTab
     && (p->wsFlags & WHERE_INDEXED)!=0
     && p->nLTerm<pTemplate->nLTerm
     && (p->rRun<pTemplate->rRun || (p->rRun==pTemplate->rRun &&
                                     p->nOut<=pTemplate->nOut))
    ){
      int i, j;
      for(j=0, i=p->nLTerm-1; i>=0 && j>=0; i--){
        for(j=pTemplate->nLTerm-1; j>=0; j--){
          if( pTemplate->aLTerm[j]==p->aLTerm[i] ) break;
        }
      }
      if( j>=0 ){
        pTemplate->rRun = p->rRun;
        pTemplate->nOut = p->nOut - 1;
      }








    }
  }
}

/*
** Search the list of WhereLoops in *ppPrev looking for one that can be
** supplanted by pTemplate.

      whereLoopDelete(db, p);
    }
    sqlite3DbFree(db, pWInfo);
  }
}

/*
** Return TRUE if the set of WHERE clause terms used by pA is a proper
** subset of the WHERE clause terms used by pB.
*/
static int whereLoopProperSubset(const WhereLoop *pA, const WhereLoop *pB){
  int i, j;
  if( pA->nLTerm>=pB->nLTerm ) return 0;
  for(j=0, i=pA->nLTerm-1; i>=0 && j>=0; i--){
    for(j=pB->nLTerm-1; j>=0; j--){
      if( pB->aLTerm[j]==pA->aLTerm[i] ) break;
    }
  }
  return j>=0;
}

/*
** Try to adjust the cost of WhereLoop pTemplate upwards or downwards so
** that:
**
**   (1) pTemplate costs less than any other WhereLoops that are a proper
**       subset of pTemplate
**
**   (2) pTemplate costs more than any other WhereLoops for which pTemplate
**       is a proper subset.
**
** To say "WhereLoop X is a proper subset of Y" means that X uses fewer
** WHERE clause terms than Y and that every WHERE clause term used by X is
** also used by Y.
*/
static void whereLoopAdjustCost(const WhereLoop *p, WhereLoop *pTemplate){
  if( (pTemplate->wsFlags & WHERE_INDEXED)==0 ) return;
  for(; p; p=p->pNextLoop){
    if( p->iTab!=pTemplate->iTab ) continue;
    if( (p->wsFlags & WHERE_INDEXED)==0 ) continue;
    if( p->nLTerm<pTemplate->nLTerm
     && (p->rRun<pTemplate->rRun || (p->rRun==pTemplate->rRun &&
                                     p->nOut<=pTemplate->nOut))
     && whereLoopProperSubset(p, pTemplate)
    ){
      pTemplate->rRun = p->rRun;
      pTemplate->nOut = p->nOut - 1;
    }else
    if( p->nLTerm>pTemplate->nLTerm
     && (p->rRun>pTemplate->rRun || (p->rRun==pTemplate->rRun &&
                                     p->nOut>=pTemplate->nOut))
     && whereLoopProperSubset(pTemplate, p)
    ){
      pTemplate->rRun = p->rRun;
      pTemplate->nOut = p->nOut + 1;
    }
  }
}

/*
** Search the list of WhereLoops in *ppPrev looking for one that can be
** supplanted by pTemplate.

# 2014-03-31
#
# 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.
#
#***********************************************************************
# 
# Test cases for query planning decisions where one candidate index
# covers a proper superset of the WHERE clause terms of another
# candidate index.
#

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

do_execsql_test whereH-1.1 {
  CREATE TABLE t1(a,b,c,d);
  CREATE INDEX t1abc ON t1(a,b,c);
  CREATE INDEX t1bc ON t1(b,c);

  EXPLAIN QUERY PLAN
  SELECT d FROM t1 WHERE a=? AND b=? AND c>=? ORDER BY c;
} {/INDEX t1abc /}
do_execsql_test whereH-1.2 {
  EXPLAIN QUERY PLAN
  SELECT d FROM t1 WHERE a=? AND b=? AND c>=? ORDER BY c;
} {~/TEMP B-TREE FOR ORDER BY/}

do_execsql_test whereH-2.1 {
  DROP TABLE t1;
  CREATE TABLE t1(a,b,c,d);
  CREATE INDEX t1bc ON t1(b,c);
  CREATE INDEX t1abc ON t1(a,b,c);

  EXPLAIN QUERY PLAN
  SELECT d FROM t1 WHERE a=? AND b=? AND c>=? ORDER BY c;
} {/INDEX t1abc /}
do_execsql_test whereH-2.2 {
  EXPLAIN QUERY PLAN
  SELECT d FROM t1 WHERE a=? AND b=? AND c>=? ORDER BY c;
} {~/TEMP B-TREE FOR ORDER BY/}

do_execsql_test whereH-3.1 {
  DROP TABLE t1;
  CREATE TABLE t1(a,b,c,d,e);
  CREATE INDEX t1cd ON t1(c,d);
  CREATE INDEX t1bcd ON t1(b,c,d);
  CREATE INDEX t1abcd ON t1(a,b,c,d);

  EXPLAIN QUERY PLAN
  SELECT d FROM t1 WHERE a=? AND b=? AND c=? AND d>=? ORDER BY d;
} {/INDEX t1abcd /}
do_execsql_test whereH-3.2 {
  EXPLAIN QUERY PLAN
  SELECT d FROM t1 WHERE a=? AND b=? AND c=? AND d>=? ORDER BY d;
} {~/TEMP B-TREE FOR ORDER BY/}

do_execsql_test whereH-4.1 {
  DROP TABLE t1;
  CREATE TABLE t1(a,b,c,d,e);
  CREATE INDEX t1cd ON t1(c,d);
  CREATE INDEX t1abcd ON t1(a,b,c,d);
  CREATE INDEX t1bcd ON t1(b,c,d);

  EXPLAIN QUERY PLAN
  SELECT d FROM t1 WHERE a=? AND b=? AND c=? AND d>=? ORDER BY d;
} {/INDEX t1abcd /}
do_execsql_test whereH-4.2 {
  EXPLAIN QUERY PLAN
  SELECT d FROM t1 WHERE a=? AND b=? AND c=? AND d>=? ORDER BY d;
} {~/TEMP B-TREE FOR ORDER BY/}

do_execsql_test whereH-5.1 {
  DROP TABLE t1;
  CREATE TABLE t1(a,b,c,d,e);
  CREATE INDEX t1bcd ON t1(b,c,d);
  CREATE INDEX t1cd ON t1(c,d);
  CREATE INDEX t1abcd ON t1(a,b,c,d);

  EXPLAIN QUERY PLAN
  SELECT d FROM t1 WHERE a=? AND b=? AND c=? AND d>=? ORDER BY d;
} {/INDEX t1abcd /}
do_execsql_test whereH-5.2 {
  EXPLAIN QUERY PLAN
  SELECT d FROM t1 WHERE a=? AND b=? AND c=? AND d>=? ORDER BY d;
} {~/TEMP B-TREE FOR ORDER BY/}

do_execsql_test whereH-6.1 {
  DROP TABLE t1;
  CREATE TABLE t1(a,b,c,d,e);
  CREATE INDEX t1bcd ON t1(b,c,d);
  CREATE INDEX t1abcd ON t1(a,b,c,d);
  CREATE INDEX t1cd ON t1(c,d);

  EXPLAIN QUERY PLAN
  SELECT d FROM t1 WHERE a=? AND b=? AND c=? AND d>=? ORDER BY d;
} {/INDEX t1abcd /}
do_execsql_test whereH-6.2 {
  EXPLAIN QUERY PLAN
  SELECT d FROM t1 WHERE a=? AND b=? AND c=? AND d>=? ORDER BY d;
} {~/TEMP B-TREE FOR ORDER BY/}

do_execsql_test whereH-7.1 {
  DROP TABLE t1;
  CREATE TABLE t1(a,b,c,d,e);
  CREATE INDEX t1abcd ON t1(a,b,c,d);
  CREATE INDEX t1bcd ON t1(b,c,d);
  CREATE INDEX t1cd ON t1(c,d);

  EXPLAIN QUERY PLAN
  SELECT d FROM t1 WHERE a=? AND b=? AND c=? AND d>=? ORDER BY d;
} {/INDEX t1abcd /}
do_execsql_test whereH-7.2 {
  EXPLAIN QUERY PLAN
  SELECT d FROM t1 WHERE a=? AND b=? AND c=? AND d>=? ORDER BY d;
} {~/TEMP B-TREE FOR ORDER BY/}

do_execsql_test whereH-8.1 {
  DROP TABLE t1;
  CREATE TABLE t1(a,b,c,d,e);
  CREATE INDEX t1abcd ON t1(a,b,c,d);
  CREATE INDEX t1cd ON t1(c,d);
  CREATE INDEX t1bcd ON t1(b,c,d);

  EXPLAIN QUERY PLAN
  SELECT d FROM t1 WHERE a=? AND b=? AND c=? AND d>=? ORDER BY d;
} {/INDEX t1abcd /}
do_execsql_test whereH-8.2 {
  EXPLAIN QUERY PLAN
  SELECT d FROM t1 WHERE a=? AND b=? AND c=? AND d>=? ORDER BY d;
} {~/TEMP B-TREE FOR ORDER BY/}



finish_test