#***********************************************************************
#
# $Id: cache.test,v 1.4 2007/08/22 02:56:44 drh Exp $

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

ifcapable {!pager_pragmas} {
  finish_test
  return
}
sqlite3_soft_heap_limit 0

proc pager_cache_size {db} {
  set bt [btree_from_db $db]

#***********************************************************************
#
# $Id: cache.test,v 1.4 2007/08/22 02:56:44 drh Exp $

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

ifcapable !pager_pragmas||!compound {
  finish_test
  return
}
sqlite3_soft_heap_limit 0

proc pager_cache_size {db} {
  set bt [btree_from_db $db]

#***********************************************************************
#
# Tests for the sqlite3_db_status() function
#

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






# Memory statistics must be enabled for this test.
db close
sqlite3_shutdown
sqlite3_config_memstatus 1
sqlite3_initialize
sqlite3 db test.db

#***********************************************************************
#
# Tests for the sqlite3_db_status() function
#

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

ifcapable !compound {
  finish_test
  return
}

# Memory statistics must be enabled for this test.
db close
sqlite3_shutdown
sqlite3_config_memstatus 1
sqlite3_initialize
sqlite3 db test.db

#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script is the DISTINCT modifier.
#

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






set testprefix distinct


proc is_distinct_noop {sql} {
  set sql1 $sql
  set sql2 [string map {DISTINCT ""} $sql]

#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script is the DISTINCT modifier.
#

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

ifcapable !compound {
  finish_test
  return
}

set testprefix distinct


proc is_distinct_noop {sql} {
  set sql1 $sql
  set sql2 [string map {DISTINCT ""} $sql]

#***********************************************************************
#
# This file implements tests to verify that the "testable statements" in 
# the lang_delete.html document are correct.
#
set testdir [file dirname $argv0]
source $testdir/tester.tcl






proc do_delete_tests {args} {
  uplevel do_select_tests $args
}

do_execsql_test e_delete-0.0 {
  CREATE TABLE t1(a, b);

#***********************************************************************
#
# This file implements tests to verify that the "testable statements" in 
# the lang_delete.html document are correct.
#
set testdir [file dirname $argv0]
source $testdir/tester.tcl

ifcapable !compound {
  finish_test
  return
}

proc do_delete_tests {args} {
  uplevel do_select_tests $args
}

do_execsql_test e_delete-0.0 {
  CREATE TABLE t1(a, b);

# the lang_expr.html document are correct.
#

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






proc do_expr_test {tn expr type value} {
  uplevel do_execsql_test $tn [list "SELECT typeof($expr), $expr"] [
    list [list $type $value]
  ]
}

# the lang_expr.html document are correct.
#

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

ifcapable !compound {
  finish_test
  return
}

proc do_expr_test {tn expr type value} {
  uplevel do_execsql_test $tn [list "SELECT typeof($expr), $expr"] [
    list [list $type $value]
  ]
}

    INSERT INTO parent VALUES(1);
    INSERT INTO child VALUES(1);
  }
} {}
do_test e_fkey-51.2 {
  execsql {
    UPDATE parent SET x = 22;
    SELECT * FROM parent UNION ALL SELECT 'xxx' UNION ALL SELECT a FROM child;
  }
} {22 21 23 xxx 22}
do_test e_fkey-51.3 {
  execsql {
    DELETE FROM child;
    DELETE FROM parent;
    INSERT INTO parent VALUES(-1);
    INSERT INTO child VALUES(-1);
    UPDATE parent SET x = 22;
    SELECT * FROM parent UNION ALL SELECT 'xxx' UNION ALL SELECT a FROM child;
  }
} {22 23 21 xxx 23}


#-------------------------------------------------------------------------
# Verify that ON UPDATE actions only actually take place if the parent key
# is set to a new value that is distinct from the old value. The default

    INSERT INTO parent VALUES(1);
    INSERT INTO child VALUES(1);
  }
} {}
do_test e_fkey-51.2 {
  execsql {
    UPDATE parent SET x = 22;
    SELECT * FROM parent ; SELECT 'xxx' ; SELECT a FROM child;
  }
} {22 21 23 xxx 22}
do_test e_fkey-51.3 {
  execsql {
    DELETE FROM child;
    DELETE FROM parent;
    INSERT INTO parent VALUES(-1);
    INSERT INTO child VALUES(-1);
    UPDATE parent SET x = 22;
    SELECT * FROM parent ; SELECT 'xxx' ; SELECT a FROM child;
  }
} {22 23 21 xxx 23}


#-------------------------------------------------------------------------
# Verify that ON UPDATE actions only actually take place if the parent key
# is set to a new value that is distinct from the old value. The default

# statements" in the lang_insert.html document are correct.
#
# Also, it contains tests to verify the statements in (the very short)
# lang_replace.html.
#
set testdir [file dirname $argv0]
source $testdir/tester.tcl






# Organization of tests:
#
#   e_insert-0.*: Test the syntax diagram.
#
#   e_insert-1.*: Test statements of the form "INSERT ... VALUES(...)".
#   

# statements" in the lang_insert.html document are correct.
#
# Also, it contains tests to verify the statements in (the very short)
# lang_replace.html.
#
set testdir [file dirname $argv0]
source $testdir/tester.tcl

ifcapable !compound {
  finish_test
  return
}

# Organization of tests:
#
#   e_insert-0.*: Test the syntax diagram.
#
#   e_insert-1.*: Test statements of the form "INSERT ... VALUES(...)".
#   

#
# This file implements tests to verify that the "testable statements" in 
# the lang_select.html document are correct.
#

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






do_execsql_test e_select-1.0 {
  CREATE TABLE t1(a, b);
  INSERT INTO t1 VALUES('a', 'one');
  INSERT INTO t1 VALUES('b', 'two');
  INSERT INTO t1 VALUES('c', 'three');

#
# This file implements tests to verify that the "testable statements" in 
# the lang_select.html document are correct.
#

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

ifcapable !compound {
  finish_test
  return
}

do_execsql_test e_select-1.0 {
  CREATE TABLE t1(a, b);
  INSERT INTO t1 VALUES('a', 'one');
  INSERT INTO t1 VALUES('b', 'two');
  INSERT INTO t1 VALUES('c', 'three');

# attached is in the TEMP database, then the unqualified name of the
# table being updated is resolved in the same way as it is for a
# top-level statement (by searching first the TEMP database, then the
# main database, then any other databases in the order they were
# attached).
#
do_execsql_test e_update-2.3.0 {
  SELECT 'main', tbl_name FROM main.sqlite_master WHERE type = 'table'
    UNION ALL
  SELECT 'temp', tbl_name FROM sqlite_temp_master WHERE type = 'table'
    UNION ALL
  SELECT 'aux', tbl_name FROM aux.sqlite_master WHERE type = 'table'
} [list {*}{
    main t1
    main t2
    main t3
    main t6
    temp t4
    temp t6

# attached is in the TEMP database, then the unqualified name of the
# table being updated is resolved in the same way as it is for a
# top-level statement (by searching first the TEMP database, then the
# main database, then any other databases in the order they were
# attached).
#
do_execsql_test e_update-2.3.0 {
  SELECT 'main', tbl_name FROM main.sqlite_master WHERE type = 'table';

  SELECT 'temp', tbl_name FROM sqlite_temp_master WHERE type = 'table';

  SELECT 'aux', tbl_name FROM aux.sqlite_master WHERE type = 'table';
} [list {*}{
    main t1
    main t2
    main t3
    main t6
    temp t4
    temp t6

#
#   e_vacuum-1.2.1 - Perform many INSERT, UPDATE and DELETE ops on table t1.
#   e_vacuum-1.2.2 - Verify that t1 and its indexes are now quite fragmented.
#   e_vacuum-1.2.3 - Run VACUUM.
#   e_vacuum-1.2.4 - Verify that t1 and its indexes are now much 
#                    less fragmented.
#
ifcapable vtab {
  create_db 
  register_dbstat_vtab db
  do_execsql_test e_vacuum-1.2.1 {
    DELETE FROM t1 WHERE a%2;
    INSERT INTO t1 SELECT b, a FROM t2 WHERE a%2;
    UPDATE t1 SET b=randomblob(600) WHERE (a%2)==0;
  } {}

#
#   e_vacuum-1.2.1 - Perform many INSERT, UPDATE and DELETE ops on table t1.
#   e_vacuum-1.2.2 - Verify that t1 and its indexes are now quite fragmented.
#   e_vacuum-1.2.3 - Run VACUUM.
#   e_vacuum-1.2.4 - Verify that t1 and its indexes are now much 
#                    less fragmented.
#
ifcapable vtab&&compound {
  create_db 
  register_dbstat_vtab db
  do_execsql_test e_vacuum-1.2.1 {
    DELETE FROM t1 WHERE a%2;
    INSERT INTO t1 SELECT b, a FROM t2 WHERE a%2;
    UPDATE t1 SET b=randomblob(600) WHERE (a%2)==0;
  } {}

#    May you share freely, never taking more than you give.
#
#***********************************************************************
#

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






set testprefix eqp

#-------------------------------------------------------------------------
#
# eqp-1.*:        Assorted tests.
# eqp-2.*:        Tests for single select statements.

#    May you share freely, never taking more than you give.
#
#***********************************************************************
#

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

ifcapable !compound {
  finish_test
  return
}

set testprefix eqp

#-------------------------------------------------------------------------
#
# eqp-1.*:        Assorted tests.
# eqp-2.*:        Tests for single select statements.

    SELECT * FROM t2 WHERE a IN (
      SELECT a, b FROM t3 INTERSECT SELECT a FROM t2
    );
  }
} {1 {SELECTs to the left and right of INTERSECT do not have the same number of result columns}}
}


do_test in-12.10 {
  catchsql {
    SELECT * FROM t2 WHERE a IN (
      SELECT a FROM t3 UNION ALL SELECT a, b FROM t2
    );
  }
} {1 {only a single result allowed for a SELECT that is part of an expression}}
................................................................................
do_test in-12.13 {
  catchsql {
    SELECT * FROM t2 WHERE a IN (
      SELECT a FROM t3 INTERSECT SELECT a, b FROM t2
    );
  }
} {1 {only a single result allowed for a SELECT that is part of an expression}}



#------------------------------------------------------------------------
# The following tests check that NULL is handled correctly when it 
# appears as part of a set of values on the right-hand side of an
# IN or NOT IN operator.
#

    SELECT * FROM t2 WHERE a IN (
      SELECT a, b FROM t3 INTERSECT SELECT a FROM t2
    );
  }
} {1 {SELECTs to the left and right of INTERSECT do not have the same number of result columns}}
}

ifcapable compound {
do_test in-12.10 {
  catchsql {
    SELECT * FROM t2 WHERE a IN (
      SELECT a FROM t3 UNION ALL SELECT a, b FROM t2
    );
  }
} {1 {only a single result allowed for a SELECT that is part of an expression}}
................................................................................
do_test in-12.13 {
  catchsql {
    SELECT * FROM t2 WHERE a IN (
      SELECT a FROM t3 INTERSECT SELECT a, b FROM t2
    );
  }
} {1 {only a single result allowed for a SELECT that is part of an expression}}
}; #ifcapable compound


#------------------------------------------------------------------------
# The following tests check that NULL is handled correctly when it 
# appears as part of a set of values on the right-hand side of an
# IN or NOT IN operator.
#

    INSERT INTO t6 SELECT nullif(y*2+10,14), y+100 FROM t6;
    SELECT x, y FROM t6;
  }
} {1 1 2 2 3 3 12 101 13 102 16 103}

# Multiple VALUES clauses
#

do_test insert-10.1 {
  execsql {
    CREATE TABLE t10(a,b,c);
    INSERT INTO t10 VALUES(1,2,3), (4,5,6), (7,8,9);
    SELECT * FROM t10;
  }
} {1 2 3 4 5 6 7 8 9}
do_test insert-10.2 {
  catchsql {
    INSERT INTO t10 VALUES(11,12,13), (14,15);
  }
} {1 {all VALUES must have the same number of terms}}


integrity_check insert-99.0

finish_test

    INSERT INTO t6 SELECT nullif(y*2+10,14), y+100 FROM t6;
    SELECT x, y FROM t6;
  }
} {1 1 2 2 3 3 12 101 13 102 16 103}

