limbo/testing/select.test

#!/usr/bin/env tclsh

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

do_execsql_test select-const-1 {
  SELECT 1
} {1}

do_execsql_test select-const-2 {
  SELECT 2
} {2}

do_execsql_test select-const-3 {
  SELECT 0xDEAF
} {57007}

do_execsql_test select-const-4 {
  SELECT -0xA
} {-10}

do_execsql_test select-true {
  SELECT true
} {1}

do_execsql_test select-false {
  SELECT false
} {0}

do_execsql_test select-text-escape-1 {
  SELECT '''a'
} {'a}

do_execsql_test select-blob-empty {
  SELECT x'';
} {}

do_execsql_test select-blob-ascii {
  SELECT x'6C696D626F';
} {limbo}

do_execsql_test select-blob-emoji {
  SELECT x'F09FA680';
} {🦀}

do_execsql_test select-limit-0 {
  SELECT id FROM users LIMIT 0;
} {}

# ORDER BY id here because sqlite uses age_idx here and we (yet) don't so force it to evaluate in ID order
do_execsql_test select-limit-true {
  SELECT id FROM users ORDER BY id LIMIT true;
} {1}

do_execsql_test select-limit-false {
  SELECT id FROM users ORDER BY id LIMIT false;
} {}

do_execsql_test realify {
    select price from products limit 1;
} {79.0}

do_execsql_test select-add {
    select u.age + 1 from users u where u.age = 91 limit 1;
} {92}

do_execsql_test select-subtract {
    select u.age - 1 from users u where u.age = 91 limit 1;
} {90}

do_execsql_test case-insensitive-columns {
    select u.aGe + 1 from USERS u where U.AGe = 91 limit 1;
} {92}

do_execsql_test table-star {
    select p.*, p.name from products p limit 1;
} {1|hat|79.0|hat}

do_execsql_test table-star-2 {
    select p.*, u.first_name from users u join products p on u.id = p.id limit 1;
} {1|hat|79.0|Jamie}

do_execsql_test select_with_quoting {
  select `users`.id from [users] where users.[id] = 5;
} {5}

do_execsql_test select_with_quoting_2 {
  select "users".`id` from users where `users`.[id] = 5;
} {5}

do_execsql_test select-rowid {
    select rowid, first_name from users u where rowid = 5;
} {5|Edward}

do_execsql_test select-rowid-2 {
    select u.rowid, first_name from users u where rowid = 5;
} {5|Edward}

do_execsql_test seekrowid {
    select * from users u where u.id = 5;
} {"5|Edward|Miller|christiankramer@example.com|725-281-1033|08522 English Plain|Lake Keith|ID|23283|15"}

do_execsql_test select_parenthesized {
  select (price + 100) from products limit 1;
} {179.0}

do_execsql_test select_case_base_else {
  select case when 0 then 'false' when 1 then 'true' else 'null' end;
} {true}

do_execsql_test select_case_noelse_null {
  select case when 0 then 0 end;
} {}

do_execsql_test select_base_case_else {
  select case 1 when 0 then 'zero' when 1 then 'one' else 'two' end;
} {one}

do_execsql_test select_base_case_null_result {
  select case NULL when 0 then 'first' else 'second' end;
  select case NULL when NULL then 'first' else 'second' end;
  select case 0 when 0 then 'first' else 'second' end;
} {second
second
first}

do_execsql_test select_base_case_noelse_null {
  select case 'null else' when 0 then 0 when 1 then 1 end;
} {}

do_execsql_test select-is-null {
    select null is null, (1 / 0) is null, null is (1 / 0), (1 / 0) is (1 / 0);
    select 4 is null, '4' is null, 0 is null, (1 / 2) is null;
} {1|1|1|1
0|0|0|0}

do_execsql_test select-is-not-null {
    select null is not null, (1 / 0) is not null, null is not (1 / 0), (1 / 0) is not (1 / 0);
    select 4 is not null, '4' is not null, 0 is not null, (1 / 2) is not null;
} {0|0|0|0
1|1|1|1}

do_execsql_test select_bin_shr {
  select 997623670 >> 0, 997623670 >> 1, 997623670 >> 10, 997623670 >> 30;
  select -997623670 >> 0, -997623670 >> 1, -997623670 >> 10, -997623670 >> 30;
  select 997623670 << 0, 997623670 << -1, 997623670 << -10, 997623670 << -30;
  select -997623670 << 0, -997623670 << -1, -997623670 << -10, -997623670 << -30;
} {997623670|498811835|974241|0
-997623670|-498811835|-974242|-1
997623670|498811835|974241|0
-997623670|-498811835|-974242|-1}

do_execsql_test select_bin_shl {
  select 997623670 << 0, 997623670 << 1, 997623670 << 10, 997623670 << 30;
  select -997623670 << 0, -997623670 << 1, -997623670 << 10, -997623670 << 30;
  select 997623670 >> 0, 997623670 >> -1, 997623670 >> -10, 997623670 >> -30;
  select -997623670 >> 0, -997623670 >> -1, -997623670 >> -10, -997623670 >> -30;
} {997623670|1995247340|1021566638080|1071190259091374080
-997623670|-1995247340|-1021566638080|-1071190259091374080
997623670|1995247340|1021566638080|1071190259091374080
-997623670|-1995247340|-1021566638080|-1071190259091374080}

# Test LIKE in SELECT position
do_execsql_test select-like-expression {
    select 'bar' like 'bar%'
} {1}

do_execsql_test select-not-like-expression {
    select 'bar' not like 'bar%'
} {0}

# regression test for float divisor being cast to zero int and panicking
do_execsql_test select-like-expression {
    select 2 % 0.5
} {}

do_execsql_test select_positive_infinite_float {
  SELECT 1.7976931348623157E+308 + 1e308; -- f64::MAX + 1e308
} {Inf}

do_execsql_test select_negative_infinite_float {
  SELECT -1.7976931348623157E+308 - 1e308 -- f64::MIN - 1e308
} {-Inf}

do_execsql_test select_shl_large_negative_float {
    SELECT 1 << -1e19;
    SELECT 1 << -9223372036854775808;  -- i64::MIN
    SELECT 1 << 9223372036854775807;   -- i64::MAX
} {0 0 0}

do_execsql_test select_shl_basic {
    SELECT 1 << 0, 1 << 1, 1 << 2, 1 << 3;
    SELECT 2 << 0, 2 << 1, 2 << 2, 2 << 3;
} {1|2|4|8
2|4|8|16}

do_execsql_test select_shl_negative_numbers {
    SELECT -1 << 0, -1 << 1, -1 << 2, -1 << 3;
    SELECT -2 << 0, -2 << 1, -2 << 2, -2 << 3;
} {-1|-2|-4|-8
-2|-4|-8|-16}
do_execsql_test select_shl_negative_shifts {
    SELECT 8 << -1, 8 << -2, 8 << -3, 8 << -4;
    SELECT -8 << -1, -8 << -2, -8 << -3, -8 << -4;
} {4|2|1|0
-4|-2|-1|-1}

do_execsql_test select_shl_large_shifts {
    SELECT 1 << 62, 1 << 63, 1 << 64;
    SELECT -1 << 62, -1 << 63, -1 << 64;
} {4611686018427387904|-9223372036854775808|0
-4611686018427387904|-9223372036854775808|0}

do_execsql_test select_shl_text_conversion {
    SELECT '1' << '2';
    SELECT '8' << '-2';
    SELECT '-4' << '2';
} {4 2 -16}

do_execsql_test select_shl_chained {
    SELECT (1 << 2) << 3;
    SELECT (2 << 1) << (1 << 1);
} {32 16}

do_execsql_test select_shl_numeric_types {
    SELECT CAST(1 AS INTEGER) << 2;
    SELECT 1.0 << 2;
    SELECT 1.5 << 2;
} {4 4 4}

do_execsql_test select_fuzz_failure_case {
  SELECT (-9 << ((-6) << (9)) >> ((5)) % -10 - + - (-9));
} {-16}

# regression test for https://github.com/tursodatabase/turso/issues/1157
do_execsql_test select-invalid-numeric-text {
  select -'e';
} {0}

do_execsql_test select-invalid-numeric-text {
  select -'E';
} {0}

do_execsql_test_on_specific_db {:memory:} select-union-all-1 {
  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);
  
  SELECT x FROM t1
  UNION ALL 
  SELECT x FROM t2
  UNION ALL
  SELECT x FROM t3;
} {1
2
3
4
5
6
7
8
9}

do_execsql_test_on_specific_db {:memory:} select-union-all-with-filters {
  CREATE TABLE t4(x INTEGER);
  CREATE TABLE t5(x INTEGER);
  CREATE TABLE t6(x INTEGER);
  
  INSERT INTO t4 VALUES(1),(2),(3),(4);
  INSERT INTO t5 VALUES(5),(6),(7),(8);
  INSERT INTO t6 VALUES(9),(10),(11),(12);

  SELECT x FROM t4 WHERE x > 2
  UNION ALL
  SELECT x FROM t5 WHERE x < 7 
  UNION ALL
  SELECT x FROM t6 WHERE x = 10;
} {3
4
5
6
10}

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:} select-union-1 {
    CREATE TABLE t(x TEXT, y TEXT);
    CREATE TABLE u(x TEXT, y TEXT);
    INSERT INTO t VALUES('x','x'),('y','y');
    INSERT INTO u VALUES('x','x'),('y','y');

    select * from t UNION select * from u;
    } {x|x
    y|y}

    do_execsql_test_on_specific_db {:memory:} select-union-all-union {
    CREATE TABLE t(x TEXT, y TEXT);
    CREATE TABLE u(x TEXT, y TEXT);
    CREATE TABLE v(x TEXT, y TEXT);
    INSERT INTO t VALUES('x','x'),('y','y');
    INSERT INTO u VALUES('x','x'),('y','y');
    INSERT INTO v VALUES('x','x'),('y','y');

    select * from t UNION select * from u UNION ALL select * from v;
    } {x|x
    y|y
    x|x
    y|y}

    do_execsql_test_on_specific_db {:memory:} select-union-all-union-2 {
    CREATE TABLE t(x TEXT, y TEXT);
    CREATE TABLE u(x TEXT, y TEXT);
    CREATE TABLE v(x TEXT, y TEXT);
    INSERT INTO t VALUES('x','x'),('y','y');
    INSERT INTO u VALUES('x','x'),('y','y');
    INSERT INTO v VALUES('x','x'),('y','y');

    select * from t UNION ALL select * from u UNION select * from v;
    } {x|x
    y|y}

    do_execsql_test_on_specific_db {:memory:} select-union-3 {
    CREATE TABLE t(x TEXT, y TEXT);
    CREATE TABLE u(x TEXT, y TEXT);
    CREATE TABLE v(x TEXT, y TEXT);
    INSERT INTO t VALUES('x','x'),('y','y');
    INSERT INTO u VALUES('x','x'),('y','y');
    INSERT INTO v VALUES('x','x'),('y','y');

    select * from t UNION select * from u UNION select * from v;
    } {x|x
    y|y}

    do_execsql_test_on_specific_db {:memory:} select-union-4 {
    CREATE TABLE t(x TEXT, y TEXT);
    CREATE TABLE u(x TEXT, y TEXT);
    CREATE TABLE v(x TEXT, y TEXT);
    INSERT INTO t VALUES('x','x'),('y','y');
    INSERT INTO u VALUES('x','x'),('y','y');
    INSERT INTO v VALUES('x','x'),('y','y');

    select * from t UNION select * from u UNION select * from v UNION select * from t;
    } {x|x
    y|y}

    do_execsql_test_on_specific_db {:memory:} select-union-all-union-3 {
    CREATE TABLE t(x TEXT, y TEXT);
    CREATE TABLE u(x TEXT, y TEXT);
    CREATE TABLE v(x TEXT, y TEXT);
    INSERT INTO t VALUES('x','x'),('y','y');
    INSERT INTO u VALUES('x','x'),('y','y');
    INSERT INTO v VALUES('x','x'),('y','y');

    select * from t UNION select * from u UNION select * from v UNION ALL select * from t;
    } {x|x
    y|y
    x|x
    y|y}

    do_execsql_test_on_specific_db {:memory:} select-intersect-1 {
      CREATE TABLE t(x TEXT, y TEXT);
      CREATE TABLE u(x TEXT, y TEXT);
      INSERT INTO t VALUES('x','x'),('y','y');
      INSERT INTO u VALUES('x','x'),('z','y');

      select * from t INTERSECT select * from u;
    } {x|x}

    do_execsql_test_on_specific_db {:memory:} select-intersect-2 {
      CREATE TABLE t(x TEXT, y TEXT);
      CREATE TABLE u(x TEXT, y TEXT);
      CREATE TABLE v(x TEXT, y TEXT);
      INSERT INTO t VALUES('x','x'),('y','y');
      INSERT INTO u VALUES('x','x'),('y','y');
      INSERT INTO v VALUES('a','x'),('y','y');

      select * from t INTERSECT select * from u INTERSECT select * from v INTERSECT select * from t;
    } {y|y}

    do_execsql_test_on_specific_db {:memory:} select-intersect-union {
      CREATE TABLE t(x TEXT, y TEXT);
      CREATE TABLE u(x TEXT, y TEXT);
      CREATE TABLE v(x TEXT, y TEXT);
      INSERT INTO t VALUES('x','x'),('y','y');
      INSERT INTO u VALUES('x','x'),('z','y');
      INSERT INTO v VALUES('x','x'),('z','z');

      select * from t INTERSECT select * from u UNION select * from v;
    } {x|x
    z|z}

    do_execsql_test_on_specific_db {:memory:} select-union-intersect {
      CREATE TABLE t(x TEXT, y TEXT);
      CREATE TABLE u(x TEXT, y TEXT);
      CREATE TABLE v(x TEXT, y TEXT);
      INSERT INTO t VALUES('x','x'),('y','y');
      INSERT INTO u VALUES('x','x'),('z','y');
      INSERT INTO v VALUES('x','x'),('z','z');

      select * from t UNION select * from u INTERSECT select * from v;
    } {x|x}

    do_execsql_test_on_specific_db {:memory:} select-union-all-intersect {
      CREATE TABLE t(x TEXT, y TEXT);
      CREATE TABLE u(x TEXT, y TEXT);
      CREATE TABLE v(x TEXT, y TEXT);
      INSERT INTO t VALUES('x','x'),('y','y');
      INSERT INTO u VALUES('x','x'),('z','y');
      INSERT INTO v VALUES('x','x'),('z','z');

      select * from t UNION ALL select * from u INTERSECT select * from v;
    } {x|x}

    do_execsql_test_on_specific_db {:memory:} select-intersect-union-all {
      CREATE TABLE t(x TEXT, y TEXT);
      CREATE TABLE u(x TEXT, y TEXT);
      CREATE TABLE v(x TEXT, y TEXT);
      INSERT INTO t VALUES('x','x'),('y','y');
      INSERT INTO u VALUES('x','x'),('z','y');
      INSERT INTO v VALUES('x','x'),('z','z');

      select * from t INTERSECT select * from u UNION ALL select * from v;
    } {x|x
    x|x
    z|z}

    do_execsql_test_on_specific_db {:memory:} select-intersect-with-limit {
      CREATE TABLE t(x TEXT, y TEXT);
      CREATE TABLE u(x TEXT, y TEXT);
      INSERT INTO t VALUES('x','x'),('y','y'), ('z','z');
      INSERT INTO u VALUES('x','x'),('y','y'), ('z','z');

      select * from t INTERSECT select * from u limit 2;
    } {x|x
    y|y}

    do_execsql_test_on_specific_db {:memory:} select-intersect-union-with-limit {
      CREATE TABLE t(x TEXT, y TEXT);
      CREATE TABLE u(x TEXT, y TEXT);
      CREATE TABLE v(x TEXT, y TEXT);
      INSERT INTO t VALUES('x','x'),('y','y'), ('z','z');
      INSERT INTO u VALUES('d','d'),('e','e'), ('z','z');
      INSERT INTO v VALUES('a','a'),('b','b');

      select * from t INTERSECT select * from u UNION select * from v limit 3;
    } {a|a
    b|b
    z|z}
}