turso/testing/collate.test

#!/usr/bin/env tclsh

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# SIMPLE SMOKE TESTS THAT DO NOT DEPEND ON SPECIFIC DATABASE ROWS

do_execsql_test collate_nocase {
    SELECT 'hat' == 'hAt' COLLATE NOCASE;
} {1}

do_execsql_test collate_binary_1 {
    SELECT 'hat' == 'hAt' COLLATE BINARY;
} {0}

do_execsql_test collate_binary_2 {
    SELECT 'hat' == 'hat' COLLATE BINARY;
} {1}

do_execsql_test collate_rtrim_1 {
    SELECT 'hat' == 'hAt ' COLLATE RTRIM;
} {0}

do_execsql_test collate_rtrim_2 {
    SELECT 'hat' == 'hat ' COLLATE RTRIM;
} {1}

do_execsql_test collate_rtrim_3 {
    SELECT 'hat' == ' hAt ' COLLATE RTRIM;
} {0}

do_execsql_test collate_rtrim_4 {
    SELECT 'hat' == ' hat ' COLLATE RTRIM;
} {0}

do_execsql_test collate_left_precedence {
    SELECT 'hat' COLLATE BINARY == 'hAt' COLLATE NOCASE;
} {0}

do_execsql_test collate_left_precedence_2 {
    SELECT 'hat' COLLATE NOCASE == 'hAt' COLLATE BINARY;
} {1}

do_execsql_test_in_memory_any_error collate_unique_constraint {
    CREATE TABLE t (a TEXT COLLATE NOCASE PRIMARY KEY);
    INSERT INTO t VALUES ('lol'), ('LOL'), ('lOl');
}

do_execsql_test_in_memory_any_error collate_unique_constraint {
    CREATE TABLE t (a TEXT COLLATE NOCASE PRIMARY KEY);
    INSERT INTO t VALUES ('lol'), ('LOL'), ('lOl');
}

do_execsql_test_on_specific_db {:memory:} collate_aggregation_default_binary {
    create table fruits(name collate binary);
    insert into fruits(name) values ('Apple') ,('banana') ,('CHERRY');
    select max(name) from fruits;
} {banana}

do_execsql_test_on_specific_db {:memory:} collate_aggregation_default_nocase {
    create table fruits(name collate nocase);
    insert into fruits(name) values ('Apple') ,('banana') ,('CHERRY');
    select max(name) from fruits;
} {CHERRY}

do_execsql_test_on_specific_db {:memory:} collate_aggregation_explicit_binary {
    create table fruits(name collate nocase);
    insert into fruits(name) values ('Apple') ,('banana') ,('CHERRY');
    select max(name collate binary) from fruits;
} {banana}

do_execsql_test_on_specific_db {:memory:} collate_aggregation_explicit_nocase {
    create table fruits(name collate binary);
    insert into fruits(name) values ('Apple') ,('banana') ,('CHERRY');
    select max(name collate nocase) from fruits;
} {CHERRY}

do_execsql_test_on_specific_db {:memory:} collate_grouped_aggregation_default_binary {
    create table fruits(name collate binary, category text);
    insert into fruits(name, category) values ('Apple', 'A'), ('banana', 'A'), ('CHERRY', 'B'), ('blueberry', 'B');
    select max(name) from fruits group by category;
} {banana
blueberry}

do_execsql_test_on_specific_db {:memory:} collate_grouped_aggregation_default_nocase {
    create table fruits(name collate nocase, category text);
    insert into fruits(name, category) values ('Apple', 'A'), ('banana', 'A'), ('CHERRY', 'B'), ('blueberry', 'B');
    select max(name) from fruits group by category;
} {banana
CHERRY}

do_execsql_test_on_specific_db {:memory:} collate_grouped_aggregation_explicit_binary {
    create table fruits(name collate nocase, category text);
    insert into fruits(name, category) values ('Apple', 'A'), ('banana', 'A'), ('CHERRY', 'B'), ('blueberry', 'B');
    select max(name collate binary) from fruits group by category;
} {banana
blueberry}

do_execsql_test_on_specific_db {:memory:} collate_groupped_aggregation_explicit_nocase {
    create table fruits(name collate binary, category text);
    insert into fruits(name, category) values ('Apple', 'A'), ('banana', 'A'), ('CHERRY', 'B'), ('blueberry', 'B');
    select max(name collate nocase) from fruits group by category;
} {banana
CHERRY}

do_execsql_test_on_specific_db {:memory:} collate_join_nocase {
    CREATE TABLE a(s TEXT);
    CREATE TABLE b(s TEXT);
    INSERT INTO a VALUES ('A');
    INSERT INTO b VALUES ('a');

    SELECT a.s, b.s FROM a JOIN b ON a.s COLLATE NOCASE = b.s;
} {A|a}

