• Home
  • Line#
  • Scopes#
  • Navigate#
  • Raw
  • Download
1# 2001 September 15
2#
3# The author disclaims copyright to this source code.  In place of
4# 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 for
14# 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.tcl
20
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}]
35puts result=$result
36do_test select8-1.1 {
37  execsql {
38    SELECT DISTINCT artist,sum(timesplayed) AS total
39    FROM songs
40    GROUP BY LOWER(artist)
41    LIMIT 1 OFFSET 1
42  }
43} [lrange $result 2 3]
44do_test select8-1.2 {
45  execsql {
46    SELECT DISTINCT artist,sum(timesplayed) AS total
47    FROM songs
48    GROUP BY LOWER(artist)
49    LIMIT 2 OFFSET 1
50  }
51} [lrange $result 2 5]
52do_test select8-1.3 {
53  execsql {
54    SELECT DISTINCT artist,sum(timesplayed) AS total
55    FROM songs
56    GROUP BY LOWER(artist)
57    LIMIT -1 OFFSET 2
58  }
59} [lrange $result 4 end]
60
61
62finish_test
63