/ Check-in [349f4834]
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:Allow the xfer optimization to proceed if the DEFAULT on the very first column of the two tables is different. This is a refinement of the fix for ticket [f67b41381a].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 349f483499dd685a8da94923b6bd810a52e5e236
User & Date: drh 2014-04-26 17:52:08
Context
2014-04-26
19:23
Update requirements marks to fix typos in the requirements text. No changes to code. check-in: f5a26365 user: drh tags: trunk
17:52
Allow the xfer optimization to proceed if the DEFAULT on the very first column of the two tables is different. This is a refinement of the fix for ticket [f67b41381a]. check-in: 349f4834 user: drh tags: trunk
14:07
Avoid transfering records between tables unless the default values for all columns are the same. Fix for [f67b41381a]. check-in: f8c4c495 user: dan tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/insert.c.

1872
1873
1874
1875
1876
1877
1878


1879
1880
1881
1882
1883
1884
1885
1886
1887
    }
    if( !xferCompatibleCollation(pDestCol->zColl, pSrcCol->zColl) ){
      return 0;    /* Collating sequence must be the same on all columns */
    }
    if( pDestCol->notNull && !pSrcCol->notNull ){
      return 0;    /* tab2 must be NOT NULL if tab1 is */
    }


    if( (pDestCol->zDflt==0)!=(pSrcCol->zDflt==0) 
     || (pDestCol->zDflt && strcmp(pDestCol->zDflt, pSrcCol->zDflt))
    ){
      return 0;    /* Default values must be the same for all columns */
    }
  }
  for(pDestIdx=pDest->pIndex; pDestIdx; pDestIdx=pDestIdx->pNext){
    if( pDestIdx->onError!=OE_None ){
      destHasUniqueIdx = 1;







>
>
|
|







1872
1873
1874
1875
1876
1877
1878
1879
1880
1881
1882
1883
1884
1885
1886
1887
1888
1889
    }
    if( !xferCompatibleCollation(pDestCol->zColl, pSrcCol->zColl) ){
      return 0;    /* Collating sequence must be the same on all columns */
    }
    if( pDestCol->notNull && !pSrcCol->notNull ){
      return 0;    /* tab2 must be NOT NULL if tab1 is */
    }
    /* Default values for second and subsequent columns need to match. */
    if( i>0
     && ((pDestCol->zDflt==0)!=(pSrcCol->zDflt==0) 
         || (pDestCol->zDflt && strcmp(pDestCol->zDflt, pSrcCol->zDflt)!=0))
    ){
      return 0;    /* Default values must be the same for all columns */
    }
  }
  for(pDestIdx=pDest->pIndex; pDestIdx; pDestIdx=pDestIdx->pNext){
    if( pDestIdx->onError!=OE_None ){
      destHasUniqueIdx = 1;

Changes to test/tkt-f67b41381a.test.

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

db cache size 0
foreach {tn tbls xfer} {
  1 { CREATE TABLE t1(a, b); CREATE TABLE t2(a, b)             }             1
  2 { CREATE TABLE t1(a, b DEFAULT 'x'); CREATE TABLE t2(a, b) }             0
  3 { CREATE TABLE t1(a, b DEFAULT 'x'); CREATE TABLE t2(a, b DEFAULT 'x') } 1
  4 { CREATE TABLE t1(a, b DEFAULT NULL); CREATE TABLE t2(a, b) }            0
  5 { CREATE TABLE t1(a DEFAULT 2, b); CREATE TABLE t2(a DEFAULT 1, b) }     0
  6 { CREATE TABLE t1(a DEFAULT 1, b); CREATE TABLE t2(a DEFAULT 1, b) }     1





} {

  execsql { DROP TABLE t1; DROP TABLE t2 }
  execsql $tbls

  set res 1
  db eval { EXPLAIN INSERT INTO t1 SELECT * FROM t2 } {
................................................................................
    if {$opcode == "Column"} { set res 0 }
  }

  do_test 2.$tn [list set res] $xfer
}

finish_test









|

>
>
>
>
>







 







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



db cache size 0
foreach {tn tbls xfer} {
  1 { CREATE TABLE t1(a, b); CREATE TABLE t2(a, b)             }             1
  2 { CREATE TABLE t1(a, b DEFAULT 'x'); CREATE TABLE t2(a, b) }             0
  3 { CREATE TABLE t1(a, b DEFAULT 'x'); CREATE TABLE t2(a, b DEFAULT 'x') } 1
  4 { CREATE TABLE t1(a, b DEFAULT NULL); CREATE TABLE t2(a, b) }            0
  5 { CREATE TABLE t1(a DEFAULT 2, b); CREATE TABLE t2(a DEFAULT 1, b) }     1
  6 { CREATE TABLE t1(a DEFAULT 1, b); CREATE TABLE t2(a DEFAULT 1, b) }     1
  7 { CREATE TABLE t1(a DEFAULT 1, b DEFAULT 1);
      CREATE TABLE t2(a DEFAULT 3, b DEFAULT 1) }                            1
  8 { CREATE TABLE t1(a DEFAULT 1, b DEFAULT 1);
      CREATE TABLE t2(a DEFAULT 3, b DEFAULT 3) }                            0

} {

  execsql { DROP TABLE t1; DROP TABLE t2 }
  execsql $tbls

  set res 1
  db eval { EXPLAIN INSERT INTO t1 SELECT * FROM t2 } {
................................................................................
    if {$opcode == "Column"} { set res 0 }
  }

  do_test 2.$tn [list set res] $xfer
}

finish_test