do_execsql_test_on_specific_db {:memory:} collate_columns_where_implicit {
    CREATE TABLE t(
        a TEXT COLLATE NOCASE,
        b TEXT COLLATE BINARY,
        c TEXT COLLATE RTRIM
    );
    INSERT INTO t(a,b,c) VALUES
        ('hat','hat','hat '),
        ('hAt','hAt','hat'),
        ('HAT','HAT','hat    '),
        ('other','other','other');

    SELECT count(*) FROM t WHERE a = 'hAt';
    SELECT count(*) FROM t WHERE b = 'hAt';
    SELECT count(*) FROM t WHERE c = 'hat';
} {3
1
3}

do_execsql_test_on_specific_db {:memory:} collate_columns_where_explicit_override {
    CREATE TABLE t(
        a TEXT COLLATE NOCASE,
        b TEXT COLLATE BINARY,
        c TEXT COLLATE RTRIM
    );
    INSERT INTO t(a,b,c) VALUES
        ('hat','hat','hat '),
        ('hAt','hAt','hat'),
        ('HAT','HAT','hat    '),
        ('other','other','other');

    SELECT count(*) FROM t WHERE a COLLATE BINARY = 'hAt';   -- override to binary
    SELECT count(*) FROM t WHERE b COLLATE NOCASE = 'hAt';   -- override to nocase
    SELECT count(*) FROM t WHERE c COLLATE BINARY = 'hat';   -- override to binary
} {1
3
1}

do_execsql_test_on_specific_db {:memory:} collate_order_by_binary {
    CREATE TABLE words(w TEXT COLLATE BINARY);
    INSERT INTO words(w) VALUES ('Apple'), ('banana'), ('CHERRY');
    SELECT w FROM words ORDER BY w;
} {Apple
CHERRY
banana}

do_execsql_test_on_specific_db {:memory:} collate_order_by_nocase {
    CREATE TABLE words(w TEXT COLLATE NOCASE);
    INSERT INTO words(w) VALUES ('Apple'), ('banana'), ('CHERRY');
    SELECT w FROM words ORDER BY w;
} {Apple
banana
CHERRY}

do_execsql_test_on_specific_db {:memory:} collate_order_by_explicit_override {
    CREATE TABLE words(w TEXT COLLATE NOCASE);
    INSERT INTO words(w) VALUES ('Apple'), ('banana'), ('CHERRY');
    SELECT w FROM words ORDER BY w COLLATE BINARY;
} {Apple
CHERRY
banana}

do_execsql_test_on_specific_db {:memory:} collate_distinct_rtrim {
    CREATE TABLE t(c TEXT COLLATE RTRIM);
    INSERT INTO t(c) VALUES ('x'), ('x '), ('x  '), ('y'), ('y  ');
    SELECT count(DISTINCT c) FROM t;
} {2}

do_execsql_test_in_memory_any_error collate_unique_nocase_conflict {
    CREATE TABLE u(a TEXT COLLATE NOCASE UNIQUE);
    INSERT INTO u VALUES ('aa');
    INSERT INTO u VALUES ('AA');
}

do_execsql_test_in_memory_any_error collate_unique_rtrim_conflict {
    CREATE TABLE r(a TEXT COLLATE RTRIM UNIQUE);
    INSERT INTO r VALUES ('bb');
    INSERT INTO r VALUES ('bb ');
}

do_execsql_test_on_specific_db {:memory:} collate_unique_binary_allows_case {
    CREATE TABLE ub(a TEXT COLLATE BINARY UNIQUE);
    INSERT INTO ub VALUES ('aa');
    INSERT INTO ub VALUES ('AA');
    SELECT count(*) FROM ub;
} {2}

do_execsql_test_in_memory_any_error collate_pk_rtrim_conflict {
    CREATE TABLE p(a TEXT COLLATE RTRIM PRIMARY KEY);
    INSERT INTO p VALUES ('key');
    INSERT INTO p VALUES ('key   ');
}

do_execsql_test_on_specific_db {:memory:} collate_join_implicit_nocase_columns {
    CREATE TABLE a(s TEXT COLLATE NOCASE);
    CREATE TABLE b(s TEXT COLLATE NOCASE);
    INSERT INTO a VALUES ('A');
    INSERT INTO b VALUES ('a');
    SELECT a.s, b.s FROM a JOIN b ON a.s = b.s;
} {A|a}

do_execsql_test_on_specific_db {:memory:} collate_join_mixed_implicit_binary_left {
    CREATE TABLE a(s TEXT COLLATE BINARY);
    CREATE TABLE b(s TEXT COLLATE NOCASE);
    INSERT INTO a VALUES ('A');
    INSERT INTO b VALUES ('a');
    SELECT a.s, b.s FROM a JOIN b ON a.s = b.s;
} {}

