mirror of
https://github.com/tursodatabase/limbo.git
synced 2025-12-23 08:21:09 +00:00
252 lines
No EOL
9 KiB
Tcl
Executable file
252 lines
No EOL
9 KiB
Tcl
Executable file
#!/usr/bin/env tclsh
|
|
|
|
set testdir [file dirname $argv0]
|
|
source $testdir/tester.tcl
|
|
|
|
do_execsql_test_on_specific_db {:memory:} affinity {
|
|
CREATE TABLE t1 (c INTEGER);
|
|
INSERT INTO t1 VALUES ('1');
|
|
INSERT INTO t1 VALUES ('1a');
|
|
SELECT c, typeof(c) FROM t1;
|
|
} {
|
|
{1|integer}
|
|
{1a|text}
|
|
}
|
|
|
|
do_execsql_test_on_specific_db {:memory:} affinity-rowid {
|
|
create table t(a integer);
|
|
insert into t(rowid, a) values (1, 1);
|
|
select * from t where rowid = '1';
|
|
select * from t where a = '1';
|
|
} {1
|
|
1}
|
|
|
|
# ============================================
|
|
# TEXT affinity: numeric values should be converted to text on INSERT
|
|
# ============================================
|
|
do_execsql_test_on_specific_db {:memory:} affinity-insert-text-from-real {
|
|
CREATE TABLE t1 (c1 TEXT);
|
|
INSERT INTO t1 VALUES (1025.1655084065987);
|
|
SELECT c1, typeof(c1) FROM t1;
|
|
} {1025.1655084066|text}
|
|
|
|
do_execsql_test_on_specific_db {:memory:} affinity-insert-text-from-integer {
|
|
CREATE TABLE t1 (c1 TEXT);
|
|
INSERT INTO t1 VALUES (42);
|
|
SELECT c1, typeof(c1) FROM t1;
|
|
} {42|text}
|
|
|
|
# ============================================
|
|
# TEXT affinity with index: both table and index should use converted value
|
|
# ============================================
|
|
do_execsql_test_on_specific_db {:memory:} affinity-insert-text-with-index {
|
|
CREATE TABLE t1 (c1 TEXT);
|
|
CREATE INDEX i1 ON t1 (c1);
|
|
INSERT INTO t1 VALUES (123.456);
|
|
SELECT c1, typeof(c1) FROM t1;
|
|
} {123.456|text}
|
|
|
|
# ============================================
|
|
# UPDATE with TEXT affinity: numeric values should be converted
|
|
# ============================================
|
|
do_execsql_test_on_specific_db {:memory:} affinity-update-text-from-real {
|
|
CREATE TABLE t1 (c1 TEXT);
|
|
INSERT INTO t1 VALUES ('initial');
|
|
UPDATE t1 SET c1 = 999.888;
|
|
SELECT c1, typeof(c1) FROM t1;
|
|
} {999.888|text}
|
|
|
|
do_execsql_test_on_specific_db {:memory:} affinity-update-text-with-index {
|
|
CREATE TABLE t1 (c1 TEXT);
|
|
CREATE INDEX i1 ON t1 (c1);
|
|
INSERT INTO t1 VALUES ('initial');
|
|
UPDATE t1 SET c1 = 123.456;
|
|
SELECT c1, typeof(c1) FROM t1;
|
|
} {123.456|text}
|
|
|
|
# ============================================
|
|
# UPSERT with TEXT affinity
|
|
# ============================================
|
|
do_execsql_test_on_specific_db {:memory:} affinity-upsert-text {
|
|
CREATE TABLE t1 (id INTEGER PRIMARY KEY, c1 TEXT);
|
|
INSERT INTO t1 VALUES (1, 'initial');
|
|
INSERT INTO t1 VALUES (1, 555.666) ON CONFLICT(id) DO UPDATE SET c1 = excluded.c1;
|
|
SELECT id, c1, typeof(c1) FROM t1;
|
|
} {1|555.666|text}
|
|
|
|
# ============================================
|
|
# IN clause with TEXT column: should apply TEXT affinity to comparison
|
|
# ============================================
|
|
do_execsql_test_on_specific_db {:memory:} affinity-in-text-column {
|
|
CREATE TABLE t1 (name TEXT);
|
|
INSERT INTO t1 VALUES ('1'), ('2'), ('3'), ('abc');
|
|
SELECT name FROM t1 WHERE name IN (1, 2, 3) ORDER BY name;
|
|
} {1
|
|
2
|
|
3}
|
|
|
|
do_execsql_test_on_specific_db {:memory:} affinity-in-text-column-with-index {
|
|
CREATE TABLE t1 (name TEXT);
|
|
INSERT INTO t1 VALUES ('1'), ('2'), ('3'), ('abc');
|
|
CREATE INDEX i1 ON t1 (name);
|
|
SELECT name FROM t1 WHERE name IN (1, 2, 3) ORDER BY name;
|
|
} {1
|
|
2
|
|
3}
|
|
|
|
# ============================================
|
|
# IN clause with INTEGER column: should apply INTEGER affinity
|
|
# ============================================
|
|
do_execsql_test_on_specific_db {:memory:} affinity-in-integer-column {
|
|
CREATE TABLE t1 (id INTEGER);
|
|
INSERT INTO t1 VALUES (1), (2), (3), (100);
|
|
SELECT id FROM t1 WHERE id IN ('1', '2', '3') ORDER BY id;
|
|
} {1
|
|
2
|
|
3}
|
|
|
|
# ============================================
|
|
# Multiple columns with different affinities
|
|
# ============================================
|
|
do_execsql_test_on_specific_db {:memory:} affinity-mixed-columns-insert {
|
|
CREATE TABLE t1 (a TEXT, b INTEGER, c REAL);
|
|
INSERT INTO t1 VALUES (100, '200', '300.5');
|
|
SELECT a, typeof(a), b, typeof(b), c, typeof(c) FROM t1;
|
|
} {100|text|200|integer|300.5|real}
|
|
|
|
do_execsql_test_on_specific_db {:memory:} affinity-mixed-columns-update {
|
|
CREATE TABLE t1 (a TEXT, b INTEGER, c REAL);
|
|
INSERT INTO t1 VALUES ('x', 0, 0.0);
|
|
UPDATE t1 SET a = 999, b = '888', c = '777.5';
|
|
SELECT a, typeof(a), b, typeof(b), c, typeof(c) FROM t1;
|
|
} {999|text|888|integer|777.5|real}
|
|
|
|
# ============================================
|
|
# Original test case from issue https://github.com/tursodatabase/turso/issues/4154
|
|
# ============================================
|
|
do_execsql_test_on_specific_db {:memory:} affinity-original-issue {
|
|
CREATE TABLE t1 (c1 TEXT COLLATE RTRIM);
|
|
INSERT INTO t1 VALUES (' ');
|
|
CREATE INDEX i1 ON t1 (c1 COLLATE RTRIM DESC);
|
|
INSERT INTO t1 VALUES (1025.1655084065987);
|
|
SELECT typeof(c1), c1 FROM t1 ORDER BY c1 COLLATE BINARY DESC, rowid ASC;
|
|
} {"text|1025.1655084066
|
|
text| "}
|
|
|
|
# ============================================
|
|
# TEXT affinity: numeric values should be converted to text on INSERT
|
|
# ============================================
|
|
do_execsql_test_on_specific_db {:memory:} affinity-insert-text-from-real {
|
|
CREATE TABLE t1 (c1 TEXT);
|
|
INSERT INTO t1 VALUES (1025.1655084065987);
|
|
SELECT c1, typeof(c1) FROM t1;
|
|
} {1025.1655084066|text}
|
|
|
|
do_execsql_test_on_specific_db {:memory:} affinity-insert-text-from-integer {
|
|
CREATE TABLE t1 (c1 TEXT);
|
|
INSERT INTO t1 VALUES (42);
|
|
SELECT c1, typeof(c1) FROM t1;
|
|
} {42|text}
|
|
|
|
# ============================================
|
|
# TEXT affinity with index: both table and index should use converted value
|
|
# ============================================
|
|
do_execsql_test_on_specific_db {:memory:} affinity-insert-text-with-index {
|
|
CREATE TABLE t1 (c1 TEXT);
|
|
CREATE INDEX i1 ON t1 (c1);
|
|
INSERT INTO t1 VALUES (123.456);
|
|
SELECT c1, typeof(c1) FROM t1;
|
|
} {123.456|text}
|
|
|
|
# ============================================
|
|
# UPDATE with TEXT affinity: numeric values should be converted
|
|
# ============================================
|
|
do_execsql_test_on_specific_db {:memory:} affinity-update-text-from-real {
|
|
CREATE TABLE t1 (c1 TEXT);
|
|
INSERT INTO t1 VALUES ('initial');
|
|
UPDATE t1 SET c1 = 999.888;
|
|
SELECT c1, typeof(c1) FROM t1;
|
|
} {999.888|text}
|
|
|
|
do_execsql_test_on_specific_db {:memory:} affinity-update-text-with-index {
|
|
CREATE TABLE t1 (c1 TEXT);
|
|
CREATE INDEX i1 ON t1 (c1);
|
|
INSERT INTO t1 VALUES ('initial');
|
|
UPDATE t1 SET c1 = 123.456;
|
|
SELECT c1, typeof(c1) FROM t1;
|
|
} {123.456|text}
|
|
|
|
# ============================================
|
|
# UPSERT with TEXT affinity
|
|
# ============================================
|
|
do_execsql_test_on_specific_db {:memory:} affinity-upsert-text {
|
|
CREATE TABLE t1 (id INTEGER PRIMARY KEY, c1 TEXT);
|
|
INSERT INTO t1 VALUES (1, 'initial');
|
|
INSERT INTO t1 VALUES (1, 555.666) ON CONFLICT(id) DO UPDATE SET c1 = excluded.c1;
|
|
SELECT id, c1, typeof(c1) FROM t1;
|
|
} {1|555.666|text}
|
|
|
|
# ============================================
|
|
# IN clause with TEXT column: should apply TEXT affinity to comparison
|
|
# ============================================
|
|
do_execsql_test_on_specific_db {:memory:} affinity-in-text-column {
|
|
CREATE TABLE t1 (name TEXT);
|
|
INSERT INTO t1 VALUES ('1'), ('2'), ('3'), ('abc');
|
|
SELECT name FROM t1 WHERE name IN (1, 2, 3) ORDER BY name;
|
|
} {1
|
|
2
|
|
3}
|
|
|
|
do_execsql_test_on_specific_db {:memory:} affinity-in-text-column-with-index {
|
|
CREATE TABLE t1 (name TEXT);
|
|
INSERT INTO t1 VALUES ('1'), ('2'), ('3'), ('abc');
|
|
CREATE INDEX i1 ON t1 (name);
|
|
SELECT name FROM t1 WHERE name IN (1, 2, 3) ORDER BY name;
|
|
} {1
|
|
2
|
|
3}
|
|
|
|
# ============================================
|
|
# IN clause with INTEGER column: should apply INTEGER affinity
|
|
# ============================================
|
|
do_execsql_test_on_specific_db {:memory:} affinity-in-integer-column {
|
|
CREATE TABLE t1 (id INTEGER);
|
|
INSERT INTO t1 VALUES (1), (2), (3), (100);
|
|
SELECT id FROM t1 WHERE id IN ('1', '2', '3') ORDER BY id;
|
|
} {1
|
|
2
|
|
3}
|
|
|
|
# ============================================
|
|
# Multiple columns with different affinities
|
|
# ============================================
|
|
do_execsql_test_on_specific_db {:memory:} affinity-mixed-columns-insert {
|
|
CREATE TABLE t1 (a TEXT, b INTEGER, c REAL);
|
|
INSERT INTO t1 VALUES (100, '200', '300.5');
|
|
SELECT a, typeof(a), b, typeof(b), c, typeof(c) FROM t1;
|
|
} {100|text|200|integer|300.5|real}
|
|
|
|
do_execsql_test_on_specific_db {:memory:} affinity-mixed-columns-update {
|
|
CREATE TABLE t1 (a TEXT, b INTEGER, c REAL);
|
|
INSERT INTO t1 VALUES ('x', 0, 0.0);
|
|
UPDATE t1 SET a = 999, b = '888', c = '777.5';
|
|
SELECT a, typeof(a), b, typeof(b), c, typeof(c) FROM t1;
|
|
} {999|text|888|integer|777.5|real}
|
|
|
|
# ============================================
|
|
# Original test case from issue https://github.com/tursodatabase/turso/issues/4154
|
|
# ============================================
|
|
do_execsql_test_on_specific_db {:memory:} affinity-original-issue {
|
|
CREATE TABLE t1 (c1 TEXT COLLATE RTRIM);
|
|
INSERT INTO t1 VALUES (' ');
|
|
CREATE INDEX i1 ON t1 (c1 COLLATE RTRIM DESC);
|
|
INSERT INTO t1 VALUES (1025.1655084065987);
|
|
SELECT typeof(c1), c1 FROM t1 ORDER BY c1 COLLATE BINARY DESC, rowid ASC;
|
|
} {"text|1025.1655084066
|
|
text| "}
|
|
|
|
do_execsql_test_on_specific_db {:memory:} affinity-ascii-whitespace-1.1 {
|
|
CREATE TABLE nb1(i INTEGER);
|
|
INSERT INTO nb1 VALUES ('12' || CHAR(160));
|
|
SELECT TYPEOF(i), LENGTH(i) FROM nb1;
|
|
} {text|3} |