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 do_catchsql_test 7.1.8 {
 261   SELECT row_number(x) OVER () FROM t1
 262 } {1 {wrong number of arguments to function row_number()}}
 263
 264 do_execsql_test 7.2 {
 265   SELECT
 266     lead(y) OVER win,
 267     lead(y, 2) OVER win,
 268     lead(y, 3, 'default') OVER win
 269   FROM t1
 270   WINDOW win AS (ORDER BY x)
 271 } {
 272   4 6 8   6 8 10   8 10 default   10 {} default   {} {} default
 273 }
 274
 275 do_execsql_test 7.3 {
 276   SELECT row_number() OVER (ORDER BY x) FROM t1
 277 } {1 2 3 4 5}
 278
 279 do_execsql_test 7.4 {
 280   SELECT
 281     row_number() OVER win,
 282     lead(x) OVER win
 283   FROM t1
 284   WINDOW win AS (ORDER BY x ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
 285 } {1 3  2 5  3 7  4 9   5 {}}
 286
 287 #-------------------------------------------------------------------------
 288 # Attempt to use a window function in a view.
 289 #
 290 do_execsql_test 8.0 {
 291   CREATE TABLE t3(a, b, c);
 292
 293   WITH s(i) AS ( VALUES(1) UNION ALL SELECT i+1 FROM s WHERE i<6 )
 294   INSERT INTO t3 SELECT i, i, i FROM s;
 295
 296   CREATE VIEW v1 AS SELECT
 297     sum(b) OVER (ORDER BY c),
 298     min(b) OVER (ORDER BY c),
 299     max(b) OVER (ORDER BY c)
 300   FROM t3;
 301
 302   CREATE VIEW v2 AS SELECT
 303     sum(b) OVER win,
 304     min(b) OVER win,
 305     max(b) OVER win
 306   FROM t3
 307   WINDOW win AS (ORDER BY c);
 308 }
 309
 310 do_execsql_test 8.1.1 {
 311   SELECT * FROM v1
 312 } {1 1 1  3 1 2  6 1 3  10 1 4  15 1 5  21 1 6}
 313 do_execsql_test 8.1.2 {
 314   SELECT * FROM v2
 315 } {1 1 1  3 1 2  6 1 3  10 1 4  15 1 5  21 1 6}
 316
 317 db close
 318 sqlite3 db test.db
 319 do_execsql_test 8.2.1 {
 320   SELECT * FROM v1
 321 } {1 1 1  3 1 2  6 1 3  10 1 4  15 1 5  21 1 6}
 322 do_execsql_test 8.2.2 {
 323   SELECT * FROM v2
 324 } {1 1 1  3 1 2  6 1 3  10 1 4  15 1 5  21 1 6}
 325
 326 #-------------------------------------------------------------------------
 327 # Attempt to use a window function in a trigger.
 328 #
 329 do_execsql_test 9.0 {
 330   CREATE TABLE t4(x, y);
 331   INSERT INTO t4 VALUES(1, 'g');
 332   INSERT INTO t4 VALUES(2, 'i');
 333   INSERT INTO t4 VALUES(3, 'l');
 334   INSERT INTO t4 VALUES(4, 'g');
 335   INSERT INTO t4 VALUES(5, 'a');
 336
 337   CREATE TABLE t5(x, y, m);
 338   CREATE TRIGGER t4i AFTER INSERT ON t4 BEGIN
 339     DELETE FROM t5;
 340     INSERT INTO t5
 341       SELECT x, y, max(y) OVER xyz FROM t4
 342       WINDOW xyz AS (PARTITION BY (x%2) ORDER BY x);
 343   END;
 344 }
 345
 346 do_execsql_test 9.1.1 {
 347   SELECT x, y, max(y) OVER xyz FROM t4
 348       WINDOW xyz AS (PARTITION BY (x%2) ORDER BY x) ORDER BY 1
 349 } {1 g g   2 i i   3 l l   4 g i   5 a l}
 350
 351 do_execsql_test 9.1.2 {
 352   INSERT INTO t4 VALUES(6, 'm');
 353   SELECT x, y, max(y) OVER xyz FROM t4
 354       WINDOW xyz AS (PARTITION BY (x%2) ORDER BY x) ORDER BY 1
 355 } {1 g g   2 i i   3 l l   4 g i   5 a l   6 m m}
 356
 357 do_execsql_test 9.1.3 {
 358   SELECT * FROM t5 ORDER BY 1
 359 } {1 g g   2 i i   3 l l   4 g i   5 a l   6 m m}
 360
 361 do_execsql_test 9.2 {
 362   WITH aaa(x, y, z) AS (
 363     SELECT x, y, max(y) OVER xyz FROM t4
 364     WINDOW xyz AS (PARTITION BY (x%2) ORDER BY x)
 365   )
 366   SELECT * FROM aaa ORDER BY 1;
 367 } {1 g g   2 i i   3 l l   4 g i   5 a l   6 m m}
 368
 369 do_execsql_test 9.3 {
 370   WITH aaa(x, y, z) AS (
 371     SELECT x, y, max(y) OVER xyz FROM t4
 372     WINDOW xyz AS (ORDER BY x)
 373   )
 374   SELECT *, min(z) OVER (ORDER BY x) FROM aaa ORDER BY 1;
 375 } {1 g g g   2 i i g   3 l l g   4 g l g   5 a l g   6 m m g}
 376
 377 do_catchsql_test 9.4 {
 378   -- 2021-04-17 dbsqlfuzz d9cf66100064952b66951845dfab41de1c124611
 379   DROP TABLE IF EXISTS t1;
 380   CREATE TABLE t1(a,b,c,d);
 381   DROP TABLE IF EXISTS t2;
 382   CREATE TABLE t2(x,y);
 383   CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN
 384     INSERT INTO t2(x,y)
 385       SELECT a, max(d) OVER w1 FROM t1
 386         WINDOW w1 AS (PARTITION BY EXISTS(SELECT 1 FROM t1 WHERE c=?1) );
 387   END;
 388 } {1 {trigger cannot use variables}}
 389
 390 do_catchsql_test 9.4.2 {
 391   CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN
 392     INSERT INTO t1(a,b)
 393         SELECT a, max(d) OVER w1 FROM t1
 394         WINDOW w1 AS (
 395           ORDER BY a ROWS BETWEEN ? PRECEDING AND UNBOUNDED FOLLOWING
 396         );
 397   END;
 398 } {1 {trigger cannot use variables}}
 399 do_catchsql_test 9.4.3 {
 400   CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN
 401     INSERT INTO t1(a,b)
 402         SELECT a, max(d) OVER w1 FROM t1
 403         WINDOW w1 AS (
 404           ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND ? FOLLOWING
 405         );
 406   END;
 407 } {1 {trigger cannot use variables}}
 408
 409 #-------------------------------------------------------------------------
 410 #
 411 do_execsql_test 10.0 {
 412   CREATE TABLE sales(emp TEXT PRIMARY KEY, region, total);
 413   INSERT INTO sales VALUES
 414       ('Alice',     'North', 34),
 415       ('Frank',     'South', 22),
 416       ('Charles',   'North', 45),
 417       ('Darrell',   'South', 8),
 418       ('Grant',     'South', 23),
 419       ('Brad' ,     'North', 22),
 420       ('Elizabeth', 'South', 99),
 421       ('Horace',    'East',   1);
 422 }
 423
 424 # Best two salespeople from each region
 425 #
 426 do_execsql_test 10.1 {
 427   SELECT emp, region, total FROM (
 428     SELECT
 429       emp, region, total,
 430       row_number() OVER (PARTITION BY region ORDER BY total DESC) AS rank
 431     FROM sales
 432   ) WHERE rank<=2 ORDER BY region, total DESC
 433 } {
 434   Horace      East     1
 435   Charles     North   45
 436   Alice       North   34
 437   Elizabeth   South   99
 438   Grant       South   23
 439 }
 440
 441 do_execsql_test 10.2 {
 442   SELECT emp, region, sum(total) OVER win FROM sales
 443   WINDOW win AS (PARTITION BY region ORDER BY total)
 444 } {
 445   Horace East       1
 446   Brad North       22
 447   Alice North      56
 448   Charles North   101
 449   Darrell South     8
 450   Frank South      30
 451   Grant South      53
 452   Elizabeth South 152
 453 }
 454
 455 do_execsql_test 10.3 {
 456   SELECT emp, region, sum(total) OVER win FROM sales
 457   WINDOW win AS (PARTITION BY region ORDER BY total)
 458   LIMIT 5
 459 } {
 460   Horace East       1
 461   Brad North       22
 462   Alice North      56
 463   Charles North   101
 464   Darrell South     8
 465 }
 466
 467 do_execsql_test 10.4 {
 468   SELECT emp, region, sum(total) OVER win FROM sales
 469   WINDOW win AS (PARTITION BY region ORDER BY total)
 470   LIMIT 5 OFFSET 2
 471 } {
 472   Alice North      56
 473   Charles North   101
 474   Darrell South     8
 475   Frank South      30
 476   Grant South      53
 477 }
 478
 479 do_execsql_test 10.5 {
 480   SELECT emp, region, sum(total) OVER win FROM sales
 481   WINDOW win AS (
 482     PARTITION BY region ORDER BY total
 483     ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
 484   )
 485 } {
 486   Horace East       1
 487   Brad North      101
 488   Alice North      79
 489   Charles North    45
 490   Darrell South   152
 491   Frank South     144
 492   Grant South     122
 493   Elizabeth South  99
 494 }
 495
 496 do_execsql_test 10.6 {
 497   SELECT emp, region, sum(total) OVER win FROM sales
 498   WINDOW win AS (
 499     PARTITION BY region ORDER BY total
 500     ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
 501   ) LIMIT 5 OFFSET 2
 502 } {
 503   Alice North      79
 504   Charles North    45
 505   Darrell South   152
 506   Frank South     144
 507   Grant South     122
 508 }
 509
 510 do_execsql_test 10.7 {
 511   SELECT emp, region, (
 512     SELECT sum(total) OVER (
 513       ORDER BY total RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
 514     ) || outer.emp FROM sales
 515   ) FROM sales AS outer;
 516 } {
 517   Alice North 254Alice
 518   Frank South 254Frank
 519   Charles North 254Charles
 520   Darrell South 254Darrell
 521   Grant South 254Grant
 522   Brad North 254Brad
 523   Elizabeth South 254Elizabeth
 524   Horace East 254Horace
 525 }
 526
 527 do_execsql_test 10.8 {
 528   SELECT emp, region, (
 529     SELECT sum(total) FILTER (WHERE sales.emp!=outer.emp) OVER (
 530       ORDER BY total RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
 531     ) FROM sales
 532   ) FROM sales AS outer;
 533 } {
 534   Alice North 220
 535   Frank South 232
 536   Charles North 209
 537   Darrell South 246
 538   Grant South 231
 539   Brad North 232
 540   Elizabeth South 155
 541   Horace East 253
 542 }
 543
 544 #-------------------------------------------------------------------------
 545 # Check that it is not possible to use a window function in a CREATE INDEX
 546 # statement.
 547 #
 548 do_execsql_test 11.0 { CREATE TABLE t6(a, b, c); }
 549
 550 do_catchsql_test 11.1 {
 551   CREATE INDEX t6i ON t6(a) WHERE sum(b) OVER ();
 552 } {1 {misuse of window function sum()}}
 553 do_catchsql_test 11.2 {
 554   CREATE INDEX t6i ON t6(a) WHERE lead(b) OVER ();
 555 } {1 {misuse of window function lead()}}
 556
 557 do_catchsql_test 11.3 {
 558   CREATE INDEX t6i ON t6(sum(b) OVER ());
 559 } {1 {misuse of window function sum()}}
 560 do_catchsql_test 11.4 {
 561   CREATE INDEX t6i ON t6(lead(b) OVER ());
 562 } {1 {misuse of window function lead()}}
 563
 564 # 2018-09-17 ticket 510cde277783b5fb5de628393959849dff377eb3
 565 # Endless loop on a query with window functions and a limit
 566 #
 567 do_execsql_test 12.100 {
 568   DROP TABLE IF EXISTS t1;
 569   CREATE TABLE t1(id INT, b VARCHAR, c VARCHAR);
 570   INSERT INTO t1 VALUES(1, 'A', 'one');
 571   INSERT INTO t1 VALUES(2, 'B', 'two');
 572   INSERT INTO t1 VALUES(3, 'C', 'three');
 573   INSERT INTO t1 VALUES(4, 'D', 'one');
 574   INSERT INTO t1 VALUES(5, 'E', 'two');
 575   SELECT id, b, lead(c,1) OVER(ORDER BY c) AS x
 576     FROM t1 WHERE id>1
 577    ORDER BY b LIMIT 1;
 578 } {2 B two}
 579 do_execsql_test 12.110 {
 580   INSERT INTO t1 VALUES(6, 'F', 'three');
 581   INSERT INTO t1 VALUES(7, 'G', 'one');
 582   SELECT id, b, lead(c,1) OVER(ORDER BY c) AS x
 583     FROM t1 WHERE id>1
 584    ORDER BY b LIMIT 2;
 585 } {2 B two 3 C three}
 586
 587 #-------------------------------------------------------------------------
 588
 589 do_execsql_test 13.1 {
 590   DROP TABLE IF EXISTS t1;
 591   CREATE TABLE t1(a int, b int);
 592   INSERT INTO t1 VALUES(1,11);
 593   INSERT INTO t1 VALUES(2,12);
 594 }
 595
 596 do_execsql_test 13.2.1 {
 597   SELECT a, rank() OVER(ORDER BY b) FROM t1;
 598   SELECT a, rank() OVER(ORDER BY b DESC) FROM t1;
 599 } {
 600   1 1   2 2   2 1   1 2
 601 }
 602 do_execsql_test 13.2.2 {
 603   SELECT a, rank() OVER(ORDER BY b) FROM t1
 604     UNION ALL
 605   SELECT a, rank() OVER(ORDER BY b DESC) FROM t1;
 606 } {
 607   1 1   2 2   2 1   1 2
 608 }
 609 do_execsql_test 13.3 {
 610   SELECT a, rank() OVER(ORDER BY b) FROM t1
 611     UNION
 612   SELECT a, rank() OVER(ORDER BY b DESC) FROM t1;
 613 } {
 614   1 1   1 2   2 1   2 2
 615 }
 616
 617 do_execsql_test 13.4 {
 618   SELECT a, rank() OVER(ORDER BY b) FROM t1
 619     EXCEPT
 620   SELECT a, rank() OVER(ORDER BY b DESC) FROM t1;
 621 } {
 622   1 1   2 2
 623 }
 624
 625 do_execsql_test 13.5 {
 626   SELECT a, rank() OVER(ORDER BY b) FROM t1
 627     INTERSECT
 628   SELECT a, rank() OVER(ORDER BY b DESC) FROM t1;
 629 } {}
 630
 631 # 2018-12-06
 632 # https://www.sqlite.org/src/info/f09fcd17810f65f7
 633 # Assertion fault when window functions are used.
 634 #
 635 # Root cause is the query flattener invoking sqlite3ExprDup() on
 636 # expressions that contain subqueries with window functions.  The
 637 # sqlite3ExprDup() routine is not making correctly initializing
 638 # Select.pWin field of the subqueries.
 639 #
 640 sqlite3 db :memory:
 641 do_execsql_test 14.0 {
 642   SELECT * FROM(
 643     SELECT * FROM (SELECT 1 AS c) WHERE c IN (
 644         SELECT (row_number() OVER()) FROM (VALUES (0))
 645     )
 646   );
 647 } {1}
 648 do_execsql_test 14.1 {
 649   CREATE TABLE t1(x); INSERT INTO t1(x) VALUES(12345);
 650   CREATE TABLE t2(c); INSERT INTO t2(c) VALUES(1);
 651   SELECT y, y+1, y+2 FROM (
 652     SELECT c IN (
 653       SELECT (row_number() OVER()) FROM t1
 654     ) AS y FROM t2
 655   );
 656 } {1 2 3}
 657
 658 # 2018-12-31
 659 # https://www.sqlite.org/src/info/d0866b26f83e9c55
 660 # Window function in correlated subquery causes assertion fault
 661 #
 662 do_catchsql_test 15.0 {
 663   WITH t(id, parent) AS (
 664   SELECT CAST(1 AS INT), CAST(NULL AS INT)
 665   UNION ALL
 666   SELECT 2, NULL
 667   UNION ALL
 668   SELECT 3, 1
 669   UNION ALL
 670   SELECT 4, 1
 671   UNION ALL
 672   SELECT 5, 2
 673   UNION ALL
 674   SELECT 6, 2
 675   ), q AS (
 676   SELECT t.*, ROW_NUMBER() OVER (ORDER BY t.id) AS rn
 677     FROM t
 678    WHERE parent IS NULL
 679    UNION ALL
 680   SELECT t.*, ROW_NUMBER() OVER (ORDER BY t.id) AS rn
 681     FROM q
 682     JOIN t
 683       ON t.parent = q.id
 684   )
 685   SELECT *
 686     FROM q;
 687 } {1 {cannot use window functions in recursive queries}}
 688 do_execsql_test 15.1 {
 689   DROP TABLE IF EXISTS t1;
 690   DROP TABLE IF EXISTS t2;
 691   CREATE TABLE t1(x);
 692   INSERT INTO t1 VALUES('a'), ('b'), ('c');
 693   CREATE TABLE t2(a, b);
 694   INSERT INTO t2 VALUES('X', 1), ('X', 2), ('Y', 2), ('Y', 3);
 695   SELECT x, (
 696     SELECT sum(b)
 697       OVER (PARTITION BY a ROWS BETWEEN UNBOUNDED PRECEDING
 698                                     AND UNBOUNDED FOLLOWING)
 699     FROM t2 WHERE b<x
 700   ) FROM t1;
 701 } {a 3 b 3 c 3}
 702
 703 do_execsql_test 15.2 {
 704   SELECT(
 705     WITH c AS(
 706       VALUES(1)
 707     ) SELECT '' FROM c,c
 708   ) x WHERE x+x;
 709 } {}
 710
 711 #-------------------------------------------------------------------------
 712
 713 do_execsql_test 16.0 {
 714   CREATE TABLE t7(a,b);
 715   INSERT INTO t7(rowid, a, b) VALUES
 716       (1, 1, 3),
 717       (2, 10, 4),
 718       (3, 100, 2);
 719 }
 720
 721 do_execsql_test 16.1 {
 722   SELECT rowid, sum(a) OVER (PARTITION BY b IN (SELECT rowid FROM t7)) FROM t7;
 723 } {
 724   2 10
 725   1 101
 726   3 101
 727 }
 728
 729 do_execsql_test 16.2 {
 730   SELECT rowid, sum(a) OVER w1 FROM t7
 731   WINDOW w1 AS (PARTITION BY b IN (SELECT rowid FROM t7));
 732 } {
 733   2 10
 734   1 101
 735   3 101
 736 }
 737
 738 #-------------------------------------------------------------------------
 739 do_execsql_test 17.0 {
 740   CREATE TABLE t8(a);
 741   INSERT INTO t8 VALUES(1), (2), (3);
 742 }
 743
 744 do_execsql_test 17.1 {
 745   SELECT +sum(0) OVER () ORDER BY +sum(0) OVER ();
 746 } {0}
 747
 748 do_execsql_test 17.2 {
 749   select +sum(a) OVER () FROM t8 ORDER BY +sum(a) OVER () DESC;
 750 } {6 6 6}
 751
 752 do_execsql_test 17.3 {
 753   SELECT 10+sum(a) OVER (ORDER BY a)
 754   FROM t8
 755   ORDER BY 10+sum(a) OVER (ORDER BY a) DESC;
 756 } {16 13 11}
 757
 758
 759 #-------------------------------------------------------------------------
 760 # Test error cases from chaining window definitions.
 761 #
 762 reset_db
 763 do_execsql_test 18.0 {
 764   DROP TABLE IF EXISTS t1;
 765   CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c TEXT, d INTEGER);
 766   INSERT INTO t1 VALUES(1, 'odd',  'one',   1);
 767   INSERT INTO t1 VALUES(2, 'even', 'two',   2);
 768   INSERT INTO t1 VALUES(3, 'odd',  'three', 3);
 769   INSERT INTO t1 VALUES(4, 'even', 'four',  4);
 770   INSERT INTO t1 VALUES(5, 'odd',  'five',  5);
 771   INSERT INTO t1 VALUES(6, 'even', 'six',   6);
 772 }
 773
 774 foreach {tn sql error} {
 775   1 {
 776     SELECT c, sum(d) OVER win2 FROM t1
 777       WINDOW win1 AS (ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING),
 778              win2 AS (win1 ORDER BY b)
 779   } {cannot override frame specification of window: win1}
 780
 781   2 {
 782     SELECT c, sum(d) OVER win2 FROM t1
 783       WINDOW win1 AS (),
 784              win2 AS (win4 ORDER BY b)
 785   } {no such window: win4}
 786
 787   3 {
 788     SELECT c, sum(d) OVER win2 FROM t1
 789       WINDOW win1 AS (),
 790              win2 AS (win1 PARTITION BY d)
 791   } {cannot override PARTITION clause of window: win1}
 792
 793   4 {
 794     SELECT c, sum(d) OVER win2 FROM t1
 795       WINDOW win1 AS (ORDER BY b),
 796              win2 AS (win1 ORDER BY d)
 797   } {cannot override ORDER BY clause of window: win1}
 798 } {
 799   do_catchsql_test 18.1.$tn $sql [list 1 $error]
 800 }
 801
 802 foreach {tn sql error} {
 803   1 {
 804     SELECT c, sum(d) OVER (win1 ORDER BY b) FROM t1
 805       WINDOW win1 AS (ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
 806   } {cannot override frame specification of window: win1}
 807
 808   2 {
 809     SELECT c, sum(d) OVER (win4 ORDER BY b) FROM t1
 810       WINDOW win1 AS ()
 811   } {no such window: win4}
 812
 813   3 {
 814     SELECT c, sum(d) OVER (win1 PARTITION BY d) FROM t1
 815       WINDOW win1 AS ()
 816   } {cannot override PARTITION clause of window: win1}
 817
 818   4 {
 819     SELECT c, sum(d) OVER (win1 ORDER BY d) FROM t1
 820       WINDOW win1 AS (ORDER BY b)
 821   } {cannot override ORDER BY clause of window: win1}
 822 } {
 823   do_catchsql_test 18.2.$tn $sql [list 1 $error]
 824 }
 825
 826 do_execsql_test 18.3.1 {
 827   SELECT group_concat(c, '.') OVER (PARTITION BY b ORDER BY c)
 828   FROM t1
 829 } {four four.six four.six.two five five.one five.one.three}
 830
 831 do_execsql_test 18.3.2 {
 832   SELECT group_concat(c, '.') OVER (win1 ORDER BY c)
 833   FROM t1
 834   WINDOW win1 AS (PARTITION BY b)
 835 } {four four.six four.six.two five five.one five.one.three}
 836
 837 do_execsql_test 18.3.3 {
 838   SELECT group_concat(c, '.') OVER win2
 839   FROM t1
 840   WINDOW win1 AS (PARTITION BY b),
 841          win2 AS (win1 ORDER BY c)
 842 } {four four.six four.six.two five five.one five.one.three}
 843
 844 do_execsql_test 18.3.4 {
 845   SELECT group_concat(c, '.') OVER (win2)
 846   FROM t1
 847   WINDOW win1 AS (PARTITION BY b),
 848          win2 AS (win1 ORDER BY c)
 849 } {four four.six four.six.two five five.one five.one.three}
 850
 851 do_execsql_test 18.3.5 {
 852   SELECT group_concat(c, '.') OVER win5
 853   FROM t1
 854   WINDOW win1 AS (PARTITION BY b),
 855          win2 AS (win1),
 856          win3 AS (win2),
 857          win4 AS (win3),
 858          win5 AS (win4 ORDER BY c)
 859 } {four four.six four.six.two five five.one five.one.three}
 860
 861 #-------------------------------------------------------------------------
 862 # Test RANGE <expr> PRECEDING/FOLLOWING when there are string, blob
 863 # and NULL values in the dataset.
 864 #
 865 reset_db
 866 do_execsql_test 19.0 {
 867   CREATE TABLE t1(a, b);
 868   INSERT INTO t1 VALUES
 869     (1, 1), (2, 2), (3, 3), (4, 4), (5, 5),
 870     ('a', 6), ('b', 7), ('c', 8), ('d', 9), ('e', 10);
 871 }
 872 do_execsql_test 19.1 {
 873   SELECT a, sum(b) OVER (ORDER BY a) FROM t1;
 874 } {1 1  2 3  3 6  4 10  5 15  a 21 b 28 c 36 d 45 e 55}
 875
 876 do_execsql_test 19.2.1 {
 877   SELECT a, sum(b) OVER (
 878     ORDER BY a RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING
 879   ) FROM t1;
 880 } {1 3 2 6 3 9 4 12 5 9 a 6 b 7 c 8 d 9 e 10}
 881 do_execsql_test 19.2.2 {
 882   SELECT a, sum(b) OVER (
 883     ORDER BY a DESC RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING
 884   ) FROM t1 ORDER BY a ASC;
 885 } {1 3 2 6 3 9 4 12 5 9 a 6 b 7 c 8 d 9 e 10}
 886
 887 do_execsql_test 19.3.1 {
 888   SELECT a, sum(b) OVER (
 889     ORDER BY a RANGE BETWEEN 2 PRECEDING AND 1 FOLLOWING
 890   ) FROM t1;
 891 } {1 3 2 6 3 10 4 14 5 12 a 6 b 7 c 8 d 9 e 10}
 892 do_execsql_test 19.3.2 {
 893   SELECT a, sum(b) OVER (
 894     ORDER BY a DESC RANGE BETWEEN 1 PRECEDING AND 2 FOLLOWING
 895   ) FROM t1 ORDER BY a ASC;
 896 } {1 3 2 6 3 10 4 14 5 12 a 6 b 7 c 8 d 9 e 10}
 897
 898
 899 reset_db
 900 do_execsql_test 20.0 {
 901   CREATE TABLE t1(a, b);
 902   INSERT INTO t1 VALUES
 903     (NULL, 100), (NULL, 100),
 904     (1, 1), (2, 2), (3, 3), (4, 4), (5, 5),
 905     ('a', 6), ('b', 7), ('c', 8), ('d', 9), ('e', 10);
 906 }
 907 do_execsql_test 20.1 {
 908   SELECT a, sum(b) OVER (ORDER BY a) FROM t1;
 909 } {
 910   {} 200 {} 200 1 201  2 203  3 206  4 210  5 215
 911   a 221 b 228 c 236 d 245 e 255
 912 }
 913
 914 do_execsql_test 20.2.1 {
 915   SELECT a, sum(b) OVER (
 916     ORDER BY a RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING
 917   ) FROM t1;
 918 } {{} 200 {} 200 1 3 2 6 3 9 4 12 5 9 a 6 b 7 c 8 d 9 e 10}
 919 do_execsql_test 20.2.2 {
 920   SELECT a, sum(b) OVER (
 921     ORDER BY a DESC RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING
 922   ) FROM t1 ORDER BY a ASC;
 923 } {{} 200 {} 200 1 3 2 6 3 9 4 12 5 9 a 6 b 7 c 8 d 9 e 10}
 924
 925 do_execsql_test 20.3.1 {
 926   SELECT a, sum(b) OVER (
 927     ORDER BY a RANGE BETWEEN 2 PRECEDING AND 1 FOLLOWING
 928   ) FROM t1;
 929 } {{} 200 {} 200 1 3 2 6 3 10 4 14 5 12 a 6 b 7 c 8 d 9 e 10}
 930 do_execsql_test 20.3.2 {
 931   SELECT a, sum(b) OVER (
 932     ORDER BY a DESC RANGE BETWEEN 1 PRECEDING AND 2 FOLLOWING
 933   ) FROM t1 ORDER BY a ASC;
 934 } {{} 200 {} 200 1 3 2 6 3 10 4 14 5 12 a 6 b 7 c 8 d 9 e 10}
 935
 936 #-------------------------------------------------------------------------
 937 do_execsql_test 21.0 {
 938   CREATE TABLE keyword_tab(
 939     current, exclude, filter, following, groups, no, others, over,
 940     partition, preceding, range, ties, unbounded, window
 941   );
 942 }
 943 do_execsql_test 21.1 {
 944   SELECT
 945     current, exclude, filter, following, groups, no, others, over,
 946     partition, preceding, range, ties, unbounded, window
 947   FROM keyword_tab
 948 }
 949
 950 #-------------------------------------------------------------------------
 951 foreach {tn expr err} {
 952   1   4.5      0
 953   2   NULL     1
 954   3   0.0      0
 955   4   0.1      0
 956   5  -0.1      1
 957   6  ''        1
 958   7  '2.0'     0
 959   8  '2.0x'    1
 960   9  x'1234'   1
 961  10  '1.2'     0
 962 } {
 963   set res {0 1}
 964   if {$err} {set res {1 {frame starting offset must be a non-negative number}} }
 965   do_catchsql_test 22.$tn.1 "
 966     WITH a(x, y) AS ( VALUES(1, 2) )
 967     SELECT sum(x) OVER (
 968       ORDER BY y RANGE BETWEEN $expr PRECEDING AND UNBOUNDED FOLLOWING
 969     ) FROM a
 970   " $res
 971
 972   set res {0 1}
 973   if {$err} {set res {1 {frame ending offset must be a non-negative number}} }
 974   do_catchsql_test 22.$tn.2 "
 975     WITH a(x, y) AS ( VALUES(1, 2) )
 976     SELECT sum(x) OVER (
 977       ORDER BY y RANGE BETWEEN UNBOUNDED PRECEDING AND $expr FOLLOWING
 978     ) FROM a
 979   " $res
 980 }
 981
 982 #-------------------------------------------------------------------------
 983 reset_db
 984 do_execsql_test 23.0 {
 985   CREATE TABLE t5(a, b, c);
 986   CREATE INDEX t5ab ON t5(a, b);
 987 }
 988
 989 proc do_ordercount_test {tn sql nOrderBy} {
 990   set plan [execsql "EXPLAIN QUERY PLAN $sql"]
 991   uplevel [list do_test $tn [list regexp -all ORDER $plan] $nOrderBy]
 992 }
 993
 994 do_ordercount_test 23.1 {
 995   SELECT
 996     sum(c) OVER (ORDER BY a, b),
 997     sum(c) OVER (PARTITION BY a ORDER BY b)
 998   FROM t5
 999 } 0
1000
1001 do_ordercount_test 23.2 {
1002   SELECT
1003     sum(c) OVER (ORDER BY b, a),
1004     sum(c) OVER (PARTITION BY b ORDER BY a)
1005   FROM t5
1006 } 1
1007
1008 do_ordercount_test 23.3 {
1009   SELECT
1010     sum(c) OVER (ORDER BY b, a),
1011     sum(c) OVER (ORDER BY c, b)
1012   FROM t5
1013 } 2
1014
1015 do_ordercount_test 23.4 {
1016   SELECT
1017     sum(c) OVER (ORDER BY b ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
1018     sum(c) OVER (ORDER BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
1019     sum(c) OVER (ORDER BY b GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
1020   FROM t5
1021 } 1
1022
1023 do_ordercount_test 23.5 {
1024   SELECT
1025     sum(c) OVER (ORDER BY b+1 ROWS UNBOUNDED PRECEDING),
1026     sum(c) OVER (ORDER BY b+1 RANGE UNBOUNDED PRECEDING),
1027     sum(c) OVER (ORDER BY b+1 GROUPS UNBOUNDED PRECEDING)
1028   FROM t5
1029 } 1
1030
1031 do_ordercount_test 23.6 {
1032   SELECT
1033     sum(c) OVER (ORDER BY b+1 ROWS UNBOUNDED PRECEDING),
1034     sum(c) OVER (ORDER BY b+2 RANGE UNBOUNDED PRECEDING),
1035     sum(c) OVER (ORDER BY b+3 GROUPS UNBOUNDED PRECEDING)
1036   FROM t5
1037 } 3
1038
1039 do_execsql_test 24.1 {
1040   SELECT sum(44) OVER ()
1041 } {44}
1042
1043 do_execsql_test 24.2 {
1044   SELECT lead(44) OVER ()
1045 } {{}}
1046
1047 #-------------------------------------------------------------------------
1048 #
1049 reset_db
1050 do_execsql_test 25.0 {
1051   CREATE TABLE t1 ( t1_id INTEGER PRIMARY KEY );
1052   CREATE TABLE t2 ( t2_id INTEGER PRIMARY KEY );
1053   CREATE TABLE t3 ( t3_id INTEGER PRIMARY KEY );
1054
1055   INSERT INTO t1 VALUES(1),  (3), (5);
1056   INSERT INTO t2 VALUES      (3), (5);
1057   INSERT INTO t3 VALUES(10), (11), (12);
1058 }
1059
1060 do_execsql_test 25.1 {
1061   SELECT t1.* FROM t1, t2 WHERE
1062     t1_id=t2_id AND t1_id IN (
1063         SELECT t1_id + row_number() OVER ( ORDER BY t1_id ) FROM t3
1064     )
1065 }
1066
1067 do_execsql_test 25.2 {
1068   SELECT t1.* FROM t1, t2 WHERE
1069     t1_id=t2_id AND t1_id IN (
1070         SELECT         row_number() OVER ( ORDER BY t1_id ) FROM t3
1071     )
1072 } {3}
1073
1074 #-------------------------------------------------------------------------
1075 reset_db
1076 do_execsql_test 26.0 {
1077   CREATE TABLE t1(x);
1078   CREATE TABLE t2(c);
1079 }
1080
1081 do_execsql_test 26.1 {
1082   SELECT ( SELECT row_number() OVER () FROM ( SELECT c FROM t1 ) ) FROM t2
1083 } {}
1084
1085 do_execsql_test 26.2 {
1086   INSERT INTO t1 VALUES(1), (2), (3), (4);
1087   INSERT INTO t2 VALUES(2), (6), (8), (4);
1088   SELECT c, c IN (
1089     SELECT row_number() OVER () FROM ( SELECT c FROM t1 )
1090   ) FROM t2
1091 } {2 1  6 0  8 0  4 1}
1092
1093 do_execsql_test 26.3 {
1094   DELETE FROM t1;
1095   DELETE FROM t2;
1096
1097   INSERT INTO t2 VALUES(1), (2), (3), (4);
1098   INSERT INTO t1 VALUES(1), (1), (2), (3), (3), (3), (3), (4), (4);
1099
1100   SELECT c, c IN (
1101     SELECT row_number() OVER () FROM ( SELECT 1 FROM t1 WHERE x=c )
1102   ) FROM t2
1103 } {1 1  2 0  3 1  4 0}
1104
1105 #-------------------------------------------------------------------------
1106 reset_db
1107 do_execsql_test 27.0 {
1108   CREATE TABLE t1(x);
1109   INSERT INTO t1 VALUES(NULL), (1), (2), (3), (4), (5);
1110 }
1111 do_execsql_test 27.1 {
1112   SELECT min(x) FROM t1;
1113 } {1}
1114 do_execsql_test 27.2 {
1115   SELECT min(x) OVER win FROM t1
1116   WINDOW win AS (ORDER BY rowid ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
1117 } {1 1 1 2 3 4}
1118
1119 #-------------------------------------------------------------------------
1120
1121 reset_db
1122 do_execsql_test 28.1.1 {
1123   CREATE TABLE t1(a INTEGER PRIMARY KEY, b CHAR(1), c CHAR(2), d ANY);
1124   INSERT INTO t1 VALUES (3, 'C', 'cc', 1.0);
1125   INSERT INTO t1 VALUES (13,'M', 'cc', NULL);
1126 }
1127
1128 do_execsql_test 28.1.2 {
1129   SELECT group_concat(b,'') OVER w1 FROM t1
1130     WINDOW w1 AS (ORDER BY a RANGE BETWEEN 3 PRECEDING AND 1 PRECEDING)
1131 } {
1132   {} {}
1133 }
1134
1135 do_execsql_test 28.2.1 {
1136   CREATE TABLE t2(a TEXT, b INTEGER);
1137   INSERT INTO t2 VALUES('A', NULL);
1138   INSERT INTO t2 VALUES('B', NULL);
1139 }
1140
1141 do_execsql_test 28.2.1 {
1142   DROP TABLE IF EXISTS t1;
1143   CREATE TABLE t1(a INTEGER PRIMARY KEY, b CHAR(1), c CHAR(2), d ANY);
1144   INSERT INTO t1 VALUES
1145     (10,'J', 'cc', NULL),
1146     (11,'K', 'cc', 'xyz'),
1147     (13,'M', 'cc', NULL);
1148 }
1149
1150 do_execsql_test 28.2.2 {
1151   SELECT a, b, c, quote(d), group_concat(b,'') OVER w1, '|' FROM t1
1152     WINDOW w1 AS
1153     (ORDER BY d DESC RANGE BETWEEN 7.0 PRECEDING AND 2.5 PRECEDING)
1154     ORDER BY c, d, a;
1155 } {
1156   10 J cc NULL JM |
1157   13 M cc NULL JM |
1158   11 K cc 'xyz' K |
1159 }
1160
1161 #-------------------------------------------------------------------------
1162 reset_db
1163
1164 do_execsql_test 29.1 {
1165   DROP TABLE IF EXISTS t1;
1166   CREATE TABLE t1(a INTEGER PRIMARY KEY, b CHAR(1), c CHAR(2), d ANY);
1167   INSERT INTO t1 VALUES
1168     (1, 'A', 'aa', 2.5),
1169     (2, 'B', 'bb', 3.75),
1170     (3, 'C', 'cc', 1.0),
1171     (4, 'D', 'cc', 8.25),
1172     (5, 'E', 'bb', 6.5),
1173     (6, 'F', 'aa', 6.5),
1174     (7, 'G', 'aa', 6.0),
1175     (8, 'H', 'bb', 9.0),
1176     (9, 'I', 'aa', 3.75),
1177     (10,'J', 'cc', NULL),
1178     (11,'K', 'cc', 'xyz'),
1179     (12,'L', 'cc', 'xyZ'),
1180     (13,'M', 'cc', NULL);
1181 }
1182
1183 do_execsql_test 29.2 {
1184   SELECT a, b, c, quote(d), group_concat(b,'') OVER w1, '|' FROM t1
1185     WINDOW w1 AS
1186     (PARTITION BY c ORDER BY d DESC
1187      RANGE BETWEEN 7.0 PRECEDING AND 2.5 PRECEDING)
1188     ORDER BY c, d, a;
1189 } {
1190   1 A aa 2.5 FG |
1191   9 I aa 3.75 F |
1192   7 G aa 6 {} |
1193   6 F aa 6.5 {} |
1194   2 B bb 3.75 HE |
1195   5 E bb 6.5 H |
1196   8 H bb 9 {} |
1197   10 J cc NULL JM |
1198   13 M cc NULL JM |
1199   3 C cc 1 {} |
1200   4 D cc 8.25 {} |
1201   12 L cc 'xyZ' L |
1202   11 K cc 'xyz' K |
1203 }
1204
1205 # 2019-07-18
1206 # Check-in [7ef7b23cbb1b9ace] (which was itself a fix for ticket
1207 # https://www.sqlite.org/src/info/1be72aab9) introduced a new problem
1208 # if the LHS of a BETWEEN operator is a WINDOW function.  The problem
1209 # was found by (the recently enhanced) dbsqlfuzz.
1210 #
1211 do_execsql_test 30.0 {
1212   DROP TABLE IF EXISTS t1;
1213   CREATE TABLE t1(a, b, c);
1214   INSERT INTO t1 VALUES('BB','aa',399);
1215   SELECT
1216     count () OVER win1 NOT BETWEEN 'a' AND 'mmm',
1217     count () OVER win3
1218   FROM t1
1219   WINDOW win1 AS (ORDER BY a GROUPS BETWEEN 4 PRECEDING AND 1 FOLLOWING
1220                   EXCLUDE CURRENT ROW),
1221          win2 AS (PARTITION BY b ORDER BY a),
1222          win3 AS (win2 RANGE BETWEEN 5.2 PRECEDING AND true PRECEDING );
1223 } {1 1}
1224
1225 #-------------------------------------------------------------------------
1226 reset_db
1227 do_execsql_test 31.1 {
1228   CREATE TABLE t1(a, b);
1229   CREATE TABLE t2(c, d);
1230   CREATE TABLE t3(e, f);
1231
1232   INSERT INTO t1 VALUES(1, 1);
1233   INSERT INTO t2 VALUES(1, 1);
1234   INSERT INTO t3 VALUES(1, 1);
1235 }
1236
1237 do_execsql_test 31.2 {
1238   SELECT d IN (SELECT sum(c) OVER (ORDER BY e+c) FROM t3) FROM (
1239     SELECT * FROM t2
1240   );
1241 } {1}
1242
1243 do_execsql_test 31.3 {
1244   SELECT d IN (SELECT sum(c) OVER (PARTITION BY d ORDER BY e+c) FROM t3) FROM (
1245     SELECT * FROM t2
1246   );
1247 } {1}
1248
1249 do_catchsql_test 31.3 {
1250   SELECT d IN (
1251     SELECT sum(c) OVER ( ROWS BETWEEN d FOLLOWING AND UNBOUNDED FOLLOWING)
1252     FROM t3
1253   )
1254   FROM (
1255     SELECT * FROM t2
1256   );
1257 } {1 {frame starting offset must be a non-negative integer}}
1258
1259 do_catchsql_test 31.3 {
1260   SELECT d IN (
1261     SELECT sum(c) OVER ( ROWS BETWEEN CURRENT ROW AND c FOLLOWING)
1262     FROM t3
1263   )
1264   FROM (
1265     SELECT * FROM t2
1266   );
1267 } {1 {frame ending offset must be a non-negative integer}}
1268
1269 # 2019-11-16 chromium issue 1025467
1270 db close
1271 sqlite3 db :memory:
1272 do_catchsql_test 32.10 {
1273   CREATE VIEW a AS SELECT NULL INTERSECT SELECT NULL ORDER BY s() OVER R;
1274   CREATE TABLE a0 AS SELECT 0;
1275   ALTER TABLE a0 RENAME TO S;
1276 } {1 {error in view a: 1st ORDER BY term does not match any column in the result set}}
1277
1278 reset_db
1279 do_execsql_test 33.1 {
1280   CREATE TABLE t1(aa, bb);
1281   INSERT INTO t1 VALUES(1, 2);
1282   INSERT INTO t1 VALUES(5, 6);
1283   CREATE TABLE t2(x);
1284   INSERT INTO t2 VALUES(1);
1285 }
1286 do_execsql_test 33.2 {
1287   SELECT (SELECT DISTINCT sum(aa) OVER() FROM t1 ORDER BY 1), x FROM t2
1288   ORDER BY 1;
1289 } {6 1}
1290
1291 reset_db
1292 do_execsql_test 34.1 {
1293   CREATE TABLE t1(a,b,c);
1294 }
1295 do_execsql_test 34.2 {
1296   SELECT avg(a) OVER (
1297       ORDER BY (SELECT sum(b) OVER ()
1298         FROM t1 ORDER BY (
1299           SELECT total(d) OVER (ORDER BY c)
1300           FROM (SELECT 1 AS d) ORDER BY 1
1301           )
1302         )
1303       )
1304   FROM t1;
1305 }
1306
1307 #-------------------------------------------------------------------------
1308 reset_db
1309 do_catchsql_test 35.0 {
1310   SELECT * WINDOW f AS () ORDER BY name COLLATE nocase;
1311 } {1 {no tables specified}}
1312
1313 do_catchsql_test 35.1 {
1314   VALUES(1) INTERSECT SELECT * WINDOW f AS () ORDER BY x COLLATE nocase;
1315 } {1 {no tables specified}}
1316
1317 do_execsql_test 35.2 {
1318   CREATE TABLE t1(x);
1319   INSERT INTO t1 VALUES(1), (2), (3);
1320   VALUES(1) INTERSECT
1321   SELECT sum(x) OVER f FROM t1 WINDOW f AS (ORDER BY x) ORDER BY 1;
1322 } {1}
1323
1324 do_execsql_test 35.3 {
1325   VALUES(8) EXCEPT
1326   SELECT sum(x) OVER f FROM t1 WINDOW f AS (ORDER BY x) ORDER BY 1;
1327 } {8}
1328
1329 do_execsql_test 35.4 {
1330   VALUES(1) UNION
1331   SELECT sum(x) OVER f FROM t1 WINDOW f AS (ORDER BY x) ORDER BY 1;
1332 } {1 3 6}
1333
1334 # 2019-12-07 gramfuzz find
1335 #
1336 do_execsql_test 36.10 {
1337   VALUES(count(*)OVER());
1338 } {1}
1339 do_execsql_test 36.20 {
1340   VALUES(count(*)OVER()),(2);
1341 } {1 2}
1342 do_execsql_test 36.30 {
1343   VALUES(2),(count(*)OVER());
1344 } {2 1}
1345 do_execsql_test 36.40 {
1346   VALUES(2),(3),(count(*)OVER()),(4),(5);
1347 } {2 3 1 4 5}
1348
1349 # 2019-12-17 crash test case found by Yongheng and Rui
1350 # See check-in 1ca0bd982ab1183b
1351 #
1352 reset_db
1353 do_execsql_test 37.10 {
1354   CREATE TABLE t0(a UNIQUE, b PRIMARY KEY);
1355   CREATE VIEW v0(c) AS SELECT max((SELECT count(a)OVER(ORDER BY 1))) FROM t0;
1356   SELECT c FROM v0 WHERE c BETWEEN 10 AND 20;
1357 } {}
1358 do_execsql_test 37.20 {
1359   DROP VIEW v0;
1360   CREATE VIEW v0(c) AS SELECT max((SELECT count(a)OVER(ORDER BY 1234))) FROM t0;
1361   SELECT c FROM v0 WHERE c BETWEEN -10 AND 20;
1362 } {}
1363
1364 # 2019-12-20 mrigger reported problem with a FILTER clause on an aggregate
1365 # in a join.
1366 #
1367 reset_db
1368 do_catchsql_test 38.10 {
1369   CREATE TABLE t0(c0);
1370   CREATE TABLE t1(c0, c1 UNIQUE);
1371   INSERT INTO t0(c0) VALUES(1);
1372   INSERT INTO t1(c0,c1) VALUES(2,3);
1373   SELECT COUNT(*) FROM t0, t1 WHERE (SELECT AVG(0) FILTER(WHERE t1.c1));
1374 } {1 {misuse of aggregate: AVG()}}
1375 do_execsql_test 38.20 {
1376   SELECT COUNT(*), AVG(1) FILTER(WHERE t1.c1) FROM t0, t1;
1377 } {1 1.0}
1378 do_catchsql_test 38.30 {
1379   SELECT COUNT(*) FROM t0, t1 WHERE (SELECT AVG(1) FILTER(WHERE t1.c1));
1380 } {1 {misuse of aggregate: AVG()}}
1381
1382 reset_db
1383 do_execsql_test 39.1 {
1384   CREATE TABLE t0(c0 UNIQUE);
1385 }
1386 do_execsql_test 39.2 {
1387   SELECT FIRST_VALUE(0) OVER();
1388 } {0}
1389 do_execsql_test 39.3 {
1390   SELECT * FROM t0 WHERE(c0, 0) IN(SELECT FIRST_VALUE(0) OVER(), 0);
1391 }
1392 do_execsql_test 39.4 {
1393   SELECT * FROM t0 WHERE (t0.c0, 1) IN(SELECT NTILE(1) OVER(), 0 FROM t0);
1394 }
1395
1396 ifcapable rtree {
1397   # 2019-12-25 ticket d87336c81c7d0873
1398   #
1399   reset_db
1400   do_catchsql_test 40.1 {
1401     CREATE VIRTUAL TABLE t0 USING rtree(c0, c1, c2);
1402     SELECT * FROM t0
1403      WHERE ((0,0) IN (SELECT COUNT(*),LAG(5)OVER(PARTITION BY 0) FROM t0),0)<=(c1,0);
1404   } {0 {}}
1405 }
1406
1407 #-------------------------------------------------------------------------
1408 reset_db
1409 do_execsql_test 41.1 {
1410   CREATE TABLE t1(a, b, c);
1411   INSERT INTO t1 VALUES(NULL,'bb',355);
1412   INSERT INTO t1 VALUES('CC','aa',158);
1413   INSERT INTO t1 VALUES('GG','bb',929);
1414   INSERT INTO t1 VALUES('FF','Rb',574);
1415 }
1416
1417 do_execsql_test 41.2 {
1418   SELECT min(c) OVER (
1419     ORDER BY a RANGE BETWEEN 5.2 PRECEDING AND 0.1 PRECEDING
1420   ) FROM t1
1421 } {355 158 574 929}
1422
1423 do_execsql_test 41.2 {
1424   SELECT min(c) OVER (
1425     ORDER BY a RANGE BETWEEN 5.2 PRECEDING AND 0.1 PRECEDING
1426   ) << 100 FROM t1
1427 } {0 0 0 0}
1428
1429 do_execsql_test 41.3 {
1430   SELECT
1431     min(c) OVER win3 << first_value(c) OVER win3,
1432     min(c) OVER win3 << first_value(c) OVER win3
1433   FROM t1
1434   WINDOW win3 AS (
1435     PARTITION BY 6 ORDER BY a RANGE BETWEEN 5.2 PRECEDING AND 0.1 PRECEDING
1436   );
1437 } {0 0  0 0  0 0  0 0}
1438
1439 #-------------------------------------------------------------------------
1440 reset_db
1441 do_execsql_test 42.1 {
1442   CREATE TABLE t1(a, b, c);
1443   INSERT INTO t1 VALUES(1, 1, 1);
1444   INSERT INTO t1 VALUES(2, 2, 2);
1445 }
1446 do_execsql_test 42.2 {
1447   SELECT * FROM t1 WHERE (0, 0) IN ( SELECT count(*), 0 FROM t1 )
1448 } {}
1449 do_execsql_test 42.3 {
1450   SELECT * FROM t1 WHERE (2, 0) IN ( SELECT count(*), 0 FROM t1 )
1451 } {1 1 1 2 2 2}
1452
1453 do_execsql_test 42.3 {
1454   SELECT count(*), max(a) OVER () FROM t1 GROUP BY c;
1455 } {1 2 1 2}
1456
1457 do_execsql_test 42.4 {
1458   SELECT sum(a), max(b) OVER () FROM t1;
1459 } {3 1}
1460
1461 do_execsql_test 42.5 {
1462   CREATE TABLE t2(a, b);
1463   INSERT INTO t2 VALUES('a', 1);
1464   INSERT INTO t2 VALUES('a', 2);
1465   INSERT INTO t2 VALUES('a', 3);
1466   INSERT INTO t2 VALUES('b', 4);
1467   INSERT INTO t2 VALUES('b', 5);
1468   INSERT INTO t2 VALUES('b', 6);
1469 }
1470
1471 do_execsql_test 42.6 {
1472   SELECT a, sum(b), sum( sum(b) ) OVER (ORDER BY a) FROM t2 GROUP BY a;
1473 } {a 6 6   b 15 21}
1474
1475 do_execsql_test 42.7 {
1476   SELECT sum(b), sum( sum(b) ) OVER (ORDER BY a) FROM t2;
1477 } {21 21}
1478
1479 #-------------------------------------------------------------------------
1480 reset_db
1481 do_execsql_test 43.1.1 {
1482   CREATE TABLE t1(x INTEGER PRIMARY KEY);
1483   INSERT INTO t1 VALUES (10);
1484 }
1485 do_catchsql_test 43.1.2 {
1486   SELECT count() OVER() AS m FROM t1 ORDER BY (SELECT m);
1487 } {1 {misuse of aliased window function m}}
1488
1489 reset_db
1490 do_execsql_test 43.2.1 {
1491   CREATE TABLE t1(a INTEGER PRIMARY KEY, b INTEGER);
1492   INSERT INTO t1(a, b) VALUES(1,  10); -- 10
1493   INSERT INTO t1(a, b) VALUES(2,  15); -- 25
1494   INSERT INTO t1(a, b) VALUES(3,  -5); -- 20
1495   INSERT INTO t1(a, b) VALUES(4,  -5); -- 15
1496   INSERT INTO t1(a, b) VALUES(5,  20); -- 35
1497   INSERT INTO t1(a, b) VALUES(6, -11); -- 24
1498 }
1499
1500 do_execsql_test 43.2.2 {
1501   SELECT a, sum(b) OVER (ORDER BY a) AS abc FROM t1 ORDER BY 2
1502 } {
1503   1 10   4 15   3 20   6 24   2 25   5 35
1504 }
1505
1506 do_execsql_test 43.2.3 {
1507   SELECT a, sum(b) OVER (ORDER BY a) AS abc FROM t1 ORDER BY abc
1508 } {
1509   1 10   4 15   3 20   6 24   2 25   5 35
1510 }
1511
1512 do_execsql_test 43.2.4 {
1513   SELECT a, sum(b) OVER (ORDER BY a) AS abc FROM t1 ORDER BY abc+5
1514 } {
1515   1 10   4 15   3 20   6 24   2 25   5 35
1516 }
1517
1518 do_catchsql_test 43.2.5 {
1519   SELECT a, sum(b) OVER (ORDER BY a) AS abc FROM t1 ORDER BY (SELECT abc)
1520 } {1 {misuse of aliased window function abc}}
1521
1522 do_catchsql_test 43.2.6 {
1523   SELECT a, 1+sum(b) OVER (ORDER BY a) AS abc FROM t1 ORDER BY (SELECT abc)
1524 } {1 {misuse of aliased window function abc}}
1525
1526 #-------------------------------------------------------------------------
1527 reset_db
1528 do_execsql_test 44.1 {
1529   CREATE TABLE t0(c0);
1530 }
1531
1532 do_catchsql_test 44.2.1 {
1533   SELECT ntile(0) OVER ();
1534 } {1 {argument of ntile must be a positive integer}}
1535 do_catchsql_test 44.2.2 {
1536   SELECT (0, 0) IN(SELECT MIN(c0), NTILE(0) OVER()) FROM t0;
1537 } {1 {argument of ntile must be a positive integer}}
1538
1539 do_execsql_test 44.3.1 {
1540   SELECT ntile(1) OVER ();
1541 } {1}
1542 do_execsql_test 44.3.2 {
1543   SELECT (0, 0) IN(SELECT MIN(c0), NTILE(1) OVER()) FROM t0;
1544 } {0}
1545
1546 do_execsql_test 44.4.2 {
1547   INSERT INTO t0 VALUES(2), (1), (0);
1548   SELECT (0, 1) IN(SELECT MIN(c0), NTILE(1) OVER()) FROM t0;
1549 } {1}
1550
1551 #-------------------------------------------------------------------------
1552 reset_db
1553 do_execsql_test 45.1 {
1554   CREATE TABLE t0(x);
1555   CREATE TABLE t1(a);
1556   INSERT INTO t1 VALUES(1000);
1557   INSERT INTO t1 VALUES(1000);
1558   INSERT INTO t0 VALUES(10000);
1559 }
1560 do_execsql_test 45.2 {
1561   SELECT * FROM (
1562       SELECT sum (a) OVER() FROM t1 UNION ALL SELECT x FROM t0
1563   );
1564 } {2000 2000 10000}
1565
1566 #-------------------------------------------------------------------------
1567 reset_db
1568 do_execsql_test 46.1 {
1569   CREATE TABLE t1 (a);
1570   CREATE INDEX i1 ON t1(a);
1571
1572   INSERT INTO t1 VALUES (10);
1573 }
1574
1575 do_execsql_test 46.2 {
1576   SELECT (SELECT sum(a) OVER(ORDER BY a)) FROM t1
1577 } 10
1578
1579 do_execsql_test 46.3 {
1580   SELECT * FROM t1 WHERE (SELECT sum(a) OVER(ORDER BY a));
1581 } 10
1582
1583 do_execsql_test 46.4 {
1584   SELECT * FROM t1 NATURAL JOIN t1
1585     WHERE a=1
1586     OR ((SELECT sum(a)OVER(ORDER BY a)) AND a<=10)
1587 } 10
1588
1589 #-------------------------------------------------------------------------
1590 reset_db
1591 do_execsql_test 47.0 {
1592   CREATE TABLE t1(
1593       a,
1594       e,
1595       f,
1596       g UNIQUE,
1597       h UNIQUE
1598   );
1599 }
1600
1601 do_execsql_test 47.1 {
1602   CREATE VIEW t2(k) AS
1603      SELECT e FROM t1 WHERE g = 'abc' OR h BETWEEN 10 AND f;
1604 }
1605
1606 do_catchsql_test 47.2 {
1607   SELECT 234 FROM t2
1608     WHERE k=1
1609     OR (SELECT k FROM t2 WHERE (SELECT sum(a) OVER() FROM t1 GROUP BY 1));
1610 } {1 {misuse of window function sum()}}
1611
1612 #-------------------------------------------------------------------------
1613 reset_db
1614 do_execsql_test 48.0 {
1615   CREATE TABLE t1(a);
1616   INSERT INTO t1 VALUES(1);
1617   INSERT INTO t1 VALUES(2);
1618   INSERT INTO t1 VALUES(3);
1619   SELECT (SELECT max(x)OVER(ORDER BY x) + min(x)OVER(ORDER BY x))
1620     FROM (SELECT (SELECT sum(a) FROM t1) AS x FROM t1);
1621 } {12 12 12}
1622
1623 do_execsql_test 48.1 {
1624   SELECT (SELECT max(x)OVER(ORDER BY x) + min(x)OVER(ORDER BY x))
1625     FROM (SELECT (SELECT sum(a) FROM t1 GROUP BY a) AS x FROM t1);
1626 } {2 2 2}
1627
1628 #-------------------------------------------------------------------------
1629 reset_db
1630 do_execsql_test 49.1 {
1631   CREATE TABLE t1 (a PRIMARY KEY);
1632   INSERT INTO t1 VALUES(1);
1633 }
1634
1635 do_execsql_test 49.2 {
1636   SELECT b AS c FROM (
1637     SELECT a AS b FROM (
1638       SELECT a FROM t1 WHERE a=1 OR (SELECT sum(a) OVER ())
1639     )
1640     WHERE b=1 OR b<10
1641   )
1642   WHERE c=1 OR c>=10;
1643 } {1}
1644
1645
1646 #-------------------------------------------------------------------------
1647 reset_db
1648 do_execsql_test 50.0 {
1649   CREATE TABLE t1 (a DOUBLE PRIMARY KEY);
1650   INSERT INTO t1 VALUES(10.0);
1651 }
1652
1653 do_execsql_test 50.1 {
1654   SELECT * FROM t1 WHERE a%1 OR (SELECT sum(a) OVER (ORDER BY a%2))
1655 } {10.0}
1656
1657 do_execsql_test 50.2 {
1658   SELECT * FROM (
1659     SELECT * FROM t1 WHERE a%1 OR (SELECT sum(a) OVER (ORDER BY a%2))
1660   )
1661   WHERE a=1 OR ( (SELECT sum(a) OVER (ORDER BY a%4)) AND a<=10 )
1662 } {10.0}
1663
1664 do_execsql_test 50.3 {
1665   SELECT a FROM (
1666     SELECT * FROM (
1667       SELECT * FROM t1 WHERE a%1 OR (SELECT sum(a) OVER (ORDER BY a%2))
1668     )
1669     WHERE a=1 OR ( (SELECT sum(a) OVER (ORDER BY a%4)) AND a<=10 )
1670   )
1671   WHERE a=1 OR a=10.0
1672 } {10.0}
1673
1674 do_execsql_test 50.4 {
1675   SELECT a FROM (
1676     SELECT * FROM (
1677       SELECT * FROM t1 WHERE a%1 OR (SELECT sum(a) OVER (ORDER BY a%2))
1678     )
1679     WHERE a=1 OR ( (SELECT sum(a) OVER (ORDER BY a%4)) AND a<=10 )
1680   )
1681   WHERE a=1 OR ((SELECT sum(a) OVER(ORDER BY a%8)) AND 10<=a)
1682 } {10.0}
1683
1684 do_execsql_test 50.5 {
1685 SELECT * FROM (SELECT * FROM t1 NATURAL JOIN t1 WHERE a%1 OR ((SELECT sum(a)OVER(ORDER BY a)) AND a<=10)) NATURAL JOIN t1 WHERE a=1 OR ((SELECT sum((SELECT * FROM (SELECT * FROM (SELECT * FROM t1 NATURAL JOIN t1 WHERE a%1 OR ((SELECT sum(a)OVER(ORDER BY a)) AND a<=10)) NATURAL JOIN t1 WHERE a=1 OR ((SELECT sum((SELECT * FROM t1 NATURAL JOIN t1 WHERE a=1 OR ((SELECT sum(a)OVER(ORDER BY a)) AND a<=10)))OVER(ORDER BY a% 1 )) AND a<=10)) NATURAL JOIN t1 WHERE a=1 OR ((SELECT sum(a)OVER(ORDER BY a)) AND 10<=a)))OVER(ORDER BY a%5)) AND a<=10);
1686 } {10.0}
1687
1688 # 2020-04-03 ticket af4556bb5c285c08
1689 #
1690 reset_db
1691 do_catchsql_test 51.1 {
1692   CREATE TABLE a(b, c);
1693   SELECT c FROM a GROUP BY c
1694     HAVING(SELECT(sum(b) OVER(ORDER BY b),
1695                   sum(b) OVER(PARTITION BY min(DISTINCT c), c ORDER BY b)));
1696 } {1 {row value misused}}
1697
1698 #-------------------------------------------------------------------------
1699 reset_db
1700 do_execsql_test 52.1 {
1701   CREATE TABLE t1(a, b, c);
1702   INSERT INTO t1 VALUES('AA','bb',356);
1703   INSERT INTO t1 VALUES('CC','aa',158);
1704   INSERT INTO t1 VALUES('BB','aa',399);
1705   INSERT INTO t1 VALUES('FF','bb',938);
1706 }
1707
1708 do_execsql_test 52.2 {
1709   SELECT
1710     count() OVER win1,
1711     sum(c) OVER win2,
1712     first_value(c) OVER win2,
1713     count(a) OVER (ORDER BY b)
1714       FROM t1
1715       WINDOW
1716       win1 AS (ORDER BY a),
1717     win2 AS (PARTITION BY 6 ORDER BY a
1718         RANGE BETWEEN 5 PRECEDING AND 0 PRECEDING );
1719 } {
1720   1 356 356 4
1721   2 399 399 2
1722   3 158 158 2
1723   4 938 938 4
1724 }
1725
1726 do_execsql_test 52.3 {
1727 SELECT
1728   count() OVER (),
1729   sum(c) OVER win2,
1730   first_value(c) OVER win2,
1731   count(a) OVER (ORDER BY b)
1732 FROM t1
1733 WINDOW
1734   win1 AS (ORDER BY a),
1735   win2 AS (PARTITION BY 6 COLLATE binary ORDER BY a
1736            RANGE BETWEEN 5 PRECEDING AND 0 PRECEDING );
1737 } {
1738   4 356 356 4
1739   4 399 399 2
1740   4 158 158 2
1741   4 938 938 4
1742 }
1743
1744 do_execsql_test 52.4 {
1745   SELECT
1746     count() OVER win1,
1747     sum(c) OVER win2,
1748     first_value(c) OVER win2,
1749     count(a) OVER (ORDER BY b)
1750   FROM t1
1751   WINDOW
1752     win1 AS (ORDER BY a),
1753     win2 AS (PARTITION BY 6 COLLATE binary ORDER BY a
1754              RANGE BETWEEN 5 PRECEDING AND 0 PRECEDING );
1755 } {
1756   1 356 356 4
1757   2 399 399 2
1758   3 158 158 2
1759   4 938 938 4
1760 }
1761
1762 # 2020-05-23
1763 # ticket 7a5279a25c57adf1
1764 #
1765 reset_db
1766 do_execsql_test 53.0 {
1767   CREATE TABLE a(c UNIQUE);
1768   INSERT INTO a VALUES(4),(0),(9),(-9);
1769   SELECT a.c
1770     FROM a
1771     JOIN a AS b ON a.c=4
1772     JOIN a AS e ON a.c=e.c
1773    WHERE a.c=(SELECT (SELECT coalesce(lead(2) OVER(),0) + sum(d.c))
1774                 FROM a AS d
1775                WHERE a.c);
1776 } {4 4 4 4}
1777
1778 #-------------------------------------------------------------------------
1779 reset_db
1780 do_execsql_test 54.1 {
1781   CREATE TABLE t1(a VARCHAR(20), b FLOAT);
1782   INSERT INTO t1 VALUES('1',10.0);
1783 }
1784
1785 do_catchsql_test 54.2 {
1786   SELECT * FROM (
1787     SELECT sum(b) OVER() AS c FROM t1
1788       UNION
1789     SELECT b AS c FROM t1
1790   ) WHERE c>10;
1791 } {0 {}}
1792
1793 do_execsql_test 54.3 {
1794   INSERT INTO t1 VALUES('2',5.0);
1795   INSERT INTO t1 VALUES('3',15.0);
1796 }
1797
1798 do_catchsql_test 54.4 {
1799   SELECT * FROM (
1800     SELECT sum(b) OVER() AS c FROM t1
1801       UNION
1802     SELECT b AS c FROM t1
1803   ) WHERE c>10;
1804 } {0 {15.0 30.0}}
1805
1806 # 2020-06-05 ticket c8d3b9f0a750a529
1807 reset_db
1808 do_execsql_test 55.1 {
1809    CREATE TABLE a(b);
1810    SELECT
1811       (SELECT b FROM a
1812         GROUP BY b
1813         HAVING (SELECT COUNT()OVER() + lead(b)OVER(ORDER BY SUM(DISTINCT b) + b))
1814       )
1815     FROM a
1816   UNION
1817    SELECT 99
1818     ORDER BY 1;
1819 } {99}
1820
1821 #------------------------------------------------------------------------
1822 reset_db
1823 do_execsql_test 56.1 {
1824   CREATE TABLE t1(a, b INTEGER);
1825   CREATE TABLE t2(c, d);
1826 }
1827 do_catchsql_test 56.2 {
1828   SELECT avg(b) FROM t1
1829     UNION ALL
1830   SELECT min(c) OVER () FROM t2
1831   ORDER BY nosuchcolumn;
1832 } {1 {1st ORDER BY term does not match any column in the result set}}
1833
1834 reset_db
1835 do_execsql_test 57.1 {
1836   CREATE TABLE t4(a, b, c, d, e);
1837 }
1838
1839 do_catchsql_test 57.2  {
1840   SELECT b FROM t4
1841   UNION
1842   SELECT a FROM t4
1843   ORDER BY (
1844     SELECT sum(x) OVER() FROM (
1845       SELECT c AS x FROM t4
1846       UNION
1847       SELECT d FROM t4
1848       ORDER BY (SELECT e FROM t4)
1849     )
1850   );
1851 } {1 {1st ORDER BY term does not match any column in the result set}}
1852
1853 # 2020-06-06 various dbsqlfuzz finds and
1854 # ticket 0899cf62f597d7e7
1855 #
1856 reset_db
1857 do_execsql_test 57.1 {
1858   CREATE TABLE t1(a, b, c);
1859   INSERT INTO t1 VALUES(NULL,NULL,NULL);
1860   SELECT
1861     sum(a),
1862     min(b) OVER (),
1863     count(c) OVER (ORDER BY b)
1864   FROM t1;
1865 } {{} {} 0}
1866 do_execsql_test 57.2 {
1867   CREATE TABLE v0 ( v1 INTEGER PRIMARY KEY ) ;
1868   INSERT INTO v0 VALUES ( 10 ) ;
1869   SELECT DISTINCT v1, lead(v1) OVER() FROM v0 GROUP BY v1 ORDER BY 2;
1870 } {10 {}}
1871 do_catchsql_test 57.3 {
1872   DROP TABLE t1;
1873   CREATE TABLE t1(a);
1874   INSERT INTO t1(a) VALUES(22);
1875   CREATE TABLE t3(y);
1876   INSERT INTO t3(y) VALUES(5),(11),(-9);
1877   SELECT (
1878     SELECT max(y) OVER( ORDER BY (SELECT x FROM (SELECT sum(y) AS x FROM t1)))
1879   )
1880   FROM t3;
1881 } {1 {misuse of aggregate: sum()}}
1882
1883 # 2020-06-06 ticket 1f6f353b684fc708
1884 reset_db
1885 do_execsql_test 58.1 {
1886   CREATE TABLE a(a, b, c);
1887   INSERT INTO a VALUES(1, 2, 3);
1888   INSERT INTO a VALUES(4, 5, 6);
1889   SELECT sum(345+b)      OVER (ORDER BY b),
1890          sum(avg(678)) OVER (ORDER BY c) FROM a;
1891 } {347 678.0}
1892
1893 # 2020-06-06 ticket e5504e987e419fb0
1894 do_catchsql_test 59.1 {
1895   DROP TABLE IF EXISTS t1;
1896   CREATE TABLE t1(x INTEGER PRIMARY KEY);
1897   INSERT INTO t1 VALUES (123);
1898   SELECT
1899      ntile( (SELECT sum(x)) ) OVER(ORDER BY x),
1900      min(x) OVER(ORDER BY x)
1901     FROM t1;
1902 } {1 {misuse of aggregate: sum()}}
1903
1904 # 2020-06-07 ticket f7d890858f361402
1905 do_execsql_test 60.1 {
1906   DROP TABLE IF EXISTS t1;
1907   CREATE TABLE t1 (x INTEGER PRIMARY KEY);
1908   INSERT INTO t1 VALUES (99);
1909   SELECT EXISTS(SELECT count(*) OVER() FROM t1 ORDER BY sum(x) OVER());
1910 } {1}
1911
1912 # 2020-06-07 test case generated by dbsqlfuzz showing how an AggInfo
1913 # object might be referenced after the sqlite3Select() call that created
1914 # it returns.  This proves the need to persist all AggInfo objects until
1915 # the Parse object is destroyed.
1916 #
1917 reset_db
1918 do_catchsql_test 61.1 {
1919 CREATE TABLE t1(a);
1920 INSERT INTO t1 VALUES(5),(NULL),('seventeen');
1921 SELECT (SELECT max(x)OVER(ORDER BY x) % min(x)OVER(ORDER BY CASE x WHEN 889 THEN x WHEN x THEN x END)) FROM (SELECT (SELECT sum(CAST(a IN(SELECT (SELECT max(x)OVER(ORDER BY CASE x WHEN 889 THEN 299 WHEN 863 THEN 863 END)) FROM (SELECT (SELECT sum(CAST((SELECT (SELECT max(x)OVER(ORDER BY x) / min(x)OVER(ORDER BY CASE x WHEN 889 THEN 299 WHEN -true THEN 863 END)) FROM (SELECT (SELECT sum(CAST(a IN(SELECT (SELECT max(x) & sum ( a )OVER(ORDER BY CASE x WHEN -8 THEN 299 WHEN 863 THEN 863 END)) FROM (SELECT (SELECT sum(CAST(a AS )) FROM t1) AS x FROM t1)) AS t1 )) FROM t1) AS x FROM t1)) AS x )) FROM t1) AS x FROM t1)) AS real)) FROM t1) AS x FROM t1);
1922 } {0 {{} {} {}}}
1923
1924 foreach tn {1 2} {
1925   if {$tn==2} { optimization_control db query-flattener 0 }
1926   do_catchsql_test 61.2.$tn {
1927     SELECT
1928       (SELECT max(x)OVER(ORDER BY x) / min(x) OVER() )
1929     FROM (
1930       SELECT (SELECT sum(a) FROM t1 ) AS x FROM t1
1931     )
1932
1933   } {0 {1.0 1.0 1.0}}
1934 }
1935
1936 reset_db
1937 optimization_control db all 0
1938 do_execsql_test 61.3.0 {
1939   CREATE TABLE t1(a);
1940   CREATE TABLE t2(y);
1941 }
1942
1943 do_execsql_test 61.3.1 {
1944   SELECT (
1945     SELECT count(a) OVER ( ORDER BY (SELECT sum(y) FROM t2) )
1946          + total(a) OVER()
1947   )
1948   FROM t1
1949 } {}
1950 do_execsql_test 61.4.2 {
1951   SELECT (
1952     SELECT count(a) OVER ( ORDER BY sum(a) )
1953          + total(a) OVER()
1954   )
1955   FROM t1
1956 } {0.0}
1957
1958 do_catchsql_test 61.4.3 {
1959   SELECT
1960     sum(a) OVER ( ORDER BY a )
1961   FROM t1
1962   ORDER BY (SELECT sum(a) FROM t2)
1963 } {1 {misuse of aggregate: sum()}}
1964 do_execsql_test 61.4.4 {
1965   SELECT
1966     sum(a) OVER ( ORDER BY a )
1967   FROM t1
1968   ORDER BY (SELECT sum(y) FROM t2)
1969 }
1970
1971
1972 #-------------------------------------------------------------------------
1973 reset_db
1974 do_execsql_test 62.1 {
1975   CREATE TABLE t1(a VARCHAR(20), b FLOAT);
1976   INSERT INTO t1 VALUES('1',10.0);
1977 }
1978
1979 do_execsql_test 62.2 {
1980   SELECT * FROM (
1981       SELECT sum(b) OVER() AS c FROM t1
1982       UNION
1983       SELECT b AS c FROM t1
1984       ) WHERE c>10;
1985 }
1986
1987 do_execsql_test 62.3 {
1988   INSERT INTO t1 VALUES('2',5.0);
1989   INSERT INTO t1 VALUES('3',15.0);
1990 }
1991
1992 do_execsql_test 62.4 {
1993   SELECT * FROM (
1994       SELECT sum(b) OVER() AS c FROM t1
1995       UNION
1996       SELECT b AS c FROM t1
1997       ) WHERE c>10;
1998 } {15.0 30.0}
1999
2000 #-------------------------------------------------------------------------
2001 reset_db
2002 do_execsql_test 63.1 {
2003   CREATE TABLE t1(b, x);
2004   CREATE TABLE t2(c, d);
2005   CREATE TABLE t3(e, f);
2006 }
2007
2008 do_execsql_test 63.2 {
2009   SELECT max(b) OVER(
2010       ORDER BY SUM(
2011         (SELECT c FROM t2 UNION SELECT x ORDER BY c)
2012       )
2013   ) FROM t1;
2014 } {{}}
2015
2016 do_execsql_test 63.3 {
2017   SELECT sum(b) over(
2018       ORDER BY (
2019         SELECT max(b) OVER(
2020           ORDER BY sum(
2021             (SELECT x AS c UNION SELECT 1234 ORDER BY c)
2022           )
2023         ) AS e
2024         ORDER BY e
2025       )
2026     )
2027   FROM t1;
2028 } {{}}
2029
2030 #-------------------------------------------------------------------------
2031 reset_db
2032 do_execsql_test 64.1 {
2033   CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
2034   INSERT INTO t1 VALUES(1, 'abcd');
2035   INSERT INTO t1 VALUES(2, 'BCDE');
2036   INSERT INTO t1 VALUES(3, 'cdef');
2037   INSERT INTO t1 VALUES(4, 'DEFG');
2038 }
2039
2040 do_execsql_test 64.2 {
2041   SELECT rowid, max(b COLLATE nocase)||''
2042   FROM t1
2043   GROUP BY rowid
2044   ORDER BY max(b COLLATE nocase)||'';
2045 } {1 abcd 2 BCDE 3 cdef 4 DEFG}
2046
2047 do_execsql_test 64.3 {
2048   SELECT count() OVER (), rowid, max(b COLLATE nocase)||''
2049   FROM t1
2050   GROUP BY rowid
2051   ORDER BY max(b COLLATE nocase)||'';
2052 } {4 1 abcd   4 2 BCDE   4 3 cdef   4 4 DEFG}
2053
2054 do_execsql_test 64.4 {
2055   SELECT count() OVER (), rowid, max(b COLLATE nocase)
2056   FROM t1
2057   GROUP BY rowid
2058   ORDER BY max(b COLLATE nocase);
2059 } {4 1 abcd   4 2 BCDE   4 3 cdef   4 4 DEFG}
2060
2061 #-------------------------------------------------------------------------
2062 reset_db
2063 do_execsql_test 65.1 {
2064   CREATE TABLE t1(c1);
2065   INSERT INTO t1 VALUES('abcd');
2066 }
2067 do_execsql_test 65.2 {
2068   SELECT max(c1 COLLATE nocase) IN (SELECT 'aBCd') FROM t1;
2069 } {1}
2070
2071 do_execsql_test 65.3 {
2072   SELECT
2073      count() OVER (),
2074      group_concat(c1 COLLATE nocase) IN (SELECT 'aBCd') FROM t1;
2075 } {1 1}
2076
2077 do_execsql_test 65.4 {
2078   SELECT COUNT() OVER () LIKE lead(102030) OVER(
2079       ORDER BY sum('abcdef' COLLATE nocase) IN (SELECT 54321)
2080   )
2081   FROM t1;
2082 } {{}}
2083
2084 #-------------------------------------------------------------------------
2085 reset_db
2086
2087 do_execsql_test 66.1 {
2088   CREATE TABLE t1(a INTEGER);
2089   INSERT INTO t1 VALUES(3578824042033200656);
2090   INSERT INTO t1 VALUES(3029012920382354029);
2091 }
2092
2093 foreach {tn spec} {
2094   1 "ORDER BY a RANGE BETWEEN 0.3 PRECEDING AND 10 FOLLOWING"
2095   2 "ORDER BY a RANGE BETWEEN 0.3 PRECEDING AND 0.1 PRECEDING"
2096   3 "ORDER BY a RANGE BETWEEN 0.3 FOLLOWING AND 10 FOLLOWING"
2097   4 "ORDER BY a DESC RANGE BETWEEN 0.3 PRECEDING AND 10 FOLLOWING"
2098   5 "ORDER BY a NULLS LAST RANGE BETWEEN 0.3 PRECEDING AND 10 FOLLOWING"
2099   6 "ORDER BY a RANGE BETWEEN 1.0 PRECEDING AND 2.0 PRECEDING"
2100 } {
2101   do_execsql_test 66.2.$tn "
2102     SELECT total(a) OVER ( $spec ) FROM t1 ORDER BY a
2103   " {
2104     3.02901292038235e+18 3.5788240420332e+18
2105   }
2106 }
2107
2108
2109 do_execsql_test 66.3 {
2110   CREATE TABLE t2(a INTEGER);
2111   INSERT INTO t2 VALUES(45);
2112   INSERT INTO t2 VALUES(30);
2113 }
2114
2115 foreach {tn spec res} {
2116   1 "ORDER BY a RANGE BETWEEN 0.3 PRECEDING AND 10 FOLLOWING"   {30.0 45.0}
2117   2 "ORDER BY a RANGE BETWEEN 0.3 PRECEDING AND 0.1 PRECEDING"  {0.0 0.0}
2118   3 "ORDER BY a RANGE BETWEEN 0.3 FOLLOWING AND 10 FOLLOWING"   {0.0 0.0}
2119   4 "ORDER BY a DESC RANGE BETWEEN 0.3 PRECEDING AND 10 FOLLOWING" {30.0 45.0}
2120   5 "ORDER BY a NULLS LAST RANGE BETWEEN 0.3 PRECEDING AND 10 FOLLOWING" {30.0 45.0}
2121   6 "ORDER BY a RANGE BETWEEN 1.0 PRECEDING AND 2.0 PRECEDING" {0.0 0.0}
2122 } {
2123   do_execsql_test 66.2.$tn "
2124     SELECT total(a) OVER ( $spec ) FROM t2 ORDER BY a
2125   " $res
2126 }
2127
2128
2129 #-------------------------------------------------------------------------
2130 reset_db
2131 do_execsql_test 67.0 {
2132   CREATE TABLE t1(a, b, c);
2133   CREATE TABLE t2(a, b, c);
2134 }
2135
2136 do_catchsql_test 67.1 {
2137   SELECT a,c,b FROM t1 INTERSECT SELECT a,b,c FROM t1 ORDER BY (
2138       SELECT nth_value(a,2) OVER w1
2139       WINDOW w1 AS ( ORDER BY ((SELECT 1 FROM v1)) )
2140   )
2141 } {1 {no such table: v1}}
2142
2143 do_catchsql_test 67.2 {
2144   SELECT a,c,b FROM t1 INTERSECT SELECT a,b,c FROM t1 ORDER BY (
2145       SELECT nth_value(a,2) OVER w1
2146       WINDOW w1 AS ( ORDER BY ((SELECT 1 FROM t2)) )
2147   )
2148 } {1 {1st ORDER BY term does not match any column in the result set}}
2149
2150 # 2021-05-07
2151 # Do not allow aggregate functions in the ORDER BY clause even if
2152 # there are window functions in the result set.
2153 # Forum: /forumpost/540fdfef77
2154 #
2155 reset_db
2156 do_catchsql_test 68.0 {
2157   CREATE TABLE t1(a,b);
2158   INSERT INTO t1(a,b) VALUES(0,0),(1,1),(2,4),(3,9),(4,99);
2159   SELECT rowid, a, b, sum(a)OVER() FROM t1 ORDER BY count(b);
2160 } {1 {misuse of aggregate: count()}}
2161
2162 # 2021-05-22
2163 # Forum https://sqlite.org/forum/forumpost/7e484e225c
2164 #
2165 reset_db
2166 do_catchsql_test 69.0 {
2167   CREATE TABLE t1(a,b);
2168   CREATE INDEX t1ba ON t1(b,a);
2169   SELECT * FROM t1 WHERE b = (SELECT b FROM t1 ORDER BY lead(b) OVER () AND sum(a));
2170 } {1 {misuse of aggregate: sum()}}
2171 do_catchsql_test 69.1 {
2172   SELECT * FROM t1 WHERE b >= (SELECT b FROM t1 ORDER BY lead(b) OVER () AND sum(a));
2173 } {1 {misuse of aggregate: sum()}}
2174 do_catchsql_test 69.2 {
2175   SELECT * FROM t1 WHERE b <= (SELECT b FROM t1 ORDER BY lead(b) OVER () AND sum(a));
2176 } {1 {misuse of aggregate: sum()}}
2177
2178
2179 finish_test