/ Check-in [2bf5475b]
Login

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

Overview
Comment:Additional test cases added. Improvements to the INSERT transfer optimization. (CVS 3662)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 2bf5475bde763f73f7f4dd9cac7d13a631a7d2aa
User & Date: drh 2007-02-24 15:18:50
Context
2007-02-24
15:29
Add comments to sqlite3ExprCompare() to clarify its operation. Ticket #2216. (CVS 3663) check-in: fba0a1e5 user: drh tags: trunk
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
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.175 2007/02/24 13:53:05 drh Exp $
           15  +** $Id: insert.c,v 1.176 2007/02/24 15:18:50 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:
................................................................................
  1458   1458       for(pSrcIdx=pSrc->pIndex; pSrcIdx; pSrcIdx=pSrcIdx->pNext){
  1459   1459         if( xferCompatibleIndex(pDestIdx, pSrcIdx) ) break;
  1460   1460       }
  1461   1461       if( pSrcIdx==0 ){
  1462   1462         return 0;    /* pDestIdx has no corresponding index in pSrc */
  1463   1463       }
  1464   1464     }
  1465         -  if( !sqlite3ExprCompare(pSrc->pCheck, pDest->pCheck) ){
         1465  +  if( pDest->pCheck && !sqlite3ExprCompare(pSrc->pCheck, pDest->pCheck) ){
  1466   1466       return 0;   /* Tables have different CHECK constraints.  Ticket #2252 */
  1467   1467     }
  1468   1468   
  1469   1469     /* If we get this far, it means either:
  1470   1470     **
  1471   1471     **    *   We can always do the transfer if the table contains an
  1472   1472     **        an integer primary key

Changes to test/insert4.test.

     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     12   # focus of this file is testing the INSERT transfer optimization.
    13     13   #
    14         -# $Id: insert4.test,v 1.2 2007/02/24 13:53:05 drh Exp $
           14  +# $Id: insert4.test,v 1.3 2007/02/24 15:18:50 drh Exp $
    15     15   
    16     16   set testdir [file dirname $argv0]
    17     17   source $testdir/tester.tcl
    18     18   
    19     19   # The sqlite3_xferopt_count variable is incremented whenever the 
    20     20   # insert transfer optimization applies.
    21     21   #
    22     22   # This procedure runs a test to see if the sqlite3_xferopt_count is
    23     23   # set to N.
    24     24   #
    25     25   proc xferopt_test {testname N} {
    26     26     do_test $testname {set ::sqlite3_xferopt_count} $N
    27     27   }
           28  +
           29  +# Create tables used for testing.
           30  +#
           31  +execsql {
           32  +  CREATE TABLE t1(a int, b int, check(b>a));
           33  +  CREATE TABLE t2(x int, y int);
           34  +  CREATE VIEW v2 AS SELECT y, x FROM t2;
           35  +  CREATE TABLE t3(a int, b int);
           36  +}
    28     37   
    29     38   # Ticket #2252.  Make sure the an INSERT from identical tables
    30     39   # does not violate constraints.
    31     40   #
    32     41   do_test insert4-1.1 {
    33     42     set sqlite3_xferopt_count 0
    34     43     execsql {
    35         -    CREATE TABLE t1(a int, b int, check(b>a));
    36         -    CREATE TABLE t2(x int, y int);
           44  +    DELETE FROM t1;
           45  +    DELETE FROM t2;
    37     46       INSERT INTO t2 VALUES(9,1);
    38     47     }
    39     48     catchsql {
    40     49       INSERT INTO t1 SELECT * FROM t2;
    41     50     }
    42     51   } {1 {constraint failed}}
    43     52   xferopt_test insert4-1.2 0
................................................................................
    49     58   
    50     59   # Tests to make sure that the transfer optimization is not occurring
    51     60   # when it is not a valid optimization.
    52     61   #
    53     62   # The SELECT must be against a real table.
    54     63   do_test insert4-2.1.1 {
    55     64     execsql {
           65  +    DELETE FROM t1;
    56     66       INSERT INTO t1 SELECT 4, 8;
    57     67       SELECT * FROM t1;
    58     68     }
    59     69   } {4 8}
    60     70   xferopt_test insert4-2.1.2  0
    61     71   do_test insert4-2.2.1 {
    62     72     catchsql {
    63     73       DELETE FROM t1;
    64         -    CREATE VIEW v1 AS SELECT y, x FROM t2;
    65         -    INSERT INTO t1 SELECT * FROM v1;
           74  +    INSERT INTO t1 SELECT * FROM v2;
    66     75       SELECT * FROM t1;
    67     76     }
    68     77   } {0 {1 9}}
    69     78   xferopt_test insert4-2.2.2 0
    70     79   
    71     80   # Do not run the transfer optimization if there is a LIMIT clause
    72     81   #
    73     82   do_test insert4-2.3.1 {
    74     83     execsql {
    75         -    CREATE TABLE t3(a int, b int);
           84  +    DELETE FROM t2;
           85  +    INSERT INTO t2 VALUES(9,1);
    76     86       INSERT INTO t2 SELECT y, x FROM t2;
    77     87       INSERT INTO t3 SELECT * FROM t2 LIMIT 1;
    78     88       SELECT * FROM t3;
    79     89     }
    80     90   } {9 1}
    81     91   xferopt_test insert4-2.3.2  0
    82     92   do_test insert4-2.3.3 {
................................................................................
   101    111   do_test insert4-2.4.3 {
   102    112     catchsql {
   103    113       DELETE FROM t1;
   104    114       INSERT INTO t1 SELECT DISTINCT * FROM t2;
   105    115     }
   106    116   } {1 {constraint failed}}
   107    117   xferopt_test insert4-2.4.4 0
          118  +
          119  +# The following procedure constructs two tables then tries to transfer
          120  +# data from one table to the other.  Checks are made to make sure the
          121  +# transfer is successful and that the transfer optimization was used or
          122  +# not, as appropriate.
          123  +#
          124  +#     xfer_check TESTID  XFER-USED   INIT-DATA   DEST-SCHEMA   SRC-SCHEMA 
          125  +#
          126  +# The TESTID argument is the symbolic name for this test.  The XFER-USED
          127  +# argument is true if the transfer optimization should be employed and
          128  +# false if not.  INIT-DATA is a single row of data that is to be 
          129  +# transfered.  DEST-SCHEMA and SRC-SCHEMA are table declarations for
          130  +# the destination and source tables.
          131  +#
          132  +proc xfer_check {testid xferused initdata destschema srcschema} {
          133  +  execsql "CREATE TABLE dest($destschema)"
          134  +  execsql "CREATE TABLE src($srcschema)"
          135  +  execsql "INSERT INTO src VALUES([join $initdata ,])"
          136  +  set ::sqlite3_xferopt_count 0
          137  +  do_test $testid.1 {
          138  +    execsql {
          139  +      INSERT INTO dest SELECT * FROM src;
          140  +      SELECT * FROM dest;
          141  +    }
          142  +  } $initdata
          143  +  do_test $testid.2 {
          144  +    set ::sqlite3_xferopt_count
          145  +  } $xferused
          146  +  execsql {
          147  +    DROP TABLE dest;
          148  +    DROP TABLE src;
          149  +  }
          150  +}
          151  +
   108    152   
   109    153   # Do run the transfer optimization if tables have identical
   110    154   # CHECK constraints.
   111    155   #
   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
          156  +xfer_check insert4-3.1 1 {1 9} \
          157  +    {a int, b int CHECK(b>a)} \
          158  +    {x int, y int CHECK(y>x)}
          159  +xfer_check insert4-3.2 1 {1 9} \
          160  +    {a int, b int CHECK(b>a)} \
          161  +    {x int CHECK(y>x), y int}
          162  +
          163  +# Do run the transfer optimization if the destination table lacks
          164  +# any CHECK constraints regardless of whether or not there are CHECK
          165  +# constraints on the source table.
          166  +#
          167  +xfer_check insert4-3.3 1 {1 9} \
          168  +    {a int, b int} \
          169  +    {x int, y int CHECK(y>x)}
          170  +
          171  +# Do run the transfer optimization if the destination table omits
          172  +# NOT NULL constraints that the source table has.
          173  +#
          174  +xfer_check insert4-3.4 0 {1 9} \
          175  +    {a int, b int CHECK(b>a)} \
          176  +    {x int, y int}
          177  +
          178  +# Do not run the optimization if the destination has NOT NULL
          179  +# constraints that the source table lacks.
          180  +#
          181  +xfer_check insert4-3.5 0 {1 9} \
          182  +    {a int, b int NOT NULL} \
          183  +    {x int, y int}
          184  +xfer_check insert4-3.6 0 {1 9} \
          185  +    {a int, b int NOT NULL} \
          186  +    {x int NOT NULL, y int}
          187  +xfer_check insert4-3.7 0 {1 9} \
          188  +    {a int NOT NULL, b int NOT NULL} \
          189  +    {x int NOT NULL, y int}
          190  +xfer_check insert4-3.8 0 {1 9} \
          191  +    {a int NOT NULL, b int} \
          192  +    {x int, y int}
          193  +
          194  +
          195  +# Do run the transfer optimization if the destination table and
          196  +# source table have the same NOT NULL constraints or if the 
          197  +# source table has extra NOT NULL constraints.
          198  +#
          199  +xfer_check insert4-3.9 1 {1 9} \
          200  +    {a int, b int} \
          201  +    {x int NOT NULL, y int}
          202  +xfer_check insert4-3.10 1 {1 9} \
          203  +    {a int, b int} \
          204  +    {x int NOT NULL, y int NOT NULL}
          205  +xfer_check insert4-3.11 1 {1 9} \
          206  +    {a int NOT NULL, b int} \
          207  +    {x int NOT NULL, y int NOT NULL}
          208  +xfer_check insert4-3.12 1 {1 9} \
          209  +    {a int, b int NOT NULL} \
          210  +    {x int NOT NULL, y int NOT NULL}
          211  +
          212  +# Do not run the optimization if any corresponding table
          213  +# columns have different affinities.
          214  +#
          215  +xfer_check insert4-3.20 0 {1 9} \
          216  +    {a text, b int} \
          217  +    {x int, b int}
          218  +xfer_check insert4-3.21 0 {1 9} \
          219  +    {a int, b int} \
          220  +    {x text, b int}
          221  +
          222  +# "int" and "integer" are equivalent so the optimization should
          223  +# run here.
          224  +#
          225  +xfer_check insert4-3.22 1 {1 9} \
          226  +    {a int, b int} \
          227  +    {x integer, b int}
          228  +
   123    229   
   124    230   finish_test

Added test/tkt2251.test.

            1  +# 2007 Febuary 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.
           12  +#
           13  +# This file implements tests to verify that table column values
           14  +# are pulled out of the database correctly.
           15  +#
           16  +# Long ago, the OP_Column opcode was sufficient to pull out the
           17  +# value of a table column.  But then we added the ALTER TABLE ADD COLUMN
           18  +# feature.  An added column might not actually exist in every row,
           19  +# and so the OP_Column opcode has to contain a default value.  Later
           20  +# still we added a feature whereby a REAL value with no fractional
           21  +# part is stored in the database file as an integer to save space.
           22  +# After extracting the value, we have to call OP_RealAffinity to
           23  +# convert it back to a REAL.
           24  +#
           25  +# The sqlite3ExprCodeGetColumn() routine was added to take care of
           26  +# all of the complications above.  The tests in this file attempt
           27  +# to verify that sqlite3ExprCodeGetColumn() is used instead of a
           28  +# raw OP_Column in all places where a table column is extracted from
           29  +# the database.
           30  +#
           31  +# $Id: tkt2251.test,v 1.1 2007/02/24 15:18:51 drh Exp $
           32  +
           33  +set testdir [file dirname $argv0]
           34  +source $testdir/tester.tcl
           35  +
           36  +# Create sample data.  Verify that the default value and type of an added
           37  +# column is correct for aggregates.
           38  +do_test tkt2251-1.1 {
           39  +  execsql {
           40  +    CREATE TABLE t1(a INTEGER);
           41  +    INSERT INTO t1 VALUES(1);
           42  +    INSERT INTO t1 VALUES(1);
           43  +    INSERT INTO t1 VALUES(2);
           44  +    INSERT INTO t1 VALUES(9);
           45  +    INSERT INTO t1 VALUES(9);
           46  +    INSERT INTO t1 VALUES(9);
           47  +    INSERT INTO t1 VALUES(3);
           48  +    INSERT INTO t1 VALUES(2);
           49  +    ALTER TABLE t1 ADD COLUMN b REAL DEFAULT 4.0;
           50  +    SELECT avg(b), typeof(avg(b)) FROM t1;
           51  +  }
           52  +} {4.0 real}
           53  +do_test tkt2251-1.2 {
           54  +  execsql {
           55  +    SELECT sum(b), typeof(sum(b)) FROM t1;
           56  +  }
           57  +} {32.0 real}
           58  +do_test tkt2251-1.3 {
           59  +  execsql {
           60  +    SELECT a, sum(b), typeof(sum(b)) FROM t1 GROUP BY a ORDER BY a;
           61  +  }
           62  +} {1 8.0 real 2 8.0 real 3 4.0 real 9 12.0 real}
           63  +
           64  +# Make sure that the REAL value comes out when values are accessed
           65  +# by index.
           66  +#
           67  +do_test tkt2251-2.1 {
           68  +  execsql {
           69  +    SELECT b, typeof(b) FROM t1 WHERE a=3;
           70  +  }
           71  +} {4.0 real}
           72  +do_test tkt2251-2.2 {
           73  +  execsql {
           74  +    CREATE INDEX t1i1 ON t1(a,b);
           75  +    SELECT b, typeof(b) FROM t1 WHERE a=3;
           76  +  }
           77  +} {4.0 real}
           78  +do_test tkt2251-2.3 {
           79  +  execsql {
           80  +    REINDEX;
           81  +    SELECT b, typeof(b) FROM t1 WHERE a=3;
           82  +  }
           83  +} {4.0 real}
           84  +
           85  +# Make sure the correct REAL value is used when copying from one
           86  +# table to another.
           87  +#
           88  +do_test tkt2251-3.1 {
           89  +  execsql {
           90  +    CREATE TABLE t2(x,y);
           91  +    INSERT INTO t2 SELECT * FROM t1;
           92  +    SELECT y, typeof(y) FROM t2 WHERE x=3;
           93  +  }
           94  +} {4.0 real}
           95  +do_test tkt2251-3.2 {
           96  +  execsql {
           97  +    CREATE TABLE t3 AS SELECT * FROM t1;
           98  +    SELECT b, typeof(b) FROM t3 WHERE a=3;
           99  +  }
          100  +} {4.0 real}
          101  +
          102  +
          103  +finish_test