SQLite

Check-in [6fc1827523]
Login

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: 6fc18275230563437f2985eac3795e4dfe8eb9de
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
Unified Diff Show Whitespace Changes Patch
Changes to src/insert.c.
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.173 2007/02/13 15:01:11 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:







|







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
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
**
**    (2)  tab1 and tab2 are different tables
**
**    (3)  There must be no triggers on tab1
**
**    (4)  The result set of the SELECT statement is "*"
**







|







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
1385
1386
1387

1388
1389
1390
1391
1392
1393
1394
1395
1396
1397
1398
1399
1400
1401
1402
1403
  }
  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 */
  }
  if( pSelect->pHaving ){
    return 0;   /* SELECT may not have a HAVING clause */
  }

  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 */
  }
  if( pSelect->pOffset ){
    return 0;   /* SELECT may not have an OFFSET clause */
  }
  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;







<
|
<
>






|
<
<







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
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.328 2007/02/24 11:52:54 drh Exp $
*/
#include "sqliteInt.h"


/*
** Delete all the content of a Select structure but do not deallocate
** the select structure itself.







|







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