Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Use a intermediate table when inserting a TEMP table from a SELECT that reads from that same TEMP table. Ticket #275. (CVS 895) |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
087d1e83af12b3a9aedd4945f02774a1 |
User & Date: | drh 2003-04-03 01:50:44.000 |
Context
2003-04-03
| ||
15:46 | Added experimental APIs: sqlite_begin_hook() and sqlite_commit_hook(). (CVS 896) (check-in: 5efbf62313 user: drh tags: trunk) | |
01:50 | Use a intermediate table when inserting a TEMP table from a SELECT that reads from that same TEMP table. Ticket #275. (CVS 895) (check-in: 087d1e83af user: drh tags: trunk) | |
2003-04-01
| ||
21:16 | Add infrastructure to suport multiple btree implementations (CVS 894) (check-in: 79b3aed2a7 user: paul tags: trunk) | |
Changes
Changes to src/insert.c.
︙ | ︙ | |||
8 9 10 11 12 13 14 | ** 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. ** | | | 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.78 2003/04/03 01:50:44 drh Exp $ */ #include "sqliteInt.h" /* ** This routine is call to handle SQL of the following forms: ** ** insert into TABLE (IDLIST) values(EXPRLIST) |
︙ | ︙ | |||
173 174 175 176 177 178 179 | ** subroutine is also coded here. That subroutine stores the SELECT ** results in a temporary table. (Template 3.) */ if( pSelect ){ /* Data is coming from a SELECT. Generate code to implement that SELECT */ int rc, iInitCode; | < > > > > > > > | | > > > > > > > | 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 | ** subroutine is also coded here. That subroutine stores the SELECT ** results in a temporary table. (Template 3.) */ if( pSelect ){ /* Data is coming from a SELECT. Generate code to implement that SELECT */ int rc, iInitCode; iInitCode = sqliteVdbeAddOp(v, OP_Goto, 0, 0); iSelectLoop = sqliteVdbeCurrentAddr(v); iInsertBlock = sqliteVdbeMakeLabel(v); rc = sqliteSelect(pParse, pSelect, SRT_Subroutine, iInsertBlock, 0,0,0); if( rc || pParse->nErr || sqlite_malloc_failed ) goto insert_cleanup; iCleanup = sqliteVdbeMakeLabel(v); sqliteVdbeAddOp(v, OP_Goto, 0, iCleanup); assert( pSelect->pEList ); nColumn = pSelect->pEList->nExpr; /* Set useTempTable to TRUE if the result of the SELECT statement ** should be written into a temporary table. Set to FALSE if each ** row of the SELECT can be written directly into the result table. ** ** A temp table must be used if the table being updated is also one ** of the tables being read by the SELECT statement. Also use a ** temp table in the case of row triggers. */ if( row_triggers_exist ){ useTempTable = 1; }else{ int addr = sqliteVdbeFindOp(v, OP_OpenRead, pTab->tnum); useTempTable = 0; if( addr>0 ){ VdbeOp *pOp = sqliteVdbeGetOp(v, addr-2); if( pOp->opcode==OP_Integer && pOp->p1==pTab->iDb ){ useTempTable = 1; } } } if( useTempTable ){ /* Generate the subroutine that SELECT calls to process each row of ** the result. Store the result in a temporary table */ srcTab = pParse->nTab++; sqliteVdbeResolveLabel(v, iInsertBlock); |
︙ | ︙ |
Changes to src/vdbe.c.
︙ | ︙ | |||
32 33 34 35 36 37 38 | ** ** Various scripts scan this source file in order to generate HTML ** documentation, headers files, or other derived files. The formatting ** of the code in this file is, therefore, important. See other comments ** in this file for details. If in doubt, do not deviate from existing ** commenting and indentation practices when changing or adding code. ** | | | 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 | ** ** Various scripts scan this source file in order to generate HTML ** documentation, headers files, or other derived files. The formatting ** of the code in this file is, therefore, important. See other comments ** in this file for details. If in doubt, do not deviate from existing ** commenting and indentation practices when changing or adding code. ** ** $Id: vdbe.c,v 1.210 2003/04/03 01:50:45 drh Exp $ */ #include "sqliteInt.h" #include <ctype.h> /* ** The makefile scans this source file and creates the following ** array of string constants which are the names of all VDBE opcodes. |
︙ | ︙ | |||
599 600 601 602 603 604 605 | } while( j>0 && isspace(z[j-1]) ){ j--; } z[j] = 0; } /* ** Search for the current program for the given opcode and P2 | | | > > > > > > > > > | 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 | } while( j>0 && isspace(z[j-1]) ){ j--; } z[j] = 0; } /* ** Search for the current program for the given opcode and P2 ** value. Return the address plus 1 if found and 0 if not found. */ int sqliteVdbeFindOp(Vdbe *p, int op, int p2){ int i; assert( p->magic==VDBE_MAGIC_INIT ); for(i=0; i<p->nOp; i++){ if( p->aOp[i].opcode==op && p->aOp[i].p2==p2 ) return i+1; } return 0; } /* ** Return the opcode for a given address. */ VdbeOp *sqliteVdbeGetOp(Vdbe *p, int addr){ assert( p->magic==VDBE_MAGIC_INIT ); assert( addr>=0 && addr<p->nOp ); return &p->aOp[addr]; } /* ** The following group or routines are employed by installable functions ** to return their results. ** ** The sqlite_set_result_string() routine can be used to return a string ** value or to return a NULL. To return a NULL, pass in NULL for zResult. |
︙ | ︙ |
Changes to src/vdbe.h.
︙ | ︙ | |||
11 12 13 14 15 16 17 | ************************************************************************* ** Header file for the Virtual DataBase Engine (VDBE) ** ** This header defines the interface to the virtual database engine ** or VDBE. The VDBE implements an abstract machine that runs a ** simple program to access and modify the underlying database. ** | | | 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | ************************************************************************* ** Header file for the Virtual DataBase Engine (VDBE) ** ** This header defines the interface to the virtual database engine ** or VDBE. The VDBE implements an abstract machine that runs a ** simple program to access and modify the underlying database. ** ** $Id: vdbe.h,v 1.65 2003/04/03 01:50:47 drh Exp $ */ #ifndef _SQLITE_VDBE_H_ #define _SQLITE_VDBE_H_ #include <stdio.h> /* ** A single VDBE is an opaque structure named "Vdbe". Only routines |
︙ | ︙ | |||
77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 | int sqliteVdbeAddOp(Vdbe*,int,int,int); int sqliteVdbeAddOpList(Vdbe*, int nOp, VdbeOp const *aOp); void sqliteVdbeChangeP1(Vdbe*, int addr, int P1); void sqliteVdbeChangeP2(Vdbe*, int addr, int P2); void sqliteVdbeChangeP3(Vdbe*, int addr, const char *zP1, int N); void sqliteVdbeDequoteP3(Vdbe*, int addr); int sqliteVdbeFindOp(Vdbe*, int, int); int sqliteVdbeMakeLabel(Vdbe*); void sqliteVdbeDelete(Vdbe*); void sqliteVdbeMakeReady(Vdbe*,sqlite_callback,void*,int); int sqliteVdbeExec(Vdbe*); int sqliteVdbeList(Vdbe*); int sqliteVdbeFinalize(Vdbe*,char**); void sqliteVdbeResolveLabel(Vdbe*, int); int sqliteVdbeCurrentAddr(Vdbe*); void sqliteVdbeTrace(Vdbe*,FILE*); void sqliteVdbeCompressSpace(Vdbe*,int); #endif | > | 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 | int sqliteVdbeAddOp(Vdbe*,int,int,int); int sqliteVdbeAddOpList(Vdbe*, int nOp, VdbeOp const *aOp); void sqliteVdbeChangeP1(Vdbe*, int addr, int P1); void sqliteVdbeChangeP2(Vdbe*, int addr, int P2); void sqliteVdbeChangeP3(Vdbe*, int addr, const char *zP1, int N); void sqliteVdbeDequoteP3(Vdbe*, int addr); int sqliteVdbeFindOp(Vdbe*, int, int); VdbeOp *sqliteVdbeGetOp(Vdbe*, int); int sqliteVdbeMakeLabel(Vdbe*); void sqliteVdbeDelete(Vdbe*); void sqliteVdbeMakeReady(Vdbe*,sqlite_callback,void*,int); int sqliteVdbeExec(Vdbe*); int sqliteVdbeList(Vdbe*); int sqliteVdbeFinalize(Vdbe*,char**); void sqliteVdbeResolveLabel(Vdbe*, int); int sqliteVdbeCurrentAddr(Vdbe*); void sqliteVdbeTrace(Vdbe*,FILE*); void sqliteVdbeCompressSpace(Vdbe*,int); #endif |
Changes to test/insert.test.
1 2 3 4 5 6 7 8 9 10 11 12 13 | # 2001 September 15 # # 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 statement. # | | | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | # 2001 September 15 # # 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 statement. # # $Id: insert.test,v 1.12 2003/04/03 01:50:48 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Try to insert into a non-existant table. # do_test insert-1.1 { |
︙ | ︙ | |||
198 199 200 201 202 203 204 | do_test insert-4.7 { execsql { INSERT INTO t3 VALUES(min(1,2,3),max(1,2,3),99); SELECT * FROM t3 WHERE c=99; } } {1 3 99} | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | | 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 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 | do_test insert-4.7 { execsql { INSERT INTO t3 VALUES(min(1,2,3),max(1,2,3),99); SELECT * FROM t3 WHERE c=99; } } {1 3 99} # Test the ability to insert from a temporary table into itself. # Ticket #275. # do_test insert-5.1 { execsql { CREATE TEMP TABLE t4(x); INSERT INTO t4 VALUES(1); SELECT * FROM t4; } } {1} do_test insert-5.2 { execsql { INSERT INTO t4 SELECT x+1 FROM t4; SELECT * FROM t4; } } {1 2} do_test insert-5.3 { # verify that a temporary table is used to copy t4 to t4 set x [execsql { EXPLAIN INSERT INTO t4 SELECT x+2 FROM t4; }] expr {[lsearch $x OpenTemp]>0} } {1} do_test insert-5.4 { # Verify that table "test1" begins on page 3. This should be the same # page number used by "t4" above. execsql { SELECT rootpage FROM sqlite_master WHERE name='test1'; } } {3} do_test insert-5.5 { # Verify that "t4" begins on page 3. execsql { SELECT rootpage FROM sqlite_temp_master WHERE name='t4'; } } {3} do_test insert-5.6 { # This should not use an intermediate temporary table. execsql { INSERT INTO t4 SELECT one FROM test1 WHERE three=7; SELECT * FROM t4 } } {1 2 8} do_test insert-5.7 { # verify that no temporary table is used to copy test1 to t4 set x [execsql { EXPLAIN INSERT INTO t4 SELECT one FROM test1; }] expr {[lsearch $x OpenTemp]>0} } {0} finish_test |