/ 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 Unified Diffs Show Whitespace Changes Patch

Changes to src/insert.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
....
1458
1459
1460
1461
1462
1463
1464
1465
1466
1467
1468
1469
1470
1471
1472
**    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:
................................................................................
    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







|







 







|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
....
1458
1459
1460
1461
1462
1463
1464
1465
1466
1467
1468
1469
1470
1471
1472
**    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.176 2007/02/24 15:18:50 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:
................................................................................
    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( pDest->pCheck && !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

Changes to test/insert4.test.

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

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







|













>
>
>
>
>
>
>
>
>







|
|







 







>








<
|









|
>







 








>
>
>
>
>
>
>
>
|
<
>
>
>

<
>
>
>
>
|
>
|
<
|
<
<
|
|
<
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|
>
>
>
>
>

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

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
..
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
...
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127

128
129
130
131

132
133
134
135
136
137
138

139


140
141

142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
#    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.3 2007/02/24 15:18:50 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
}

# Create tables used for testing.
#
execsql {
  CREATE TABLE t1(a int, b int, check(b>a));
  CREATE TABLE t2(x int, y int);
  CREATE VIEW v2 AS SELECT y, x FROM t2;
  CREATE TABLE t3(a int, b int);
}

# 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 {
    DELETE FROM t1;
    DELETE FROM t2;
    INSERT INTO t2 VALUES(9,1);
  }
  catchsql {
    INSERT INTO t1 SELECT * FROM t2;
  }
} {1 {constraint failed}}
xferopt_test insert4-1.2 0
................................................................................