do_execsql_test_on_specific_db {:memory:} collate_join_mixed_explicit_nocase_left {
    CREATE TABLE a(s TEXT COLLATE BINARY);
    CREATE TABLE b(s TEXT COLLATE NOCASE);
    INSERT INTO a VALUES ('A');
    INSERT INTO b VALUES ('a');
    SELECT a.s, b.s FROM a JOIN b ON a.s COLLATE NOCASE = b.s;
} {A|a}

do_execsql_test_on_specific_db {:memory:} collate_where_with_and_without_explicit {
    CREATE TABLE t(a TEXT COLLATE NOCASE, b TEXT COLLATE BINARY, c TEXT COLLATE RTRIM);
    INSERT INTO t VALUES ('Foo','Foo','Foo '), ('fOo','fOo','Foo'), ('other','other','other');
    -- implicit uses column collation
    SELECT count(*) FROM t WHERE a = 'foo';
    -- explicit override on binary column
    SELECT count(*) FROM t WHERE b COLLATE NOCASE = 'foo';
    -- implicit RTRIM on c
    SELECT count(*) FROM t WHERE c = 'Foo';
    -- explicit override to BINARY on RTRIM column
    SELECT count(*) FROM t WHERE c COLLATE BINARY = 'Foo';
} {2
2
2
1}

do_execsql_test_on_specific_db {:memory:} collate_group_by_implicit_nocase {
    CREATE TABLE t(s TEXT COLLATE NOCASE);
    INSERT INTO t VALUES ('A'), ('a'), ('B'), ('b');
    SELECT s, count(*) FROM t GROUP BY s ORDER BY s;
} {A|2
B|2}

do_execsql_test_on_specific_db {:memory:} collate_group_by_implicit_rtrim {
    CREATE TABLE t(s TEXT COLLATE RTRIM);
    INSERT INTO t VALUES ('A'), ('A '), ('B'), ('B  ');
    SELECT s, count(*) FROM t GROUP BY s ORDER BY s;
} {A|2
B|2}

do_execsql_test_on_specific_db {:memory:} collate_group_by_explicit_override {
    CREATE TABLE t(s TEXT COLLATE BINARY);
    INSERT INTO t VALUES ('A'), ('a'), ('B'), ('b');
    SELECT s, count(*) FROM t GROUP BY s COLLATE NOCASE ORDER BY s;
} {A|2
B|2}

do_execsql_test_on_specific_db {:memory:} collate_group_by_mixed_columns {
    CREATE TABLE t(a TEXT COLLATE NOCASE, b TEXT COLLATE RTRIM);
    INSERT INTO t VALUES ('A', 'x'), ('a', 'x '), ('B', 'y'), ('b', 'y  ');
    SELECT a, b, count(*) FROM t GROUP BY a, b ORDER BY a, b;
} {A|x|2
B|y|2}


do_execsql_test_on_specific_db {:memory:} collate_subquery_preserves_column_collation {
    CREATE TABLE t(a TEXT COLLATE NOCASE, b TEXT COLLATE BINARY);
    INSERT INTO t VALUES ('A', 'A'), ('a', 'a'), ('B', 'B'), ('b', 'b');
    
    -- Subquery preserves NOCASE collation
    SELECT count(*) FROM (SELECT a FROM t) WHERE a = 'a';
    -- Subquery preserves BINARY collation 
    SELECT count(*) FROM (SELECT b FROM t) WHERE b = 'a';
} {2
1}

do_execsql_test_on_specific_db {:memory:} collate_subquery_preserves_explicit_collation {
    CREATE TABLE t(a TEXT COLLATE BINARY, b TEXT COLLATE BINARY);
    INSERT INTO t VALUES ('A', 'A'), ('a', 'a'), ('B', 'B'), ('b', 'b');
    
    -- Explicit NOCASE in subquery is preserved
    SELECT count(*) FROM (SELECT a COLLATE NOCASE as a FROM t) WHERE a = 'a';
    -- Explicit BINARY in subquery is preserved
    SELECT count(*) FROM (SELECT b COLLATE BINARY as b FROM t) WHERE b = 'a';
} {2
1}

do_execsql_test_on_specific_db {:memory:} collate_subquery_preserves_collation_in_order_by {
    CREATE TABLE t(a TEXT COLLATE NOCASE, b TEXT COLLATE BINARY);
    INSERT INTO t VALUES ('A', 'A'), ('b', 'b'), ('C', 'C');
    
    -- ORDER BY in subquery preserves NOCASE collation
    SELECT a FROM (SELECT a FROM t ORDER BY a);
    -- ORDER BY in subquery preserves BINARY collation
    SELECT b FROM (SELECT b FROM t ORDER BY b);
} {A
b
C
A
C
b}