/trunk/test/insert4.test |
@@ -0,0 +1,389 @@ |
# 2007 January 24 |
# |
# 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 INSERT transfer optimization. |
# |
# $Id: insert4.test,v 1.10 2008/01/21 16:22:46 drh Exp $ |
|
set testdir [file dirname $argv0] |
source $testdir/tester.tcl |
|
ifcapable !view||!subquery { |
finish_test |
return |
} |
|
# The sqlite3_xferopt_count variable is incremented whenever the |
# insert transfer optimization applies. |
# |
# This procedure runs a test to see if the sqlite3_xferopt_count is |
# set to N. |
# |
proc xferopt_test {testname N} { |
do_test $testname {set ::sqlite3_xferopt_count} $N |
} |
|
# Create tables used for testing. |
# |
execsql { |
PRAGMA legacy_file_format = 0; |
CREATE TABLE t1(a int, b int, check(b>a)); |
CREATE TABLE t2(x int, y int); |
CREATE VIEW v2 AS SELECT y, x FROM t2; |
CREATE TABLE t3(a int, b int); |
} |
|
# Ticket #2252. Make sure the an INSERT from identical tables |
# does not violate constraints. |
# |
do_test insert4-1.1 { |
set sqlite3_xferopt_count 0 |
execsql { |
DELETE FROM t1; |
DELETE FROM t2; |
INSERT INTO t2 VALUES(9,1); |
} |
catchsql { |
INSERT INTO t1 SELECT * FROM t2; |
} |
} {1 {constraint failed}} |
xferopt_test insert4-1.2 0 |
do_test insert4-1.3 { |
execsql { |
SELECT * FROM t1; |
} |
} {} |
|
# Tests to make sure that the transfer optimization is not occurring |
# when it is not a valid optimization. |
# |
# The SELECT must be against a real table. |
do_test insert4-2.1.1 { |
execsql { |
DELETE FROM t1; |
INSERT INTO t1 SELECT 4, 8; |
SELECT * FROM t1; |
} |
} {4 8} |
xferopt_test insert4-2.1.2 0 |
do_test insert4-2.2.1 { |
catchsql { |
DELETE FROM t1; |
INSERT INTO t1 SELECT * FROM v2; |
SELECT * FROM t1; |
} |
} {0 {1 9}} |
xferopt_test insert4-2.2.2 0 |
|
# Do not run the transfer optimization if there is a LIMIT clause |
# |
do_test insert4-2.3.1 { |
execsql { |
DELETE FROM t2; |
INSERT INTO t2 VALUES(9,1); |
INSERT INTO t2 SELECT y, x FROM t2; |
INSERT INTO t3 SELECT * FROM t2 LIMIT 1; |
SELECT * FROM t3; |
} |
} {9 1} |
xferopt_test insert4-2.3.2 0 |
do_test insert4-2.3.3 { |
catchsql { |
DELETE FROM t1; |
INSERT INTO t1 SELECT * FROM t2 LIMIT 1; |
SELECT * FROM t1; |
} |
} {1 {constraint failed}} |
xferopt_test insert4-2.3.4 0 |
|
# Do not run the transfer optimization if there is a DISTINCT |
# |
do_test insert4-2.4.1 { |
execsql { |
DELETE FROM t3; |
INSERT INTO t3 SELECT DISTINCT * FROM t2; |
SELECT * FROM t3; |
} |
} {1 9 9 1} |
xferopt_test insert4-2.4.2 0 |
do_test insert4-2.4.3 { |
catchsql { |
DELETE FROM t1; |
INSERT INTO t1 SELECT DISTINCT * FROM t2; |
} |
} {1 {constraint failed}} |
xferopt_test insert4-2.4.4 0 |
|
# The following procedure constructs two tables then tries to transfer |
# data from one table to the other. Checks are made to make sure the |
# transfer is successful and that the transfer optimization was used or |
# not, as appropriate. |
# |
# xfer_check TESTID XFER-USED INIT-DATA DEST-SCHEMA SRC-SCHEMA |
# |
# The TESTID argument is the symbolic name for this test. The XFER-USED |
# argument is true if the transfer optimization should be employed and |
# false if not. INIT-DATA is a single row of data that is to be |
# transfered. DEST-SCHEMA and SRC-SCHEMA are table declarations for |
# the destination and source tables. |
# |
proc xfer_check {testid xferused initdata destschema srcschema} { |
execsql "CREATE TABLE dest($destschema)" |
execsql "CREATE TABLE src($srcschema)" |
execsql "INSERT INTO src VALUES([join $initdata ,])" |
set ::sqlite3_xferopt_count 0 |
do_test $testid.1 { |
execsql { |
INSERT INTO dest SELECT * FROM src; |
SELECT * FROM dest; |
} |
} $initdata |
do_test $testid.2 { |
set ::sqlite3_xferopt_count |
} $xferused |
execsql { |
DROP TABLE dest; |
DROP TABLE src; |
} |
} |
|
|
# Do run the transfer optimization if tables have identical |
# CHECK constraints. |
# |
xfer_check insert4-3.1 1 {1 9} \ |
{a int, b int CHECK(b>a)} \ |
{x int, y int CHECK(y>x)} |
xfer_check insert4-3.2 1 {1 9} \ |
{a int, b int CHECK(b>a)} \ |
{x int CHECK(y>x), y int} |
|
# Do run the transfer optimization if the destination table lacks |
# any CHECK constraints regardless of whether or not there are CHECK |
# constraints on the source table. |
# |
xfer_check insert4-3.3 1 {1 9} \ |
{a int, b int} \ |
{x int, y int CHECK(y>x)} |
|
# Do run the transfer optimization if the destination table omits |
# NOT NULL constraints that the source table has. |
# |
xfer_check insert4-3.4 0 {1 9} \ |
{a int, b int CHECK(b>a)} \ |
{x int, y int} |
|
# Do not run the optimization if the destination has NOT NULL |
# constraints that the source table lacks. |
# |
xfer_check insert4-3.5 0 {1 9} \ |
{a int, b int NOT NULL} \ |
{x int, y int} |
xfer_check insert4-3.6 0 {1 9} \ |
{a int, b int NOT NULL} \ |
{x int NOT NULL, y int} |
xfer_check insert4-3.7 0 {1 9} \ |
{a int NOT NULL, b int NOT NULL} \ |
{x int NOT NULL, y int} |
xfer_check insert4-3.8 0 {1 9} \ |
{a int NOT NULL, b int} \ |
{x int, y int} |
|
|
# Do run the transfer optimization if the destination table and |
# source table have the same NOT NULL constraints or if the |
# source table has extra NOT NULL constraints. |
# |
xfer_check insert4-3.9 1 {1 9} \ |
{a int, b int} \ |
{x int NOT NULL, y int} |
xfer_check insert4-3.10 1 {1 9} \ |
{a int, b int} \ |
{x int NOT NULL, y int NOT NULL} |
xfer_check insert4-3.11 1 {1 9} \ |
{a int NOT NULL, b int} \ |
{x int NOT NULL, y int NOT NULL} |
xfer_check insert4-3.12 1 {1 9} \ |
{a int, b int NOT NULL} \ |
{x int NOT NULL, y int NOT NULL} |
|
# Do not run the optimization if any corresponding table |
# columns have different affinities. |
# |
xfer_check insert4-3.20 0 {1 9} \ |
{a text, b int} \ |
{x int, b int} |
xfer_check insert4-3.21 0 {1 9} \ |
{a int, b int} \ |
{x text, b int} |
|
# "int" and "integer" are equivalent so the optimization should |
# run here. |
# |
xfer_check insert4-3.22 1 {1 9} \ |
{a int, b int} \ |
{x integer, b int} |
|
# Ticket #2291. |
# |
|
do_test insert4-4.1a { |
execsql {CREATE TABLE t4(a, b, UNIQUE(a,b))} |
} {} |
ifcapable vacuum { |
do_test insert4-4.1b { |
execsql { |
INSERT INTO t4 VALUES(NULL,0); |
INSERT INTO t4 VALUES(NULL,1); |
INSERT INTO t4 VALUES(NULL,1); |
VACUUM; |
} |
} {} |
} |
|
# Check some error conditions: |
# |
do_test insert4-5.1 { |
# Table does not exist. |
catchsql { INSERT INTO t2 SELECT * FROM nosuchtable } |
} {1 {no such table: nosuchtable}} |
do_test insert4-5.2 { |
# Number of columns does not match. |
catchsql { |
CREATE TABLE t5(a, b, c); |
INSERT INTO t4 SELECT * FROM t5; |
} |
} {1 {table t4 has 2 columns but 3 values were supplied}} |
|
do_test insert4-6.1 { |
set ::sqlite3_xferopt_count 0 |
execsql { |
CREATE INDEX t2_i2 ON t2(x, y COLLATE nocase); |
CREATE INDEX t2_i1 ON t2(x ASC, y DESC); |
CREATE INDEX t3_i1 ON t3(a, b); |
INSERT INTO t2 SELECT * FROM t3; |
} |
set ::sqlite3_xferopt_count |
} {0} |
do_test insert4-6.2 { |
set ::sqlite3_xferopt_count 0 |
execsql { |
DROP INDEX t2_i2; |
INSERT INTO t2 SELECT * FROM t3; |
} |
set ::sqlite3_xferopt_count |
} {0} |
do_test insert4-6.3 { |
set ::sqlite3_xferopt_count 0 |
execsql { |
DROP INDEX t2_i1; |
CREATE INDEX t2_i1 ON t2(x ASC, y ASC); |
INSERT INTO t2 SELECT * FROM t3; |
} |
set ::sqlite3_xferopt_count |
} {1} |
do_test insert4-6.4 { |
set ::sqlite3_xferopt_count 0 |
execsql { |
DROP INDEX t2_i1; |
CREATE INDEX t2_i1 ON t2(x ASC, y COLLATE RTRIM); |
INSERT INTO t2 SELECT * FROM t3; |
} |
set ::sqlite3_xferopt_count |
} {0} |
|
|
do_test insert4-6.5 { |
execsql { |
CREATE TABLE t6a(x CHECK( x<>'abc' )); |
INSERT INTO t6a VALUES('ABC'); |
SELECT * FROM t6a; |
} |
} {ABC} |
do_test insert4-6.6 { |
execsql { |
CREATE TABLE t6b(x CHECK( x<>'abc' COLLATE nocase )); |
} |
catchsql { |
INSERT INTO t6b SELECT * FROM t6a; |
} |
} {1 {constraint failed}} |
do_test insert4-6.7 { |
execsql { |
DROP TABLE t6b; |
CREATE TABLE t6b(x CHECK( x COLLATE nocase <>'abc' )); |
} |
catchsql { |
INSERT INTO t6b SELECT * FROM t6a; |
} |
} {1 {constraint failed}} |
|
# Ticket [6284df89debdfa61db8073e062908af0c9b6118e] |
# Disable the xfer optimization if the destination table contains |
# a foreign key constraint |
# |
ifcapable foreignkey { |
do_test insert4-7.1 { |
set ::sqlite3_xferopt_count 0 |
execsql { |
CREATE TABLE t7a(x INTEGER PRIMARY KEY); INSERT INTO t7a VALUES(123); |
CREATE TABLE t7b(y INTEGER REFERENCES t7a); |
CREATE TABLE t7c(z INT); INSERT INTO t7c VALUES(234); |
INSERT INTO t7b SELECT * FROM t7c; |
SELECT * FROM t7b; |
} |
} {234} |
do_test insert4-7.2 { |
set ::sqlite3_xferopt_count |
} {1} |
do_test insert4-7.3 { |
set ::sqlite3_xferopt_count 0 |
execsql { |
DELETE FROM t7b; |
PRAGMA foreign_keys=ON; |
} |
catchsql { |
INSERT INTO t7b SELECT * FROM t7c; |
} |
} {1 {foreign key constraint failed}} |
do_test insert4-7.4 { |
execsql {SELECT * FROM t7b} |
} {} |
do_test insert4-7.5 { |
set ::sqlite3_xferopt_count |
} {0} |
do_test insert4-7.6 { |
set ::sqlite3_xferopt_count 0 |
execsql { |
DELETE FROM t7b; DELETE FROM t7c; |
INSERT INTO t7c VALUES(123); |
INSERT INTO t7b SELECT * FROM t7c; |
SELECT * FROM t7b; |
} |
} {123} |
do_test insert4-7.7 { |
set ::sqlite3_xferopt_count |
} {0} |
do_test insert4-7.7 { |
set ::sqlite3_xferopt_count 0 |
execsql { |
PRAGMA foreign_keys=OFF; |
DELETE FROM t7b; |
INSERT INTO t7b SELECT * FROM t7c; |
SELECT * FROM t7b; |
} |
} {123} |
do_test insert4-7.8 { |
set ::sqlite3_xferopt_count |
} {1} |
} |
|
finish_test |