turso/testing/partial_idx.test

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

do_execsql_test_on_specific_db {:memory:} partial-index-unique-basic {
    CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT, status TEXT);
    CREATE UNIQUE INDEX idx_active_email ON users(email) WHERE status = 'active';
    INSERT INTO users VALUES (1, 'user@test.com', 'active');
    INSERT INTO users VALUES (2, 'user@test.com', 'inactive');
    INSERT INTO users VALUES (3, 'user@test.com', 'deleted');
    SELECT id, email, status FROM users ORDER BY id;
} {1|user@test.com|active
2|user@test.com|inactive
3|user@test.com|deleted}

do_execsql_test_in_memory_error_content partial-index-unique-violation {
    CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT, status TEXT);
    CREATE UNIQUE INDEX idx_active_email ON users(email) WHERE status = 'active';
    INSERT INTO users VALUES (1, 'user@test.com', 'active');
    INSERT INTO users VALUES (2, 'user@test.com', 'inactive');
    INSERT INTO users VALUES (3, 'user@test.com', 'deleted');
    INSERT INTO users VALUES (4, 'user@test.com', 'active');
} {UNIQUE constraint failed: users.email (19)}

do_execsql_test_on_specific_db {:memory:} partial-index-expression-where {
    CREATE TABLE products (id INTEGER PRIMARY KEY, sku TEXT, price INTEGER);
    CREATE UNIQUE INDEX idx_expensive ON products(sku) WHERE price > 100;
    INSERT INTO products VALUES (1, 'ABC123', 50);
    INSERT INTO products VALUES (2, 'ABC123', 150);
    INSERT INTO products VALUES (3, 'XYZ789', 200);
    INSERT INTO products VALUES (4, 'ABC123', 75);
    SELECT id, sku, price FROM products ORDER BY id;
} {1|ABC123|50
2|ABC123|150
3|XYZ789|200
4|ABC123|75}

do_execsql_test_in_memory_error_content partial-index-expensive-violation {
    CREATE TABLE products (id INTEGER PRIMARY KEY, sku TEXT, price INTEGER);
    CREATE UNIQUE INDEX idx_expensive ON products(sku) WHERE price > 100;
    INSERT INTO products VALUES (1, 'ABC123', 50);
    INSERT INTO products VALUES (2, 'ABC123', 150);
    INSERT INTO products VALUES (3, 'XYZ789', 200);
    INSERT INTO products VALUES (4, 'ABC123', 75);
    INSERT INTO products VALUES (5, 'ABC123', 250);
	-- should fail with unique sku where price > 100
} {UNIQUE constraint failed: products.sku (19)}

do_execsql_test_in_memory_error_content partial-index-expensive-violation-update {
    CREATE TABLE products (id INTEGER PRIMARY KEY, sku TEXT, price INTEGER);
    CREATE UNIQUE INDEX idx_expensive ON products(sku) WHERE price > 100;
    INSERT INTO products VALUES (1, 'ABC123', 50);
    INSERT INTO products VALUES (2, 'ABC123', 150);
    INSERT INTO products VALUES (3, 'XYZ789', 200);
    INSERT INTO products VALUES (4, 'ABC123', 75);
	UPDATE products SET price = 300 WHERE id = 1;
	-- should fail with unique sku where price > 100
} {UNIQUE constraint failed: products.sku (19)}

do_execsql_test_on_specific_db {:memory:} partial-index-null-where {
    CREATE TABLE items (id INTEGER PRIMARY KEY, code TEXT, category TEXT);
    CREATE UNIQUE INDEX idx_categorized ON items(code) WHERE category IS NOT NULL;
    INSERT INTO items VALUES (1, 'ITEM1', 'electronics');
    INSERT INTO items VALUES (2, 'ITEM1', NULL);
    INSERT INTO items VALUES (3, 'ITEM1', NULL);
    INSERT INTO items VALUES (4, 'ITEM2', 'books');
    SELECT id, code, category FROM items ORDER BY id;
} {1|ITEM1|electronics
2|ITEM1|
3|ITEM1|
4|ITEM2|books}


