/ Check-in [f8c4c495]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Avoid transfering records between tables unless the default values for all columns are the same. Fix for [f67b41381a].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: f8c4c495e6de1f124d205383d4bafa46accbff5c
User & Date: dan 2014-04-26 14:07:57
Context
2014-04-26
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
2014-04-25
17:37
Add test cases to ensure correct operation of joins with a virtual table that include DISTINCT and ORDER BY clauses. Verification for ticket [388d01d4bb8f9]. check-in: 5ada136f user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/insert.c.

  1861   1861     if( pDest->nCol!=pSrc->nCol ){
  1862   1862       return 0;   /* Number of columns must be the same in tab1 and tab2 */
  1863   1863     }
  1864   1864     if( pDest->iPKey!=pSrc->iPKey ){
  1865   1865       return 0;   /* Both tables must have the same INTEGER PRIMARY KEY */
  1866   1866     }
  1867   1867     for(i=0; i<pDest->nCol; i++){
  1868         -    if( pDest->aCol[i].affinity!=pSrc->aCol[i].affinity ){
         1868  +    Column *pDestCol = &pDest->aCol[i];
         1869  +    Column *pSrcCol = &pSrc->aCol[i];
         1870  +    if( pDestCol->affinity!=pSrcCol->affinity ){
  1869   1871         return 0;    /* Affinity must be the same on all columns */
  1870   1872       }
  1871         -    if( !xferCompatibleCollation(pDest->aCol[i].zColl, pSrc->aCol[i].zColl) ){
         1873  +    if( !xferCompatibleCollation(pDestCol->zColl, pSrcCol->zColl) ){
  1872   1874         return 0;    /* Collating sequence must be the same on all columns */
  1873   1875       }
  1874         -    if( pDest->aCol[i].notNull && !pSrc->aCol[i].notNull ){
         1876  +    if( pDestCol->notNull && !pSrcCol->notNull ){
  1875   1877         return 0;    /* tab2 must be NOT NULL if tab1 is */
  1876   1878       }
         1879  +    if( (pDestCol->zDflt==0)!=(pSrcCol->zDflt==0) 
         1880  +     || (pDestCol->zDflt && strcmp(pDestCol->zDflt, pSrcCol->zDflt))
         1881  +    ){
         1882  +      return 0;    /* Default values must be the same for all columns */
         1883  +    }
  1877   1884     }
  1878   1885     for(pDestIdx=pDest->pIndex; pDestIdx; pDestIdx=pDestIdx->pNext){
  1879   1886       if( pDestIdx->onError!=OE_None ){
  1880   1887         destHasUniqueIdx = 1;
  1881   1888       }
  1882   1889       for(pSrcIdx=pSrc->pIndex; pSrcIdx; pSrcIdx=pSrcIdx->pNext){
  1883   1890         if( xferCompatibleIndex(pDestIdx, pSrcIdx) ) break;

Added test/tkt-f67b41381a.test.

            1  +# 2014 April 26
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +# Test that ticket f67b41381a has been resolved.
           12  +#
           13  +
           14  +set testdir [file dirname $argv0]
           15  +source $testdir/tester.tcl
           16  +set testprefix tkt-f67b41381a
           17  +
           18  +do_execsql_test 1.0 {
           19  +  CREATE TABLE t1(a);
           20  +  INSERT INTO t1 VALUES(1);
           21  +  ALTER TABLE t1 ADD COLUMN b DEFAULT 2;
           22  +  CREATE TABLE t2(a, b);
           23  +  INSERT INTO t2 SELECT * FROM t1;
           24  +  SELECT * FROM t2;
           25  +} {1 2}
           26  +
           27  +db cache size 0
           28  +foreach {tn tbls xfer} {
           29  +  1 { CREATE TABLE t1(a, b); CREATE TABLE t2(a, b)             }             1
           30  +  2 { CREATE TABLE t1(a, b DEFAULT 'x'); CREATE TABLE t2(a, b) }             0
           31  +  3 { CREATE TABLE t1(a, b DEFAULT 'x'); CREATE TABLE t2(a, b DEFAULT 'x') } 1
           32  +  4 { CREATE TABLE t1(a, b DEFAULT NULL); CREATE TABLE t2(a, b) }            0
           33  +  5 { CREATE TABLE t1(a DEFAULT 2, b); CREATE TABLE t2(a DEFAULT 1, b) }     0
           34  +  6 { CREATE TABLE t1(a DEFAULT 1, b); CREATE TABLE t2(a DEFAULT 1, b) }     1
           35  +} {
           36  +
           37  +  execsql { DROP TABLE t1; DROP TABLE t2 }
           38  +  execsql $tbls
           39  +
           40  +  set res 1
           41  +  db eval { EXPLAIN INSERT INTO t1 SELECT * FROM t2 } {
           42  +    if {$opcode == "Column"} { set res 0 }
           43  +  }
           44  +
           45  +  do_test 2.$tn [list set res] $xfer
           46  +}
           47  +
           48  +finish_test
           49  +
           50  +