AryaWu/sqlite

1# 2001 September 152#3# The author disclaims copyright to this source code.  In place of4# a legal notice, here is a blessing:5#6#    May you do good and not evil.7#    May you find forgiveness for yourself and forgive others.8#    May you share freely, never taking more than you give.9#10#***********************************************************************11# This file implements regression tests for SQLite library. 12#13# The focus of this file is testing that LIMIT and OFFSET work for14# unusual combinations SELECT statements.15#16# $Id: select8.test,v 1.1 2008/01/12 12:48:09 drh Exp $17 18set testdir [file dirname $argv0]19source $testdir/tester.tcl20 21execsql {22  CREATE TABLE songs(songid, artist, timesplayed);23  INSERT INTO songs VALUES(1,'one',1);24  INSERT INTO songs VALUES(2,'one',2);25  INSERT INTO songs VALUES(3,'two',3);26  INSERT INTO songs VALUES(4,'three',5);27  INSERT INTO songs VALUES(5,'one',7);28  INSERT INTO songs VALUES(6,'two',11);29}30set result [execsql {31  SELECT DISTINCT artist,sum(timesplayed) AS total      32  FROM songs      33  GROUP BY LOWER(artist)      34}]35do_test select8-1.1 {36  execsql {37    SELECT DISTINCT artist,sum(timesplayed) AS total      38    FROM songs      39    GROUP BY LOWER(artist)      40    LIMIT 1 OFFSET 141  }42} [lrange $result 2 3]43do_test select8-1.2 {44  execsql {45    SELECT DISTINCT artist,sum(timesplayed) AS total      46    FROM songs      47    GROUP BY LOWER(artist)      48    LIMIT 2 OFFSET 149  }50} [lrange $result 2 5]51do_test select8-1.3 {52  execsql {53    SELECT DISTINCT artist,sum(timesplayed) AS total      54    FROM songs      55    GROUP BY LOWER(artist)      56    LIMIT -1 OFFSET 257  }58} [lrange $result 4 end]59 60 61finish_test62