SQLite

Check-in [8309858143]
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
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 830985814345f71ba2def3c206e36aabe9e1ee7c
User & Date: drh 2007-02-24 13:53:05.000
Context
2007-02-24
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)
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)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/expr.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 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.
**







|







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 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.
**
2206
2207
2208
2209
2210
2211
2212
2213
2214
2215
2216
2217
2218
2219
2220
      }
    }
  }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;







|







2206
2207
2208
2209
2210
2211
2212
2213
2214
2215
2216
2217
2218
2219
2220
      }
    }
  }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
**    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:







|







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.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:
1293
1294
1295
1296
1297
1298
1299










1300
1301
1302
1303
1304
1305
1306
    }
  }

  /* 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
**







>
>
>
>
>
>
>
>
>
>







1293
1294
1295
1296
1297
1298
1299
1300
1301
1302
1303
1304
1305
1306
1307
1308
1309
1310
1311
1312
1313
1314
1315
1316
    }
  }

  /* 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
**
1460
1461
1462
1463
1464
1465
1466



1467
1468
1469
1470
1471
1472
1473
  **
  **    *   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 ){







>
>
>







1470
1471
1472
1473
1474
1475
1476
1477
1478
1479
1480
1481
1482
1483
1484
1485
1486
  **
  **    *   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
**    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>








|







9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
**    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>

4220
4221
4222
4223
4224
4225
4226

4227
4228
4229
4230
4231
4232
4233
  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







>







4220
4221
4222
4223
4224
4225
4226
4227
4228
4229
4230
4231
4232
4233
4234
  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
4257
4258
4259
4260
4261
4262
4263


4264
4265
4266
4267
4268
4269
4270
      (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);







>
>







4258
4259
4260
4261
4262
4263
4264
4265
4266
4267
4268
4269
4270
4271
4272
4273
      (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.
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











|

|



>
>
>
>
>
>
>
>
>
>





>









>
|





|
>

>
|





>

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







>
|






>
>
>
>
|






>
|





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


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
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
# 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.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