SQLite

        }

        /* Conditions under which this table becomes the best so far:
        **
        **   (1) The table must not depend on other tables that have not
        **       yet run.
        **
        **   (2) A full-table-scan plan cannot supercede indexed plan unless
        **       the full-table-scan is an "optimal" plan as defined above.
        **
        **   (3) All tables have an INDEXED BY clause or this table lacks an
        **       INDEXED BY clause or this table uses the specific
        **       index specified by its INDEXED BY clause.  This rule ensures
        **       that a best-so-far is always selected even if an impossible
        **       combination of INDEXED BY clauses are given.  The error
        **       will be detected and relayed back to the application later.
        **       The NEVER() comes about because rule (2) above prevents
        **       An indexable full-table-scan from reaching rule (3).
        **
        **   (4) The plan cost must be lower than prior plans or else the
        **       cost must be the same and the number of rows must be lower.
        */
        if( (sCost.used&notReady)==0                       /* (1) */
            && (bestJ<0 || (notIndexed&m)!=0               /* (2) */
                || (bestPlan.plan.wsFlags & WHERE_NOT_FULLSCAN)==0
                || (sCost.plan.wsFlags & WHERE_NOT_FULLSCAN)!=0)
            && (nUnconstrained==0 || pTabItem->pIndex==0   /* (3) */
                || NEVER((sCost.plan.wsFlags & WHERE_NOT_FULLSCAN)!=0))
            && (bestJ<0 || sCost.rCost<bestPlan.rCost      /* (4) */
                || (sCost.rCost<=bestPlan.rCost 
                 && sCost.plan.nRow<bestPlan.plan.nRow))
        ){

# 2011 March 3
#
# 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 tests for SQLite library.  The focus of the tests
# in this file a corner-case query planner optimization involving the
# join order of two tables of different sizes.
#

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

ifcapable !stat2 {
  finish_test
  return
}

set testprefix analyze6

proc eqp {sql {db db}} {
  uplevel execsql [list "EXPLAIN QUERY PLAN $sql"] $db
}

do_test analyze6-1.0 {
  db eval {
    CREATE TABLE cat(x INT);
    CREATE UNIQUE INDEX catx ON cat(x);
    /* Give cat 16 unique integers */
    INSERT INTO cat VALUES(1);
    INSERT INTO cat VALUES(2);
    INSERT INTO cat SELECT x+2 FROM cat;
    INSERT INTO cat SELECT x+4 FROM cat;
    INSERT INTO cat SELECT x+8 FROM cat;

    CREATE TABLE ev(y INT);
    CREATE INDEX evy ON ev(y);
    /* ev will hold 32 copies of 16 integers found in cat */
    INSERT INTO ev SELECT x FROM cat;
    INSERT INTO ev SELECT x FROM cat;
    INSERT INTO ev SELECT y FROM ev;
    INSERT INTO ev SELECT y FROM ev;
    INSERT INTO ev SELECT y FROM ev;
    INSERT INTO ev SELECT y FROM ev;
    ANALYZE;
    SELECT count(*) FROM cat;
    SELECT count(*) FROM ev;
  }
} {16 512}

# The lowest cost plan is to scan CAT and for each integer there, do a single
# lookup of the first corresponding entry in EV then read off the equal values
# in EV.  (Prior to the 2011-03-04 enhancement to where.c, this query would
# have used EV for the outer loop instead of CAT - which was about 3x slower.)
#
do_test analyze6-1.1 {
  eqp {SELECT count(*) FROM ev, cat WHERE x=y}
} {0 0 1 {SCAN TABLE cat (~16 rows)} 0 1 0 {SEARCH TABLE ev USING COVERING INDEX evy (y=?) (~32 rows)}}

# The same plan is chosen regardless of the order of the tables in the
# FROM clause.
#
do_test analyze6-1.2 {
  eqp {SELECT count(*) FROM cat, ev WHERE x=y}
} {0 0 0 {SCAN TABLE cat (~16 rows)} 0 1 1 {SEARCH TABLE ev USING COVERING INDEX evy (y=?) (~32 rows)}}


finish_test