turso/testing/upsert.test

#!/usr/bin/env tclsh
set testdir [file dirname $argv0]
source $testdir/tester.tcl

do_execsql_test_on_specific_db {:memory:} upsert-pk-update {
    CREATE TABLE t (id INTEGER PRIMARY KEY, name);
    INSERT INTO t VALUES (1,'old');
    INSERT INTO t VALUES (1,'new') ON CONFLICT DO UPDATE SET name = excluded.name;
    SELECT * FROM t;
} {1|new}

do_execsql_test_on_specific_db {:memory:} upsert-pk-do-nothing {
    CREATE TABLE t (id INTEGER PRIMARY KEY, name);
    INSERT INTO t VALUES (1,'new');
    INSERT INTO t VALUES (1,'ignored') ON CONFLICT DO NOTHING;
    SELECT * FROM t;
} {1|new}


do_execsql_test_on_specific_db {:memory:} upsert-unique-update {
    CREATE TABLE u (a, b, c);
    CREATE UNIQUE INDEX u_a ON u(a);
    INSERT INTO u VALUES (1,10,100);
    INSERT INTO u VALUES (1,20,200) ON CONFLICT(a) DO UPDATE SET b = excluded.b, c = excluded.c;
    SELECT * FROM u;
} {1|20|200}

do_execsql_test_on_specific_db {:memory:} upsert-unique-do-nothing {
    CREATE TABLE u (a, b, c);
    CREATE UNIQUE INDEX u_a ON u(a);
    INSERT INTO u VALUES (1,10,100);
    INSERT INTO u VALUES (2,30,300) ON CONFLICT(a) DO NOTHING;
    SELECT * FROM u ORDER BY a;
} {1|10|100
2|30|300}

do_execsql_test_on_specific_db {:memory:} upsert-where-guard-no-change {
    CREATE TABLE g (a UNIQUE, b);
    INSERT INTO g VALUES (1,'x');
    INSERT INTO g VALUES (1,'y') ON CONFLICT(a) DO UPDATE SET b = excluded.b WHERE b IS NULL;
    SELECT * FROM g;
} {1|x}

do_execsql_test_on_specific_db {:memory:} upsert-where-guard-apply {
    CREATE TABLE g (a UNIQUE, b);
    INSERT INTO g VALUES (1,NULL);
    INSERT INTO g VALUES (1,'y') ON CONFLICT(a) DO UPDATE SET b = excluded.b WHERE b IS NULL;
    SELECT * FROM g;
} {1|y}

do_execsql_test_on_specific_db {:memory:} upsert-selfref-and-excluded {
    CREATE TABLE s (a UNIQUE, b, c);
    INSERT INTO s VALUES (1,10,'old');
    INSERT INTO s VALUES (1,99,'new')
      ON CONFLICT(a) DO UPDATE SET b = b + 1, c = excluded.c;
    SELECT * FROM s;
} {1|11|new}

do_execsql_test_on_specific_db {:memory:} upsert-values-mixed-insert-update {
    CREATE TABLE m (a UNIQUE, b);
    INSERT INTO m VALUES (1,'one');
    INSERT INTO m VALUES (1,'uno'), (2,'dos')
      ON CONFLICT(a) DO UPDATE SET b = excluded.b;
    SELECT * FROM m ORDER BY a;
} {1|uno
2|dos}

do_execsql_test_on_specific_db {:memory:} upsert-select-single {
    CREATE TABLE s1 (a UNIQUE, b);
    INSERT INTO s1 VALUES (1,'old');
    INSERT INTO s1 SELECT 1,'NEW' ON CONFLICT(a) DO UPDATE SET b = excluded.b;
    SELECT * FROM s1;
} {1|NEW}

do_execsql_test_on_specific_db {:memory:} upsert-composite-target-orderless {
    CREATE TABLE c (a, b, val);
    CREATE UNIQUE INDEX c_ab ON c(a,b);
    INSERT INTO c VALUES (1,1,'x');
    INSERT INTO c VALUES (1,1,'y') ON CONFLICT(b,a) DO UPDATE SET val = excluded.val;
    SELECT val FROM c WHERE a=1 AND b=1;
} {y}

do_execsql_test_on_specific_db {:memory:} upsert-collate-nocase {
    CREATE TABLE nc (name TEXT COLLATE NOCASE UNIQUE, v);
    INSERT INTO nc VALUES ('Alice', 1);
    INSERT INTO nc VALUES ('aLiCe', 2)
      ON CONFLICT(name COLLATE NOCASE) DO UPDATE SET v = excluded.v;
    SELECT * FROM nc;
} {Alice|2}

