SQLite

    u32 flags = pLevel->plan.wsFlags;
    struct SrcList_item *pItem = &pTabList->a[pLevel->iFrom];
    Vdbe *v = pParse->pVdbe;      /* VM being constructed */
    sqlite3 *db = pParse->db;     /* Database handle */
    char *zMsg;                   /* Text to add to EQP output */
    sqlite3_int64 nRow;           /* Expected number of rows visited by scan */
    int iId = pParse->iSelectId;  /* Select id (left-most output column) */
    int isSearch;                 /* True for a SEARCH. False for SCAN. */

    if( (flags&WHERE_MULTI_OR) || (wctrlFlags&WHERE_ONETABLE_ONLY) ) return;

    isSearch = (pLevel->plan.nEq>0 || flags&(WHERE_BTM_LIMIT|WHERE_TOP_LIMIT));

    zMsg = sqlite3MPrintf(db, "%s", isSearch?"SEARCH":"SCAN");
    if( pItem->pSelect ){
      zMsg = sqlite3MAppendf(db, zMsg, "%s SUBQUERY %d", zMsg,pItem->iSelectId);
    }else{
      zMsg = sqlite3MAppendf(db, zMsg, "%s TABLE %s", zMsg, pItem->zName);
    }

    if( pItem->zAlias ){
      zMsg = sqlite3MAppendf(db, zMsg, "%s AS %s", zMsg, pItem->zAlias);
    }
    if( (flags & WHERE_INDEXED)!=0 ){
      char *zWhere = explainIndexRange(db, pLevel, pItem->pTab);
      zMsg = sqlite3MAppendf(db, zMsg, "%s USING %s%sINDEX%s%s%s", zMsg, 
          ((flags & WHERE_TEMP_INDEX)?"AUTOMATIC ":""),
          ((flags & WHERE_IDX_ONLY)?"COVERING ":""),
          ((flags & WHERE_TEMP_INDEX)?"":" "),
          ((flags & WHERE_TEMP_INDEX)?"": pLevel->plan.u.pIdx->zName),
          zWhere
      );
      sqlite3DbFree(db, zWhere);
    }else if( flags & (WHERE_ROWID_EQ|WHERE_ROWID_RANGE) ){
      zMsg = sqlite3MAppendf(db, zMsg, "%s USING INTEGER PRIMARY KEY", zMsg);

      if( flags&WHERE_ROWID_EQ ){
        zMsg = sqlite3MAppendf(db, zMsg, "%s (rowid=?)", zMsg);
      }else if( flags&WHERE_BTM_LIMIT && flags&WHERE_TOP_LIMIT ){
        zMsg = sqlite3MAppendf(db, zMsg, "%s (rowid>? AND rowid<?)", zMsg);
      }else if( flags&WHERE_BTM_LIMIT ){
        zMsg = sqlite3MAppendf(db, zMsg, "%s (rowid>?)", zMsg);

do_execsql_test autoindex1-500 {
  CREATE TABLE t501(a INTEGER PRIMARY KEY, b);
  CREATE TABLE t502(x INTEGER PRIMARY KEY, y);
  EXPLAIN QUERY PLAN
  SELECT b FROM t501
   WHERE t501.a IN (SELECT x FROM t502 WHERE y=?);
} {
  0 0 0 {SEARCH TABLE t501 USING INTEGER PRIMARY KEY (rowid=?) (~25 rows)} 
  0 0 0 {EXECUTE LIST SUBQUERY 1} 
  1 0 0 {SCAN TABLE t502 (~100000 rows)}
}
do_execsql_test autoindex1-501 {
  EXPLAIN QUERY PLAN
  SELECT b FROM t501
   WHERE t501.a IN (SELECT x FROM t502 WHERE y=t501.b);
} {
  0 0 0 {SCAN TABLE t501 (~500000 rows)} 
  0 0 0 {EXECUTE CORRELATED LIST SUBQUERY 1} 
  1 0 0 {SEARCH TABLE t502 USING AUTOMATIC COVERING INDEX (y=?) (~7 rows)}
}
do_execsql_test autoindex1-502 {
  EXPLAIN QUERY PLAN
  SELECT b FROM t501
   WHERE t501.a=123
     AND t501.a IN (SELECT x FROM t502 WHERE y=t501.b);
} {
  0 0 0 {SEARCH TABLE t501 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 
  0 0 0 {EXECUTE CORRELATED LIST SUBQUERY 1} 
  1 0 0 {SCAN TABLE t502 (~100000 rows)}
}


# The following code checks a performance regression reported on the
# mailing list on 2010-10-19.  The problem is that the nRowEst field

           AND later.owner_change_date > prev.owner_change_date
           AND later.owner_change_date <= s.date_of_registration||' 00:00:00')
       ) y ON x.sheep_no = y.sheep_no
   WHERE y.sheep_no IS NULL
   ORDER BY x.registering_flock;
} {
  1 0 0 {SCAN TABLE sheep AS s (~1000000 rows)} 
  1 1 1 {SEARCH TABLE flock_owner AS prev USING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date<?) (~2 rows)} 
  1 0 0 {EXECUTE CORRELATED SCALAR SUBQUERY 2} 
  2 0 0 {SEARCH TABLE flock_owner AS later USING COVERING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date>? AND owner_change_date<?) (~1 rows)} 
  0 0 0 {SCAN TABLE sheep AS x USING INDEX sheep_reg_flock_index (~1000000 rows)} 
  0 1 1 {SEARCH SUBQUERY 1 AS y USING AUTOMATIC COVERING INDEX (sheep_no=?) (~7 rows)}
}

