niceSOFT/sqlite: Official Git mirror of the SQLite source tree

test/window1.test
2 397 строк62 KB
drh
Fix the window-function group_concat() so that it returns an empty string
24 май 2024, 02:26
24 май 2024, 02:26e6d3c57
# 2018 May 8
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix window1

ifcapable !windowfunc {
  finish_test
  return
}

do_execsql_test 1.0 {
  CREATE TABLE t1(a, b, c, d);
  INSERT INTO t1 VALUES(1, 2, 3, 4);
  INSERT INTO t1 VALUES(5, 6, 7, 8);
  INSERT INTO t1 VALUES(9, 10, 11, 12);
}

do_execsql_test 1.1 {
  SELECT sum(b) OVER () FROM t1
} {18 18 18}

do_execsql_test 1.2 {
  SELECT a, sum(b) OVER () FROM t1
} {1 18 5 18 9 18}

do_execsql_test 1.3 {
  SELECT a, 4 + sum(b) OVER () FROM t1
} {1 22 5 22 9 22}

do_execsql_test 1.4 {
  SELECT a + 4 + sum(b) OVER () FROM t1
} {23 27 31}

do_execsql_test 1.5 {
  SELECT a, sum(b) OVER (PARTITION BY c) FROM t1
} {1 2 5 6 9 10}

foreach {tn sql} {
  1 "SELECT sum(b) OVER () FROM t1"
  2 "SELECT sum(b) OVER (PARTITION BY c) FROM t1"
  3 "SELECT sum(b) OVER (ORDER BY c) FROM t1"
  4 "SELECT sum(b) OVER (PARTITION BY d ORDER BY c) FROM t1"
  5 "SELECT sum(b) FILTER (WHERE a>0) OVER (PARTITION BY d ORDER BY c) FROM t1"
  6 "SELECT sum(b) OVER (ORDER BY c RANGE UNBOUNDED PRECEDING) FROM t1"
  7 "SELECT sum(b) OVER (ORDER BY c ROWS 45 PRECEDING) FROM t1"
  8 "SELECT sum(b) OVER (ORDER BY c RANGE CURRENT ROW) FROM t1"
  9 "SELECT sum(b) OVER (ORDER BY c RANGE BETWEEN UNBOUNDED PRECEDING 
     AND CURRENT ROW) FROM t1"
 10 "SELECT sum(b) OVER (ORDER BY c ROWS BETWEEN UNBOUNDED PRECEDING 
     AND UNBOUNDED FOLLOWING) FROM t1"
} {
  do_test 2.$tn { lindex [catchsql $sql] 0 } 0
}

foreach {tn sql} {
  1 "SELECT * FROM t1 WHERE sum(b) OVER ()"
  2 "SELECT * FROM t1 GROUP BY sum(b) OVER ()"
  3 "SELECT * FROM t1 GROUP BY a HAVING sum(b) OVER ()"
} {
  do_catchsql_test 3.$tn $sql {1 {misuse of window function sum()}}
}

do_execsql_test 4.0 {
  CREATE TABLE t2(a, b, c);
  INSERT INTO t2 VALUES(0, 0, 0);
  INSERT INTO t2 VALUES(1, 1, 1);
  INSERT INTO t2 VALUES(2, 0, 2);
  INSERT INTO t2 VALUES(3, 1, 0);
  INSERT INTO t2 VALUES(4, 0, 1);
  INSERT INTO t2 VALUES(5, 1, 2);
  INSERT INTO t2 VALUES(6, 0, 0);
}

do_execsql_test 4.1 {
  SELECT a, sum(a) OVER (PARTITION BY b) FROM t2;
} {
  0 12  2 12  4 12  6 12   1  9  3  9  5  9 
}

do_execsql_test 4.2 {
  SELECT a, sum(a) OVER (PARTITION BY b) FROM t2 ORDER BY a;
} {
  0 12  1  9  2 12  3  9  4 12  5  9 6 12   
}

do_execsql_test 4.3 {
  SELECT a, sum(a) OVER () FROM t2 ORDER BY a;
} {
  0 21  1  21  2 21  3  21  4 21  5  21 6 21   
}

do_execsql_test 4.4 {
  SELECT a, sum(a) OVER (ORDER BY a) FROM t2;
} {
  0 0  1 1  2 3  3 6  4 10  5 15  6 21
}

do_execsql_test 4.5 {
  SELECT a, sum(a) OVER (PARTITION BY b ORDER BY a) FROM t2 ORDER BY a
} {
  0 0  1 1  2 2  3 4  4 6  5 9  6 12
}

do_execsql_test 4.6 {
  SELECT a, sum(a) OVER (PARTITION BY c ORDER BY a) FROM t2 ORDER BY a
} {
  0 0  1 1  2 2  3 3  4 5  5 7  6 9
}

do_execsql_test 4.7 {
  SELECT a, sum(a) OVER (PARTITION BY b ORDER BY a DESC) FROM t2 ORDER BY a
} {
  0 12  1 9  2 12  3 8  4 10  5 5  6 6
}

do_execsql_test 4.8 {
  SELECT a, 
    sum(a) OVER (PARTITION BY b ORDER BY a DESC),
    sum(a) OVER (PARTITION BY c ORDER BY a) 
  FROM t2 ORDER BY a
} {
  0  12  0
  1   9  1 
  2  12  2 
  3   8  3 
  4  10  5 
  5   5  7 
  6   6  9
}

do_execsql_test 4.9 {
  SELECT a, 
    sum(a) OVER (ORDER BY a), 
    avg(a) OVER (ORDER BY a) 
  FROM t2 ORDER BY a
} {
  0  0       0.0
  1  1       0.5
  2  3       1.0
  3  6       1.5
  4  10      2.0
  5  15      2.5
  6  21      3.0
}

do_execsql_test 4.10.1 {
  SELECT a, 
    count() OVER (ORDER BY a DESC),
    string_agg(a, '.') OVER (ORDER BY a DESC) 
  FROM t2 ORDER BY a DESC
} {
  6 1 6
  5 2 6.5
  4 3 6.5.4
  3 4 6.5.4.3
  2 5 6.5.4.3.2
  1 6 6.5.4.3.2.1
  0 7 6.5.4.3.2.1.0
}

do_execsql_test 4.10.2 {
  SELECT a, 
    count(*) OVER (ORDER BY a DESC),
    group_concat(a, '.') OVER (ORDER BY a DESC) 
  FROM t2 ORDER BY a DESC
} {
  6 1 6
  5 2 6.5
  4 3 6.5.4
  3 4 6.5.4.3
  2 5 6.5.4.3.2
  1 6 6.5.4.3.2.1
  0 7 6.5.4.3.2.1.0
}

do_catchsql_test 5.1 {
  SELECT ntile(0) OVER (ORDER BY a) FROM t2;
} {1 {argument of ntile must be a positive integer}}
do_catchsql_test 5.2 {
  SELECT ntile(-1) OVER (ORDER BY a) FROM t2;
} {1 {argument of ntile must be a positive integer}}
do_catchsql_test 5.3 {
  SELECT ntile('zbc') OVER (ORDER BY a) FROM t2;
} {1 {argument of ntile must be a positive integer}}
do_execsql_test 5.4 {
  CREATE TABLE t4(a, b);
  SELECT ntile(1) OVER (ORDER BY a) FROM t4;
} {}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 6.1 {
  CREATE TABLE t1(x);
  INSERT INTO t1 VALUES(7), (6), (5), (4), (3), (2), (1);

  CREATE TABLE t2(x);
  INSERT INTO t2 VALUES('b'), ('a');

  SELECT x, count(*) OVER (ORDER BY x) FROM t1;
} {1 1 2 2 3 3 4 4 5 5 6 6 7 7}

do_execsql_test 6.2 {
  SELECT * FROM t2, (SELECT x, count(*) OVER (ORDER BY x) FROM t1)
  ORDER BY 1, 2;
} {
  a 1 1 a 2 2 a 3 3 a 4 4 a 5 5 a 6 6 a 7 7
  b 1 1 b 2 2 b 3 3 b 4 4 b 5 5 b 6 6 b 7 7
}

do_catchsql_test 6.3 {
  SELECT x, lag(x) FILTER (WHERE (x%2)=0) OVER w FROM t1 
  WINDOW w AS (ORDER BY x)
} {1 {FILTER clause may only be used with aggregate window functions}}
 
#-------------------------------------------------------------------------
# Attempt to use a window function as an aggregate. And other errors.
#
reset_db
do_execsql_test 7.0 {
  CREATE TABLE t1(x, y);
  INSERT INTO t1 VALUES(1, 2);
  INSERT INTO t1 VALUES(3, 4);
  INSERT INTO t1 VALUES(5, 6);
  INSERT INTO t1 VALUES(7, 8);
  INSERT INTO t1 VALUES(9, 10);
}

do_catchsql_test 7.1.1 {
  SELECT nth_value(x, 1) FROM t1;
} {1 {misuse of window function nth_value()}}
do_catchsql_test 7.1.2 {
  SELECT * FROM t1 WHERE nth_value(x, 1) OVER (ORDER BY y);
} {1 {misuse of window function nth_value()}}
do_catchsql_test 7.1.3 {
  SELECT count(*) FROM t1 GROUP BY y HAVING nth_value(x, 1) OVER (ORDER BY y);
} {1 {misuse of window function nth_value()}}
do_catchsql_test 7.1.4 {
  SELECT count(*) FROM t1 GROUP BY nth_value(x, 1) OVER (ORDER BY y);
} {1 {misuse of window function nth_value()}}
do_catchsql_test 7.1.5 {
  SELECT count(*) FROM t1 LIMIT nth_value(x, 1) OVER ();
} {1 {no such column: x}}
do_catchsql_test 7.1.6 {
  SELECT trim(x) OVER (ORDER BY y) FROM t1;
} {1 {trim() may not be used as a window function}}
do_catchsql_test 7.1.7 {
  SELECT max(x) OVER abc FROM t1 WINDOW def AS (ORDER BY y);
} {1 {no such window: abc}}
do_catchsql_test 7.1.8 {
  SELECT row_number(x) OVER () FROM t1
} {1 {wrong number of arguments to function row_number()}}

do_execsql_test 7.2 {
  SELECT 
    lead(y) OVER win, 
    lead(y, 2) OVER win, 
    lead(y, 3, 'default') OVER win
  FROM t1
  WINDOW win AS (ORDER BY x)
} {
  4 6 8   6 8 10   8 10 default   10 {} default   {} {} default
}

do_execsql_test 7.3 {
  SELECT row_number() OVER (ORDER BY x) FROM t1
} {1 2 3 4 5}