do_execsql_test_on_specific_db {:memory:} upsert-returning-update {
    CREATE TABLE r (id INTEGER PRIMARY KEY, name);
    INSERT INTO r VALUES (1,'a');
    INSERT INTO r VALUES (1,'b')
      ON CONFLICT DO UPDATE SET name = excluded.name
      RETURNING id, name;
} {1|b}

do_execsql_test_on_specific_db {:memory:} upsert-returning-insert {
    CREATE TABLE r2 (id INTEGER PRIMARY KEY, name);
    INSERT INTO r2 VALUES (2,'c')
      ON CONFLICT DO UPDATE SET name = excluded.name
      RETURNING id, name;
} {2|c}

do_execsql_test_on_specific_db {:memory:} upsert-returning-do-nothing-empty {
    CREATE TABLE r3 (id INTEGER PRIMARY KEY, name);
    INSERT INTO r3 VALUES (2,'orig');
    INSERT INTO r3 VALUES (2,'ignored')
      ON CONFLICT DO NOTHING
      RETURNING id, name;
} {}

do_execsql_test_on_specific_db {:memory:} upsert-rowid-in-set {
    CREATE TABLE rid (id INTEGER PRIMARY KEY, name);
    INSERT INTO rid VALUES (5,'foo');
    INSERT INTO rid VALUES (5,'bar')
      ON CONFLICT DO UPDATE SET name = printf('id=%d', rowid);
    SELECT * FROM rid;
} {5|id=5}

do_execsql_test_in_memory_any_error upsert-notnull-violation {
    CREATE TABLE nn (a UNIQUE, b NOT NULL);
    INSERT INTO nn VALUES (1,'x');
    INSERT INTO nn VALUES (1,'y') ON CONFLICT(a) DO UPDATE SET b = NULL;
}

do_execsql_test_on_specific_db {:memory:} upsert-updates-other-unique-key {
    CREATE TABLE idx (a UNIQUE, b UNIQUE);
    INSERT INTO idx VALUES (1,1);
    INSERT INTO idx VALUES (1,2) ON CONFLICT(a) DO UPDATE SET b = excluded.b;
    SELECT * FROM idx;
} {1|2}


do_execsql_test_in_memory_any_error upsert-target-mismatch-errors {
    CREATE TABLE tm (a, b UNIQUE);
    INSERT INTO tm VALUES (1,1);
    INSERT INTO tm VALUES (2,1)
      ON CONFLICT(a) DO UPDATE SET a = excluded.a;  -- conflict is on b, target is a → error
}

do_execsql_test_on_specific_db {:memory:} upsert-omitted-target-matches-pk {
    CREATE TABLE pkalias (a INTEGER PRIMARY KEY, b);
    INSERT INTO pkalias VALUES (42,'old');
    INSERT INTO pkalias (a,b) VALUES (42,'new') ON CONFLICT DO UPDATE SET b = excluded.b;
    SELECT * FROM pkalias;
} {42|new}

do_execsql_test_on_specific_db {:memory:} upsert-rowvalue-set {
    CREATE TABLE rv (a INTEGER PRIMARY KEY, b, c);
    INSERT INTO rv VALUES (1,'x','y');
    INSERT INTO rv VALUES (1,'B','C')
      ON CONFLICT DO UPDATE SET (b,c) = (excluded.b, excluded.c);
    SELECT * FROM rv;
} {1|B|C}

do_execsql_test_on_specific_db {:memory:} upsert-where-excluded-vs-target {
    CREATE TABLE wh (a UNIQUE, b);
    INSERT INTO wh VALUES (1,5);
    INSERT INTO wh VALUES (1,3)
      ON CONFLICT(a) DO UPDATE SET b = excluded.b WHERE excluded.b > b;  -- 3 > 5 → no
    INSERT INTO wh VALUES (1,10)
      ON CONFLICT(a) DO UPDATE SET b = excluded.b WHERE excluded.b > b; -- 10 > 5 → yes
    SELECT * FROM wh;
} {1|10}

do_execsql_test_in_memory_any_error upsert-invalid-qualified-lhs {
    CREATE TABLE bad (a UNIQUE, b);
    INSERT INTO bad VALUES (1,'x');
    INSERT INTO bad VALUES (1,'y')
      ON CONFLICT(a) DO UPDATE SET excluded.b = 'z';
}

