ANALYZE main.t1;
  }
} {0 {}}
do_test analyze-1.11 {
  execsql {
    SELECT * FROM sqlite_stat1
  }
} {}
do_test analyze-1.12 {
  catchsql {
    ANALYZE t1;
  }
} {0 {}}
do_test analyze-1.13 {
  execsql {
    SELECT * FROM sqlite_stat1
  }
} {}

# Create some indices that can be analyzed.  But do not yet add
# data.  Without data in the tables, no analysis is done.
#
do_test analyze-2.1 {
  execsql {
    CREATE INDEX t1i1 ON t1(a);
    ANALYZE main.t1;
    SELECT * FROM sqlite_stat1 ORDER BY idx;
  }
} {}
do_test analyze-2.2 {
  execsql {
    CREATE INDEX t1i2 ON t1(b);
    ANALYZE t1;
    SELECT * FROM sqlite_stat1 ORDER BY idx;
  }
} {}
do_test analyze-2.3 {
  execsql {
    CREATE INDEX t1i3 ON t1(a,b);
    ANALYZE main;
    SELECT * FROM sqlite_stat1 ORDER BY idx;
  }
} {}

# Start adding data to the table.  Verify that the analysis
# is done correctly.
#
do_test analyze-3.1 {
  execsql {
    INSERT INTO t1 VALUES(1,2);

    ANALYZE main.t1;
  }
} {0 {}}
do_test analyze-1.11 {
  execsql {
    SELECT * FROM sqlite_stat1
  }
} {t1 {} 0}
do_test analyze-1.12 {
  catchsql {
    ANALYZE t1;
  }
} {0 {}}
do_test analyze-1.13 {
  execsql {
    SELECT * FROM sqlite_stat1
  }
} {t1 {} 0}

# Create some indices that can be analyzed.  But do not yet add
# data.  Without data in the tables, no analysis is done.
#
do_test analyze-2.1 {
  execsql {
    CREATE INDEX t1i1 ON t1(a);
    ANALYZE main.t1;
    SELECT * FROM sqlite_stat1 ORDER BY idx;
  }
} {t1 {} 0}
do_test analyze-2.2 {
  execsql {
    CREATE INDEX t1i2 ON t1(b);
    ANALYZE t1;
    SELECT * FROM sqlite_stat1 ORDER BY idx;
  }
} {t1 {} 0}
do_test analyze-2.3 {
  execsql {
    CREATE INDEX t1i3 ON t1(a,b);
    ANALYZE main;
    SELECT * FROM sqlite_stat1 ORDER BY idx;
  }
} {t1 {} 0}

# Start adding data to the table.  Verify that the analysis
# is done correctly.
#
do_test analyze-3.1 {
  execsql {
    INSERT INTO t1 VALUES(1,2);

  }
  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<?) (~12 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<?) (~12 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<?) (~12 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<?) (~12 rows)}
}

do_test analyze2-3.1 {
  set alphabet [list a b c d e f g h i j]
................................................................................
    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<?) (~100 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) }
................................................................................
    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} {
................................................................................
    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)}
  }
}

  }
  for {set i 0} {$i < 1000} {incr i} {
    execsql { INSERT INTO t1 VALUES($i, $i) }
  }
  execsql COMMIT
  execsql ANALYZE
} {}
do_eqp_test analyze2-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 analyze2-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 analyze2-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 analyze2-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 analyze2-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 analyze2-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 analyze2-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<?) (~12 rows)}
}
do_eqp_test analyze2-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<?) (~12 rows)}
}
do_eqp_test analyze2-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<?) (~12 rows)}
}
do_eqp_test analyze2-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<?) (~12 rows)}
}

do_test analyze2-3.1 {
  set alphabet [list a b c d e f g h i j]
................................................................................
    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 analyze2-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 analyze2-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<?) (~100 rows)}
}
do_eqp_test analyze2-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 analyze2-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 analyze2-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) }
................................................................................
    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 analyze2-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 analyze2-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} {
................................................................................
    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 analyze2-5.3 {
    SELECT * FROM t4 WHERE x>'ccc'
  } {0 0 0 {SEARCH TABLE t4 USING COVERING INDEX t4x (x>?) (~800 rows)}}
  do_eqp_test analyze2-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 analyze2-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)}
  }
}

  }
  execsql {
    COMMIT;
    ANALYZE;
  }
} {}

