xref: /sqlite-3.40.0/test/insert4.test (revision 8103b7d2) 
1# 2007 January 24
2#
3# The author disclaims copyright to this source code.  In place of
4# a legal notice, here is a blessing:
5#
6#    May you do good and not evil.
7#    May you find forgiveness for yourself and forgive others.
8#    May you share freely, never taking more than you give.
9#
10#***********************************************************************
11# This file implements regression tests for SQLite library.  The
12# focus of this file is testing corner cases of the INSERT statement.
13#
14# $Id: insert4.test,v 1.1 2007/02/24 13:23:53 drh Exp $
15
16set testdir [file dirname $argv0]
17source $testdir/tester.tcl
18
19# Ticket #2252.  Make sure the an INSERT from identical tables
20# does not violate constraints.
21#
22do_test insert4-1.1 {
23  execsql {
24    CREATE TABLE t1(a int, b int, check(b>a));
25    CREATE TABLE t2(x int, y int);
26    INSERT INTO t2 VALUES(9,1);
27  }
28  catchsql {
29    INSERT INTO t1 SELECT * FROM t2;
30  }
31} {1 {constraint failed}}
32do_test insert4-1.2 {
33  execsql {
34    SELECT * FROM t1;
35  }
36} {}
37
38# Other coverage tests for the INSERT transfer optimization.
39#
40do_test insert4-2.1 {
41  execsql {
42    INSERT INTO t1 SELECT 4, 8;
43    SELECT * FROM t1;
44  }
45} {4 8}
46do_test insert4-2.2.1 {
47  execsql {
48    CREATE TABLE t3(a int, b int);
49    INSERT INTO t2 SELECT y, x FROM t2;
50    INSERT INTO t3 SELECT * FROM t2 LIMIT 1;
51    SELECT * FROM t3;
52  }
53} {9 1}
54do_test insert4-2.2.2 {
55  catchsql {
56    DELETE FROM t1;
57    INSERT INTO t1 SELECT * FROM t2 LIMIT 1;
58    SELECT * FROM t1;
59  }
60} {1 {constraint failed}}
61do_test insert4-2.3.1 {
62  execsql {
63    DELETE FROM t3;
64    INSERT INTO t3 SELECT DISTINCT * FROM t2;
65    SELECT * FROM t3;
66  }
67} {9 1 1 9}
68do_test insert4-2.3.2 {
69  catchsql {
70    DELETE FROM t1;
71    INSERT INTO t1 SELECT DISTINCT * FROM t2;
72  }
73} {1 {constraint failed}}
74
75finish_test
76