do_execsql_test_in_memory_error_content partial-index-function-where {
 CREATE TABLE docs (id INTEGER PRIMARY KEY, title TEXT);
    CREATE UNIQUE INDEX idx_lower_title ON docs(title) WHERE LOWER(title) = title;
    INSERT INTO docs VALUES (1, 'lowercase');
    INSERT INTO docs VALUES (2, 'UPPERCASE');
    INSERT INTO docs VALUES (3, 'lowercase');
} {UNIQUE constraint failed: docs.title (19)}

do_execsql_test_on_specific_db {:memory:} partial-index-multiple { 
    CREATE TABLE tasks (id INTEGER PRIMARY KEY, name TEXT, priority INTEGER, status TEXT);
    CREATE UNIQUE INDEX idx_urgent ON tasks(name) WHERE priority = 1;
    CREATE UNIQUE INDEX idx_completed ON tasks(name) WHERE status = 'done';
    
    INSERT INTO tasks VALUES (1, 'task1', 1, 'open');
    INSERT INTO tasks VALUES (2, 'task1', 2, 'open');
    INSERT INTO tasks VALUES (3, 'task1', 3, 'done');
    INSERT INTO tasks VALUES (4, 'task2', 1, 'done');
    
    SELECT id, name, priority, status FROM tasks ORDER BY id;
} {1|task1|1|open
2|task1|2|open
3|task1|3|done
4|task2|1|done}

do_execsql_test_in_memory_error_content partial-index-function-where {
    CREATE TABLE tasks (id INTEGER PRIMARY KEY, name TEXT, priority INTEGER, status TEXT);
    CREATE UNIQUE INDEX idx_urgent ON tasks(name) WHERE priority = 1;
    CREATE UNIQUE INDEX idx_completed ON tasks(name) WHERE status = 'done';
    
    INSERT INTO tasks VALUES (1, 'task1', 1, 'open');
    INSERT INTO tasks VALUES (2, 'task1', 2, 'open');
    INSERT INTO tasks VALUES (3, 'task1', 3, 'done');
    INSERT INTO tasks VALUES (4, 'task2', 1, 'done');
	INSERT INTO tasks VALUES (5, 'task1', 1, 'pending');
	--  should fail for unique name where priority = 1
} {UNIQUE constraint failed: tasks.name (19)}

do_execsql_test_in_memory_error_content partial-index-function-where-2 {
    CREATE TABLE tasks (id INTEGER PRIMARY KEY, name TEXT, priority INTEGER, status TEXT);
    CREATE UNIQUE INDEX idx_urgent ON tasks(name) WHERE priority = 1;
    CREATE UNIQUE INDEX idx_completed ON tasks(name) WHERE status = 'done';
    INSERT INTO tasks VALUES (1, 'task1', 1, 'open');
    INSERT INTO tasks VALUES (2, 'task1', 2, 'open');
    INSERT INTO tasks VALUES (3, 'task1', 3, 'done');
    INSERT INTO tasks VALUES (4, 'task2', 1, 'done');
	INSERT INTO tasks VALUES (6, 'task1', 2, 'done');
	 -- should fail for unique name where status = 'done'
} {UNIQUE constraint failed: tasks.name (19)}

do_execsql_test_on_specific_db {:memory:} partial-index-update-rowid {
    CREATE TABLE rowid_test (id INTEGER PRIMARY KEY, val TEXT, flag INTEGER);
    CREATE UNIQUE INDEX idx_flagged ON rowid_test(val) WHERE flag = 1;
    INSERT INTO rowid_test VALUES (1, 'test', 1);
    INSERT INTO rowid_test VALUES (2, 'test', 0);
    UPDATE rowid_test SET id = 10 WHERE id = 1;
    SELECT id, val, flag FROM rowid_test ORDER BY id;
} {2|test|0
10|test|1}

do_execsql_test_in_memory_error_content partial-index-update-complex {
	CREATE TABLE complex (id INTEGER PRIMARY KEY, a TEXT, b INTEGER, c TEXT);
    CREATE UNIQUE INDEX idx_complex ON complex(a) WHERE b > 10 AND c = 'active';
    INSERT INTO complex VALUES (1, 'dup', 5, 'active');
    INSERT INTO complex VALUES (2, 'dup', 15, 'inactive');
    INSERT INTO complex VALUES (3, 'dup', 15, 'active');
    INSERT INTO complex VALUES (4, 'dup', 20, 'active');
} {UNIQUE constraint failed: complex.a (19)}

