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