limbo/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}