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 (ORDER BY y);
 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 finish_test
 407