/ Check-in [6f9898db]
Login

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

Overview
Comment:Make sure the INSERT INTO ... SELECT statement works correctly even when the destination table contains an INTEGER PRIMARY KEY ON CONFLICT... column. Ticket [676bc02b87176125].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 6f9898db7ff0730cc03f561f9c32ef3dee7e5d81
User & Date: drh 2011-11-04 00:23:53
Context
2011-11-04
00:35
Enhance the shell so that the ".schema" command works with case insensitive LIKE patterns even with PRAGMA cache_sensitive_like=ON. check-in: b06bf3b3 user: drh tags: trunk
00:23
Make sure the INSERT INTO ... SELECT statement works correctly even when the destination table contains an INTEGER PRIMARY KEY ON CONFLICT... column. Ticket [676bc02b87176125]. check-in: 6f9898db user: drh tags: trunk
2011-11-01
00:52
Version 3.7.9 check-in: c7c6050e user: drh tags: trunk, release, version-3.7.9
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/insert.c.

  1635   1635     }
  1636   1636   #ifndef SQLITE_OMIT_VIRTUALTABLE
  1637   1637     if( pDest->tabFlags & TF_Virtual ){
  1638   1638       return 0;   /* tab1 must not be a virtual table */
  1639   1639     }
  1640   1640   #endif
  1641   1641     if( onError==OE_Default ){
  1642         -    onError = OE_Abort;
         1642  +    if( pDest->iPKey>=0 ) onError = pDest->keyConf;
         1643  +    if( onError==OE_Default ) onError = OE_Abort;
  1643   1644     }
  1644   1645     if( onError!=OE_Abort && onError!=OE_Rollback ){
  1645   1646       return 0;   /* Cannot do OR REPLACE or OR IGNORE or OR FAIL */
  1646   1647     }
  1647   1648     assert(pSelect->pSrc);   /* allocated even if there is no FROM clause */
  1648   1649     if( pSelect->pSrc->nSrc!=1 ){
  1649   1650       return 0;   /* FROM clause must have exactly one term */

Changes to test/insert4.test.

   381    381         SELECT * FROM t7b;
   382    382       }
   383    383     } {123}
   384    384     do_test insert4-7.8 {
   385    385       set ::sqlite3_xferopt_count
   386    386     } {1}
   387    387   }
          388  +
          389  +# Ticket [676bc02b87176125635cb174d110b431581912bb]
          390  +# Make sure INTEGER PRIMARY KEY ON CONFLICT ... works with the xfer
          391  +# optimization.
          392  +#
          393  +do_test insert4-8.1 {
          394  +  execsql {
          395  +    DROP TABLE IF EXISTS t1;
          396  +    DROP TABLE IF EXISTS t2;
          397  +    CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT REPLACE, b);
          398  +    CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT REPLACE, y);
          399  +    INSERT INTO t1 VALUES(1,2);
          400  +    INSERT INTO t2 VALUES(1,3);
          401  +    INSERT INTO t1 SELECT * FROM t2;
          402  +    SELECT * FROM t1;
          403  +  }
          404  +} {1 3}
          405  +do_test insert4-8.2 {
          406  +  execsql {
          407  +    DROP TABLE IF EXISTS t1;
          408  +    DROP TABLE IF EXISTS t2;
          409  +    CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT REPLACE, b);
          410  +    CREATE TABLE t2(x, y);
          411  +    INSERT INTO t1 VALUES(1,2);
          412  +    INSERT INTO t2 VALUES(1,3);
          413  +    INSERT INTO t1 SELECT * FROM t2;
          414  +    SELECT * FROM t1;
          415  +  }
          416  +} {1 3}
          417  +do_test insert4-8.3 {
          418  +  execsql {
          419  +    DROP TABLE IF EXISTS t1;
          420  +    DROP TABLE IF EXISTS t2;
          421  +    CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT IGNORE, b);
          422  +    CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT IGNORE, y);
          423  +    INSERT INTO t1 VALUES(1,2);
          424  +    INSERT INTO t2 VALUES(1,3);
          425  +    INSERT INTO t1 SELECT * FROM t2;
          426  +    SELECT * FROM t1;
          427  +  }
          428  +} {1 2}
          429  +do_test insert4-8.4 {
          430  +  execsql {
          431  +    DROP TABLE IF EXISTS t1;
          432  +    DROP TABLE IF EXISTS t2;
          433  +    CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT IGNORE, b);
          434  +    CREATE TABLE t2(x, y);
          435  +    INSERT INTO t1 VALUES(1,2);
          436  +    INSERT INTO t2 VALUES(1,3);
          437  +    INSERT INTO t1 SELECT * FROM t2;
          438  +    SELECT * FROM t1;
          439  +  }
          440  +} {1 2}
          441  +do_test insert4-8.5 {
          442  +  execsql {
          443  +    DROP TABLE IF EXISTS t1;
          444  +    DROP TABLE IF EXISTS t2;
          445  +    CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT FAIL, b);
          446  +    CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT FAIL, y);
          447  +    INSERT INTO t1 VALUES(1,2);
          448  +    INSERT INTO t2 VALUES(-99,100);
          449  +    INSERT INTO t2 VALUES(1,3);
          450  +    SELECT * FROM t1;
          451  +  }
          452  +  catchsql {
          453  +    INSERT INTO t1 SELECT * FROM t2;
          454  +  }
          455  +} {1 {PRIMARY KEY must be unique}}
          456  +do_test insert4-8.6 {
          457  +  execsql {
          458  +    SELECT * FROM t1;
          459  +  }
          460  +} {-99 100 1 2} 
          461  +do_test insert4-8.7 {
          462  +  execsql {
          463  +    DROP TABLE IF EXISTS t1;
          464  +    DROP TABLE IF EXISTS t2;
          465  +    CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT ABORT, b);
          466  +    CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT ABORT, y);
          467  +    INSERT INTO t1 VALUES(1,2);
          468  +    INSERT INTO t2 VALUES(-99,100);
          469  +    INSERT INTO t2 VALUES(1,3);
          470  +    SELECT * FROM t1;
          471  +  }
          472  +  catchsql {
          473  +    INSERT INTO t1 SELECT * FROM t2;
          474  +  }
          475  +} {1 {PRIMARY KEY must be unique}}
          476  +do_test insert4-8.8 {
          477  +  execsql {
          478  +    SELECT * FROM t1;
          479  +  }
          480  +} {1 2} 
          481  +do_test insert4-8.9 {
          482  +  execsql {
          483  +    DROP TABLE IF EXISTS t1;
          484  +    DROP TABLE IF EXISTS t2;
          485  +    CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT ROLLBACK, b);
          486  +    CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT ROLLBACK, y);
          487  +    INSERT INTO t1 VALUES(1,2);
          488  +    INSERT INTO t2 VALUES(-99,100);
          489  +    INSERT INTO t2 VALUES(1,3);
          490  +    SELECT * FROM t1;
          491  +  }
          492  +  catchsql {
          493  +    BEGIN;
          494  +    INSERT INTO t1 VALUES(2,3);
          495  +    INSERT INTO t1 SELECT * FROM t2;
          496  +  }
          497  +} {1 {PRIMARY KEY must be unique}}
          498  +do_test insert4-8.10 {
          499  +  catchsql {COMMIT}
          500  +} {1 {cannot commit - no transaction is active}}
          501  +do_test insert4-8.11 {
          502  +  execsql {
          503  +    SELECT * FROM t1;
          504  +  }
          505  +} {1 2} 
          506  +
   388    507   
   389    508   finish_test