SQLite

**
*************************************************************************
** This file contains code used to implement the VACUUM command.
**
** Most of the code in this file may be omitted by defining the
** SQLITE_OMIT_VACUUM macro.
**
** $Id: vacuum.c,v 1.35 2004/11/22 13:35:41 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include "os.h"

#ifndef SQLITE_OMIT_VACUUM
/*
** Generate a random name of 20 character in length.

  if( rc!=SQLITE_OK ) goto end_of_vacuum;

  /* Query the schema of the main database. Create a mirror schema
  ** in the temporary database.
  */
  rc = execExecSql(db, 
      "SELECT 'CREATE TABLE vacuum_db.' || substr(sql,14,100000000) "
      "  FROM sqlite_master WHERE type='table'");
  if( rc!=SQLITE_OK ) goto end_of_vacuum;
  rc = execExecSql(db, 
      "SELECT 'CREATE INDEX vacuum_db.' || substr(sql,14,100000000)"
      "  FROM sqlite_master WHERE sql LIKE 'CREATE INDEX %' ");

  if( rc!=SQLITE_OK ) goto end_of_vacuum;
  rc = execExecSql(db, 
      "SELECT 'CREATE UNIQUE INDEX vacuum_db.' || substr(sql,21,100000000) "
      "  FROM sqlite_master WHERE sql LIKE 'CREATE UNIQUE INDEX %'");

  if( rc!=SQLITE_OK ) goto end_of_vacuum;
  rc = execExecSql(db, 
      "SELECT 'CREATE VIEW vacuum_db.' || substr(sql,13,100000000) "
      "  FROM sqlite_master WHERE type='view'"
  );
  if( rc!=SQLITE_OK ) goto end_of_vacuum;

  /* Loop through the tables in the main database. For each, do
  ** an "INSERT INTO vacuum_db.xxx SELECT * FROM xxx;" to copy

#
# The author or author's hereby grant to the public domain a non-exclusive,
# fully paid-up, perpetual, license in the software and all related
# intellectual property to make, have made, use, have used, reproduce,
# prepare derivative works, distribute, perform and display the work.  
#
#*************************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script is testing the ALTER TABLE statement.
#
# $Id: alter.test,v 1.7 2004/11/22 13:35:42 danielk1977 Exp $
#

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

# If SQLITE_OMIT_ALTERTABLE is defined, omit this file.
ifcapable !altertable {

    CREATE INDEX t1i2 ON t1(a,b);
    CREATE INDEX i3 ON [t1'x1](b,c);
    CREATE TEMP TABLE "temp table"(e,f,g UNIQUE);
    CREATE INDEX i2 ON [temp table](f);
    INSERT INTO [temp table] VALUES(5,6,7);
  }
  execsql {
    SELECT 't1', * FROM t1;

    SELECT 't1''x1', * FROM "t1'x1";

    SELECT * FROM [temp table];
  }
} {t1 1 2 t1'x1 3 4 5 6 7}
do_test alter-1.2 {
  execsql {
    CREATE TEMP TABLE objlist(type, name, tbl_name);
    INSERT INTO objlist SELECT type, name, tbl_name FROM sqlite_master;

    INSERT INTO objlist 
      SELECT type, name, tbl_name FROM sqlite_temp_master WHERE NAME!='objlist';
    SELECT type, name, tbl_name FROM objlist ORDER BY tbl_name, type desc, name;
  }
} [list \
     table t1                              t1             \
     index t1i1                            t1             \
     index t1i2                            t1             \
     table t1'x1                           t1'x1          \
     index i3                              t1'x1          \

    ALTER TABLE "t1'x1" RENAME TO T2;
    ALTER TABLE [temp table] RENAME to TempTab;
  }
} {}
integrity_check alter-1.3.1
do_test alter-1.4 {
  execsql {
    SELECT 't1', * FROM [-t1-];

    SELECT 't2', * FROM t2;

    SELECT * FROM temptab;
  }
} {t1 1 2 t2 3 4 5 6 7}
do_test alter-1.5 {
  execsql {
    DELETE FROM objlist;
    INSERT INTO objlist SELECT type, name, tbl_name FROM sqlite_master;

    INSERT INTO objlist 
      SELECT type, name, tbl_name FROM sqlite_temp_master WHERE NAME!='objlist';
    SELECT type, name, tbl_name FROM objlist ORDER BY tbl_name, type desc, name;
  }
} [list \
     table -t1-                         -t1-        \
     index t1i1                         -t1-        \
     index t1i2                         -t1-        \
     table T2                           T2          \
     index i3                           T2          \

# Make sure the changes persist after restarting the database.
# (The TEMP table will not persist, of course.)
#
do_test alter-1.6 {
  db close
  set DB [sqlite3 db test.db]
  execsql {
    CREATE TEMP TABLE objlist(type, name, tbl_name);
    INSERT INTO objlist SELECT type, name, tbl_name FROM sqlite_master;
    INSERT INTO objlist 
        SELECT type, name, tbl_name FROM sqlite_temp_master 

        WHERE NAME!='objlist';
    SELECT type, name, tbl_name FROM objlist 
        ORDER BY tbl_name, type desc, name;
  }
} [list \
     table -t1-                         -t1-           \
     index t1i1                         -t1-           \
     index t1i2                         -t1-           \
     table T2                           T2          \
     index i3                           T2          \
     index {sqlite_autoindex_T2_1}      T2          \
     index {sqlite_autoindex_T2_2}      T2          \
  ]

# Make sure the ALTER TABLE statements work with the
# non-callback API
#
do_test alter-1.7 {
  stepsql $DB {
    ALTER TABLE [-t1-] RENAME to [*t1*];
    ALTER TABLE T2 RENAME TO [<t2>];
  }
  execsql {
    DELETE FROM objlist;
    INSERT INTO objlist SELECT type, name, tbl_name FROM sqlite_master;
    INSERT INTO objlist 
        SELECT type, name, tbl_name FROM sqlite_temp_master 

        WHERE NAME!='objlist';
    SELECT type, name, tbl_name FROM objlist 
        ORDER BY tbl_name, type desc, name;
  }
} [list \
     table *t1*                         *t1*           \
     index t1i1                         *t1*           \
     index t1i2                         *t1*           \
     table <t2>                         <t2>          \
     index i3                           <t2>          \

#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script is testing the ATTACH and DETACH commands
# and related functionality.
#
# $Id: attach.test,v 1.31 2004/11/22 13:35:42 danielk1977 Exp $
#

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

for {set i 2} {$i<=15} {incr i} {
  file delete -force test$i.db

# This one is tricky.  On the UNION ALL select, we have to make sure
# the schema for both main and db2 is valid before starting to execute
# the first query of the UNION ALL.  If we wait to test the validity of
# the schema for main until after the first query has run, that test will
# fail and the query will abort but we will have already output some
# results.  When the query is retried, the results will be repeated.
#
ifcapable compound {
do_test attach-4.8 {
  execsql {
    ATTACH DATABASE 'test2.db' AS db2;
    INSERT INTO db2.t3 VALUES(13,14);
    SELECT * FROM db2.t4 UNION ALL SELECT * FROM main.t4;
  }
} {db2.6 db2.13 main.11}

do_test attach-4.9 {
  ifcapable {!trigger} {execsql {INSERT INTO main.t4 VALUES('main.15')}}
  execsql {
    INSERT INTO main.t3 VALUES(15,16);
    SELECT * FROM db2.t4 UNION ALL SELECT * FROM main.t4;
  }
} {db2.6 db2.13 main.11 main.15}
} ;# ifcapable compound

ifcapable !compound {
  ifcapable {!trigger} {execsql {INSERT INTO main.t4 VALUES('main.15')}}
  execsql {
    ATTACH DATABASE 'test2.db' AS db2;
    INSERT INTO db2.t3 VALUES(13,14);
    INSERT INTO main.t3 VALUES(15,16);
  } 
} ;# ifcapable !compound

ifcapable view {
do_test attach-4.10 {
  execsql {
    DETACH DATABASE db2;
  }
  execsql {

# 2004 November 12
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#*************************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script is testing the AUTOINCREMENT features.
#
# $Id: autoinc.test,v 1.2 2004/11/22 13:35:42 danielk1977 Exp $
#

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

# If the library is not compiled with autoincrement support then
# skip all tests in this file.

  }
} {}

# AUTOINCREMENT on TEMP tables.
#
do_test autoinc-4.1 {
  execsql {
    SELECT 1, name FROM sqlite_master WHERE type='table';

    SELECT 2, name FROM sqlite_temp_master WHERE type='table';
  }
} {1 sqlite_sequence}
do_test autoinc-4.2 {
  execsql {
    CREATE TABLE t1(x INTEGER PRIMARY KEY AUTOINCREMENT, y);
    CREATE TEMP TABLE t3(a INTEGER PRIMARY KEY AUTOINCREMENT, b);
    SELECT 1, name FROM sqlite_master WHERE type='table';

    SELECT 2, name FROM sqlite_temp_master WHERE type='table';
  }
} {1 sqlite_sequence 1 t1 2 t3 2 sqlite_sequence}
do_test autoinc-4.3 {
  execsql {
    SELECT 1, * FROM main.sqlite_sequence;

    SELECT 2, * FROM temp.sqlite_sequence;
  }
} {}
do_test autoinc-4.4 {
  execsql {
    INSERT INTO t1 VALUES(10,1);
    INSERT INTO t3 VALUES(20,2);
    INSERT INTO t1 VALUES(NULL,3);
    INSERT INTO t3 VALUES(NULL,4);
  }
} {}

ifcapable compound {
do_test autoinc-4.4.1 {
  execsql {
    SELECT * FROM t1 UNION ALL SELECT * FROM t3;
  }
} {10 1 11 3 20 2 21 4}
} ;# ifcapable compound

do_test autoinc-4.5 {
  execsql {
    SELECT 1, * FROM main.sqlite_sequence;

    SELECT 2, * FROM temp.sqlite_sequence;
  }
} {1 t1 11 2 t3 21}
do_test autoinc-4.6 {
  execsql {
    INSERT INTO t1 SELECT * FROM t3;
    SELECT 1, * FROM main.sqlite_sequence;

    SELECT 2, * FROM temp.sqlite_sequence;
  }
} {1 t1 21 2 t3 21}
do_test autoinc-4.7 {
  execsql {
    INSERT INTO t3 SELECT x+100, y  FROM t1;
    SELECT 1, * FROM main.sqlite_sequence;

    SELECT 2, * FROM temp.sqlite_sequence;
  }
} {1 t1 21 2 t3 121}
do_test autoinc-4.8 {
  execsql {
    DROP TABLE t3;
    SELECT 1, * FROM main.sqlite_sequence;

    SELECT 2, * FROM temp.sqlite_sequence;
  }
} {1 t1 21}
do_test autoinc-4.9 {
  execsql {
    CREATE TEMP TABLE t2(p INTEGER PRIMARY KEY AUTOINCREMENT, q);
    INSERT INTO t2 SELECT * FROM t1;
    DROP TABLE t1;
    SELECT 1, * FROM main.sqlite_sequence;

    SELECT 2, * FROM temp.sqlite_sequence;
  }
} {2 t2 21}
do_test autoinc-4.10 {
  execsql {
    DROP TABLE t2;
    SELECT 1, * FROM main.sqlite_sequence;

    SELECT 2, * FROM temp.sqlite_sequence;
  }
} {}

# Make sure AUTOINCREMENT works on ATTACH-ed tables.
#
do_test autoinc-5.1 {
  file delete -force test2.db
  file delete -force test2.db-journal
  sqlite3 db2 test2.db
  execsql {
    CREATE TABLE t4(m INTEGER PRIMARY KEY AUTOINCREMENT, n);
    CREATE TABLE t5(o, p INTEGER PRIMARY KEY AUTOINCREMENT);
  } db2;
  execsql {
    ATTACH 'test2.db' as aux;
    SELECT 1, * FROM main.sqlite_sequence;

    SELECT 2, * FROM temp.sqlite_sequence;

    SELECT 3, * FROM aux.sqlite_sequence;
  }
} {}
do_test autoinc-5.2 {
  execsql {
    INSERT INTO t4 VALUES(NULL,1);
    SELECT 1, * FROM main.sqlite_sequence;

    SELECT 2, * FROM temp.sqlite_sequence;

    SELECT 3, * FROM aux.sqlite_sequence;
  }
} {3 t4 1}
do_test autoinc-5.3 {
  execsql {
    INSERT INTO t5 VALUES(100,200);
    SELECT * FROM sqlite_sequence
  } db2
} {t4 1 t5 200}
do_test autoinc-5.4 {
  execsql {
    SELECT 1, * FROM main.sqlite_sequence;

    SELECT 2, * FROM temp.sqlite_sequence;

    SELECT 3, * FROM aux.sqlite_sequence;
  }
} {3 t4 1 3 t5 200}

# Requirement REQ00310:  Make sure an insert fails if the sequence is
# already at its maximum value.
#
do_test autoinc-6.1 {

# 2001 September 15
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script is page cache subsystem.
#
# $Id: collate3.test,v 1.6 2004/11/22 13:35:42 danielk1977 Exp $

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

#
# Tests are organised as follows:
#

} {1 {no such collation sequence: string_compare}} 
do_test collate3-2.8 {
  catchsql {
    SELECT DISTINCT c1 FROM collate3t1;
  }
} {1 {no such collation sequence: string_compare}} 

ifcapable compound {
do_test collate3-2.9 {
  catchsql {
    SELECT c1 FROM collate3t1 UNION SELECT c1 FROM collate3t1;
  }
} {1 {no such collation sequence: string_compare}} 
do_test collate3-2.10 {
  catchsql {

  }
} {1 {no such collation sequence: string_compare}} 
do_test collate3-2.17 {
  catchsql {
    SELECT c1 FROM collate3t1 UNION ALL SELECT c1 FROM collate3t1 ORDER BY 1;
  }
} {1 {no such collation sequence: string_compare}} 
} ;# ifcapable compound

#
# Create an index that uses a collation sequence then close and
# re-open the database without re-registering the collation
# sequence. Then check that for the table with the index 
# * An INSERT fails,
# * An UPDATE on the column with the index fails,

#
#*************************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing DISTINCT, UNION, INTERSECT and EXCEPT
# SELECT statements that use user-defined collation sequences. Also
# GROUP BY clauses that use user-defined collation sequences.
#
# $Id: collate5.test,v 1.2 2004/11/22 13:35:42 danielk1977 Exp $

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


#
# Tests are organised as follows:

} {apple Apple banana {}}
do_test collate5-1.3 {
  execsql {
    SELECT DISTINCT a, b FROM collate5t1;
  }
} {a apple A Apple b banana n {}}

# The remainder of this file tests compound SELECT statements.
# Omit it if the library is compiled such that they are omitted.
#
ifcapable !compound {
  finish_test
  return
}

#
# Tests named collate5-2.* focus on UNION, EXCEPT and INTERSECT
# queries that use user-defined collation sequences.
#
# collate5-2.1.* - UNION
# collate5-2.2.* - INTERSECT

#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the INSERT statement that takes is
# result from a SELECT.
#
# $Id: insert2.test,v 1.13 2004/11/22 13:35:42 danielk1977 Exp $

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

# Create some tables with data that we can select against
#
do_test insert2-1.0 {

do_test insert2-1.1.2 {
  db changes
} {6}
do_test insert2-1.1.3 {
  execsql {SELECT * FROM t1 ORDER BY log}
} {0 1 1 1 2 2 3 4 4 8 5 4}

ifcapable compound {
do_test insert2-1.2.1 {
  catch {execsql {DROP TABLE t1}}
  execsql {
    CREATE TABLE t1(log int, cnt int);
    INSERT INTO t1 
       SELECT log, count(*) FROM d1 GROUP BY log
       EXCEPT SELECT n-1,log FROM d1;

  }
} {}
do_test insert2-1.3.2 {
  execsql {
    SELECT * FROM t1 ORDER BY log;
  }
} {1 1 2 2}
} ;# ifcapable compound
execsql {PRAGMA count_changes=off;}

do_test insert2-1.4 {
  catch {execsql {DROP TABLE t1}}
  set r [execsql {
    CREATE TABLE t1(log int, cnt int);
    CREATE INDEX i1 ON t1(log);
    CREATE INDEX i2 ON t1(cnt);
    INSERT INTO t1 SELECT log, count() FROM d1 GROUP BY log;

#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for joins, including outer joins.
#
# $Id: join.test,v 1.13 2004/11/22 13:35:42 danielk1977 Exp $

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

do_test join-1.1 {
  execsql {
    CREATE TABLE t1(a,b,c);

#
# To test the problem, we generate the same LEFT OUTER JOIN in two
# separate selects but with on using a subquery and the other calling
# the table directly.  Then connect the two SELECTs using an EXCEPT.
# Both queries should generate the same results so the answer should
# be an empty set.
#
ifcapable compound {
do_test join-9.1 {
  execsql {
    BEGIN;
    CREATE TABLE t12(a,b);
    INSERT INTO t12 VALUES(1,11);
    INSERT INTO t12 VALUES(2,22);
    CREATE TABLE t13(b,c);

    CREATE VIEW v13 AS SELECT * FROM t13 WHERE b>0;
    SELECT * FROM t12 NATURAL LEFT JOIN t13
      EXCEPT
      SELECT * FROM t12 NATURAL LEFT JOIN v13;
  }
} {}
} ;# ifcapable view
} ;# ifcapable compound

finish_test

#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the LIMIT ... OFFSET ... clause
#  of SELECT statements.
#
# $Id: limit.test,v 1.18 2004/11/22 13:35:42 danielk1977 Exp $

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

# Build some test data
#
execsql {

    SELECT * FROM t6 LIMIT 0 OFFSET 1
  }
} {}

# Make sure LIMIT works well with compound SELECT statements.
# Ticket #393
#
ifcapable compound {
do_test limit-7.1.1 {
  catchsql {
    SELECT x FROM t2 LIMIT 5 UNION ALL SELECT a FROM t6;
  }
} {1 {LIMIT clause should come after UNION ALL not before}}
do_test limit-7.1.2 {
  catchsql {

} {31}
do_test limit-7.12 {
  execsql {
    SELECT a+27 FROM t6 INTERSECT SELECT x FROM t2 
       ORDER BY 1 DESC LIMIT 1 OFFSET 1;
  }
} {30}
} ;# ifcapable compound

# Tests for limit in conjunction with distinct.  The distinct should
# occur before both the limit and the offset.  Ticket #749.
#
do_test limit-8.1 {
  execsql {
    SELECT DISTINCT round(x/100) FROM t3 LIMIT 5;

# 2001 September 15
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script is in-memory database backend.
#
# $Id: memdb.test,v 1.12 2004/11/22 13:35:42 danielk1977 Exp $


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

ifcapable memorydb {

  }
} {t1 t2 t3 t4}
do_test memdb-6.4 {
  execsql {
    SELECT * FROM t2;
  }
} {1 2 1 2 3 2 3 4 1 4 5 4}
ifcapable compound {
do_test memdb-6.5 {
  execsql {
    SELECT a FROM t2 UNION SELECT b FROM t2 ORDER BY 1;
  }
} {1 2 3 4 5}
} ;# ifcapable compound 
do_test memdb-6.6 {
  execsql {
    CREATE INDEX i2 ON t2(c);
    SELECT a FROM t2 ORDER BY c;
  }
} {1 3 2 4}
do_test memdb-6.6 {

#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing SELECT statements that contain
# aggregate min() and max() functions and which are handled as
# as a special case.
#
# $Id: minmax.test,v 1.14 2004/11/22 13:35:42 danielk1977 Exp $

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

do_test minmax-1.0 {
  execsql {
    BEGIN;

    SELECT b FROM t2 WHERE a=(SELECT max(a) FROM t2)
  }
} {999}
do_test minmax-3.3 {
  set sqlite_search_count
} {0}

ifcapable compound {
do_test minmax-4.1 {
  execsql {
    SELECT coalesce(min(x+0),-1), coalesce(max(x+0),-1) FROM
      (SELECT * FROM t1 UNION SELECT NULL as 'x', NULL as 'y')
  }
} {1 20}
do_test minmax-4.2 {
  execsql {
    SELECT y, sum(x) FROM
      (SELECT null, y+1 FROM t1 UNION SELECT * FROM t1)
    GROUP BY y ORDER BY y;
  }
} {1 1.0 2 5.0 3 22.0 4 92.0 5 90.0 6 0.0}
do_test minmax-4.3 {
  execsql {
    SELECT y, count(x), count(*) FROM
      (SELECT null, y+1 FROM t1 UNION SELECT * FROM t1)
    GROUP BY y ORDER BY y;
  }
} {1 1 1 2 2 3 3 4 5 4 8 9 5 5 6 6 0 1}
} ;# ifcapable compound

# Make sure the min(x) and max(x) optimizations work on empty tables
# including empty tables with indices. Ticket #296.
#
do_test minmax-5.1 {
  execsql {
    CREATE TABLE t3(x INTEGER UNIQUE NOT NULL);

    SELECT min(a), max(a) FROM t5;
  }
} {34 1234}

# Ticket #658:  Test the min()/max() optimization when the FROM clause
# is a subquery.
#
ifcapable compound {
do_test minmax-9.1 {
  execsql {
    SELECT max(rowid) FROM (
      SELECT max(rowid) FROM t4 UNION SELECT max(rowid) FROM t5
    )
  }
} {1}
do_test minmax-9.2 {
  execsql {
    SELECT max(rowid) FROM (
      SELECT max(rowid) FROM t4 EXCEPT SELECT max(rowid) FROM t5
    )
  }
} {{}}
} ;# ifcapable compound

# If there is a NULL in an aggregate max() or min(), ignore it.  An
# aggregate min() or max() will only return NULL if all values are NULL.
#
do_test minmax-10.1 {
  execsql {
    CREATE TABLE t6(x);

#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for miscellanous features that were
# left out of other test files.
#
# $Id: misc2.test,v 1.17 2004/11/22 13:35:42 danielk1977 Exp $

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

ifcapable {trigger} {
# Test for ticket #360
#

    INSERT INTO foo(bar) VALUES (111);
  }
} {1 aiieee}
} ;# endif trigger

# Make sure ROWID works on a view and a subquery.  Ticket #364
#
ifcapable view {
do_test misc2-2.1 {
  execsql {
    CREATE TABLE t1(a,b,c);
    INSERT INTO t1 VALUES(1,2,3);
    CREATE TABLE t2(a,b,c);
    INSERT INTO t2 VALUES(7,8,9);
    SELECT rowid, * FROM (SELECT * FROM t1, t2);
  }
} {{} 1 2 3 7 8 9}

do_test misc2-2.2 {
  execsql {
    CREATE VIEW v1 AS SELECT * FROM t1, t2;
    SELECT rowid, * FROM v1;
  }
} {{} 1 2 3 7 8 9}
} ;# ifcapable view

#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for miscellanous features that were
# left out of other test files.
#
# $Id: misc4.test,v 1.8 2004/11/22 13:35:42 danielk1977 Exp $

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

# Prepare a statement that will create a temporary table.  Then do
# a rollback.  Then try to execute the prepared statement.
#

  catchsql {
    INSERT INTO t3 VALUES(1);
  }
} {0 {}}

# Ticket #966
#
ifcapable compound {
do_test misc4-3.1 {
  execsql { 
    CREATE TABLE Table1(ID integer primary key, Value TEXT);
    INSERT INTO Table1 VALUES(1, 'x');
    CREATE TABLE Table2(ID integer NOT NULL, Value TEXT);
    INSERT INTO Table2 VALUES(1, 'z');
    INSERT INTO Table2 VALUES (1, 'a');
    SELECT ID, Value FROM Table1
       UNION SELECT ID, max(Value) FROM Table2 GROUP BY 1,2
    ORDER BY 1, 2;
  }
} {{} {} 1 x 1 z}
} ;# ifcapable compound


finish_test

    select distinct b from t1 order by b;
  }
} {{} 0 1}

# A UNION to two queries should treat NULL values
# as distinct
#
ifcapable compound {
do_test null-6.1 {
  execsql {
    select b from t1 union select c from t1 order by c;
  }
} {{} 0 1}
} ;# ifcapable compound

# The UNIQUE constraint only applies to non-null values
#
do_test null-7.1 {
  execsql {
    create table t2(a, b unique on conflict ignore);
    insert into t2 values(1,1);

# 2001 September 15
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the SELECT statement.
#
# $Id: select1.test,v 1.37 2004/11/22 13:35:42 danielk1977 Exp $

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

# Try to select on a non-existant table.
#
do_test select1-1.1 {

  lappend v $msg
} {1 {ambiguous column name: A.f1}}
do_test select1-6.9 {
  set v [catch {execsql2 {SELECT A.f1, B.f1 FROM test1 as A, test1 as B 
         ORDER BY A.f1, B.f1}} msg]
  lappend v $msg
} {0 {A.f1 11 B.f1 11 A.f1 11 B.f1 33 A.f1 33 B.f1 11 A.f1 33 B.f1 33}}

ifcapable compound {
do_test select1-6.10 {
  set v [catch {execsql2 {
    SELECT f1 FROM test1 UNION SELECT f2 FROM test1
    ORDER BY f2;
  }} msg]
  lappend v $msg
} {0 {f2 11 f2 22 f2 33 f2 44}}
do_test select1-6.11 {
  set v [catch {execsql2 {
    SELECT f1 FROM test1 UNION SELECT f2+100 FROM test1
    ORDER BY f2+100;
  }} msg]
  lappend v $msg
} {0 {f2+100 11 f2+100 33 f2+100 122 f2+100 144}}
} ;#ifcapable compound

do_test select1-7.1 {
  set v [catch {execsql {
     SELECT f1 FROM test1 WHERE f2=;
  }} msg]
  lappend v $msg
} {1 {near ";": syntax error}}
ifcapable compound {
do_test select1-7.2 {
  set v [catch {execsql {
     SELECT f1 FROM test1 UNION SELECT WHERE;
  }} msg]
  lappend v $msg
} {1 {near "WHERE": syntax error}}
} ;# ifcapable compound
do_test select1-7.3 {
  set v [catch {execsql {SELECT f1 FROM test1 as 'hi', test2 as}} msg]
  lappend v $msg
} {1 {near "as": syntax error}}
do_test select1-7.4 {
  set v [catch {execsql {
     SELECT f1 FROM test1 ORDER BY;

    SELECT 1 AS 'a','hello' AS 'b',2 AS 'c'
  }
} {a 1 b hello c 2}
do_test select1-12.4 {
  execsql {
    DELETE FROM t3;
    INSERT INTO t3 VALUES(1,2);
  }
} {}

ifcapable compound {
do_test select1-12.5 {
  execsql {
    SELECT * FROM t3 UNION SELECT 3 AS 'a', 4 ORDER BY a;
  }
} {1 2 3 4}

do_test select1-12.6 {
  execsql {
    SELECT 3, 4 UNION SELECT * FROM t3;
  }
} {1 2 3 4}
} ;# ifcapable compound

do_test select1-12.7 {
  execsql {
    SELECT * FROM t3 WHERE a=(SELECT 1);
  }
} {1 2}
do_test select1-12.8 {
  execsql {
    SELECT * FROM t3 WHERE a=(SELECT 2);
  }
} {}

ifcapable compound {
do_test select1-12.9 {
  execsql2 {
    SELECT x FROM (
      SELECT a,b FROM t3 UNION SELECT a AS 'x', b AS 'y' FROM t4 ORDER BY a,b
    ) ORDER BY x;
  }
} {x 1 x 3}
do_test select1-12.10 {
  execsql2 {
    SELECT z.x FROM (
      SELECT a,b FROM t3 UNION SELECT a AS 'x', b AS 'y' FROM t4 ORDER BY a,b
    ) AS 'z' ORDER BY x;
  }
} {z.x 1 z.x 3}
} ;# ifcapable compound


finish_test

#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing UNION, INTERSECT and EXCEPT operators
# in SELECT statements.
#
# $Id: select4.test,v 1.17 2004/11/22 13:35:42 danielk1977 Exp $

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

# Most tests in this file depend on compound-select. But there are a couple
# right at the end that test DISTINCT, so we cannot omit the entire file.
#
ifcapable compound {

# Build some test data
#
execsql {
  CREATE TABLE t1(n int, log int);
  BEGIN;
}

} {n 6 log 3 n 7 log 3}
do_test select4-7.4 {
  execsql2 {
    SELECT * FROM t1 WHERE n IN (SELECT n FROM t1 UNION SELECT x FROM t2)
    ORDER BY n LIMIT 2
  }
} {n 1 log 0 n 2 log 1}

} ;# ifcapable compound

# Make sure DISTINCT works appropriately on TEXT and NUMERIC columns.
do_test select4-8.1 {
  execsql {
    BEGIN;
    CREATE TABLE t3(a text, b float, c text);
    INSERT INTO t3 VALUES(1, 1.1, '1.1');

#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing SELECT statements that contain
# subqueries in their FROM clause.
#
# $Id: select6.test,v 1.14 2004/11/22 13:35:42 danielk1977 Exp $

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

do_test select6-1.0 {
  execsql {
    BEGIN;

    WHERE a=b
    ORDER BY a
  }
} {8 5 8 9 6 9 10 7 10}

# Tests of compound sub-selects
#
ifcapable compound {
do_test select5-6.1 {
  execsql {
    DELETE FROM t1 WHERE x>4;
    SELECT * FROM t1
  }
} {1 1 2 2 3 2 4 3}
do_test select6-6.2 {

do_test select6-6.6 {
  execsql {
    SELECT * FROM (
      SELECT x AS 'a' FROM t1 EXCEPT SELECT x*2 AS 'a' FROM t1
    ) ORDER BY a;
  }
} {1 3}
} ;# ifcapable compound

# Subselects with no FROM clause
#
do_test select6-7.1 {
  execsql {
    SELECT * FROM (SELECT 1)
  }

# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing compute SELECT statements and nested
# views.
#
# $Id: select7.test,v 1.3 2004/11/22 13:35:42 danielk1977 Exp $


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

ifcapable compound {

# A 3-way INTERSECT.  Ticket #875
do_test select7-1.1 {
  execsql {
    create temp table t1(x);
    insert into t1 values('amx');
    insert into t1 values('anx');

    CREATE VIEW tv1 AS SELECT x.id, tx.id FROM x JOIN tempx tx ON tx.id=x.id;
    CREATE VIEW tv1b AS SELECT x.id, tx.id FROM x JOIN tempx tx on tx.id=x.id;
    CREATE VIEW tv2 AS SELECT * FROM tv1 UNION SELECT * FROM tv1b;
    SELECT * FROM tv2;
  }
} {1 1}
} ;# ifcapable view

} ;# ifcapable compound


finish_test

# 2001 September 15.
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the CREATE TABLE statement.
#
# $Id: sort.test,v 1.18 2004/11/22 13:35:42 danielk1977 Exp $

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

# Create a bunch of data to sort against
#
do_test sort-1.0 {

  }
} {1 2 11 12}
do_test sort-7.4 {
  execsql {
    SELECT b FROM v4 ORDER BY 1;
  }
} {1 11 12 2}

ifcapable compound {
do_test sort-7.5 {
  execsql {
    SELECT a FROM t4 UNION SELECT a FROM v4 ORDER BY 1;
  }
} {1 2 11 12}
do_test sort-7.6 {
  execsql {
    SELECT b FROM t4 UNION SELECT a FROM v4 ORDER BY 1;
  }
} {1 2 11 12 1 11 12 2}  ;# text from t4.b and numeric from v4.a
do_test sort-7.7 {
  execsql {
    SELECT a FROM t4 UNION SELECT b FROM v4 ORDER BY 1;
  }
} {1 2 11 12 1 11 12 2} ;# numeric from t4.a and text from v4.b
do_test sort-7.8 {
  execsql {
    SELECT b FROM t4 UNION SELECT b FROM v4 ORDER BY 1;
  }
} {1 11 12 2}
} ;# ifcapable compound
} ;# ifcapable view

#### Version 3 works differently here:
#do_test sort-7.9 {
#  execsql {
#    SELECT b FROM t4 UNION SELECT b FROM v4 ORDER BY 1 COLLATE numeric;
#  }

#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing SELECT statements that are part of
# expressions.
#
# $Id: subselect.test,v 1.11 2004/11/22 13:35:42 danielk1977 Exp $

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

# Basic sanity checking.  Try a simple subselect.
#
do_test subselect-1.1 {

  execsql {
    SELECT 2 IN (SELECT a FROM t1 ORDER BY a DESC);
  }
} {0}

# Verify that the ORDER BY clause is honored in a subquery.
#
ifcapable compound {
do_test subselect-3.1 {
  execsql {
    CREATE TABLE t3(x int);
    INSERT INTO t3 SELECT a FROM t1 UNION ALL SELECT b FROM t1;
    SELECT * FROM t3 ORDER BY x;
  }
} {1 2 3 4 5 6}
} ;# ifcapable compound
ifcapable !compound {
do_test subselect-3.1 {
  execsql {
    CREATE TABLE t3(x int);
    INSERT INTO t3 SELECT a FROM t1; 
    INSERT INTO t3 SELECT b FROM t1;
    SELECT * FROM t3 ORDER BY x;
  }
} {1 2 3 4 5 6}
} ;# ifcapable !compound

do_test subselect-3.2 {
  execsql {
    SELECT sum(x) FROM (SELECT x FROM t3 ORDER BY x LIMIT 2);
  }
} {3.0}
do_test subselect-3.3 {
  execsql {

do_test trigger-3.7 {
  execsql {
    DROP TABLE t2;
    CREATE TABLE t2(x,y);
    SELECT * FROM t2;
  }
} {}

# There are two versions of trigger-3.8 and trigger-3.9. One that uses
# compound SELECT statements, and another that does not.
ifcapable compound {
do_test trigger-3.8 {
  execsql {
    INSERT INTO t1 VALUES(3,4);
    SELECT * FROM t1 UNION ALL SELECT * FROM t2;
  }
} {1 2 3 4 3 4}
do_test trigger-3.9 {
  db close
  sqlite3 db test.db
  execsql {
    INSERT INTO t1 VALUES(5,6);
    SELECT * FROM t1 UNION ALL SELECT * FROM t2;
  }
} {1 2 3 4 5 6 3 4}
} ;# ifcapable compound
ifcapable !compound {
do_test trigger-3.8 {
  execsql {
    INSERT INTO t1 VALUES(3,4);
    SELECT * FROM t1; 
    SELECT * FROM t2;
  }
} {1 2 3 4 3 4}
do_test trigger-3.9 {
  db close
  sqlite3 db test.db
  execsql {
    INSERT INTO t1 VALUES(5,6);
    SELECT * FROM t1;
    SELECT * FROM t2;
  }
} {1 2 3 4 5 6 3 4}
} ;# ifcapable !compound

do_test trigger-4.1 {
  execsql {
    CREATE TEMP TRIGGER r1 BEFORE INSERT ON t1 BEGIN
      INSERT INTO t2 VALUES(NEW.a,NEW.b);
    END;
    INSERT INTO t1 VALUES(7,8);

    DROP TRIGGER [trigger];
    SELECT name FROM sqlite_master WHERE type='trigger';
  }
} {}

# Make sure REPLACE works inside of triggers.
#
# There are two versions of trigger-9.1 and trigger-9.2. One that uses
# compound SELECT statements, and another that does not.
ifcapable compound {
do_test trigger-9.1 {
  execsql {
    CREATE TABLE t3(a,b);
    CREATE TABLE t4(x UNIQUE, b);
    CREATE TRIGGER r34 AFTER INSERT ON t3 BEGIN
      REPLACE INTO t4 VALUES(new.a,new.b);
    END;
    INSERT INTO t3 VALUES(1,2);
    SELECT * FROM t3 UNION ALL SELECT 99, 99 UNION ALL SELECT * FROM t4;
  }
} {1 2 99 99 1 2}
do_test trigger-9.2 {
  execsql {
    INSERT INTO t3 VALUES(1,3);
    SELECT * FROM t3 UNION ALL SELECT 99, 99 UNION ALL SELECT * FROM t4;
  }
} {1 2 1 3 99 99 1 3}
}
ifcapable !compound {
do_test trigger-9.1 {
  execsql {
    CREATE TABLE t3(a,b);
    CREATE TABLE t4(x UNIQUE, b);
    CREATE TRIGGER r34 AFTER INSERT ON t3 BEGIN
      REPLACE INTO t4 VALUES(new.a,new.b);
    END;
    INSERT INTO t3 VALUES(1,2);
    SELECT * FROM t3; SELECT 99, 99; SELECT * FROM t4;
  }
} {1 2 99 99 1 2}
do_test trigger-9.2 {
  execsql {
    INSERT INTO t3 VALUES(1,3);
    SELECT * FROM t3; SELECT 99, 99; SELECT * FROM t4;
  }
} {1 2 1 3 99 99 1 3}
}

execsql {
  DROP TABLE t2;
  DROP TABLE t3;
  DROP TABLE t4;
}

# 2002 February 26
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing VIEW statements.
#
# $Id: view.test,v 1.21 2004/11/22 13:35:42 danielk1977 Exp $
set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Omit this entire file if the library is not configured with views enabled.
ifcapable !view {
  finish_test
  return

do_test view-3.3 {
  execsql2 {
    DROP VIEW v1;
    CREATE VIEW v1 AS SELECT a AS 'xyz', b+c AS 'pqr', c-b FROM t1;
    SELECT * FROM v1 LIMIT 1
  }
} {xyz 2 pqr 7 c-b 1}

ifcapable compound {
do_test  view-3.4 {
  execsql2 {
    CREATE VIEW v3 AS SELECT a FROM t1 UNION SELECT b FROM t1 ORDER BY b;
    SELECT * FROM v3 LIMIT 4;
  }
} {b 2 b 3 b 5 b 6}
do_test  view-3.5 {
  execsql2 {
    CREATE VIEW v4 AS 
      SELECT a, b FROM t1 
      UNION
      SELECT b AS 'x', a AS 'y' FROM t1
      ORDER BY x, y;
    SELECT y FROM v4 ORDER BY y LIMIT 4;
  }
} {y 2 y 3 y 5 y 6}
} ;# ifcapable compound


do_test view-4.1 {
  catchsql {
    DROP VIEW t1;
  }
} {1 {use DROP TABLE to delete table t1}}