do_test analyze3-1.1.2 {
  eqp { SELECT sum(y) FROM t1 WHERE x>200 AND x<300 }
} {0 0 {TABLE t1 WITH INDEX i1}}
do_test analyze3-1.1.3 {
  eqp { SELECT sum(y) FROM t1 WHERE x>0 AND x<1100 }
} {0 0 {TABLE t1}}

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_test analyze3-1.2.2 {
  eqp { SELECT sum(y) FROM t2 WHERE x>1 AND x<2 }
} {0 0 {TABLE t2 WITH INDEX i2}}
do_test analyze3-1.2.3 {
  eqp { SELECT sum(y) FROM t2 WHERE x>0 AND x<99 }
} {0 0 {TABLE t2}}
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_test analyze3-1.3.2 {
  eqp { SELECT sum(y) FROM t3 WHERE x>200 AND x<300 }
} {0 0 {TABLE t3 WITH INDEX i3}}
do_test analyze3-1.3.3 {
  eqp { SELECT sum(y) FROM t3 WHERE x>0 AND x<1100 }
} {0 0 {TABLE t3}}

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_test analyze3-2.2 {
  eqp { SELECT count(a) FROM t1 WHERE b LIKE 'a%' }
} {0 0 {TABLE t1 WITH INDEX i1}}
do_test analyze3-2.3 {
  eqp { SELECT count(a) FROM t1 WHERE b LIKE '%a' }
} {0 0 {TABLE t1}}

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}

  }
  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<?) (~30000 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}

      CREATE TABLE t4(a,b,c);
      CREATE INDEX t4i1 ON t4(a);
      CREATE INDEX t4i2 ON t4(b,a,c);
      INSERT INTO t4 VALUES(1,2,3);
      ANALYZE;
    }
    set ::authargs
  } {t4 {} main {}}
  do_test auth-1.295 {
    execsql {
      SELECT count(*) FROM sqlite_stat1;
    }
  } 2
  proc auth {code args} {
    if {$code=="SQLITE_ANALYZE"} {
      set ::authargs [concat $::authargs $args]
      return SQLITE_DENY
    }
    return SQLITE_OK
  }
................................................................................
      ANALYZE;
    }
  } {1 {not authorized}}
  do_test auth-1.297 {
    execsql {
      SELECT count(*) FROM sqlite_stat1;
    }
  } 2
} ;# ifcapable analyze


# Authorization for ALTER TABLE ADD COLUMN.
# These tests are omitted if the library
# was built without ALTER TABLE support.
ifcapable {altertable} {

      CREATE TABLE t4(a,b,c);
      CREATE INDEX t4i1 ON t4(a);
      CREATE INDEX t4i2 ON t4(b,a,c);
      INSERT INTO t4 VALUES(1,2,3);
      ANALYZE;
    }
    set ::authargs
  } {t4 {} main {} t2 {} main {}}
  do_test auth-1.295 {
    execsql {
      SELECT count(*) FROM sqlite_stat1;
    }
  } 3
  proc auth {code args} {
    if {$code=="SQLITE_ANALYZE"} {
      set ::authargs [concat $::authargs $args]
      return SQLITE_DENY
    }
    return SQLITE_OK
  }
................................................................................
      ANALYZE;
    }
  } {1 {not authorized}}
  do_test auth-1.297 {
    execsql {
      SELECT count(*) FROM sqlite_stat1;
    }
  } 3
} ;# ifcapable analyze


# Authorization for ALTER TABLE ADD COLUMN.
# These tests are omitted if the library
# was built without ALTER TABLE support.
ifcapable {altertable} {

    SELECT count(*) FROM t4;
  }
} {4096}
do_test autoindex1-401 {
  db eval {
    SELECT count(*)
      FROM t4 AS x1
/*      JOIN t4 AS x2 ON x2.a=x1.b
      JOIN t4 AS x3 ON x3.a=x2.b
      JOIN t4 AS x4 ON x4.a=x3.b
      JOIN t4 AS x5 ON x5.a=x4.b
      JOIN t4 AS x6 ON x6.a=x5.b
      JOIN t4 AS x7 ON x7.a=x6.b
      JOIN t4 AS x8 ON x8.a=x7.b
      JOIN t4 AS x9 ON x9.a=x8.b
      JOIN t4 AS x10 ON x10.a=x9.b;*/
  }
} {4087 FIXME}