finish_test

#
do_execsql_test 4.10.0 {
  CREATE TABLE t1(a, b PRIMARY KEY);
  CREATE TABLE t2(a, b, c, UNIQUE(b, c));
}
do_createtable_tests 4.10 {
  1    "EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b = 5" 
       {0 0 0 {SEARCH TABLE t1 USING INDEX sqlite_autoindex_t1_1 (b=?) (~1 rows)}}

  2    "EXPLAIN QUERY PLAN SELECT * FROM t2 ORDER BY b, c"
       {0 0 0 {SCAN TABLE t2 USING INDEX sqlite_autoindex_t2_1 (~1000000 rows)}}

  3    "EXPLAIN QUERY PLAN SELECT * FROM t2 WHERE b=10 AND c>10"
       {0 0 0 {SEARCH TABLE t2 USING INDEX sqlite_autoindex_t2_1 (b=? AND c>?) (~3 rows)}}
}

# EVIDENCE-OF: R-45493-35653 A CHECK constraint may be attached to a
# column definition or specified as a table constraint. In practice it
# makes no difference.
#
#   All the tests that deal with CHECK constraints below (4.11.* and 

do_test e_fkey-27.2 {
  eqp { INSERT INTO artist VALUES(?, ?) }
} {}
do_execsql_test e_fkey-27.3 {
  EXPLAIN QUERY PLAN UPDATE artist SET artistid = ?, artistname = ?
} {
  0 0 0 {SCAN TABLE artist (~1000000 rows)} 
  0 0 0 {SEARCH TABLE track USING COVERING INDEX trackindex (trackartist=?) (~10 rows)} 
  0 0 0 {SEARCH TABLE track USING COVERING INDEX trackindex (trackartist=?) (~10 rows)}
}
do_execsql_test e_fkey-27.4 {
  EXPLAIN QUERY PLAN DELETE FROM artist
} {
  0 0 0 {SCAN TABLE artist (~1000000 rows)} 
  0 0 0 {SEARCH TABLE track USING COVERING INDEX trackindex (trackartist=?) (~10 rows)}
}


###########################################################################
### SECTION 4.1: Composite Foreign Key Constraints
###########################################################################

  CREATE TABLE t2(a, b);
  CREATE TABLE t3(a, b);
}

do_eqp_test 1.2 {
  SELECT * FROM t2, t1 WHERE t1.a=1 OR t1.b=2;
} {
  0 0 1 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~10 rows)} 
  0 0 1 {SEARCH TABLE t1 USING INDEX i2 (b=?) (~10 rows)} 
  0 1 0 {SCAN TABLE t2 (~1000000 rows)}
}
do_eqp_test 1.3 {
  SELECT * FROM t2 CROSS JOIN t1 WHERE t1.a=1 OR t1.b=2;
} {
  0 0 0 {SCAN TABLE t2 (~1000000 rows)}
  0 1 1 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~10 rows)} 
  0 1 1 {SEARCH TABLE t1 USING INDEX i2 (b=?) (~10 rows)} 
}
do_eqp_test 1.3 {
  SELECT a FROM t1 ORDER BY a
} {
  0 0 0 {SCAN TABLE t1 USING COVERING INDEX i1 (~1000000 rows)}
}
do_eqp_test 1.4 {
  SELECT a FROM t1 ORDER BY +a
} {
  0 0 0 {SCAN TABLE t1 (~1000000 rows)}
  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
}
do_eqp_test 1.5 {
  SELECT a FROM t1 WHERE a=4
} {
  0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?) (~10 rows)}
}
do_eqp_test 1.6 {
  SELECT DISTINCT count(*) FROM t3 GROUP BY a;
} {
  0 0 0 {SCAN TABLE t3 (~1000000 rows)}
  0 0 0 {USE TEMP B-TREE FOR GROUP BY}
  0 0 0 {USE TEMP B-TREE FOR DISTINCT}

det 2.2.1 "SELECT DISTINCT min(x), max(x) FROM t1 GROUP BY x ORDER BY 1" {
  0 0 0 {SCAN TABLE t1 (~1000000 rows)}
  0 0 0 {USE TEMP B-TREE FOR GROUP BY}
  0 0 0 {USE TEMP B-TREE FOR DISTINCT}
  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
}
det 2.2.2 "SELECT DISTINCT min(x), max(x) FROM t2 GROUP BY x ORDER BY 1" {
  0 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1 (~1000000 rows)}
  0 0 0 {USE TEMP B-TREE FOR DISTINCT}
  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
}
det 2.2.3 "SELECT DISTINCT * FROM t1" {
  0 0 0 {SCAN TABLE t1 (~1000000 rows)}
  0 0 0 {USE TEMP B-TREE FOR DISTINCT}
}
det 2.2.4 "SELECT DISTINCT * FROM t1, t2" {
  0 0 0 {SCAN TABLE t1 (~1000000 rows)}
  0 1 1 {SCAN TABLE t2 (~1000000 rows)}
  0 0 0 {USE TEMP B-TREE FOR DISTINCT}
}
det 2.2.5 "SELECT DISTINCT * FROM t1, t2 ORDER BY t1.x" {
  0 0 0 {SCAN TABLE t1 (~1000000 rows)}
  0 1 1 {SCAN TABLE t2 (~1000000 rows)}
  0 0 0 {USE TEMP B-TREE FOR DISTINCT}
  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
}
det 2.2.6 "SELECT DISTINCT t2.x FROM t1, t2 ORDER BY t2.x" {
  0 0 1 {SCAN TABLE t2 USING COVERING INDEX t2i1 (~1000000 rows)}
  0 1 0 {SCAN TABLE t1 (~1000000 rows)}
}

