Debian Codegraph @

test/windowerr.tcl

raw
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
13 source [file join [file dirname $argv0] pg_common.tcl]
14
15 #=========================================================================
16
17 start_test windowerr "2019 March 01"
18 ifcapable !windowfunc
19
20 execsql_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
30 foreach {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 }
50 errorsql_test 2.1 {
51 SELECT sum( sum(a) OVER () ) FROM t1;
52 }
53
54 errorsql_test 2.2 {
55 SELECT sum(a) OVER () AS xyz FROM t1 ORDER BY sum(xyz);
56 }
57
58 errorsql_test 3.0 {
59 SELECT sum(a) OVER win FROM t1
60 WINDOW win AS (ROWS BETWEEN 'hello' PRECEDING AND 10 FOLLOWING)
61 }
62 errorsql_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
67 errorsql_test 3.3 {
68 SELECT row_number(a) OVER () FROM t1;
69 }
70
71 finish_test
72