do_execsql_test_on_specific_db {:memory:} partial-index-delete {
	CREATE TABLE products (id INTEGER PRIMARY KEY, sku TEXT, price INTEGER);
    CREATE UNIQUE INDEX idx_expensive ON products(sku) WHERE price > 100;
    INSERT INTO products VALUES (1, 'ABC123', 50);
    INSERT INTO products VALUES (2, 'ABC123', 150);
    INSERT INTO products VALUES (3, 'XYZ789', 200);
    INSERT INTO products VALUES (4, 'ABC123', 75);
    DELETE FROM products WHERE price > 100;
    INSERT INTO products VALUES (5, 'ABC123', 500);
    INSERT INTO products VALUES (6, 'XYZ789', 600);
    SELECT id, sku, price FROM products WHERE price > 100 ORDER BY id;
} {5|ABC123|500
6|XYZ789|600}

do_execsql_test_on_specific_db {:memory:} partial-index-delete-function-where {
    CREATE TABLE func_del (id INTEGER PRIMARY KEY, name TEXT);
    CREATE UNIQUE INDEX idx_lower ON func_del(name) WHERE LOWER(name) = name;
    
    INSERT INTO func_del VALUES (1, 'lowercase');
    INSERT INTO func_del VALUES (2, 'UPPERCASE');
    INSERT INTO func_del VALUES (3, 'MixedCase');
    DELETE FROM func_del WHERE LOWER(name) = name;

    -- Should be able to insert lowercase now
    INSERT INTO func_del VALUES (4, 'lowercase');
    INSERT INTO func_del VALUES (5, 'another');
    SELECT id, name FROM func_del ORDER BY id;
} {2|UPPERCASE
3|MixedCase
4|lowercase
5|another}

do_execsql_test_in_memory_error_content partial-index-delete-all {
    CREATE TABLE del_all (id INTEGER PRIMARY KEY, val TEXT, flag INTEGER);
    CREATE UNIQUE INDEX idx_all ON del_all(val) WHERE flag = 1;
    INSERT INTO del_all VALUES (1, 'test', 1), (2, 'test', 0), (3, 'other', 1);
    DELETE FROM del_all;
    -- Should be able to insert anything now
    INSERT INTO del_all VALUES (4, 'test', 1);
    INSERT INTO del_all VALUES (5, 'test', 1);
} {UNIQUE constraint failed: del_all.val (19)}

do_execsql_test_on_specific_db {:memory:} partial-index-delete-cascade-scenario {
    CREATE TABLE parent_del (id INTEGER PRIMARY KEY, status TEXT);
    CREATE TABLE child_del (id INTEGER PRIMARY KEY, parent_id INTEGER, name TEXT, active INTEGER);
    CREATE UNIQUE INDEX idx_active_child ON child_del(name) WHERE active = 1;
    
    INSERT INTO parent_del VALUES (1, 'active'), (2, 'inactive');
    INSERT INTO child_del VALUES (1, 1, 'child1', 1);
    INSERT INTO child_del VALUES (2, 1, 'child2', 1);
    INSERT INTO child_del VALUES (3, 2, 'child1', 0);
    -- Simulate cascade by deleting children of parent 1
    DELETE FROM child_del WHERE parent_id = 1;
    -- Should now allow these since active children are gone
    INSERT INTO child_del VALUES (4, 2, 'child1', 1);
    INSERT INTO child_del VALUES (5, 2, 'child2', 1);
    SELECT COUNT(*) FROM child_del WHERE active = 1;
} {2}

do_execsql_test_on_specific_db {:memory:} partial-index-delete-null-where {
    CREATE TABLE null_del (id INTEGER PRIMARY KEY, code TEXT, category TEXT);
    CREATE UNIQUE INDEX idx_with_category ON null_del(code) WHERE category IS NOT NULL;
    INSERT INTO null_del VALUES (1, 'CODE1', 'cat1');
    INSERT INTO null_del VALUES (2, 'CODE1', NULL);
    INSERT INTO null_del VALUES (3, 'CODE2', 'cat2');
    INSERT INTO null_del VALUES (4, 'CODE1', NULL);
    -- Delete the one with category
    DELETE FROM null_del WHERE code = 'CODE1' AND category IS NOT NULL;
    -- Should allow this now
    INSERT INTO null_del VALUES (5, 'CODE1', 'cat3');
    
    SELECT id, code, category FROM null_del WHERE code = 'CODE1' ORDER BY id;
} {2|CODE1|
4|CODE1|
5|CODE1|cat3}