det 2.3.1 "SELECT max(x) FROM t2" {
  0 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1 (~1 rows)}
}
det 2.3.2 "SELECT min(x) FROM t2" {
  0 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1 (~1 rows)}
}
det 2.3.3 "SELECT min(x), max(x) FROM t2" {
  0 0 0 {SCAN TABLE t2 (~1000000 rows)}
}

det 2.4.1 "SELECT * FROM t1 WHERE rowid=?" {
  0 0 0 {SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}
}



#-------------------------------------------------------------------------
# Test cases eqp-3.* - tests for select statements that use sub-selects.
#

  1 0 0 {USE TEMP B-TREE FOR ORDER BY}
}
do_eqp_test 3.1.4 {
  SELECT * FROM t1 WHERE (SELECT x FROM t2 ORDER BY x);
} {
  0 0 0 {SCAN TABLE t1 (~1000000 rows)}
  0 0 0 {EXECUTE SCALAR SUBQUERY 1}
  1 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1 (~1000000 rows)}
}

det 3.2.1 {
  SELECT * FROM (SELECT * FROM t1 ORDER BY x LIMIT 10) ORDER BY y LIMIT 5
} {
  1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
  1 0 0 {USE TEMP B-TREE FOR ORDER BY} 
  0 0 0 {SCAN SUBQUERY 1 (~1000000 rows)} 
  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
}
det 3.2.2 {
  SELECT * FROM 
    (SELECT * FROM t1 ORDER BY x LIMIT 10) AS x1,
    (SELECT * FROM t2 ORDER BY x LIMIT 10) AS x2
  ORDER BY x2.y LIMIT 5
} {
  1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
  1 0 0 {USE TEMP B-TREE FOR ORDER BY} 
  2 0 0 {SCAN TABLE t2 USING INDEX t2i1 (~1000000 rows)} 
  0 0 0 {SCAN SUBQUERY 1 AS x1 (~1000000 rows)} 
  0 1 1 {SCAN SUBQUERY 2 AS x2 (~1000000 rows)} 
  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
}

det 3.3.1 {
  SELECT * FROM t1 WHERE y IN (SELECT y FROM t2)

}

do_eqp_test 4.2.2 {
  SELECT * FROM t1 UNION ALL SELECT * FROM t2 ORDER BY 1
} {
  1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
  1 0 0 {USE TEMP B-TREE FOR ORDER BY}
  2 0 0 {SCAN TABLE t2 USING INDEX t2i1 (~1000000 rows)} 
  0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (UNION ALL)} 
}
do_eqp_test 4.2.3 {
  SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY 1
} {
  1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
  1 0 0 {USE TEMP B-TREE FOR ORDER BY}

  0 0 0 {COMPOUND SUBQUERIES 1 AND 4 USING TEMP B-TREE (UNION)}
}
do_eqp_test 4.3.3 {
  SELECT x FROM t1 UNION SELECT x FROM t2 UNION SELECT x FROM t1 ORDER BY 1
} {
  2 0 0 {SCAN TABLE t1 (~1000000 rows)} 
  2 0 0 {USE TEMP B-TREE FOR ORDER BY} 
  3 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1 (~1000000 rows)} 
  1 0 0 {COMPOUND SUBQUERIES 2 AND 3 (UNION)} 
  4 0 0 {SCAN TABLE t1 (~1000000 rows)} 
  4 0 0 {USE TEMP B-TREE FOR ORDER BY} 
  0 0 0 {COMPOUND SUBQUERIES 1 AND 4 (UNION)}
}

#-------------------------------------------------------------------------
# This next block of tests verifies that the examples on the 
# lang_explain.html page are correct.
#
drop_all_tables

# EVIDENCE-OF: R-64208-08323 sqlite> EXPLAIN QUERY PLAN SELECT a, b
# FROM t1 WHERE a=1; 0|0|0|SCAN TABLE t1 (~100000 rows)

