third_party/sqlite/src/test/insert4.test - cobalt - Git at Google

 # 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}}

 finish_test
	# 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}}

	finish_test