# Ticket [8011086c85c6c404014c947fcf3eb9f42b184a0d] from 2010-07-08
# Make sure automatic indices are not created for the RHS of an IN expression
# that is not a correlated subquery.
#
do_test autoindex1-500 {
  db eval {
    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 {TABLE t501 USING PRIMARY KEY} 0 0 {TABLE t502}}
do_test autoindex1-501 {
  db eval {
    EXPLAIN QUERY PLAN
    SELECT b FROM t501
     WHERE t501.a IN (SELECT x FROM t502 WHERE y=t501.b);
  }
} {0 0 {TABLE t501} 0 0 {TABLE t502 WITH AUTOMATIC INDEX}}
do_test autoindex1-502 {
  db eval {
    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 {TABLE t501 USING PRIMARY KEY} 0 0 {TABLE t502}}
    

finish_test

    SELECT count(*) FROM t4;
  }
} {4096}
do_test autoindex1-401 {
  db eval {
    SELECT count(*)
      FROM t4 AS x1
      JOIN t4 AS x2 ON x2.a=x1.b
      JOIN t4 AS x3 ON x3.a=x2.b
      JOIN t4 AS x4 ON x4.a=x3.b
      JOIN t4 AS x5 ON x5.a=x4.b
      JOIN t4 AS x6 ON x6.a=x5.b
      JOIN t4 AS x7 ON x7.a=x6.b
      JOIN t4 AS x8 ON x8.a=x7.b
      JOIN t4 AS x9 ON x9.a=x8.b
      JOIN t4 AS x10 ON x10.a=x9.b;
  }
} {4087}