do_execsql_test 5.1.0 { CREATE TABLE t1(a, b) }
det 5.1.1 "SELECT a, b FROM t1 WHERE a=1" {
  0 0 0 {SCAN TABLE t1 (~100000 rows)}
}

# EVIDENCE-OF: R-09022-44606 sqlite> CREATE INDEX i1 ON t1(a);
# sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1;
# 0|0|0|SEARCH TABLE t1 USING INDEX i1 (a=?) (~10 rows)
do_execsql_test 5.2.0 { CREATE INDEX i1 ON t1(a) }
det 5.2.1 "SELECT a, b FROM t1 WHERE a=1" {
  0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~10 rows)}
}

# EVIDENCE-OF: R-62228-34103 sqlite> CREATE INDEX i2 ON t1(a, b);
# sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1;
# 0|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) (~10 rows)
do_execsql_test 5.3.0 { CREATE INDEX i2 ON t1(a, b) }
det 5.3.1 "SELECT a, b FROM t1 WHERE a=1" {
  0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) (~10 rows)}
}

# EVIDENCE-OF: R-22253-05302 sqlite> EXPLAIN QUERY PLAN SELECT t1.*,
# t2.* FROM t1, t2 WHERE t1.a=1 AND t1.b>2; 0|0|0|SEARCH TABLE t1
# USING COVERING INDEX i2 (a=? AND b>?) (~3 rows) 0|1|1|SCAN TABLE t2
# (~1000000 rows)
do_execsql_test 5.4.0 {CREATE TABLE t2(c, d)}
det 5.4.1 "SELECT t1.*, t2.* FROM t1, t2 WHERE t1.a=1 AND t1.b>2" {
  0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?) (~3 rows)}
  0 1 1 {SCAN TABLE t2 (~1000000 rows)}
}

# EVIDENCE-OF: R-21040-07025 sqlite> EXPLAIN QUERY PLAN SELECT t1.*,
# t2.* FROM t2, t1 WHERE t1.a=1 AND t1.b>2; 0|0|1|SEARCH TABLE t1
# USING COVERING INDEX i2 (a=? AND b>?) (~3 rows) 0|1|0|SCAN TABLE t2
# (~1000000 rows)
det 5.5 "SELECT t1.*, t2.* FROM t2, t1 WHERE t1.a=1 AND t1.b>2" {
  0 0 1 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?) (~3 rows)}
  0 1 0 {SCAN TABLE t2 (~1000000 rows)}
}

# EVIDENCE-OF: R-39007-61103 sqlite> CREATE INDEX i3 ON t1(b);
# sqlite> EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=1 OR b=2;
# 0|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) (~10 rows)
# 0|0|0|SEARCH TABLE t1 USING INDEX i3 (b=?) (~10 rows)
do_execsql_test 5.5.0 {CREATE INDEX i3 ON t1(b)}
det 5.6.1 "SELECT * FROM t1 WHERE a=1 OR b=2" {
  0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) (~10 rows)}
  0 0 0 {SEARCH TABLE t1 USING INDEX i3 (b=?) (~10 rows)}
}

# EVIDENCE-OF: R-33025-54904 sqlite> EXPLAIN QUERY PLAN SELECT c, d
# FROM t2 ORDER BY c; 0|0|0|SCAN TABLE t2 (~1000000 rows) 0|0|0|USE TEMP
# B-TREE FOR ORDER BY
det 5.7 "SELECT c, d FROM t2 ORDER BY c" {
  0 0 0 {SCAN TABLE t2 (~1000000 rows)}
  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
}

# EVIDENCE-OF: R-38854-22809 sqlite> CREATE INDEX i4 ON t2(c);
# sqlite> EXPLAIN QUERY PLAN SELECT c, d FROM t2 ORDER BY c;
# 0|0|0|SCAN TABLE t2 USING INDEX i4 (~1000000 rows)
do_execsql_test 5.8.0 {CREATE INDEX i4 ON t2(c)}
det 5.8.1 "SELECT c, d FROM t2 ORDER BY c" {
  0 0 0 {SCAN TABLE t2 USING INDEX i4 (~1000000 rows)}
}

# EVIDENCE-OF: R-29884-43993 sqlite> EXPLAIN QUERY PLAN SELECT
# (SELECT b FROM t1 WHERE a=0), (SELECT a FROM t1 WHERE b=t2.c) FROM t2;
# 0|0|0|SCAN TABLE t2 (~1000000 rows) 0|0|0|EXECUTE SCALAR SUBQUERY 1
# 1|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) (~10 rows)
# 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2 2|0|0|SEARCH TABLE t1 USING
# INDEX i3 (b=?) (~10 rows)
det 5.9 {
  SELECT (SELECT b FROM t1 WHERE a=0), (SELECT a FROM t1 WHERE b=t2.c) FROM t2
} {
  0 0 0 {SCAN TABLE t2 (~1000000 rows)}
  0 0 0 {EXECUTE SCALAR SUBQUERY 1}
  1 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) (~10 rows)}
  0 0 0 {EXECUTE CORRELATED SCALAR SUBQUERY 2}
  2 0 0 {SEARCH TABLE t1 USING INDEX i3 (b=?) (~10 rows)}
}