do_execsql_test 7.4 {
  SELECT 
    row_number() OVER win,
    lead(x) OVER win
  FROM t1
  WINDOW win AS (ORDER BY x ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
} {1 3  2 5  3 7  4 9   5 {}}

#-------------------------------------------------------------------------
# Attempt to use a window function in a view.
#
do_execsql_test 8.0 {
  CREATE TABLE t3(a, b, c);

  WITH s(i) AS ( VALUES(1) UNION ALL SELECT i+1 FROM s WHERE i<6 )
  INSERT INTO t3 SELECT i, i, i FROM s;

  CREATE VIEW v1 AS SELECT
    sum(b) OVER (ORDER BY c),
    min(b) OVER (ORDER BY c),
    max(b) OVER (ORDER BY c)
  FROM t3;

  CREATE VIEW v2 AS SELECT
    sum(b) OVER win,
    min(b) OVER win,
    max(b) OVER win
  FROM t3
  WINDOW win AS (ORDER BY c);
}

do_execsql_test 8.1.1 {
  SELECT * FROM v1
} {1 1 1  3 1 2  6 1 3  10 1 4  15 1 5  21 1 6}
do_execsql_test 8.1.2 {
  SELECT * FROM v2
} {1 1 1  3 1 2  6 1 3  10 1 4  15 1 5  21 1 6}

db close
sqlite3 db test.db
do_execsql_test 8.2.1 {
  SELECT * FROM v1
} {1 1 1  3 1 2  6 1 3  10 1 4  15 1 5  21 1 6}
do_execsql_test 8.2.2 {
  SELECT * FROM v2
} {1 1 1  3 1 2  6 1 3  10 1 4  15 1 5  21 1 6}

#-------------------------------------------------------------------------
# Attempt to use a window function in a trigger.
#
do_execsql_test 9.0 {
  CREATE TABLE t4(x, y);
  INSERT INTO t4 VALUES(1, 'g');
  INSERT INTO t4 VALUES(2, 'i');
  INSERT INTO t4 VALUES(3, 'l');
  INSERT INTO t4 VALUES(4, 'g');
  INSERT INTO t4 VALUES(5, 'a');

  CREATE TABLE t5(x, y, m);
  CREATE TRIGGER t4i AFTER INSERT ON t4 BEGIN
    DELETE FROM t5;
    INSERT INTO t5 
      SELECT x, y, max(y) OVER xyz FROM t4
      WINDOW xyz AS (PARTITION BY (x%2) ORDER BY x);
  END;
}

do_execsql_test 9.1.1 {
  SELECT x, y, max(y) OVER xyz FROM t4
      WINDOW xyz AS (PARTITION BY (x%2) ORDER BY x) ORDER BY 1
} {1 g g   2 i i   3 l l   4 g i   5 a l}

do_execsql_test 9.1.2 {
  INSERT INTO t4 VALUES(6, 'm');
  SELECT x, y, max(y) OVER xyz FROM t4
      WINDOW xyz AS (PARTITION BY (x%2) ORDER BY x) ORDER BY 1
} {1 g g   2 i i   3 l l   4 g i   5 a l   6 m m}

do_execsql_test 9.1.3 {
  SELECT * FROM t5 ORDER BY 1
} {1 g g   2 i i   3 l l   4 g i   5 a l   6 m m}

do_execsql_test 9.2 {
  WITH aaa(x, y, z) AS (
    SELECT x, y, max(y) OVER xyz FROM t4
    WINDOW xyz AS (PARTITION BY (x%2) ORDER BY x)
  )
  SELECT * FROM aaa ORDER BY 1;
} {1 g g   2 i i   3 l l   4 g i   5 a l   6 m m}

do_execsql_test 9.3 {
  WITH aaa(x, y, z) AS (
    SELECT x, y, max(y) OVER xyz FROM t4
    WINDOW xyz AS (ORDER BY x)
  )
  SELECT *, min(z) OVER (ORDER BY x) FROM aaa ORDER BY 1;
} {1 g g g   2 i i g   3 l l g   4 g l g   5 a l g   6 m m g}

do_catchsql_test 9.4 {
  -- 2021-04-17 dbsqlfuzz d9cf66100064952b66951845dfab41de1c124611
  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1(a,b,c,d);
  DROP TABLE IF EXISTS t2;
  CREATE TABLE t2(x,y);
  CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN
    INSERT INTO t2(x,y)
      SELECT a, max(d) OVER w1 FROM t1
        WINDOW w1 AS (PARTITION BY EXISTS(SELECT 1 FROM t1 WHERE c=?1) );
  END;
} {1 {trigger cannot use variables}}

do_catchsql_test 9.4.2 {
  CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN
    INSERT INTO t1(a,b) 
        SELECT a, max(d) OVER w1 FROM t1
        WINDOW w1 AS (
          ORDER BY a ROWS BETWEEN ? PRECEDING AND UNBOUNDED FOLLOWING
        );
  END;
} {1 {trigger cannot use variables}}
do_catchsql_test 9.4.3 {
  CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN
    INSERT INTO t1(a,b) 
        SELECT a, max(d) OVER w1 FROM t1
        WINDOW w1 AS (
          ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND ? FOLLOWING
        );
  END;
} {1 {trigger cannot use variables}}

#-------------------------------------------------------------------------
#
do_execsql_test 10.0 {
  CREATE TABLE sales(emp TEXT PRIMARY KEY, region, total);
  INSERT INTO sales VALUES
      ('Alice',     'North', 34),
      ('Frank',     'South', 22),
      ('Charles',   'North', 45),
      ('Darrell',   'South', 8),
      ('Grant',     'South', 23),
      ('Brad' ,     'North', 22),
      ('Elizabeth', 'South', 99),
      ('Horace',    'East',   1);
}

# Best two salespeople from each region
#
do_execsql_test 10.1 {
  SELECT emp, region, total FROM (
    SELECT 
      emp, region, total,
      row_number() OVER (PARTITION BY region ORDER BY total DESC) AS rank
    FROM sales
  ) WHERE rank<=2 ORDER BY region, total DESC
} {
  Horace      East     1
  Charles     North   45
  Alice       North   34
  Elizabeth   South   99
  Grant       South   23
}

do_execsql_test 10.2 {
  SELECT emp, region, sum(total) OVER win FROM sales
  WINDOW win AS (PARTITION BY region ORDER BY total)
} {
  Horace East       1  
  Brad North       22 
  Alice North      56 
  Charles North   101 
  Darrell South     8 
  Frank South      30 
  Grant South      53 
  Elizabeth South 152
}

do_execsql_test 10.3 {
  SELECT emp, region, sum(total) OVER win FROM sales
  WINDOW win AS (PARTITION BY region ORDER BY total)
  LIMIT 5
} {
  Horace East       1  
  Brad North       22 
  Alice North      56 
  Charles North   101 
  Darrell South     8 
}

do_execsql_test 10.4 {
  SELECT emp, region, sum(total) OVER win FROM sales
  WINDOW win AS (PARTITION BY region ORDER BY total)
  LIMIT 5 OFFSET 2
} {
  Alice North      56 
  Charles North   101 
  Darrell South     8 
  Frank South      30 
  Grant South      53 
}

do_execsql_test 10.5 {
  SELECT emp, region, sum(total) OVER win FROM sales
  WINDOW win AS (
    PARTITION BY region ORDER BY total 
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
  )
} {
  Horace East       1  
  Brad North      101
  Alice North      79 
  Charles North    45 
  Darrell South   152
  Frank South     144 
  Grant South     122 
  Elizabeth South  99
}

do_execsql_test 10.6 {
  SELECT emp, region, sum(total) OVER win FROM sales
  WINDOW win AS (
    PARTITION BY region ORDER BY total 
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
  ) LIMIT 5 OFFSET 2
} {
  Alice North      79 
  Charles North    45 
  Darrell South   152
  Frank South     144 
  Grant South     122 
}

do_execsql_test 10.7 {
  SELECT emp, region, (
    SELECT sum(total) OVER (
      ORDER BY total RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) || outer.emp FROM sales
  ) FROM sales AS outer;
} {
  Alice North 254Alice 
  Frank South 254Frank 
  Charles North 254Charles 
  Darrell South 254Darrell 
  Grant South 254Grant 
  Brad North 254Brad 
  Elizabeth South 254Elizabeth 
  Horace East 254Horace
}

do_execsql_test 10.8 {
  SELECT emp, region, (
    SELECT sum(total) FILTER (WHERE sales.emp!=outer.emp) OVER (
      ORDER BY total RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) FROM sales
  ) FROM sales AS outer;
} {
  Alice North 220 
  Frank South 232 
  Charles North 209 
  Darrell South 246
  Grant South 231 
  Brad North 232 
  Elizabeth South 155 
  Horace East 253
}

#-------------------------------------------------------------------------
# Check that it is not possible to use a window function in a CREATE INDEX
# statement.
#
do_execsql_test 11.0 { CREATE TABLE t6(a, b, c); }

do_catchsql_test 11.1 {
  CREATE INDEX t6i ON t6(a) WHERE sum(b) OVER ();
} {1 {misuse of window function sum()}}
do_catchsql_test 11.2 {
  CREATE INDEX t6i ON t6(a) WHERE lead(b) OVER ();
} {1 {misuse of window function lead()}}

do_catchsql_test 11.3 {
  CREATE INDEX t6i ON t6(sum(b) OVER ());
} {1 {misuse of window function sum()}}
do_catchsql_test 11.4 {
  CREATE INDEX t6i ON t6(lead(b) OVER ());
} {1 {misuse of window function lead()}}

# 2018-09-17 ticket 510cde277783b5fb5de628393959849dff377eb3
# Endless loop on a query with window functions and a limit
#
do_execsql_test 12.100 {
  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1(id INT, b VARCHAR, c VARCHAR);
  INSERT INTO t1 VALUES(1, 'A', 'one');
  INSERT INTO t1 VALUES(2, 'B', 'two');
  INSERT INTO t1 VALUES(3, 'C', 'three');
  INSERT INTO t1 VALUES(4, 'D', 'one');
  INSERT INTO t1 VALUES(5, 'E', 'two');
  SELECT id, b, lead(c,1) OVER(ORDER BY c) AS x 
    FROM t1 WHERE id>1
   ORDER BY b LIMIT 1;
} {2 B two}
do_execsql_test 12.110 {
  INSERT INTO t1 VALUES(6, 'F', 'three');
  INSERT INTO t1 VALUES(7, 'G', 'one');
  SELECT id, b, lead(c,1) OVER(ORDER BY c) AS x
    FROM t1 WHERE id>1
   ORDER BY b LIMIT 2;
} {2 B two 3 C three}

#-------------------------------------------------------------------------

do_execsql_test 13.1 {
  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1(a int, b int);
  INSERT INTO t1 VALUES(1,11);
  INSERT INTO t1 VALUES(2,12);
}

do_execsql_test 13.2.1 {
  SELECT a, rank() OVER(ORDER BY b) FROM t1;
  SELECT a, rank() OVER(ORDER BY b DESC) FROM t1;
} {
  1 1   2 2   2 1   1 2
}
do_execsql_test 13.2.2 {
  SELECT a, rank() OVER(ORDER BY b) FROM t1
    UNION ALL
  SELECT a, rank() OVER(ORDER BY b DESC) FROM t1;
} {
  1 1   2 2   2 1   1 2
}
do_execsql_test 13.3 {
  SELECT a, rank() OVER(ORDER BY b) FROM t1
    UNION 
  SELECT a, rank() OVER(ORDER BY b DESC) FROM t1;
} {
  1 1   1 2   2 1   2 2  
}

do_execsql_test 13.4 {
  SELECT a, rank() OVER(ORDER BY b) FROM t1
    EXCEPT 
  SELECT a, rank() OVER(ORDER BY b DESC) FROM t1;
} {
  1 1   2 2 
}

do_execsql_test 13.5 {
  SELECT a, rank() OVER(ORDER BY b) FROM t1
    INTERSECT 
  SELECT a, rank() OVER(ORDER BY b DESC) FROM t1;
} {}

# 2018-12-06
# https://www.sqlite.org/src/info/f09fcd17810f65f7
# Assertion fault when window functions are used.
#
# Root cause is the query flattener invoking sqlite3ExprDup() on
# expressions that contain subqueries with window functions.  The
# sqlite3ExprDup() routine is not making correctly initializing
# Select.pWin field of the subqueries.
#
sqlite3 db :memory:
do_execsql_test 14.0 {
  SELECT * FROM(
    SELECT * FROM (SELECT 1 AS c) WHERE c IN (
        SELECT (row_number() OVER()) FROM (VALUES (0))
    )
  );
} {1}
do_execsql_test 14.1 {
  CREATE TABLE t1(x); INSERT INTO t1(x) VALUES(12345);
  CREATE TABLE t2(c); INSERT INTO t2(c) VALUES(1);
  SELECT y, y+1, y+2 FROM (
    SELECT c IN (
      SELECT (row_number() OVER()) FROM t1
    ) AS y FROM t2
  );
} {1 2 3}

# 2018-12-31
# https://www.sqlite.org/src/info/d0866b26f83e9c55
# Window function in correlated subquery causes assertion fault 
#
do_catchsql_test 15.0 {
  WITH t(id, parent) AS (
  SELECT CAST(1 AS INT), CAST(NULL AS INT)
  UNION ALL
  SELECT 2, NULL
  UNION ALL
  SELECT 3, 1
  UNION ALL
  SELECT 4, 1
  UNION ALL
  SELECT 5, 2
  UNION ALL
  SELECT 6, 2
  ), q AS (
  SELECT t.*, ROW_NUMBER() OVER (ORDER BY t.id) AS rn
    FROM t
   WHERE parent IS NULL
   UNION ALL
  SELECT t.*, ROW_NUMBER() OVER (ORDER BY t.id) AS rn
    FROM q
    JOIN t
      ON t.parent = q.id
  )
  SELECT *
    FROM q;
} {1 {cannot use window functions in recursive queries}}
do_execsql_test 15.1 {
  DROP TABLE IF EXISTS t1;
  DROP TABLE IF EXISTS t2;
  CREATE TABLE t1(x);
  INSERT INTO t1 VALUES('a'), ('b'), ('c');
  CREATE TABLE t2(a, b);
  INSERT INTO t2 VALUES('X', 1), ('X', 2), ('Y', 2), ('Y', 3);
  SELECT x, (
    SELECT sum(b)
      OVER (PARTITION BY a ROWS BETWEEN UNBOUNDED PRECEDING
                                    AND UNBOUNDED FOLLOWING)
    FROM t2 WHERE b<x
  ) FROM t1;
} {a 3 b 3 c 3}

do_execsql_test 15.2 {
  SELECT(
    WITH c AS(
      VALUES(1)
    ) SELECT '' FROM c,c
  ) x WHERE x+x;
} {}

#-------------------------------------------------------------------------

do_execsql_test 16.0 {
  CREATE TABLE t7(a,b); 
  INSERT INTO t7(rowid, a, b) VALUES
      (1, 1, 3),
      (2, 10, 4),
      (3, 100, 2);
}

do_execsql_test 16.1 {
  SELECT rowid, sum(a) OVER (PARTITION BY b IN (SELECT rowid FROM t7)) FROM t7;
} {
  2 10
  1 101
  3 101
}

do_execsql_test 16.2 {
  SELECT rowid, sum(a) OVER w1 FROM t7 
  WINDOW w1 AS (PARTITION BY b IN (SELECT rowid FROM t7));
} {
  2 10
  1 101
  3 101
}

#-------------------------------------------------------------------------
do_execsql_test 17.0 {
  CREATE TABLE t8(a);
  INSERT INTO t8 VALUES(1), (2), (3);
}

do_execsql_test 17.1 {
  SELECT +sum(0) OVER () ORDER BY +sum(0) OVER ();
} {0}

do_execsql_test 17.2 {
  select +sum(a) OVER () FROM t8 ORDER BY +sum(a) OVER () DESC;
} {6 6 6}

do_execsql_test 17.3 {
  SELECT 10+sum(a) OVER (ORDER BY a) 
  FROM t8 
  ORDER BY 10+sum(a) OVER (ORDER BY a) DESC;
} {16 13 11}


#-------------------------------------------------------------------------
# Test error cases from chaining window definitions.
#
reset_db
do_execsql_test 18.0 {
  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c TEXT, d INTEGER);
  INSERT INTO t1 VALUES(1, 'odd',  'one',   1);
  INSERT INTO t1 VALUES(2, 'even', 'two',   2);
  INSERT INTO t1 VALUES(3, 'odd',  'three', 3);
  INSERT INTO t1 VALUES(4, 'even', 'four',  4);
  INSERT INTO t1 VALUES(5, 'odd',  'five',  5);
  INSERT INTO t1 VALUES(6, 'even', 'six',   6);
}