# Multiple VALUES clauses
#
ifcapable compound {
  do_test insert-10.1 {
    execsql {
      CREATE TABLE t10(a,b,c);
      INSERT INTO t10 VALUES(1,2,3), (4,5,6), (7,8,9);
      SELECT * FROM t10;
    }
  } {1 2 3 4 5 6 7 8 9}
  do_test insert-10.2 {
    catchsql {
      INSERT INTO t10 VALUES(11,12,13), (14,15);
    }
  } {1 {all VALUES must have the same number of terms}}
}

integrity_check insert-99.0

finish_test

} {1 91 92 3 93 5 93}
do_test join6-3.6 {
  execsql {
    SELECT * FROM t1 NATURAL JOIN t2 JOIN t3 USING(z);
  }
} {1 91 92 3 93 5 91 2 93 94 4 95 6 99}


do_test join6-4.1 {
  execsql {
    SELECT * FROM
       (SELECT 1 AS a, 91 AS x, 92 AS y UNION SELECT 2, 93, 94)
       NATURAL JOIN t2 NATURAL JOIN t3
  }
} {1 91 92 3 93 5}
do_test join6-4.2 {
  execsql {
    SELECT * FROM t1 NATURAL JOIN
       (SELECT 3 AS b, 92 AS y, 93 AS z UNION SELECT 4, 94, 95)
       NATURAL JOIN t3
  }
} {1 91 92 3 93 5}
do_test join6-4.3 {
  execsql {
    SELECT * FROM t1 NATURAL JOIN t2 NATURAL JOIN
       (SELECT 5 AS c, 91 AS x, 93 AS z UNION SELECT 6, 99, 95)
  }
} {1 91 92 3 93 5}












