/ Check-in [3877c9f5]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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 | SQL archive
Timelines: family | ancestors | descendants | both | omit-rowid
Files: files | file ages | folders
SHA1: 3877c9f50582b51817dcf3cd75d836891a34e590
User & Date: dan 2013-11-05 16:39:31
Context
2013-11-05
16:56
Remove an "explain" command from test script without_rowid1.test that was accidentally committed. check-in: 4b41d989 user: dan tags: omit-rowid
16:39
Unless the destination table is completely empty, disable the xfer optimization for WITHOUT ROWID tables. check-in: 3877c9f5 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: ace7e7b6 user: dan tags: omit-rowid
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/insert.c.

1893
1894
1895
1896
1897
1898
1899
1900
1901
1902
1903
1904
1905
1906
1907
1908
1909
....
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
1990
1991
1992
1993
  }
  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 */
  }
  if( HasRowid(pDest)!=HasRowid(pSrc) ){
    return 0;   /* source and destination must both be WITHOUT ROWID or not */
  }
  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 */
    }
................................................................................
  v = sqlite3GetVdbe(pParse);
  sqlite3CodeVerifySchema(pParse, iDbSrc);
  iSrc = pParse->nTab++;
  iDest = pParse->nTab++;
  regAutoinc = autoIncBegin(pParse, iDbDest, pDest);
  regData = sqlite3GetTempReg(pParse);
  regRowid = sqlite3GetTempReg(pParse);
  if( HasRowid(pSrc) ){
    sqlite3OpenTable(pParse, iDest, iDbDest, pDest, OP_OpenWrite);

    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);
    }else{
      emptyDestTest = 0;
    }

    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);







<
<
<







 







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







1893
1894
1895
1896
1897
1898
1899



1900
1901
1902
1903
1904
1905
1906
....
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
  }
  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 */
    }
................................................................................
  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
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
}

proc explain_i {sql {db db}} {
  puts ""
  puts "addr  opcode        p1      p2      p3      p4                p5  #"
  puts "----  ------------  ------  ------  ------  ----------------  --  -"

  set addrTail 0


  $db eval "explain $sql" {} {
    set x($addr) 0
    set op($addr) $opcode

    if {$opcode == "Goto" && $addrTail==0} {
      set addrTail $p2

    }

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




  }

  $db eval "explain $sql" {} {
    if {$addr == $addrTail} {
      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
    ]

    if {$opcode == "Halt" && $comment == "End of coroutine"} {
      puts ""
    }
  }
  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.







<

>




|
|
>













>
>
>
>



|






<
<
<
<







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