Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Make sure the INSERT xfer optimization does not trigger if the CHECK constraints on the two tables are not identical. Ticket #2252. (CVS 3660) |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
6fc18275230563437f2985eac3795e4d |
User & Date: | drh 2007-02-24 13:23:52.000 |
Context
2007-02-24
| ||
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) | |
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: 6fc1827523 user: drh tags: trunk) | |
11:52 | Make sure that integer values are converted to real when pulled from a REAL table column by GROUP BY. Ticket #2251. Also make sure default values are correctly expanded. There may be other places in the code where this issue comes up - we need to look further. (CVS 3659) (check-in: e11bbf174c 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.174 2007/02/24 13:23:52 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: |
︙ | ︙ | |||
1301 1302 1303 1304 1305 1306 1307 | ** Attempt the transfer optimization on INSERTs of the form ** ** INSERT INTO tab1 SELECT * FROM tab2; ** ** This optimization is only attempted if ** ** (1) tab1 and tab2 have identical schemas including all the | | | 1301 1302 1303 1304 1305 1306 1307 1308 1309 1310 1311 1312 1313 1314 1315 | ** Attempt the transfer optimization on INSERTs of the form ** ** INSERT INTO tab1 SELECT * FROM tab2; ** ** This optimization is only attempted if ** ** (1) tab1 and tab2 have identical schemas including all the ** same indices and constraints ** ** (2) tab1 and tab2 are different tables ** ** (3) There must be no triggers on tab1 ** ** (4) The result set of the SELECT statement is "*" ** |
︙ | ︙ | |||
1378 1379 1380 1381 1382 1383 1384 | } if( pSelect->pWhere ){ return 0; /* SELECT may not have a WHERE clause */ } if( pSelect->pOrderBy ){ return 0; /* SELECT may not have an ORDER BY clause */ } | < | < > | < < | 1378 1379 1380 1381 1382 1383 1384 1385 1386 1387 1388 1389 1390 1391 1392 1393 1394 1395 1396 1397 1398 1399 1400 | } if( pSelect->pWhere ){ return 0; /* SELECT may not have a WHERE clause */ } if( pSelect->pOrderBy ){ return 0; /* SELECT may not have an ORDER BY clause */ } /* Do not need to test for a HAVING clause. If HAVING is present but ** there is no ORDER BY, we will get an error. */ if( pSelect->pGroupBy ){ return 0; /* SELECT may not have a GROUP BY clause */ } if( pSelect->pLimit ){ return 0; /* SELECT may not have a LIMIT clause */ } assert( pSelect->pOffset==0 ); /* Must be so if pLimit==0 */ if( pSelect->pPrior ){ return 0; /* SELECT may not be a compound query */ } if( pSelect->isDistinct ){ return 0; /* SELECT may not be DISTINCT */ } pEList = pSelect->pEList; |
︙ | ︙ | |||
1451 1452 1453 1454 1455 1456 1457 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 */ } } /* If we get this far, it means either: ** ** * We can always do the transfer if the table contains an ** an integer primary key ** ** * We can conditionally do the transfer if the destination | > > > | 1448 1449 1450 1451 1452 1453 1454 1455 1456 1457 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 */ } } if( !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 ** ** * We can conditionally do the transfer if the destination |
︙ | ︙ |
Changes to src/select.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 SELECT 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 SELECT statements in SQLite. ** ** $Id: select.c,v 1.329 2007/02/24 13:23:53 drh Exp $ */ #include "sqliteInt.h" /* ** Delete all the content of a Select structure but do not deallocate ** the select structure itself. |
︙ | ︙ | |||
64 65 66 67 68 69 70 71 72 73 74 75 76 77 | pNew->pSrc = pSrc; pNew->pWhere = pWhere; pNew->pGroupBy = pGroupBy; pNew->pHaving = pHaving; pNew->pOrderBy = pOrderBy; pNew->isDistinct = isDistinct; pNew->op = TK_SELECT; pNew->pLimit = pLimit; pNew->pOffset = pOffset; pNew->iLimit = -1; pNew->iOffset = -1; pNew->addrOpenEphm[0] = -1; pNew->addrOpenEphm[1] = -1; pNew->addrOpenEphm[2] = -1; | > | 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 | pNew->pSrc = pSrc; pNew->pWhere = pWhere; pNew->pGroupBy = pGroupBy; pNew->pHaving = pHaving; pNew->pOrderBy = pOrderBy; pNew->isDistinct = isDistinct; pNew->op = TK_SELECT; assert( pOffset==0 || pLimit!=0 ); pNew->pLimit = pLimit; pNew->pOffset = pOffset; pNew->iLimit = -1; pNew->iOffset = -1; pNew->addrOpenEphm[0] = -1; pNew->addrOpenEphm[1] = -1; pNew->addrOpenEphm[2] = -1; |
︙ | ︙ |
Added test/insert4.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 | # 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 corner cases of the INSERT statement. # # $Id: insert4.test,v 1.1 2007/02/24 13:23:53 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Ticket #2252. Make sure the an INSERT from identical tables # does not violate constraints. # do_test insert4-1.1 { execsql { CREATE TABLE t1(a int, b int, check(b>a)); CREATE TABLE t2(x int, y int); INSERT INTO t2 VALUES(9,1); } catchsql { INSERT INTO t1 SELECT * FROM t2; } } {1 {constraint failed}} do_test insert4-1.2 { execsql { SELECT * FROM t1; } } {} # Other coverage tests for the INSERT transfer optimization. # do_test insert4-2.1 { execsql { INSERT INTO t1 SELECT 4, 8; SELECT * FROM t1; } } {4 8} do_test insert4-2.2.1 { execsql { CREATE TABLE t3(a int, b int); INSERT INTO t2 SELECT y, x FROM t2; INSERT INTO t3 SELECT * FROM t2 LIMIT 1; SELECT * FROM t3; } } {9 1} do_test insert4-2.2.2 { catchsql { DELETE FROM t1; INSERT INTO t1 SELECT * FROM t2 LIMIT 1; SELECT * FROM t1; } } {1 {constraint failed}} do_test insert4-2.3.1 { execsql { DELETE FROM t3; INSERT INTO t3 SELECT DISTINCT * FROM t2; SELECT * FROM t3; } } {9 1 1 9} do_test insert4-2.3.2 { catchsql { DELETE FROM t1; INSERT INTO t1 SELECT DISTINCT * FROM t2; } } {1 {constraint failed}} finish_test |