# Ticket [8011086c85c6c404014c947fcf3eb9f42b184a0d] from 2010-07-08
# Make sure automatic indices are not created for the RHS of an IN expression
# that is not a correlated subquery.
#
do_test autoindex1-500 {
  db eval {
    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 {SCAN TABLE t502 (~100000 rows)}}
do_test autoindex1-501 {
  db eval {
    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 {SEARCH TABLE t502 USING AUTOMATIC COVERING INDEX (y=?) (~7 rows)}}
do_test autoindex1-502 {
  db eval {
    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 {SCAN TABLE t502 (~100000 rows)}}


finish_test

} {}
do_test e_fkey-25.2 {
  execsql {
    PRAGMA foreign_keys = OFF;
    EXPLAIN QUERY PLAN DELETE FROM artist WHERE 1;
    EXPLAIN QUERY PLAN SELECT rowid FROM track WHERE trackartist = ?;
  }
} {0 0 {TABLE artist} 0 0 {TABLE track}}
do_test e_fkey-25.3 {
  execsql { 
    PRAGMA foreign_keys = ON;
    EXPLAIN QUERY PLAN DELETE FROM artist WHERE 1;
  }
} {0 0 {TABLE artist} 0 0 {TABLE track}}
do_test e_fkey-25.4 {
  execsql {
    INSERT INTO artist VALUES(5, 'artist 5');
    INSERT INTO artist VALUES(6, 'artist 6');
    INSERT INTO artist VALUES(7, 'artist 7');
    INSERT INTO track VALUES(1, 'track 1', 5);
    INSERT INTO track VALUES(2, 'track 2', 6);
................................................................................
} {}
do_test e_fkey-27.2 {
  eqp { INSERT INTO artist VALUES(?, ?) }
} {}
do_test e_fkey-27.3 {
  eqp { UPDATE artist SET artistid = ?, artistname = ? }
} [list \
  0 0 {TABLE artist} \
  0 0 {TABLE track WITH INDEX trackindex} \
  0 0 {TABLE track WITH INDEX trackindex}
]
do_test e_fkey-27.4 {
  eqp { DELETE FROM artist }
} [list \
  0 0 {TABLE artist} \
  0 0 {TABLE track WITH INDEX trackindex}
]


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

} {}
do_test e_fkey-25.2 {
  execsql {
    PRAGMA foreign_keys = OFF;
    EXPLAIN QUERY PLAN DELETE FROM artist WHERE 1;
    EXPLAIN QUERY PLAN SELECT rowid FROM track WHERE trackartist = ?;
  }
} {0 0 0 {SCAN TABLE artist (~1000000 rows)} 0 0 0 {SCAN TABLE track (~100000 rows)}}
do_test e_fkey-25.3 {
  execsql { 
    PRAGMA foreign_keys = ON;
    EXPLAIN QUERY PLAN DELETE FROM artist WHERE 1;
  }
} {0 0 0 {SCAN TABLE artist (~1000000 rows)} 0 0 0 {SCAN TABLE track (~100000 rows)}}
do_test e_fkey-25.4 {
  execsql {
    INSERT INTO artist VALUES(5, 'artist 5');
    INSERT INTO artist VALUES(6, 'artist 6');
    INSERT INTO artist VALUES(7, 'artist 7');
    INSERT INTO track VALUES(1, 'track 1', 5);
    INSERT INTO track VALUES(2, 'track 2', 6);
................................................................................
} {}
do_test e_fkey-27.2 {
  eqp { INSERT INTO artist VALUES(?, ?) }
} {}
do_test e_fkey-27.3 {
  eqp { UPDATE artist SET artistid = ?, artistname = ? }
} [list \
  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_test e_fkey-27.4 {
  eqp { DELETE FROM artist }
} [list \
  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
###########################################################################

    INSERT INTO t1 VALUES('1', 'I',   'one');
    INSERT INTO t1 VALUES('2', 'IV',  'four');
    INSERT INTO t1 VALUES('2', NULL,  'three');
    INSERT INTO t1 VALUES('2', 'II',  'two');
    INSERT INTO t1 VALUES('2', 'V',   'five');
    INSERT INTO t1 VALUES('3', 'VI',  'six');
    COMMIT;

  }
} {}
do_test minmax3-1.1.1 {
  # Linear scan.
  count { SELECT max(y) FROM t1 WHERE x = '2'; }
} {V 5}
do_test minmax3-1.1.2 {

    INSERT INTO t1 VALUES('1', 'I',   'one');
    INSERT INTO t1 VALUES('2', 'IV',  'four');
    INSERT INTO t1 VALUES('2', NULL,  'three');
    INSERT INTO t1 VALUES('2', 'II',  'two');
    INSERT INTO t1 VALUES('2', 'V',   'five');
    INSERT INTO t1 VALUES('3', 'VI',  'six');
    COMMIT;
    PRAGMA automatic_index=OFF;
  }
} {}
do_test minmax3-1.1.1 {
  # Linear scan.
  count { SELECT max(y) FROM t1 WHERE x = '2'; }
} {V 5}
do_test minmax3-1.1.2 {

      insert into b values ('01',1);
      insert into b values ('01',2);
      insert into b values ('+1',3);
      insert into b values ('+1',4);
      
      select a.*, x.*
        from a, (select key,sum(period) from b group by key) as x
        where a.key=x.key;
    }
  } {01 data01 01 3 +1 data+1 +1 7}

  # This test case tests the same property as misc4-4.1, but it is
  # a bit smaller which makes it easier to work with while debugging.
  do_test misc4-4.2 {
    execsql {

      insert into b values ('01',1);
      insert into b values ('01',2);
      insert into b values ('+1',3);
      insert into b values ('+1',4);
      
      select a.*, x.*
        from a, (select key,sum(period) from b group by key) as x
        where a.key=x.key order by 1 desc;
    }
  } {01 data01 01 3 +1 data+1 +1 7}

  # This test case tests the same property as misc4-4.1, but it is
  # a bit smaller which makes it easier to work with while debugging.
  do_test misc4-4.2 {
    execsql {

} ;# ifcapable view

# Ticket #1634
#
do_test select6-9.1 {
  execsql {
    SELECT a.x, b.x FROM t1 AS a, (SELECT x FROM t1 LIMIT 2) AS b

  }
} {1 1 1 2 2 1 2 2 3 1 3 2 4 1 4 2}
do_test select6-9.2 {
  execsql {
    SELECT x FROM (SELECT x FROM t1 LIMIT 2);
  }
} {1 2}

} ;# ifcapable view

# Ticket #1634
#
do_test select6-9.1 {
  execsql {
    SELECT a.x, b.x FROM t1 AS a, (SELECT x FROM t1 LIMIT 2) AS b
     ORDER BY 1, 2
  }
} {1 1 1 2 2 1 2 2 3 1 3 2 4 1 4 2}
do_test select6-9.2 {
  execsql {
    SELECT x FROM (SELECT x FROM t1 LIMIT 2);
  }
} {1 2}

  } else {
    puts " Ok"
  }
  flush stdout
}
    
