test/expridx2.test

# 2026-04-23
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
#
# Tests for omitting the update of an index when the new value is the
# same as the old.
#
set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix expridx2

do_execsql_test 1.0 {
  PRAGMA journal_mode = wal;
  CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT);
  INSERT INTO t1 VALUES(1, '{a: 10, b: 20, c: 30}');
  INSERT INTO t1 VALUES(2, '{a: 11, b: 21, c: 31}');
  INSERT INTO t1 VALUES(3, '{a: 12, b: 22, c: 32}');
  INSERT INTO t1 VALUES(4, '{a: 13, b: 23, c: 33}');

  CREATE INDEX t1a ON t1( (b->>'a') );
  CREATE INDEX t1b ON t1( (b->>'b') );
  CREATE INDEX t1c ON t1( (b->>'c') );

  CREATE TABLE t2(a INTEGER PRIMARY KEY, b, c);
  CREATE INDEX t2b ON t2( b );
  INSERT INTO t2 VALUES(1, 1, 1);
  INSERT INTO t2 VALUES(2, 2, 2);
  INSERT INTO t2 VALUES(3, 3, 3);
  INSERT INTO t2 VALUES(4, 4, 4);
} {wal}

proc nWrite {sql} {
  sqlite3_db_status db CACHE_WRITE 1
  execsql $sql
  lindex [sqlite3_db_status db CACHE_WRITE 0] 1
}

do_test 1.1 {
  nWrite { UPDATE t1 SET b = json_set(b, '$.a', b->>'a' + 1) }
} {2}

do_test 1.2 {
  nWrite { 
    UPDATE t1 SET b = json_set( 
        json_set(b, '$.a', b->>'a' + 1),
        '$.b', b->>'b' + 1
    );
  }
} {3}

do_test 1.3 {
  nWrite { 
    UPDATE t1 SET b = '{a:1, b:2, c:3}' WHERE rowid=1;
  }
} {4}

integrity_check 1.4

do_test 1.5 {
  nWrite { UPDATE t2 SET b = b+1, c=c+1 }
} {2}

do_test 1.6 {
  nWrite { UPDATE t2 SET b = b, c=c+1 }
} {2}

do_test 1.7 {
  nWrite { UPDATE t2 SET c=c+1 }
} {1}

finish_test