/ 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 Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/expr.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 routines used for analyzing expressions and
    13     13   ** for generating VDBE code that evaluates expressions in SQLite.
    14     14   **
    15         -** $Id: expr.c,v 1.278 2007/02/24 11:52:53 drh Exp $
           15  +** $Id: expr.c,v 1.279 2007/02/24 13:53:05 drh Exp $
    16     16   */
    17     17   #include "sqliteInt.h"
    18     18   #include <ctype.h>
    19     19   
    20     20   /*
    21     21   ** Return the 'affinity' of the expression pExpr if any.
    22     22   **
................................................................................
  2206   2206         }
  2207   2207       }
  2208   2208     }else if( pB->pList ){
  2209   2209       return 0;
  2210   2210     }
  2211   2211     if( pA->pSelect || pB->pSelect ) return 0;
  2212   2212     if( pA->iTable!=pB->iTable || pA->iColumn!=pB->iColumn ) return 0;
  2213         -  if( pA->token.z ){
         2213  +  if( pA->op!=TK_COLUMN && pA->token.z ){
  2214   2214       if( pB->token.z==0 ) return 0;
  2215   2215       if( pB->token.n!=pA->token.n ) return 0;
  2216   2216       if( sqlite3StrNICmp((char*)pA->token.z,(char*)pB->token.z,pB->token.n)!=0 ){
  2217   2217         return 0;
  2218   2218       }
  2219   2219     }
  2220   2220     return 1;

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.174 2007/02/24 13:23:52 drh Exp $
           15  +** $Id: insert.c,v 1.175 2007/02/24 13:53:05 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:
................................................................................
  1293   1293       }
  1294   1294     }
  1295   1295   
  1296   1296     /* If no test above fails then the indices must be compatible */
  1297   1297     return 1;
  1298   1298   }
  1299   1299   
         1300  +#ifdef SQLITE_TEST
         1301  +/*
         1302  +** The following global variable is incremented whenever the
         1303  +** transfer optimization is used.  This is used for testing
         1304  +** purposes only - to make sure the transfer optimization really
         1305  +** is happening when it is suppose to.
         1306  +*/
         1307  +int sqlite3_xferopt_count;
         1308  +#endif /* SQLITE_TEST */
         1309  +
  1300   1310   /*
  1301   1311   ** Attempt the transfer optimization on INSERTs of the form
  1302   1312   **
  1303   1313   **     INSERT INTO tab1 SELECT * FROM tab2;
  1304   1314   **
  1305   1315   ** This optimization is only attempted if
  1306   1316   **
................................................................................
  1460   1470     **
  1461   1471     **    *   We can always do the transfer if the table contains an
  1462   1472     **        an integer primary key
  1463   1473     **
  1464   1474     **    *   We can conditionally do the transfer if the destination
  1465   1475     **        table is empty.
  1466   1476     */
         1477  +#ifdef SQLITE_TEST
         1478  +  sqlite3_xferopt_count++;
         1479  +#endif
  1467   1480     iDbSrc = sqlite3SchemaToIndex(pParse->db, pSrc->pSchema);
  1468   1481     v = sqlite3GetVdbe(pParse);
  1469   1482     iSrc = pParse->nTab++;
  1470   1483     iDest = pParse->nTab++;
  1471   1484     counterMem = autoIncBegin(pParse, iDbDest, pDest);
  1472   1485     sqlite3OpenTable(pParse, iDest, iDbDest, pDest, OP_OpenWrite);
  1473   1486     if( pDest->iPKey<0 ){

Changes to src/test1.c.

     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** Code for testing all sorts of SQLite interfaces.  This code
    13     13   ** is not included in the SQLite library.  It is used for automated
    14     14   ** testing of the SQLite library.
    15     15   **
    16         -** $Id: test1.c,v 1.228 2007/02/05 14:21:48 danielk1977 Exp $
           16  +** $Id: test1.c,v 1.229 2007/02/24 13:53:05 drh Exp $
    17     17   */
    18     18   #include "sqliteInt.h"
    19     19   #include "tcl.h"
    20     20   #include "os.h"
    21     21   #include <stdlib.h>
    22     22   #include <string.h>
    23     23   
................................................................................
  4220   4220     extern int sqlite3_sync_count, sqlite3_fullsync_count;
  4221   4221     extern int sqlite3_opentemp_count;
  4222   4222     extern int sqlite3_memUsed;
  4223   4223     extern int sqlite3_malloc_id;
  4224   4224     extern int sqlite3_memMax;
  4225   4225     extern int sqlite3_like_count;
  4226   4226     extern int sqlite3_tsd_count;
         4227  +  extern int sqlite3_xferopt_count;
  4227   4228   #if OS_UNIX && defined(SQLITE_TEST) && defined(THREADSAFE) && THREADSAFE
  4228   4229     extern int threadsOverrideEachOthersLocks;
  4229   4230   #endif
  4230   4231   #if OS_WIN
  4231   4232     extern int sqlite3_os_type;
  4232   4233   #endif
  4233   4234   #ifdef SQLITE_DEBUG
................................................................................
  4257   4258         (char*)&sqlite3_open_file_count, TCL_LINK_INT);
  4258   4259     Tcl_LinkVar(interp, "sqlite_current_time", 
  4259   4260         (char*)&sqlite3_current_time, TCL_LINK_INT);
  4260   4261     Tcl_LinkVar(interp, "sqlite_os_trace",
  4261   4262         (char*)&sqlite3_os_trace, TCL_LINK_INT);
  4262   4263     Tcl_LinkVar(interp, "sqlite3_tsd_count",
  4263   4264         (char*)&sqlite3_tsd_count, TCL_LINK_INT);
         4265  +  Tcl_LinkVar(interp, "sqlite3_xferopt_count",
         4266  +      (char*)&sqlite3_xferopt_count, TCL_LINK_INT);
  4264   4267   #ifndef SQLITE_OMIT_UTF16
  4265   4268     Tcl_LinkVar(interp, "unaligned_string_counter",
  4266   4269         (char*)&unaligned_string_counter, TCL_LINK_INT);
  4267   4270   #endif
  4268   4271   #if OS_UNIX && defined(SQLITE_TEST) && defined(THREADSAFE) && THREADSAFE
  4269   4272     Tcl_LinkVar(interp, "threadsOverrideEachOthersLocks",
  4270   4273         (char*)&threadsOverrideEachOthersLocks, TCL_LINK_INT);

