Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Additional test cases added. Improvements to the INSERT transfer optimization. (CVS 3662) |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
2bf5475bde763f73f7f4dd9cac7d13a6 |
User & Date: | drh 2007-02-24 15:18:50.000 |
Context
2007-02-24
| ||
15:29 | Add comments to sqlite3ExprCompare() to clarify its operation. Ticket #2216. (CVS 3663) (check-in: fba0a1e508 user: drh tags: trunk) | |
15:18 | Additional test cases added. Improvements to the INSERT transfer optimization. (CVS 3662) (check-in: 2bf5475bde user: drh tags: trunk) | |
13:53 | Additional tests and some improvements to the INSERT transfer optimization. More testing is needed. (CVS 3661) (check-in: 8309858143 user: drh tags: trunk) | |
Changes
Changes to src/insert.c.
︙ | ︙ | |||
8 9 10 11 12 13 14 | ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* ** This file contains C code routines that are called by the parser ** to handle INSERT statements in SQLite. ** | | | 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* ** This file contains C code routines that are called by the parser ** to handle INSERT statements in SQLite. ** ** $Id: insert.c,v 1.176 2007/02/24 15:18:50 drh Exp $ */ #include "sqliteInt.h" /* ** Set P3 of the most recently inserted opcode to a column affinity ** string for index pIdx. A column affinity string has one character ** for each column in the table, according to the affinity of the column: |
︙ | ︙ | |||
1458 1459 1460 1461 1462 1463 1464 | for(pSrcIdx=pSrc->pIndex; pSrcIdx; pSrcIdx=pSrcIdx->pNext){ if( xferCompatibleIndex(pDestIdx, pSrcIdx) ) break; } if( pSrcIdx==0 ){ return 0; /* pDestIdx has no corresponding index in pSrc */ } } | | | 1458 1459 1460 1461 1462 1463 1464 1465 1466 1467 1468 1469 1470 1471 1472 | for(pSrcIdx=pSrc->pIndex; pSrcIdx; pSrcIdx=pSrcIdx->pNext){ if( xferCompatibleIndex(pDestIdx, pSrcIdx) ) break; } if( pSrcIdx==0 ){ return 0; /* pDestIdx has no corresponding index in pSrc */ } } if( pDest->pCheck && !sqlite3ExprCompare(pSrc->pCheck, pDest->pCheck) ){ return 0; /* Tables have different CHECK constraints. Ticket #2252 */ } /* If we get this far, it means either: ** ** * We can always do the transfer if the table contains an ** an integer primary key |
︙ | ︙ |
Changes to test/insert4.test.
1 2 3 4 5 6 7 8 9 10 11 12 13 | # 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. # | | > > > > > > > > > | | > < | | > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 | # 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.3 2007/02/24 15:18:50 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # 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 { 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 { |
︙ | ︙ | |||
101 102 103 104 105 106 107 108 109 110 111 | 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 # Do run the transfer optimization if tables have identical # CHECK constraints. # | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | > > | > > | > > > > > > > | > > > > > > | > > > > > | > > > > > > > > > | | > > > > > > > > > > > > > > > > | > > > > > > > > > | > > > | > > > | 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 | 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} finish_test |
Added test/tkt2251.test.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 | # 2007 Febuary 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. # # This file implements tests to verify that table column values # are pulled out of the database correctly. # # Long ago, the OP_Column opcode was sufficient to pull out the # value of a table column. But then we added the ALTER TABLE ADD COLUMN # feature. An added column might not actually exist in every row, # and so the OP_Column opcode has to contain a default value. Later # still we added a feature whereby a REAL value with no fractional # part is stored in the database file as an integer to save space. # After extracting the value, we have to call OP_RealAffinity to # convert it back to a REAL. # # The sqlite3ExprCodeGetColumn() routine was added to take care of # all of the complications above. The tests in this file attempt # to verify that sqlite3ExprCodeGetColumn() is used instead of a # raw OP_Column in all places where a table column is extracted from # the database. # # $Id: tkt2251.test,v 1.1 2007/02/24 15:18:51 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Create sample data. Verify that the default value and type of an added # column is correct for aggregates. do_test tkt2251-1.1 { execsql { CREATE TABLE t1(a INTEGER); INSERT INTO t1 VALUES(1); INSERT INTO t1 VALUES(1); INSERT INTO t1 VALUES(2); INSERT INTO t1 VALUES(9); INSERT INTO t1 VALUES(9); INSERT INTO t1 VALUES(9); INSERT INTO t1 VALUES(3); INSERT INTO t1 VALUES(2); ALTER TABLE t1 ADD COLUMN b REAL DEFAULT 4.0; SELECT avg(b), typeof(avg(b)) FROM t1; } } {4.0 real} do_test tkt2251-1.2 { execsql { SELECT sum(b), typeof(sum(b)) FROM t1; } } {32.0 real} do_test tkt2251-1.3 { execsql { SELECT a, sum(b), typeof(sum(b)) FROM t1 GROUP BY a ORDER BY a; } } {1 8.0 real 2 8.0 real 3 4.0 real 9 12.0 real} # Make sure that the REAL value comes out when values are accessed # by index. # do_test tkt2251-2.1 { execsql { SELECT b, typeof(b) FROM t1 WHERE a=3; } } {4.0 real} do_test tkt2251-2.2 { execsql { CREATE INDEX t1i1 ON t1(a,b); SELECT b, typeof(b) FROM t1 WHERE a=3; } } {4.0 real} do_test tkt2251-2.3 { execsql { REINDEX; SELECT b, typeof(b) FROM t1 WHERE a=3; } } {4.0 real} # Make sure the correct REAL value is used when copying from one # table to another. # do_test tkt2251-3.1 { execsql { CREATE TABLE t2(x,y); INSERT INTO t2 SELECT * FROM t1; SELECT y, typeof(y) FROM t2 WHERE x=3; } } {4.0 real} do_test tkt2251-3.2 { execsql { CREATE TABLE t3 AS SELECT * FROM t1; SELECT b, typeof(b) FROM t3 WHERE a=3; } } {4.0 real} finish_test |