do_execsql_test_on_specific_db {:memory:} upsert-values-returning-mixed {
    CREATE TABLE mix (k UNIQUE, v);
    INSERT INTO mix VALUES (1,'one');
    INSERT INTO mix VALUES (1,'uno'), (2,'dos')
      ON CONFLICT(k) DO UPDATE SET v = excluded.v
      RETURNING k, v
      ;
} {1|uno
2|dos}

do_execsql_test_on_specific_db {:memory:} upsert-collate-implicit-match {
    CREATE TABLE ci (name TEXT COLLATE NOCASE, v);
    -- no explicit collate on index
    CREATE UNIQUE INDEX ci_name ON ci(name);
    INSERT INTO ci VALUES ('Alice', 1);
    INSERT INTO ci VALUES ('aLiCe', 2)
      ON CONFLICT(name COLLATE NOCASE) DO UPDATE SET v = excluded.v;
    SELECT * FROM ci;
} {Alice|2}

# Composite index requires exact coverage, targeting too few columns must not match.
do_execsql_test_in_memory_any_error upsert-composite-target-too-few {
    CREATE TABLE ct (a, b, val);
    CREATE UNIQUE INDEX ct_ab ON ct(a,b);
    INSERT INTO ct VALUES (1,1,'x');
    INSERT INTO ct VALUES (1,1,'y')
      ON CONFLICT(a) DO UPDATE SET val = excluded.val;   -- only "a" given → no match → error
}

# Composite index requires exact coverage, targeting too few columns must not match.
do_execsql_test_on_specific_db {:memory:} upsert-multilple-conflict-targets {
    CREATE TABLE ct (id INTEGER PRIMARY KEY, x UNIQUE, y UNIQUE, z DEFAULT NULL);
    INSERT INTO ct(id, x, y) VALUES (1, 'x', 'y');
    INSERT INTO ct(id, x, y) VALUES (2, 'a', 'b');
    INSERT INTO ct(id, x, y) VALUES (3, '!', '@');
    INSERT INTO ct(id, x, y) VALUES (4, 'x', 'y1'), (5, 'a1', 'b'), (3, '_', '_')
      ON CONFLICT(x) DO UPDATE SET x = excluded.x || '-' || x, y = excluded.y || '@' || y, z = 'x' 
      ON CONFLICT(y) DO UPDATE SET x = excluded.x || '+' || x, y = excluded.y || '!' || y, z = 'y' 
      ON CONFLICT DO UPDATE SET x = excluded.x || '#' || x, y = excluded.y || '%' || y, z = 'fallback';
    SELECT * FROM ct;
} {1|x-x|y1@y|x
2|a1+a|b!b|y
3|_#!|_%@|fallback
}

# Qualified target (t.a) should match unique index on a.
do_execsql_test_on_specific_db {:memory:} upsert-qualified-target {
    CREATE TABLE qt (a UNIQUE, b);
    INSERT INTO qt VALUES (1,'old');
    INSERT INTO qt VALUES (1,'new')
      ON CONFLICT(qt.a) DO UPDATE SET b = excluded.b;
    SELECT * FROM qt;
} {1|new}

# Non-simple target expression is not allowed (e.g., lower(name)) → no match → error.
do_execsql_test_in_memory_any_error upsert-invalid-target-expression {
    CREATE TABLE it (name, v);
    CREATE UNIQUE INDEX it_name ON it(name);
    INSERT INTO it VALUES ('x',1);
    INSERT INTO it VALUES ('x',2)
      ON CONFLICT(lower(name)) DO UPDATE SET v = excluded.v;
}


# WHERE with three-valued logic: b < excluded.b is NULL if b IS NULL, should NOT update.
do_execsql_test_on_specific_db {:memory:} upsert-where-null-3vl-no-update {
    CREATE TABLE w3 (a UNIQUE, b);
    INSERT INTO w3 VALUES (1, NULL);
    INSERT INTO w3 VALUES (1, 5)
      ON CONFLICT(a) DO UPDATE SET b = excluded.b WHERE b < excluded.b;
    SELECT * FROM w3;
} {1|}

# WHERE false on PK conflict → behaves like DO NOTHING.
do_execsql_test_on_specific_db {:memory:} upsert-pk-where-false {
    CREATE TABLE pw (id INTEGER PRIMARY KEY, name);
    INSERT INTO pw VALUES (7,'keep');
    INSERT INTO pw VALUES (7,'drop')
      ON CONFLICT DO UPDATE SET name = excluded.name WHERE 0;
    SELECT * FROM pw;
} {7|keep}

