/ Check-in [6fc18275]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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 | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 6fc18275230563437f2985eac3795e4dfe8eb9de
User & Date: drh 2007-02-24 13:23:52
Context
2007-02-24
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
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: e11bbf17 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

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.173 2007/02/13 15:01:11 drh Exp $
           15  +** $Id: insert.c,v 1.174 2007/02/24 13:23:52 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:
................................................................................
  1301   1301   ** Attempt the transfer optimization on INSERTs of the form
  1302   1302   **
  1303   1303   **     INSERT INTO tab1 SELECT * FROM tab2;
  1304   1304   **
  1305   1305   ** This optimization is only attempted if
  1306   1306   **
  1307   1307   **    (1)  tab1 and tab2 have identical schemas including all the
  1308         -**         same indices
         1308  +**         same indices and constraints
  1309   1309   **
  1310   1310   **    (2)  tab1 and tab2 are different tables
  1311   1311   **
  1312   1312   **    (3)  There must be no triggers on tab1
  1313   1313   **
  1314   1314   **    (4)  The result set of the SELECT statement is "*"
  1315   1315   **
................................................................................
  1378   1378     }
  1379   1379     if( pSelect->pWhere ){
  1380   1380       return 0;   /* SELECT may not have a WHERE clause */
  1381   1381     }
  1382   1382     if( pSelect->pOrderBy ){
  1383   1383       return 0;   /* SELECT may not have an ORDER BY clause */
  1384   1384     }
  1385         -  if( pSelect->pHaving ){
  1386         -    return 0;   /* SELECT may not have a HAVING clause */
  1387         -  }
         1385  +  /* Do not need to test for a HAVING clause.  If HAVING is present but
         1386  +  ** there is no ORDER BY, we will get an error. */
  1388   1387     if( pSelect->pGroupBy ){
  1389   1388       return 0;   /* SELECT may not have a GROUP BY clause */
  1390   1389     }
  1391   1390     if( pSelect->pLimit ){
  1392   1391       return 0;   /* SELECT may not have a LIMIT clause */
  1393   1392     }
  1394         -  if( pSelect->pOffset ){
  1395         -    return 0;   /* SELECT may not have an OFFSET clause */
  1396         -  }
         1393  +  assert( pSelect->pOffset==0 );  /* Must be so if pLimit==0 */
  1397   1394     if( pSelect->pPrior ){
  1398   1395       return 0;   /* SELECT may not be a compound query */
  1399   1396     }
  1400   1397     if( pSelect->isDistinct ){
  1401   1398       return 0;   /* SELECT may not be DISTINCT */
  1402   1399     }
  1403   1400     pEList = pSelect->pEList;
................................................................................
  1451   1448       for(pSrcIdx=pSrc->pIndex; pSrcIdx; pSrcIdx=pSrcIdx->pNext){
  1452   1449         if( xferCompatibleIndex(pDestIdx, pSrcIdx) ) break;
  1453   1450       }
  1454   1451       if( pSrcIdx==0 ){
  1455   1452         return 0;    /* pDestIdx has no corresponding index in pSrc */
  1456   1453       }
  1457   1454     }
         1455  +  if( !sqlite3ExprCompare(pSrc->pCheck, pDest->pCheck) ){
         1456  +    return 0;   /* Tables have different CHECK constraints.  Ticket #2252 */
         1457  +  }
  1458   1458   
  1459   1459     /* If we get this far, it means either:
  1460   1460     **
  1461   1461     **    *   We can always do the transfer if the table contains an
  1462   1462     **        an integer primary key
  1463   1463     **
  1464   1464     **    *   We can conditionally do the transfer if the destination

Changes to src/select.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 SELECT statements in SQLite.
    14     14   **
    15         -** $Id: select.c,v 1.328 2007/02/24 11:52:54 drh Exp $
           15  +** $Id: select.c,v 1.329 2007/02/24 13:23:53 drh Exp $
    16     16   */
    17     17   #include "sqliteInt.h"
    18     18   
    19     19   
    20     20   /*
    21     21   ** Delete all the content of a Select structure but do not deallocate
    22     22   ** the select structure itself.
................................................................................
    64     64     pNew->pSrc = pSrc;
    65     65     pNew->pWhere = pWhere;
    66     66     pNew->pGroupBy = pGroupBy;
    67     67     pNew->pHaving = pHaving;
    68     68     pNew->pOrderBy = pOrderBy;
    69     69     pNew->isDistinct = isDistinct;
    70     70     pNew->op = TK_SELECT;
           71  +  assert( pOffset==0 || pLimit!=0 );
    71     72     pNew->pLimit = pLimit;
    72     73     pNew->pOffset = pOffset;
    73     74     pNew->iLimit = -1;
    74     75     pNew->iOffset = -1;
    75     76     pNew->addrOpenEphm[0] = -1;
    76     77     pNew->addrOpenEphm[1] = -1;
    77     78     pNew->addrOpenEphm[2] = -1;

Added test/insert4.test.

            1  +# 2007 January 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.  The
           12  +# focus of this file is testing corner cases of the INSERT statement.
           13  +#
           14  +# $Id: insert4.test,v 1.1 2007/02/24 13:23:53 drh Exp $
           15  +
           16  +set testdir [file dirname $argv0]
           17  +source $testdir/tester.tcl
           18  +
           19  +# Ticket #2252.  Make sure the an INSERT from identical tables
           20  +# does not violate constraints.
           21  +#
           22  +do_test insert4-1.1 {
           23  +  execsql {
           24  +    CREATE TABLE t1(a int, b int, check(b>a));
           25  +    CREATE TABLE t2(x int, y int);
           26  +    INSERT INTO t2 VALUES(9,1);
           27  +  }
           28  +  catchsql {
           29  +    INSERT INTO t1 SELECT * FROM t2;
           30  +  }
           31  +} {1 {constraint failed}}
           32  +do_test insert4-1.2 {
           33  +  execsql {
           34  +    SELECT * FROM t1;
           35  +  }
           36  +} {}
           37  +
           38  +# Other coverage tests for the INSERT transfer optimization.
           39  +#
           40  +do_test insert4-2.1 {
           41  +  execsql {
           42  +    INSERT INTO t1 SELECT 4, 8;
           43  +    SELECT * FROM t1;
           44  +  }
           45  +} {4 8}
           46  +do_test insert4-2.2.1 {
           47  +  execsql {
           48  +    CREATE TABLE t3(a int, b int);
           49  +    INSERT INTO t2 SELECT y, x FROM t2;
           50  +    INSERT INTO t3 SELECT * FROM t2 LIMIT 1;
           51  +    SELECT * FROM t3;
           52  +  }
           53  +} {9 1}
           54  +do_test insert4-2.2.2 {
           55  +  catchsql {
           56  +    DELETE FROM t1;
           57  +    INSERT INTO t1 SELECT * FROM t2 LIMIT 1;
           58  +    SELECT * FROM t1;
           59  +  }
           60  +} {1 {constraint failed}}
           61  +do_test insert4-2.3.1 {
           62  +  execsql {
           63  +    DELETE FROM t3;
           64  +    INSERT INTO t3 SELECT DISTINCT * FROM t2;
           65  +    SELECT * FROM t3;
           66  +  }
           67  +} {9 1 1 9}
           68  +do_test insert4-2.3.2 {
           69  +  catchsql {
           70  +    DELETE FROM t1;
           71  +    INSERT INTO t1 SELECT DISTINCT * FROM t2;
           72  +  }
           73  +} {1 {constraint failed}}
           74  +
           75  +finish_test