xref: /sqlite-3.40.0/test/windowerr.tcl (revision 5e61c1b7) 
1# 2018 May 19
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#
12
13source [file join [file dirname $argv0] pg_common.tcl]
14
15#=========================================================================
16
17start_test windowerr "2019 March 01"
18ifcapable !windowfunc
19
20execsql_test 1.0 {
21  DROP TABLE IF EXISTS t1;
22  CREATE TABLE t1(a INTEGER, b INTEGER);
23  INSERT INTO t1 VALUES(1, 1);
24  INSERT INTO t1 VALUES(2, 2);
25  INSERT INTO t1 VALUES(3, 3);
26  INSERT INTO t1 VALUES(4, 4);
27  INSERT INTO t1 VALUES(5, 5);
28}
29
30foreach {tn frame} {
31  1 "ORDER BY a ROWS BETWEEN -1 PRECEDING AND 1 FOLLOWING"
32  2 "ORDER BY a ROWS BETWEEN  1 PRECEDING AND -1 FOLLOWING"
33
34  3 "ORDER BY a RANGE BETWEEN -1 PRECEDING AND 1 FOLLOWING"
35  4 "ORDER BY a RANGE BETWEEN  1 PRECEDING AND -1 FOLLOWING"
36
37  5 "ORDER BY a GROUPS BETWEEN -1 PRECEDING AND 1 FOLLOWING"
38  6 "ORDER BY a GROUPS BETWEEN  1 PRECEDING AND -1 FOLLOWING"
39
40  7 "ORDER BY a,b RANGE BETWEEN  1 PRECEDING AND 1 FOLLOWING"
41
42  8 "PARTITION BY a RANGE BETWEEN  1 PRECEDING AND 1 FOLLOWING"
43} {
44  errorsql_test 1.$tn "
45  SELECT a, sum(b) OVER (
46    $frame
47  ) FROM t1 ORDER BY 1
48  "
49}
50errorsql_test 2.1 {
51  SELECT sum( sum(a) OVER () ) FROM t1;
52}
53
54errorsql_test 2.2 {
55  SELECT sum(a) OVER () AS xyz FROM t1 ORDER BY sum(xyz);
56}
57
58errorsql_test 3.0 {
59  SELECT sum(a) OVER win FROM t1
60  WINDOW win AS (ROWS BETWEEN 'hello' PRECEDING AND 10 FOLLOWING)
61}
62errorsql_test 3.2 {
63  SELECT sum(a) OVER win FROM t1
64  WINDOW win AS (ROWS BETWEEN 10 PRECEDING AND x'ABCD' FOLLOWING)
65}
66
67errorsql_test 3.3 {
68  SELECT row_number(a) OVER () FROM t1;
69}
70
71finish_test
72
73