# WHERE referencing both target and EXCLUDED with arithmetic.
do_execsql_test_on_specific_db {:memory:} upsert-where-combo {
    CREATE TABLE wc (a UNIQUE, b);
    INSERT INTO wc VALUES (1, 10);
    INSERT INTO wc VALUES (1, 12)
      ON CONFLICT(a) DO UPDATE SET b = excluded.b
      WHERE excluded.b >= b + 2;   -- 12 >= 10 + 2 → yes
    SELECT * FROM wc;
} {1|12}

# Invalid EXCLUDED reference should error.
do_execsql_test_in_memory_error_content upsert-invalid-excluded-column {
    CREATE TABLE xe (a UNIQUE, v);
    INSERT INTO xe VALUES (1, 'ok');
    INSERT INTO xe VALUES (1, 'nope')
      ON CONFLICT(a) DO UPDATE SET v = excluded.not_a_column;
} {".*no such column.*"}


# DO UPDATE changes the *conflicting key column* to a different unique value.
do_execsql_test_on_specific_db {:memory:} upsert-update-conflicting-key {
    CREATE TABLE uk (a UNIQUE, b);
    INSERT INTO uk VALUES (1,'old');
    INSERT INTO uk VALUES (1,'new')
      ON CONFLICT(a) DO UPDATE SET a = 2, b = excluded.b;
    SELECT * FROM uk;
} {2|new}

# DO UPDATE that writes the same values (no-op) should still succeed.
do_execsql_test_on_specific_db {:memory:} upsert-noop-update-ok {
    CREATE TABLE nu (a UNIQUE, b);
    INSERT INTO nu VALUES (5,'same');
    INSERT INTO nu VALUES (5,'irrelevant')
      ON CONFLICT(a) DO UPDATE SET b = b;  -- no change
    SELECT * FROM nu;
} {5|same}

# DO UPDATE that would violate a different UNIQUE constraint should error.
do_execsql_test_in_memory_any_error upsert-update-causes-second-unique-violation {
    CREATE TABLE uv (a UNIQUE, b UNIQUE);
    INSERT INTO uv VALUES (1, 10);
    INSERT INTO uv VALUES (2, 20);
    INSERT INTO uv VALUES (1, 20)
      ON CONFLICT(a) DO UPDATE SET b = excluded.b; # would duplicate b=20 from row a=2
}

# Multi-row VALUES with mixed conflict/non-conflict and WHERE filter in the DO UPDATE.
do_execsql_test_on_specific_db {:memory:} upsert-multirow-mixed-where {
    CREATE TABLE mm (k UNIQUE, v);
    INSERT INTO mm VALUES (1,'one');
    INSERT INTO mm VALUES (1,'two'), (2,'dos'), (1,'zzz')
      ON CONFLICT(k) DO UPDATE SET v = excluded.v
      WHERE excluded.v != 'zzz';  -- skip the 'zzz' update
    SELECT * FROM mm ORDER BY k;
} {1|two
2|dos}

# Omitted target with UNIQUE index: confirm it updates.
do_execsql_test_on_specific_db {:memory:} upsert-omitted-target-updates-unique {
    CREATE TABLE ou (a, b);
    CREATE UNIQUE INDEX ou_a ON ou(a);
    INSERT INTO ou VALUES (3,'x');
    INSERT INTO ou VALUES (3,'y')
      ON CONFLICT DO UPDATE SET b = excluded.b;
    SELECT * FROM ou;
} {3|y}

do_execsql_test_on_specific_db {:memory:} upsert-current-qualified.1 {
  CREATE TABLE dq (a INTEGER UNIQUE, b TEXT);
  INSERT INTO dq VALUES (1,'old');
  INSERT INTO dq VALUES (1,'new')
    ON CONFLICT(dq.a) DO UPDATE SET b = dq.b || '-' || excluded.b;
  SELECT * FROM dq;
} {1|old-new}

do_execsql_test_on_specific_db {:memory:} upsert-multicol-set.1 {
  CREATE TABLE dq (a INTEGER UNIQUE, b TEXT);
  INSERT INTO dq VALUES (1,'old');
  INSERT INTO dq VALUES (1,'new')
    ON CONFLICT(a) DO UPDATE SET (`a`,`b`) = (`excluded`.`a`, `excluded`.`b`);
  SELECT * FROM dq;
} {1|new}