do_execsql_test_on_specific_db {:memory:} partial-index-delete-complex-where {
    CREATE TABLE complex_del (id INTEGER PRIMARY KEY, a INTEGER, b INTEGER, c TEXT);
    CREATE UNIQUE INDEX idx_complex ON complex_del(c) WHERE a > 10 AND b < 20;
    INSERT INTO complex_del VALUES (1, 15, 10, 'dup');
    INSERT INTO complex_del VALUES (2, 5, 15, 'dup');
    INSERT INTO complex_del VALUES (3, 15, 25, 'dup');
    INSERT INTO complex_del VALUES (4, 20, 10, 'unique');
    -- Delete the one entry that's actually in the partial index
    DELETE FROM complex_del WHERE a > 10 AND b < 20;
    
    -- Should now allow this since we deleted the conflicting entry
    INSERT INTO complex_del VALUES (5, 12, 18, 'dup');
    SELECT COUNT(*) FROM complex_del WHERE c = 'dup';
} {3}


# Entering predicate via UPDATE should conflict with an existing in-predicate key
do_execsql_test_in_memory_error_content partial-index-update-enter-conflict-1 {
    CREATE TABLE products (id INTEGER PRIMARY KEY, sku TEXT, price INTEGER);
    CREATE UNIQUE INDEX idx_expensive ON products(sku) WHERE price > 100;
    INSERT INTO products VALUES (1, 'ABC123', 50);
    INSERT INTO products VALUES (2, 'ABC123', 150);
    UPDATE products SET price = 200 WHERE id = 1;
} {UNIQUE constraint failed: products.sku (19)}

# Staying in predicate but changing key to a conflicting key should fail
do_execsql_test_in_memory_error_content partial-index-update-change-key-conflict {
    CREATE TABLE products (id INTEGER PRIMARY KEY, sku TEXT, price INTEGER);
    CREATE UNIQUE INDEX idx_expensive ON products(sku) WHERE price > 100;
    INSERT INTO products VALUES (1, 'ABC123', 150);
    INSERT INTO products VALUES (2, 'XYZ789', 200);
    UPDATE products SET sku = 'XYZ789' WHERE id = 1;
} {UNIQUE constraint failed: products.sku (19)}

# Exiting predicate via UPDATE should remove index entry; then re-entering later may fail
do_execsql_test_in_memory_error_content partial-index-update-exit-then-reenter {
    CREATE TABLE products (id INTEGER PRIMARY KEY, sku TEXT, price INTEGER);
    CREATE UNIQUE INDEX idx_expensive ON products(sku) WHERE price > 100;
    INSERT INTO products VALUES (1, 'ABC123', 150);
    UPDATE products SET price = 50 WHERE id = 1;
    INSERT INTO products VALUES (2, 'ABC123', 200);
    UPDATE products SET price = 300 WHERE id = 1;
} {UNIQUE constraint failed: products.sku (19)}

# Multi-row UPDATE causing multiple rows to enter predicate together should conflict
do_execsql_test_in_memory_error_content partial-index-update-multirow-conflict {
    CREATE TABLE products (id INTEGER PRIMARY KEY, sku TEXT, price INTEGER);
    CREATE UNIQUE INDEX idx_expensive ON products(sku) WHERE price > 100;
    INSERT INTO products VALUES (1, 'ABC123', 50);
    INSERT INTO products VALUES (2, 'ABC123', 150);
    INSERT INTO products VALUES (3, 'ABC123', 75);
    UPDATE products SET price = 150 WHERE sku = 'ABC123';
} {UNIQUE constraint failed: products.sku (19)}

