SQLite

  rtree_strategy {SELECT * FROM t1,t2 WHERE k=ii AND x1<10}
} {Ca}

do_test rtree6-1.5 {
  rtree_strategy {SELECT * FROM t1,t2 WHERE k=+ii AND x1<10}
} {Ca}

do_eqp_test rtree6.2.1 {
  SELECT * FROM t1,t2 WHERE k=+ii AND x1<10
} {
  0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2:Ca (~0 rows)} 
  0 1 1 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}

}

do_eqp_test rtree6.2.2 {
  SELECT * FROM t1,t2 WHERE k=ii AND x1<10
} {
  0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2:Ca (~0 rows)} 
  0 1 1 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}

}

do_eqp_test rtree6.2.3 {
  SELECT * FROM t1,t2 WHERE k=ii
} {
  0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2: (~0 rows)} 
  0 1 1 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}

}

do_eqp_test rtree6.2.4 {
  SELECT * FROM t1,t2 WHERE v=10 and x1<10 and x2>10
} {
  0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2:CaEb (~0 rows)} 
  0 1 1 {SCAN TABLE t2 (~100000 rows)}

}

do_eqp_test rtree6.2.5 {
  SELECT * FROM t1,t2 WHERE k=ii AND x1<v
} {
  0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2: (~0 rows)} 
  0 1 1 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}

}

finish_test

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

ifcapable !stat2 {
  finish_test
  return
}

set testprefix analyze2

# Do not use a codec for tests in this file, as the database file is
# manipulated directly using tcl scripts (using the [hexio_write] command).
#
do_not_use_codec

#--------------------------------------------------------------------

  }
  for {set i 0} {$i < 1000} {incr i} {
    execsql { INSERT INTO t1 VALUES($i, $i) }
  }
  execsql COMMIT
  execsql ANALYZE
} {}
do_eqp_test 2.2 {
  SELECT * FROM t1 WHERE x>500 AND y>700
} {
  0 0 0 {SEARCH TABLE t1 USING INDEX t1_y (y>?) (~100 rows)}
}
do_eqp_test 2.3 {
  SELECT * FROM t1 WHERE x>700 AND y>500
} {
  0 0 0 {SEARCH TABLE t1 USING INDEX t1_x (x>?) (~100 rows)}
}
do_eqp_test 2.3 {
  SELECT * FROM t1 WHERE y>700 AND x>500
} {
  0 0 0 {SEARCH TABLE t1 USING INDEX t1_y (y>?) (~100 rows)}
}
do_eqp_test 2.4 {
  SELECT * FROM t1 WHERE y>500 AND x>700
} {
  0 0 0 {SEARCH TABLE t1 USING INDEX t1_x (x>?) (~100 rows)}
}
do_eqp_test 2.5 {
  SELECT * FROM t1 WHERE x BETWEEN 100 AND 200 AND y BETWEEN 400 AND 700
} {
  0 0 0 {SEARCH TABLE t1 USING INDEX t1_x (x>? AND x<?) (~25 rows)}
}
do_eqp_test 2.6 {
  SELECT * FROM t1 WHERE x BETWEEN 100 AND 500 AND y BETWEEN 400 AND 700
} {
  0 0 0 {SEARCH TABLE t1 USING INDEX t1_y (y>? AND y<?) (~75 rows)}
}
do_eqp_test 2.7 {
  SELECT * FROM t1 WHERE x BETWEEN -400 AND -300 AND y BETWEEN 100 AND 300
} {
  0 0 0 {SEARCH TABLE t1 USING INDEX t1_x (x>? AND x<?) (~25 rows)}
}
do_eqp_test 2.8 {
  SELECT * FROM t1 WHERE x BETWEEN 100 AND 300 AND y BETWEEN -400 AND -300
} {
  0 0 0 {SEARCH TABLE t1 USING INDEX t1_y (y>? AND y<?) (~25 rows)}
}
do_eqp_test 2.9 {
  SELECT * FROM t1 WHERE x BETWEEN 500 AND 100 AND y BETWEEN 100 AND 300
} {
  0 0 0 {SEARCH TABLE t1 USING INDEX t1_x (x>? AND x<?) (~25 rows)}
}
do_eqp_test 2.10 {
  SELECT * FROM t1 WHERE x BETWEEN 100 AND 300 AND y BETWEEN 500 AND 100
} {
  0 0 0 {SEARCH TABLE t1 USING INDEX t1_y (y>? AND y<?) (~25 rows)}
}

