Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Additional tests and some improvements to the INSERT transfer optimization. More testing is needed. (CVS 3661) |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
830985814345f71ba2def3c206e36aab |
| User & Date: | drh 2007-02-24 13:53:05 |
Context
|
2007-02-24
| ||
| 15:18 | Additional test cases added. Improvements to the INSERT transfer optimization. (CVS 3662) check-in: 2bf5475b user: drh tags: trunk | |
| 13:53 | Additional tests and some improvements to the INSERT transfer optimization. More testing is needed. (CVS 3661) check-in: 83098581 user: drh tags: trunk | |
| 13:23 | Make sure the INSERT xfer optimization does not trigger if the CHECK constraints on the two tables are not identical. Ticket #2252. (CVS 3660) check-in: 6fc18275 user: drh tags: trunk | |
Changes
Changes to src/expr.c.
8 8 ** May you find forgiveness for yourself and forgive others. 9 9 ** May you share freely, never taking more than you give. 10 10 ** 11 11 ************************************************************************* 12 12 ** This file contains routines used for analyzing expressions and 13 13 ** for generating VDBE code that evaluates expressions in SQLite. 14 14 ** 15 -** $Id: expr.c,v 1.278 2007/02/24 11:52:53 drh Exp $ 15 +** $Id: expr.c,v 1.279 2007/02/24 13:53:05 drh Exp $ 16 16 */ 17 17 #include "sqliteInt.h" 18 18 #include <ctype.h> 19 19 20 20 /* 21 21 ** Return the 'affinity' of the expression pExpr if any. 22 22 ** ................................................................................ 2206 2206 } 2207 2207 } 2208 2208 }else if( pB->pList ){ 2209 2209 return 0; 2210 2210 } 2211 2211 if( pA->pSelect || pB->pSelect ) return 0; 2212 2212 if( pA->iTable!=pB->iTable || pA->iColumn!=pB->iColumn ) return 0; 2213 - if( pA->token.z ){ 2213 + if( pA->op!=TK_COLUMN && pA->token.z ){ 2214 2214 if( pB->token.z==0 ) return 0; 2215 2215 if( pB->token.n!=pA->token.n ) return 0; 2216 2216 if( sqlite3StrNICmp((char*)pA->token.z,(char*)pB->token.z,pB->token.n)!=0 ){ 2217 2217 return 0; 2218 2218 } 2219 2219 } 2220 2220 return 1;
Changes to src/insert.c.
8 8 ** May you find forgiveness for yourself and forgive others. 9 9 ** May you share freely, never taking more than you give. 10 10 ** 11 11 ************************************************************************* 12 12 ** This file contains C code routines that are called by the parser 13 13 ** to handle INSERT statements in SQLite. 14 14 ** 15 -** $Id: insert.c,v 1.174 2007/02/24 13:23:52 drh Exp $ 15 +** $Id: insert.c,v 1.175 2007/02/24 13:53:05 drh Exp $ 16 16 */ 17 17 #include "sqliteInt.h" 18 18 19 19 /* 20 20 ** Set P3 of the most recently inserted opcode to a column affinity 21 21 ** string for index pIdx. A column affinity string has one character 22 22 ** for each column in the table, according to the affinity of the column: ................................................................................ 1293 1293 } 1294 1294 } 1295 1295 1296 1296 /* If no test above fails then the indices must be compatible */ 1297 1297 return 1; 1298 1298 } 1299 1299 1300 +#ifdef SQLITE_TEST 1301 +/* 1302 +** The following global variable is incremented whenever the 1303 +** transfer optimization is used. This is used for testing 1304 +** purposes only - to make sure the transfer optimization really 1305 +** is happening when it is suppose to. 1306 +*/ 1307 +int sqlite3_xferopt_count; 1308 +#endif /* SQLITE_TEST */ 1309 + 1300 1310 /* 1301 1311 ** Attempt the transfer optimization on INSERTs of the form 1302 1312 ** 1303 1313 ** INSERT INTO tab1 SELECT * FROM tab2; 1304 1314 ** 1305 1315 ** This optimization is only attempted if 1306 1316 ** ................................................................................ 1460 1470 ** 1461 1471 ** * We can always do the transfer if the table contains an 1462 1472 ** an integer primary key 1463 1473 ** 1464 1474 ** * We can conditionally do the transfer if the destination 1465 1475 ** table is empty. 1466 1476 */ 1477 +#ifdef SQLITE_TEST 1478 + sqlite3_xferopt_count++; 1479 +#endif 1467 1480 iDbSrc = sqlite3SchemaToIndex(pParse->db, pSrc->pSchema); 1468 1481 v = sqlite3GetVdbe(pParse); 1469 1482 iSrc = pParse->nTab++; 1470 1483 iDest = pParse->nTab++; 1471 1484 counterMem = autoIncBegin(pParse, iDbDest, pDest); 1472 1485 sqlite3OpenTable(pParse, iDest, iDbDest, pDest, OP_OpenWrite); 1473 1486 if( pDest->iPKey<0 ){
Changes to src/test1.c.
9 9 ** May you share freely, never taking more than you give. 10 10 ** 11 11 ************************************************************************* 12 12 ** Code for testing all sorts of SQLite interfaces. This code 13 13 ** is not included in the SQLite library. It is used for automated 14 14 ** testing of the SQLite library. 15 15 ** 16 -** $Id: test1.c,v 1.228 2007/02/05 14:21:48 danielk1977 Exp $ 16 +** $Id: test1.c,v 1.229 2007/02/24 13:53:05 drh Exp $ 17 17 */ 18 18 #include "sqliteInt.h" 19 19 #include "tcl.h" 20 20 #include "os.h" 21 21 #include <stdlib.h> 22 22 #include <string.h> 23 23 ................................................................................ 4220 4220 extern int sqlite3_sync_count, sqlite3_fullsync_count; 4221 4221 extern int sqlite3_opentemp_count; 4222 4222 extern int sqlite3_memUsed; 4223 4223 extern int sqlite3_malloc_id; 4224 4224 extern int sqlite3_memMax; 4225 4225 extern int sqlite3_like_count; 4226 4226 extern int sqlite3_tsd_count; 4227 + extern int sqlite3_xferopt_count; 4227 4228 #if OS_UNIX && defined(SQLITE_TEST) && defined(THREADSAFE) && THREADSAFE 4228 4229 extern int threadsOverrideEachOthersLocks; 4229 4230 #endif 4230 4231 #if OS_WIN 4231 4232 extern int sqlite3_os_type; 4232 4233 #endif 4233 4234 #ifdef SQLITE_DEBUG ................................................................................ 4257 4258 (char*)&sqlite3_open_file_count, TCL_LINK_INT); 4258 4259 Tcl_LinkVar(interp, "sqlite_current_time", 4259 4260 (char*)&sqlite3_current_time, TCL_LINK_INT); 4260 4261 Tcl_LinkVar(interp, "sqlite_os_trace", 4261 4262 (char*)&sqlite3_os_trace, TCL_LINK_INT); 4262 4263 Tcl_LinkVar(interp, "sqlite3_tsd_count", 4263 4264 (char*)&sqlite3_tsd_count, TCL_LINK_INT); 4265 + Tcl_LinkVar(interp, "sqlite3_xferopt_count", 4266 + (char*)&sqlite3_xferopt_count, TCL_LINK_INT); 4264 4267 #ifndef SQLITE_OMIT_UTF16 4265 4268 Tcl_LinkVar(interp, "unaligned_string_counter", 4266 4269 (char*)&unaligned_string_counter, TCL_LINK_INT); 4267 4270 #endif 4268 4271 #if OS_UNIX && defined(SQLITE_TEST) && defined(THREADSAFE) && THREADSAFE 4269 4272 Tcl_LinkVar(interp, "threadsOverrideEachOthersLocks", 4270 4273 (char*)&threadsOverrideEachOthersLocks, TCL_LINK_INT);
Changes to test/insert4.test.
5 5 # 6 6 # May you do good and not evil. 7 7 # May you find forgiveness for yourself and forgive others. 8 8 # May you share freely, never taking more than you give. 9 9 # 10 10 #*********************************************************************** 11 11 # This file implements regression tests for SQLite library. The 12 -# focus of this file is testing corner cases of the INSERT statement. 12 +# focus of this file is testing the INSERT transfer optimization. 13 13 # 14 -# $Id: insert4.test,v 1.1 2007/02/24 13:23:53 drh Exp $ 14 +# $Id: insert4.test,v 1.2 2007/02/24 13:53:05 drh Exp $ 15 15 16 16 set testdir [file dirname $argv0] 17 17 source $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 +# 25 +proc xferopt_test {testname N} { 26 + do_test $testname {set ::sqlite3_xferopt_count} $N 27 +} 18 28 19 29 # Ticket #2252. Make sure the an INSERT from identical tables 20 30 # does not violate constraints. 21 31 # 22 32 do_test insert4-1.1 { 33 + set sqlite3_xferopt_count 0 23 34 execsql { 24 35 CREATE TABLE t1(a int, b int, check(b>a)); 25 36 CREATE TABLE t2(x int, y int); 26 37 INSERT INTO t2 VALUES(9,1); 27 38 } 28 39 catchsql { 29 40 INSERT INTO t1 SELECT * FROM t2; 30 41 } 31 42 } {1 {constraint failed}} 32 -do_test insert4-1.2 { 43 +xferopt_test insert4-1.2 0 44 +do_test insert4-1.3 { 33 45 execsql { 34 46 SELECT * FROM t1; 35 47 } 36 48 } {} 37 49 38 -# Other coverage tests for the INSERT transfer optimization. 50 +# Tests to make sure that the transfer optimization is not occurring 51 +# when it is not a valid optimization. 39 52 # 40 -do_test insert4-2.1 { 53 +# The SELECT must be against a real table. 54 +do_test insert4-2.1.1 { 41 55 execsql { 42 56 INSERT INTO t1 SELECT 4, 8; 43 57 SELECT * FROM t1; 44 58 } 45 59 } {4 8} 60 +xferopt_test insert4-2.1.2 0 46 61 do_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}} 69 +xferopt_test insert4-2.2.2 0 70 + 71 +# Do not run the transfer optimization if there is a LIMIT clause 72 +# 73 +do_test insert4-2.3.1 { 47 74 execsql { 48 75 CREATE TABLE t3(a int, b int); 49 76 INSERT INTO t2 SELECT y, x FROM t2; 50 77 INSERT INTO t3 SELECT * FROM t2 LIMIT 1; 51 78 SELECT * FROM t3; 52 79 } 53 80 } {9 1} 54 -do_test insert4-2.2.2 { 81 +xferopt_test insert4-2.3.2 0 82 +do_test insert4-2.3.3 { 55 83 catchsql { 56 84 DELETE FROM t1; 57 85 INSERT INTO t1 SELECT * FROM t2 LIMIT 1; 58 86 SELECT * FROM t1; 59 87 } 60 88 } {1 {constraint failed}} 61 -do_test insert4-2.3.1 { 89 +xferopt_test insert4-2.3.4 0 90 + 91 +# Do not run the transfer optimization if there is a DISTINCT 92 +# 93 +do_test insert4-2.4.1 { 62 94 execsql { 63 95 DELETE FROM t3; 64 96 INSERT INTO t3 SELECT DISTINCT * FROM t2; 65 97 SELECT * FROM t3; 66 98 } 67 99 } {9 1 1 9} 68 -do_test insert4-2.3.2 { 100 +xferopt_test insert4-2.4.2 0 101 +do_test insert4-2.4.3 { 69 102 catchsql { 70 103 DELETE FROM t1; 71 104 INSERT INTO t1 SELECT DISTINCT * FROM t2; 72 105 } 73 106 } {1 {constraint failed}} 107 +xferopt_test insert4-2.4.4 0 108 + 109 +# Do run the transfer optimization if tables have identical 110 +# CHECK constraints. 111 +# 112 +do_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} 122 +xferopt_test insert4-3.1.2 1 74 123 75 124 finish_test