/ Check-in [83098581]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Additional tests and some improvements to the INSERT transfer optimization. More testing is needed. (CVS 3661)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 830985814345f71ba2def3c206e36aabe9e1ee7c
User & Date: drh 2007-02-24 13:53:05
Context
2007-02-24
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
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
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/expr.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
....
2206
2207
2208
2209
2210
2211
2212
2213
2214
2215
2216
2217
2218
2219
2220
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains routines used for analyzing expressions and
** for generating VDBE code that evaluates expressions in SQLite.
**
** $Id: expr.c,v 1.278 2007/02/24 11:52:53 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** Return the 'affinity' of the expression pExpr if any.
**
................................................................................
      }
    }
  }else if( pB->pList ){
    return 0;
  }
  if( pA->pSelect || pB->pSelect ) return 0;
  if( pA->iTable!=pB->iTable || pA->iColumn!=pB->iColumn ) return 0;
  if( pA->token.z ){
    if( pB->token.z==0 ) return 0;
    if( pB->token.n!=pA->token.n ) return 0;
    if( sqlite3StrNICmp((char*)pA->token.z,(char*)pB->token.z,pB->token.n)!=0 ){
      return 0;
    }
  }
  return 1;







|







 







|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
....
2206
2207
2208
2209
2210
2211
2212
2213
2214
2215
2216
2217
2218
2219
2220
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains routines used for analyzing expressions and
** for generating VDBE code that evaluates expressions in SQLite.
**
** $Id: expr.c,v 1.279 2007/02/24 13:53:05 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** Return the 'affinity' of the expression pExpr if any.
**
................................................................................
      }
    }
  }else if( pB->pList ){
    return 0;
  }
  if( pA->pSelect || pB->pSelect ) return 0;
  if( pA->iTable!=pB->iTable || pA->iColumn!=pB->iColumn ) return 0;
  if( pA->op!=TK_COLUMN && pA->token.z ){
    if( pB->token.z==0 ) return 0;
    if( pB->token.n!=pA->token.n ) return 0;
    if( sqlite3StrNICmp((char*)pA->token.z,(char*)pB->token.z,pB->token.n)!=0 ){
      return 0;
    }
  }
  return 1;

Changes to src/insert.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
....
1293
1294
1295
1296
1297
1298
1299










1300
1301
1302
1303
1304
1305
1306
....
1460
1461
1462
1463
1464
1465
1466



1467
1468
1469
1470
1471
1472
1473
**    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:
................................................................................
    }
  }

  /* If no test above fails then the indices must be compatible */
  return 1;
}











/*
** Attempt the transfer optimization on INSERTs of the form
**
**     INSERT INTO tab1 SELECT * FROM tab2;
**
** This optimization is only attempted if
**
................................................................................
  **
  **    *   We can always do the transfer if the table contains an
  **        an integer primary key
  **
  **    *   We can conditionally do the transfer if the destination
  **        table is empty.
  */



  iDbSrc = sqlite3SchemaToIndex(pParse->db, pSrc->pSchema);
  v = sqlite3GetVdbe(pParse);
  iSrc = pParse->nTab++;
  iDest = pParse->nTab++;
  counterMem = autoIncBegin(pParse, iDbDest, pDest);
  sqlite3OpenTable(pParse, iDest, iDbDest, pDest, OP_OpenWrite);
  if( pDest->iPKey<0 ){







|







 







>
>
>
>
>
>
>
>
>
>







 







>
>
>







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
....
1293
1294
1295
1296
1297
1298
1299
1300
1301
1302
1303
1304
1305
1306
1307
1308
1309
1310
1311
1312
1313
1314
1315
1316
....
1470
1471
1472
1473
1474
1475
1476
1477
1478
1479
1480
1481
1482
1483
1484
1485
1486
**    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.175 2007/02/24 13:53:05 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:
................................................................................
    }
  }

  /* If no test above fails then the indices must be compatible */
  return 1;
}

#ifdef SQLITE_TEST
/*
** The following global variable is incremented whenever the
** transfer optimization is used.  This is used for testing
** purposes only - to make sure the transfer optimization really
** is happening when it is suppose to.
*/
int sqlite3_xferopt_count;
#endif /* SQLITE_TEST */

/*
** Attempt the transfer optimization on INSERTs of the form
**
**     INSERT INTO tab1 SELECT * FROM tab2;
**
** This optimization is only attempted if
**
................................................................................
  **
  **    *   We can always do the transfer if the table contains an
  **        an integer primary key
  **
  **    *   We can conditionally do the transfer if the destination
  **        table is empty.
  */
#ifdef SQLITE_TEST
  sqlite3_xferopt_count++;