proc do_execsql_test {testname sql result} {


  uplevel do_test $testname [list "execsql {$sql}"] [list $result]
}
proc do_catchsql_test {testname sql result} {
  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 [string trim $res]]
}


# Run an SQL script.  
# Return the number of microseconds per statement.
#
proc speed_trial {name numstmt units sql} {

  } else {
    puts " Ok"
  }
  flush stdout
}
    
proc do_execsql_test {testname sql result} {
  set r {}
  foreach x $result {lappend r $x}
  uplevel do_test $testname [list "execsql {$sql}"] [list $r]
}
proc do_catchsql_test {testname sql result} {
  uplevel do_test $testname [list "catchsql {$sql}"] [list $result]
}
proc do_eqp_test {name sql res} {
  set r {}
  foreach x $res {lappend r $x}
  uplevel do_execsql_test $name [list "EXPLAIN QUERY PLAN $sql"] [list $r]

}


# Run an SQL script.  
# Return the number of microseconds per statement.
#
proc speed_trial {name numstmt units sql} {

    CREATE INDEX i1 ON ""("" COLLATE nocase);
  }
} {}
do_test tkt-78e04-1.4 {
  execsql {
    EXPLAIN QUERY PLAN SELECT * FROM "" WHERE "" LIKE 'abc%';
  }
} {0 0 {TABLE }}
do_test tkt-78e04-1.5 {
  execsql {
    DROP TABLE "";
    SELECT name FROM sqlite_master;
  }
} {t2}

do_test tkt-78e04-2.1 {
  execsql {
    CREATE INDEX "" ON t2(x);
    EXPLAIN QUERY PLAN SELECT * FROM t2 WHERE x=5;
  }
} {0 0 {TABLE t2 WITH INDEX }}
do_test tkt-78e04-2.2 {
  execsql {
    DROP INDEX "";
    EXPLAIN QUERY PLAN SELECT * FROM t2 WHERE x=2;
  }
} {0 0 {TABLE t2}}

finish_test

    CREATE INDEX i1 ON ""("" COLLATE nocase);
  }
} {}
do_test tkt-78e04-1.4 {
  execsql {
    EXPLAIN QUERY PLAN SELECT * FROM "" WHERE "" LIKE 'abc%';
  }
} {0 0 0 {SCAN TABLE  (~500000 rows)}}
do_test tkt-78e04-1.5 {
  execsql {
    DROP TABLE "";
    SELECT name FROM sqlite_master;
  }
} {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)}}

finish_test

# 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 {TABLE listhash WITH INDEX ididx}}
  do_test tkt3442-1.3 {
    EQP { SELECT node FROM listhash WHERE id="5000" LIMIT 1; }
  } {0 0 {TABLE listhash WITH INDEX ididx}}
}


# 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 {TABLE listhash WITH INDEX ididx}}
}
do_test tkt3442-1.5 {
  catchsql {
    SELECT node FROM listhash WHERE id=[5000] LIMIT 1;
  }
} {1 {no such column: 5000}}

finish_test

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

finish_test

  db eval {
     CREATE TABLE t1(x INTEGER, y INTEGER, z TEXT);
     CREATE INDEX t1i1 ON t1(y,z);
     INSERT INTO t1 VALUES(1,2,'three');
     CREATE TABLE t2(a INTEGER, b TEXT);
     INSERT INTO t2 VALUES(2, 'two');
     ANALYZE;
     SELECT * FROM sqlite_stat1;
  }
} {t1 t1i1 {1 1 1}}

# Modify statistics in order to make the optimizer then that:
#
#   (1)  Table T1 has about 250K entries
#   (2)  There are only about 5 distinct values of T1.
#
# Then run a query with "t1.y IN (SELECT ..)" in the WHERE clause.

  db eval {
     CREATE TABLE t1(x INTEGER, y INTEGER, z TEXT);
     CREATE INDEX t1i1 ON t1(y,z);
     INSERT INTO t1 VALUES(1,2,'three');
     CREATE TABLE t2(a INTEGER, b TEXT);
     INSERT INTO t2 VALUES(2, 'two');
     ANALYZE;
     SELECT * FROM sqlite_stat1 ORDER BY 1, 2;
  }
} {t1 t1i1 {1 1 1} t2 {} 1}