do_test analyze2-3.1 {
  set alphabet [list a b c d e f g h i j]
  execsql BEGIN
  for {set i 0} {$i < 1000} {incr i} {
    set str    [lindex $alphabet [expr ($i/100)%10]] 
    append str [lindex $alphabet [expr ($i/ 10)%10]]

    SELECT tbl,idx,group_concat(sample,' ') 
    FROM sqlite_stat2 
    WHERE idx = 't1_y' 
    GROUP BY tbl,idx
  }
} {t1 t1_y {100 299 499 699 899 ajj cjj ejj gjj ijj}}

do_eqp_test 3.3 {
  SELECT * FROM t1 WHERE x BETWEEN 100 AND 500 AND y BETWEEN 'a' AND 'b'
} {
  0 0 0 {SEARCH TABLE t1 USING INDEX t1_y (y>? AND y<?) (~50 rows)}
}
do_eqp_test 3.4 {
  SELECT * FROM t1 WHERE x BETWEEN 100 AND 400 AND y BETWEEN 'a' AND 'h'
} {
  0 0 0 {SEARCH TABLE t1 USING INDEX t1_x (x>? AND x<?) (~50 rows)}
}
do_eqp_test 3.5 {
  SELECT * FROM t1 WHERE x<'a' AND y>'h'
} {
  0 0 0 {SEARCH TABLE t1 USING INDEX t1_y (y>?) (~66 rows)}
}
do_eqp_test 3.6 {
  SELECT * FROM t1 WHERE x<444 AND y>'h'
} {
  0 0 0 {SEARCH TABLE t1 USING INDEX t1_y (y>?) (~66 rows)}
}
do_eqp_test 3.7 {
  SELECT * FROM t1 WHERE x<221 AND y>'g'
} {
  0 0 0 {SEARCH TABLE t1 USING INDEX t1_x (x<?) (~66 rows)}
}

do_test analyze2-4.1 {
  execsql { CREATE TABLE t3(a COLLATE nocase, b) }
  execsql { CREATE INDEX t3a ON t3(a) }
  execsql { CREATE INDEX t3b ON t3(b) }
  set alphabet [list A b C d E f G h I j]
  execsql BEGIN

    SELECT tbl,idx,group_concat(sample,' ') 
    FROM sqlite_stat2 
    WHERE idx = 't3b' 
    GROUP BY tbl,idx
  }
} {t3 t3b {AbA CIj EIj GIj IIj bIj dIj fIj hIj jIj}}

do_eqp_test 4.4 {
  SELECT * FROM t3 WHERE a > 'A' AND a < 'C' AND b > 'A' AND b < 'C'
} {
  0 0 0 {SEARCH TABLE t3 USING INDEX t3b (b>? AND b<?) (~11 rows)}
}
do_eqp_test 4.5 {
  SELECT * FROM t3 WHERE a > 'A' AND a < 'c' AND b > 'A' AND b < 'c'
} {
  0 0 0 {SEARCH TABLE t3 USING INDEX t3a (a>? AND a<?) (~22 rows)}
}

