3
# The author disclaims copyright to this source code. In place of
4
# a legal notice, here is a blessing:
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.
10
#***********************************************************************
11
# This file implements regression tests for SQLite library. The
12
# focus of this file is testing the INSERT transfer optimization.
14
# $Id: insert4.test,v 1.5 2007/04/12 21:25:02 drh Exp $
16
set testdir [file dirname $argv0]
17
source $testdir/tester.tcl
19
# The sqlite3_xferopt_count variable is incremented whenever the
20
# insert transfer optimization applies.
22
# This procedure runs a test to see if the sqlite3_xferopt_count is
25
proc xferopt_test {testname N} {
26
do_test $testname {set ::sqlite3_xferopt_count} $N
29
# Create tables used for testing.
32
CREATE TABLE t1(a int, b int, check(b>a));
33
CREATE TABLE t2(x int, y int);
34
CREATE VIEW v2 AS SELECT y, x FROM t2;
35
CREATE TABLE t3(a int, b int);
38
# Ticket #2252. Make sure the an INSERT from identical tables
39
# does not violate constraints.
42
set sqlite3_xferopt_count 0
46
INSERT INTO t2 VALUES(9,1);
49
INSERT INTO t1 SELECT * FROM t2;
51
} {1 {constraint failed}}
52
xferopt_test insert4-1.2 0
59
# Tests to make sure that the transfer optimization is not occurring
60
# when it is not a valid optimization.
62
# The SELECT must be against a real table.
63
do_test insert4-2.1.1 {
66
INSERT INTO t1 SELECT 4, 8;
70
xferopt_test insert4-2.1.2 0
71
do_test insert4-2.2.1 {
74
INSERT INTO t1 SELECT * FROM v2;
78
xferopt_test insert4-2.2.2 0
80
# Do not run the transfer optimization if there is a LIMIT clause
82
do_test insert4-2.3.1 {
85
INSERT INTO t2 VALUES(9,1);
86
INSERT INTO t2 SELECT y, x FROM t2;
87
INSERT INTO t3 SELECT * FROM t2 LIMIT 1;
91
xferopt_test insert4-2.3.2 0
92
do_test insert4-2.3.3 {
95
INSERT INTO t1 SELECT * FROM t2 LIMIT 1;
98
} {1 {constraint failed}}
99
xferopt_test insert4-2.3.4 0
101
# Do not run the transfer optimization if there is a DISTINCT
103
do_test insert4-2.4.1 {
106
INSERT INTO t3 SELECT DISTINCT * FROM t2;
110
xferopt_test insert4-2.4.2 0
111
do_test insert4-2.4.3 {
114
INSERT INTO t1 SELECT DISTINCT * FROM t2;
116
} {1 {constraint failed}}
117
xferopt_test insert4-2.4.4 0
119
# The following procedure constructs two tables then tries to transfer
120
# data from one table to the other. Checks are made to make sure the
121
# transfer is successful and that the transfer optimization was used or
122
# not, as appropriate.
124
# xfer_check TESTID XFER-USED INIT-DATA DEST-SCHEMA SRC-SCHEMA
126
# The TESTID argument is the symbolic name for this test. The XFER-USED
127
# argument is true if the transfer optimization should be employed and
128
# false if not. INIT-DATA is a single row of data that is to be
129
# transfered. DEST-SCHEMA and SRC-SCHEMA are table declarations for
130
# the destination and source tables.
132
proc xfer_check {testid xferused initdata destschema srcschema} {
133
execsql "CREATE TABLE dest($destschema)"
134
execsql "CREATE TABLE src($srcschema)"
135
execsql "INSERT INTO src VALUES([join $initdata ,])"
136
set ::sqlite3_xferopt_count 0
139
INSERT INTO dest SELECT * FROM src;
144
set ::sqlite3_xferopt_count
153
# Do run the transfer optimization if tables have identical
156
xfer_check insert4-3.1 1 {1 9} \
157
{a int, b int CHECK(b>a)} \
158
{x int, y int CHECK(y>x)}
159
xfer_check insert4-3.2 1 {1 9} \
160
{a int, b int CHECK(b>a)} \
161
{x int CHECK(y>x), y int}
163
# Do run the transfer optimization if the destination table lacks
164
# any CHECK constraints regardless of whether or not there are CHECK
165
# constraints on the source table.
167
xfer_check insert4-3.3 1 {1 9} \
169
{x int, y int CHECK(y>x)}
171
# Do run the transfer optimization if the destination table omits
172
# NOT NULL constraints that the source table has.
174
xfer_check insert4-3.4 0 {1 9} \
175
{a int, b int CHECK(b>a)} \
178
# Do not run the optimization if the destination has NOT NULL
179
# constraints that the source table lacks.
181
xfer_check insert4-3.5 0 {1 9} \
182
{a int, b int NOT NULL} \
184
xfer_check insert4-3.6 0 {1 9} \
185
{a int, b int NOT NULL} \
186
{x int NOT NULL, y int}
187
xfer_check insert4-3.7 0 {1 9} \
188
{a int NOT NULL, b int NOT NULL} \
189
{x int NOT NULL, y int}
190
xfer_check insert4-3.8 0 {1 9} \
191
{a int NOT NULL, b int} \
195
# Do run the transfer optimization if the destination table and
196
# source table have the same NOT NULL constraints or if the
197
# source table has extra NOT NULL constraints.
199
xfer_check insert4-3.9 1 {1 9} \
201
{x int NOT NULL, y int}
202
xfer_check insert4-3.10 1 {1 9} \
204
{x int NOT NULL, y int NOT NULL}
205
xfer_check insert4-3.11 1 {1 9} \
206
{a int NOT NULL, b int} \
207
{x int NOT NULL, y int NOT NULL}
208
xfer_check insert4-3.12 1 {1 9} \
209
{a int, b int NOT NULL} \
210
{x int NOT NULL, y int NOT NULL}
212
# Do not run the optimization if any corresponding table
213
# columns have different affinities.
215
xfer_check insert4-3.20 0 {1 9} \
218
xfer_check insert4-3.21 0 {1 9} \
222
# "int" and "integer" are equivalent so the optimization should
225
xfer_check insert4-3.22 1 {1 9} \
232
do_test insert4-4.1 {
234
CREATE TABLE t4(a, b, UNIQUE(a,b));
235
INSERT INTO t4 VALUES(NULL,0);
236
INSERT INTO t4 VALUES(NULL,1);
237
INSERT INTO t4 VALUES(NULL,1);
added
removed
test/insert4.test