#endif
  iDbSrc = sqlite3SchemaToIndex(pParse->db, pSrc->pSchema);
  v = sqlite3GetVdbe(pParse);
  iSrc = pParse->nTab++;
  iDest = pParse->nTab++;
  counterMem = autoIncBegin(pParse, iDbDest, pDest);
  sqlite3OpenTable(pParse, iDest, iDbDest, pDest, OP_OpenWrite);
  if( pDest->iPKey<0 ){

Changes to src/test1.c.

9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
....
4220
4221
4222
4223
4224
4225
4226

4227
4228
4229
4230
4231
4232
4233
....
4257
4258
4259
4260
4261
4262
4263


4264
4265
4266
4267
4268
4269
4270
**    May you share freely, never taking more than you give.
**
*************************************************************************
** Code for testing all sorts of SQLite interfaces.  This code
** is not included in the SQLite library.  It is used for automated
** testing of the SQLite library.
**
** $Id: test1.c,v 1.228 2007/02/05 14:21:48 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include "tcl.h"
#include "os.h"
#include <stdlib.h>
#include <string.h>

................................................................................
  extern int sqlite3_sync_count, sqlite3_fullsync_count;
  extern int sqlite3_opentemp_count;
  extern int sqlite3_memUsed;
  extern int sqlite3_malloc_id;
  extern int sqlite3_memMax;
  extern int sqlite3_like_count;
  extern int sqlite3_tsd_count;

#if OS_UNIX && defined(SQLITE_TEST) && defined(THREADSAFE) && THREADSAFE
  extern int threadsOverrideEachOthersLocks;
#endif
#if OS_WIN
  extern int sqlite3_os_type;
#endif
#ifdef SQLITE_DEBUG
................................................................................
      (char*)&sqlite3_open_file_count, TCL_LINK_INT);
  Tcl_LinkVar(interp, "sqlite_current_time", 
      (char*)&sqlite3_current_time, TCL_LINK_INT);
  Tcl_LinkVar(interp, "sqlite_os_trace",
      (char*)&sqlite3_os_trace, TCL_LINK_INT);
  Tcl_LinkVar(interp, "sqlite3_tsd_count",
      (char*)&sqlite3_tsd_count, TCL_LINK_INT);


#ifndef SQLITE_OMIT_UTF16
  Tcl_LinkVar(interp, "unaligned_string_counter",
      (char*)&unaligned_string_counter, TCL_LINK_INT);
#endif
#if OS_UNIX && defined(SQLITE_TEST) && defined(THREADSAFE) && THREADSAFE
  Tcl_LinkVar(interp, "threadsOverrideEachOthersLocks",
      (char*)&threadsOverrideEachOthersLocks, TCL_LINK_INT);







|







 







>







 







>
>







9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
....
4220
4221
4222
4223
4224
4225
4226
4227
4228
4229
4230
4231
4232
4233
4234
....
4258
4259
4260
4261
4262
4263
4264
4265
4266
4267
4268
4269
4270
4271
4272
4273
**    May you share freely, never taking more than you give.
**
*************************************************************************
** Code for testing all sorts of SQLite interfaces.  This code
** is not included in the SQLite library.  It is used for automated
** testing of the SQLite library.
**
** $Id: test1.c,v 1.229 2007/02/24 13:53:05 drh Exp $
*/
#include "sqliteInt.h"
#include "tcl.h"
#include "os.h"
#include <stdlib.h>
#include <string.h>

................................................................................
  extern int sqlite3_sync_count, sqlite3_fullsync_count;
  extern int sqlite3_opentemp_count;
  extern int sqlite3_memUsed;
  extern int sqlite3_malloc_id;
  extern int sqlite3_memMax;
  extern int sqlite3_like_count;
  extern int sqlite3_tsd_count;
  extern int sqlite3_xferopt_count;
#if OS_UNIX && defined(SQLITE_TEST) && defined(THREADSAFE) && THREADSAFE
  extern int threadsOverrideEachOthersLocks;
#endif
#if OS_WIN
  extern int sqlite3_os_type;
#endif
#ifdef SQLITE_DEBUG
................................................................................
      (char*)&sqlite3_open_file_count, TCL_LINK_INT);
  Tcl_LinkVar(interp, "sqlite_current_time", 
      (char*)&sqlite3_current_time, TCL_LINK_INT);
  Tcl_LinkVar(interp, "sqlite_os_trace",
      (char*)&sqlite3_os_trace, TCL_LINK_INT);
  Tcl_LinkVar(interp, "sqlite3_tsd_count",
      (char*)&sqlite3_tsd_count, TCL_LINK_INT);
  Tcl_LinkVar(interp, "sqlite3_xferopt_count",
      (char*)&sqlite3_xferopt_count, TCL_LINK_INT);
#ifndef SQLITE_OMIT_UTF16
  Tcl_LinkVar(interp, "unaligned_string_counter",
      (char*)&unaligned_string_counter, TCL_LINK_INT);
#endif
#if OS_UNIX && defined(SQLITE_TEST) && defined(THREADSAFE) && THREADSAFE
  Tcl_LinkVar(interp, "threadsOverrideEachOthersLocks",
      (char*)&threadsOverrideEachOthersLocks, TCL_LINK_INT);

Changes to test/insert4.test.

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
#
#    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







|

|



>
>
>
>
>
>
>
>
>
>





>









>
|





|
>

>
|





>

>
>
>
>
>
>
>
>
>
>
>
>







>
|






>
>
>
>
|






>
|





>

>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

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
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
#
#    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.2 2007/02/24 13:53:05 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
}

# 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 {
    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}}
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 {
    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;
    CREATE VIEW v1 AS SELECT y, x FROM t2;
    INSERT INTO t1 SELECT * FROM v1;
    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 {
    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}
xferopt_test insert4-2.3.2  0
do_test insert4-2.3.3 {
  catchsql {
    DELETE FROM t1;
    INSERT INTO t1 SELECT * FROM t2 LIMIT 1;
    SELECT * FROM t1;
  }
} {1 {constraint failed}}
xferopt_test insert4-2.3.4 0

# Do not run the transfer optimization if there is a DISTINCT
#
do_test insert4-2.4.1 {
  execsql {
    DELETE FROM t3;
    INSERT INTO t3 SELECT DISTINCT * FROM t2;
    SELECT * FROM t3;
  }
} {9 1 1 9}
xferopt_test insert4-2.4.2 0
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.
#
do_test insert4-3.1.1 {
  set sqlite3_xferopt_count 0
  execsql {
    DELETE FROM t1;
    INSERT INTO t1 VALUES(1,9);
    CREATE TABLE t4(m int, n int, CHECK(n>m));
    INSERT INTO t4 SELECT * FROM t1;
    SELECT * FROM t4;
  }
} {1 9}
xferopt_test insert4-3.1.2 1

finish_test