ifcapable utf16 {
  proc test_collate {enc lhs rhs} {
    # puts $enc
    return [string compare $lhs $rhs]
  }
  do_test analyze2-5.1 {

    execsql { 
      SELECT tbl,idx,group_concat(sample,' ') 
      FROM sqlite_stat2 
      WHERE tbl = 't4' 
      GROUP BY tbl,idx
    }
  } {t4 t4x {afa bej cej dej eej fej gej hej iej jej}}
  do_eqp_test 5.3 {
    SELECT * FROM t4 WHERE x>'ccc'
  } {0 0 0 {SEARCH TABLE t4 USING COVERING INDEX t4x (x>?) (~800 rows)}}
  do_eqp_test 5.4 {
    SELECT * FROM t4 AS t41, t4 AS t42 WHERE t41.x>'ccc' AND t42.x>'ggg'
  } {
    0 0 1 {SEARCH TABLE t4 AS t42 USING COVERING INDEX t4x (x>?) (~300 rows)} 
    0 1 0 {SEARCH TABLE t4 AS t41 USING COVERING INDEX t4x (x>?) (~800 rows)}
  }
  do_eqp_test 5.5 {
    SELECT * FROM t4 AS t41, t4 AS t42 WHERE t41.x>'ddd' AND t42.x>'ccc'
  } {
    0 0 0 {SEARCH TABLE t4 AS t41 USING COVERING INDEX t4x (x>?) (~700 rows)} 
    0 1 1 {SEARCH TABLE t4 AS t42 USING COVERING INDEX t4x (x>?) (~800 rows)}
  }
}

#--------------------------------------------------------------------
# These tests, analyze2-6.*, verify that the library behaves correctly
# when one of the sqlite_stat1 and sqlite_stat2 tables is missing.
#
# If the sqlite_stat1 table is not present, then the sqlite_stat2

} {}