# Update of unrelated columns should not affect partial index membership
do_execsql_test_on_specific_db {:memory:} partial-index-update-unrelated-column {
    CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT, status TEXT, note TEXT);
    CREATE UNIQUE INDEX idx_active_email ON users(email) WHERE status = 'active';
    INSERT INTO users VALUES (1, 'u@test.com', 'active',  'n1');
    INSERT INTO users VALUES (2, 'u@test.com', 'inactive','n2');
    UPDATE users SET note = 'changed' WHERE id = 2;
    SELECT id,email,status,note FROM users ORDER BY id;
} {1|u@test.com|active|n1
2|u@test.com|inactive|changed}

# NULL -> NOT NULL transition enters predicate and may conflict
do_execsql_test_in_memory_error_content partial-index-update-null-enters-conflict {
    CREATE TABLE items (id INTEGER PRIMARY KEY, code TEXT, category TEXT);
    CREATE UNIQUE INDEX idx_categorized ON items(code) WHERE category IS NOT NULL;
    INSERT INTO items VALUES (1,'CODE1','electronics');
    INSERT INTO items VALUES (2,'CODE1',NULL);
    UPDATE items SET category = 'x' WHERE id = 2;
} {UNIQUE constraint failed: items.code (19)}

# Function predicate: UPDATE causes entry into predicate -> conflict
do_execsql_test_in_memory_error_content partial-index-update-function-enters {
    CREATE TABLE docs (id INTEGER PRIMARY KEY, title TEXT);
    CREATE UNIQUE INDEX idx_lower_title ON docs(title) WHERE LOWER(title) = title;
    INSERT INTO docs VALUES (1, 'lowercase');
    INSERT INTO docs VALUES (2, 'UPPERCASE');
    UPDATE docs SET title = 'lowercase' WHERE id = 2;
} {UNIQUE constraint failed: docs.title (19)}

# Multi-column unique key with partial predicate: conflict on UPDATE entering predicate
do_execsql_test_in_memory_error_content partial-index-update-multicol-enter-conflict {
    CREATE TABLE inv (id INTEGER PRIMARY KEY, sku TEXT, region TEXT, price INT);
    CREATE UNIQUE INDEX idx_sr ON inv(sku,region) WHERE price > 100;
    INSERT INTO inv VALUES (1,'A','US', 50);
    INSERT INTO inv VALUES (2,'A','US',150);
    INSERT INTO inv VALUES (3,'A','EU',150);
    UPDATE inv SET price = 200 WHERE id = 1;
} {UNIQUE constraint failed: inv.sku, inv.region (19)}

# Staying in predicate but changing second key part to collide should fail
do_execsql_test_in_memory_error_content partial-index-update-multicol-change-second {
    CREATE TABLE inv2 (id INTEGER PRIMARY KEY, sku TEXT, region TEXT, price INT);
    CREATE UNIQUE INDEX idx_sr2 ON inv2(sku,region) WHERE price > 100;
    INSERT INTO inv2 VALUES (1,'A','US',150);
    INSERT INTO inv2 VALUES (2,'A','EU',150);
    UPDATE inv2 SET region = 'US' WHERE id = 2;
} {UNIQUE constraint failed: inv2.sku, inv2.region (19)}

# UPDATE that leaves predicate and then changes key should be allowed, then re-entering may fail
do_execsql_test_in_memory_error_content partial-index-update-exit-change-key-reenter {
    CREATE TABLE t (id INTEGER PRIMARY KEY, a TEXT, b INT);
    CREATE UNIQUE INDEX idx_a ON t(a) WHERE b > 0;
    INSERT INTO t VALUES (1,'K', 10);
    INSERT INTO t VALUES (2,'X', 10);
    UPDATE t SET b = 0       WHERE id = 1;
    UPDATE t SET a = 'X'     WHERE id = 1;
    UPDATE t SET b = 5       WHERE id = 1;
} {UNIQUE constraint failed: t.a (19)}

# Rowid (INTEGER PRIMARY KEY) change while in predicate should not self-conflict
do_execsql_test_on_specific_db {:memory:} partial-index-update-rowid-no-self-conflict {
    CREATE TABLE rowid_test (id INTEGER PRIMARY KEY, val TEXT, flag INT);
    CREATE UNIQUE INDEX idx_flagged ON rowid_test(val) WHERE flag = 1;
    INSERT INTO rowid_test VALUES (1,'v',1);
    UPDATE rowid_test SET id = 9 WHERE id = 1;
    SELECT id,val,flag FROM rowid_test ORDER BY id;
} {9|v|1}

