1# 2018 June 04 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 14#################################################### 15# DO NOT EDIT! THIS FILE IS AUTOMATICALLY GENERATED! 16#################################################### 17 18set testdir [file dirname $argv0] 19source $testdir/tester.tcl 20set testprefix window4 21 22do_execsql_test 1.0 { 23 DROP TABLE IF EXISTS t3; 24 CREATE TABLE t3(a TEXT PRIMARY KEY); 25 INSERT INTO t3 VALUES('a'), ('b'), ('c'), ('d'), ('e'); 26 INSERT INTO t3 VALUES('f'), ('g'), ('h'), ('i'), ('j'); 27} {} 28 29do_execsql_test 1.1 { 30 SELECT a, ntile(1) OVER (ORDER BY a) FROM t3 31} {a 1 b 1 c 1 d 1 e 1 f 1 g 1 h 1 i 1 j 1} 32 33do_execsql_test 1.2 { 34 SELECT a, ntile(2) OVER (ORDER BY a) FROM t3 35} {a 1 b 1 c 1 d 1 e 1 f 2 g 2 h 2 i 2 j 2} 36 37do_execsql_test 1.3 { 38 SELECT a, ntile(3) OVER (ORDER BY a) FROM t3 39} {a 1 b 1 c 1 d 1 e 2 f 2 g 2 h 3 i 3 j 3} 40 41do_execsql_test 1.4 { 42 SELECT a, ntile(4) OVER (ORDER BY a) FROM t3 43} {a 1 b 1 c 1 d 2 e 2 f 2 g 3 h 3 i 4 j 4} 44 45do_execsql_test 1.5 { 46 SELECT a, ntile(5) OVER (ORDER BY a) FROM t3 47} {a 1 b 1 c 2 d 2 e 3 f 3 g 4 h 4 i 5 j 5} 48 49do_execsql_test 1.6 { 50 SELECT a, ntile(6) OVER (ORDER BY a) FROM t3 51} {a 1 b 1 c 2 d 2 e 3 f 3 g 4 h 4 i 5 j 6} 52 53do_execsql_test 1.7 { 54 SELECT a, ntile(7) OVER (ORDER BY a) FROM t3 55} {a 1 b 1 c 2 d 2 e 3 f 3 g 4 h 5 i 6 j 7} 56 57do_execsql_test 1.8 { 58 SELECT a, ntile(8) OVER (ORDER BY a) FROM t3 59} {a 1 b 1 c 2 d 2 e 3 f 4 g 5 h 6 i 7 j 8} 60 61do_execsql_test 1.9 { 62 SELECT a, ntile(9) OVER (ORDER BY a) FROM t3 63} {a 1 b 1 c 2 d 3 e 4 f 5 g 6 h 7 i 8 j 9} 64 65do_execsql_test 1.10 { 66 SELECT a, ntile(10) OVER (ORDER BY a) FROM t3 67} {a 1 b 2 c 3 d 4 e 5 f 6 g 7 h 8 i 9 j 10} 68 69do_execsql_test 1.11 { 70 SELECT a, ntile(11) OVER (ORDER BY a) FROM t3 71} {a 1 b 2 c 3 d 4 e 5 f 6 g 7 h 8 i 9 j 10} 72 73do_execsql_test 1.12 { 74 SELECT a, ntile(12) OVER (ORDER BY a) FROM t3 75} {a 1 b 2 c 3 d 4 e 5 f 6 g 7 h 8 i 9 j 10} 76 77do_execsql_test 1.13 { 78 SELECT a, ntile(13) OVER (ORDER BY a) FROM t3 79} {a 1 b 2 c 3 d 4 e 5 f 6 g 7 h 8 i 9 j 10} 80 81do_execsql_test 1.14 { 82 SELECT a, ntile(14) OVER (ORDER BY a) FROM t3 83} {a 1 b 2 c 3 d 4 e 5 f 6 g 7 h 8 i 9 j 10} 84 85do_execsql_test 1.15 { 86 SELECT a, ntile(15) OVER (ORDER BY a) FROM t3 87} {a 1 b 2 c 3 d 4 e 5 f 6 g 7 h 8 i 9 j 10} 88 89do_execsql_test 1.16 { 90 SELECT a, ntile(16) OVER (ORDER BY a) FROM t3 91} {a 1 b 2 c 3 d 4 e 5 f 6 g 7 h 8 i 9 j 10} 92 93do_execsql_test 1.17 { 94 SELECT a, ntile(17) OVER (ORDER BY a) FROM t3 95} {a 1 b 2 c 3 d 4 e 5 f 6 g 7 h 8 i 9 j 10} 96 97do_execsql_test 1.18 { 98 SELECT a, ntile(18) OVER (ORDER BY a) FROM t3 99} {a 1 b 2 c 3 d 4 e 5 f 6 g 7 h 8 i 9 j 10} 100 101do_execsql_test 1.19 { 102 SELECT a, ntile(19) OVER (ORDER BY a) FROM t3 103} {a 1 b 2 c 3 d 4 e 5 f 6 g 7 h 8 i 9 j 10} 104 105do_execsql_test 2.0 { 106 DROP TABLE IF EXISTS t4; 107 CREATE TABLE t4(a INTEGER PRIMARY KEY, b TEXT, c INTEGER); 108 INSERT INTO t4 VALUES(1, 'A', 9); 109 INSERT INTO t4 VALUES(2, 'B', 3); 110 INSERT INTO t4 VALUES(3, 'C', 2); 111 INSERT INTO t4 VALUES(4, 'D', 10); 112 INSERT INTO t4 VALUES(5, 'E', 5); 113 INSERT INTO t4 VALUES(6, 'F', 1); 114 INSERT INTO t4 VALUES(7, 'G', 1); 115 INSERT INTO t4 VALUES(8, 'H', 2); 116 INSERT INTO t4 VALUES(9, 'I', 10); 117 INSERT INTO t4 VALUES(10, 'J', 4); 118} {} 119 120do_execsql_test 2.1 { 121 SELECT a, nth_value(b, c) OVER (ORDER BY a) FROM t4 122} {1 {} 2 {} 3 B 4 {} 5 E 6 A 7 A 8 B 9 {} 10 D} 123 124do_execsql_test 2.2.1 { 125 SELECT a, lead(b) OVER (ORDER BY a) FROM t4 126} {1 B 2 C 3 D 4 E 5 F 6 G 7 H 8 I 9 J 10 {}} 127 128do_execsql_test 2.2.2 { 129 SELECT a, lead(b, 2) OVER (ORDER BY a) FROM t4 130} {1 C 2 D 3 E 4 F 5 G 6 H 7 I 8 J 9 {} 10 {}} 131 132do_execsql_test 2.2.3 { 133 SELECT a, lead(b, 3, 'abc') OVER (ORDER BY a) FROM t4 134} {1 D 2 E 3 F 4 G 5 H 6 I 7 J 8 abc 9 abc 10 abc} 135 136do_execsql_test 2.3.1 { 137 SELECT a, lag(b) OVER (ORDER BY a) FROM t4 138} {1 {} 2 A 3 B 4 C 5 D 6 E 7 F 8 G 9 H 10 I} 139 140do_execsql_test 2.3.2 { 141 SELECT a, lag(b, 2) OVER (ORDER BY a) FROM t4 142} {1 {} 2 {} 3 A 4 B 5 C 6 D 7 E 8 F 9 G 10 H} 143 144do_execsql_test 2.3.3 { 145 SELECT a, lag(b, 3, 'abc') OVER (ORDER BY a) FROM t4 146} {1 abc 2 abc 3 abc 4 A 5 B 6 C 7 D 8 E 9 F 10 G} 147 148do_execsql_test 3.0 { 149 DROP TABLE IF EXISTS t5; 150 CREATE TABLE t5(a INTEGER PRIMARY KEY, b TEXT, c TEXT, d INTEGER); 151 INSERT INTO t5 VALUES(1, 'A', 'one', 5); 152 INSERT INTO t5 VALUES(2, 'B', 'two', 4); 153 INSERT INTO t5 VALUES(3, 'A', 'three', 3); 154 INSERT INTO t5 VALUES(4, 'B', 'four', 2); 155 INSERT INTO t5 VALUES(5, 'A', 'five', 1); 156} {} 157 158do_execsql_test 3.1 { 159 SELECT a, nth_value(c, d) OVER (ORDER BY b) FROM t5 160} {1 {} 3 five 5 one 2 two 4 three} 161 162do_execsql_test 3.2 { 163 SELECT a, nth_value(c, d) OVER (PARTITION BY b ORDER BY a) FROM t5 164} {1 {} 3 {} 5 one 2 {} 4 four} 165 166finish_test 167