# Modify statistics in order to make the optimizer then that:
#
#   (1)  Table T1 has about 250K entries
#   (2)  There are only about 5 distinct values of T1.
#
# Then run a query with "t1.y IN (SELECT ..)" in the WHERE clause.

    SELECT a FROM t2 WHERE b=2 AND c IS NULL ORDER BY b, a;
  }
} {5 9 sort}
do_test tkt3824-2.3 {
  lsort [execsql_status {
    SELECT a FROM t2 WHERE b=2 AND c IS NULL ORDER BY b;
  }]
} {5 9 sort}

do_test tkt3824-3.1 {
  db eval {
    CREATE TABLE t3(x,y);
    INSERT INTO t3 SELECT a, b FROM t1;
    INSERT INTO t3 VALUES(234,567);
    CREATE UNIQUE INDEX t3y ON t3(y);

    SELECT a FROM t2 WHERE b=2 AND c IS NULL ORDER BY b, a;
  }
} {5 9 sort}
do_test tkt3824-2.3 {
  lsort [execsql_status {
    SELECT a FROM t2 WHERE b=2 AND c IS NULL ORDER BY b;
  }]
} {5 9 nosort}

do_test tkt3824-3.1 {
  db eval {
    CREATE TABLE t3(x,y);
    INSERT INTO t3 SELECT a, b FROM t1;
    INSERT INTO t3 VALUES(234,567);
    CREATE UNIQUE INDEX t3y ON t3(y);

    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 {TABLE t302} 1 1 {TABLE t301 USING PRIMARY KEY}}
do_test where3-3.1 {
  execsql {
    explain query plan
    SELECT * FROM t301, t302 WHERE t302.x=5 AND t301.a=t302.y;
  }
} {0 1 {TABLE t302} 1 0 {TABLE t301 USING PRIMARY KEY}}

# 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_test where3-4.0 {
  execsql {
    CREATE TABLE t400(a INTEGER PRIMARY KEY, b, c);
    CREATE TABLE t401(p INTEGER PRIMARY KEY, q, r);
    CREATE TABLE t402(x INTEGER PRIMARY KEY, y, z);
    EXPLAIN QUERY PLAN
    SELECT * FROM t400, t401, t402 WHERE t402.z GLOB 'abc*';
  }
} {0 2 {TABLE t402} 1 0 {TABLE t400} 2 1 {TABLE t401}}
do_test where3-4.1 {
  execsql {
    EXPLAIN QUERY PLAN
    SELECT * FROM t400, t401, t402 WHERE t401.r GLOB 'abc*';
  }
} {0 1 {TABLE t401} 1 0 {TABLE t400} 2 2 {TABLE t402}}
do_test where3-4.2 {
  execsql {
    EXPLAIN QUERY PLAN
    SELECT * FROM t400, t401, t402 WHERE t400.c GLOB 'abc*';
  }
} {0 0 {TABLE t400} 1 1 {TABLE t401} 2 2 {TABLE t402}}

finish_test

    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 (~1 rows)} 0 1 1 {SEARCH TABLE t301 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}}
