xref: /sqlite-3.40.0/test/insert4.test (revision dd73521b) 
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 the INSERT transfer optimization.
13#
14# $Id: insert4.test,v 1.2 2007/02/24 13:53:05 drh Exp $
15
16set testdir [file dirname $argv0]
17source $testdir/tester.tcl
18
19# The sqlite3_xferopt_count variable is incremented whenever the
20# insert transfer optimization applies.
21#
22# This procedure runs a test to see if the sqlite3_xferopt_count is
23# set to N.
24#
25proc xferopt_test {testname N} {
26  do_test $testname {set ::sqlite3_xferopt_count} $N
27}
28
29# Ticket #2252.  Make sure the an INSERT from identical tables
30# does not violate constraints.
31#
32do_test insert4-1.1 {
33  set sqlite3_xferopt_count 0
34  execsql {
35    CREATE TABLE t1(a int, b int, check(b>a));
36    CREATE TABLE t2(x int, y int);
37    INSERT INTO t2 VALUES(9,1);
38  }
39  catchsql {
40    INSERT INTO t1 SELECT * FROM t2;
41  }
42} {1 {constraint failed}}
43xferopt_test insert4-1.2 0
44do_test insert4-1.3 {
45  execsql {
46    SELECT * FROM t1;
47  }
48} {}
49
50# Tests to make sure that the transfer optimization is not occurring
51# when it is not a valid optimization.
52#
53# The SELECT must be against a real table.
54do_test insert4-2.1.1 {
55  execsql {
56    INSERT INTO t1 SELECT 4, 8;
57    SELECT * FROM t1;
58  }
59} {4 8}
60xferopt_test insert4-2.1.2  0
61do_test insert4-2.2.1 {
62  catchsql {
63    DELETE FROM t1;
64    CREATE VIEW v1 AS SELECT y, x FROM t2;
65    INSERT INTO t1 SELECT * FROM v1;
66    SELECT * FROM t1;
67  }
68} {0 {1 9}}
69xferopt_test insert4-2.2.2 0
70
71# Do not run the transfer optimization if there is a LIMIT clause
72#
73do_test insert4-2.3.1 {
74  execsql {
75    CREATE TABLE t3(a int, b int);
76    INSERT INTO t2 SELECT y, x FROM t2;
77    INSERT INTO t3 SELECT * FROM t2 LIMIT 1;
78    SELECT * FROM t3;
79  }
80} {9 1}
81xferopt_test insert4-2.3.2  0
82do_test insert4-2.3.3 {
83  catchsql {
84    DELETE FROM t1;
85    INSERT INTO t1 SELECT * FROM t2 LIMIT 1;
86    SELECT * FROM t1;
87  }
88} {1 {constraint failed}}
89xferopt_test insert4-2.3.4 0
90
91# Do not run the transfer optimization if there is a DISTINCT
92#
93do_test insert4-2.4.1 {
94  execsql {
95    DELETE FROM t3;
96    INSERT INTO t3 SELECT DISTINCT * FROM t2;
97    SELECT * FROM t3;
98  }
99} {9 1 1 9}
100xferopt_test insert4-2.4.2 0
101do_test insert4-2.4.3 {
102  catchsql {
103    DELETE FROM t1;
104    INSERT INTO t1 SELECT DISTINCT * FROM t2;
105  }
106} {1 {constraint failed}}
107xferopt_test insert4-2.4.4 0
108
109# Do run the transfer optimization if tables have identical
110# CHECK constraints.
111#
112do_test insert4-3.1.1 {
113  set sqlite3_xferopt_count 0
114  execsql {
115    DELETE FROM t1;
116    INSERT INTO t1 VALUES(1,9);
117    CREATE TABLE t4(m int, n int, CHECK(n>m));
118    INSERT INTO t4 SELECT * FROM t1;
119    SELECT * FROM t4;
120  }
121} {1 9}
122xferopt_test insert4-3.1.2 1
123
124finish_test
125