xref: /sqlite-3.40.0/test/window9.test (revision f71a243a) 
1# 2019 June 8
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
14set testdir [file dirname $argv0]
15source $testdir/tester.tcl
16set testprefix window9
17
18ifcapable !windowfunc {
19  finish_test
20  return
21}
22
23do_execsql_test 1.0 {
24  CREATE TABLE fruits(
25     name TEXT COLLATE NOCASE,
26     color TEXT COLLATE NOCASE
27  );
28}
29
30do_execsql_test 1.1 {
31  INSERT INTO fruits (name, color) VALUES ('apple', 'RED');
32  INSERT INTO fruits (name, color) VALUES ('APPLE', 'yellow');
33  INSERT INTO fruits (name, color) VALUES ('pear', 'YELLOW');
34  INSERT INTO fruits (name, color) VALUES ('PEAR', 'green');
35}
36
37do_execsql_test 1.2 {
38  SELECT name, color, dense_rank() OVER (ORDER BY name) FROM fruits;
39} {
40  apple RED    1
41  APPLE yellow 1
42  pear  YELLOW 2
43  PEAR  green  2
44}
45
46do_execsql_test 1.3 {
47  SELECT name, color,
48    dense_rank() OVER (PARTITION BY name ORDER BY color)
49  FROM fruits;
50} {
51  apple RED    1
52  APPLE yellow 2
53  PEAR green   1
54  pear YELLOW  2
55}
56
57do_execsql_test 1.4 {
58  SELECT name, color,
59    dense_rank() OVER (ORDER BY name),
60    dense_rank() OVER (PARTITION BY name ORDER BY color)
61  FROM fruits;
62} {
63  apple RED    1 1
64  APPLE yellow 1 2
65  PEAR  green  2 1
66  pear  YELLOW 2 2
67}
68
69do_execsql_test 1.5 {
70  SELECT name, color,
71    dense_rank() OVER (ORDER BY name),
72    dense_rank() OVER (PARTITION BY name ORDER BY color)
73  FROM fruits ORDER BY color;
74} {
75  PEAR  green  2 1
76  apple RED    1 1
77  APPLE yellow 1 2
78  pear  YELLOW 2 2
79}
80
81do_execsql_test 2.0 {
82  CREATE TABLE t1(a BLOB, b INTEGER, c COLLATE nocase);
83  INSERT INTO t1 VALUES(1, 2, 'abc');
84  INSERT INTO t1 VALUES(3, 4, 'ABC');
85}
86
87do_execsql_test 2.1.1 {
88  SELECT c=='Abc' FROM t1
89} {1     1}
90do_execsql_test 2.1.2 {
91  SELECT c=='Abc', rank() OVER (ORDER BY b) FROM t1
92} {1 1   1 2}
93
94do_execsql_test 2.2.1 {
95  SELECT b=='2' FROM t1
96} {1     0}
97do_execsql_test 2.2.2 {
98  SELECT b=='2', rank() OVER (ORDER BY a) FROM t1
99} {1 1   0 2}
100
101#-------------------------------------------------------------------------
102reset_db
103do_execsql_test 3.0 {
104  CREATE TABLE t1(a);
105  CREATE TABLE t2(a,b,c);
106}
107
108do_execsql_test 3.1 {
109  SELECT EXISTS(SELECT 1 FROM t1 ORDER BY sum(a) OVER ()) FROM t1;
110}
111
112do_execsql_test 3.2 {
113  SELECT sum(a) OVER () FROM t2
114   ORDER BY EXISTS(SELECT 1 FROM t2 ORDER BY sum(a) OVER ());
115}
116
117do_catchsql_test 3.3 {
118  SELECT a, sum(a) OVER (ORDER BY a DESC) FROM t2
119  ORDER BY EXISTS(
120    SELECT 1 FROM t2 ORDER BY sum(a) OVER (ORDER BY a)
121  ) OVER (ORDER BY a);
122} {1 {near "OVER": syntax error}}
123
124do_catchsql_test 3.4 {
125  SELECT y, y+1, y+2 FROM (
126      SELECT c IN (
127        SELECT min(a) OVER (),
128        (abs(row_number() OVER())+22)/19,
129        max(a) OVER () FROM t1
130        ) AS y FROM t2
131      );
132} {1 {sub-select returns 3 columns - expected 1}}
133
134
135finish_test
136
137