finish_test

} {1 91 92 3 93 5 93}
do_test join6-3.6 {
  execsql {
    SELECT * FROM t1 NATURAL JOIN t2 JOIN t3 USING(z);
  }
} {1 91 92 3 93 5 91 2 93 94 4 95 6 99}

ifcapable compound {
  do_test join6-4.1 {
    execsql {
      SELECT * FROM
         (SELECT 1 AS a, 91 AS x, 92 AS y UNION SELECT 2, 93, 94)
         NATURAL JOIN t2 NATURAL JOIN t3
    }
  } {1 91 92 3 93 5}
  do_test join6-4.2 {
    execsql {
      SELECT * FROM t1 NATURAL JOIN
         (SELECT 3 AS b, 92 AS y, 93 AS z UNION SELECT 4, 94, 95)
         NATURAL JOIN t3
    }
  } {1 91 92 3 93 5}
  do_test join6-4.3 {
    execsql {
      SELECT * FROM t1 NATURAL JOIN t2 NATURAL JOIN
         (SELECT 5 AS c, 91 AS x, 93 AS z UNION SELECT 6, 99, 95)
    }
  } {1 91 92 3 93 5}
}











finish_test

#
# Demonstration that the value returned for p is on the same row as 
# the maximum q.
#

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






do_test minmax4-1.1 {
  db eval {
    CREATE TABLE t1(p,q);
    SELECT p, max(q) FROM t1;
  }
} {{} {}}