foreach {tn sql error} {
  1 {
    SELECT c, sum(d) OVER win2 FROM t1
      WINDOW win1 AS (ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING), 
             win2 AS (win1 ORDER BY b)
  } {cannot override frame specification of window: win1}

  2 {
    SELECT c, sum(d) OVER win2 FROM t1
      WINDOW win1 AS (),
             win2 AS (win4 ORDER BY b)
  } {no such window: win4}

  3 {
    SELECT c, sum(d) OVER win2 FROM t1
      WINDOW win1 AS (),
             win2 AS (win1 PARTITION BY d)
  } {cannot override PARTITION clause of window: win1}

  4 {
    SELECT c, sum(d) OVER win2 FROM t1
      WINDOW win1 AS (ORDER BY b),
             win2 AS (win1 ORDER BY d)
  } {cannot override ORDER BY clause of window: win1}
} {
  do_catchsql_test 18.1.$tn $sql [list 1 $error]
}

foreach {tn sql error} {
  1 {
    SELECT c, sum(d) OVER (win1 ORDER BY b) FROM t1
      WINDOW win1 AS (ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
  } {cannot override frame specification of window: win1}

  2 {
    SELECT c, sum(d) OVER (win4 ORDER BY b) FROM t1
      WINDOW win1 AS ()
  } {no such window: win4}

  3 {
    SELECT c, sum(d) OVER (win1 PARTITION BY d) FROM t1
      WINDOW win1 AS ()
  } {cannot override PARTITION clause of window: win1}

  4 {
    SELECT c, sum(d) OVER (win1 ORDER BY d) FROM t1
      WINDOW win1 AS (ORDER BY b)
  } {cannot override ORDER BY clause of window: win1}
} {
  do_catchsql_test 18.2.$tn $sql [list 1 $error]
}

do_execsql_test 18.3.1 {
  SELECT string_agg(c, '.') OVER (PARTITION BY b ORDER BY c)
  FROM t1
} {four four.six four.six.two five five.one five.one.three}

do_execsql_test 18.3.2 {
  SELECT group_concat(c, '.') OVER (win1 ORDER BY c)
  FROM t1
  WINDOW win1 AS (PARTITION BY b)
} {four four.six four.six.two five five.one five.one.three}

do_execsql_test 18.3.3 {
  SELECT string_agg(c, '.') OVER win2
  FROM t1
  WINDOW win1 AS (PARTITION BY b),
         win2 AS (win1 ORDER BY c)
} {four four.six four.six.two five five.one five.one.three}

do_execsql_test 18.3.4 {
  SELECT group_concat(c, '.') OVER (win2)
  FROM t1
  WINDOW win1 AS (PARTITION BY b),
         win2 AS (win1 ORDER BY c)
} {four four.six four.six.two five five.one five.one.three}

do_execsql_test 18.3.5 {
  SELECT string_agg(c, '.') OVER win5
  FROM t1
  WINDOW win1 AS (PARTITION BY b),
         win2 AS (win1),
         win3 AS (win2),
         win4 AS (win3),
         win5 AS (win4 ORDER BY c)
} {four four.six four.six.two five five.one five.one.three}

#-------------------------------------------------------------------------
# Test RANGE <expr> PRECEDING/FOLLOWING when there are string, blob
# and NULL values in the dataset.
#
reset_db
do_execsql_test 19.0 {
  CREATE TABLE t1(a, b);
  INSERT INTO t1 VALUES
    (1, 1), (2, 2), (3, 3), (4, 4), (5, 5),
    ('a', 6), ('b', 7), ('c', 8), ('d', 9), ('e', 10);
}
do_execsql_test 19.1 {
  SELECT a, sum(b) OVER (ORDER BY a) FROM t1;
} {1 1  2 3  3 6  4 10  5 15  a 21 b 28 c 36 d 45 e 55}

do_execsql_test 19.2.1 {
  SELECT a, sum(b) OVER (
    ORDER BY a RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) FROM t1;
} {1 3 2 6 3 9 4 12 5 9 a 6 b 7 c 8 d 9 e 10}
do_execsql_test 19.2.2 {
  SELECT a, sum(b) OVER (
    ORDER BY a DESC RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) FROM t1 ORDER BY a ASC;
} {1 3 2 6 3 9 4 12 5 9 a 6 b 7 c 8 d 9 e 10}

do_execsql_test 19.3.1 {
  SELECT a, sum(b) OVER (
    ORDER BY a RANGE BETWEEN 2 PRECEDING AND 1 FOLLOWING
  ) FROM t1;
} {1 3 2 6 3 10 4 14 5 12 a 6 b 7 c 8 d 9 e 10}
do_execsql_test 19.3.2 {
  SELECT a, sum(b) OVER (
    ORDER BY a DESC RANGE BETWEEN 1 PRECEDING AND 2 FOLLOWING
  ) FROM t1 ORDER BY a ASC;
} {1 3 2 6 3 10 4 14 5 12 a 6 b 7 c 8 d 9 e 10}


reset_db
do_execsql_test 20.0 {
  CREATE TABLE t1(a, b);
  INSERT INTO t1 VALUES
    (NULL, 100), (NULL, 100), 
    (1, 1), (2, 2), (3, 3), (4, 4), (5, 5),
    ('a', 6), ('b', 7), ('c', 8), ('d', 9), ('e', 10);
}
do_execsql_test 20.1 {
  SELECT a, sum(b) OVER (ORDER BY a) FROM t1;
} {
  {} 200 {} 200 1 201  2 203  3 206  4 210  5 215  
  a 221 b 228 c 236 d 245 e 255
}

do_execsql_test 20.2.1 {
  SELECT a, sum(b) OVER (
    ORDER BY a RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) FROM t1;
} {{} 200 {} 200 1 3 2 6 3 9 4 12 5 9 a 6 b 7 c 8 d 9 e 10}
do_execsql_test 20.2.2 {
  SELECT a, sum(b) OVER (
    ORDER BY a DESC RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) FROM t1 ORDER BY a ASC;
} {{} 200 {} 200 1 3 2 6 3 9 4 12 5 9 a 6 b 7 c 8 d 9 e 10}

do_execsql_test 20.3.1 {
  SELECT a, sum(b) OVER (
    ORDER BY a RANGE BETWEEN 2 PRECEDING AND 1 FOLLOWING
  ) FROM t1;
} {{} 200 {} 200 1 3 2 6 3 10 4 14 5 12 a 6 b 7 c 8 d 9 e 10}
do_execsql_test 20.3.2 {
  SELECT a, sum(b) OVER (
    ORDER BY a DESC RANGE BETWEEN 1 PRECEDING AND 2 FOLLOWING
  ) FROM t1 ORDER BY a ASC;
} {{} 200 {} 200 1 3 2 6 3 10 4 14 5 12 a 6 b 7 c 8 d 9 e 10}

#-------------------------------------------------------------------------
do_execsql_test 21.0 {
  CREATE TABLE keyword_tab(
    current, exclude, filter, following, groups, no, others, over,
    partition, preceding, range, ties, unbounded, window
  );
}
do_execsql_test 21.1 {
  SELECT
    current, exclude, filter, following, groups, no, others, over,
    partition, preceding, range, ties, unbounded, window
  FROM keyword_tab
}

#-------------------------------------------------------------------------
foreach {tn expr err} {
  1   4.5      0
  2   NULL     1
  3   0.0      0
  4   0.1      0
  5  -0.1      1
  6  ''        1
  7  '2.0'     0
  8  '2.0x'    1
  9  x'1234'   1
 10  '1.2'     0
} {
  set res {0 1}
  if {$err} {set res {1 {frame starting offset must be a non-negative number}} }
  do_catchsql_test 22.$tn.1 "
    WITH a(x, y) AS ( VALUES(1, 2) )
    SELECT sum(x) OVER (
      ORDER BY y RANGE BETWEEN $expr PRECEDING AND UNBOUNDED FOLLOWING
    ) FROM a
  " $res

  set res {0 1}
  if {$err} {set res {1 {frame ending offset must be a non-negative number}} }
  do_catchsql_test 22.$tn.2 "
    WITH a(x, y) AS ( VALUES(1, 2) )
    SELECT sum(x) OVER (
      ORDER BY y RANGE BETWEEN UNBOUNDED PRECEDING AND $expr FOLLOWING
    ) FROM a
  " $res
}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 23.0 {
  CREATE TABLE t5(a, b, c);
  CREATE INDEX t5ab ON t5(a, b);
}

proc do_ordercount_test {tn sql nOrderBy} {
  set plan [execsql "EXPLAIN QUERY PLAN $sql"]
  uplevel [list do_test $tn [list regexp -all ORDER $plan] $nOrderBy]
}

do_ordercount_test 23.1 {
  SELECT 
    sum(c) OVER (ORDER BY a, b),
    sum(c) OVER (PARTITION BY a ORDER BY b)
  FROM t5
} 0

do_ordercount_test 23.2 {
  SELECT 
    sum(c) OVER (ORDER BY b, a),
    sum(c) OVER (PARTITION BY b ORDER BY a)
  FROM t5
} 1

do_ordercount_test 23.3 {
  SELECT 
    sum(c) OVER (ORDER BY b, a),
    sum(c) OVER (ORDER BY c, b)
  FROM t5
} 2

do_ordercount_test 23.4 {
  SELECT 
    sum(c) OVER (ORDER BY b ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
    sum(c) OVER (ORDER BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
    sum(c) OVER (ORDER BY b GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
  FROM t5
} 1

do_ordercount_test 23.5 {
  SELECT 
    sum(c) OVER (ORDER BY b+1 ROWS UNBOUNDED PRECEDING),
    sum(c) OVER (ORDER BY b+1 RANGE UNBOUNDED PRECEDING),
    sum(c) OVER (ORDER BY b+1 GROUPS UNBOUNDED PRECEDING)
  FROM t5
} 1

do_ordercount_test 23.6 {
  SELECT 
    sum(c) OVER (ORDER BY b+1 ROWS UNBOUNDED PRECEDING),
    sum(c) OVER (ORDER BY b+2 RANGE UNBOUNDED PRECEDING),
    sum(c) OVER (ORDER BY b+3 GROUPS UNBOUNDED PRECEDING)
  FROM t5
} 3

do_execsql_test 24.1 {
  SELECT sum(44) OVER ()
} {44}

do_execsql_test 24.2 {
  SELECT lead(44) OVER ()
} {{}}

#-------------------------------------------------------------------------
#
reset_db
do_execsql_test 25.0 {
  CREATE TABLE t1 ( t1_id INTEGER PRIMARY KEY );
  CREATE TABLE t2 ( t2_id INTEGER PRIMARY KEY );
  CREATE TABLE t3 ( t3_id INTEGER PRIMARY KEY );

  INSERT INTO t1 VALUES(1),  (3), (5);
  INSERT INTO t2 VALUES      (3), (5);
  INSERT INTO t3 VALUES(10), (11), (12);
}

do_execsql_test 25.1 {
  SELECT t1.* FROM t1, t2 WHERE 
    t1_id=t2_id AND t1_id IN (
        SELECT t1_id + row_number() OVER ( ORDER BY t1_id ) FROM t3
    )
}

do_execsql_test 25.2 {
  SELECT t1.* FROM t1, t2 WHERE 
    t1_id=t2_id AND t1_id IN (
        SELECT         row_number() OVER ( ORDER BY t1_id ) FROM t3
    )
} {3}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 26.0 {
  CREATE TABLE t1(x);
  CREATE TABLE t2(c);
}

do_execsql_test 26.1 {
  SELECT ( SELECT row_number() OVER () FROM ( SELECT c FROM t1 ) ) FROM t2
} {}

do_execsql_test 26.2 {
  INSERT INTO t1 VALUES(1), (2), (3), (4);
  INSERT INTO t2 VALUES(2), (6), (8), (4);
  SELECT c, c IN ( 
    SELECT row_number() OVER () FROM ( SELECT c FROM t1 )
  ) FROM t2
} {2 1  6 0  8 0  4 1}

do_execsql_test 26.3 {
  DELETE FROM t1;
  DELETE FROM t2;

  INSERT INTO t2 VALUES(1), (2), (3), (4);
  INSERT INTO t1 VALUES(1), (1), (2), (3), (3), (3), (3), (4), (4);

  SELECT c, c IN ( 
    SELECT row_number() OVER () FROM ( SELECT 1 FROM t1 WHERE x=c )
  ) FROM t2
} {1 1  2 0  3 1  4 0}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 27.0 {
  CREATE TABLE t1(x);
  INSERT INTO t1 VALUES(NULL), (1), (2), (3), (4), (5);
}
do_execsql_test 27.1 {
  SELECT min(x) FROM t1;
} {1}
do_execsql_test 27.2 {
  SELECT min(x) OVER win FROM t1
  WINDOW win AS (ORDER BY rowid ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
} {1 1 1 2 3 4}

#-------------------------------------------------------------------------

reset_db
do_execsql_test 28.1.1 {
  CREATE TABLE t1(a INTEGER PRIMARY KEY, b CHAR(1), c CHAR(2), d ANY);
  INSERT INTO t1 VALUES (3, 'C', 'cc', 1.0);
  INSERT INTO t1 VALUES (13,'M', 'cc', NULL);
}

do_execsql_test 28.1.2 {
  SELECT string_agg(b,'') OVER w1 FROM t1
    WINDOW w1 AS (ORDER BY a RANGE BETWEEN 3 PRECEDING AND 1 PRECEDING)
} {
  {} {}
}

do_execsql_test 28.2.1 {
  CREATE TABLE t2(a TEXT, b INTEGER);
  INSERT INTO t2 VALUES('A', NULL);
  INSERT INTO t2 VALUES('B', NULL);
}

do_execsql_test 28.2.1 {
  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1(a INTEGER PRIMARY KEY, b CHAR(1), c CHAR(2), d ANY);
  INSERT INTO t1 VALUES
    (10,'J', 'cc', NULL),
    (11,'K', 'cc', 'xyz'),
    (13,'M', 'cc', NULL);
}

do_execsql_test 28.2.2 {
  SELECT a, b, c, quote(d), group_concat(b,'') OVER w1, '|' FROM t1
    WINDOW w1 AS
    (ORDER BY d DESC RANGE BETWEEN 7.0 PRECEDING AND 2.5 PRECEDING)
    ORDER BY c, d, a;
} {
  10 J cc NULL JM |
  13 M cc NULL JM | 
  11 K cc 'xyz' K |
}

#-------------------------------------------------------------------------
reset_db

do_execsql_test 29.1 {
  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1(a INTEGER PRIMARY KEY, b CHAR(1), c CHAR(2), d ANY);
  INSERT INTO t1 VALUES
    (1, 'A', 'aa', 2.5),
    (2, 'B', 'bb', 3.75),
    (3, 'C', 'cc', 1.0),
    (4, 'D', 'cc', 8.25),
    (5, 'E', 'bb', 6.5),
    (6, 'F', 'aa', 6.5),
    (7, 'G', 'aa', 6.0),
    (8, 'H', 'bb', 9.0),
    (9, 'I', 'aa', 3.75),
    (10,'J', 'cc', NULL),
    (11,'K', 'cc', 'xyz'),
    (12,'L', 'cc', 'xyZ'),
    (13,'M', 'cc', NULL);
}

do_execsql_test 29.2 {
  SELECT a, b, c, quote(d), group_concat(b,'') OVER w1, '|' FROM t1
    WINDOW w1 AS
    (PARTITION BY c ORDER BY d DESC
     RANGE BETWEEN 7.0 PRECEDING AND 2.5 PRECEDING)
    ORDER BY c, d, a;
} {
  1 A aa 2.5 FG | 
  9 I aa 3.75 F | 
  7 G aa 6 {} | 
  6 F aa 6.5 {} | 
  2 B bb 3.75 HE |
  5 E bb 6.5 H | 
  8 H bb 9 {} | 
  10 J cc NULL JM | 
  13 M cc NULL JM | 
  3 C cc 1 {} | 
  4 D cc 8.25 {} | 
  12 L cc 'xyZ' L | 
  11 K cc 'xyz' K |
}

# 2019-07-18
# Check-in [7ef7b23cbb1b9ace] (which was itself a fix for ticket
# https://www.sqlite.org/src/info/1be72aab9) introduced a new problem
# if the LHS of a BETWEEN operator is a WINDOW function.  The problem
# was found by (the recently enhanced) dbsqlfuzz.
#
do_execsql_test 30.0 {
  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1(a, b, c);
  INSERT INTO t1 VALUES('BB','aa',399);
  SELECT
    count () OVER win1 NOT BETWEEN 'a' AND 'mmm',
    count () OVER win3
  FROM t1
  WINDOW win1 AS (ORDER BY a GROUPS BETWEEN 4 PRECEDING AND 1 FOLLOWING
                  EXCLUDE CURRENT ROW),
         win2 AS (PARTITION BY b ORDER BY a),
         win3 AS (win2 RANGE BETWEEN 5.2 PRECEDING AND true PRECEDING );
} {1 1}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 31.1 {
  CREATE TABLE t1(a, b);
  CREATE TABLE t2(c, d);
  CREATE TABLE t3(e, f);

  INSERT INTO t1 VALUES(1, 1);
  INSERT INTO t2 VALUES(1, 1);
  INSERT INTO t3 VALUES(1, 1);
}

do_execsql_test 31.2 {
  SELECT d IN (SELECT sum(c) OVER (ORDER BY e+c) FROM t3) FROM (
    SELECT * FROM t2
  );
} {1}

do_execsql_test 31.3 {
  SELECT d IN (SELECT sum(c) OVER (PARTITION BY d ORDER BY e+c) FROM t3) FROM (
    SELECT * FROM t2
  );
} {1}

do_catchsql_test 31.3 {
  SELECT d IN (
    SELECT sum(c) OVER ( ROWS BETWEEN d FOLLOWING AND UNBOUNDED FOLLOWING) 
    FROM t3
  )
  FROM (
    SELECT * FROM t2
  );
} {1 {frame starting offset must be a non-negative integer}}

do_catchsql_test 31.3 {
  SELECT d IN (
    SELECT sum(c) OVER ( ROWS BETWEEN CURRENT ROW AND c FOLLOWING) 
    FROM t3
  )
  FROM (
    SELECT * FROM t2
  );
} {1 {frame ending offset must be a non-negative integer}}

# 2019-11-16 chromium issue 1025467
ifcapable altertable {
  db close
  sqlite3 db :memory:
  do_catchsql_test 32.10 {
    CREATE VIEW a AS SELECT NULL INTERSECT SELECT NULL ORDER BY s() OVER R;
    CREATE TABLE a0 AS SELECT 0;
    ALTER TABLE a0 RENAME TO S;
  } {1 {error in view a: 1st ORDER BY term does not match any column in the result set}}
}

reset_db
do_execsql_test 33.1 {
  CREATE TABLE t1(aa, bb);
  INSERT INTO t1 VALUES(1, 2);
  INSERT INTO t1 VALUES(5, 6);
  CREATE TABLE t2(x);
  INSERT INTO t2 VALUES(1);
}
do_execsql_test 33.2 {
  SELECT (SELECT DISTINCT sum(aa) OVER() FROM t1 ORDER BY 1), x FROM t2 
  ORDER BY 1;
} {6 1}

reset_db
do_execsql_test 34.1 {
  CREATE TABLE t1(a,b,c);
}
do_execsql_test 34.2 {
  SELECT avg(a) OVER (
      ORDER BY (SELECT sum(b) OVER ()
        FROM t1 ORDER BY (
          SELECT total(d) OVER (ORDER BY c)
          FROM (SELECT 1 AS d) ORDER BY 1
          )
        )
      )
  FROM t1;
}

#-------------------------------------------------------------------------
reset_db
do_catchsql_test 35.0 {
  SELECT * WINDOW f AS () ORDER BY name COLLATE nocase;
} {1 {no tables specified}}

do_catchsql_test 35.1 {
  VALUES(1) INTERSECT SELECT * WINDOW f AS () ORDER BY x COLLATE nocase;
} {1 {no tables specified}}

do_execsql_test 35.2 {
  CREATE TABLE t1(x);
  INSERT INTO t1 VALUES(1), (2), (3);
  VALUES(1) INTERSECT 
  SELECT sum(x) OVER f FROM t1 WINDOW f AS (ORDER BY x) ORDER BY 1;
} {1}

do_execsql_test 35.3 {
  VALUES(8) EXCEPT 
  SELECT sum(x) OVER f FROM t1 WINDOW f AS (ORDER BY x) ORDER BY 1;
} {8}

do_execsql_test 35.4 {
  VALUES(1) UNION 
  SELECT sum(x) OVER f FROM t1 WINDOW f AS (ORDER BY x) ORDER BY 1;
} {1 3 6}

# 2019-12-07 gramfuzz find
#
do_execsql_test 36.10 {
  VALUES(count(*)OVER());
} {1}
do_execsql_test 36.20 {
  VALUES(count(*)OVER()),(2);
} {1 2}
do_execsql_test 36.30 {
  VALUES(2),(count(*)OVER());
} {2 1}
do_execsql_test 36.40 {
  VALUES(2),(3),(count(*)OVER()),(4),(5);
} {2 3 1 4 5}

# 2019-12-17 crash test case found by Yongheng and Rui
# See check-in 1ca0bd982ab1183b
#
reset_db
do_execsql_test 37.10 {
  CREATE TABLE t0(a UNIQUE, b PRIMARY KEY);
  CREATE VIEW v0(c) AS SELECT max((SELECT count(a)OVER(ORDER BY 1))) FROM t0;
  SELECT c FROM v0 WHERE c BETWEEN 10 AND 20;
} {}
do_execsql_test 37.20 {
  DROP VIEW v0;
  CREATE VIEW v0(c) AS SELECT max((SELECT count(a)OVER(ORDER BY 1234))) FROM t0;
  SELECT c FROM v0 WHERE c BETWEEN -10 AND 20;
} {}

# 2019-12-20 mrigger reported problem with a FILTER clause on an aggregate
# in a join.
#
reset_db
do_catchsql_test 38.10 {
  CREATE TABLE t0(c0);
  CREATE TABLE t1(c0, c1 UNIQUE);
  INSERT INTO t0(c0) VALUES(1);
  INSERT INTO t1(c0,c1) VALUES(2,3);
  SELECT COUNT(*) FROM t0, t1 WHERE (SELECT AVG(0) FILTER(WHERE t1.c1));
} {1 {misuse of aggregate: AVG()}}
do_execsql_test 38.20 {
  SELECT COUNT(*), AVG(1) FILTER(WHERE t1.c1) FROM t0, t1;
} {1 1.0}
do_catchsql_test 38.30 {
  SELECT COUNT(*) FROM t0, t1 WHERE (SELECT AVG(1) FILTER(WHERE t1.c1));
} {1 {misuse of aggregate: AVG()}}

reset_db
do_execsql_test 39.1 {
  CREATE TABLE t0(c0 UNIQUE);
}
do_execsql_test 39.2 {
  SELECT FIRST_VALUE(0) OVER();
} {0}
do_execsql_test 39.3 {
  SELECT * FROM t0 WHERE(c0, 0) IN(SELECT FIRST_VALUE(0) OVER(), 0);
}
do_execsql_test 39.4 {
  SELECT * FROM t0 WHERE (t0.c0, 1) IN(SELECT NTILE(1) OVER(), 0 FROM t0);
}

ifcapable rtree {
  # 2019-12-25 ticket d87336c81c7d0873
  #
  reset_db
  do_catchsql_test 40.1 {
    CREATE VIRTUAL TABLE t0 USING rtree(c0, c1, c2);
    SELECT * FROM t0
     WHERE ((0,0) IN (SELECT COUNT(*),LAG(5)OVER(PARTITION BY 0) FROM t0),0)<=(c1,0);
  } {0 {}}
}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 41.1 {
  CREATE TABLE t1(a, b, c);
  INSERT INTO t1 VALUES(NULL,'bb',355);
  INSERT INTO t1 VALUES('CC','aa',158);
  INSERT INTO t1 VALUES('GG','bb',929);
  INSERT INTO t1 VALUES('FF','Rb',574);
}

do_execsql_test 41.2 {
  SELECT min(c) OVER (
    ORDER BY a RANGE BETWEEN 5.2 PRECEDING AND 0.1 PRECEDING
  ) FROM t1
} {355 158 574 929}

do_execsql_test 41.2 {
  SELECT min(c) OVER (
    ORDER BY a RANGE BETWEEN 5.2 PRECEDING AND 0.1 PRECEDING
  ) << 100 FROM t1
} {0 0 0 0}

do_execsql_test 41.3 {
  SELECT
    min(c) OVER win3 << first_value(c) OVER win3,
    min(c) OVER win3 << first_value(c) OVER win3
  FROM t1
  WINDOW win3 AS (
    PARTITION BY 6 ORDER BY a RANGE BETWEEN 5.2 PRECEDING AND 0.1 PRECEDING
  );
} {0 0  0 0  0 0  0 0}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 42.1 {
  CREATE TABLE t1(a, b, c);
  INSERT INTO t1 VALUES(1, 1, 1);
  INSERT INTO t1 VALUES(2, 2, 2);
}
do_execsql_test 42.2 {
  SELECT * FROM t1 WHERE (0, 0) IN ( SELECT count(*), 0 FROM t1 )
} {}
do_execsql_test 42.3 {
  SELECT * FROM t1 WHERE (2, 0) IN ( SELECT count(*), 0 FROM t1 )
} {1 1 1 2 2 2}

do_execsql_test 42.3 {
  SELECT count(*), max(a) OVER () FROM t1 GROUP BY c; 
} {1 2 1 2}

do_execsql_test 42.4 {
  SELECT sum(a), max(b) OVER () FROM t1;
} {3 1}

do_execsql_test 42.5 {
  CREATE TABLE t2(a, b);
  INSERT INTO t2 VALUES('a', 1);
  INSERT INTO t2 VALUES('a', 2);
  INSERT INTO t2 VALUES('a', 3);
  INSERT INTO t2 VALUES('b', 4);
  INSERT INTO t2 VALUES('b', 5);
  INSERT INTO t2 VALUES('b', 6);
}

do_execsql_test 42.6 {
  SELECT a, sum(b), sum( sum(b) ) OVER (ORDER BY a) FROM t2 GROUP BY a;
} {a 6 6   b 15 21}

do_execsql_test 42.7 {
  SELECT sum(b), sum( sum(b) ) OVER (ORDER BY a) FROM t2;
} {21 21}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 43.1.1 {
  CREATE TABLE t1(x INTEGER PRIMARY KEY);
  INSERT INTO t1 VALUES (10);
}
do_catchsql_test 43.1.2 {
  SELECT count() OVER() AS m FROM t1 ORDER BY (SELECT m);
} {1 {misuse of aliased window function m}}

reset_db
do_execsql_test 43.2.1 {
  CREATE TABLE t1(a INTEGER PRIMARY KEY, b INTEGER);
  INSERT INTO t1(a, b) VALUES(1,  10); -- 10
  INSERT INTO t1(a, b) VALUES(2,  15); -- 25
  INSERT INTO t1(a, b) VALUES(3,  -5); -- 20
  INSERT INTO t1(a, b) VALUES(4,  -5); -- 15
  INSERT INTO t1(a, b) VALUES(5,  20); -- 35
  INSERT INTO t1(a, b) VALUES(6, -11); -- 24
}

do_execsql_test 43.2.2 {
  SELECT a, sum(b) OVER (ORDER BY a) AS abc FROM t1 ORDER BY 2
} {
  1 10   4 15   3 20   6 24   2 25   5 35
}

do_execsql_test 43.2.3 {
  SELECT a, sum(b) OVER (ORDER BY a) AS abc FROM t1 ORDER BY abc
} {
  1 10   4 15   3 20   6 24   2 25   5 35
}

do_execsql_test 43.2.4 {
  SELECT a, sum(b) OVER (ORDER BY a) AS abc FROM t1 ORDER BY abc+5
} {
  1 10   4 15   3 20   6 24   2 25   5 35
}

do_catchsql_test 43.2.5 {
  SELECT a, sum(b) OVER (ORDER BY a) AS abc FROM t1 ORDER BY (SELECT abc)
} {1 {misuse of aliased window function abc}}

do_catchsql_test 43.2.6 {
  SELECT a, 1+sum(b) OVER (ORDER BY a) AS abc FROM t1 ORDER BY (SELECT abc)
} {1 {misuse of aliased window function abc}}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 44.1 {
  CREATE TABLE t0(c0);
}

do_catchsql_test 44.2.1 {
  SELECT ntile(0) OVER ();
} {1 {argument of ntile must be a positive integer}}
do_catchsql_test 44.2.2 {
  SELECT (0, 0) IN(SELECT MIN(c0), NTILE(0) OVER()) FROM t0;
} {1 {argument of ntile must be a positive integer}}

do_execsql_test 44.3.1 {
  SELECT ntile(1) OVER ();
} {1}
do_execsql_test 44.3.2 {
  SELECT (0, 0) IN(SELECT MIN(c0), NTILE(1) OVER()) FROM t0;
} {0}

do_execsql_test 44.4.2 {
  INSERT INTO t0 VALUES(2), (1), (0);
  SELECT (0, 1) IN(SELECT MIN(c0), NTILE(1) OVER()) FROM t0;
} {1}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 45.1 {
  CREATE TABLE t0(x);
  CREATE TABLE t1(a);
  INSERT INTO t1 VALUES(1000);
  INSERT INTO t1 VALUES(1000);
  INSERT INTO t0 VALUES(10000);
}
do_execsql_test 45.2 {
  SELECT * FROM (
      SELECT sum (a) OVER() FROM t1 UNION ALL SELECT x FROM t0
  );
} {2000 2000 10000}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 46.1 {
  CREATE TABLE t1 (a);
  CREATE INDEX i1 ON t1(a);

  INSERT INTO t1 VALUES (10);
}

do_execsql_test 46.2 {
  SELECT (SELECT sum(a) OVER(ORDER BY a)) FROM t1
} 10

do_execsql_test 46.3 {
  SELECT * FROM t1 WHERE (SELECT sum(a) OVER(ORDER BY a));
} 10

do_execsql_test 46.4 {
  SELECT * FROM t1 NATURAL JOIN t1
    WHERE a=1
    OR ((SELECT sum(a)OVER(ORDER BY a)) AND a<=10)
} 10

#-------------------------------------------------------------------------
reset_db
do_execsql_test 47.0 {
  CREATE TABLE t1(
      a,
      e,
      f,
      g UNIQUE,
      h UNIQUE
  );
}

do_execsql_test 47.1 {
  CREATE VIEW t2(k) AS
     SELECT e FROM t1 WHERE g = 'abc' OR h BETWEEN 10 AND f;
}

do_catchsql_test 47.2 {
  SELECT 234 FROM t2
    WHERE k=1
    OR (SELECT k FROM t2 WHERE (SELECT sum(a) OVER() FROM t1 GROUP BY 1));
} {1 {misuse of window function sum()}}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 48.0 {
  CREATE TABLE t1(a);
  INSERT INTO t1 VALUES(1);
  INSERT INTO t1 VALUES(2);
  INSERT INTO t1 VALUES(3);
  SELECT (SELECT max(x)OVER(ORDER BY x) + min(x)OVER(ORDER BY x))
    FROM (SELECT (SELECT sum(a) FROM t1) AS x FROM t1);
} {12 12 12}

do_execsql_test 48.1 {
  SELECT (SELECT max(x)OVER(ORDER BY x) + min(x)OVER(ORDER BY x))
    FROM (SELECT (SELECT sum(a) FROM t1 GROUP BY a) AS x FROM t1);
} {2 2 2}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 49.1 {
  CREATE TABLE t1 (a PRIMARY KEY);
  INSERT INTO t1 VALUES(1);
}

do_execsql_test 49.2 {
  SELECT b AS c FROM (
    SELECT a AS b FROM (
      SELECT a FROM t1 WHERE a=1 OR (SELECT sum(a) OVER ())
    ) 
    WHERE b=1 OR b<10
  ) 
  WHERE c=1 OR c>=10;
} {1}


#-------------------------------------------------------------------------
reset_db
do_execsql_test 50.0 {
  CREATE TABLE t1 (a DOUBLE PRIMARY KEY);
  INSERT INTO t1 VALUES(10.0);
}

do_execsql_test 50.1 {
  SELECT * FROM t1 WHERE a%1 OR (SELECT sum(a) OVER (ORDER BY a%2))
} {10.0}

do_execsql_test 50.2 {
  SELECT * FROM (
    SELECT * FROM t1 WHERE a%1 OR (SELECT sum(a) OVER (ORDER BY a%2))
  ) 
  WHERE a=1 OR ( (SELECT sum(a) OVER (ORDER BY a%4)) AND a<=10 )
} {10.0}

do_execsql_test 50.3 {
  SELECT a FROM (
    SELECT * FROM (
      SELECT * FROM t1 WHERE a%1 OR (SELECT sum(a) OVER (ORDER BY a%2))
    ) 
    WHERE a=1 OR ( (SELECT sum(a) OVER (ORDER BY a%4)) AND a<=10 )
  ) 
  WHERE a=1 OR a=10.0
} {10.0}

do_execsql_test 50.4 {
  SELECT a FROM (
    SELECT * FROM (
      SELECT * FROM t1 WHERE a%1 OR (SELECT sum(a) OVER (ORDER BY a%2))
    ) 
    WHERE a=1 OR ( (SELECT sum(a) OVER (ORDER BY a%4)) AND a<=10 )
  ) 
  WHERE a=1 OR ((SELECT sum(a) OVER(ORDER BY a%8)) AND 10<=a)
} {10.0}

do_execsql_test 50.5 {
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);
} {10.0}

# 2020-04-03 ticket af4556bb5c285c08
#
reset_db
do_catchsql_test 51.1 {
  CREATE TABLE a(b, c);
  SELECT c FROM a GROUP BY c
    HAVING(SELECT(sum(b) OVER(ORDER BY b),
                  sum(b) OVER(PARTITION BY min(DISTINCT c), c ORDER BY b)));
} {1 {row value misused}}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 52.1 {
  CREATE TABLE t1(a, b, c);
  INSERT INTO t1 VALUES('AA','bb',356);
  INSERT INTO t1 VALUES('CC','aa',158);
  INSERT INTO t1 VALUES('BB','aa',399);
  INSERT INTO t1 VALUES('FF','bb',938);
}

do_execsql_test 52.2 {
  SELECT
    count() OVER win1,
    sum(c) OVER win2, 
    first_value(c) OVER win2,
    count(a) OVER (ORDER BY b)
      FROM t1
      WINDOW
      win1 AS (ORDER BY a),
    win2 AS (PARTITION BY 6 ORDER BY a
        RANGE BETWEEN 5 PRECEDING AND 0 PRECEDING );
} {
  1 356 356 4 
  2 399 399 2 
  3 158 158 2 
  4 938 938 4
}

do_execsql_test 52.3 {
SELECT
  count() OVER (),
  sum(c) OVER win2, 
  first_value(c) OVER win2,
  count(a) OVER (ORDER BY b)
FROM t1
WINDOW
  win1 AS (ORDER BY a),
  win2 AS (PARTITION BY 6 COLLATE binary ORDER BY a
           RANGE BETWEEN 5 PRECEDING AND 0 PRECEDING );
} {
  4 356 356 4 
  4 399 399 2 
  4 158 158 2 
  4 938 938 4
}

do_execsql_test 52.4 {
  SELECT
    count() OVER win1,
    sum(c) OVER win2, 
    first_value(c) OVER win2,
    count(a) OVER (ORDER BY b)
  FROM t1
  WINDOW
    win1 AS (ORDER BY a),
    win2 AS (PARTITION BY 6 COLLATE binary ORDER BY a
             RANGE BETWEEN 5 PRECEDING AND 0 PRECEDING );
} {
  1 356 356 4 
  2 399 399 2 
  3 158 158 2 
  4 938 938 4
}

# 2020-05-23
# ticket 7a5279a25c57adf1
#
reset_db
do_execsql_test 53.0 {
  CREATE TABLE a(c UNIQUE);
  INSERT INTO a VALUES(4),(0),(9),(-9);
  SELECT a.c
    FROM a
    JOIN a AS b ON a.c=4
    JOIN a AS e ON a.c=e.c
   WHERE a.c=(SELECT (SELECT coalesce(lead(2) OVER(),0) + sum(d.c))
                FROM a AS d
               WHERE a.c);
} {4 4 4 4}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 54.1 {
  CREATE TABLE t1(a VARCHAR(20), b FLOAT);
  INSERT INTO t1 VALUES('1',10.0);
}

do_catchsql_test 54.2 {
  SELECT * FROM ( 
    SELECT sum(b) OVER() AS c FROM t1 
      UNION
    SELECT b AS c FROM t1
  ) WHERE c>10;
} {0 {}}

do_execsql_test 54.3 {
  INSERT INTO t1 VALUES('2',5.0);
  INSERT INTO t1 VALUES('3',15.0);
}

do_catchsql_test 54.4 {
  SELECT * FROM ( 
    SELECT sum(b) OVER() AS c FROM t1 
      UNION
    SELECT b AS c FROM t1
  ) WHERE c>10;
} {0 {15.0 30.0}}

# 2020-06-05 ticket c8d3b9f0a750a529
reset_db
do_execsql_test 55.1 {
   CREATE TABLE a(b);
   SELECT
      (SELECT b FROM a
        GROUP BY b
        HAVING (SELECT COUNT()OVER() + lead(b)OVER(ORDER BY SUM(DISTINCT b) + b))
      ) 
    FROM a
  UNION
   SELECT 99
    ORDER BY 1;
} {99}

#------------------------------------------------------------------------
reset_db
do_execsql_test 56.1 {
  CREATE TABLE t1(a, b INTEGER); 
  CREATE TABLE t2(c, d); 
}
do_catchsql_test 56.2 {
  SELECT avg(b) FROM t1 
    UNION ALL 
  SELECT min(c) OVER () FROM t2 
  ORDER BY nosuchcolumn;
} {1 {1st ORDER BY term does not match any column in the result set}}

reset_db
do_execsql_test 57.1 {
  CREATE TABLE t4(a, b, c, d, e);
}

do_catchsql_test 57.2  {
  SELECT b FROM t4
  UNION
  SELECT a FROM t4
  ORDER BY (
    SELECT sum(x) OVER() FROM (
      SELECT c AS x FROM t4
      UNION
      SELECT d FROM t4
      ORDER BY (SELECT e FROM t4)
    )
  );
} {1 {1st ORDER BY term does not match any column in the result set}}

# 2020-06-06 various dbsqlfuzz finds and
# ticket 0899cf62f597d7e7
#
reset_db
do_execsql_test 57.1 {
  CREATE TABLE t1(a, b, c);
  INSERT INTO t1 VALUES(NULL,NULL,NULL);
  SELECT 
    sum(a),
    min(b) OVER (),
    count(c) OVER (ORDER BY b)
  FROM t1;
} {{} {} 0}
do_execsql_test 57.2 {
  CREATE TABLE v0 ( v1 INTEGER PRIMARY KEY ) ; 
  INSERT INTO v0 VALUES ( 10 ) ; 
  SELECT DISTINCT v1, lead(v1) OVER() FROM v0 GROUP BY v1 ORDER BY 2;
} {10 {}}
do_catchsql_test 57.3 {
  DROP TABLE t1;
  CREATE TABLE t1(a);
  INSERT INTO t1(a) VALUES(22);
  CREATE TABLE t3(y);
  INSERT INTO t3(y) VALUES(5),(11),(-9);
  SELECT (
    SELECT max(y) OVER( ORDER BY (SELECT x FROM (SELECT sum(y) AS x FROM t1)))
  )
  FROM t3;
} {0 5}

# 2020-06-06 ticket 1f6f353b684fc708
reset_db
do_execsql_test 58.1 {
  CREATE TABLE a(a, b, c);
  INSERT INTO a VALUES(1, 2, 3);
  INSERT INTO a VALUES(4, 5, 6);
  SELECT sum(345+b)      OVER (ORDER BY b),
         sum(avg(678)) OVER (ORDER BY c) FROM a;
} {347 678.0}

# 2020-06-06 ticket e5504e987e419fb0
do_catchsql_test 59.1 {
  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1(x INTEGER PRIMARY KEY);
  INSERT INTO t1 VALUES (123);
  SELECT
     ntile( (SELECT sum(x)) ) OVER(ORDER BY x),
     min(x) OVER(ORDER BY x)
    FROM t1; 
} {1 {misuse of aggregate: sum()}}

# 2020-06-07 ticket f7d890858f361402
do_execsql_test 60.1 {
  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1 (x INTEGER PRIMARY KEY);
  INSERT INTO t1 VALUES (99);
  SELECT EXISTS(SELECT count(*) OVER() FROM t1 ORDER BY sum(x) OVER());
} {1}

# 2020-06-07 test case generated by dbsqlfuzz showing how an AggInfo
# object might be referenced after the sqlite3Select() call that created
# it returns.  This proves the need to persist all AggInfo objects until
# the Parse object is destroyed.
#
reset_db
do_catchsql_test 61.1 {
CREATE TABLE t1(a);
INSERT INTO t1 VALUES(5),(NULL),('seventeen');
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);
} {0 {{} {} {}}}

foreach tn {1 2} {
  if {$tn==2} { optimization_control db query-flattener 0 }
  do_catchsql_test 61.2.$tn {
    SELECT 
      (SELECT max(x)OVER(ORDER BY x) / min(x) OVER() ) 
    FROM (
      SELECT (SELECT sum(a) FROM t1 ) AS x FROM t1
    )

  } {0 {1.0 1.0 1.0}}
}

reset_db
optimization_control db all 0 
do_execsql_test 61.3.0 {
  CREATE TABLE t1(a);
  CREATE TABLE t2(y);
}

do_execsql_test 61.3.1 {
  SELECT (
    SELECT count(a) OVER ( ORDER BY (SELECT sum(y) FROM t2) )
         + total(a) OVER() 
  )
  FROM t1
} {}
do_execsql_test 61.4.2 {
  SELECT (
    SELECT count(a) OVER ( ORDER BY sum(a) )
         + total(a) OVER() 
  )
  FROM t1
} {0.0}

do_catchsql_test 61.4.3 {
  SELECT 
    sum(a) OVER ( ORDER BY a ) 
  FROM t1 
  ORDER BY (SELECT sum(a) FROM t2)
} {1 {misuse of aggregate: sum()}}
do_execsql_test 61.4.4 {
  SELECT 
    sum(a) OVER ( ORDER BY a ) 
  FROM t1 
  ORDER BY (SELECT sum(y) FROM t2)
} 


#-------------------------------------------------------------------------
reset_db
do_execsql_test 62.1 {
  CREATE TABLE t1(a VARCHAR(20), b FLOAT);
  INSERT INTO t1 VALUES('1',10.0);
}

do_execsql_test 62.2 {
  SELECT * FROM ( 
      SELECT sum(b) OVER() AS c FROM t1 
      UNION
      SELECT b AS c FROM t1
      ) WHERE c>10;
}

do_execsql_test 62.3 {
  INSERT INTO t1 VALUES('2',5.0);
  INSERT INTO t1 VALUES('3',15.0);
}

do_execsql_test 62.4 {
  SELECT * FROM ( 
      SELECT sum(b) OVER() AS c FROM t1 
      UNION
      SELECT b AS c FROM t1
      ) WHERE c>10;
} {15.0 30.0}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 63.1 {
  CREATE TABLE t1(b, x);
  CREATE TABLE t2(c, d);
  CREATE TABLE t3(e, f);
}

do_execsql_test 63.2 {
  SELECT max(b) OVER(
      ORDER BY SUM(
        (SELECT c FROM t2 UNION SELECT x ORDER BY c)
      )
  ) FROM t1;
} {{}}

do_execsql_test 63.3 {
  SELECT sum(b) over(
      ORDER BY (
        SELECT max(b) OVER(
          ORDER BY sum(
            (SELECT x AS c UNION SELECT 1234 ORDER BY c)
          )
        ) AS e
        ORDER BY e
      )
    )
  FROM t1;
} {{}}

#-------------------------------------------------------------------------
reset_db 
do_execsql_test 64.1 {
  CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
  INSERT INTO t1 VALUES(1, 'abcd');
  INSERT INTO t1 VALUES(2, 'BCDE');
  INSERT INTO t1 VALUES(3, 'cdef');
  INSERT INTO t1 VALUES(4, 'DEFG');
}

do_execsql_test 64.2 {
  SELECT rowid, max(b COLLATE nocase)||'' 
  FROM t1 
  GROUP BY rowid
  ORDER BY max(b COLLATE nocase)||'';
} {1 abcd 2 BCDE 3 cdef 4 DEFG}

do_execsql_test 64.3 {
  SELECT count() OVER (), rowid, max(b COLLATE nocase)||'' 
  FROM t1 
  GROUP BY rowid
  ORDER BY max(b COLLATE nocase)||'';
} {4 1 abcd   4 2 BCDE   4 3 cdef   4 4 DEFG}

do_execsql_test 64.4 {
  SELECT count() OVER (), rowid, max(b COLLATE nocase)
  FROM t1 
  GROUP BY rowid
  ORDER BY max(b COLLATE nocase);
} {4 1 abcd   4 2 BCDE   4 3 cdef   4 4 DEFG}

#-------------------------------------------------------------------------
reset_db 
do_execsql_test 65.1 {
  CREATE TABLE t1(c1);
  INSERT INTO t1 VALUES('abcd');
}
do_execsql_test 65.2 {
  SELECT max(c1 COLLATE nocase) IN (SELECT 'aBCd') FROM t1;
} {1}

do_execsql_test 65.3 {
  SELECT 
     count() OVER (), 
     group_concat(c1 COLLATE nocase) IN (SELECT 'aBCd') FROM t1;
} {1 1}

do_execsql_test 65.4 {
  SELECT COUNT() OVER () LIKE lead(102030) OVER(
      ORDER BY sum('abcdef' COLLATE nocase) IN (SELECT 54321) 
  )
  FROM t1;
} {{}}

#-------------------------------------------------------------------------
reset_db

do_execsql_test 66.1 {
  CREATE TABLE t1(a INTEGER);
  INSERT INTO t1 VALUES(3578824042033200656);
  INSERT INTO t1 VALUES(3029012920382354029);
}

foreach {tn spec} {
  1 "ORDER BY a RANGE BETWEEN 0.3 PRECEDING AND 10 FOLLOWING"
  2 "ORDER BY a RANGE BETWEEN 0.3 PRECEDING AND 0.1 PRECEDING"
  3 "ORDER BY a RANGE BETWEEN 0.3 FOLLOWING AND 10 FOLLOWING"
  4 "ORDER BY a DESC RANGE BETWEEN 0.3 PRECEDING AND 10 FOLLOWING"
  5 "ORDER BY a NULLS LAST RANGE BETWEEN 0.3 PRECEDING AND 10 FOLLOWING"
  6 "ORDER BY a RANGE BETWEEN 1.0 PRECEDING AND 2.0 PRECEDING"
} {
  do_execsql_test 66.2.$tn "
    SELECT total(a) OVER ( $spec ) FROM t1 ORDER BY a
  " {
    3.02901292038235e+18 3.5788240420332e+18
  }
}


do_execsql_test 66.3 {
  CREATE TABLE t2(a INTEGER);
  INSERT INTO t2 VALUES(45);
  INSERT INTO t2 VALUES(30);
}

foreach {tn spec res} {
  1 "ORDER BY a RANGE BETWEEN 0.3 PRECEDING AND 10 FOLLOWING"   {30.0 45.0}
  2 "ORDER BY a RANGE BETWEEN 0.3 PRECEDING AND 0.1 PRECEDING"  {0.0 0.0}
  3 "ORDER BY a RANGE BETWEEN 0.3 FOLLOWING AND 10 FOLLOWING"   {0.0 0.0}
  4 "ORDER BY a DESC RANGE BETWEEN 0.3 PRECEDING AND 10 FOLLOWING" {30.0 45.0}
  5 "ORDER BY a NULLS LAST RANGE BETWEEN 0.3 PRECEDING AND 10 FOLLOWING" {30.0 45.0}
  6 "ORDER BY a RANGE BETWEEN 1.0 PRECEDING AND 2.0 PRECEDING" {0.0 0.0}
} {
  do_execsql_test 66.2.$tn "
    SELECT total(a) OVER ( $spec ) FROM t2 ORDER BY a
  " $res
}


#-------------------------------------------------------------------------
reset_db
do_execsql_test 67.0 {
  CREATE TABLE t1(a, b, c);
  CREATE TABLE t2(a, b, c);
}

do_catchsql_test 67.1 {
  SELECT a,c,b FROM t1 INTERSECT SELECT a,b,c FROM t1 ORDER BY (             
      SELECT nth_value(a,2) OVER w1 
      WINDOW w1 AS ( ORDER BY ((SELECT 1 FROM v1)) )
  )
} {1 {no such table: v1}}

do_catchsql_test 67.2 {
  SELECT a,c,b FROM t1 INTERSECT SELECT a,b,c FROM t1 ORDER BY (             
      SELECT nth_value(a,2) OVER w1 
      WINDOW w1 AS ( ORDER BY ((SELECT 1 FROM t2)) )
  )
} {1 {1st ORDER BY term does not match any column in the result set}}

# 2021-05-07
# Do not allow aggregate functions in the ORDER BY clause even if
# there are window functions in the result set.
# Forum: /forumpost/540fdfef77
#
reset_db
do_catchsql_test 68.0 {
  CREATE TABLE t1(a,b);
  INSERT INTO t1(a,b) VALUES(0,0),(1,1),(2,4),(3,9),(4,99);
  SELECT rowid, a, b, sum(a)OVER() FROM t1 ORDER BY count(b);
} {1 {misuse of aggregate: count()}}

# 2021-05-22
# Forum https://sqlite.org/forum/forumpost/7e484e225c
#
reset_db
do_catchsql_test 69.0 {
  CREATE TABLE t1(a,b);
  CREATE INDEX t1ba ON t1(b,a);
  SELECT * FROM t1 WHERE b = (SELECT b FROM t1 ORDER BY lead(b) OVER () AND sum(a));
} {1 {misuse of aggregate: sum()}}
do_catchsql_test 69.1 {
  SELECT * FROM t1 WHERE b >= (SELECT b FROM t1 ORDER BY lead(b) OVER () AND sum(a));
} {1 {misuse of aggregate: sum()}}
do_catchsql_test 69.2 {
  SELECT * FROM t1 WHERE b <= (SELECT b FROM t1 ORDER BY lead(b) OVER () AND sum(a));
} {1 {misuse of aggregate: sum()}}

# 2021-06-23
# Forum https://sqlite.org/forum/forumpost/31e0432608
#
reset_db
do_execsql_test 70.0 {
  CREATE TABLE t1(a);
}
do_execsql_test 70.1 {
  SELECT substr(a,4,lag(a,7) OVER(PARTITION BY 'cf23' ORDER BY 2)) AS ca0 FROM t1 ORDER BY ca0;
}
do_execsql_test 70.2 {
  SELECT substr(a,4,lag(a,7) OVER(PARTITION BY 'cf23' ORDER BY likely(2))) AS ca0 FROM t1 ORDER BY ca0;
}

# 2021-11-07
# Bug report from Wang Ke
# https://sqlite.org/forum/forumpost/9ba4f60ff8
reset_db
do_catchsql_test 71.0 {
  CREATE TABLE t0(a);
  SELECT a FROM t0, (SELECT a AS b FROM t0)
   WHERE (a,1)=(SELECT 2,2 UNION SELECT sum(b),max(b) OVER(ORDER BY b) ORDER BY 2)
     AND b=4
   ORDER BY b;
} {/1 {.*}/}

do_execsql_test 72.1 {
  CREATE TABLE dual(dummy);  INSERT INTO dual VALUES('X');
  CREATE VIEW v1(x,y) AS SELECT RANK() OVER (PARTITION BY 0), SUM(0) FROM dual;
  SELECT * FROM v1 WHERE true;
} {1 0}

#-------------------------------------------------------------------------
reset_db

do_execsql_test 72.0 {
  CREATE TABLE t0(c0);
  INSERT INTO t0(c0) VALUES (0);
  CREATE VIEW v0(c0) AS SELECT TOTAL(0) OVER (PARTITION BY t0.c0) FROM t0;
}
do_execsql_test 72.1 {
  SELECT COUNT(*) FROM (
      SELECT TOTAL(0) OVER (PARTITION BY t0.c0) FROM t0
      )
  WHERE ('1' IS NOT ('abcde' NOTNULL)); 
} {1}

# 2023-03-28 https://sqlite.org/forum/forumpost/dc3b92cfa0 (Song Liu)
#
reset_db
do_execsql_test 73.0 {
  CREATE TABLE t1(a INT);
  INSERT INTO t1(a) VALUES(1),(2),(4);
  CREATE VIEW t2(b,c) AS SELECT * FROM t1 JOIN t1 A ORDER BY sum(0) OVER(PARTITION BY 0);
  CREATE TRIGGER x1 INSTEAD OF UPDATE ON t2 BEGIN SELECT true; END;
}
do_execsql_test 73.1 {
  SELECT * FROM t2;
} {1 1 1 2 1 4 2 1 2 2 2 4 4 1 4 2 4 4}
do_execsql_test 73.2 {
  UPDATE t2 SET c=99 WHERE b=4 RETURNING *;
} {4 99 4 99 4 99}
do_execsql_test 73.3 {
  SELECT *, nth_value(15,2) OVER() FROM t2, t1 WHERE b=4;
} {
  4 1 1 15
  4 2 1 15
  4 4 1 15
  4 1 2 15
  4 2 2 15
  4 4 2 15
  4 1 4 15
  4 2 4 15
  4 4 4 15
}
do_execsql_test 73.4 {
  UPDATE t2 SET c=nth_value(15,2) OVER() FROM (SELECT * FROM t1) WHERE b=4 RETURNING *;
} {
  4 15
  4 15
  4 15
  4 15
  4 15
  4 15
  4 15
  4 15
  4 15
}
do_execsql_test 73.5 {
  DROP TRIGGER x1;
}
do_catchsql_test 73.6 {
  UPDATE t2 SET c=99 WHERE b=4 RETURNING *;
} {1 {cannot modify t2 because it is a view}}
do_catchsql_test 73.7 {
  UPDATE t2 SET c=nth_value(15,2) OVER() FROM (SELECT * FROM t1) WHERE b=4 RETURNING *;
} {1 {cannot modify t2 because it is a view}}

# 2023-03-28 https://sqlite.org/forum/forumpost/bad532820c
#
reset_db
do_execsql_test 74.0 {
  CREATE TABLE t1 (a INT, b INT);
  CREATE TABLE t2 (c INT, d INT);
  CREATE INDEX idx ON t1(abs(a));
  INSERT INTO t1 VALUES(1,2),(3,4);
  INSERT INTO t2 VALUES(5,6),(7,8);
}
do_execsql_test 74.1 {
  SELECT (
    SELECT count( a ) FROM t2 LIMIT 1
  )
  FROM t1;
} {2}  ;# Verified using PG 14.2
do_execsql_test 74.2 {
  SELECT (
    SELECT count( a+c ) FROM t2 LIMIT 1
  )
  FROM t1;
} {2 2}  ;# verified on PG 14.2.  Crashes PG 9.6!
do_execsql_test 74.3 {
  SELECT (
    SELECT count( ( SELECT(sum(0) OVER(ORDER BY c, abs(a))) ) )
      FROM t2 GROUP BY c LIMIT 1
  ) 
  FROM t1;
} {1 1}  ;# verified on PG 14.2
do_execsql_test 74.4 {
  /* Original test case reported in https://sqlite.org/forum/forumpost/bad532820c
  CREATE TABLE v0 (c1);
  CREATE INDEX i ON v0 (c1, c1=1);
  SELECT 0 FROM v0 AS a1
   WHERE (SELECT count((SELECT(sum(0) OVER(PARTITION BY(c1), (a1.c1=1) ))))
            FROM v0
           GROUP BY hex(0))
     AND a1.c1=0;
} {}

# 2023-04-11 https://sqlite.org/forum/forumpost/6c5678e3da
# An ALWAYS() turns out to be sometimes false.
#
do_execsql_test 75.0 {
  DROP TABLE t1;
  CREATE TABLE t1(a INT, b INT);
  CREATE INDEX t1x ON t1(a+b);
}
do_catchsql_test 75.1 {
  SELECT count((SELECT count(a0.a+a0.b) ORDER BY sum(0) OVER (PARTITION BY 0)))
    FROM t1 AS a0 JOIN t1 AS a1
   GROUP BY a1.a;
} {1 {misuse of aggregate: count()}}

# 2023-04-13 https://sqlite.org/forum/forumpost/0d48347967
reset_db
do_execsql_test 76.0 {
  CREATE TABLE t1(a INT, b INT);
  INSERT INTO t1(a,b) VALUES (111,222),(111,223),(118,229);
  CREATE INDEX t1a ON t1(a);
  CREATE TABLE t2(x INT);
  INSERT INTO t2 VALUES (333),(444),(555);
}
do_execsql_test 76.1 {
  SELECT c, (SELECT c + sum(1) OVER ()) AS "res"
    FROM t2 LEFT JOIN (SELECT +a AS c FROM t1) AS v1 ON true
   GROUP BY c
   ORDER by c;
} {111 112 118 119}
# ^^^^^^^^^^^^^^^^^-- results verified against PG 14.2

do_execsql_test 76.2 {
  CREATE TABLE t3(x);
  CREATE TABLE t4(y);
  INSERT INTO t3 VALUES(100), (200), (400);
  INSERT INTO t4 VALUES(100), (300), (400);
}
do_execsql_test 76.3 {
  SELECT (SELECT y+sum(0) OVER ()) FROM t3 LEFT JOIN t4 ON x=y;
} {100 {} 400}
do_execsql_test 76.4 {
  SELECT (SELECT y+sum(0) OVER ()) FROM t3 LEFT JOIN t4 ON x=y GROUP BY x;
} {100 {} 400}
do_execsql_test 76.5 {
  SELECT (SELECT max(y)+sum(0) OVER ()) FROM t3 LEFT JOIN t4 ON x=y GROUP BY x;
} {100 {} 400}

# 2023-05-23 https://sqlite.org/forum/forumpost/fbfe330a20
#
reset_db
do_execsql_test 77.1 {
  CREATE TABLE t1(x INT);
  CREATE INDEX t1x ON t1(likely(x));
  INSERT INTO t1 VALUES(1),(2),(4),(8);
}
do_execsql_test 77.2 {
  SELECT max(~likely(x)) FILTER (WHERE true) FROM t1 INDEXED BY t1x GROUP BY x;
} {-2 -3 -5 -9}

# 2024-05-23 https://sqlite.org/forum/forumpost/bf8f43aa522c2299
#
# A bug in group_concat() when used as a window function, reported
# just hours after the 3.46.0 release, though first appearing
# in 3.28.0.
#
# When used as a window function, a group_concat() was not
# correctly distinguishing between NULL and empty-string for
# its return value.
#
do_execsql_test 78.1 {
  SELECT quote(group_concat(x) OVER ()) FROM (SELECT '' AS x);
} ''
do_execsql_test 78.2 {
  SELECT quote(group_concat(x) OVER (
      ORDER BY y RANGE BETWEEN 1 FOLLOWING AND 2 FOLLOWING
    )) FROM (SELECT 'abc' AS x, 1 AS y);
} NULL

finish_test