# Batch UPDATE that toggles predicate truth for multiple rows; ensure net uniqueness is enforced
do_execsql_test_in_memory_error_content partial-index-update-batch-crossing {
    CREATE TABLE p (id INTEGER PRIMARY KEY, k TEXT, x INT);
    CREATE UNIQUE INDEX idx_k ON p(k) WHERE x > 0;
    INSERT INTO p VALUES (1,'A', 1);
    INSERT INTO p VALUES (2,'A', 0);
    INSERT INTO p VALUES (3,'A', 0);
    UPDATE p SET x = CASE id WHEN 1 THEN 0 ELSE 1 END;
} {UNIQUE constraint failed: p.k (19)}

# UPDATE with WHERE predicate true, but changing to a unique new key while staying in predicate
do_execsql_test_on_specific_db {:memory:} partial-index-update-stay-in-predicate-change-to-unique {
    CREATE TABLE q (id INTEGER PRIMARY KEY, k TEXT, x INT);
    CREATE UNIQUE INDEX idx_kx ON q(k) WHERE x > 0;
    INSERT INTO q VALUES (1,'A',1);
    INSERT INTO q VALUES (2,'B',1);
    UPDATE q SET k='C' WHERE id=1;   -- stays in predicate, key now unique
    SELECT id,k,x FROM q ORDER BY id;
} {1|C|1
2|B|1}

do_execsql_test_in_memory_error_content partial-index-update-only-predicate-col-error {
    CREATE TABLE r2 (id INTEGER PRIMARY KEY, k TEXT, x INT);
    CREATE UNIQUE INDEX idx_k ON r2(k) WHERE x > 0;
    INSERT INTO r2 VALUES (1,'A',0);
    INSERT INTO r2 VALUES (2,'A',1);
    UPDATE r2 SET x = 1 WHERE id = 1;
} {UNIQUE constraint failed: r2.k (19)}


do_execsql_test_on_specific_db {:memory:} partial-index-multi-predicate-references {
    CREATE TABLE r2 (id INTEGER PRIMARY KEY, k TEXT, x INT);
    CREATE UNIQUE INDEX idx_k ON r2(k) WHERE x < 10 AND id > 10;
    INSERT INTO r2 (k,x) VALUES ('A',1), ('A',2), ('A',3), ('A',4), ('A',5), ('A',6), ('A',7), ('A',8), ('A', 9), ('A', 10), ('A', 10);
	-- now `id` will be greater than 10, so anything added with k='A' and x<10 should conflict
    INSERT INTO r2 (k,x) VALUES ('A',11);
	INSERT INTO r2 (k,x) VALUES ('A',12);
	SELECT id FROM r2 ORDER BY id DESC LIMIT 1;
} {13}

do_execsql_test_in_memory_error_content partial-index-multi-predicate-references-rowid-alais {
    CREATE TABLE r2 (id INTEGER PRIMARY KEY, k TEXT, x INT);
    CREATE UNIQUE INDEX idx_k ON r2(k) WHERE x < 10 AND id > 10;
    INSERT INTO r2 (k,x) VALUES ('A',1), ('A',2), ('A',3), ('A',4), ('A',5), ('A',6), ('A',7), ('A',8), ('A', 9), ('A', 10), ('A', 10);
	-- now `id` will be greater than 10, so anything added with k='A' and x<10 should conflict
    INSERT INTO r2 (k,x) VALUES ('A',11);
	INSERT INTO r2 (k,x) VALUES ('A',12);
	INSERT INTO r2 (k,x) VALUES ('A', 3);
	INSERT INTO r2 (k,x) VALUES ('A', 9);
	 -- should fail now
} {UNIQUE constraint failed: r2.k (19)}


do_execsql_test_in_memory_any_error upsert-partial-donothing-basic {
  CREATE TABLE u1(id INTEGER PRIMARY KEY, email TEXT, status TEXT, note TEXT);
  CREATE UNIQUE INDEX idx_active_email ON u1(email) WHERE status='active';
  INSERT INTO u1(email,status,note)
    VALUES('a@test','active','n3')
    ON CONFLICT(email) DO NOTHING;
}

