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.1.1 2012/02/21 17:04:16 misho Exp $
17:
18: set testdir [file dirname $argv0]
19: source $testdir/tester.tcl
20:
21: execsql {
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: }
30: set result [execsql {
31: SELECT DISTINCT artist,sum(timesplayed) AS total
32: FROM songs
33: GROUP BY LOWER(artist)
34: }]
35: puts result=$result
36: do_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]
44: do_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]
52: do_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:
62: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>