/ Check-in [7ea08d07]
Login

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

Overview
Comment:Add extra test cases for UPSERT.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 7ea08d0750dd9e07d2ffea336127034800db14aa23e60adec384d338871bf53a
User & Date: dan 2018-04-18 17:56:37
Context
2018-04-18
18:18
Improved matching of COLLATE clauses within the ON CONFLICT conflict-target. check-in: 8f4376e5 user: drh tags: trunk
17:56
Add extra test cases for UPSERT. check-in: 7ea08d07 user: dan tags: trunk
17:52
Fix a problem in the sqlite3ExprCompare() function that caused two dissimilar expressions to match if they have the same "COLLATE name" at the outer layer. check-in: fb16348a user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to test/upsert4.test.

     9      9   #
    10     10   #***********************************************************************
    11     11   #
    12     12   # Test cases for UPSERT
    13     13   
    14     14   set testdir [file dirname $argv0]
    15     15   source $testdir/tester.tcl
    16         -set testprefix upsert2
           16  +set testprefix upsert4
    17     17   
    18     18   foreach {tn sql} {
    19     19     1 { CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c UNIQUE) }
    20     20     2 { CREATE TABLE t1(a INT PRIMARY KEY, b, c UNIQUE) }
    21     21     3 { CREATE TABLE t1(a INT PRIMARY KEY, b, c UNIQUE) WITHOUT ROWID}
    22     22   } {
    23     23     reset_db
................................................................................
    59     59       INSERT INTO t1 VALUES(2, NULL, 'zero') ON CONFLICT (a) 
    60     60         DO UPDATE SET c = 'one';
    61     61     } {1 {UNIQUE constraint failed: t1.c}}
    62     62   
    63     63     do_execsql_test 1.$tn.6 {
    64     64       SELECT * FROM t1;
    65     65     } {1 {} one 2 2 two 3 {} three}
           66  +
           67  +  do_execsql_test 1.$tn.7 {
           68  +    INSERT INTO t1 VALUES(2, NULL, 'zero') ON CONFLICT (a) 
           69  +      DO UPDATE SET (b, c) = (SELECT 'x', 'y');
           70  +    SELECT * FROM t1;
           71  +  } {1 {} one 2 x y 3 {} three}
           72  +
           73  +  do_execsql_test 1.$tn.8 {
           74  +    INSERT INTO t1 VALUES(1, NULL, NULL) ON CONFLICT (a) 
           75  +      DO UPDATE SET (c, a) = ('four', 4);
           76  +    SELECT * FROM t1 ORDER BY 1;
           77  +  } {2 x y 3 {} three 4 {} four}
           78  +}
           79  +
           80  +#-------------------------------------------------------------------------
           81  +# Test target analysis.
           82  +#
           83  +set rtbl(0) {0 {}}
           84  +set rtbl(1) {/1 .*failed.*/}
           85  +set rtbl(2) {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}}
           86  +
           87  +foreach {tn sql} {
           88  +  1 { 
           89  +      CREATE TABLE xyz(a INTEGER PRIMARY KEY, b, c, d);
           90  +      CREATE UNIQUE INDEX xyz1 ON xyz(d, c, b COLLATE nocase);
           91  +  }
           92  +
           93  +  2 { 
           94  +      CREATE TABLE xyz(a INT PRIMARY KEY, b, c, d);
           95  +      CREATE UNIQUE INDEX xyz1 ON xyz(d, c, b COLLATE nocase);
           96  +  }
           97  +
           98  +  3 { 
           99  +      CREATE TABLE xyz(a INT PRIMARY KEY, b, c, d) WITHOUT ROWID;
          100  +      CREATE UNIQUE INDEX xyz1 ON xyz(d, c, b COLLATE nocase);
          101  +  }
          102  +} {
          103  +  reset_db
          104  +  execsql $sql
          105  +  do_execsql_test 2.$tn.1 {
          106  +    INSERT INTO xyz VALUES(10, 1, 1, 'one');
          107  +  }
          108  +
          109  +
          110  +  foreach {tn2 oc res} {
          111  +    1 "ON CONFLICT (b COLLATE nocase, c, d) DO NOTHING"   0
          112  +    2 "ON CONFLICT (b, c, d) DO NOTHING"                  0
          113  +    3 "ON CONFLICT (b, c COLLATE nocase, d) DO NOTHING"   2
          114  +    4 "ON CONFLICT (a) DO NOTHING"                        1
          115  +    5 "ON CONFLICT DO NOTHING"                            0
          116  +    6 "ON CONFLICT (b, c, d) WHERE a!=0 DO NOTHING"       0
          117  +    7 "ON CONFLICT (d, c, c) WHERE a!=0 DO NOTHING"       2
          118  +  } {
          119  +
          120  +    do_catchsql_test 2.$tn.2.$tn2 "
          121  +      INSERT INTO xyz VALUES(11, 1, 1, 'one') $oc
          122  +    " $rtbl($res)
          123  +  }
          124  +
          125  +  do_execsql_test 2.$tn.3 {
          126  +    SELECT * FROM xyz;
          127  +  } {10 1 1 one}
          128  +}
          129  +
          130  +foreach {tn sql} {
          131  +  1 { 
          132  +    CREATE TABLE abc(a INTEGER PRIMARY KEY, x, y);
          133  +    CREATE UNIQUE INDEX abc1 ON abc(('x' || x) COLLATE nocase);
          134  +  }
          135  +  2 { 
          136  +    CREATE TABLE abc(a INT PRIMARY KEY, x, y);
          137  +    CREATE UNIQUE INDEX abc1 ON abc(('x' || x) COLLATE nocase);
          138  +  }
          139  +  3 { 
          140  +    CREATE TABLE abc(a INT PRIMARY KEY, x, y) WITHOUT ROWID;
          141  +    CREATE UNIQUE INDEX abc1 ON abc(('x' || x) COLLATE nocase);
          142  +  }
          143  +} {
          144  +  reset_db
          145  +  execsql $sql
          146  +  do_execsql_test 2.$tn.1 {
          147  +    INSERT INTO abc VALUES(1, 'one', 'two');
          148  +  }
          149  +
          150  +  foreach {tn2 oc res} {
          151  +    1 "ON CONFLICT DO NOTHING"                             0
          152  +    2 "ON CONFLICT ('x' || x) DO NOTHING"                  0
          153  +    3 "ON CONFLICT (('x' || x) COLLATE nocase) DO NOTHING" 0
          154  +    4 "ON CONFLICT (('x' || x) COLLATE binary) DO NOTHING" 2
          155  +    5 "ON CONFLICT (x || 'x') DO NOTHING"                  2
          156  +    6 "ON CONFLICT ((('x' || x))) DO NOTHING"              0
          157  +  } {
          158  +    do_catchsql_test 2.$tn.2.$tn2 "
          159  +      INSERT INTO abc VALUES(2, 'one', NULL) $oc;
          160  +    " $rtbl($res)
          161  +  }
          162  +
          163  +  do_execsql_test 2.$tn.3 {
          164  +    SELECT * FROM abc
          165  +  } {1 one two}
    66    166   }
          167  +
    67    168   
    68    169   finish_test
    69    170