# EVIDENCE-OF: R-17911-16445 sqlite> EXPLAIN QUERY PLAN SELECT
# count(*) FROM (SELECT max(b) AS x FROM t1 GROUP BY a) GROUP BY x;
# 1|0|0|SCAN TABLE t1 USING COVERING INDEX i2 (~1000000 rows) 0|0|0|SCAN
# SUBQUERY 1 (~1000000 rows) 0|0|0|USE TEMP B-TREE FOR GROUP BY
det 5.10 {
  SELECT count(*) FROM (SELECT max(b) AS x FROM t1 GROUP BY a) GROUP BY x
} {
  1 0 0 {SCAN TABLE t1 USING COVERING INDEX i2 (~1000000 rows)}
  0 0 0 {SCAN SUBQUERY 1 (~1000000 rows)}
  0 0 0 {USE TEMP B-TREE FOR GROUP BY}
}

# EVIDENCE-OF: R-18544-33103 sqlite> EXPLAIN QUERY PLAN SELECT * FROM
# (SELECT * FROM t2 WHERE c=1), t1; 0|0|0|SEARCH TABLE t2 USING INDEX i4
# (c=?) (~10 rows) 0|1|1|SCAN TABLE t1 (~1000000 rows)
det 5.11 "SELECT * FROM (SELECT * FROM t2 WHERE c=1), t1" {
  0 0 0 {SEARCH TABLE t2 USING INDEX i4 (c=?) (~10 rows)}
  0 1 1 {SCAN TABLE t1 (~1000000 rows)}
}

# EVIDENCE-OF: R-40701-42164 sqlite> EXPLAIN QUERY PLAN SELECT a FROM
# t1 UNION SELECT c FROM t2; 1|0|0|SCAN TABLE t1 (~1000000 rows)
# 2|0|0|SCAN TABLE t2 (~1000000 rows) 0|0|0|COMPOUND SUBQUERIES 1 AND 2
# USING TEMP B-TREE (UNION)
det 5.12 "SELECT a FROM t1 UNION SELECT c FROM t2" {
  1 0 0 {SCAN TABLE t1 (~1000000 rows)}
  2 0 0 {SCAN TABLE t2 (~1000000 rows)}
  0 0 0 {COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION)}
}

# EVIDENCE-OF: R-61538-24748 sqlite> EXPLAIN QUERY PLAN SELECT a FROM
# t1 EXCEPT SELECT d FROM t2 ORDER BY 1; 1|0|0|SCAN TABLE t1 USING
# COVERING INDEX i2 (~1000000 rows) 2|0|0|SCAN TABLE t2 (~1000000 rows)
# 2|0|0|USE TEMP B-TREE FOR ORDER BY 0|0|0|COMPOUND SUBQUERIES 1 AND 2
# (EXCEPT)
det 5.13 "SELECT a FROM t1 EXCEPT SELECT d FROM t2 ORDER BY 1" {
  1 0 0 {SCAN TABLE t1 USING COVERING INDEX i2 (~1000000 rows)}
  2 0 0 {SCAN TABLE t2 (~1000000 rows)}
  2 0 0 {USE TEMP B-TREE FOR ORDER BY}
  0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)}
}





finish_test

  uplevel "execsql {EXPLAIN QUERY PLAN $sql}"
}

# These tests are to check that "EXPLAIN QUERY PLAN" is working as expected.
#
do_execsql_test indexedby-1.2 {
  EXPLAIN QUERY PLAN select * from t1 WHERE a = 10; 
} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~10 rows)}}
do_execsql_test indexedby-1.3 {
  EXPLAIN QUERY PLAN select * from t1 ; 
} {0 0 0 {SCAN TABLE t1 (~1000000 rows)}}
do_execsql_test indexedby-1.4 {
  EXPLAIN QUERY PLAN select * from t1, t2 WHERE c = 10; 
} {
  0 0 1 {SEARCH TABLE t2 USING INDEX i3 (c=?) (~10 rows)} 
  0 1 0 {SCAN TABLE t1 (~1000000 rows)}
}

# Parser tests. Test that an INDEXED BY or NOT INDEX clause can be 
# attached to a table in the FROM clause, but not to a sub-select or
# SQL view. Also test that specifying an index that does not exist or
# is attached to a different table is detected as an error.

#
do_execsql_test indexedby-3.1 {
  EXPLAIN QUERY PLAN SELECT * FROM t1 NOT INDEXED WHERE a = 'one' AND b = 'two'
} {0 0 0 {SCAN TABLE t1 (~10000 rows)}}
do_execsql_test indexedby-3.2 {
  EXPLAIN QUERY PLAN 
  SELECT * FROM t1 INDEXED BY i1 WHERE a = 'one' AND b = 'two'
} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~2 rows)}}
do_execsql_test indexedby-3.3 {
  EXPLAIN QUERY PLAN 
  SELECT * FROM t1 INDEXED BY i2 WHERE a = 'one' AND b = 'two'
} {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?) (~2 rows)}}
do_test indexedby-3.4 {
  catchsql { SELECT * FROM t1 INDEXED BY i2 WHERE a = 'one' }
} {1 {cannot use index: i2}}
do_test indexedby-3.5 {
  catchsql { SELECT * FROM t1 INDEXED BY i2 ORDER BY a }
} {1 {cannot use index: i2}}
do_test indexedby-3.6 {
  catchsql { SELECT * FROM t1 INDEXED BY i1 WHERE a = 'one' }
} {0 {}}
do_test indexedby-3.7 {
  catchsql { SELECT * FROM t1 INDEXED BY i1 ORDER BY a }
} {0 {}}