do_execsql_test_on_specific_db {:memory:} upsert-where-predicate.1 {
  CREATE TABLE dq (a INTEGER UNIQUE, b TEXT);
  INSERT INTO dq VALUES (1,'old');
  INSERT INTO dq VALUES (1,'old')
    ON CONFLICT(a) DO UPDATE SET b = excluded.b WHERE dq.b != excluded.b;
  SELECT * FROM dq;
} {1|old}

do_execsql_test_on_specific_db {:memory:} upsert-doubly-qualified-target {
    CREATE TABLE dq (a UNIQUE, b);
    INSERT INTO dq VALUES (1,'old');
    INSERT INTO dq VALUES (1,'new')
      ON CONFLICT(main.dq.a) DO UPDATE SET b = excluded.b;
    SELECT * FROM dq;
} {1|new}

do_execsql_test_on_specific_db {:memory:} upsert-targets-chain {
    CREATE TABLE dq (a UNIQUE, b UNIQUE, c UNIQUE, value TEXT);
    CREATE UNIQUE INDEX dq_ab ON dq(a, b);
    INSERT INTO dq VALUES ('a1', 'a2', 'a3', 'aaa');
    INSERT INTO dq VALUES ('b1', 'b2', 'b3', 'bbb');
    INSERT INTO dq VALUES ('c1', 'c2', 'c3', 'ccc');
    INSERT INTO dq VALUES ('d1', 'd2', 'd3', 'ddd');
    INSERT INTO dq VALUES
      ('a1', 'a2', 'a3', 'upd1'), ('b1', 'b1', 'b1', 'upd2'), ('c2', 'c2', 'c2', 'upd3'), ('d3', 'd3', 'd3', 'upd4')
    ON CONFLICT (a, b) DO UPDATE SET value = excluded.value || '-a'
    ON CONFLICT (a) DO UPDATE SET value = excluded.value || '-b'
    ON CONFLICT (b) DO UPDATE SET value = excluded.value || '-c'
    ON CONFLICT DO UPDATE SET value = excluded.value || '-d';
    SELECT * FROM dq;
} {a1|a2|a3|upd1-a
b1|b2|b3|upd2-b
c1|c2|c3|upd3-c
d1|d2|d3|upd4-d}

# https://github.com/tursodatabase/turso/issues/3384
do_execsql_test_on_specific_db {:memory:} upsert-non-rowid-pk-target {
	create table phonebook(name text primary key, phonenumber text, validDate date);
	insert into phonebook values ('Alice','704-545-3333','2018-10-10');
	insert into phonebook values ('Alice','704-111-1111','2018-10-20') on conflict (name) do update set phonenumber=excluded.phonenumber, validDate=excluded.validDate;
    SELECT phonenumber, validDate FROM phonebook;
} {704-111-1111|2018-10-20}


# TODO: uncomment these when we support collations in indexes 
# (right now it errors on Parse Error: cannot use expressions in CREATE INDEX)
#
# Target specifies BINARY but the unique index is NOCASE: target should NOT match, so expect error
# do_execsql_test_in_memory_any_error upsert-collate-target-mismatch {
#     CREATE TABLE cm (name TEXT, v);
#     CREATE UNIQUE INDEX cm_name_nocase ON cm(name COLLATE NOCASE);
#     INSERT INTO cm VALUES ('Alice', 1);
#     INSERT INTO cm VALUES ('aLiCe', 2)
#       ON CONFLICT(name COLLATE BINARY) DO UPDATE SET v = excluded.v;
# }
#
# do_execsql_test_on_specific_db {:memory:} upsert-collate-omitted-target-matches {
#     CREATE TABLE co (name TEXT, v);
#     CREATE UNIQUE INDEX co_name_nocase ON co(name COLLATE NOCASE);
#     INSERT INTO co VALUES ('Alice', 1);
#     INSERT INTO co VALUES ('aLiCe', 9)
#       ON CONFLICT DO UPDATE SET v = excluded.v;
#     SELECT * FROM co;
# } {Alice|9}
#
#
# do_execsql_test_on_specific_db {:memory:} upsert-composite-collate-orderless {
#     CREATE TABLE cc (name TEXT, city TEXT, val);
#     CREATE UNIQUE INDEX cc_nc ON cc(name COLLATE NOCASE, city);
#     INSERT INTO cc VALUES ('Alice','SF','old');
#     INSERT INTO cc VALUES ('aLiCe','SF','new')
#       ON CONFLICT(city, name COLLATE NOCASE) DO UPDATE SET val = excluded.val;
#     SELECT * FROM cc;
# } {Alice|SF|new}