aljaz/turso
1
0
Fork 0
mirror of https://github.com/aljazceru/turso.git synced 2025-12-17 08:34:19 +01:00
Code Issues Packages Projects Releases Wiki Activity
Files
main
turso/testing/insert.test
PThorpe92 b83921d838 Add TCL tests for insert or replace handling
2025-11-17 08:23:16 -05:00

1054 lines
34 KiB
Tcl
Executable File
Raw Permalink Blame History

#!/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:} basic-insert {
create table temp (t1 integer, primary key (t1));
insert into temp values (1);
select * from temp;
} {1}
do_execsql_test_on_specific_db {:memory:} must-be-int-insert {
create table temp (t1 integer, primary key (t1));
insert into temp values (1),(2.0),('3'),('4.0');
select * from temp;
} {1
2
3
4}
do_execsql_test_on_specific_db {:memory:} strict-basic-creation {
CREATE TABLE test1 (id INTEGER, name TEXT, price REAL) STRICT;
INSERT INTO test1 VALUES(1, 'item1', 10.5);
SELECT * FROM test1;
} {1|item1|10.5}
do_execsql_test_on_specific_db {:memory:} self-insert-sum-pk {
CREATE TABLE t(a INTEGER PRIMARY KEY, b INTEGER);
INSERT INTO t(b) VALUES(1),(2);
INSERT INTO t(b) SELECT sum(b) FROM t;
SELECT a, b FROM t ORDER BY a;
} {1|1
2|2
3|3}
# Reproducer for https://github.com/tursodatabase/turso/issues/2822
do_execsql_test_on_specific_db {:memory:} strict-type-case-insensitivity {
CREATE TABLE test1 (id integer, name text, price real) STRICT;
INSERT INTO test1 VALUES(1, 'item1', 10.5);
SELECT * FROM test1;
} {1|item1|10.5}
do_execsql_test_in_memory_any_error strict-require-datatype {
CREATE TABLE test2 (id INTEGER, name) STRICT;
}
do_execsql_test_in_memory_any_error strict-valid-datatypes {
CREATE TABLE test2 (id INTEGER, value DATETIME) STRICT;
}
do_execsql_test_in_memory_any_error strict-type-enforcement {
CREATE TABLE test3 (id INTEGER, name TEXT, price REAL) STRICT;
INSERT INTO test3 VALUES(1, 'item1', 'not-a-number');
}
do_execsql_test_on_specific_db {:memory:} strict-type-coercion {
CREATE TABLE test4 (id INTEGER, name TEXT, price REAL) STRICT;
INSERT INTO test4 VALUES(1, 'item1', '10.5');
SELECT typeof(price), price FROM test4;
} {real|10.5}
do_execsql_test_on_specific_db {:memory:} strict-any-flexibility {
CREATE TABLE test5 (id INTEGER, data ANY) STRICT;
INSERT INTO test5 VALUES(1, 100);
INSERT INTO test5 VALUES(2, 'text');
INSERT INTO test5 VALUES(3, 3.14);
SELECT id, typeof(data) FROM test5 ORDER BY id;
} {1|integer
2|text
3|real}
do_execsql_test_on_specific_db {:memory:} strict-any-preservation {
CREATE TABLE test6 (id INTEGER, code ANY) STRICT;
INSERT INTO test6 VALUES(1, '000123');
SELECT typeof(code), code FROM test6;
} {text|000123}
do_execsql_test_in_memory_any_error strict-int-vs-integer-pk {
CREATE TABLE test8 (id INT PRIMARY KEY, name TEXT) STRICT
INSERT INTO test8 VALUES(NULL, 'test');
}
do_execsql_test_on_specific_db {:memory:} strict-integer-pk-behavior {
CREATE TABLE test9 (id INTEGER PRIMARY KEY, name TEXT) STRICT;
INSERT INTO test9 VALUES(NULL, 'test');
SELECT id, name FROM test9;
} {1|test}
do_execsql_test_on_specific_db {:memory:} strict-mixed-inserts {
CREATE TABLE test11 (
id INTEGER PRIMARY KEY,
name TEXT,
price REAL,
quantity INT,
tags ANY
) STRICT;
INSERT INTO test11 VALUES(1, 'item1', 10.5, 5, 'tag1');
INSERT INTO test11 VALUES(2, 'item2', 20.75, 10, 42);
SELECT id, name, price, quantity, typeof(tags) FROM test11 ORDER BY id;
} {1|item1|10.5|5|text
2|item2|20.75|10|integer}
do_execsql_test_on_specific_db {:memory:} strict-update-basic {
CREATE TABLE test1 (id INTEGER, name TEXT, price REAL) STRICT;
INSERT INTO test1 VALUES(1, 'item1', 10.5);
UPDATE test1 SET price = 15.75 WHERE id = 1;
SELECT * FROM test1;
} {1|item1|15.75}
do_execsql_test_in_memory_any_error strict-update-type-enforcement {
CREATE TABLE test2 (id INTEGER, name TEXT, price REAL) STRICT;
INSERT INTO test2 VALUES(1, 'item1', 10.5);
UPDATE test2 SET price = 'not-a-number' WHERE id = 1;
}
do_execsql_test_on_specific_db {:memory:} strict-update-type-coercion {
CREATE TABLE test3 (id INTEGER, name TEXT, price REAL) STRICT;
INSERT INTO test3 VALUES(1, 'item1', 10.5);
UPDATE test3 SET price = '15.75' WHERE id = 1;
SELECT id, typeof(price), price FROM test3;
} {1|real|15.75}
do_execsql_test_on_specific_db {:memory:} strict-update-any-flexibility {
CREATE TABLE test4 (id INTEGER, data ANY) STRICT;
INSERT INTO test4 VALUES(1, 100);
UPDATE test4 SET data = 'text' WHERE id = 1;
INSERT INTO test4 VALUES(2, 'original');
UPDATE test4 SET data = 3.14 WHERE id = 2;
SELECT id, typeof(data), data FROM test4 ORDER BY id;
} {1|text|text
2|real|3.14}
do_execsql_test_on_specific_db {:memory:} strict-update-any-preservation {
CREATE TABLE test5 (id INTEGER, code ANY) STRICT;
INSERT INTO test5 VALUES(1, 'text');
UPDATE test5 SET code = '000123' WHERE id = 1;
SELECT typeof(code), code FROM test5;
} {text|000123}
do_execsql_test_in_memory_any_error strict-update-not-null-constraint {
CREATE TABLE test7 (id INTEGER, name TEXT NOT NULL) STRICT;
INSERT INTO test7 VALUES(1, 'name');
UPDATE test7 SET name = NULL WHERE id = 1;
}
# Uncomment following test case when unique constraint is added
do_execsql_test_in_memory_any_error strict-update-pk-constraint {
CREATE TABLE test8 (id INTEGER PRIMARY KEY, name TEXT) STRICT;
INSERT INTO test8 VALUES(1, 'name1');
INSERT INTO test8 VALUES(2, 'name2');
UPDATE test8 SET id = 2 WHERE id = 1;
}
do_execsql_test_on_specific_db {:memory:} strict-update-multiple-columns {
CREATE TABLE test9 (id INTEGER, name TEXT, price REAL, quantity INT) STRICT;
INSERT INTO test9 VALUES(1, 'item1', 10.5, 5);
UPDATE test9 SET name = 'updated', price = 20.75, quantity = 10 WHERE id = 1;
SELECT * FROM test9;
} {1|updated|20.75|10}
do_execsql_test_on_specific_db {:memory:} strict-update-where-clause {
CREATE TABLE test10 (id INTEGER, category TEXT, price REAL) STRICT;
INSERT INTO test10 VALUES(1, 'A', 10);
INSERT INTO test10 VALUES(2, 'A', 20);
INSERT INTO test10 VALUES(3, 'B', 30);
UPDATE test10 SET price = price * 2 WHERE category = 'A';
SELECT id, price FROM test10 ORDER BY id;
} {1|20.0
2|40.0
3|30.0}
do_execsql_test_on_specific_db {:memory:} strict-update-expression {
CREATE TABLE test11 (id INTEGER, name TEXT, price REAL, discount REAL) STRICT;
INSERT INTO test11 VALUES(1, 'item1', 100, 0.1);
UPDATE test11 SET price = price - (price * discount);
SELECT id, price FROM test11;
} {1|90.0}
do_execsql_test_on_specific_db {:memory:} duplicate-set {
CREATE TABLE t (x, y);
INSERT INTO t VALUES(1, 'A');
UPDATE t SET y = 'B', y = 'C', y = 'D' WHERE x = 1;
SELECT x, y FROM t;
} {1|D}
do_execsql_test_on_specific_db {:memory:} named-insert-1 {
CREATE TABLE test (id INTEGER PRIMARY KEY AUTOINCREMENT, col_a TEXT, col_b TEXT, col_c TEXT, col_d TEXT);
INSERT INTO test (col_b, col_d, col_a, col_c) VALUES ('1', '2', '3', '4');
SELECT * FROM test;
} {1|3|1|4|2}
do_execsql_test_on_specific_db {:memory:} named-insert-2 {
CREATE TABLE test (id INTEGER PRIMARY KEY AUTOINCREMENT, col_a TEXT DEFAULT 'Empty', col_b TEXT, col_c TEXT, col_d TEXT);
INSERT INTO test (col_b, col_d, col_c) VALUES ('1', '2', '4');
SELECT * FROM test;
} {1|Empty|1|4|2}
do_execsql_test_on_specific_db {:memory:} multi-rows {
CREATE TABLE test (id INTEGER PRIMARY KEY AUTOINCREMENT, col);
INSERT INTO test (col) VALUES (1),(1);
SELECT * FROM test;
} {1|1
2|1}
do_execsql_test_on_specific_db {:memory:} unique_insert_no_pkey {
CREATE TABLE t2 (x INTEGER, y INTEGER UNIQUE);
INSERT INTO t2 (y) VALUES (1);
INSERT INTO t2 (y) VALUES (6);
SELECT * FROM t2;
} {|1
|6}
do_execsql_test_on_specific_db {:memory:} unique_insert_with_pkey {
CREATE TABLE t2 (x INTEGER PRIMARY KEY, y INTEGER UNIQUE);
INSERT INTO t2 (y) VALUES (1);
INSERT INTO t2 (y) VALUES (6);
SELECT * FROM t2;
} {1|1
2|6}
do_execsql_test_on_specific_db {:memory:} not_null_insert {
CREATE TABLE t2 (y INTEGER NOT NULL);
INSERT INTO t2 (y) VALUES (1);
INSERT INTO t2 (y) VALUES (6);
SELECT * FROM t2;
} {1
6}
do_execsql_test_in_memory_any_error not_null_primary_key {
CREATE TABLE t2 (y INT PRIMARY KEY NOT NULL);
INSERT INTO t2 (y) VALUES (1);
INSERT INTO t2 (y) VALUES (NULL);
}
do_execsql_test_in_memory_any_error not_null_insert_select {
CREATE TABLE t2 (y INT PRIMARY KEY NOT NULL);
CREATE TABLE d (y INT);
INSERT INTO d (y) VALUES (1);
INSERT INTO d (y) VALUES (NULL);
INSERT INTO t2 (y) SELECT y FROM d;
}
do_execsql_test_in_memory_any_error not_null_insert_multiple_values {
CREATE TABLE t2 (y INT PRIMARY KEY NOT NULL);
INSERT INTO t2 (y) VALUES (1), (NULL);
}
do_execsql_test_on_specific_db {:memory:} insert_from_select_1 {
CREATE TABLE t (a);
CREATE TABLE t2 (b, c);
INSERT INTO t2 values (1, 2), (10, 20);
INSERT INTO t SELECT b FROM t2;
SELECT * FROM t;
INSERT INTO t SELECT c FROM t2;
SELECT * FROM t;
} {1
10
1
10
2
20}
do_execsql_test_on_specific_db {:memory:} insert_from_select_where {
CREATE TABLE t (a);
CREATE TABLE t2 (b, c);
INSERT INTO t2 values (1, 2), (10, 20);
INSERT INTO t SELECT b FROM t2 WHERE b < 10;
SELECT * FROM t;
INSERT INTO t SELECT c FROM t2 WHERE c > 2;
SELECT * FROM t;
} {1
1
20}
do_execsql_test_on_specific_db {:memory:} insert_from_select_union_all {
CREATE TABLE t1 (x INTEGER);
CREATE TABLE t2 (x INTEGER);
CREATE TABLE t3 (x INTEGER);
INSERT INTO t1 VALUES(1),(2),(3);
INSERT INTO t2 VALUES(4),(5),(6);
INSERT INTO t3 VALUES(7),(8),(9);
INSERT INTO t1 SELECT x FROM t2 UNION ALL SELECT x FROM t3;
SELECT * FROM t1;
} {1
2
3
4
5
6
7
8
9}
do_execsql_test_on_specific_db {:memory:} insert_from_select_union_all_where {
CREATE TABLE t1 (x INTEGER);
CREATE TABLE t2 (x INTEGER);
CREATE TABLE t3 (x INTEGER);
INSERT INTO t1 VALUES(1),(2),(3);
INSERT INTO t2 VALUES(4),(5),(6);
INSERT INTO t3 VALUES(7),(8),(9);
INSERT INTO t1 SELECT x FROM t2 WHERE x != 4 UNION ALL SELECT x FROM t3 WHERE x == 8;
SELECT * FROM t1;
} {1
2
3
5
6
8}
do_execsql_test_on_specific_db {:memory:} insert_from_select_union_all_values {
CREATE TABLE t(a, b);
INSERT INTO t(a, b) SELECT * FROM (VALUES(3, 3)) UNION ALL VALUES(4, 4);
SELECT a, b FROM t ORDER BY rowid;
} {3|3
4|4}
do_execsql_test_on_specific_db {:memory:} values-union-all {
CREATE TABLE test (a INTEGER NOT NULL, b INTEGER NOT NULL);
INSERT INTO test(a, b) VALUES(3, 3) UNION ALL VALUES(4, 4);
SELECT a, b FROM test ORDER BY rowid;
} {3|3
4|4}
do_execsql_test_on_specific_db {:memory:} insert_from_select_same_table {
CREATE TABLE t (a INTEGER PRIMARY KEY, b);
INSERT INTO t(b) VALUES (1),(2),(3);
SELECT * FROM t;
INSERT INTO t(b) SELECT b FROM t;
SELECT * FROM t;
} {1|1
2|2
3|3
1|1
2|2
3|3
4|1
5|2
6|3}
do_execsql_test_on_specific_db {:memory:} insert_from_select_same_table_2 {
CREATE TABLE t (a INTEGER PRIMARY KEY, b, c);
INSERT INTO t(b, c) VALUES (1, 100),(2, 200),(3, 300);
SELECT * FROM t;
INSERT INTO t(b, c) SELECT b,c FROM t;
SELECT * FROM t;
} {1|1|100
2|2|200
3|3|300
1|1|100
2|2|200
3|3|300
4|1|100
5|2|200
6|3|300}
do_execsql_test_on_specific_db {:memory:} insert_from_select_union {
CREATE TABLE t (a, b);
CREATE TABLE t2 (b, c);
INSERT INTO t2 VALUES (1, 100), (2, 200);
INSERT INTO t SELECT * FROM t UNION SELECT * FROM t2;
SELECT * FROM t;
} {1|100
2|200}
do_execsql_test_on_specific_db {:memory:} insert_from_select_union-2 {
CREATE TABLE t (a, b);
CREATE TABLE t2 (b, c);
INSERT INTO t SELECT * FROM t UNION values(1, 100), (2, 200);
SELECT * FROM t;
} {1|100
2|200}
do_execsql_test_on_specific_db {:memory:} insert_from_select_intersect {
CREATE TABLE t (a, b);
CREATE TABLE t1 (a, b);
CREATE TABLE t2 (a, b);
INSERT INTO t1 VALUES (1, 100), (2, 200);
INSERT INTO t2 VALUES (2, 200), (3, 300);
INSERT INTO t SELECT * FROM t1 INTERSECT SELECT * FROM t2;
SELECT * FROM t;
} {2|200}
do_execsql_test_on_specific_db {:memory:} insert_from_select_intersect-2 {
CREATE TABLE t (a, b);
CREATE TABLE t1 (a, b);
CREATE TABLE t2 (a, b);
CREATE TABLE t3 (a, b);
INSERT INTO t1 VALUES (1, 100), (2, 200);
INSERT INTO t2 VALUES (2, 200), (3, 300);
INSERT INTO t3 VALUES (2, 200), (4, 400);
INSERT INTO t SELECT * FROM t1 INTERSECT SELECT * FROM t2 INTERSECT SELECT * FROM t3;
SELECT * FROM t;
} {2|200}
do_execsql_test_on_specific_db {:memory:} insert_from_select_except {
CREATE TABLE t (a, b);
CREATE TABLE t1 (a, b);
CREATE TABLE t2 (a, b);
INSERT INTO t1 VALUES (1, 100), (2, 200);
INSERT INTO t2 VALUES (2, 200), (3, 300);
INSERT INTO t SELECT * FROM t1 EXCEPT SELECT * FROM t2;
SELECT * FROM t;
} {1|100}
do_execsql_test_on_specific_db {:memory:} negative-primary-integer-key {
CREATE TABLE t (a INTEGER PRIMARY KEY);
insert into t values (-2),(13);
select * from t order by a asc;
} {-2
13}
do_execsql_test_on_specific_db {:memory:} not-null-rowid-alias {
CREATE TABLE t (a INTEGER PRIMARY KEY NOT NULL, b);
insert into t values (1, 2);
select * from t;
} {1|2}
do_execsql_test_on_specific_db {:memory:} rowid-overflow-random-generation {
CREATE TABLE q (x INTEGER PRIMARY KEY, y);
INSERT INTO q VALUES (9223372036854775807, 1);
INSERT INTO q(y) VALUES (2);
INSERT INTO q(y) VALUES (3);
SELECT COUNT(*) FROM q;
} {3}
# regression test for incorrect processing of record header in the case of large text columns
do_execsql_test_on_specific_db {:memory:} large-text-index-seek {
CREATE TABLE t (x TEXT, y);
CREATE INDEX t_idx ON t(x);
INSERT INTO t VALUES (replace(hex(zeroblob(1000)), '00', 'a') || 'a', 1);
INSERT INTO t VALUES (replace(hex(zeroblob(1000)), '00', 'a') || 'b', 2);
INSERT INTO t VALUES (replace(hex(zeroblob(1000)), '00', 'a') || 'c', 3);
INSERT INTO t VALUES (replace(hex(zeroblob(1000)), '00', 'a') || 'd', 4);
INSERT INTO t VALUES (replace(hex(zeroblob(1000)), '00', 'a') || 'e', 5);
INSERT INTO t VALUES (replace(hex(zeroblob(1000)), '00', 'a') || 'f', 6);
INSERT INTO t VALUES (replace(hex(zeroblob(1000)), '00', 'a') || 'g', 7);
INSERT INTO t VALUES (replace(hex(zeroblob(1000)), '00', 'a') || 'h', 8);
SELECT COUNT(*) FROM t WHERE x >= replace(hex(zeroblob(100)), '00', 'a');
} {8}
do_execsql_test_skip_lines_on_specific_db 1 {:memory:} double-quote-string-literals {
.dbconfig dqs_dml on
CREATE TABLE test (id INTEGER, name TEXT);
INSERT INTO test (id, name) VALUES (1, "Dave");
INSERT INTO test (id,name) VALUES (2, 'Alice');
SELECT * FROM test ORDER BY id;
} {1|Dave
2|Alice}
do_execsql_test_skip_lines_on_specific_db 1 {:memory:} mixed-quote-types {
.dbconfig dqs_dml on
CREATE TABLE mixed (a TEXT, b TEXT, c TEXT);
INSERT INTO mixed (a,b,c) VALUES ("double", 'single', "another");
SELECT * FROM mixed;
} {double|single|another}
do_execsql_test_skip_lines_on_specific_db 1 {:memory:} double-quote-regression-original-case {
.dbconfig dqs_dml on
CREATE TABLE users (
id INTEGER,
name TEXT,
email TEXT
);
INSERT INTO users (name) values ("Dave");
SELECT name FROM users;
} {Dave}
do_execsql_test_skip_lines_on_specific_db 1 {:memory:} double-quote-multiple-rows {
.dbconfig dqs_dml on
CREATE TABLE items (id INTEGER, description TEXT, category TEXT);
INSERT INTO items (id, description, category) VALUES
(1, "First_item", "category_a"),
(2, 'Second_item', "category_b"),
(3, "Third_item", 'category_c');
SELECT id, description, category FROM items ORDER BY id;
} {1|First_item|category_a
2|Second_item|category_b
3|Third_item|category_c}
do_execsql_test_skip_lines_on_specific_db 1 {:memory:} double-quote-inner-quotes-preservation {
.dbconfig dqs_dml on
CREATE TABLE inner_quotes_test (id INTEGER, content TEXT);
INSERT INTO inner_quotes_test VALUES (1, '"foo"');
INSERT INTO inner_quotes_test VALUES (2, "'bar'");
SELECT id, content FROM inner_quotes_test ORDER BY id;
} {1|"foo"
2|'bar'}
# regression test for incorrect order of column in insert
do_execsql_test_on_specific_db {:memory:} insert-tricky-column-order-values {
create table t (x, y, z);
insert into t(z, x) values (1, 2), (3, 4);
insert into t(y, z) values (5, 6), (7, 8);
select * from t;
} {2||1
4||3
|5|6
|7|8}
do_execsql_test_on_specific_db {:memory:} insert-tricky-column-order-table {
create table t (x, y, z);
create table q (x, y, z);
insert into q values (1, 2, 3), (4, 5, 6);
insert into t(z, x) select y, x from q;
insert into t(y, z) select z, y from q;
select * from t;
} {1||2
4||5
|3|2
|6|5}
do_execsql_test_on_specific_db {:memory:} insert-explicit-rowid {
create table t (x, y, z);
insert into t(z, x, y, rowid) values (1, 2, 3, 4), (5, 6, 7, 8);
insert into t(z, x, y, rowid) values (9, 10, 11, 12);
select rowid, x, y, z from t;
} {4|2|3|1
8|6|7|5
12|10|11|9}
do_execsql_test_on_specific_db {:memory:} insert-explicit-rowid-with-rowidalias {
create table t (x, y INTEGER PRIMARY KEY, z);
insert into t(z, x, y, rowid) values (1, 2, 3, 4), (5, 6, 7, 8);
insert into t(z, x, y, rowid) values (9, 10, 11, 12);
insert into t(z, x, rowid, y) values (-1, -2, -3, -4), (-5, -6, -7, -8);
insert into t(z, x, rowid, y) values (-9, -10, -11, -12);
select rowid, x, y, z from t;
} {-12|-10|-12|-9
-8|-6|-8|-5
-4|-2|-4|-1
4|2|4|1
8|6|8|5
12|10|12|9}
do_execsql_test_on_specific_db {:memory:} null-value-insert-null-type-column {
CREATE TABLE test (id INTEGER,name NULL);
INSERT INTO test (id, name) VALUES (1, NULL);
SELECT * FROM test;
} {1|}
# https://github.com/tursodatabase/turso/issues/1710
do_execsql_test_in_memory_error_content uniq_constraint {
CREATE TABLE test (id INTEGER unique);
insert into test values (1);
insert into test values (1);
} {UNIQUE constraint failed: test.id (19)}
do_execsql_test_in_memory_error_content insert-explicit-rowid-conflict {
create table t (x);
insert into t(rowid, x) values (1, 1);
insert into t(rowid, x) values (1, 2);
} {UNIQUE constraint failed: t.rowid (19)}
# RETURNING clause tests
do_execsql_test_on_specific_db {:memory:} returning-basic-column {
CREATE TABLE test (id INTEGER, name TEXT, value REAL);
INSERT INTO test (id, name, value) VALUES (1, 'test', 10.5) RETURNING id;
} {1}
do_execsql_test_on_specific_db {:memory:} returning-multiple-columns {
CREATE TABLE test (id INTEGER, name TEXT, value REAL);
INSERT INTO test (id, name, value) VALUES (1, 'test', 10.5) RETURNING id, name;
} {1|test}
do_execsql_test_on_specific_db {:memory:} returning-all-columns {
CREATE TABLE test (id INTEGER, name TEXT, value REAL);
INSERT INTO test (id, name, value) VALUES (1, 'test', 10.5) RETURNING *;
} {1|test|10.5}
do_execsql_test_on_specific_db {:memory:} returning-literal {
CREATE TABLE test (id INTEGER);
INSERT INTO test (id) VALUES (1) RETURNING 42;
} {42}
do_execsql_test_on_specific_db {:memory:} returning-arithmetic {
CREATE TABLE test (id INTEGER, value INTEGER);
INSERT INTO test (id, value) VALUES (1, 10) RETURNING 2 * value;
} {20}
do_execsql_test_on_specific_db {:memory:} returning-complex-expression {
CREATE TABLE test (id INTEGER, x INTEGER, y INTEGER);
INSERT INTO test (id, x, y) VALUES (1, 5, 3) RETURNING x + y * 2;
} {11}
do_execsql_test_on_specific_db {:memory:} returning-function-call {
CREATE TABLE test (id INTEGER, name TEXT);
INSERT INTO test (id, name) VALUES (1, 'hello') RETURNING upper(name);
} {HELLO}
do_execsql_test_on_specific_db {:memory:} returning-mixed-expressions {
CREATE TABLE test (id INTEGER, name TEXT, value INTEGER);
INSERT INTO test (id, name, value) VALUES (1, 'test', 10) RETURNING id, upper(name), value * 3;
} {1|TEST|30}
do_execsql_test_on_specific_db {:memory:} returning-multiple-rows {
CREATE TABLE test (id INTEGER, name TEXT);
INSERT INTO test (id, name) VALUES (1, 'first'), (2, 'second') RETURNING id, name;
} {1|first
2|second}
do_execsql_test_on_specific_db {:memory:} returning-with-autoincrement {
CREATE TABLE test (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT);
INSERT INTO test (name) VALUES ('test') RETURNING id, name;
} {1|test}
do_execsql_test_on_specific_db {:memory:} returning-rowid {
CREATE TABLE test (name TEXT);
INSERT INTO test (name) VALUES ('test') RETURNING rowid, name;
} {1|test}
do_execsql_test_on_specific_db {:memory:} returning-null-values {
CREATE TABLE test (id INTEGER, name TEXT, value INTEGER);
INSERT INTO test (id, name, value) VALUES (1, NULL, NULL) RETURNING id, name, value;
} {1||}
do_catchsql_test unknown-identifier-in-values-clause {
DROP TABLE IF EXISTS tt;
CREATE TABLE tt (x);
INSERT INTO tt VALUES(asdf);
} {1 {no such column: asdf}}
do_catchsql_test unknown-backtick-identifier-in-values-clause {
DROP TABLE IF EXISTS tt;
CREATE TABLE tt (x);
INSERT INTO tt VALUES(`asdf`);
} {1 {no such column: `asdf`}}
do_execsql_test_in_memory_error_content null-insert-in-nulltype-column-notnull-constraint {
CREATE TABLE test (id INTEGER,name NULL NOT NULL);
INSERT INTO test (id, name) VALUES (1, NULL);
} {NOT NULL constraint failed}
do_execsql_test_on_specific_db {:memory:} returning-true-literal {
CREATE TABLE test (id INTEGER, value TEXT);
INSERT INTO test (id, value) VALUES (1, true) RETURNING id, value;
} {1|1}
do_execsql_test_on_specific_db {:memory:} returning-false-literal {
CREATE TABLE test (id INTEGER, value TEXT);
INSERT INTO test (id, value) VALUES (1, false) RETURNING id, value;
} {1|0}
do_execsql_test_on_specific_db {:memory:} boolean-literal-edgecase {
CREATE TABLE true (id INTEGER, value TEXT);
INSERT INTO true (id, value) VALUES (1, true) RETURNING id, value;
} {1|1}
do_execsql_test_on_specific_db {:memory:} boolean-literal-edgecase-false {
CREATE TABLE false (id INTEGER, true TEXT);
INSERT INTO false (id, true) VALUES (1, false) RETURNING id, false;
} {1|0}
do_execsql_test_on_specific_db {:memory:} default-values-population {
CREATE TABLE t (x INTEGER PRIMARY KEY, y DEFAULT 666, z);
INSERT INTO t DEFAULT VALUES;
INSERT INTO t DEFAULT VALUES;
SELECT * FROM t;
} {1|666|
2|666|}
do_execsql_test_on_specific_db {:memory:} set-explicit-null-default-value {
CREATE TABLE t (id INTEGER PRIMARY KEY, x DEFAULT 1);
INSERT INTO t(id, x) VALUES (1, 2);
SELECT * FROM t;
UPDATE t SET x = NULL WHERE id = 1;
SELECT * FROM t;
} {1|2
1|}
do_execsql_test_on_specific_db {:memory:} insert-select-nested-subquery {
CREATE TABLE t (x);
INSERT INTO T VALUES (1);
INSERT INTO T SELECT * FROM (SELECT * FROM t);
SELECT * FROM t;
} {1
1}
# Regression test for: https://github.com/tursodatabase/turso/issues/3567 (used to panic, now returns unique constraint error)
do_execsql_test_in_memory_any_error insert-rowid-select-rowid {
CREATE TABLE t(a);
INSERT INTO t VALUES (1);
INSERT INTO t(rowid) SELECT rowid FROM t;
}
do_execsql_test_in_memory_any_error insert-rowidalias-select-rowid {
CREATE TABLE t(a INTEGER PRIMARY KEY);
INSERT INTO t VALUES (1);
INSERT INTO t(a) SELECT rowid FROM t;
}
do_execsql_test_on_specific_db {:memory:} insert-rowid-select-rowid-success {
CREATE TABLE t(a);
INSERT INTO t VALUES (2);
INSERT INTO t(a) SELECT rowid FROM t;
SELECT * FROM t;
} {2
1}
# Due to a bug in SQLite, this check is needed to maintain backwards compatibility with rowid alias
# SQLite docs: https://sqlite.org/lang_createtable.html#rowids_and_the_integer_primary_key
# Issue: https://github.com/tursodatabase/turso/issues/3665
do_execsql_test_on_specific_db {:memory:} insert-rowid-backwards-compability {
CREATE TABLE t(a INTEGER PRIMARY KEY DESC);
INSERT INTO t(a) VALUES (123);
SELECT rowid, * FROM t;
} {1|123}
do_execsql_test_on_specific_db {:memory:} insert-rowid-backwards-compability-2 {
CREATE TABLE t(a INTEGER, PRIMARY KEY (a DESC));
INSERT INTO t(a) VALUES (123);
SELECT rowid, * FROM t;
} {123|123}
do_execsql_test_on_specific_db {:memory:} ignore-pk-conflict {
CREATE TABLE t(a INTEGER PRIMARY KEY);
INSERT INTO t VALUES (1),(2),(3);
INSERT OR IGNORE INTO t VALUES (2);
SELECT a FROM t ORDER BY a;
} {1
2
3}
do_execsql_test_on_specific_db {:memory:} ignore-unique-conflict {
CREATE TABLE t(a INTEGER, b TEXT UNIQUE);
INSERT INTO t VALUES (1,'x'),(2,'y');
INSERT OR IGNORE INTO t VALUES (3,'y');
SELECT a,b FROM t ORDER BY a;
} {1|x
2|y}
do_execsql_test_on_specific_db {:memory:} ignore-multi-unique-conflict {
CREATE TABLE t(a UNIQUE, b UNIQUE, c);
INSERT INTO t VALUES (1,10,100),(2,20,200);
INSERT OR IGNORE INTO t VALUES (1,30,300); -- conflicts on a
INSERT OR IGNORE INTO t VALUES (3,20,300); -- conflicts on b
INSERT OR IGNORE INTO t VALUES (1,20,300); -- conflicts on both
SELECT a,b,c FROM t ORDER BY a;
} {1|10|100
2|20|200}
do_execsql_test_on_specific_db {:memory:} ignore-some-conflicts-multirow {
CREATE TABLE t(a INTEGER UNIQUE);
INSERT INTO t VALUES (2),(4);
INSERT OR IGNORE INTO t VALUES (1),(2),(3),(4),(5);
SELECT a FROM t ORDER BY a;
} {1
2
3
4
5}
do_execsql_test_on_specific_db {:memory:} ignore-from-select {
CREATE TABLE src(x);
INSERT INTO src VALUES (1),(2),(2),(3);
CREATE TABLE dst(a INTEGER UNIQUE);
INSERT INTO dst VALUES (2);
INSERT OR IGNORE INTO dst SELECT x FROM src;
SELECT a FROM dst ORDER BY a;
} {1
2
3}
do_execsql_test_on_specific_db {:memory:} ignore-null-in-unique {
CREATE TABLE t(a INTEGER UNIQUE);
INSERT INTO t VALUES (1),(NULL),(NULL);
INSERT OR IGNORE INTO t VALUES (1),(NULL);
SELECT COUNT(*) FROM t WHERE a IS NULL;
} {3}
do_execsql_test_on_specific_db {:memory:} ignore-preserves-rowid {
CREATE TABLE t(data TEXT UNIQUE);
INSERT INTO t VALUES ('x'),('y'),('z');
SELECT rowid, data FROM t WHERE data='y';
INSERT OR IGNORE INTO t VALUES ('y');
SELECT rowid, data FROM t WHERE data='y';
} {2|y
2|y}
do_execsql_test_on_specific_db {:memory:} ignore-intra-statement-dups {
CREATE TABLE t(a INTEGER PRIMARY KEY, b TEXT);
INSERT OR IGNORE INTO t VALUES (5,'first'),(6,'x'),(5,'second'),(5,'third');
SELECT a,b FROM t ORDER BY a;
} {5|first
6|x}
# Tests for incorrect provided column count
do_execsql_test_in_memory_any_error wrong-column-count-values-too-few {
CREATE TABLE t(a, b, c);
INSERT INTO t VALUES (1, 2);
}
do_execsql_test_in_memory_any_error wrong-column-count-values-too-many {
CREATE TABLE t(a, b);
INSERT INTO t VALUES (1, 2, 3);
}
do_execsql_test_in_memory_any_error wrong-column-count-values-specified-columns-too-few {
CREATE TABLE t(a, b, c);
INSERT INTO t(a, b, c) VALUES (1, 2);
}
do_execsql_test_in_memory_any_error wrong-column-count-values-specified-columns-too-many {
CREATE TABLE t(a, b);
INSERT INTO t(a, b) VALUES (1, 2, 3);
}
do_execsql_test_in_memory_any_error wrong-column-count-select-named-too-few {
CREATE TABLE src(x, y);
INSERT INTO src VALUES (1, 2);
CREATE TABLE dst(a, b, c);
INSERT INTO dst SELECT x, y FROM src;
}
do_execsql_test_in_memory_any_error wrong-column-count-select-named-too-many {
CREATE TABLE src(x, y, z);
INSERT INTO src VALUES (1, 2, 3);
CREATE TABLE dst(a, b);
INSERT INTO dst SELECT x, y, z FROM src;
}
do_execsql_test_in_memory_any_error wrong-column-count-select-star-too-few {
CREATE TABLE src(x, y);
INSERT INTO src VALUES (1, 2);
CREATE TABLE dst(a, b, c);
INSERT INTO dst SELECT * FROM src;
}
do_execsql_test_in_memory_any_error wrong-column-count-select-star-too-many {
CREATE TABLE src(x, y, z);
INSERT INTO src VALUES (1, 2, 3);
CREATE TABLE dst(a, b);
INSERT INTO dst SELECT * FROM src;
}
do_execsql_test_in_memory_any_error wrong-column-count-specified-columns-select-mismatch {
CREATE TABLE src(x, y, z);
INSERT INTO src VALUES (1, 2, 3);
CREATE TABLE dst(a, b, c, d);
INSERT INTO dst(a, b) SELECT x, y, z FROM src;
}
do_execsql_test_in_memory_any_error wrong-column-count-partial-columns-values-too-few {
CREATE TABLE t(a, b, c);
INSERT INTO t(b, c) VALUES (2);
}
do_execsql_test_in_memory_any_error wrong-column-count-partial-columns-values-too-many {
CREATE TABLE t(a, b, c);
INSERT INTO t(a) VALUES (1, 2);
}
# https://github.com/tursodatabase/turso/issues/3951
do_execsql_test_on_specific_db {:memory:} insert-999999999 {
create table t(a);
insert into t(a, a) values (2, 3);
select * from t;
} {2}
do_execsql_test_on_specific_db {:memory:} insert-on-conflict-do-nothing-single-row {
CREATE TABLE t(a unique, b text);
INSERT INTO t VALUES (2, 'foo'),(3, 'bar');
INSERT OR IGNORE INTO t values (2, 'baz');
SELECT * FROM t;
} {2|foo
3|bar}
do_execsql_test_on_specific_db {:memory:} insert-on-conflict-replace-single-row {
CREATE TABLE t(a unique, b text);
INSERT INTO t VALUES (2, 'foo'),(3, 'bar');
INSERT OR REPLACE INTO t values (2, 'baz');
SELECT * FROM t ORDER BY a;
} {2|baz
3|bar}
do_execsql_test_on_specific_db {:memory:} insert-on-conflict-do-nothing-multiple-rows {
CREATE TABLE t(a unique);
INSERT INTO t VALUES (2),(3);
INSERT OR IGNORE INTO t values (1),(2),(3);
SELECT * FROM t order by a;
} {1
2
3}
do_execsql_test_on_specific_db {:memory:} onconflict-ignore-existing {
CREATE TABLE t(a INTEGER UNIQUE, b TEXT);
INSERT INTO t VALUES (1,'x'),(2,'y');
INSERT OR IGNORE INTO t VALUES (2,'yy'),(3,'z'),(2,'zzz');
SELECT a, b FROM t ORDER BY a;
} {1|x
2|y
3|z}
do_execsql_test_on_specific_db {:memory:} onconflict-ignore-selfdup-values {
CREATE TABLE t(a INTEGER UNIQUE, b TEXT);
INSERT OR IGNORE INTO t VALUES (1,'one'),(1,'two'),(1,'three');
SELECT a, b FROM t ORDER BY a;
} {1|one}
do_execsql_test_on_specific_db {:memory:} onconflict-ignore-selfdup-values-targeted {
CREATE TABLE t(a INTEGER PRIMARY KEY, b INTEGER UNIQUE);
INSERT INTO t(a,b) VALUES (100,1);
INSERT INTO t(a,b) VALUES (200,1) ON CONFLICT(b) DO NOTHING;
SELECT a,b FROM t ORDER BY a;
} {100|1}
do_execsql_test_on_specific_db {:memory:} onconflict-ignore-selfdup-multirow-targeted {
CREATE TABLE t(a INTEGER PRIMARY KEY, b INTEGER UNIQUE);
INSERT INTO t(a,b) VALUES (1,10),(2,20);
INSERT INTO t(a,b) VALUES (3,30),(4,30) ON CONFLICT(b) DO NOTHING;
SELECT a,b FROM t ORDER BY a;
} {1|10
2|20
3|30}
do_execsql_test_on_specific_db {:memory:} onconflict-replace-existing {
CREATE TABLE t(a INTEGER UNIQUE, b TEXT);
INSERT INTO t VALUES (1,'x'),(2,'y');
INSERT OR REPLACE INTO t VALUES (2,'yy'),(3,'z');
SELECT a,b FROM t ORDER BY a;
} {1|x
2|yy
3|z}
do_execsql_test_on_specific_db {:memory:} onconflict-replace-selfdup-values-lastwins {
CREATE TABLE t(a INTEGER UNIQUE, b TEXT);
INSERT OR REPLACE INTO t VALUES (5,'one'),(5,'two'),(5,'three');
SELECT a,b FROM t;
} {5|three}
do_execsql_test_on_specific_db {:memory:} onconflict-replace-existing-then-selfdup-lastwins {
CREATE TABLE t(a INTEGER UNIQUE, b TEXT);
INSERT INTO t VALUES (5,'orig');
INSERT OR REPLACE INTO t VALUES (5,'first'),(5,'second');
SELECT a,b FROM t;
} {5|second}
do_execsql_test_on_specific_db {:memory:} onconflict-replace-pk-rowid-alias {
CREATE TABLE t(a INTEGER PRIMARY KEY, b TEXT);
INSERT OR REPLACE INTO t(a,b) VALUES (1,'foo'),(1,'bar');
SELECT a,b FROM t;
} {1|bar}
do_execsql_test_on_specific_db {:memory:} onconflict-replace-from-select-selfdup {
CREATE TABLE src(x);
INSERT INTO src VALUES (1),(1),(2);
CREATE TABLE t(a INTEGER UNIQUE);
INSERT OR REPLACE INTO t SELECT x FROM src;
SELECT a FROM t ORDER BY a;
} {1
2}
do_execsql_test_on_specific_db {:memory:} onconflict-replace-notnull-default-existing-null {
CREATE TABLE t(
a INTEGER PRIMARY KEY,
b TEXT NOT NULL DEFAULT 'd'
);
INSERT INTO t VALUES (1,'x');
INSERT OR REPLACE INTO t(a,b) VALUES (1,NULL);
SELECT a,b FROM t;
} {1|d}
do_execsql_test_on_specific_db {:memory:} onconflict-replace-notnull-default-existing-omit {
CREATE TABLE t(
a INTEGER PRIMARY KEY,
b TEXT NOT NULL DEFAULT 'd'
);
INSERT INTO t VALUES (1,'x');
INSERT OR REPLACE INTO t(a) VALUES (1);
SELECT a,b FROM t;
} {1|d}
# need to assert row survival after failed REPLACE due to NOT NULL constraint
# so we create table t here
do_execsql_test 1.0 {
CREATE TABLE t(a INTEGER PRIMARY KEY, b TEXT NOT NULL);
INSERT INTO t VALUES (1,'x');
} {}
do_execsql_test_any_error 1.1 {
INSERT OR REPLACE INTO t(a,b) VALUES (1,NULL);
}
do_execsql_test 1.2 {
SELECT a,b FROM t;
} {1|x}
# drop the table created above as not to alter the testing db
do_execsql_test drop-t {
DROP TABLE t;
} {}
do_execsql_test_on_specific_db {:memory:} onconflict-replace-composite-unique-existing {
CREATE TABLE t(
a INTEGER,
b INTEGER,
c TEXT,
UNIQUE(a,b)
);
INSERT INTO t VALUES (1,1,'x'),(1,2,'y');
INSERT OR REPLACE INTO t VALUES (1,2,'yy');
SELECT a,b,c FROM t ORDER BY a,b;
} {1|1|x
1|2|yy}
do_execsql_test_on_specific_db {:memory:} onconflict-replace-composite-unique-selfdup-lastwins {
CREATE TABLE t(
a INTEGER,
b INTEGER,
c TEXT,
UNIQUE(a,b)
);
INSERT OR REPLACE INTO t VALUES
(1,1,'one'),
(1,1,'two'),
(1,1,'three');
SELECT a,b,c FROM t;
} {1|1|three}
do_execsql_test_on_specific_db {:memory:} onconflict-replace-unique-index-existing {
CREATE TABLE t(a INTEGER, b TEXT);
CREATE UNIQUE INDEX t_a_u ON t(a);
INSERT INTO t VALUES (1,'x'),(2,'y');
INSERT OR REPLACE INTO t VALUES (2,'yy'),(3,'z');
SELECT a,b FROM t ORDER BY a;
} {1|x
2|yy
3|z}
do_execsql_test_on_specific_db {:memory:} onconflict-replace-unique-index-selfdup-lastwins {
CREATE TABLE t(a INTEGER, b TEXT);
CREATE UNIQUE INDEX t_a_u ON t(a);
INSERT OR REPLACE INTO t VALUES (5,'one'),(5,'two'),(5,'three');
SELECT a,b FROM t;
} {5|three}
Reference in New Issue View Git Blame Copy Permalink