do_execsql_test indexedby-3.8 {
  EXPLAIN QUERY PLAN 
  SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_1 ORDER BY e 
} {0 0 0 {SCAN TABLE t3 USING INDEX sqlite_autoindex_t3_1 (~1000000 rows)}}
do_execsql_test indexedby-3.9 {
  EXPLAIN QUERY PLAN 
  SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_1 WHERE e = 10 
} {0 0 0 {SEARCH TABLE t3 USING INDEX sqlite_autoindex_t3_1 (e=?) (~1 rows)}}
do_test indexedby-3.10 {
  catchsql { SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_1 WHERE f = 10 }
} {1 {cannot use index: sqlite_autoindex_t3_1}}
do_test indexedby-3.11 {
  catchsql { SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_2 WHERE f = 10 }
} {1 {no such index: sqlite_autoindex_t3_2}}

# Tests for multiple table cases.
#
do_execsql_test indexedby-4.1 {
  EXPLAIN QUERY PLAN SELECT * FROM t1, t2 WHERE a = c 
} {
  0 0 0 {SCAN TABLE t1 (~1000000 rows)} 
  0 1 1 {SEARCH TABLE t2 USING INDEX i3 (c=?) (~10 rows)}
}
do_execsql_test indexedby-4.2 {
  EXPLAIN QUERY PLAN SELECT * FROM t1 INDEXED BY i1, t2 WHERE a = c 
} {
  0 0 1 {SCAN TABLE t2 (~1000000 rows)} 
  0 1 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~10 rows)}
}
do_test indexedby-4.3 {
  catchsql {
    SELECT * FROM t1 INDEXED BY i1, t2 INDEXED BY i3 WHERE a=c
  }
} {1 {cannot use index: i1}}
do_test indexedby-4.4 {
  catchsql {
    SELECT * FROM t2 INDEXED BY i3, t1 INDEXED BY i1 WHERE a=c
  }
} {1 {cannot use index: i3}}

# Test embedding an INDEXED BY in a CREATE VIEW statement. This block
# also tests that nothing bad happens if an index refered to by
# a CREATE VIEW statement is dropped and recreated.
#
do_execsql_test indexedby-5.1 {
  CREATE VIEW v2 AS SELECT * FROM t1 INDEXED BY i1 WHERE a > 5;
  EXPLAIN QUERY PLAN SELECT * FROM v2 
} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a>?) (~330000 rows)}}
do_execsql_test indexedby-5.2 {
  EXPLAIN QUERY PLAN SELECT * FROM v2 WHERE b = 10 
} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a>?) (~33000 rows)}}
do_test indexedby-5.3 {
  execsql { DROP INDEX i1 }
  catchsql { SELECT * FROM v2 }
} {1 {no such index: i1}}
do_test indexedby-5.4 {
  # Recreate index i1 in such a way as it cannot be used by the view query.
  execsql { CREATE INDEX i1 ON t1(b) }
  catchsql { SELECT * FROM v2 }
} {1 {cannot use index: i1}}
do_test indexedby-5.5 {
  # Drop and recreate index i1 again. This time, create it so that it can
  # be used by the query.
  execsql { DROP INDEX i1 ; CREATE INDEX i1 ON t1(a) }
  catchsql { SELECT * FROM v2 }
} {0 {}}

# Test that "NOT INDEXED" may use the rowid index, but not others.
# 
do_execsql_test indexedby-6.1 {
  EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b = 10 ORDER BY rowid 
} {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?) (~10 rows)}}
do_execsql_test indexedby-6.2 {
  EXPLAIN QUERY PLAN SELECT * FROM t1 NOT INDEXED WHERE b = 10 ORDER BY rowid 
} {0 0 0 {SCAN TABLE t1 USING INTEGER PRIMARY KEY (~100000 rows)}}