do_execsql_test_on_specific_db {:memory:} upsert-partial-doupdate-basic {
  CREATE TABLE u2(id INTEGER PRIMARY KEY, email TEXT, status TEXT, note TEXT);
  CREATE UNIQUE INDEX idx_active_email ON u2(email) WHERE status='active';

  INSERT INTO u2 VALUES (1,'a@test','active','n1');

  INSERT INTO u2(email,status,note)
    VALUES('a@test','active','nNEW')
    ON CONFLICT DO UPDATE SET note=excluded.note;

  SELECT id,email,status,note FROM u2;
} {1|a@test|active|nNEW}

do_execsql_test_on_specific_db {:memory:} upsert-partial-doupdate-leave-predicate {
  CREATE TABLE u3(id INTEGER PRIMARY KEY, email TEXT, status TEXT);
  CREATE UNIQUE INDEX idx_active_email ON u3(email) WHERE status='active';

  INSERT INTO u3 VALUES (1,'a@test','active');

  INSERT INTO u3(email,status)
    VALUES('a@test','active')
    ON CONFLICT DO UPDATE SET status='inactive';

  -- After update, the conflicting row no longer participates in idx predicate.
  -- Insert should now succeed for active variant.
  INSERT INTO u3 VALUES (2,'a@test','active');

  SELECT id,email,status FROM u3 ORDER BY id;
} {1|a@test|inactive 2|a@test|active}

do_execsql_test_on_specific_db {:memory:} upsert-partial-doupdate-where-skip {
  CREATE TABLE u4(id INTEGER PRIMARY KEY, email TEXT, status TEXT, hits INT DEFAULT 0);
  CREATE UNIQUE INDEX idx_active_email ON u4(email) WHERE status='active';

  INSERT INTO u4 VALUES(1,'a@test','active',5);

  INSERT INTO u4(email,status)
    VALUES('a@test','active')
    ON CONFLICT DO UPDATE SET hits=hits+1 WHERE excluded.status='inactive';

  -- filter false => no UPDATE; constraint remains => INSERT must be suppressed,
  -- SQLite semantics: when WHERE is false, the UPSERT does nothing (no row added).
  SELECT id,email,status,hits FROM u4 ORDER BY id;
} {1|a@test|active|5}

do_execsql_test_on_specific_db {:memory:} upsert-partial-omitted-target-matches {
  CREATE TABLE u6(id INTEGER PRIMARY KEY, email TEXT, status TEXT, n INT);
  CREATE UNIQUE INDEX idx_active_email ON u6(email) WHERE status='active';
  INSERT INTO u6 VALUES (1,'a@test','active',0);

  INSERT INTO u6(email,status,n)
    VALUES('a@test','active',10)
    ON CONFLICT DO UPDATE SET n = excluded.n;

  SELECT id,email,status,n FROM u6;
} {1|a@test|active|10}

do_execsql_test_on_specific_db {:memory:} upsert-partial-multicol-leave-predicate {
  CREATE TABLE m2(id INTEGER PRIMARY KEY, sku TEXT, region TEXT, price INT);
  CREATE UNIQUE INDEX idx_sr ON m2(sku,region) WHERE price > 100;

  INSERT INTO m2 VALUES(1,'A','US',150);

  INSERT INTO m2(sku,region,price)
    VALUES('A','US',150)
    ON CONFLICT DO UPDATE SET price=50;

  -- Now predicate false; insert another high-price duplicate should succeed
  INSERT INTO m2 VALUES(2,'A','US',200);

  SELECT id,sku,region,price FROM m2 ORDER BY id;
} {1|A|US|50 2|A|US|200}

do_execsql_test_on_specific_db {:memory:} upsert-partial-func-predicate {
  CREATE TABLE d1(id INTEGER PRIMARY KEY, title TEXT, n INT DEFAULT 0);
  CREATE UNIQUE INDEX idx_lower_title ON d1(title) WHERE LOWER(title)=title;

  INSERT INTO d1 VALUES(1,'lower',0);

  INSERT INTO d1(title)
    VALUES('lower')
    ON CONFLICT DO UPDATE SET n = n+1;

  SELECT id,title,n FROM d1;
} {1|lower|1}