do_test where3-3.1 {
  execsql {
    explain query plan
    SELECT * FROM t301, t302 WHERE t302.x=5 AND t301.a=t302.y;
  }
} {0 0 1 {SCAN TABLE t302 (~1 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_test where3-4.0 {
  execsql {
    CREATE TABLE t400(a INTEGER PRIMARY KEY, b, c);
    CREATE TABLE t401(p INTEGER PRIMARY KEY, q, r);
    CREATE TABLE t402(x INTEGER PRIMARY KEY, y, z);
    EXPLAIN QUERY PLAN
    SELECT * FROM t400, t401, t402 WHERE t402.z GLOB 'abc*';
  }
} {0 0 2 {SCAN TABLE t402 (~500000 rows)} 0 1 0 {SCAN TABLE t400 (~1000000 rows)} 0 2 1 {SCAN TABLE t401 (~1000000 rows)}}
do_test where3-4.1 {
  execsql {
    EXPLAIN QUERY PLAN
    SELECT * FROM t400, t401, t402 WHERE t401.r GLOB 'abc*';
  }
} {0 0 1 {SCAN TABLE t401 (~500000 rows)} 0 1 0 {SCAN TABLE t400 (~1000000 rows)} 0 2 2 {SCAN TABLE t402 (~1000000 rows)}}
do_test where3-4.2 {
  execsql {
    EXPLAIN QUERY PLAN
    SELECT * FROM t400, t401, t402 WHERE t400.c GLOB 'abc*';
  }
} {0 0 0 {SCAN TABLE t400 (~500000 rows)} 0 1 1 {SCAN TABLE t401 (~1000000 rows)} 0 2 2 {SCAN TABLE t402 (~1000000 rows)}}

finish_test

     WHERE t1.a=t3.y OR t1.b=t3.y*11 OR (t1.c=27027 AND round(t1.d)==80)
    ORDER BY 1, 2, 3
  }
} {1 80 2 1 80 28 1 80 54 1 80 80 2 80 2 2 80 28 2 80 54 2 80 80 scan 1 sort 1}


ifcapable explain {
  do_test where9-3.1 {
    set r [db eval {
      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)
    }]
    set a [expr {[lsearch $r {TABLE t2 VIA MULTI-INDEX UNION}]>=0}]
    set b [expr {[lsearch $r {TABLE t2 WITH INDEX t2f}]>=0}]
    set c [expr {([lsearch $r {TABLE t2 WITH INDEX t2c}]>=0)+
                  [lsearch $r {TABLE t2 WITH INDEX t2d}]>=0}]
    concat $a $b $c
  } {1 1 1}
  do_test where9-3.2 {
    set r [db eval {
      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
    }]
    set a [expr {[lsearch $r {TABLE t2 VIA MULTI-INDEX UNION}]>=0}]
    set b [expr {[lsearch $r {TABLE t2 WITH INDEX t2f}]>=0}]
    set c [expr {([lsearch $r {TABLE t2 WITH INDEX t2c}]>=0)+
                  [lsearch $r {TABLE t2 WITH INDEX t2d}]>=0}]
    concat $a $b $c
  } {1 1 1}
} 

# Make sure that INDEXED BY and multi-index OR clauses play well with
# one another.
#
do_test where9-4.1 {
  count_steps {
................................................................................
  }
} {1 {cannot use index: t1d}}

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_test where9-5.1 {
    set r [db eval {
      EXPLAIN QUERY PLAN
      SELECT a FROM t1
       WHERE b>1000
         AND (c=31031 OR d IS NULL)
    }]
    set a [expr {[lsearch $r {TABLE t1 VIA MULTI-INDEX UNION}]>=0}]
    set b [expr {[lsearch $r {TABLE t1 WITH INDEX t1b}]>=0}]
    concat $a $b
  } {1 0}




  # In contrast, b=1000 is preferred over any OR-clause.
  #
  do_test where9-5.2 {
    set r [db eval {
      EXPLAIN QUERY PLAN
      SELECT a FROM t1
       WHERE b=1000
         AND (c=31031 OR d IS NULL)
    }]
    set a [expr {[lsearch $r {TABLE t1 VIA MULTI-INDEX UNION}]>=0}]
    set b [expr {[lsearch $r {TABLE t1 WITH INDEX t1b}]>=0}]
    concat $a $b
  } {0 1}



  # Likewise, inequalities in an AND are preferred over inequalities in
  # an OR.
  #
  do_test where9-5.3 {
    set r [db eval {
      EXPLAIN QUERY PLAN
      SELECT a FROM t1
       WHERE b>1000
         AND (c>=31031 OR d IS NULL)
    }]
    set a [expr {[lsearch $r {TABLE t1 VIA MULTI-INDEX UNION}]>=0}]
    set b [expr {[lsearch $r {TABLE t1 WITH INDEX t1b}]>=0}]
    concat $a $b
  } {0 1}


}

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

do_test where9-6.2.1 {
  db eval {SELECT count(*) FROM t1 UNION ALL SELECT a FROM t1 WHERE a>=85}

     WHERE t1.a=t3.y OR t1.b=t3.y*11 OR (t1.c=27027 AND round(t1.d)==80)
    ORDER BY 1, 2, 3
  }
} {1 80 2 1 80 28 1 80 54 1 80 80 2 80 2 2 80 28 2 80 54 2 80 80 scan 1 sort 1}


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 {
  count_steps {
................................................................................
  }
} {1 {cannot use index: t1d}}

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>?) (~125000 rows)}
  }
}

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

do_test where9-6.2.1 {
  db eval {SELECT count(*) FROM t1 UNION ALL SELECT a FROM t1 WHERE a>=85}