# Test that "INDEXED BY" can be used in a DELETE statement.
# 
do_execsql_test indexedby-7.1 {
  EXPLAIN QUERY PLAN DELETE FROM t1 WHERE a = 5 
} {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?) (~10 rows)}}
do_execsql_test indexedby-7.2 {
  EXPLAIN QUERY PLAN DELETE FROM t1 NOT INDEXED WHERE a = 5 
} {0 0 0 {SCAN TABLE t1 (~100000 rows)}}
do_execsql_test indexedby-7.3 {
  EXPLAIN QUERY PLAN DELETE FROM t1 INDEXED BY i1 WHERE a = 5 
} {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?) (~10 rows)}}
do_execsql_test indexedby-7.4 {
  EXPLAIN QUERY PLAN DELETE FROM t1 INDEXED BY i1 WHERE a = 5 AND b = 10
} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~2 rows)}}
do_execsql_test indexedby-7.5 {
  EXPLAIN QUERY PLAN DELETE FROM t1 INDEXED BY i2 WHERE a = 5 AND b = 10
} {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?) (~2 rows)}}
do_test indexedby-7.6 {
  catchsql { DELETE FROM t1 INDEXED BY i2 WHERE a = 5}
} {1 {cannot use index: i2}}

# Test that "INDEXED BY" can be used in an UPDATE statement.
# 
do_execsql_test indexedby-8.1 {
  EXPLAIN QUERY PLAN UPDATE t1 SET rowid=rowid+1 WHERE a = 5 
} {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?) (~10 rows)}}
do_execsql_test indexedby-8.2 {
  EXPLAIN QUERY PLAN UPDATE t1 NOT INDEXED SET rowid=rowid+1 WHERE a = 5 
} {0 0 0 {SCAN TABLE t1 (~100000 rows)}}
do_execsql_test indexedby-8.3 {
  EXPLAIN QUERY PLAN UPDATE t1 INDEXED BY i1 SET rowid=rowid+1 WHERE a = 5 
} {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?) (~10 rows)}}
do_execsql_test indexedby-8.4 {
  EXPLAIN QUERY PLAN 
  UPDATE t1 INDEXED BY i1 SET rowid=rowid+1 WHERE a = 5 AND b = 10
} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~2 rows)}}
do_execsql_test indexedby-8.5 {
  EXPLAIN QUERY PLAN 
  UPDATE t1 INDEXED BY i2 SET rowid=rowid+1 WHERE a = 5 AND b = 10
} {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?) (~2 rows)}}
do_test indexedby-8.6 {
  catchsql { UPDATE t1 INDEXED BY i2 SET rowid=rowid+1 WHERE a = 5}
} {1 {cannot use index: i2}}

# Test that bug #3560 is fixed.
#
do_test indexedby-9.1 {

} {t2}

do_test tkt-78e04-2.1 {
  execsql {
    CREATE INDEX "" ON t2(x);
    EXPLAIN QUERY PLAN SELECT * FROM t2 WHERE x=5;
  }
} {0 0 0 {SEARCH TABLE t2 USING COVERING INDEX  (x=?) (~10 rows)}}
do_test tkt-78e04-2.2 {
  execsql {
    DROP INDEX "";
    EXPLAIN QUERY PLAN SELECT * FROM t2 WHERE x=2;
  }
} {0 0 0 {SCAN TABLE t2 (~100000 rows)}}

# These tests perform an EXPLAIN QUERY PLAN on both versions of the 
# SELECT referenced in ticket #3442 (both '5000' and "5000") 
# and verify that the query plan is the same.
#
ifcapable explain {
  do_test tkt3442-1.2 {
    EQP { SELECT node FROM listhash WHERE id='5000' LIMIT 1; }
  } {0 0 0 {SEARCH TABLE listhash USING INDEX ididx (id=?) (~1 rows)}}
  do_test tkt3442-1.3 {
    EQP { SELECT node FROM listhash WHERE id="5000" LIMIT 1; }
  } {0 0 0 {SEARCH TABLE listhash USING INDEX ididx (id=?) (~1 rows)}}
}


# Some extra tests testing other permutations of 5000.
#
ifcapable explain {
  do_test tkt3442-1.4 {
    EQP { SELECT node FROM listhash WHERE id=5000 LIMIT 1; }
  } {0 0 0 {SEARCH TABLE listhash USING INDEX ididx (id=?) (~1 rows)}}
}
do_test tkt3442-1.5 {
  catchsql {
    SELECT node FROM listhash WHERE id=[5000] LIMIT 1;
  }
} {1 {no such column: 5000}}

  CREATE INDEX t301c ON t301(c);
  INSERT INTO t301 VALUES(1,2,3);
  CREATE TABLE t302(x, y);
  ANALYZE;
  explain query plan SELECT * FROM t302, t301 WHERE t302.x=5 AND t301.a=t302.y;
} {
  0 0 0 {SCAN TABLE t302 (~0 rows)} 
  0 1 1 {SEARCH TABLE t301 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}
}
do_execsql_test where3-3.1 {
  explain query plan
  SELECT * FROM t301, t302 WHERE t302.x=5 AND t301.a=t302.y;
} {
  0 0 1 {SCAN TABLE t302 (~0 rows)} 
  0 1 0 {SEARCH TABLE t301 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}
}

