Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Unless the destination table is completely empty, disable the xfer optimization for WITHOUT ROWID tables. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | omit-rowid |
Files: | files | file ages | folders |
SHA1: |
3877c9f50582b51817dcf3cd75d83689 |
User & Date: | dan 2013-11-05 16:39:31.103 |
Context
2013-11-05
| ||
16:56 | Remove an "explain" command from test script without_rowid1.test that was accidentally committed. (check-in: 4b41d989e8 user: dan tags: omit-rowid) | |
16:39 | Unless the destination table is completely empty, disable the xfer optimization for WITHOUT ROWID tables. (check-in: 3877c9f505 user: dan tags: omit-rowid) | |
15:02 | Updates to the backcompat.test test script so that it works with really old (3.6.*) versions. (check-in: ace7e7b642 user: dan tags: omit-rowid) | |
Changes
Changes to src/insert.c.
︙ | ︙ | |||
1893 1894 1895 1896 1897 1898 1899 | } if( pDest->nCol!=pSrc->nCol ){ return 0; /* Number of columns must be the same in tab1 and tab2 */ } if( pDest->iPKey!=pSrc->iPKey ){ return 0; /* Both tables must have the same INTEGER PRIMARY KEY */ } | < < < | 1893 1894 1895 1896 1897 1898 1899 1900 1901 1902 1903 1904 1905 1906 | } if( pDest->nCol!=pSrc->nCol ){ return 0; /* Number of columns must be the same in tab1 and tab2 */ } if( pDest->iPKey!=pSrc->iPKey ){ return 0; /* Both tables must have the same INTEGER PRIMARY KEY */ } for(i=0; i<pDest->nCol; i++){ if( pDest->aCol[i].affinity!=pSrc->aCol[i].affinity ){ return 0; /* Affinity must be the same on all columns */ } if( !xferCompatibleCollation(pDest->aCol[i].zColl, pSrc->aCol[i].zColl) ){ return 0; /* Collating sequence must be the same on all columns */ } |
︙ | ︙ | |||
1954 1955 1956 1957 1958 1959 1960 | v = sqlite3GetVdbe(pParse); sqlite3CodeVerifySchema(pParse, iDbSrc); iSrc = pParse->nTab++; iDest = pParse->nTab++; regAutoinc = autoIncBegin(pParse, iDbDest, pDest); regData = sqlite3GetTempReg(pParse); regRowid = sqlite3GetTempReg(pParse); | < | > | | | | | | | | | | | | | | | | | | | | | < < | > | 1951 1952 1953 1954 1955 1956 1957 1958 1959 1960 1961 1962 1963 1964 1965 1966 1967 1968 1969 1970 1971 1972 1973 1974 1975 1976 1977 1978 1979 1980 1981 1982 1983 1984 1985 1986 1987 1988 1989 | v = sqlite3GetVdbe(pParse); sqlite3CodeVerifySchema(pParse, iDbSrc); iSrc = pParse->nTab++; iDest = pParse->nTab++; regAutoinc = autoIncBegin(pParse, iDbDest, pDest); regData = sqlite3GetTempReg(pParse); regRowid = sqlite3GetTempReg(pParse); sqlite3OpenTable(pParse, iDest, iDbDest, pDest, OP_OpenWrite); assert( HasRowid(pDest) || destHasUniqueIdx ); if( (pDest->iPKey<0 && pDest->pIndex!=0) /* (1) */ || destHasUniqueIdx /* (2) */ || (onError!=OE_Abort && onError!=OE_Rollback) /* (3) */ ){ /* In some circumstances, we are able to run the xfer optimization ** only if the destination table is initially empty. This code makes ** that determination. Conditions under which the destination must ** be empty: ** ** (1) There is no INTEGER PRIMARY KEY but there are indices. ** (If the destination is not initially empty, the rowid fields ** of index entries might need to change.) ** ** (2) The destination has a unique index. (The xfer optimization ** is unable to test uniqueness.) ** ** (3) onError is something other than OE_Abort and OE_Rollback. */ addr1 = sqlite3VdbeAddOp2(v, OP_Rewind, iDest, 0); emptyDestTest = sqlite3VdbeAddOp2(v, OP_Goto, 0, 0); sqlite3VdbeJumpHere(v, addr1); } if( HasRowid(pSrc) ){ sqlite3OpenTable(pParse, iSrc, iDbSrc, pSrc, OP_OpenRead); emptySrcTest = sqlite3VdbeAddOp2(v, OP_Rewind, iSrc, 0); if( pDest->iPKey>=0 ){ addr1 = sqlite3VdbeAddOp2(v, OP_Rowid, iSrc, regRowid); addr2 = sqlite3VdbeAddOp3(v, OP_NotExists, iDest, 0, regRowid); sqlite3RowidConstraint(pParse, onError, pDest); sqlite3VdbeJumpHere(v, addr2); |
︙ | ︙ |
Changes to test/tester.tcl.
︙ | ︙ | |||
1024 1025 1026 1027 1028 1029 1030 | } proc explain_i {sql {db db}} { puts "" puts "addr opcode p1 p2 p3 p4 p5 #" puts "---- ------------ ------ ------ ------ ---------------- -- -" | < > | > | > > > > | < < < < | 1024 1025 1026 1027 1028 1029 1030 1031 1032 1033 1034 1035 1036 1037 1038 1039 1040 1041 1042 1043 1044 1045 1046 1047 1048 1049 1050 1051 1052 1053 1054 1055 1056 1057 1058 1059 1060 1061 1062 1063 1064 1065 1066 1067 1068 1069 1070 1071 1072 1073 | } proc explain_i {sql {db db}} { puts "" puts "addr opcode p1 p2 p3 p4 p5 #" puts "---- ------------ ------ ------ ------ ---------------- -- -" set bSeenGoto 0 $db eval "explain $sql" {} { set x($addr) 0 set op($addr) $opcode if {$opcode == "Goto" && ($bSeenGoto==0 || ($p2 > $addr+10))} { set linebreak($p2) 1 set bSeenGoto 1 } if {$opcode == "Next"} { for {set i $p2} {$i<$addr} {incr i} { incr x($i) 2 } } if {$opcode == "Goto" && $p2<$addr && $op($p2)=="Yield"} { for {set i [expr $p2+1]} {$i<$addr} {incr i} { incr x($i) 2 } } if {$opcode == "Halt" && $comment == "End of coroutine"} { set linebreak([expr $addr+1]) 1 } } $db eval "explain $sql" {} { if {[info exists linebreak($addr)]} { puts "" } set I [string repeat " " $x($addr)] puts [format {%-4d %s%-12.12s %-6d %-6d %-6d % -17s %s %s} \ $addr $I $opcode $p1 $p2 $p3 $p4 $p5 $comment ] } puts "---- ------------ ------ ------ ------ ---------------- -- -" } # Show the VDBE program for an SQL statement but omit the Trace # opcode at the beginning. This procedure can be used to prove # that different SQL statements generate exactly the same VDBE code. |
︙ | ︙ |
Changes to test/without_rowid1.test.
︙ | ︙ | |||
149 150 151 152 153 154 155 156 157 158 | } {} do_execsql_test 2.4.2 { SELECT * FROM t6 ORDER BY b, a; SELECT * FROM t6 ORDER BY c; } {ABC def ghi ABC def ghi} finish_test | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 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 | } {} do_execsql_test 2.4.2 { SELECT * FROM t6 ORDER BY b, a; SELECT * FROM t6 ORDER BY c; } {ABC def ghi ABC def ghi} #------------------------------------------------------------------------- # Unless the destination table is completely empty, the xfer optimization # is disabled for WITHOUT ROWID tables. The following tests check for # some problems that might occur if this were not the case. # reset_db do_execsql_test 3.1.1 { CREATE TABLE t1(a, b, PRIMARY KEY(a)) WITHOUT ROWID; CREATE UNIQUE INDEX i1 ON t1(b); CREATE TABLE t2(a, b, PRIMARY KEY(a)) WITHOUT ROWID; CREATE UNIQUE INDEX i2 ON t2(b); INSERT INTO t1 VALUES('one', 'two'); INSERT INTO t2 VALUES('three', 'two'); } do_execsql_test 3.1.2 { INSERT OR REPLACE INTO t1 SELECT * FROM t2; SELECT * FROM t1; } {three two} do_execsql_test 3.1.3 { DELETE FROM t1; INSERT INTO t1 SELECT * FROM t2; SELECT * FROM t1; } {three two} do_catchsql_test 3.1.4 { INSERT INTO t2 VALUES('four', 'four'); INSERT INTO t2 VALUES('six', 'two'); INSERT INTO t1 SELECT * FROM t2; } {1 {UNIQUE constraint failed: t2.b}} do_execsql_test 3.1.5 { CREATE TABLE t3(a PRIMARY KEY); CREATE TABLE t4(a PRIMARY KEY); INSERT INTO t4 VALUES('i'); INSERT INTO t4 VALUES('ii'); INSERT INTO t4 VALUES('iii'); INSERT INTO t3 SELECT * FROM t4; SELECT * FROM t3; } {i ii iii} explain_i { INSERT INTO t3 SELECT * FROM t4; } finish_test |