test/window1.test

   1 # 2018 May 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
  14 set testdir [file dirname $argv0]
  15 source $testdir/tester.tcl
  16 set testprefix window1
  17
  18 ifcapable !windowfunc {
  19   finish_test
  20   return
  21 }
  22
  23 do_execsql_test 1.0 {
  24   CREATE TABLE t1(a, b, c, d);
  25   INSERT INTO t1 VALUES(1, 2, 3, 4);
  26   INSERT INTO t1 VALUES(5, 6, 7, 8);
  27   INSERT INTO t1 VALUES(9, 10, 11, 12);
  28 }
  29
  30 do_execsql_test 1.1 {
  31   SELECT sum(b) OVER () FROM t1
  32 } {18 18 18}
  33
  34 do_execsql_test 1.2 {
  35   SELECT a, sum(b) OVER () FROM t1
  36 } {1 18 5 18 9 18}
  37
  38 do_execsql_test 1.3 {
  39   SELECT a, 4 + sum(b) OVER () FROM t1
  40 } {1 22 5 22 9 22}
  41
  42 do_execsql_test 1.4 {
  43   SELECT a + 4 + sum(b) OVER () FROM t1
  44 } {23 27 31}
  45
  46 do_execsql_test 1.5 {
  47   SELECT a, sum(b) OVER (PARTITION BY c) FROM t1
  48 } {1 2 5 6 9 10}
  49
  50 foreach {tn sql} {
  51   1 "SELECT sum(b) OVER () FROM t1"
  52   2 "SELECT sum(b) OVER (PARTITION BY c) FROM t1"
  53   3 "SELECT sum(b) OVER (ORDER BY c) FROM t1"
  54   4 "SELECT sum(b) OVER (PARTITION BY d ORDER BY c) FROM t1"
  55   5 "SELECT sum(b) FILTER (WHERE a>0) OVER (PARTITION BY d ORDER BY c) FROM t1"
  56   6 "SELECT sum(b) OVER (ORDER BY c RANGE UNBOUNDED PRECEDING) FROM t1"
  57   7 "SELECT sum(b) OVER (ORDER BY c ROWS 45 PRECEDING) FROM t1"
  58   8 "SELECT sum(b) OVER (ORDER BY c RANGE CURRENT ROW) FROM t1"
  59   9 "SELECT sum(b) OVER (ORDER BY c RANGE BETWEEN UNBOUNDED PRECEDING
  60      AND CURRENT ROW) FROM t1"
  61  10 "SELECT sum(b) OVER (ORDER BY c ROWS BETWEEN UNBOUNDED PRECEDING
  62      AND UNBOUNDED FOLLOWING) FROM t1"
  63 } {
  64   do_test 2.$tn { lindex [catchsql $sql] 0 } 0
  65 }
  66
  67 foreach {tn sql} {
  68   1 "SELECT * FROM t1 WHERE sum(b) OVER ()"
  69   2 "SELECT * FROM t1 GROUP BY sum(b) OVER ()"
  70   3 "SELECT * FROM t1 GROUP BY a HAVING sum(b) OVER ()"
  71 } {
  72   do_catchsql_test 3.$tn $sql {1 {misuse of window function sum()}}
  73 }
  74
  75 do_execsql_test 4.0 {
  76   CREATE TABLE t2(a, b, c);
  77   INSERT INTO t2 VALUES(0, 0, 0);
  78   INSERT INTO t2 VALUES(1, 1, 1);
  79   INSERT INTO t2 VALUES(2, 0, 2);
  80   INSERT INTO t2 VALUES(3, 1, 0);
  81   INSERT INTO t2 VALUES(4, 0, 1);
  82   INSERT INTO t2 VALUES(5, 1, 2);
  83   INSERT INTO t2 VALUES(6, 0, 0);
  84 }
  85
  86 do_execsql_test 4.1 {
  87   SELECT a, sum(a) OVER (PARTITION BY b) FROM t2;
  88 } {
  89   0 12  2 12  4 12  6 12   1  9  3  9  5  9
  90 }
  91
  92 do_execsql_test 4.2 {
  93   SELECT a, sum(a) OVER (PARTITION BY b) FROM t2 ORDER BY a;
  94 } {
  95   0 12  1  9  2 12  3  9  4 12  5  9 6 12
  96 }
  97
  98 do_execsql_test 4.3 {
  99   SELECT a, sum(a) OVER () FROM t2 ORDER BY a;
 100 } {
 101   0 21  1  21  2 21  3  21  4 21  5  21 6 21
 102 }
 103
 104 do_execsql_test 4.4 {
 105   SELECT a, sum(a) OVER (ORDER BY a) FROM t2;
 106 } {
 107   0 0  1 1  2 3  3 6  4 10  5 15  6 21
 108 }
 109
 110 do_execsql_test 4.5 {
 111   SELECT a, sum(a) OVER (PARTITION BY b ORDER BY a) FROM t2 ORDER BY a
 112 } {
 113   0 0  1 1  2 2  3 4  4 6  5 9  6 12
 114 }
 115
 116 do_execsql_test 4.6 {
 117   SELECT a, sum(a) OVER (PARTITION BY c ORDER BY a) FROM t2 ORDER BY a
 118 } {
 119   0 0  1 1  2 2  3 3  4 5  5 7  6 9
 120 }
 121
 122 do_execsql_test 4.7 {
 123   SELECT a, sum(a) OVER (PARTITION BY b ORDER BY a DESC) FROM t2 ORDER BY a
 124 } {
 125   0 12  1 9  2 12  3 8  4 10  5 5  6 6
 126 }
 127
 128 do_execsql_test 4.8 {
 129   SELECT a,
 130     sum(a) OVER (PARTITION BY b ORDER BY a DESC),
 131     sum(a) OVER (PARTITION BY c ORDER BY a)
 132   FROM t2 ORDER BY a
 133 } {
 134   0  12  0
 135   1   9  1
 136   2  12  2
 137   3   8  3
 138   4  10  5
 139   5   5  7
 140   6   6  9
 141 }
 142
 143 do_execsql_test 4.9 {
 144   SELECT a,
 145     sum(a) OVER (ORDER BY a),
 146     avg(a) OVER (ORDER BY a)
 147   FROM t2 ORDER BY a
 148 } {
 149   0  0       0.0
 150   1  1       0.5
 151   2  3       1.0
 152   3  6       1.5
 153   4  10      2.0
 154   5  15      2.5
 155   6  21      3.0
 156 }
 157
 158 do_execsql_test 4.10.1 {
 159   SELECT a,
 160     count() OVER (ORDER BY a DESC),
 161     group_concat(a, '.') OVER (ORDER BY a DESC)
 162   FROM t2 ORDER BY a DESC
 163 } {
 164   6 1 6
 165   5 2 6.5
 166   4 3 6.5.4
 167   3 4 6.5.4.3
 168   2 5 6.5.4.3.2
 169   1 6 6.5.4.3.2.1
 170   0 7 6.5.4.3.2.1.0
 171 }
 172
 173 do_execsql_test 4.10.2 {
 174   SELECT a,
 175     count(*) OVER (ORDER BY a DESC),
 176     group_concat(a, '.') OVER (ORDER BY a DESC)
 177   FROM t2 ORDER BY a DESC
 178 } {
 179   6 1 6
 180   5 2 6.5
 181   4 3 6.5.4
 182   3 4 6.5.4.3
 183   2 5 6.5.4.3.2
 184   1 6 6.5.4.3.2.1
 185   0 7 6.5.4.3.2.1.0
 186 }
 187
 188 do_catchsql_test 5.1 {
 189   SELECT ntile(0) OVER (ORDER BY a) FROM t2;
 190 } {1 {argument of ntile must be a positive integer}}
 191 do_catchsql_test 5.2 {
 192   SELECT ntile(-1) OVER (ORDER BY a) FROM t2;
 193 } {1 {argument of ntile must be a positive integer}}
 194 do_catchsql_test 5.3 {
 195   SELECT ntile('zbc') OVER (ORDER BY a) FROM t2;
 196 } {1 {argument of ntile must be a positive integer}}
 197 do_execsql_test 5.4 {
 198   CREATE TABLE t4(a, b);
 199   SELECT ntile(1) OVER (ORDER BY a) FROM t4;
 200 } {}
 201
 202 #-------------------------------------------------------------------------
 203 reset_db
 204 do_execsql_test 6.1 {
 205   CREATE TABLE t1(x);
 206   INSERT INTO t1 VALUES(7), (6), (5), (4), (3), (2), (1);
 207
 208   CREATE TABLE t2(x);
 209   INSERT INTO t2 VALUES('b'), ('a');
 210
 211   SELECT x, count(*) OVER (ORDER BY x) FROM t1;
 212 } {1 1 2 2 3 3 4 4 5 5 6 6 7 7}
 213
 214 do_execsql_test 6.2 {
 215   SELECT * FROM t2, (SELECT x, count(*) OVER (ORDER BY x) FROM t1);
 216 } {
 217   b 1 1 b 2 2 b 3 3 b 4 4 b 5 5 b 6 6 b 7 7
 218   a 1 1 a 2 2 a 3 3 a 4 4 a 5 5 a 6 6 a 7 7
 219 }
 220
 221 do_catchsql_test 6.3 {
 222   SELECT x, lag(x) FILTER (WHERE (x%2)=0) OVER w FROM t1
 223   WINDOW w AS (ORDER BY x)
 224 } {1 {FILTER clause may only be used with aggregate window functions}}
 225
 226 #-------------------------------------------------------------------------
 227 # Attempt to use a window function as an aggregate. And other errors.
 228 #
 229 reset_db
 230 do_execsql_test 7.0 {
 231   CREATE TABLE t1(x, y);
 232   INSERT INTO t1 VALUES(1, 2);
 233   INSERT INTO t1 VALUES(3, 4);
 234   INSERT INTO t1 VALUES(5, 6);
 235   INSERT INTO t1 VALUES(7, 8);
 236   INSERT INTO t1 VALUES(9, 10);
 237 }
 238
 239 do_catchsql_test 7.1.1 {
 240   SELECT nth_value(x, 1) FROM t1;
 241 } {1 {misuse of window function nth_value()}}
 242 do_catchsql_test 7.1.2 {
 243   SELECT * FROM t1 WHERE nth_value(x, 1) OVER (ORDER BY y);
 244 } {1 {misuse of window function nth_value()}}
 245 do_catchsql_test 7.1.3 {
 246   SELECT count(*) FROM t1 GROUP BY y HAVING nth_value(x, 1) OVER (ORDER BY y);
 247 } {1 {misuse of window function nth_value()}}
 248 do_catchsql_test 7.1.4 {
 249   SELECT count(*) FROM t1 GROUP BY nth_value(x, 1) OVER (ORDER BY y);
 250 } {1 {misuse of window function nth_value()}}
 251 do_catchsql_test 7.1.5 {
 252   SELECT count(*) FROM t1 LIMIT nth_value(x, 1) OVER ();
 253 } {1 {no such column: x}}
 254 do_catchsql_test 7.1.6 {
 255   SELECT trim(x) OVER (ORDER BY y) FROM t1;
 256 } {1 {trim() may not be used as a window function}}
 257 do_catchsql_test 7.1.7 {
 258   SELECT max(x) OVER abc FROM t1 WINDOW def AS (ORDER BY y);
 259 } {1 {no such window: abc}}
 260
 261 do_execsql_test 7.2 {
 262   SELECT
 263     lead(y) OVER win,
 264     lead(y, 2) OVER win,
 265     lead(y, 3, 'default') OVER win
 266   FROM t1
 267   WINDOW win AS (ORDER BY x)
 268 } {
 269   4 6 8   6 8 10   8 10 default   10 {} default   {} {} default
 270 }
 271
 272 do_execsql_test 7.3 {
 273   SELECT row_number() OVER (ORDER BY x) FROM t1
 274 } {1 2 3 4 5}
 275
 276 do_execsql_test 7.4 {
 277   SELECT
 278     row_number() OVER win,
 279     lead(x) OVER win
 280   FROM t1
 281   WINDOW win AS (ORDER BY x ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
 282 } {1 3  2 5  3 7  4 9   5 {}}
 283
 284 #-------------------------------------------------------------------------
 285 # Attempt to use a window function in a view.
 286 #
 287 do_execsql_test 8.0 {
 288   CREATE TABLE t3(a, b, c);
 289
 290   WITH s(i) AS ( VALUES(1) UNION ALL SELECT i+1 FROM s WHERE i<6 )
 291   INSERT INTO t3 SELECT i, i, i FROM s;
 292
 293   CREATE VIEW v1 AS SELECT
 294     sum(b) OVER (ORDER BY c),
 295     min(b) OVER (ORDER BY c),
 296     max(b) OVER (ORDER BY c)
 297   FROM t3;
 298
 299   CREATE VIEW v2 AS SELECT
 300     sum(b) OVER win,
 301     min(b) OVER win,
 302     max(b) OVER win
 303   FROM t3
 304   WINDOW win AS (ORDER BY c);
 305 }
 306
 307 do_execsql_test 8.1.1 {
 308   SELECT * FROM v1
 309 } {1 1 1  3 1 2  6 1 3  10 1 4  15 1 5  21 1 6}
 310 do_execsql_test 8.1.2 {
 311   SELECT * FROM v2
 312 } {1 1 1  3 1 2  6 1 3  10 1 4  15 1 5  21 1 6}
 313
 314 db close
 315 sqlite3 db test.db
 316 do_execsql_test 8.2.1 {
 317   SELECT * FROM v1
 318 } {1 1 1  3 1 2  6 1 3  10 1 4  15 1 5  21 1 6}
 319 do_execsql_test 8.2.2 {
 320   SELECT * FROM v2
 321 } {1 1 1  3 1 2  6 1 3  10 1 4  15 1 5  21 1 6}
 322
 323 #-------------------------------------------------------------------------
 324 # Attempt to use a window function in a trigger.
 325 #
 326 do_execsql_test 9.0 {
 327   CREATE TABLE t4(x, y);
 328   INSERT INTO t4 VALUES(1, 'g');
 329   INSERT INTO t4 VALUES(2, 'i');
 330   INSERT INTO t4 VALUES(3, 'l');
 331   INSERT INTO t4 VALUES(4, 'g');
 332   INSERT INTO t4 VALUES(5, 'a');
 333
 334   CREATE TABLE t5(x, y, m);
 335   CREATE TRIGGER t4i AFTER INSERT ON t4 BEGIN
 336     DELETE FROM t5;
 337     INSERT INTO t5
 338       SELECT x, y, max(y) OVER xyz FROM t4
 339       WINDOW xyz AS (PARTITION BY (x%2) ORDER BY x);
 340   END;
 341 }
 342
 343 do_execsql_test 9.1.1 {
 344   SELECT x, y, max(y) OVER xyz FROM t4
 345       WINDOW xyz AS (PARTITION BY (x%2) ORDER BY x) ORDER BY 1
 346 } {1 g g   2 i i   3 l l   4 g i   5 a l}
 347
 348 do_execsql_test 9.1.2 {
 349   INSERT INTO t4 VALUES(6, 'm');
 350   SELECT x, y, max(y) OVER xyz FROM t4
 351       WINDOW xyz AS (PARTITION BY (x%2) ORDER BY x) ORDER BY 1
 352 } {1 g g   2 i i   3 l l   4 g i   5 a l   6 m m}
 353
 354 do_execsql_test 9.1.3 {
 355   SELECT * FROM t5 ORDER BY 1
 356 } {1 g g   2 i i   3 l l   4 g i   5 a l   6 m m}
 357
 358 do_execsql_test 9.2 {
 359   WITH aaa(x, y, z) AS (
 360     SELECT x, y, max(y) OVER xyz FROM t4
 361     WINDOW xyz AS (PARTITION BY (x%2) ORDER BY x)
 362   )
 363   SELECT * FROM aaa ORDER BY 1;
 364 } {1 g g   2 i i   3 l l   4 g i   5 a l   6 m m}
 365
 366 do_execsql_test 9.3 {
 367   WITH aaa(x, y, z) AS (
 368     SELECT x, y, max(y) OVER xyz FROM t4
 369     WINDOW xyz AS (ORDER BY x)
 370   )
 371   SELECT *, min(z) OVER (ORDER BY x) FROM aaa ORDER BY 1;
 372 } {1 g g g   2 i i g   3 l l g   4 g l g   5 a l g   6 m m g}
 373
 374 #-------------------------------------------------------------------------
 375 #
 376 do_execsql_test 10.0 {
 377   CREATE TABLE sales(emp TEXT PRIMARY KEY, region, total);
 378   INSERT INTO sales VALUES
 379       ('Alice',     'North', 34),
 380       ('Frank',     'South', 22),
 381       ('Charles',   'North', 45),
 382       ('Darrell',   'South', 8),
 383       ('Grant',     'South', 23),
 384       ('Brad' ,     'North', 22),
 385       ('Elizabeth', 'South', 99),
 386       ('Horace',    'East',   1);
 387 }
 388
 389 # Best two salespeople from each region
 390 #
 391 do_execsql_test 10.1 {
 392   SELECT emp, region, total FROM (
 393     SELECT
 394       emp, region, total,
 395       row_number() OVER (PARTITION BY region ORDER BY total DESC) AS rank
 396     FROM sales
 397   ) WHERE rank<=2 ORDER BY region, total DESC
 398 } {
 399   Horace      East     1
 400   Charles     North   45
 401   Alice       North   34
 402   Elizabeth   South   99
 403   Grant       South   23
 404 }
 405
 406 do_execsql_test 10.2 {
 407   SELECT emp, region, sum(total) OVER win FROM sales
 408   WINDOW win AS (PARTITION BY region ORDER BY total)
 409 } {
 410   Horace East       1
 411   Brad North       22
 412   Alice North      56
 413   Charles North   101
 414   Darrell South     8
 415   Frank South      30
 416   Grant South      53
 417   Elizabeth South 152
 418 }
 419
 420 do_execsql_test 10.3 {
 421   SELECT emp, region, sum(total) OVER win FROM sales
 422   WINDOW win AS (PARTITION BY region ORDER BY total)
 423   LIMIT 5
 424 } {
 425   Horace East       1
 426   Brad North       22
 427   Alice North      56
 428   Charles North   101
 429   Darrell South     8
 430 }
 431
 432 do_execsql_test 10.4 {
 433   SELECT emp, region, sum(total) OVER win FROM sales
 434   WINDOW win AS (PARTITION BY region ORDER BY total)
 435   LIMIT 5 OFFSET 2
 436 } {
 437   Alice North      56
 438   Charles North   101
 439   Darrell South     8
 440   Frank South      30
 441   Grant South      53
 442 }
 443
 444 do_execsql_test 10.5 {
 445   SELECT emp, region, sum(total) OVER win FROM sales
 446   WINDOW win AS (
 447     PARTITION BY region ORDER BY total
 448     ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
 449   )
 450 } {
 451   Horace East       1
 452   Brad North      101
 453   Alice North      79
 454   Charles North    45
 455   Darrell South   152
 456   Frank South     144
 457   Grant South     122
 458   Elizabeth South  99
 459 }
 460
 461 do_execsql_test 10.6 {
 462   SELECT emp, region, sum(total) OVER win FROM sales
 463   WINDOW win AS (
 464     PARTITION BY region ORDER BY total
 465     ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
 466   ) LIMIT 5 OFFSET 2
 467 } {
 468   Alice North      79
 469   Charles North    45
 470   Darrell South   152
 471   Frank South     144
 472   Grant South     122
 473 }
 474
 475 do_execsql_test 10.7 {
 476   SELECT emp, region, (
 477     SELECT sum(total) OVER (
 478       ORDER BY total RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
 479     ) || outer.emp FROM sales
 480   ) FROM sales AS outer;
 481 } {
 482   Alice North 254Alice
 483   Frank South 254Frank
 484   Charles North 254Charles
 485   Darrell South 254Darrell
 486   Grant South 254Grant
 487   Brad North 254Brad
 488   Elizabeth South 254Elizabeth
 489   Horace East 254Horace
 490 }
 491
 492 do_execsql_test 10.8 {
 493   SELECT emp, region, (
 494     SELECT sum(total) FILTER (WHERE sales.emp!=outer.emp) OVER (
 495       ORDER BY total RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
 496     ) FROM sales
 497   ) FROM sales AS outer;
 498 } {
 499   Alice North 220
 500   Frank South 232
 501   Charles North 209
 502   Darrell South 246
 503   Grant South 231
 504   Brad North 232
 505   Elizabeth South 155
 506   Horace East 253
 507 }
 508
 509 #-------------------------------------------------------------------------
 510 # Check that it is not possible to use a window function in a CREATE INDEX
 511 # statement.
 512 #
 513 do_execsql_test 11.0 { CREATE TABLE t6(a, b, c); }
 514
 515 do_catchsql_test 11.1 {
 516   CREATE INDEX t6i ON t6(a) WHERE sum(b) OVER ();
 517 } {1 {misuse of window function sum()}}
 518 do_catchsql_test 11.2 {
 519   CREATE INDEX t6i ON t6(a) WHERE lead(b) OVER ();
 520 } {1 {misuse of window function lead()}}
 521
 522 do_catchsql_test 11.3 {
 523   CREATE INDEX t6i ON t6(sum(b) OVER ());
 524 } {1 {misuse of window function sum()}}
 525 do_catchsql_test 11.4 {
 526   CREATE INDEX t6i ON t6(lead(b) OVER ());
 527 } {1 {misuse of window function lead()}}
 528
 529 finish_test
 530