1# 2019-08-30 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# Test cases for RANGE BETWEEN and especially with NULLS LAST 12# 13 14set testdir [file dirname $argv0] 15source $testdir/tester.tcl 16set testprefix windowA 17 18ifcapable !windowfunc { 19 finish_test 20 return 21} 22 23do_execsql_test 1.0 { 24 CREATE TABLE t1(a INTEGER PRIMARY KEY, b CHAR(1), d FLOAT); 25 INSERT INTO t1 VALUES 26 (1, 'A', 5.4), 27 (2, 'B', 5.55), 28 (3, 'C', 8.0), 29 (4, 'D', 10.25), 30 (5, 'E', 10.26), 31 (6, 'N', NULL), 32 (7, 'N', NULL); 33} {} 34 35do_execsql_test 1.1 { 36 SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1 37 WINDOW w1 AS 38 (ORDER BY d DESC NULLS LAST 39 RANGE BETWEEN 2.50 PRECEDING AND 2.25 FOLLOWING) 40 ORDER BY +d DESC NULLS LAST, +a; 41} [list \ 42 5 E 10.26 ED \ 43 4 D 10.25 EDC \ 44 3 C 8.0 EDC \ 45 2 B 5.55 CBA \ 46 1 A 5.4 BA \ 47 6 N NULL NN \ 48 7 N NULL NN \ 49] 50 51do_execsql_test 1.2 { 52 SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1 53 WINDOW w1 AS 54 (ORDER BY d DESC NULLS FIRST 55 RANGE BETWEEN 2.50 PRECEDING AND 2.25 FOLLOWING) 56 ORDER BY +d DESC NULLS FIRST, +a; 57} [list \ 58 6 N NULL NN \ 59 7 N NULL NN \ 60 5 E 10.26 ED \ 61 4 D 10.25 EDC \ 62 3 C 8.0 EDC \ 63 2 B 5.55 CBA \ 64 1 A 5.4 BA \ 65] 66 67do_execsql_test 1.3 { 68 SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1 69 WINDOW w1 AS 70 (ORDER BY d DESC NULLS LAST 71 RANGE BETWEEN 2.50 PRECEDING AND UNBOUNDED FOLLOWING) 72 ORDER BY +d DESC NULLS LAST, +a; 73} [list \ 74 5 E 10.26 EDCBANN \ 75 4 D 10.25 EDCBANN \ 76 3 C 8.0 EDCBANN \ 77 2 B 5.55 CBANN \ 78 1 A 5.4 BANN \ 79 6 N NULL NN \ 80 7 N NULL NN \ 81] 82 83do_execsql_test 1.4 { 84 SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1 85 WINDOW w1 AS 86 (ORDER BY d DESC NULLS FIRST 87 RANGE BETWEEN 2.50 PRECEDING AND UNBOUNDED FOLLOWING) 88 ORDER BY +d DESC NULLS FIRST, +a; 89} [list \ 90 6 N NULL NNEDCBA \ 91 7 N NULL NNEDCBA \ 92 5 E 10.26 EDCBA \ 93 4 D 10.25 EDCBA \ 94 3 C 8.0 EDCBA \ 95 2 B 5.55 CBA \ 96 1 A 5.4 BA \ 97] 98 99do_execsql_test 1.5 { 100 SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1 101 WINDOW w1 AS 102 (ORDER BY d DESC NULLS LAST 103 RANGE BETWEEN 2.50 PRECEDING AND CURRENT ROW) 104 ORDER BY +d DESC NULLS LAST, +a; 105} [list \ 106 5 E 10.26 E \ 107 4 D 10.25 ED \ 108 3 C 8.0 EDC \ 109 2 B 5.55 CB \ 110 1 A 5.4 BA \ 111 6 N NULL NN \ 112 7 N NULL NN \ 113] 114 115do_execsql_test 1.6 { 116 SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1 117 WINDOW w1 AS 118 (ORDER BY d DESC NULLS FIRST 119 RANGE BETWEEN 2.50 PRECEDING AND CURRENT ROW) 120 ORDER BY +d DESC NULLS FIRST, +a; 121} [list \ 122 6 N NULL NN \ 123 7 N NULL NN \ 124 5 E 10.26 E \ 125 4 D 10.25 ED \ 126 3 C 8.0 EDC \ 127 2 B 5.55 CB \ 128 1 A 5.4 BA \ 129] 130 131do_execsql_test 2.1 { 132 SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1 133 WINDOW w1 AS 134 (ORDER BY d DESC NULLS LAST 135 RANGE BETWEEN UNBOUNDED PRECEDING AND 2.25 FOLLOWING) 136 ORDER BY +d DESC NULLS LAST, +a; 137} [list \ 138 5 E 10.26 ED \ 139 4 D 10.25 EDC \ 140 3 C 8.0 EDC \ 141 2 B 5.55 EDCBA \ 142 1 A 5.4 EDCBA \ 143 6 N NULL EDCBANN \ 144 7 N NULL EDCBANN \ 145] 146 147do_execsql_test 2.2 { 148 SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1 149 WINDOW w1 AS 150 (ORDER BY d DESC NULLS FIRST 151 RANGE BETWEEN UNBOUNDED PRECEDING AND 2.25 FOLLOWING) 152 ORDER BY +d DESC NULLS FIRST, +a; 153} [list \ 154 6 N NULL NN \ 155 7 N NULL NN \ 156 5 E 10.26 NNED \ 157 4 D 10.25 NNEDC \ 158 3 C 8.0 NNEDC \ 159 2 B 5.55 NNEDCBA \ 160 1 A 5.4 NNEDCBA \ 161] 162 163do_execsql_test 2.3 { 164 SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1 165 WINDOW w1 AS 166 (ORDER BY d DESC NULLS LAST 167 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) 168 ORDER BY +d DESC NULLS LAST, +a; 169} [list \ 170 5 E 10.26 EDCBANN \ 171 4 D 10.25 EDCBANN \ 172 3 C 8.0 EDCBANN \ 173 2 B 5.55 EDCBANN \ 174 1 A 5.4 EDCBANN \ 175 6 N NULL EDCBANN \ 176 7 N NULL EDCBANN \ 177] 178 179do_execsql_test 2.4 { 180 SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1 181 WINDOW w1 AS 182 (ORDER BY d DESC NULLS FIRST 183 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) 184 ORDER BY +d DESC NULLS FIRST, +a; 185} [list \ 186 6 N NULL NNEDCBA \ 187 7 N NULL NNEDCBA \ 188 5 E 10.26 NNEDCBA \ 189 4 D 10.25 NNEDCBA \ 190 3 C 8.0 NNEDCBA \ 191 2 B 5.55 NNEDCBA \ 192 1 A 5.4 NNEDCBA \ 193] 194 195do_execsql_test 2.5 { 196 SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1 197 WINDOW w1 AS 198 (ORDER BY d DESC NULLS LAST 199 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 200 ORDER BY +d DESC NULLS LAST, +a; 201} [list \ 202 5 E 10.26 E \ 203 4 D 10.25 ED \ 204 3 C 8.0 EDC \ 205 2 B 5.55 EDCB \ 206 1 A 5.4 EDCBA \ 207 6 N NULL EDCBANN \ 208 7 N NULL EDCBANN \ 209] 210 211do_execsql_test 2.6 { 212 SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1 213 WINDOW w1 AS 214 (ORDER BY d DESC NULLS FIRST 215 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 216 ORDER BY +d DESC NULLS FIRST, +a; 217} [list \ 218 6 N NULL NN \ 219 7 N NULL NN \ 220 5 E 10.26 NNE \ 221 4 D 10.25 NNED \ 222 3 C 8.0 NNEDC \ 223 2 B 5.55 NNEDCB \ 224 1 A 5.4 NNEDCBA \ 225] 226 227 228do_execsql_test 3.1 { 229 SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1 230 WINDOW w1 AS 231 (ORDER BY d DESC NULLS LAST 232 RANGE BETWEEN CURRENT ROW AND 2.25 FOLLOWING) 233 ORDER BY +d DESC NULLS LAST, +a; 234} [list \ 235 5 E 10.26 ED \ 236 4 D 10.25 DC \ 237 3 C 8.0 C \ 238 2 B 5.55 BA \ 239 1 A 5.4 A \ 240 6 N NULL NN \ 241 7 N NULL NN \ 242] 243 244do_execsql_test 3.2 { 245 SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1 246 WINDOW w1 AS 247 (ORDER BY d DESC NULLS FIRST 248 RANGE BETWEEN CURRENT ROW AND 2.25 FOLLOWING) 249 ORDER BY +d DESC NULLS FIRST, +a; 250} [list \ 251 6 N NULL NN \ 252 7 N NULL NN \ 253 5 E 10.26 ED \ 254 4 D 10.25 DC \ 255 3 C 8.0 C \ 256 2 B 5.55 BA \ 257 1 A 5.4 A \ 258] 259 260do_execsql_test 3.3 { 261 SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1 262 WINDOW w1 AS 263 (ORDER BY d DESC NULLS LAST 264 RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 265 ORDER BY +d DESC NULLS LAST, +a; 266} [list \ 267 5 E 10.26 EDCBANN \ 268 4 D 10.25 DCBANN \ 269 3 C 8.0 CBANN \ 270 2 B 5.55 BANN \ 271 1 A 5.4 ANN \ 272 6 N NULL NN \ 273 7 N NULL NN \ 274] 275 276do_execsql_test 3.4 { 277 SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1 278 WINDOW w1 AS 279 (ORDER BY d DESC NULLS FIRST 280 RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 281 ORDER BY +d DESC NULLS FIRST, +a; 282} [list \ 283 6 N NULL NNEDCBA \ 284 7 N NULL NNEDCBA \ 285 5 E 10.26 EDCBA \ 286 4 D 10.25 DCBA \ 287 3 C 8.0 CBA \ 288 2 B 5.55 BA \ 289 1 A 5.4 A \ 290] 291 292do_execsql_test 4.0 { 293 SELECT a, b, quote(d), group_concat(b,'') OVER w1 FROM t1 294 WINDOW w1 AS 295 (ORDER BY d DESC NULLS FIRST 296 RANGE BETWEEN 2.50 PRECEDING AND 0.5 PRECEDING) 297 ORDER BY +d DESC NULLS FIRST, +a; 298} [list \ 299 6 N NULL NN \ 300 7 N NULL NN \ 301 5 E 10.26 {} \ 302 4 D 10.25 {} \ 303 3 C 8.0 ED \ 304 2 B 5.55 C \ 305 1 A 5.4 {} \ 306] 307 308 309finish_test 310