do_test analyze2-6.1.1 {
  eqp {SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
       t5.a = 1 AND
       t6.a = 1 AND t6.b = 1
  }
} {0 0 1 {SEARCH TABLE t6 USING COVERING INDEX t6i (a=? AND b=?) (~9 rows)} 0 1 0 {SEARCH TABLE t5 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}
do_test analyze2-6.1.2 {
  db cache flush
  execsql ANALYZE
  eqp {SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
       t5.a = 1 AND
       t6.a = 1 AND t6.b = 1
  }
} {0 0 0 {SEARCH TABLE t5 USING COVERING INDEX t5i (a=?) (~1 rows)} 0 1 1 {SEARCH TABLE t6 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}
do_test analyze2-6.1.3 {
  sqlite3 db test.db
  eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
       t5.a = 1 AND
       t6.a = 1 AND t6.b = 1
  }
} {0 0 0 {SEARCH TABLE t5 USING COVERING INDEX t5i (a=?) (~1 rows)} 0 1 1 {SEARCH TABLE t6 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}
do_test analyze2-6.1.4 {
  execsql { 
    PRAGMA writable_schema = 1;
    DELETE FROM sqlite_master WHERE tbl_name = 'sqlite_stat2';
  }
  sqlite3 db test.db
  eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
       t5.a = 1 AND
       t6.a = 1 AND t6.b = 1
  }
} {0 0 0 {SEARCH TABLE t5 USING COVERING INDEX t5i (a=?) (~1 rows)} 0 1 1 {SEARCH TABLE t6 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}
do_test analyze2-6.1.5 {
  execsql { 
    PRAGMA writable_schema = 1;
    DELETE FROM sqlite_master WHERE tbl_name = 'sqlite_stat1';
  }
  sqlite3 db test.db
  eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
       t5.a = 1 AND
       t6.a = 1 AND t6.b = 1
  }
} {0 0 1 {SEARCH TABLE t6 USING COVERING INDEX t6i (a=? AND b=?) (~9 rows)} 0 1 0 {SEARCH TABLE t5 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}
do_test analyze2-6.1.6 {
  execsql { 
    PRAGMA writable_schema = 1;
    INSERT INTO sqlite_master SELECT * FROM master;
  }
  sqlite3 db test.db
  eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
       t5.a = 1 AND
       t6.a = 1 AND t6.b = 1
  }
} {0 0 0 {SEARCH TABLE t5 USING COVERING INDEX t5i (a=?) (~1 rows)} 0 1 1 {SEARCH TABLE t6 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}

do_test analyze2-6.2.1 {
  execsql { 
    DELETE FROM sqlite_stat1;
    DELETE FROM sqlite_stat2;
  }
  sqlite3 db test.db
  eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
        t5.a>1 AND t5.a<15 AND
        t6.a>1
  }
} {0 0 0 {SEARCH TABLE t5 USING COVERING INDEX t5i (a>? AND a<?) (~110000 rows)} 0 1 1 {SEARCH TABLE t6 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}
do_test analyze2-6.2.2 {
  db cache flush
  execsql ANALYZE
  eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
        t5.a>1 AND t5.a<15 AND
        t6.a>1
  }
} {0 0 1 {SEARCH TABLE t6 USING COVERING INDEX t6i (a>?) (~2 rows)} 0 1 0 {SEARCH TABLE t5 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}
do_test analyze2-6.2.3 {
  sqlite3 db test.db
  eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
        t5.a>1 AND t5.a<15 AND
        t6.a>1
  }
} {0 0 1 {SEARCH TABLE t6 USING COVERING INDEX t6i (a>?) (~2 rows)} 0 1 0 {SEARCH TABLE t5 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}
do_test analyze2-6.2.4 {
  execsql { 
    PRAGMA writable_schema = 1;
    DELETE FROM sqlite_master WHERE tbl_name = 'sqlite_stat1';
  }
  sqlite3 db test.db
  eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
        t5.a>1 AND t5.a<15 AND
        t6.a>1
  }
} {0 0 0 {SEARCH TABLE t5 USING COVERING INDEX t5i (a>? AND a<?) (~110000 rows)} 0 1 1 {SEARCH TABLE t6 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}
do_test analyze2-6.2.5 {
  execsql { 
    PRAGMA writable_schema = 1;
    DELETE FROM sqlite_master WHERE tbl_name = 'sqlite_stat2';
  }
  sqlite3 db test.db
  eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
        t5.a>1 AND t5.a<15 AND
        t6.a>1
  }
} {0 0 0 {SEARCH TABLE t5 USING COVERING INDEX t5i (a>? AND a<?) (~110000 rows)} 0 1 1 {SEARCH TABLE t6 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}
do_test analyze2-6.2.6 {
  execsql { 
    PRAGMA writable_schema = 1;
    INSERT INTO sqlite_master SELECT * FROM master;
  }
  sqlite3 db test.db
  execsql ANALYZE
  eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
        t5.a>1 AND t5.a<15 AND
        t6.a>1
  }
} {0 0 1 {SEARCH TABLE t6 USING COVERING INDEX t6i (a>?) (~2 rows)} 0 1 0 {SEARCH TABLE t5 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}

#--------------------------------------------------------------------
# These tests, analyze2-7.*, test that the sqlite_stat2 functionality
# works in shared-cache mode. Note that these tests reuse the database
# created for the analyze2-6.* tests.
#
ifcapable shared_cache {

  } {20}

  do_test analyze2-7.5 {
    eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
          t5.a>1 AND t5.a<15 AND
          t6.a>1
    } db1
  } {0 0 1 {SEARCH TABLE t6 USING COVERING INDEX t6i (a>?) (~2 rows)} 0 1 0 {SEARCH TABLE t5 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}
  do_test analyze2-7.6 {
    incr_schema_cookie test.db
    execsql { SELECT * FROM sqlite_master } db2
    eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
          t5.a>1 AND t5.a<15 AND
          t6.a>1
    } db2
  } {0 0 1 {SEARCH TABLE t6 USING COVERING INDEX t6i (a>?) (~2 rows)} 0 1 0 {SEARCH TABLE t5 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}
  do_test analyze2-7.7 {
    incr_schema_cookie test.db
    execsql { SELECT * FROM sqlite_master } db1
    eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
          t5.a>1 AND t5.a<15 AND
          t6.a>1
    } db1
  } {0 0 1 {SEARCH TABLE t6 USING COVERING INDEX t6i (a>?) (~2 rows)} 0 1 0 {SEARCH TABLE t5 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}

  do_test analyze2-7.8 {
    execsql { DELETE FROM sqlite_stat2 } db2
    execsql { SELECT * FROM sqlite_master } db1
    eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
          t5.a>1 AND t5.a<15 AND
          t6.a>1
    } db1
  } {0 0 1 {SEARCH TABLE t6 USING COVERING INDEX t6i (a>?) (~2 rows)} 0 1 0 {SEARCH TABLE t5 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}
  do_test analyze2-7.9 {
    execsql { SELECT * FROM sqlite_master } db2
    eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
          t5.a>1 AND t5.a<15 AND
          t6.a>1
    } db2
  } {0 0 1 {SEARCH TABLE t6 USING COVERING INDEX t6i (a>?) (~2 rows)} 0 1 0 {SEARCH TABLE t5 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}

  do_test analyze2-7.10 {
    incr_schema_cookie test.db
    execsql { SELECT * FROM sqlite_master } db1
    eqp { SELECT * FROM t5,t6 WHERE t5.rowid=t6.rowid AND 
          t5.a>1 AND t5.a<15 AND
          t6.a>1
    } db1
  } {0 0 0 {SEARCH TABLE t5 USING COVERING INDEX t5i (a>? AND a<?) (~2 rows)} 0 1 1 {SEARCH TABLE t6 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}

  db1 close
  db2 close
  sqlite3_enable_shared_cache $::enable_shared_cache
}

finish_test

  }
  execsql {
    COMMIT;
    ANALYZE;
  }
} {}

