SQLite4  Check-in [57b8ae848c]

          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(pIndex, pPk, j);
            if( sqlite4_stricmp(pColl->zName, zIdxColl)!=0 ) continue;
          }
          isMatch = 1;
          break;
        }
        if( isMatch ){
          obSat |= MASKBIT(i);

    INSERT INTO t1 SELECT a+32, b FROM t1;
    INSERT INTO t1 SELECT a+64, b FROM t1;
    ANALYZE;
  }

  # Should choose the t1a index since it is more specific than t1b.
  db eval {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=5 AND b IS NULL}
} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?)}}

# Verify that the t1b index shows that it does not narrow down the
# search any at all.
#
do_test analyze4-1.1 {
  db eval {
    SELECT idx, stat FROM sqlite_stat1 WHERE tbl='t1' ORDER BY idx;

#***********************************************************************
# 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 
    3 rootpage integer 0 {} 0 

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}}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 89.1 {
  CREATE TABLE t1(a COLLATE NOCASE);
  CREATE INDEX i1 ON t1(a);
}
do_eqp_test 89.2 {
  SELECT * FROM t1 ORDER BY a;
} {0 0 0 {SCAN TABLE t1 USING INDEX i1}}

finish_test

  );
  CREATE INDEX t302_c3 on t302(c3);
  CREATE INDEX t302_c8_c3 on t302(c8, c3);
  CREATE INDEX t302_c5 on t302(c5);
  
  EXPLAIN QUERY PLAN
  SELECT t302.c1 
    FROM t302 JOIN t301 ON t302.c8 = +t301.c8
    WHERE t302.c2 = 19571
      AND t302.c3 > 1287603136
      AND (t301.c4 = 1407449685622784
           OR t301.c8 = 1407424651264000)
   ORDER BY t302.c5 LIMIT 200;
} {
  0 0 1 {SEARCH TABLE t301 USING INDEX t301_c4 (c4=?)}
  0 0 1 {SEARCH TABLE t301 USING INDEX t301 (c8=?)}
  0 1 0 {SEARCH TABLE t302 USING INDEX t302_c8_c3 (c8=? AND c3>?)}
  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
}

finish_test