xref: /sqlite-3.40.0/test/window9.test (revision 62742fd2) 
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
101finish_test
102
103