# 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 {
    DELETE FROM t1;
    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;

    INSERT INTO t1 SELECT * FROM v2;
    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 {
    DELETE FROM t2;
    INSERT INTO t2 VALUES(9,1);
    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 {
................................................................................
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

# The following procedure constructs two tables then tries to transfer
# data from one table to the other.  Checks are made to make sure the
# transfer is successful and that the transfer optimization was used or
# not, as appropriate.
#
#     xfer_check TESTID  XFER-USED   INIT-DATA   DEST-SCHEMA   SRC-SCHEMA 
#
# The TESTID argument is the symbolic name for this test.  The XFER-USED
# argument is true if the transfer optimization should be employed and

# false if not.  INIT-DATA is a single row of data that is to be 
# transfered.  DEST-SCHEMA and SRC-SCHEMA are table declarations for
# the destination and source tables.
#

proc xfer_check {testid xferused initdata destschema srcschema} {
  execsql "CREATE TABLE dest($destschema)"
  execsql "CREATE TABLE src($srcschema)"
  execsql "INSERT INTO src VALUES([join $initdata ,])"
  set ::sqlite3_xferopt_count 0
  do_test $testid.1 {
    execsql {

      INSERT INTO dest SELECT * FROM src;


      SELECT * FROM dest;
    }

  } $initdata
  do_test $testid.2 {
    set ::sqlite3_xferopt_count
  } $xferused
  execsql {
    DROP TABLE dest;
    DROP TABLE src;
  }
}


# Do run the transfer optimization if tables have identical
# CHECK constraints.
#
xfer_check insert4-3.1 1 {1 9} \
    {a int, b int CHECK(b>a)} \
    {x int, y int CHECK(y>x)}
xfer_check insert4-3.2 1 {1 9} \
    {a int, b int CHECK(b>a)} \
    {x int CHECK(y>x), y int}

# Do run the transfer optimization if the destination table lacks
# any CHECK constraints regardless of whether or not there are CHECK
# constraints on the source table.
#
xfer_check insert4-3.3 1 {1 9} \
    {a int, b int} \
    {x int, y int CHECK(y>x)}

# Do run the transfer optimization if the destination table omits
# NOT NULL constraints that the source table has.
#
xfer_check insert4-3.4 0 {1 9} \
    {a int, b int CHECK(b>a)} \
    {x int, y int}

# Do not run the optimization if the destination has NOT NULL
# constraints that the source table lacks.
#
xfer_check insert4-3.5 0 {1 9} \
    {a int, b int NOT NULL} \
    {x int, y int}
xfer_check insert4-3.6 0 {1 9} \
    {a int, b int NOT NULL} \
    {x int NOT NULL, y int}
xfer_check insert4-3.7 0 {1 9} \
    {a int NOT NULL, b int NOT NULL} \
    {x int NOT NULL, y int}
xfer_check insert4-3.8 0 {1 9} \
    {a int NOT NULL, b int} \
    {x int, y int}


# Do run the transfer optimization if the destination table and
# source table have the same NOT NULL constraints or if the 
# source table has extra NOT NULL constraints.
#
xfer_check insert4-3.9 1 {1 9} \
    {a int, b int} \
    {x int NOT NULL, y int}
xfer_check insert4-3.10 1 {1 9} \
    {a int, b int} \
    {x int NOT NULL, y int NOT NULL}
xfer_check insert4-3.11 1 {1 9} \
    {a int NOT NULL, b int} \
    {x int NOT NULL, y int NOT NULL}
xfer_check insert4-3.12 1 {1 9} \
    {a int, b int NOT NULL} \
    {x int NOT NULL, y int NOT NULL}

# Do not run the optimization if any corresponding table
# columns have different affinities.
#
xfer_check insert4-3.20 0 {1 9} \
    {a text, b int} \
    {x int, b int}
xfer_check insert4-3.21 0 {1 9} \
    {a int, b int} \
    {x text, b int}

# "int" and "integer" are equivalent so the optimization should
# run here.
#
xfer_check insert4-3.22 1 {1 9} \
    {a int, b int} \
    {x integer, b int}


finish_test

Added test/tkt2251.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
# 2007 Febuary 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.
#
# This file implements tests to verify that table column values
# are pulled out of the database correctly.
#
# Long ago, the OP_Column opcode was sufficient to pull out the
# value of a table column.  But then we added the ALTER TABLE ADD COLUMN
# feature.  An added column might not actually exist in every row,
# and so the OP_Column opcode has to contain a default value.  Later
# still we added a feature whereby a REAL value with no fractional
# part is stored in the database file as an integer to save space.
# After extracting the value, we have to call OP_RealAffinity to
# convert it back to a REAL.
#
# The sqlite3ExprCodeGetColumn() routine was added to take care of
# all of the complications above.  The tests in this file attempt
# to verify that sqlite3ExprCodeGetColumn() is used instead of a
# raw OP_Column in all places where a table column is extracted from
# the database.
#
# $Id: tkt2251.test,v 1.1 2007/02/24 15:18:51 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Create sample data.  Verify that the default value and type of an added
# column is correct for aggregates.
do_test tkt2251-1.1 {
  execsql {
    CREATE TABLE t1(a INTEGER);
    INSERT INTO t1 VALUES(1);
    INSERT INTO t1 VALUES(1);
    INSERT INTO t1 VALUES(2);
    INSERT INTO t1 VALUES(9);
    INSERT INTO t1 VALUES(9);
    INSERT INTO t1 VALUES(9);
    INSERT INTO t1 VALUES(3);
    INSERT INTO t1 VALUES(2);
    ALTER TABLE t1 ADD COLUMN b REAL DEFAULT 4.0;
    SELECT avg(b), typeof(avg(b)) FROM t1;
  }
} {4.0 real}
do_test tkt2251-1.2 {
  execsql {
    SELECT sum(b), typeof(sum(b)) FROM t1;
  }
} {32.0 real}
do_test tkt2251-1.3 {
  execsql {
    SELECT a, sum(b), typeof(sum(b)) FROM t1 GROUP BY a ORDER BY a;
  }
} {1 8.0 real 2 8.0 real 3 4.0 real 9 12.0 real}

# Make sure that the REAL value comes out when values are accessed
# by index.
#
do_test tkt2251-2.1 {
  execsql {
    SELECT b, typeof(b) FROM t1 WHERE a=3;
  }
} {4.0 real}
do_test tkt2251-2.2 {
  execsql {
    CREATE INDEX t1i1 ON t1(a,b);
    SELECT b, typeof(b) FROM t1 WHERE a=3;
  }
} {4.0 real}
do_test tkt2251-2.3 {
  execsql {
    REINDEX;
    SELECT b, typeof(b) FROM t1 WHERE a=3;
  }
} {4.0 real}

# Make sure the correct REAL value is used when copying from one
# table to another.
#
do_test tkt2251-3.1 {
  execsql {
    CREATE TABLE t2(x,y);
    INSERT INTO t2 SELECT * FROM t1;
    SELECT y, typeof(y) FROM t2 WHERE x=3;
  }
} {4.0 real}
do_test tkt2251-3.2 {
  execsql {
    CREATE TABLE t3 AS SELECT * FROM t1;
    SELECT b, typeof(b) FROM t3 WHERE a=3;
  }
} {4.0 real}


finish_test