do_eqp_test analyze3-1.1.2 {
  SELECT sum(y) FROM t1 WHERE x>200 AND x<300
} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (x>? AND x<?) (~100 rows)}}
do_eqp_test analyze3-1.1.3 {
  SELECT sum(y) FROM t1 WHERE x>0 AND x<1100 
} {0 0 0 {SCAN TABLE t1 (~111 rows)}}

do_test analyze3-1.1.4 {
  sf_execsql { SELECT sum(y) FROM t1 WHERE x>200 AND x<300 }
} {199 0 14850}
do_test analyze3-1.1.5 {
  set l [string range "200" 0 end]
  set u [string range "300" 0 end]

      CREATE TABLE t2(x TEXT, y);
      INSERT INTO t2 SELECT * FROM t1;
      CREATE INDEX i2 ON t2(x);
    COMMIT;
    ANALYZE;
  }
} {}
do_eqp_test analyze3-1.2.2 {
  SELECT sum(y) FROM t2 WHERE x>1 AND x<2
} {0 0 0 {SEARCH TABLE t2 USING INDEX i2 (x>? AND x<?) (~200 rows)}}
do_eqp_test analyze3-1.2.3 {
  SELECT sum(y) FROM t2 WHERE x>0 AND x<99
} {0 0 0 {SCAN TABLE t2 (~111 rows)}}
do_test analyze3-1.2.4 {
  sf_execsql { SELECT sum(y) FROM t2 WHERE x>12 AND x<20 }
} {161 0 4760}
do_test analyze3-1.2.5 {
  set l [string range "12" 0 end]
  set u [string range "20" 0 end]
  sf_execsql {SELECT typeof($l), typeof($u), sum(y) FROM t2 WHERE x>$l AND x<$u}

      CREATE TABLE t3(y TEXT, x INTEGER);
      INSERT INTO t3 SELECT y, x FROM t1;
      CREATE INDEX i3 ON t3(x);
    COMMIT;
    ANALYZE;
  }
} {}
do_eqp_test analyze3-1.3.2 {
  SELECT sum(y) FROM t3 WHERE x>200 AND x<300
} {0 0 0 {SEARCH TABLE t3 USING INDEX i3 (x>? AND x<?) (~100 rows)}}
do_eqp_test analyze3-1.3.3 {
  SELECT sum(y) FROM t3 WHERE x>0 AND x<1100
} {0 0 0 {SCAN TABLE t3 (~111 rows)}}

do_test analyze3-1.3.4 {
  sf_execsql { SELECT sum(y) FROM t3 WHERE x>200 AND x<300 }
} {199 0 14850}
do_test analyze3-1.3.5 {
  set l [string range "200" 0 end]
  set u [string range "300" 0 end]

    append t [lindex {a b c d e f g h i j} [expr $i/100]]
    append t [lindex {a b c d e f g h i j} [expr ($i/10)%10]]
    append t [lindex {a b c d e f g h i j} [expr ($i%10)]]
    execsql { INSERT INTO t1 VALUES($i, $t) }
  }
  execsql COMMIT
} {}
do_eqp_test analyze3-2.2 {
  SELECT count(a) FROM t1 WHERE b LIKE 'a%'
} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (b>? AND b<?) (~55000 rows)}}
do_eqp_test analyze3-2.3 {
  SELECT count(a) FROM t1 WHERE b LIKE '%a'
} {0 0 0 {SCAN TABLE t1 (~500000 rows)}}

