/ Check-in [cf253584]
Login

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

Overview
Comment:Add tests for name resolution in ON CONFLICT clauses.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: cf253584ecf7aed04406b4bae78b536818fadfb3fb96c05f2c99954b841db85f
User & Date: dan 2018-04-20 17:50:49
Context
2018-04-20
18:01
Fix a VDBE comment on upsert. Provide an error message when upsert detects index corruption. check-in: 279c48f6 user: drh tags: trunk
17:50
Add tests for name resolution in ON CONFLICT clauses. check-in: cf253584 user: dan tags: trunk
17:02
Avoid unnecessary cursor seeking when performing an UPSERT. check-in: 693a3dcb user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to test/upsert4.test.

   215    215     CREATE TABLE w1(a INT PRIMARY KEY, x, y);
   216    216     CREATE UNIQUE INDEX w1expr ON w1(('x' || x));
   217    217     INSERT INTO w1 VALUES(2, 'one', NULL)
   218    218       ON CONFLICT (('x' || x) COLLATE nocase) DO NOTHING;
   219    219   } {1 {ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint}}
   220    220   
   221    221   #-------------------------------------------------------------------------
          222  +# Test that ON CONFLICT constraint processing occurs before any REPLACE
          223  +# constraint processing.
          224  +#
          225  +foreach {tn sql} {
          226  +  1 {
          227  +    CREATE TABLE t1(a INTEGER PRIMARY KEY, b UNIQUE, c);
          228  +  }
          229  +  2 {
          230  +    CREATE TABLE t1(a INT PRIMARY KEY, b UNIQUE, c);
          231  +  }
          232  +  3 {
          233  +    CREATE TABLE t1(a INT PRIMARY KEY, b UNIQUE, c) WITHOUT ROWID;
          234  +  }
          235  +} {
          236  +  reset_db
          237  +  execsql $sql
          238  +  do_execsql_test 6.1.$tn {
          239  +    INSERT INTO t1 VALUES(1, 1, 'one');
          240  +    INSERT INTO t1 VALUES(2, 2, 'two');
          241  +    INSERT OR REPLACE INTO t1 VALUES(1, 2, 'two') ON CONFLICT(b) DO NOTHING;
          242  +    PRAGMA integrity_check;
          243  +  } {ok}
          244  +}
          245  +
          246  +foreach {tn sql} {
          247  +  1 {
          248  +    CREATE TABLE t1(a INTEGER PRIMARY KEY, b UNIQUE, c UNIQUE);
          249  +  }
          250  +} {
          251  +  reset_db
          252  +  execsql $sql
          253  +
          254  +  do_execsql_test 6.2.$tn.1 {
          255  +    INSERT INTO t1 VALUES(1, 1, 1);
          256  +    INSERT INTO t1 VALUES(2, 2, 2);
          257  +  }
          258  +
          259  +  do_execsql_test 6.2.$tn.2 {
          260  +    INSERT OR REPLACE INTO t1 VALUES(3, 1, 1) ON CONFLICT(b) DO NOTHING;
          261  +    SELECT * FROM t1;
          262  +    PRAGMA integrity_check;
          263  +  } {1 1 1 2 2 2 ok}
          264  +
          265  +  do_execsql_test 6.2.$tn.3 {
          266  +    INSERT OR REPLACE INTO t1 VALUES(3, 2, 2) ON CONFLICT(c) DO NOTHING;
          267  +    SELECT * FROM t1;
          268  +    PRAGMA integrity_check;
          269  +  } {1 1 1 2 2 2 ok}
          270  +
          271  +  do_execsql_test 6.2.$tn.2 {
          272  +    INSERT OR REPLACE INTO t1 VALUES(3, 1, 1) ON CONFLICT(b) 
          273  +      DO UPDATE SET b=b||'x';
          274  +    SELECT * FROM t1;
          275  +    PRAGMA integrity_check;
          276  +  } {1 1x 1 2 2 2 ok}
          277  +
          278  +  do_execsql_test 6.2.$tn.2 {
          279  +    INSERT OR REPLACE INTO t1 VALUES(3, 2, 2) ON CONFLICT(c) 
          280  +      DO UPDATE SET c=c||'x';
          281  +    SELECT * FROM t1;
          282  +    PRAGMA integrity_check;
          283  +  } {1 1x 1 2 2 2x ok}
          284  +}
          285  +
          286  +#-------------------------------------------------------------------------
          287  +# Test references to "excluded". And using an alias in an INSERT 
          288  +# statement.
   222    289   #
   223         -do_execsql_test 6.0 {
   224         -  CREATE TABLE t1(a INTEGER PRIMARY KEY, b UNIQUE, c);
   225         -  INSERT INTO t1 VALUES(1, 1, 'one');
   226         -  INSERT INTO t1 VALUES(2, 2, 'two');
   227         -  INSERT OR REPLACE INTO t1 VALUES(1, 2, 'two') ON CONFLICT(b) DO NOTHING;
   228         -  PRAGMA integrity_check;
   229         -} {ok}
          290  +foreach {tn sql} {
          291  +  1 {
          292  +    CREATE TABLE t1(w, x, y, z, PRIMARY KEY(x, y));
          293  +    CREATE UNIQUE INDEX zz ON t1(z);
          294  +  }
          295  +  2 {
          296  +    CREATE TABLE t1(w, x, y, z, PRIMARY KEY(x, y)) WITHOUT ROWID;
          297  +    CREATE UNIQUE INDEX zz ON t1(z);
          298  +  }
          299  +} {
          300  +  reset_db
          301  +  execsql $sql
          302  +  do_execsql_test 7.$tn.0 {
          303  +    INSERT INTO t1 VALUES('a', 1, 1, 1);
          304  +    INSERT INTO t1 VALUES('b', 2, 2, 2);
          305  +  }
          306  +
          307  +  do_execsql_test 7.$tn.1 {
          308  +    INSERT INTO t1 VALUES('c', 3, 3, 1) ON CONFLICT(z) 
          309  +      DO UPDATE SET w = excluded.w;
          310  +    SELECT * FROM t1;
          311  +  } {c 1 1 1 b 2 2 2}
          312  +
          313  +  do_execsql_test 7.$tn.2 {
          314  +    INSERT INTO t1 VALUES('c', 2, 2, 3) ON CONFLICT(y, x) 
          315  +      DO UPDATE SET w = w||w;
          316  +    SELECT * FROM t1;
          317  +  } {c 1 1 1 bb 2 2 2}
          318  +
          319  +  do_execsql_test 7.$tn.3 {
          320  +    INSERT INTO t1 VALUES('c', 2, 2, 3) ON CONFLICT(y, x) 
          321  +      DO UPDATE SET w = w||t1.w;
          322  +    SELECT * FROM t1;
          323  +  } {c 1 1 1 bbbb 2 2 2}
          324  +
          325  +  do_execsql_test 7.$tn.4 {
          326  +    INSERT INTO t1 AS tbl VALUES('c', 2, 2, 3) ON CONFLICT(y, x) 
          327  +      DO UPDATE SET w = w||tbl.w;
          328  +    SELECT * FROM t1;
          329  +  } {c 1 1 1 bbbbbbbb 2 2 2}
          330  +}
          331  +
          332  +foreach {tn sql} {
          333  +  1 {
          334  +    CREATE TABLE excluded(w, x, 'a b', z, PRIMARY KEY(x, 'a b'));
          335  +    CREATE UNIQUE INDEX zz ON excluded(z);
          336  +  }
          337  +  2 {
          338  +    CREATE TABLE excluded(w, x, 'a b', z, PRIMARY KEY(x, 'a b')) WITHOUT ROWID;
          339  +    CREATE UNIQUE INDEX zz ON excluded(z);
          340  +  }
          341  +} {
          342  +  reset_db
          343  +  execsql $sql
          344  +  do_execsql_test 8.$tn.0 {
          345  +    INSERT INTO excluded VALUES('a', 1, 1, 1);
          346  +    INSERT INTO excluded VALUES('b', 2, 2, 2);
          347  +  }
          348  +
          349  +  # Note: An error in Postgres: "table reference "excluded" is ambiguous".
          350  +  #
          351  +  do_execsql_test 8.$tn.1 {
          352  +    INSERT INTO excluded VALUES('hello', 1, 1, NULL) ON CONFLICT(x, "a b")
          353  +      DO UPDATE SET w=excluded.w;
          354  +    SELECT * FROM excluded;
          355  +  } {a 1 1 1 b 2 2 2}
          356  +
          357  +  do_execsql_test 8.$tn.2 {
          358  +    INSERT INTO excluded AS x1 VALUES('hello', 1, 1, NULL) ON CONFLICT(x, [a b])
          359  +      DO UPDATE SET w=excluded.w;
          360  +    SELECT * FROM excluded;
          361  +  } {hello 1 1 1 b 2 2 2}
          362  +}
          363  +
   230    364   
   231    365   finish_test
   232         -