Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
| Comment: | Additional test cases added. Improvements to the INSERT transfer optimization. (CVS 3662) |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
2bf5475bde763f73f7f4dd9cac7d13a6 |
| User & Date: | drh 2007-02-24 15:18:50 |
|
2007-02-24
| ||
| 15:29 | Add comments to sqlite3ExprCompare() to clarify its operation. Ticket #2216. (CVS 3663) (check-in: fba0a1e5 user: drh tags: trunk) | |
| 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) | |
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.175 2007/02/24 13:53:05 drh Exp $ 15 +** $Id: insert.c,v 1.176 2007/02/24 15:18:50 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: ................................................................................ 1458 1458 for(pSrcIdx=pSrc->pIndex; pSrcIdx; pSrcIdx=pSrcIdx->pNext){ 1459 1459 if( xferCompatibleIndex(pDestIdx, pSrcIdx) ) break; 1460 1460 } 1461 1461 if( pSrcIdx==0 ){ 1462 1462 return 0; /* pDestIdx has no corresponding index in pSrc */ 1463 1463 } 1464 1464 } 1465 - if( !sqlite3ExprCompare(pSrc->pCheck, pDest->pCheck) ){ 1465 + if( pDest->pCheck && !sqlite3ExprCompare(pSrc->pCheck, pDest->pCheck) ){ 1466 1466 return 0; /* Tables have different CHECK constraints. Ticket #2252 */ 1467 1467 } 1468 1468 1469 1469 /* If we get this far, it means either: 1470 1470 ** 1471 1471 ** * We can always do the transfer if the table contains an 1472 1472 ** an integer primary key
Changes to test/insert4.test.
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 12 # focus of this file is testing the INSERT transfer optimization. 13 13 # 14 -# $Id: insert4.test,v 1.2 2007/02/24 13:53:05 drh Exp $ 14 +# $Id: insert4.test,v 1.3 2007/02/24 15:18:50 drh Exp $ 15 15 16 16 set testdir [file dirname $argv0] 17 17 source $testdir/tester.tcl 18 18 19 19 # The sqlite3_xferopt_count variable is incremented whenever the 20 20 # insert transfer optimization applies. 21 21 # 22 22 # This procedure runs a test to see if the sqlite3_xferopt_count is 23 23 # set to N. 24 24 # 25 25 proc xferopt_test {testname N} { 26 26 do_test $testname {set ::sqlite3_xferopt_count} $N 27 27 } 28 + 29 +# Create tables used for testing. 30 +# 31 +execsql { 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); 36 +} 28 37 29 38 # Ticket #2252. Make sure the an INSERT from identical tables 30 39 # does not violate constraints. 31 40 # 32 41 do_test insert4-1.1 { 33 42 set sqlite3_xferopt_count 0 34 43 execsql { 35 - CREATE TABLE t1(a int, b int, check(b>a)); 36 - CREATE TABLE t2(x int, y int); 44 + DELETE FROM t1; 45 + DELETE FROM t2; 37 46 INSERT INTO t2 VALUES(9,1); 38 47 } 39 48 catchsql { 40 49 INSERT INTO t1 SELECT * FROM t2; 41 50 } 42 51 } {1 {constraint failed}} 43 52 xferopt_test insert4-1.2 0 ................................................................................ 49 58 50 59 # Tests to make sure that the transfer optimization is not occurring 51 60 # when it is not a valid optimization. 52 61 # 53 62 # The SELECT must be against a real table. 54 63 do_test insert4-2.1.1 { 55 64 execsql { 65 + DELETE FROM t1; 56 66 INSERT INTO t1 SELECT 4, 8; 57 67 SELECT * FROM t1; 58 68 } 59 69 } {4 8} 60 70 xferopt_test insert4-2.1.2 0 61 71 do_test insert4-2.2.1 { 62 72 catchsql { 63 73 DELETE FROM t1; 64 - CREATE VIEW v1 AS SELECT y, x FROM t2; 65 - INSERT INTO t1 SELECT * FROM v1; 74 + INSERT INTO t1 SELECT * FROM v2; 66 75 SELECT * FROM t1; 67 76 } 68 77 } {0 {1 9}} 69 78 xferopt_test insert4-2.2.2 0 70 79 71 80 # Do not run the transfer optimization if there is a LIMIT clause 72 81 # 73 82 do_test insert4-2.3.1 { 74 83 execsql { 75 - CREATE TABLE t3(a int, b int); 84 + DELETE FROM t2; 85 + INSERT INTO t2 VALUES(9,1); 76 86 INSERT INTO t2 SELECT y, x FROM t2; 77 87 INSERT INTO t3 SELECT * FROM t2 LIMIT 1; 78 88 SELECT * FROM t3; 79 89 } 80 90 } {9 1} 81 91 xferopt_test insert4-2.3.2 0 82 92 do_test insert4-2.3.3 { ................................................................................ 101 111 do_test insert4-2.4.3 { 102 112 catchsql { 103 113 DELETE FROM t1; 104 114 INSERT INTO t1 SELECT DISTINCT * FROM t2; 105 115 } 106 116 } {1 {constraint failed}} 107 117 xferopt_test insert4-2.4.4 0 118 + 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. 123 +# 124 +# xfer_check TESTID XFER-USED INIT-DATA DEST-SCHEMA SRC-SCHEMA 125 +# 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. 131 +# 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 137 + do_test $testid.1 { 138 + execsql { 139 + INSERT INTO dest SELECT * FROM src; 140 + SELECT * FROM dest; 141 + } 142 + } $initdata 143 + do_test $testid.2 { 144 + set ::sqlite3_xferopt_count 145 + } $xferused 146 + execsql { 147 + DROP TABLE dest; 148 + DROP TABLE src; 149 + } 150 +} 151 + 108 152 109 153 # Do run the transfer optimization if tables have identical 110 154 # CHECK constraints. 111 155 # 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 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} 162 + 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. 166 +# 167 +xfer_check insert4-3.3 1 {1 9} \ 168 + {a int, b int} \ 169 + {x int, y int CHECK(y>x)} 170 + 171 +# Do run the transfer optimization if the destination table omits 172 +# NOT NULL constraints that the source table has. 173 +# 174 +xfer_check insert4-3.4 0 {1 9} \ 175 + {a int, b int CHECK(b>a)} \ 176 + {x int, y int} 177 + 178 +# Do not run the optimization if the destination has NOT NULL 179 +# constraints that the source table lacks. 180 +# 181 +xfer_check insert4-3.5 0 {1 9} \ 182 + {a int, b int NOT NULL} \ 183 + {x int, y int} 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} \ 192 + {x int, y int} 193 + 194 + 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. 198 +# 199 +xfer_check insert4-3.9 1 {1 9} \ 200 + {a int, b int} \ 201 + {x int NOT NULL, y int} 202 +xfer_check insert4-3.10 1 {1 9} \ 203 + {a int, b int} \ 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} 211 + 212 +# Do not run the optimization if any corresponding table 213 +# columns have different affinities. 214 +# 215 +xfer_check insert4-3.20 0 {1 9} \ 216 + {a text, b int} \ 217 + {x int, b int} 218 +xfer_check insert4-3.21 0 {1 9} \ 219 + {a int, b int} \ 220 + {x text, b int} 221 + 222 +# "int" and "integer" are equivalent so the optimization should 223 +# run here. 224 +# 225 +xfer_check insert4-3.22 1 {1 9} \ 226 + {a int, b int} \ 227 + {x integer, b int} 228 + 123 229 124 230 finish_test
Added test/tkt2251.test.
1 +# 2007 Febuary 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. 12 +# 13 +# This file implements tests to verify that table column values 14 +# are pulled out of the database correctly. 15 +# 16 +# Long ago, the OP_Column opcode was sufficient to pull out the 17 +# value of a table column. But then we added the ALTER TABLE ADD COLUMN 18 +# feature. An added column might not actually exist in every row, 19 +# and so the OP_Column opcode has to contain a default value. Later 20 +# still we added a feature whereby a REAL value with no fractional 21 +# part is stored in the database file as an integer to save space. 22 +# After extracting the value, we have to call OP_RealAffinity to 23 +# convert it back to a REAL. 24 +# 25 +# The sqlite3ExprCodeGetColumn() routine was added to take care of 26 +# all of the complications above. The tests in this file attempt 27 +# to verify that sqlite3ExprCodeGetColumn() is used instead of a 28 +# raw OP_Column in all places where a table column is extracted from 29 +# the database. 30 +# 31 +# $Id: tkt2251.test,v 1.1 2007/02/24 15:18:51 drh Exp $ 32 + 33 +set testdir [file dirname $argv0] 34 +source $testdir/tester.tcl 35 + 36 +# Create sample data. Verify that the default value and type of an added 37 +# column is correct for aggregates. 38 +do_test tkt2251-1.1 { 39 + execsql { 40 + CREATE TABLE t1(a INTEGER); 41 + INSERT INTO t1 VALUES(1); 42 + INSERT INTO t1 VALUES(1); 43 + INSERT INTO t1 VALUES(2); 44 + INSERT INTO t1 VALUES(9); 45 + INSERT INTO t1 VALUES(9); 46 + INSERT INTO t1 VALUES(9); 47 + INSERT INTO t1 VALUES(3); 48 + INSERT INTO t1 VALUES(2); 49 + ALTER TABLE t1 ADD COLUMN b REAL DEFAULT 4.0; 50 + SELECT avg(b), typeof(avg(b)) FROM t1; 51 + } 52 +} {4.0 real} 53 +do_test tkt2251-1.2 { 54 + execsql { 55 + SELECT sum(b), typeof(sum(b)) FROM t1; 56 + } 57 +} {32.0 real} 58 +do_test tkt2251-1.3 { 59 + execsql { 60 + SELECT a, sum(b), typeof(sum(b)) FROM t1 GROUP BY a ORDER BY a; 61 + } 62 +} {1 8.0 real 2 8.0 real 3 4.0 real 9 12.0 real} 63 + 64 +# Make sure that the REAL value comes out when values are accessed 65 +# by index. 66 +# 67 +do_test tkt2251-2.1 { 68 + execsql { 69 + SELECT b, typeof(b) FROM t1 WHERE a=3; 70 + } 71 +} {4.0 real} 72 +do_test tkt2251-2.2 { 73 + execsql { 74 + CREATE INDEX t1i1 ON t1(a,b); 75 + SELECT b, typeof(b) FROM t1 WHERE a=3; 76 + } 77 +} {4.0 real} 78 +do_test tkt2251-2.3 { 79 + execsql { 80 + REINDEX; 81 + SELECT b, typeof(b) FROM t1 WHERE a=3; 82 + } 83 +} {4.0 real} 84 + 85 +# Make sure the correct REAL value is used when copying from one 86 +# table to another. 87 +# 88 +do_test tkt2251-3.1 { 89 + execsql { 90 + CREATE TABLE t2(x,y); 91 + INSERT INTO t2 SELECT * FROM t1; 92 + SELECT y, typeof(y) FROM t2 WHERE x=3; 93 + } 94 +} {4.0 real} 95 +do_test tkt2251-3.2 { 96 + execsql { 97 + CREATE TABLE t3 AS SELECT * FROM t1; 98 + SELECT b, typeof(b) FROM t3 WHERE a=3; 99 + } 100 +} {4.0 real} 101 + 102 + 103 +finish_test