do_test analyze3-2.4 {
  sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE 'a%' }
} {101 0 100}
do_test analyze3-2.5 {
  sf_execsql { SELECT count(*) FROM t1 WHERE b LIKE '%a' }
} {999 999 100}

#
# eqp-1.*:        Assorted tests.
# eqp-2.*:        Tests for single select statements.
# eqp-3.*:        Select statements that execute sub-selects.
# eqp-4.*:        Compound select statements.
#




proc det {args} { uplevel do_eqp_test $args }

do_execsql_test 1.1 {
  CREATE TABLE t1(a, b);
  CREATE INDEX i1 ON t1(a);
  CREATE INDEX i2 ON t1(b);
  CREATE TABLE t2(a, b);

do_test fts3query-3.3 {
  execsql { SELECT mit(matchinfo(foobar)) FROM foobar WHERE foobar MATCH 'the' }
} {{1 1 3 3 1}}

# The following tests check that ticket 775b39dd3c has been fixed.
#



do_test fts3query-4.1 {
  execsql {
    DROP TABLE IF EXISTS t1;
    CREATE TABLE t1(number INTEGER PRIMARY KEY, date);
    CREATE INDEX i1 ON t1(date);
    CREATE VIRTUAL TABLE ft USING fts3(title);
    CREATE TABLE bt(title);
  }
} {}
do_eqp_test fts3query-4.2 {
  SELECT t1.number FROM t1, ft WHERE t1.number=ft.rowid ORDER BY t1.date
} {
  0 0 0 {SCAN TABLE t1 USING COVERING INDEX i1 (~1000000 rows)} 
  0 1 1 {SCAN TABLE ft VIRTUAL TABLE INDEX 1: (~0 rows)}
}
do_eqp_test fts3query-4.3 {
  SELECT t1.number FROM ft, t1 WHERE t1.number=ft.rowid ORDER BY t1.date
} {
  0 0 1 {SCAN TABLE t1 USING COVERING INDEX i1 (~1000000 rows)} 
  0 1 0 {SCAN TABLE ft VIRTUAL TABLE INDEX 1: (~0 rows)}
}
do_eqp_test fts3query-4.4 {
  SELECT t1.number FROM t1, bt WHERE t1.number=bt.rowid ORDER BY t1.date
} {
  0 0 0 {SCAN TABLE t1 USING COVERING INDEX i1 (~1000000 rows)} 
  0 1 1 {SEARCH TABLE bt USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}
}
do_eqp_test fts3query-4.5 {
  SELECT t1.number FROM bt, t1 WHERE t1.number=bt.rowid ORDER BY t1.date
} {
  0 0 1 {SCAN TABLE t1 USING COVERING INDEX i1 (~1000000 rows)} 
  0 1 0 {SEARCH TABLE bt USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}
}


# Test that calling matchinfo() with the wrong number of arguments, or with
# an invalid argument returns an error.
#
do_execsql_test 5.1 {
  CREATE VIRTUAL TABLE t2 USING FTS4;

  fix_testname testname
  uplevel do_test $testname [list "execsql {$sql}"] [list [list {*}$result]]
}
proc do_catchsql_test {testname sql result} {
  fix_testname testname
  uplevel do_test $testname [list "catchsql {$sql}"] [list $result]
}
proc do_eqp_test {name sql res} {
  uplevel do_execsql_test $name [list "EXPLAIN QUERY PLAN $sql"] [list $res]
}

#-------------------------------------------------------------------------
#   Usage: do_select_tests PREFIX ?SWITCHES? TESTLIST
#
# Where switches are:
#
#   -errorformat FMTSTRING