turso/testing/offset.test

#!/usr/bin/env tclsh

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

do_execsql_test select-offset-0 {
  SELECT id FROM users ORDER BY id LIMIT 1 OFFSET 0;
} {1}

do_execsql_test select-offset-1 {
  SELECT id FROM users ORDER BY id LIMIT 1 OFFSET 1;
} {2}

do_execsql_test select-offset-negative {
  SELECT id FROM users ORDER BY id LIMIT 1 OFFSET -1;
} {1}

do_execsql_test select-offset-0-groupby {
  SELECT COUNT(*) FROM users GROUP BY STATE ORDER BY STATE LIMIT 5 OFFSET 0;
} {168
166
162
153
166}

do_execsql_test select-offset-1-groupby {
  SELECT COUNT(*) FROM users GROUP BY STATE ORDER BY STATE LIMIT 5 OFFSET 1;
} {166
162
153
166
170}

do_execsql_test select-offset-subquery {
    SELECT id, first_name, age 
    FROM (
        SELECT id, first_name, age 
        FROM users 
        ORDER BY id ASC 
        LIMIT 5 OFFSET 2
    )
    ORDER BY id DESC;
} {7|Aimee|24
6|Nicholas|89
5|Edward|15
4|Jennifer|33
3|Tommy|18}

do_execsql_test_on_specific_db {:memory:} select-limit-comma-offset-equivalence {
    CREATE TABLE nums (x INTEGER);
    INSERT INTO nums VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
    SELECT x FROM nums ORDER BY x LIMIT 3 OFFSET 2;
    SELECT x FROM nums ORDER BY x LIMIT 2,3;
} {3
4
5
3
4
5}

# https://github.com/tursodatabase/turso/issues/3300
do_execsql_test_on_specific_db {:memory:} select-ungrouped-aggregate-with-offset-limit {
    CREATE TABLE t(a INTEGER);
    INSERT INTO t VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
    SELECT COUNT(a) FROM t LIMIT 1 OFFSET 1;
} {}


do_execsql_test_on_specific_db {:memory:} offset-expr-can-be-cast-losslessly-1 {
  SELECT 1 LIMIT 3 OFFSET 1.1 + 2.9;
} {}

do_execsql_test_on_specific_db {:memory:} offset-expr-can-be-cast-losslessly-2 {
  CREATE TABLE T(a);
  INSERT INTO T VALUES (1),(2),(3),(4);
  SELECT * FROM T LIMIT 1+'2' OFFSET 1.6/2 + 3.6/3 + 4*0.25;
} {4}

# Strings are cast to float. Final result is integer losslessly
do_execsql_test_on_specific_db {:memory:} offset-expr-can-be-cast-losslessly-3 {
  CREATE TABLE T(a);
  INSERT INTO T VALUES (1),(2),(3),(4);
  SELECT * FROM T LIMIT 3 OFFSET '0.8' + '1.2' + '4'*'0.25';
} {4}

# Strings are cast to 0. Expression still valid.
do_execsql_test_on_specific_db {:memory:} offset-expr-int-and-string {
  SELECT 1 LIMIT 3 OFFSET 3/3 + 'test' + 4*'test are best';
} {}

do_execsql_test_in_memory_error_content offset-expr-cannot-be-cast-losslessly-1 {
  SELECT 1 LIMIT 3 OFFSET 1.1;
} {"datatype mismatch"}

do_execsql_test_in_memory_error_content offset-expr-cannot-be-cast-losslessly-2 {
  SELECT 1 LIMIT 3 OFFSET 1.1 + 2.2 + 1.9/8;
} {"datatype mismatch"}

# Return error as float in expression cannot be cast losslessly
do_execsql_test_in_memory_error_content offset-expr-cannot-be-cast-losslessly-3 {
  SELECT 1 LIMIT 3 OFFSET 1.1 + 'a';
} {"datatype mismatch"}

do_execsql_test_in_memory_error_content offset-expr-invalid-data-type-1 {
  SELECT 1 LIMIT 3 OFFSET 'a';
} {"datatype mismatch"}

do_execsql_test_in_memory_error_content offset-expr-invalid-data-type-2 {
  SELECT 1 LIMIT 3 OFFSET NULL;
} {"datatype mismatch"}

# Expression below evaluates to NULL (string → 0)
do_execsql_test_in_memory_error_content offset-expr-invalid-data-type-3 {
  SELECT 1 LIMIT 3 OFFSET 1/'iwillbezero ;-; ';
} {"datatype mismatch"}