#
# Demonstration that the value returned for p is on the same row as 
# the maximum q.
#

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

ifcapable !compound {
  finish_test
  return
}

do_test minmax4-1.1 {
  db eval {
    CREATE TABLE t1(p,q);
    SELECT p, max(q) FROM t1;
  }
} {{} {}}

# expression evaluation logic of TCL.
#
# An early version of this script is how bug #3541 was detected.
#
# $Id: randexpr1.test,v 1.1 2008/12/15 16:33:30 drh Exp $
set testdir [file dirname $argv0]
source $testdir/tester.tcl






# Create test data
#
do_test randexpr1-1.1 {
  db eval {
    CREATE TABLE t1(a,b,c,d,e,f);
    INSERT INTO t1 VALUES(100,200,300,400,500,600);

# expression evaluation logic of TCL.
#
# An early version of this script is how bug #3541 was detected.
#
# $Id: randexpr1.test,v 1.1 2008/12/15 16:33:30 drh Exp $
set testdir [file dirname $argv0]
source $testdir/tester.tcl

ifcapable !compound {
  finish_test
  return
}

# Create test data
#
do_test randexpr1-1.1 {
  db eval {
    CREATE TABLE t1(a,b,c,d,e,f);
    INSERT INTO t1 VALUES(100,200,300,400,500,600);

    execsql {
      ATTACH 'test2.db' AS aux1;
      ATTACH 'test3.db' AS aux2;
      DROP TABLE t1;
      CREATE TABLE main.t1(x, y);
      CREATE TABLE aux1.t2(x, y);
      CREATE TABLE aux2.t3(x, y);
      SELECT name FROM sqlite_master 
        UNION ALL
      SELECT name FROM aux1.sqlite_master 
        UNION ALL
      SELECT name FROM aux2.sqlite_master;
    }
  } {t1 t2 t3}
  do_test savepoint-10.2.2 {
    execsql { PRAGMA lock_status }
  } [list main unlocked temp $templockstate aux1 unlocked aux2 unlocked]
  
................................................................................
    execsql { ROLLBACK TO two }
    execsql { SELECT * FROM t2 }
  } {}
  do_test savepoint-10.2.8 {
    execsql { PRAGMA lock_status }
  } [list main reserved temp $templockstate aux1 reserved aux2 reserved]
  do_test savepoint-10.2.9 {
    execsql { SELECT 'a', * FROM t1 UNION ALL SELECT 'b', * FROM t3 }
  } {a 1 2 b 3 4}
  do_test savepoint-10.2.9 {
    execsql {
      INSERT INTO t2 VALUES(5, 6);
      RELEASE one;
    }
    execsql { 

    execsql {
      ATTACH 'test2.db' AS aux1;
      ATTACH 'test3.db' AS aux2;
      DROP TABLE t1;
      CREATE TABLE main.t1(x, y);
      CREATE TABLE aux1.t2(x, y);
      CREATE TABLE aux2.t3(x, y);
      SELECT name FROM sqlite_master;

      SELECT name FROM aux1.sqlite_master;

      SELECT name FROM aux2.sqlite_master;
    }
  } {t1 t2 t3}
  do_test savepoint-10.2.2 {
    execsql { PRAGMA lock_status }
  } [list main unlocked temp $templockstate aux1 unlocked aux2 unlocked]
  
................................................................................
    execsql { ROLLBACK TO two }
    execsql { SELECT * FROM t2 }
  } {}
  do_test savepoint-10.2.8 {
    execsql { PRAGMA lock_status }
  } [list main reserved temp $templockstate aux1 reserved aux2 reserved]
  do_test savepoint-10.2.9 {
    execsql { SELECT 'a', * FROM t1 ; SELECT 'b', * FROM t3 }
  } {a 1 2 b 3 4}
  do_test savepoint-10.2.9 {
    execsql {
      INSERT INTO t2 VALUES(5, 6);
      RELEASE one;
    }
    execsql { 

      FROM t1
     ORDER BY x DESC
  }
} {CCC AAA AAA}

# The following query used to leak memory.  Verify that has been fixed.
#
ifcapable trigger {
  do_test selectC-2.1 {
    catchsql {
      CREATE TABLE t21a(a,b);
      INSERT INTO t21a VALUES(1,2);
      CREATE TABLE t21b(n);
      CREATE TRIGGER r21 AFTER INSERT ON t21b BEGIN
        SELECT a FROM t21a WHERE a>new.x UNION ALL

      FROM t1
     ORDER BY x DESC
  }
} {CCC AAA AAA}

# The following query used to leak memory.  Verify that has been fixed.
#
ifcapable trigger&&compound {
  do_test selectC-2.1 {
    catchsql {
      CREATE TABLE t21a(a,b);
      INSERT INTO t21a VALUES(1,2);
      CREATE TABLE t21b(n);
      CREATE TRIGGER r21 AFTER INSERT ON t21b BEGIN
        SELECT a FROM t21a WHERE a>new.x UNION ALL

# This file implements regression tests for SQLite library.  The
# focus of this file is testing the SELECT statement.
#

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

ifcapable !vtab {
  finish_test
  return
}


set ::asc 1
proc a_string {n} { string range [string repeat [incr ::asc]. $n] 1 $n }

# This file implements regression tests for SQLite library.  The
# focus of this file is testing the SELECT statement.
#

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

ifcapable !vtab||!compound {
  finish_test
  return
}


set ::asc 1
proc a_string {n} { string range [string repeat [incr ::asc]. $n] 1 $n }

#
# This file implements tests to verify that ticket [02a8e81d44] has been
# fixed.  
#

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






do_test tkt-02a838-1.1 {
  execsql {
    CREATE TABLE t1(a);
    INSERT INTO t1 VALUES(1);
    INSERT INTO t1 VALUES(2);
    INSERT INTO t1 VALUES(4);
    INSERT INTO t1 VALUES(5);
    SELECT * FROM (SELECT a FROM t1 LIMIT 1) UNION ALL SELECT 3;
  }
} {1 3}

finish_test

#
# This file implements tests to verify that ticket [02a8e81d44] has been
# fixed.  
#

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

ifcapable !compound {
  finish_test
  return
}

do_test tkt-02a838-1.1 {
  execsql {
    CREATE TABLE t1(a);
    INSERT INTO t1 VALUES(1);
    INSERT INTO t1 VALUES(2);
    INSERT INTO t1 VALUES(4);
    INSERT INTO t1 VALUES(5);
    SELECT * FROM (SELECT a FROM t1 LIMIT 1) UNION ALL SELECT 3;
  }
} {1 3}

finish_test

# This file implements regression tests for SQLite library. Specifically,
# it tests that ticket [38cb5df375078d3f9711482d2a1615d09f6b3f33] has
# been resolved.
#

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






do_test tkt-38cb5df375.0 {
  execsql {
    CREATE TABLE t1(a);
    INSERT INTO t1 VALUES(1);
    INSERT INTO t1 VALUES(2);
    INSERT INTO t1 SELECT a+2 FROM t1;

# This file implements regression tests for SQLite library. Specifically,
# it tests that ticket [38cb5df375078d3f9711482d2a1615d09f6b3f33] has
# been resolved.
#

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

ifcapable !compound {
  finish_test
  return
}

do_test tkt-38cb5df375.0 {
  execsql {
    CREATE TABLE t1(a);
    INSERT INTO t1 VALUES(1);
    INSERT INTO t1 VALUES(2);
    INSERT INTO t1 SELECT a+2 FROM t1;

# This file implements regression tests for SQLite library.
#
# This file implements tests to verify that ticket [3a77c9714e] has been
# fixed.  

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






set testprefix "tkt-3a77c9714e"

do_execsql_test 1.1 {
  CREATE TABLE t1(t1_id INTEGER PRIMARY KEY, t1_title TEXT);
  CREATE TABLE t2(t2_id INTEGER PRIMARY KEY, t2_title TEXT);
  CREATE TABLE t3(t3_id INTEGER PRIMARY KEY, t3_title TEXT);

# This file implements regression tests for SQLite library.
#
# This file implements tests to verify that ticket [3a77c9714e] has been
# fixed.  

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

ifcapable !compound {
  finish_test
  return
}

set testprefix "tkt-3a77c9714e"

do_execsql_test 1.1 {
  CREATE TABLE t1(t1_id INTEGER PRIMARY KEY, t1_title TEXT);
  CREATE TABLE t2(t2_id INTEGER PRIMARY KEY, t2_title TEXT);
  CREATE TABLE t3(t3_id INTEGER PRIMARY KEY, t3_title TEXT);

  INSERT INTO t3(t3_a) VALUES(2);
  INSERT INTO t3(t3_a) VALUES(3);
  SELECT * FROM t3;
} {1 I 2 II 3 III}

do_execsql_test 1.3 { DELETE FROM t3 }


do_execsql_test 1.4 {
  INSERT INTO t3(t3_a) SELECT 1 UNION SELECT 2 UNION SELECT 3;
  SELECT * FROM t3;
} {1 I 2 II 3 III}




#-------------------------------------------------------------------------
# The following test case - 2.* - is from the original bug report as 
# posted to the mailing list.
#

  INSERT INTO t3(t3_a) VALUES(2);
  INSERT INTO t3(t3_a) VALUES(3);
  SELECT * FROM t3;
} {1 I 2 II 3 III}

do_execsql_test 1.3 { DELETE FROM t3 }

ifcapable compound {
  do_execsql_test 1.4 {
    INSERT INTO t3(t3_a) SELECT 1 UNION SELECT 2 UNION SELECT 3;
    SELECT * FROM t3;
  } {1 I 2 II 3 III}
}



#-------------------------------------------------------------------------
# The following test case - 2.* - is from the original bug report as 
# posted to the mailing list.
#

do_execsql_test 303 {
  SELECT * FROM t1, t2 WHERE
    (a='A' AND d='E') OR
    (b='B' AND c IN (SELECT c FROM t1))
} {A B C D E}


do_execsql_test 304 {
  SELECT * FROM t1, t2 WHERE
    (a='A' AND d='E') OR
    (b='B' AND c IN (SELECT 'B' UNION SELECT 'C' UNION SELECT 'D'))
} {A B C D E}


do_execsql_test 305 {
  SELECT * FROM t1, t2 WHERE
    (b='B' AND c IN ('C', 'D', 'E')) OR
    (a='A' AND d='E')
} {A B C D E}

do_execsql_test 306 {
  SELECT * FROM t1, t2 WHERE
    (b='B' AND c IN (SELECT c FROM t1)) OR
    (a='A' AND d='E')
} {A B C D E}


do_execsql_test 307 {
  SELECT * FROM t1, t2 WHERE
    (b='B' AND c IN (SELECT 'B' UNION SELECT 'C' UNION SELECT 'D')) OR
    (a='A' AND d='E')
} {A B C D E}


finish_test

do_execsql_test 303 {
  SELECT * FROM t1, t2 WHERE
    (a='A' AND d='E') OR
    (b='B' AND c IN (SELECT c FROM t1))
} {A B C D E}

ifcapable compound {
  do_execsql_test 304 {
    SELECT * FROM t1, t2 WHERE
      (a='A' AND d='E') OR
      (b='B' AND c IN (SELECT 'B' UNION SELECT 'C' UNION SELECT 'D'))
  } {A B C D E}
}

do_execsql_test 305 {
  SELECT * FROM t1, t2 WHERE
    (b='B' AND c IN ('C', 'D', 'E')) OR
    (a='A' AND d='E')
} {A B C D E}

do_execsql_test 306 {
  SELECT * FROM t1, t2 WHERE
    (b='B' AND c IN (SELECT c FROM t1)) OR
    (a='A' AND d='E')
} {A B C D E}

ifcapable compound {
  do_execsql_test 307 {
    SELECT * FROM t1, t2 WHERE
      (b='B' AND c IN (SELECT 'B' UNION SELECT 'C' UNION SELECT 'D')) OR
      (a='A' AND d='E')
  } {A B C D E}
}

finish_test

#     The bug is that sqlite3ExpirePreparedStatements expires all statements.
#     Note that B was prepared after the schema change and hence is perfectly
#     valid and then is marked as expired while running.
#

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






unset -nocomplain ::STMT
proc runsql {} {
  db eval {CREATE TABLE IF NOT EXISTS t4(q)}
  sqlite3_step $::STMT
  set rc [sqlite3_column_int $::STMT 0]
  sqlite3_reset $::STMT

#     The bug is that sqlite3ExpirePreparedStatements expires all statements.
#     Note that B was prepared after the schema change and hence is perfectly
#     valid and then is marked as expired while running.
#

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

ifcapable !compound {
  finish_test
  return
}

unset -nocomplain ::STMT
proc runsql {} {
  db eval {CREATE TABLE IF NOT EXISTS t4(q)}
  sqlite3_step $::STMT
  set rc [sqlite3_column_int $::STMT 0]
  sqlite3_reset $::STMT

#
# This file implements tests to verify that ticket [d82e3f3721] has been
# fixed.  
#

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






do_test tkt-d82e3-1.1 {
  db eval {
    CREATE TABLE t1(a INTEGER PRIMARY KEY AUTOINCREMENT, b);
    INSERT INTO t1 VALUES(null,'abc');
    INSERT INTO t1 VALUES(null,'def');
    DELETE FROM t1;

#
# This file implements tests to verify that ticket [d82e3f3721] has been
# fixed.  
#

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

ifcapable !compound {
  finish_test
  return
}

do_test tkt-d82e3-1.1 {
  db eval {
    CREATE TABLE t1(a INTEGER PRIMARY KEY AUTOINCREMENT, b);
    INSERT INTO t1 VALUES(null,'abc');
    INSERT INTO t1 VALUES(null,'def');
    DELETE FROM t1;

#
# This file implements tests to verify that ticket [f777251dc7a] has been
# fixed.  
#

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






do_test tkt-f7772-1.1 {
  execsql {
     CREATE TEMP TABLE t1(x UNIQUE);
     INSERT INTO t1 VALUES(1);
     CREATE TABLE t2(x, y);
     INSERT INTO t2 VALUES(1, 2);

#
# This file implements tests to verify that ticket [f777251dc7a] has been
# fixed.  
#

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

ifcapable !compound {
  finish_test
  return
}

do_test tkt-f7772-1.1 {
  execsql {
     CREATE TEMP TABLE t1(x UNIQUE);
     INSERT INTO t1 VALUES(1);
     CREATE TABLE t2(x, y);
     INSERT INTO t2 VALUES(1, 2);

# This file is a verification that the bugs identified in ticket
# #3527 have been fixed.
#
# $Id: tkt3527.test,v 1.1 2008/12/08 13:42:36 drh Exp $

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






do_test tkt3527-1.1 {
  db eval {
    CREATE TABLE Element (
     Code INTEGER PRIMARY KEY,
     Name VARCHAR(60)
    );

# This file is a verification that the bugs identified in ticket
# #3527 have been fixed.
#
# $Id: tkt3527.test,v 1.1 2008/12/08 13:42:36 drh Exp $

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

ifcapable !compound {
  finish_test
  return
}

do_test tkt3527-1.1 {
  db eval {
    CREATE TABLE Element (
     Code INTEGER PRIMARY KEY,
     Name VARCHAR(60)
    );

# subquery contains an ORDER BY clause.
#
#
# $Id: tkt3773.test,v 1.1 2009/04/02 16:59:47 drh Exp $

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






do_test tkt3773-1.1 {
  db eval {
    CREATE TABLE t1(a,b);
    INSERT INTO t1 VALUES(2,1);
    INSERT INTO t1 VALUES(33,3);
    CREATE TABLE t2(x,y);

# subquery contains an ORDER BY clause.
#
#
# $Id: tkt3773.test,v 1.1 2009/04/02 16:59:47 drh Exp $

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

ifcapable !compound {
  finish_test
  return
}

do_test tkt3773-1.1 {
  db eval {
    CREATE TABLE t1(a,b);
    INSERT INTO t1 VALUES(2,1);
    INSERT INTO t1 VALUES(33,3);
    CREATE TABLE t2(x,y);

# trig-1.12: Ensure that INSTEAD OF triggers cannot be created on tables
# trig-1.13: Ensure that AFTER triggers cannot be created on views
# trig-1.14: Ensure that BEFORE triggers cannot be created on views
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
ifcapable {!trigger} {
  finish_test
  return
}

do_test trigger1-1.1.1 {
   catchsql {
     CREATE TRIGGER trig UPDATE ON no_such_table BEGIN

# trig-1.12: Ensure that INSTEAD OF triggers cannot be created on tables
# trig-1.13: Ensure that AFTER triggers cannot be created on views
# trig-1.14: Ensure that BEFORE triggers cannot be created on views
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
ifcapable !trigger||!compound {
  finish_test
  return
}

do_test trigger1-1.1.1 {
   catchsql {
     CREATE TRIGGER trig UPDATE ON no_such_table BEGIN

do_test vtabD-1.5 {
  execsql { SELECT * FROM tv1 WHERE (a > 0 AND a < 5) OR (b > 15 AND b < 65) }
} {1 1 2 4 3 9 4 16 5 25 6 36 7 49 8 64}

do_test vtabD-1.6 {
  execsql { SELECT * FROM tv1 WHERE a < 500 OR b = 810000 }
} [execsql {
  SELECT * FROM t1 WHERE a < 500
    UNION ALL
  SELECT * FROM t1 WHERE b = 810000 AND NOT (a < 500)
}]

do_test vtabD-1.7 {
  execsql { SELECT * FROM tv1 WHERE a < 90000 OR b = 8100000000 }
} [execsql {
  SELECT * FROM t1 WHERE a < 90000
    UNION ALL
  SELECT * FROM t1 WHERE b = 8100000000 AND NOT (a < 90000)
}]

if {[working_64bit_int]} {
do_test vtabD-1.8 {
  execsql { SELECT * FROM tv1 WHERE a = 90001 OR b = 810000 }
} {90001 8100180001 900 810000}
}

finish_test

do_test vtabD-1.5 {
  execsql { SELECT * FROM tv1 WHERE (a > 0 AND a < 5) OR (b > 15 AND b < 65) }
} {1 1 2 4 3 9 4 16 5 25 6 36 7 49 8 64}

do_test vtabD-1.6 {
  execsql { SELECT * FROM tv1 WHERE a < 500 OR b = 810000 }
} [execsql {
  SELECT * FROM t1 WHERE a < 500;

  SELECT * FROM t1 WHERE b = 810000 AND NOT (a < 500);
}]

do_test vtabD-1.7 {
  execsql { SELECT * FROM tv1 WHERE a < 90000 OR b = 8100000000 }
} [execsql {
  SELECT * FROM t1 WHERE a < 90000;

  SELECT * FROM t1 WHERE b = 8100000000 AND NOT (a < 90000);
}]

if {[working_64bit_int]} {
do_test vtabD-1.8 {
  execsql { SELECT * FROM tv1 WHERE a = 90001 OR b = 810000 }
} {90001 8100180001 900 810000}
}

finish_test

  execsql {
    CREATE VIRTUAL TABLE t2 USING echo(t0);
    CREATE VIRTUAL TABLE t3 USING echo(t0);
  }
  execsql { SELECT * FROM t3 } db2
} {1 2 3 4 5 6}


do_test vtab_shared-1.12.1 {
  db close
  execsql { 
    SELECT * FROM t1 UNION ALL
    SELECT * FROM t2 UNION ALL
    SELECT * FROM t3 
  } db2
} {1 2 3 4 5 6 1 2 3 4 5 6 1 2 3 4 5 6}
do_test vtab_shared-1.12.2 {
  sqlite3 db test.db
  register_echo_module [sqlite3_connection_pointer db]
  execsql { 
    SELECT * FROM t1 UNION ALL
    SELECT * FROM t2 UNION ALL
    SELECT * FROM t3 
  } db
} {1 2 3 4 5 6 1 2 3 4 5 6 1 2 3 4 5 6}


# Try a rename or two.
#
ifcapable altertable {
  do_test vtab_shared-1.13.1 {
    execsql { ALTER TABLE t1 RENAME TO t4 }
    execsql { SELECT * FROM t4 } db

  execsql {
    CREATE VIRTUAL TABLE t2 USING echo(t0);
    CREATE VIRTUAL TABLE t3 USING echo(t0);
  }
  execsql { SELECT * FROM t3 } db2
} {1 2 3 4 5 6}

ifcapable compound {
  do_test vtab_shared-1.12.1 {
    db close
    execsql { 
      SELECT * FROM t1 UNION ALL
      SELECT * FROM t2 UNION ALL
      SELECT * FROM t3 
    } db2
  } {1 2 3 4 5 6 1 2 3 4 5 6 1 2 3 4 5 6}
  do_test vtab_shared-1.12.2 {
    sqlite3 db test.db
    register_echo_module [sqlite3_connection_pointer db]
    execsql { 
      SELECT * FROM t1 UNION ALL
      SELECT * FROM t2 UNION ALL
      SELECT * FROM t3 
    } db
  } {1 2 3 4 5 6 1 2 3 4 5 6 1 2 3 4 5 6}
}

# Try a rename or two.
#
ifcapable altertable {
  do_test vtab_shared-1.13.1 {
    execsql { ALTER TABLE t1 RENAME TO t4 }
    execsql { SELECT * FROM t4 } db

# This file implements regression tests for SQLite library.  The
# focus of this file is testing the multi-index OR clause optimizer.
#

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

ifcapable !or_opt {
  finish_test
  return
}

# Evaluate SQL.  Return the result set followed by the
# and the number of full-scan steps.
#

# This file implements regression tests for SQLite library.  The
# focus of this file is testing the multi-index OR clause optimizer.
#

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

ifcapable !or_opt||!compound {
  finish_test
  return
}

# Evaluate SQL.  Return the result set followed by the
# and the number of full-scan steps.
#