} {David Jack Patrick Quiana Xavier}
do_execsql_test skipscan2-1.7 {
  SELECT name FROM people WHERE role='teacher' AND height>=180
  UNION ALL
  SELECT name FROM people WHERE role='student' AND height>=180
  ORDER BY 1;
} {David Jack Patrick Quiana Xavier}














































# Repeat using a WITHOUT ROWID table.
#
do_execsql_test skipscan2-2.1 {
  CREATE TABLE peoplew(
    name TEXT PRIMARY KEY,
    role TEXT NOT NULL,
    height INT NOT NULL, -- in cm
    CHECK( role IN ('student','teacher') )
  ) WITHOUT ROWID;
  CREATE INDEX peoplew_idx1 ON peoplew(role, height);
  INSERT INTO peoplew(name,role,height)
     SELECT name, role, height FROM  people;
  DROP TABLE people;
  SELECT name FROM peoplew WHERE height>=180 ORDER BY +name;
} {David Jack Patrick Quiana Xavier}
do_execsql_test skipscan2-2.2 {
  SELECT name FROM peoplew
   WHERE role IN (SELECT DISTINCT role FROM peoplew)
     AND height>=180 ORDER BY +name;
} {David Jack Patrick Quiana Xavier}
................................................................................
  ORDER BY 1;
} {David Jack Patrick Quiana Xavier}

# Now do an ANALYZE.  A skip-scan can be used after ANALYZE.
#
do_execsql_test skipscan2-2.4 {
  ANALYZE;
  -- We do not have enough people above to actually force the use
  -- of a skip-scan.  So make a manual adjustment to the stat1 table
  -- to make it seem like there are many more.
  UPDATE sqlite_stat1 SET stat='10000 5000 20' WHERE idx='peoplew_idx1';
  ANALYZE sqlite_master;
}
db cache flush
do_execsql_test skipscan2-2.5 {
  SELECT name FROM peoplew WHERE height>=180 ORDER BY +name;
} {David Jack Patrick Quiana Xavier}
do_execsql_test skipscan2-2.5eqp {
  EXPLAIN QUERY PLAN
  SELECT name FROM peoplew WHERE height>=180 ORDER BY +name;
} {/*INDEX peoplew_idx1 */}



finish_test

} {David Jack Patrick Quiana Xavier}
do_execsql_test skipscan2-1.7 {
  SELECT name FROM people WHERE role='teacher' AND height>=180
  UNION ALL
  SELECT name FROM people WHERE role='student' AND height>=180
  ORDER BY 1;
} {David Jack Patrick Quiana Xavier}

# Add 8 more people, bringing the total to 34.  Then the number of
# duplicates in the left-column of the index will be 17 and 
# skip-scan should not be used after an (unfudged) ANALYZE.
#
do_execsql_test skipscan2-1.8 {
  INSERT INTO people VALUES('Angie','student',166);
  INSERT INTO people VALUES('Brad','student',176);
  INSERT INTO people VALUES('Claire','student',168);
  INSERT INTO people VALUES('Donald','student',162);
  INSERT INTO people VALUES('Elaine','student',177);
  INSERT INTO people VALUES('Frazier','student',159);
  INSERT INTO people VALUES('Grace','student',179);
  INSERT INTO people VALUES('Horace','student',166);
  ANALYZE;
  SELECT stat FROM sqlite_stat1 WHERE idx='people_idx1';
} {{34 17 2}}
db cache flush
do_execsql_test skipscan2-1.9 {
  SELECT name FROM people WHERE height>=180 ORDER BY +name;
} {David Jack Patrick Quiana Xavier}
do_execsql_test skipscan2-1.9eqp {
  EXPLAIN QUERY PLAN
  SELECT name FROM people WHERE height>=180 ORDER BY +name;
} {~/*INDEX people_idx1 */}

# Add 2 more people, bringing the total to 36.  Then the number of
# duplicates in the left-column of the index will be 18 and 
# skip-scan will be used after an (unfudged) ANALYZE.
#
do_execsql_test skipscan2-1.10 {
  INSERT INTO people VALUES('Ingrad','student',155);
  INSERT INTO people VALUES('Jacob','student',179);
  ANALYZE;
  SELECT stat FROM sqlite_stat1 WHERE idx='people_idx1';
} {{36 18 2}}
db cache flush
do_execsql_test skipscan2-1.11 {
  SELECT name FROM people WHERE height>=180 ORDER BY +name;
} {David Jack Patrick Quiana Xavier}
do_execsql_test skipscan2-1.11eqp {
  EXPLAIN QUERY PLAN
  SELECT name FROM people WHERE height>=180 ORDER BY +name;
} {/*INDEX people_idx1 */}


# Repeat using a WITHOUT ROWID table.
#
do_execsql_test skipscan2-2.1 {
  CREATE TABLE peoplew(
    name TEXT PRIMARY KEY,
    role TEXT NOT NULL,
    height INT NOT NULL, -- in cm
    CHECK( role IN ('student','teacher') )
  ) WITHOUT ROWID;
  CREATE INDEX peoplew_idx1 ON peoplew(role, height);
  INSERT INTO peoplew(name,role,height)
     SELECT name, role, height FROM  people;
  ALTER TABLE people RENAME TO old_people;
  SELECT name FROM peoplew WHERE height>=180 ORDER BY +name;
} {David Jack Patrick Quiana Xavier}
do_execsql_test skipscan2-2.2 {
  SELECT name FROM peoplew
   WHERE role IN (SELECT DISTINCT role FROM peoplew)
     AND height>=180 ORDER BY +name;
} {David Jack Patrick Quiana Xavier}
................................................................................
  ORDER BY 1;
} {David Jack Patrick Quiana Xavier}

# Now do an ANALYZE.  A skip-scan can be used after ANALYZE.
#
do_execsql_test skipscan2-2.4 {
  ANALYZE;





}
db cache flush
do_execsql_test skipscan2-2.5 {
  SELECT name FROM peoplew WHERE height>=180 ORDER BY +name;
} {David Jack Patrick Quiana Xavier}
do_execsql_test skipscan2-2.5eqp {
  EXPLAIN QUERY PLAN
  SELECT name FROM peoplew WHERE height>=180 ORDER BY +name;
} {/*INDEX peoplew_idx1 */}



finish_test