do_execsql_test_on_specific_db {:memory:} upsert-partial-rowid-predicate {
  CREATE TABLE r1(id INTEGER PRIMARY KEY, k TEXT, x INT, hits INT DEFAULT 0);
  CREATE UNIQUE INDEX idx_k ON r1(k) WHERE x < 10 AND id > 10;

  -- create ids 1..12, with ('A', >=10) rows to push rowid>10
  INSERT INTO r1(k,x) VALUES('A',10),('A',10),('A',10),('A',10),('A',10),
                            ('A',10),('A',10),('A',10),('A',10),('A',10),('A',11),('A',12);

  -- Now conflict for ('A', 5) is against partial index (id>10 & x<10)
  INSERT INTO r1(k,x,hits)
    VALUES('A',5,1)
    ON CONFLICT DO UPDATE SET hits = hits + excluded.hits;

  SELECT k, SUM(hits) FROM r1 GROUP BY k;
} {A|1}

# EXCLUDED usage inside DO UPDATE stays within predicate and changes key
do_execsql_test_on_specific_db {:memory:} upsert-partial-excluded-rewrite {
  CREATE TABLE ex1(id INTEGER PRIMARY KEY, a TEXT, b INT, c TEXT);
  CREATE UNIQUE INDEX idx_a ON ex1(a) WHERE b>0;

  INSERT INTO ex1 VALUES(1,'X',1,'old');

  INSERT INTO ex1(a,b,c)
    VALUES('X',1,'new')
    ON CONFLICT DO UPDATE SET c = excluded.c, b = excluded.b;

  SELECT id,a,b,c FROM ex1;
} {1|X|1|new}

do_execsql_test_on_specific_db {:memory:} upsert-partial-stay-change-to-unique {
  CREATE TABLE s1(id INTEGER PRIMARY KEY, a TEXT, flag INT);
  CREATE UNIQUE INDEX idx_a ON s1(a) WHERE flag=1;

  INSERT INTO s1 VALUES(1,'K',1);

  INSERT INTO s1(a,flag)
    VALUES('K',1)
    ON CONFLICT DO UPDATE SET a='K2';

  SELECT id,a,flag FROM s1;
} {1|K2|1}

do_execsql_test_on_specific_db {:memory:} upsert-partial-toggle-predicate {
  CREATE TABLE tgl(id INTEGER PRIMARY KEY, k TEXT, x INT);
  CREATE UNIQUE INDEX idx_k ON tgl(k) WHERE x>0;

  INSERT INTO tgl VALUES(1,'A',1);

  -- Conflicts on 'A', flips x to 0 (leaves predicate)
  INSERT INTO tgl(k,x)
    VALUES('A',1)
    ON CONFLICT DO UPDATE SET x=0;

  -- Now another 'A' with x>0 should insert
  INSERT INTO tgl VALUES(2,'A',5);

  SELECT id,k,x FROM tgl ORDER BY id;
} {1|A|0 2|A|5}

do_execsql_test_in_memory_error_content upsert-partial-target-pk-only {
  CREATE TABLE pko(id INTEGER PRIMARY KEY, k TEXT, x INT);
  CREATE UNIQUE INDEX idx_k ON pko(k) WHERE x>0;

  INSERT INTO pko VALUES(1,'A',1);

  -- Target PK only; conflict is on idx_k, so DO UPDATE must NOT fire and error is raised
  INSERT INTO pko(id,k,x)
    VALUES(2,'A',1)
    ON CONFLICT(id) DO UPDATE SET x=99;
} {UNIQUE constraint failed: pko.k (19)}

do_execsql_test_on_specific_db {:memory:} upsert-partial-omitted-no-conflict {
  CREATE TABLE insfree(id INTEGER PRIMARY KEY, k TEXT, x INT);
  CREATE UNIQUE INDEX idx_k ON insfree(k) WHERE x>0;

  INSERT INTO insfree VALUES(1,'A',1);

  -- x=0 => not in predicate, so no conflict; row must be inserted
  INSERT INTO insfree(k,x)
    VALUES('A',0)
    ON CONFLICT DO NOTHING;

  SELECT COUNT(*) FROM insfree WHERE k='A';
} {2}