mirror of
https://github.com/tursodatabase/limbo.git
synced 2025-07-07 12:35:00 +00:00
7ca902979d
When an `UPDATE` statement modifies a table's `INTEGER PRIMARY KEY` (which acts as a `rowid` alias) alongside other indexed columns, the index entries were incorrectly retaining the old `rowid`. This led to stale index references, causing subsequent queries to return incorrect results. This change ensures that when the `rowid` alias is part of the `SET` clause in an `UPDATE` statement, the new `rowid` value is used for generating and updating index records. This guarantees that all index entries correctly point to the updated row, resolving the data inconsistency.
272 lines
No EOL
9.4 KiB
Tcl
Executable file
272 lines
No EOL
9.4 KiB
Tcl
Executable file
#!/usr/bin/env tclsh
|
|
set testdir [file dirname $argv0]
|
|
source $testdir/tester.tcl
|
|
|
|
|
|
do_execsql_test_on_specific_db {:memory:} basic-update {
|
|
create table temp (t1 integer);
|
|
insert into temp values (1);
|
|
update temp set t1 = 2;
|
|
select * from temp;
|
|
} {2}
|
|
|
|
do_execsql_test_on_specific_db {:memory:} update-mul {
|
|
create table temp (t1 integer);
|
|
insert into temp values (1),(2.0),('3'),('4.0');
|
|
update temp set t1 = 2;
|
|
select * from temp;
|
|
} {2
|
|
2
|
|
2
|
|
2}
|
|
|
|
do_execsql_test_on_specific_db {:memory:} update-where {
|
|
create table temp (a,b,c);
|
|
insert into temp values (1,2,33);
|
|
insert into temp values (1,22,33);
|
|
insert into temp values (1,22,33);
|
|
update temp set a = 6 where b = 2;
|
|
select * from temp;
|
|
} {6|2|33
|
|
1|22|33
|
|
1|22|33}
|
|
|
|
|
|
do_execsql_test_on_specific_db {:memory:} update-where-2 {
|
|
create table temp (a,b,c);
|
|
insert into temp values (1,22,33);
|
|
insert into temp values (1,22,33);
|
|
insert into temp values (1,22,33);
|
|
insert into temp values (6,22,33);
|
|
insert into temp values (1,22,33);
|
|
insert into temp values (1,22,33);
|
|
update temp set b = 100000 where a = 6;
|
|
select b from temp where a = 6;
|
|
} {100000}
|
|
|
|
|
|
do_execsql_test_on_specific_db {:memory:} update-all-many {
|
|
create table temp (a,b,c);
|
|
insert into temp values (1,22,33),(1,22,33),(1,22,33),(1,22,33),(1,22,33),(1,22,33),(1,22,33),(1,22,33);
|
|
update temp set a = 1234234234234234;
|
|
select COUNT(*) from temp where a = 1234234234234234;
|
|
} {8}
|
|
|
|
do_execsql_test_on_specific_db {:memory:} update-large-small {
|
|
create table temp (a,b,c);
|
|
insert into temp values (randomblob(1024), 1, 2);
|
|
update temp set a = 'a';
|
|
select * from temp;
|
|
} {a|1|2}
|
|
|
|
|
|
do_execsql_test_on_specific_db {:memory:} update-null {
|
|
create table temp (a,b,c);
|
|
insert into temp values (NULL, 1, 2);
|
|
insert into temp values (3, 4, 5);
|
|
update temp set a = 10 where a IS NULL;
|
|
select * from temp;
|
|
} {10|1|2
|
|
3|4|5}
|
|
|
|
do_execsql_test_on_specific_db {:memory:} update-not-null-1 {
|
|
create table temp (a,b not null,c);
|
|
insert into temp values (1, 2, 3);
|
|
update temp set b = 999;
|
|
select * from temp;
|
|
} {1|999|3}
|
|
|
|
do_execsql_test_in_memory_any_error update-not-null-2 {
|
|
create table temp (a,b not null,c);
|
|
insert into temp values (1, 2, 3);
|
|
update temp set b = NULL;
|
|
}
|
|
|
|
do_execsql_test_on_specific_db {:memory:} update-not-null-3 {
|
|
create table temp (a not null, b not null, c not null);
|
|
update temp set a = null, b = null, c = null;
|
|
select * from temp;
|
|
} {}
|
|
|
|
do_execsql_test_on_specific_db {:memory:} update-mixed-types {
|
|
create table temp (a);
|
|
insert into temp values (1);
|
|
insert into temp values ('2');
|
|
insert into temp values (3.0);
|
|
insert into temp values (X'01');
|
|
update temp set a = 'updated';
|
|
select * from temp;
|
|
} {updated
|
|
updated
|
|
updated
|
|
updated}
|
|
|
|
do_execsql_test_on_specific_db {:memory:} update-with-expression {
|
|
create table temp (a, b);
|
|
insert into temp values (1, 10);
|
|
insert into temp values (2, 20);
|
|
insert into temp values (3, 30);
|
|
update temp set a = b * 3 / 2;
|
|
select * from temp;
|
|
} {15|10
|
|
30|20
|
|
45|30}
|
|
|
|
do_execsql_test_on_specific_db {:memory:} update-self-reference {
|
|
create table temp (a, b);
|
|
insert into temp values (1, 10);
|
|
insert into temp values (2, 20);
|
|
update temp set a = b where a = 1;
|
|
select * from temp;
|
|
} {10|10
|
|
2|20}
|
|
|
|
do_execsql_test_on_specific_db {:memory:} update-self-ref-all {
|
|
create table temp (a, b, c);
|
|
insert into temp values (1, 2, 3);
|
|
insert into temp values (4, 5, 6);
|
|
update temp set a = b;
|
|
select * from temp;
|
|
} {2|2|3
|
|
5|5|6}
|
|
|
|
do_execsql_test_on_specific_db {:memory:} update-large-text {
|
|
create table temp (a);
|
|
insert into temp values ('short');
|
|
update temp set a = 'this is a very large text field that exceeds the default cell size and should trigger overflow handling in the btree implementation';
|
|
select * from temp;
|
|
} {{this is a very large text field that exceeds the default cell size and should trigger overflow handling in the btree implementation}}
|
|
|
|
do_execsql_test_on_specific_db {:memory:} update-with-null-condition {
|
|
create table temp (a, b);
|
|
insert into temp values (NULL, 1);
|
|
insert into temp values (2, NULL);
|
|
insert into temp values (3, 4);
|
|
update temp set a = 99 where b IS NULL;
|
|
select * from temp;
|
|
} {|1
|
|
99|
|
|
3|4}
|
|
|
|
do_execsql_test_on_specific_db {:memory:} update-to-null {
|
|
create table temp (a, b);
|
|
insert into temp values (1, 10);
|
|
insert into temp values (2, 20);
|
|
update temp set a = NULL where b = 10;
|
|
select * from temp;
|
|
} {|10
|
|
2|20}
|
|
|
|
do_execsql_test_on_specific_db {:memory:} update-with-randomblob {
|
|
create table temp (a, b);
|
|
insert into temp values (randomblob(100), 1);
|
|
update temp set a = randomblob(200);
|
|
select length(a) from temp;
|
|
} {200}
|
|
|
|
do_execsql_test_on_specific_db {:memory:} update-multiple-columns {
|
|
create table temp (a, b, c);
|
|
insert into temp values (1, 2, 3);
|
|
insert into temp values (4, 5, 6);
|
|
update temp set a = 10, b = 20, c = 30;
|
|
select * from temp;
|
|
} {10|20|30
|
|
10|20|30}
|
|
|
|
do_execsql_test_on_specific_db {:memory:} update-true-expr {
|
|
create table temp (a, b, c);
|
|
insert into temp values (1, 2, 3);
|
|
insert into temp values (4, 5, 6);
|
|
update temp set a = 10, b = 20, c = 30 where 1;
|
|
select * from temp;
|
|
} {10|20|30
|
|
10|20|30}
|
|
|
|
# https://github.com/tursodatabase/turso/issues/1625
|
|
do_execsql_test_on_specific_db {:memory:} update_cache_full_regression_test_#1625 {
|
|
CREATE TABLE t(x);
|
|
INSERT INTO t VALUES (randomblob(4096));
|
|
UPDATE t SET x = randomblob(4096) WHERE rowid = 1;
|
|
SELECT count(*) FROM t;
|
|
} {1}
|
|
|
|
if {[info exists ::env(SQLITE_EXEC)] && ($::env(SQLITE_EXEC) eq "scripts/limbo-sqlite3-index-experimental" || $::env(SQLITE_EXEC) eq "sqlite3")} {
|
|
do_execsql_test_on_specific_db {:memory:} update_index_regression_test {
|
|
CREATE TABLE t(x, y);
|
|
CREATE INDEX tx ON t (x);
|
|
CREATE UNIQUE INDEX tyu ON t (y);
|
|
INSERT INTO t VALUES (1, 1);
|
|
SELECT x FROM t; -- uses tx index
|
|
SELECT y FROM t; -- uses ty index
|
|
UPDATE t SET x=2, y=2;
|
|
SELECT x FROM t; -- uses tx index
|
|
SELECT y FROM t; -- uses ty index
|
|
} {1
|
|
1
|
|
2
|
|
2}
|
|
|
|
do_execsql_test_on_specific_db {:memory:} update_rowid_alias_index_regression_test {
|
|
CREATE TABLE t(a INTEGER PRIMARY KEY, b);
|
|
CREATE INDEX idx_b ON t (b);
|
|
INSERT INTO t VALUES (1, 'foo');
|
|
SELECT a FROM t WHERE b = 'foo';
|
|
UPDATE t SET a = 2, b = 'bar';
|
|
SELECT a FROM t WHERE b = 'bar';
|
|
} {1
|
|
2}
|
|
}
|
|
|
|
do_execsql_test_on_specific_db {:memory:} update_where_or_regression_test {
|
|
CREATE TABLE t (a INTEGER);
|
|
INSERT INTO t VALUES (1), ('hi');
|
|
UPDATE t SET a = X'6C6F76656C795F7265766F6C74' WHERE ~ 'gorgeous_thropy' OR NOT -3830873834.233324;
|
|
SELECT * from t;
|
|
} {lovely_revolt
|
|
lovely_revolt}
|
|
|
|
do_execsql_test_in_memory_any_error update_primary_key_constraint_error {
|
|
CREATE TABLE eye (study REAL, spring BLOB, save TEXT, thank REAL, election INTEGER, PRIMARY KEY (election));
|
|
INSERT INTO eye VALUES (183559032.521585, x'6625d092', 'Trial six should.', 2606132742.43174, 2817);
|
|
INSERT INTO eye VALUES (78255586.9204539, x'651061e8', 'World perhaps.', -5815764.49018679, 1917);
|
|
UPDATE eye SET election = 6150;
|
|
}
|
|
|
|
do_execsql_test_in_memory_any_error update_primary_key_constraint_error_2 {
|
|
CREATE TABLE eye (study REAL, spring BLOB, save TEXT, thank REAL, election INTEGER, PRIMARY KEY (election));
|
|
INSERT INTO eye VALUES (183559032.521585, x'6625d092', 'Trial six should.', 2606132742.43174, 2817);
|
|
INSERT INTO eye VALUES (78255586.9204539, x'651061e8', 'World perhaps.', -5815764.49018679, 1917);
|
|
INSERT INTO eye VALUES (53.3274327094467, x'f574c507', 'Senior wish degree.', -423.432750526747, 2650);
|
|
INSERT INTO eye VALUES (-908148213048.983, x'6d812051', 'Possible able.', 101.171781837336, 4100);
|
|
INSERT INTO eye VALUES (-572332773760.924, x'd7a4d9fb', 'Money catch expect.', -271065488.756746, 4667);
|
|
UPDATE eye SET election = 6150 WHERE election != 1917;
|
|
}
|
|
|
|
do_execsql_test_in_memory_any_error update_primary_key_constraint_error_3 {
|
|
CREATE TABLE eye (study REAL, spring BLOB, save TEXT, thank REAL, election INTEGER, PRIMARY KEY (election));
|
|
INSERT INTO eye VALUES (183559032.521585, x'6625d092', 'Trial six should.', 2606132742.43174, 2817);
|
|
INSERT INTO eye VALUES (78255586.9204539, x'651061e8', 'World perhaps.', -5815764.49018679, 1917);
|
|
INSERT INTO eye VALUES (53.3274327094467, x'f574c507', 'Senior wish degree.', -423.432750526747, 2650);
|
|
INSERT INTO eye VALUES (-908148213048.983, x'6d812051', 'Possible able.', 101.171781837336, 4100);
|
|
INSERT INTO eye VALUES (-572332773760.924, x'd7a4d9fb', 'Money catch expect.', -271065488.756746, 4667);
|
|
UPDATE eye SET election = 6150 WHERE election > 1000 AND study > 1;
|
|
}
|
|
|
|
do_execsql_test_in_memory_any_error update_primary_key_constraint_error_4 {
|
|
CREATE TABLE t(a PRIMARY KEY INTEGER, b UNIQUE);
|
|
INSERT INTO t(b) VALUES (100), (200), (300);
|
|
UPDATE t SET a = 1;
|
|
}
|
|
|
|
do_execsql_test_in_memory_any_error update_primary_key_unique_constraint_error {
|
|
CREATE TABLE t(a PRIMARY KEY INTEGER, b UNIQUE);
|
|
INSERT INTO t(b) VALUES (100), (200), (300);
|
|
UPDATE t SET b = 2;
|
|
}
|
|
|
|
do_execsql_test_on_specific_db {:memory:} update-single-rowid {
|
|
CREATE TABLE t(x INTEGER PRIMARY KEY);
|
|
INSERT INTO t VALUES (1);
|
|
UPDATE t SET x = 2 WHERE x = 1;
|
|
SELECT * FROM t;
|
|
} {2} |