Changes to test/insert4.test.

     5      5   #
     6      6   #    May you do good and not evil.
     7      7   #    May you find forgiveness for yourself and forgive others.
     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #***********************************************************************
    11     11   # This file implements regression tests for SQLite library.  The
    12         -# focus of this file is testing corner cases of the INSERT statement.
           12  +# focus of this file is testing the INSERT transfer optimization.
    13     13   #
    14         -# $Id: insert4.test,v 1.1 2007/02/24 13:23:53 drh Exp $
           14  +# $Id: insert4.test,v 1.2 2007/02/24 13:53:05 drh Exp $
    15     15   
    16     16   set testdir [file dirname $argv0]
    17     17   source $testdir/tester.tcl
           18  +
           19  +# The sqlite3_xferopt_count variable is incremented whenever the 
           20  +# insert transfer optimization applies.
           21  +#
           22  +# This procedure runs a test to see if the sqlite3_xferopt_count is
           23  +# set to N.
           24  +#
           25  +proc xferopt_test {testname N} {
           26  +  do_test $testname {set ::sqlite3_xferopt_count} $N
           27  +}
    18     28   
    19     29   # Ticket #2252.  Make sure the an INSERT from identical tables
    20     30   # does not violate constraints.
    21     31   #
    22     32   do_test insert4-1.1 {
           33  +  set sqlite3_xferopt_count 0
    23     34     execsql {
    24     35       CREATE TABLE t1(a int, b int, check(b>a));
    25     36       CREATE TABLE t2(x int, y int);
    26     37       INSERT INTO t2 VALUES(9,1);
    27     38     }
    28     39     catchsql {
    29     40       INSERT INTO t1 SELECT * FROM t2;
    30     41     }
    31     42   } {1 {constraint failed}}
    32         -do_test insert4-1.2 {
           43  +xferopt_test insert4-1.2 0
           44  +do_test insert4-1.3 {
    33     45     execsql {
    34     46       SELECT * FROM t1;
    35     47     }
    36     48   } {}
    37     49   
    38         -# Other coverage tests for the INSERT transfer optimization.
           50  +# Tests to make sure that the transfer optimization is not occurring
           51  +# when it is not a valid optimization.
    39     52   #
    40         -do_test insert4-2.1 {
           53  +# The SELECT must be against a real table.
           54  +do_test insert4-2.1.1 {
    41     55     execsql {
    42     56       INSERT INTO t1 SELECT 4, 8;
    43     57       SELECT * FROM t1;
    44     58     }
    45     59   } {4 8}
           60  +xferopt_test insert4-2.1.2  0
    46     61   do_test insert4-2.2.1 {
           62  +  catchsql {
           63  +    DELETE FROM t1;
           64  +    CREATE VIEW v1 AS SELECT y, x FROM t2;
           65  +    INSERT INTO t1 SELECT * FROM v1;
           66  +    SELECT * FROM t1;
           67  +  }
           68  +} {0 {1 9}}
           69  +xferopt_test insert4-2.2.2 0
           70  +
           71  +# Do not run the transfer optimization if there is a LIMIT clause
           72  +#
           73  +do_test insert4-2.3.1 {
    47     74     execsql {
    48     75       CREATE TABLE t3(a int, b int);
    49     76       INSERT INTO t2 SELECT y, x FROM t2;
    50     77       INSERT INTO t3 SELECT * FROM t2 LIMIT 1;
    51     78       SELECT * FROM t3;
    52     79     }
    53     80   } {9 1}
    54         -do_test insert4-2.2.2 {
           81  +xferopt_test insert4-2.3.2  0
           82  +do_test insert4-2.3.3 {
    55     83     catchsql {
    56     84       DELETE FROM t1;
    57     85       INSERT INTO t1 SELECT * FROM t2 LIMIT 1;
    58     86       SELECT * FROM t1;
    59     87     }
    60     88   } {1 {constraint failed}}
    61         -do_test insert4-2.3.1 {
           89  +xferopt_test insert4-2.3.4 0
           90  +
           91  +# Do not run the transfer optimization if there is a DISTINCT
           92  +#
           93  +do_test insert4-2.4.1 {
    62     94     execsql {
    63     95       DELETE FROM t3;
    64     96       INSERT INTO t3 SELECT DISTINCT * FROM t2;
    65     97       SELECT * FROM t3;
    66     98     }
    67     99   } {9 1 1 9}
    68         -do_test insert4-2.3.2 {
          100  +xferopt_test insert4-2.4.2 0
          101  +do_test insert4-2.4.3 {
    69    102     catchsql {
    70    103       DELETE FROM t1;
    71    104       INSERT INTO t1 SELECT DISTINCT * FROM t2;
    72    105     }
    73    106   } {1 {constraint failed}}
          107  +xferopt_test insert4-2.4.4 0
          108  +
          109  +# Do run the transfer optimization if tables have identical
          110  +# CHECK constraints.
          111  +#
          112  +do_test insert4-3.1.1 {
          113  +  set sqlite3_xferopt_count 0
          114  +  execsql {
          115  +    DELETE FROM t1;
          116  +    INSERT INTO t1 VALUES(1,9);
          117  +    CREATE TABLE t4(m int, n int, CHECK(n>m));
          118  +    INSERT INTO t4 SELECT * FROM t1;
          119  +    SELECT * FROM t4;
          120  +  }
          121  +} {1 9}
          122  +xferopt_test insert4-3.1.2 1
    74    123   
    75    124   finish_test