# Verify that when there are multiple tables in a join which must be
# full table scans that the query planner attempts put the table with
# the fewest number of output rows as the outer loop.
#
do_execsql_test where3-4.0 {

   SELECT bbb.title AS tag_title 
     FROM aaa JOIN bbb ON bbb.id = aaa.parent  
    WHERE aaa.fk = 'constant'
      AND LENGTH(bbb.title) > 0
      AND bbb.parent = 4
    ORDER BY bbb.title COLLATE NOCASE ASC;
} {
  0 0 0 {SEARCH TABLE aaa USING INDEX aaa_333 (fk=?) (~10 rows)} 
  0 1 1 {SEARCH TABLE bbb USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 
  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
}
do_execsql_test where3-5.1 {
  EXPLAIN QUERY PLAN
   SELECT bbb.title AS tag_title 
     FROM aaa JOIN aaa AS bbb ON bbb.id = aaa.parent  
    WHERE aaa.fk = 'constant'
      AND LENGTH(bbb.title) > 0
      AND bbb.parent = 4
    ORDER BY bbb.title COLLATE NOCASE ASC;
} {
  0 0 0 {SEARCH TABLE aaa USING INDEX aaa_333 (fk=?) (~10 rows)} 
  0 1 1 {SEARCH TABLE aaa AS bbb USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 
  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
}
do_execsql_test where3-5.2 {
  EXPLAIN QUERY PLAN
   SELECT bbb.title AS tag_title 
     FROM bbb JOIN aaa ON bbb.id = aaa.parent  
    WHERE aaa.fk = 'constant'
      AND LENGTH(bbb.title) > 0
      AND bbb.parent = 4
    ORDER BY bbb.title COLLATE NOCASE ASC;
} {
  0 0 1 {SEARCH TABLE aaa USING INDEX aaa_333 (fk=?) (~10 rows)} 
  0 1 0 {SEARCH TABLE bbb USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 
  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
}
do_execsql_test where3-5.3 {
  EXPLAIN QUERY PLAN
   SELECT bbb.title AS tag_title 
     FROM aaa AS bbb JOIN aaa ON bbb.id = aaa.parent  
    WHERE aaa.fk = 'constant'
      AND LENGTH(bbb.title) > 0
      AND bbb.parent = 4
    ORDER BY bbb.title COLLATE NOCASE ASC;
} {
  0 0 1 {SEARCH TABLE aaa USING INDEX aaa_333 (fk=?) (~10 rows)} 
  0 1 0 {SEARCH TABLE aaa AS bbb USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 
  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
}


finish_test

    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 COVERING INDEX t301_c4 (c4=?) (~10 rows)} 
  0 0 1 {SEARCH TABLE t301 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 
  0 1 0 {SEARCH TABLE t302 USING INDEX t302_c8_c3 (c8=? AND c3>?) (~2 rows)} 
  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
}

finish_test

ifcapable explain {
  do_execsql_test where9-3.1 {
    EXPLAIN QUERY PLAN
    SELECT t2.a FROM t1, t2
    WHERE t1.a=80 AND ((t1.c=t2.c AND t1.d=t2.d) OR t1.f=t2.f)
  } {
    0 0 0 {SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 
    0 1 1 {SEARCH TABLE t2 USING INDEX t2d (d=?) (~2 rows)} 
    0 1 1 {SEARCH TABLE t2 USING COVERING INDEX t2f (f=?) (~10 rows)}
  }
  do_execsql_test where9-3.2 {
    EXPLAIN QUERY PLAN
    SELECT coalesce(t2.a,9999)
    FROM t1 LEFT JOIN t2 ON (t1.c+1=t2.c AND t1.d=t2.d) OR (t1.f||'x')=t2.f
    WHERE t1.a=80
  } {
    0 0 0 {SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 
    0 1 1 {SEARCH TABLE t2 USING INDEX t2d (d=?) (~2 rows)} 
    0 1 1 {SEARCH TABLE t2 USING COVERING INDEX t2f (f=?) (~10 rows)}
  }
} 

# Make sure that INDEXED BY and multi-index OR clauses play well with
# one another.
#
do_test where9-4.1 {

ifcapable explain {
  # The (c=31031 OR d IS NULL) clause is preferred over b>1000 because
  # the former is an equality test which is expected to return fewer rows.
  #
  do_execsql_test where9-5.1 {
    EXPLAIN QUERY PLAN SELECT a FROM t1 WHERE b>1000 AND (c=31031 OR d IS NULL)
  } {
    0 0 0 {SEARCH TABLE t1 USING INDEX t1c (c=?) (~10 rows)} 
    0 0 0 {SEARCH TABLE t1 USING INDEX t1d (d=?) (~10 rows)}
  }

  # In contrast, b=1000 is preferred over any OR-clause.
  #
  do_execsql_test where9-5.2 {
    EXPLAIN QUERY PLAN SELECT a FROM t1 WHERE b=1000 AND (c=31031 OR d IS NULL)
  } {
    0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?) (~5 rows)}
  }

  # Likewise, inequalities in an AND are preferred over inequalities in
  # an OR.
  #
  do_execsql_test where9-5.3 {
    EXPLAIN QUERY PLAN SELECT a FROM t1 WHERE b>1000 AND (c>=31031 OR d IS NULL)
  } {
    0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b>?) (~165000 rows)}
  }
}

############################################################################
# Make sure OR-clauses work correctly on UPDATE and DELETE statements.

do_test where9-6.2.1 {