SET NAMES utf8;
Warnings:
Warning	3719	'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
Single window function (plus ORDER BY).
CREATE TABLE t(i INT, j INT);
INSERT INTO t VALUES (1,1);
INSERT INTO t VALUES (1,4);
INSERT INTO t VALUES (1,2);
INSERT INTO t VALUES (1,4);
ANALYZE TABLE t;
Table	Op	Msg_type	Msg_text
test.t	analyze	status	OK
Single partition, no sorting
SELECT i, j, MIN(i) OVER (ROWS UNBOUNDED PRECEDING) min,
MAX(j) OVER (ROWS UNBOUNDED PRECEDING) max FROM t;
i	j	min	max
1	1	1	1
1	4	1	4
1	2	1	4
1	4	1	4
SELECT i, j, MIN(i) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) min,
MAX(j) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) max FROM t;
i	j	min	max
1	1	1	1
1	4	1	4
1	2	1	4
1	4	1	4
SELECT i, j, MIN(i+j) OVER (ROWS UNBOUNDED PRECEDING) min FROM t ORDER BY j;
i	j	min
1	1	2
1	2	2
1	4	2
1	4	2
SELECT i, j, MAX(i+j) OVER (ROWS UNBOUNDED PRECEDING) max FROM t ORDER BY j;
i	j	max
1	1	2
1	2	3
1	4	5
1	4	5
SELECT i, j, MIN(i+j) OVER (ROWS UNBOUNDED PRECEDING) min FROM t ORDER BY j DESC;
i	j	min
1	4	5
1	4	5
1	2	3
1	1	2
SELECT i, j, MAX(i+j) OVER (ROWS UNBOUNDED PRECEDING) max FROM t ORDER BY j DESC;
i	j	max
1	4	5
1	4	5
1	2	5
1	1	5
Single ordered partition
SELECT i, j, MIN(i) OVER (ORDER BY j ROWS UNBOUNDED PRECEDING) min,
MAX(j) OVER (ORDER BY j ROWS UNBOUNDED PRECEDING) max FROM t;
i	j	min	max
1	1	1	1
1	2	1	2
1	4	1	4
1	4	1	4
SELECT i, j, MIN(j) OVER (ORDER BY j ROWS UNBOUNDED PRECEDING) min,
MAX(i) OVER (ORDER BY i ROWS UNBOUNDED PRECEDING) max FROM t ORDER BY j ;
i	j	min	max
1	1	1	1
1	2	1	1
1	4	1	1
1	4	1	1
SELECT i, j, MIN(i+j) OVER (ORDER BY j ROWS UNBOUNDED PRECEDING) min FROM t ORDER BY j DESC;
i	j	min
1	4	2
1	4	2
1	2	2
1	1	2
SELECT i, j, MAX(i+j) OVER (ORDER BY j ROWS UNBOUNDED PRECEDING) max FROM t ORDER BY j DESC;
i	j	max
1	4	5
1	4	5
1	2	3
1	1	2
SELECT i, j, MIN(i+j) OVER (ORDER BY j DESC ROWS UNBOUNDED PRECEDING) min FROM t ORDER BY j DESC;
i	j	min
1	4	5
1	4	5
1	2	3
1	1	2
SELECT i, j, MAX(i+j) OVER (ORDER BY j DESC ROWS UNBOUNDED PRECEDING) max FROM t ORDER BY j DESC;
i	j	max
1	4	5
1	4	5
1	2	5
1	1	5
Check that we eliminate redundant sorting in ORDER BY even with wfs
SELECT i, j, MIN(i+j) OVER (ROWS UNBOUNDED PRECEDING) min FROM t ORDER BY NULL DESC;
i	j	min
1	1	2
1	4	2
1	2	2
1	4	2
EXPLAIN FORMAT=JSON SELECT i, j, MIN(i+j) OVER (ROWS UNBOUNDED PRECEDING) min FROM t ORDER BY NULL DESC;
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "0.65"
    },
    "ordering_operation": {
      "using_filesort": false,
      "windowing": {
        "windows": [
          {
            "name": "<unnamed window>",
            "functions": [
              "min"
            ]
          }
        ],
        "table": {
          "table_name": "t",
          "access_type": "ALL",
          "rows_examined_per_scan": 4,
          "rows_produced_per_join": 4,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "0.25",
            "eval_cost": "0.40",
            "prefix_cost": "0.65",
            "data_read_per_join": "64"
          },
          "used_columns": [
            "i",
            "j"
          ]
        }
      }
    }
  }
}
Warnings:
Note	1003	/* select#1 */ select `test`.`t`.`i` AS `i`,`test`.`t`.`j` AS `j`,min((`test`.`t`.`i` + `test`.`t`.`j`)) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)  AS `min` from `test`.`t` order by NULL desc
With LIMIT
SELECT i, j, MIN(i+j) OVER (ROWS UNBOUNDED PRECEDING) min FROM t ORDER BY j DESC LIMIT 3;
i	j	min
1	4	5
1	4	5
1	2	3
Min/Max with View
CREATE VIEW v AS
SELECT i, j, MIN(i+j) OVER (ORDER BY j DESC ROWS UNBOUNDED PRECEDING) min,
MAX(i+j) OVER (ORDER BY j DESC ROWS UNBOUNDED PRECEDING) max FROM t;
SHOW CREATE VIEW v;
View	Create View	character_set_client	collation_connection
v	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select `t`.`i` AS `i`,`t`.`j` AS `j`,min((`t`.`i` + `t`.`j`)) OVER (ORDER BY `t`.`j` desc ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)  AS `min`,max((`t`.`i` + `t`.`j`)) OVER (ORDER BY `t`.`j` desc ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)  AS `max` from `t`	utf8mb3	utf8mb3_general_ci
SELECT * FROM v;
i	j	min	max
1	4	5	5
1	4	5	5
1	2	3	5
1	1	2	5
DROP VIEW v;
TRUNCATE TABLE t;
INSERT INTO t VALUES (999961560, DEFAULT);
INSERT INTO t VALUES (44721, DEFAULT);
SELECT MIN(i) OVER () FROM t;
MIN(i) OVER ()
44721
44721
SELECT MAX(i) OVER () FROM t;
MAX(i) OVER ()
999961560
999961560
DROP TABLE t;
CREATE TABLE t(i INT, j INT, k INT);
INSERT INTO t VALUES (1,1,1);
INSERT INTO t VALUES (1,4,1);
INSERT INTO t VALUES (1,2,1);
INSERT INTO t VALUES (1,4,1);
INSERT INTO t VALUES (1,4,1);
INSERT INTO t VALUES (1,1,2);
INSERT INTO t VALUES (1,4,2);
INSERT INTO t VALUES (1,2,2);
INSERT INTO t VALUES (1,4,2);
INSERT INTO t VALUES (1,1,3);
INSERT INTO t VALUES (1,4,3);
INSERT INTO t VALUES (1,2,3);
INSERT INTO t VALUES (1,4,3);
INSERT INTO t VALUES (1,1,4);
INSERT INTO t VALUES (1,4,4);
INSERT INTO t VALUES (1,2,4);
INSERT INTO t VALUES (1,4,4);
----------------------------------------------------------------------
-    Combination with GROUP BY
----------------------------------------------------------------------
SELECT k, MIN(k) OVER (ROWS UNBOUNDED PRECEDING) min,
MAX(k) OVER (ROWS UNBOUNDED PRECEDING) max FROM t;
k	min	max
1	1	1
1	1	1
1	1	1
1	1	1
1	1	1
2	1	2
2	1	2
2	1	2
2	1	2
3	1	3
3	1	3
3	1	3
3	1	3
4	1	4
4	1	4
4	1	4
4	1	4
SELECT k, MIN(i), SUM(j), MIN(k) OVER (ROWS UNBOUNDED PRECEDING) min_wf FROM t GROUP BY (k);
k	MIN(i)	SUM(j)	min_wf
1	1	15	1
2	1	11	1
3	1	11	1
4	1	11	1
SELECT k, MIN(i), SUM(j), MAX(k) OVER (ROWS UNBOUNDED PRECEDING) max_wf FROM t GROUP BY (k);
k	MIN(i)	SUM(j)	max_wf
1	1	15	1
2	1	11	2
3	1	11	3
4	1	11	4
SELECT k, MIN(i), SUM(j), MIN(k) OVER (ROWS UNBOUNDED PRECEDING) min_wf FROM t GROUP BY (k) ORDER BY min_wf DESC;
k	MIN(i)	SUM(j)	min_wf
1	1	15	1
2	1	11	1
3	1	11	1
4	1	11	1
SELECT k, MIN(i), SUM(j), MAX(k) OVER (ROWS UNBOUNDED PRECEDING) max_wf FROM t GROUP BY (k) ORDER BY max_wf DESC;
k	MIN(i)	SUM(j)	max_wf
1	1	15	1
2	1	11	2
3	1	11	3
4	1	11	4
SELECT k, GROUP_CONCAT(j ORDER BY j), MIN(k) OVER (ROWS UNBOUNDED PRECEDING) min,
MAX(k) OVER (ROWS UNBOUNDED PRECEDING) max FROM t GROUP BY (k);
k	GROUP_CONCAT(j ORDER BY j)	min	max
1	1,2,4,4,4	1	1
2	1,2,4,4	1	2
3	1,2,4,4	1	3
4	1,2,4,4	1	4
SELECT k, AVG(DISTINCT j), MIN(k) OVER (ROWS UNBOUNDED PRECEDING) min,
MAX(k) OVER (ROWS UNBOUNDED PRECEDING) max FROM t GROUP BY (k);
k	AVG(DISTINCT j)	min	max
1	2.3333	1	1
2	2.3333	1	2
3	2.3333	1	3
4	2.3333	1	4
SELECT k, GROUP_CONCAT(j ORDER BY j), MIN(k+1) OVER (ROWS UNBOUNDED PRECEDING) min FROM t GROUP BY (k);
k	GROUP_CONCAT(j ORDER BY j)	min
1	1,2,4,4,4	2
2	1,2,4,4	2
3	1,2,4,4	2
4	1,2,4,4	2
SELECT k, GROUP_CONCAT(j ORDER BY j), MAX(k+1) OVER (ROWS UNBOUNDED PRECEDING) max FROM t GROUP BY (k);
k	GROUP_CONCAT(j ORDER BY j)	max
1	1,2,4,4,4	2
2	1,2,4,4	3
3	1,2,4,4	4
4	1,2,4,4	5
SELECT k, GROUP_CONCAT(j ORDER BY j), MIN(k+1) OVER (ORDER BY k DESC ROWS UNBOUNDED PRECEDING) min FROM t GROUP BY (k);
k	GROUP_CONCAT(j ORDER BY j)	min
4	1,2,4,4	5
3	1,2,4,4	4
2	1,2,4,4	3
1	1,2,4,4,4	2
SELECT k, GROUP_CONCAT(j ORDER BY j), MAX(k+1) OVER (ORDER BY k DESC ROWS UNBOUNDED PRECEDING) max FROM t GROUP BY (k);
k	GROUP_CONCAT(j ORDER BY j)	max
4	1,2,4,4	5
3	1,2,4,4	5
2	1,2,4,4	5
1	1,2,4,4,4	5
CREATE TABLE t1 (id INTEGER, sex CHAR(1));
INSERT INTO t1 VALUES (1, 'M');
INSERT INTO t1 VALUES (2, 'F');
INSERT INTO t1 VALUES (3, 'F');
INSERT INTO t1 VALUES (4, 'F');
INSERT INTO t1 VALUES (5, 'M');
INSERT INTO t1 VALUES (10, NULL);
INSERT INTO t1 VALUES (11, NULL);
CREATE TABLE ss(c CHAR(1));
INSERT INTO ss VALUES ('M');
This is grouped aggregate with HAVING in conjunction with a streaming wf
SELECT sex, AVG(id), MIN(AVG(id)) OVER w min, MAX(AVG(id)) OVER w max FROM t1
GROUP BY sex HAVING sex='M' OR sex='F' OR sex IS NULL
WINDOW w AS (ROWS UNBOUNDED PRECEDING) ORDER BY sex DESC;
sex	AVG(id)	min	max
M	3.0000	3.0000	3.0000
F	3.0000	3.0000	3.0000
NULL	10.5000	3.0000	10.5000
Ditto, but HAVING using subquery
SELECT sex, AVG(id), MIN(AVG(id)) OVER w min, MAX(AVG(id)) OVER w max FROM t1
GROUP BY sex HAVING sex=(SELECT c FROM ss LIMIT 1) OR sex='F' OR sex IS NULL
WINDOW w AS (ROWS UNBOUNDED PRECEDING) ORDER BY sex DESC;
sex	AVG(id)	min	max
M	3.0000	3.0000	3.0000
F	3.0000	3.0000	3.0000
NULL	10.5000	3.0000	10.5000
This is a grouped aggregate in conjunction with a buffered wf
which generates an extra tmp file step
SELECT sex, AVG(id), MIN(AVG(id)) OVER w min, MAX(AVG(id)) OVER w max, NTILE(2) OVER w FROM t1
GROUP BY sex
WINDOW w AS (ORDER BY sex ROWS UNBOUNDED PRECEDING) ORDER BY sex DESC;
sex	AVG(id)	min	max	NTILE(2) OVER w
M	3.0000	3.0000	10.5000	2
F	3.0000	3.0000	10.5000	1
NULL	10.5000	10.5000	10.5000	1
This is a grouped aggregate with HAVING in conjunction with a buffered wf
which generates an extra tmp file step
SELECT sex, AVG(id), MIN(AVG(id)) OVER w min, MAX(AVG(id)) OVER w max, NTILE(2) OVER w FROM t1
GROUP BY sex HAVING sex=(SELECT c FROM ss LIMIT 1) OR sex='F' OR sex IS NULL
WINDOW w AS (ORDER BY sex ROWS UNBOUNDED PRECEDING) ORDER BY sex DESC;
sex	AVG(id)	min	max	NTILE(2) OVER w
M	3.0000	3.0000	10.5000	2
F	3.0000	3.0000	10.5000	1
NULL	10.5000	10.5000	10.5000	1
Pure HAVING: In absence of filtering in the grouping sort step,
make sure we filter before windowing steps
SELECT sex, NTILE(2) OVER w , MIN(ASCII(sex)) OVER w min,
MAX(ASCII(sex)) OVER w max FROM t1
HAVING sex=(SELECT c FROM ss LIMIT 1)
WINDOW w AS (ORDER BY sex ROWS UNBOUNDED PRECEDING);
sex	NTILE(2) OVER w	min	max
M	1	77	77
M	2	77	77
Bug fix for prepared statements
PREPARE p FROM "SELECT sex, AVG(id), MIN(AVG(id)) OVER w min,
        MAX(AVG(id)) OVER w max, NTILE(2) OVER w FROM t1
    GROUP BY sex HAVING sex=(SELECT c FROM ss LIMIT 1) OR sex='F' OR sex IS NULL
    WINDOW w AS (ORDER BY sex ROWS UNBOUNDED PRECEDING) ORDER BY sex DESC";
EXECUTE p;
sex	AVG(id)	min	max	NTILE(2) OVER w
M	3.0000	3.0000	10.5000	2
F	3.0000	3.0000	10.5000	1
NULL	10.5000	10.5000	10.5000	1
EXECUTE p;
sex	AVG(id)	min	max	NTILE(2) OVER w
M	3.0000	3.0000	10.5000	2
F	3.0000	3.0000	10.5000	1
NULL	10.5000	10.5000	10.5000	1
DROP PREPARE p;
DROP TABLE t1,ss;
Tests with ROLLUP
SELECT k, MIN(i), SUM(j), MIN(k) OVER (ROWS UNBOUNDED PRECEDING) min_wf FROM t
GROUP BY (k) WITH ROLLUP;
k	MIN(i)	SUM(j)	min_wf
1	1	15	1
2	1	11	1
3	1	11	1
4	1	11	1
NULL	1	48	1
SELECT k, MIN(i), SUM(j), MAX(k) OVER (ROWS UNBOUNDED PRECEDING) max_wf FROM t
GROUP BY (k) WITH ROLLUP;
k	MIN(i)	SUM(j)	max_wf
1	1	15	1
2	1	11	2
3	1	11	3
4	1	11	4
NULL	1	48	4
SELECT k, MIN(i), SUM(j), MIN(k) OVER (ORDER BY k DESC ROWS UNBOUNDED PRECEDING) min_wf FROM t
GROUP BY (k) WITH ROLLUP ORDER BY k DESC;
k	MIN(i)	SUM(j)	min_wf
4	1	11	4
3	1	11	3
2	1	11	2
1	1	15	1
NULL	1	48	1
SELECT k, MIN(i), SUM(j), MIN(k) OVER (ROWS UNBOUNDED PRECEDING) min_wf FROM t
GROUP BY k,j WITH ROLLUP;
k	MIN(i)	SUM(j)	min_wf
1	1	1	1
1	1	2	1
1	1	12	1
1	1	15	1
2	1	1	1
2	1	2	1
2	1	8	1
2	1	11	1
3	1	1	1
3	1	2	1
3	1	8	1
3	1	11	1
4	1	1	1
4	1	2	1
4	1	8	1
4	1	11	1
NULL	1	48	1
DROP TABLE t;
----------------------------------------------------------------------
-    Min/Max with NTH VALUE/LEAD/LAG
----------------------------------------------------------------------
CREATE TABLE t3(t3_id INT, k INT);
INSERT INTO t3 VALUES (0, 0);
INSERT INTO t3 VALUES (0, 0);
INSERT INTO t3 VALUES (2, 0);
INSERT INTO t3 VALUES (2, 0);
INSERT INTO t3 VALUES (4, 0);
INSERT INTO t3 VALUES (4, 0);
INSERT INTO t3 VALUES (6, 0);
INSERT INTO t3 VALUES (6, 0);
INSERT INTO t3 VALUES (8, 0);
INSERT INTO t3 VALUES (8, 0);
INSERT INTO t3 VALUES (1, 1);
INSERT INTO t3 VALUES (1, 1);
INSERT INTO t3 VALUES (3, 1);
INSERT INTO t3 VALUES (3, 1);
INSERT INTO t3 VALUES (5, 1);
INSERT INTO t3 VALUES (5, 1);
INSERT INTO t3 VALUES (7, 1);
INSERT INTO t3 VALUES (7, 1);
INSERT INTO t3 VALUES (9, 1);
INSERT INTO t3 VALUES (9, 1);
SELECT t3_id, MIN(t3_id) OVER w min,
MAX(t3_id) OVER w max,
CUME_DIST() OVER w c_dist,
LEAD(t3_id, 2) OVER w lead2,
NTH_VALUE(t3_id, 3) OVER w nth,
k FROM t3
WINDOW w AS (PARTITION BY k ORDER BY t3_id);
t3_id	min	max	c_dist	lead2	nth	k
0	0	0	0.2	2	NULL	0
0	0	0	0.2	2	NULL	0
2	0	2	0.4	4	2	0
2	0	2	0.4	4	2	0
4	0	4	0.6	6	2	0
4	0	4	0.6	6	2	0
6	0	6	0.8	8	2	0
6	0	6	0.8	8	2	0
8	0	8	1	NULL	2	0
8	0	8	1	NULL	2	0
1	1	1	0.2	3	NULL	1
1	1	1	0.2	3	NULL	1
3	1	3	0.4	5	3	1
3	1	3	0.4	5	3	1
5	1	5	0.6	7	3	1
5	1	5	0.6	7	3	1
7	1	7	0.8	9	3	1
7	1	7	0.8	9	3	1
9	1	9	1	NULL	3	1
9	1	9	1	NULL	3	1
SELECT t3_id, MIN(t3_id) OVER w min,
MAX(t3_id) OVER w max,
CUME_DIST() OVER w c_dist,
LEAD(t3_id, 2) OVER w lead2,
NTH_VALUE(t3_id, 3) OVER w nth,
k FROM t3
WINDOW w AS (PARTITION BY k ORDER BY t3_id RANGE UNBOUNDED PRECEDING);
t3_id	min	max	c_dist	lead2	nth	k
0	0	0	0.2	2	NULL	0
0	0	0	0.2	2	NULL	0
2	0	2	0.4	4	2	0
2	0	2	0.4	4	2	0
4	0	4	0.6	6	2	0
4	0	4	0.6	6	2	0
6	0	6	0.8	8	2	0
6	0	6	0.8	8	2	0
8	0	8	1	NULL	2	0
8	0	8	1	NULL	2	0
1	1	1	0.2	3	NULL	1
1	1	1	0.2	3	NULL	1
3	1	3	0.4	5	3	1
3	1	3	0.4	5	3	1
5	1	5	0.6	7	3	1
5	1	5	0.6	7	3	1
7	1	7	0.8	9	3	1
7	1	7	0.8	9	3	1
9	1	9	1	NULL	3	1
9	1	9	1	NULL	3	1
DROP TABLE t3;
----------------------------------------------------------------------
-    Window function in parent and subquery
----------------------------------------------------------------------
CREATE TABLE t(i INT, j INT);
INSERT INTO t VALUES (1,NULL);
INSERT INTO t VALUES (1,NULL);
INSERT INTO t VALUES (1,1);
INSERT INTO t VALUES (1,NULL);
INSERT INTO t VALUES (1,2);
INSERT INTO t VALUES (2,1);
INSERT INTO t VALUES (2,2);
INSERT INTO t VALUES (2,NULL);
INSERT INTO t VALUES (2,NULL);
CREATE TABLE t1 (id INTEGER, sex CHAR(1));
INSERT INTO t1 VALUES (1, 'M');
INSERT INTO t1 VALUES (2, 'F');
INSERT INTO t1 VALUES (3, 'F');
INSERT INTO t1 VALUES (4, 'F');
INSERT INTO t1 VALUES (5, 'M');
CREATE TABLE t2 (user_id INTEGER NOT NULL, date DATE);
INSERT INTO t2 VALUES (1, '2002-06-09');
INSERT INTO t2 VALUES (2, '2002-06-09');
INSERT INTO t2 VALUES (1, '2002-06-09');
INSERT INTO t2 VALUES (3, '2002-06-09');
INSERT INTO t2 VALUES (4, '2002-06-09');
INSERT INTO t2 VALUES (4, '2002-06-09');
INSERT INTO t2 VALUES (5, '2002-06-09');
SELECT t.*, MIN(t.rank) OVER (ROWS UNBOUNDED PRECEDING) min,
MAX(t.rank) OVER (ROWS UNBOUNDED PRECEDING) max FROM
(SELECT sex, id, date, ROW_NUMBER() OVER w AS row_no, RANK() OVER w AS `rank` FROM t1,t2
WHERE t1.id=t2.user_id
WINDOW w AS (PARTITION BY date ORDER BY id)
) AS t;
sex	id	date	row_no	rank	min	max
M	1	2002-06-09	1	1	1	1
M	1	2002-06-09	2	1	1	1
F	2	2002-06-09	3	3	1	3
F	3	2002-06-09	4	4	1	4
F	4	2002-06-09	5	5	1	5
F	4	2002-06-09	6	5	1	5
M	5	2002-06-09	7	7	1	7
----------------------------------------------------------------------
-    Multiple windows
----------------------------------------------------------------------
SELECT t1.*, RANK() OVER (ORDER BY sex) `rank`, MIN(id) OVER (ORDER BY sex,id ROWS UNBOUNDED PRECEDING) min FROM t1;
id	sex	rank	min
2	F	1	2
3	F	1	2
4	F	1	2
1	M	4	1
5	M	4	1
SELECT t1.*, PERCENT_RANK() OVER (ORDER BY sex) p_rank, MAX(id) OVER (ORDER BY sex,id ROWS UNBOUNDED PRECEDING) max FROM t1;
id	sex	p_rank	max
2	F	0	2
3	F	0	3
4	F	0	4
1	M	0.75	4
5	M	0.75	5
SELECT t1.*, CUME_DIST() OVER (ORDER BY sex) c_dist, MIN(id) OVER (ORDER BY sex,id ROWS UNBOUNDED PRECEDING) min FROM t1;
id	sex	c_dist	min
2	F	0.6	2
3	F	0.6	2
4	F	0.6	2
1	M	1	1
5	M	1	1
SELECT * from (SELECT t1.*, MIN(id) OVER (ROWS UNBOUNDED PRECEDING) min, RANK() OVER (ORDER BY sex) `rank` FROM t1) alias ORDER BY id;
id	sex	min	rank
1	M	1	4
2	F	1	1
3	F	1	1
4	F	1	1
5	M	1	4
SELECT * from (SELECT t1.*, MAX(id) OVER (ROWS UNBOUNDED PRECEDING) max, PERCENT_RANK() OVER (ORDER BY sex) p_rank FROM t1) alias ORDER BY id;
id	sex	max	p_rank
1	M	1	0.75
2	F	2	0
3	F	3	0
4	F	4	0
5	M	5	0.75
SELECT * from (SELECT t1.*, MAX(id) OVER (ROWS UNBOUNDED PRECEDING) max, CUME_DIST() OVER (ORDER BY sex) c_dist FROM t1) alias ORDER BY id;
id	sex	max	c_dist
1	M	1	1
2	F	2	0.6
3	F	3	0.6
4	F	4	0.6
5	M	5	1
SELECT t1.*, MIN(id) OVER (ORDER BY id ROWS UNBOUNDED PRECEDING) min,
MAX(id) OVER (ORDER BY id ROWS UNBOUNDED PRECEDING) max,
RANK() OVER (ORDER BY sex,id) `rank`,
ROW_NUMBER() OVER (ORDER BY sex,id) row_num
FROM t1;
id	sex	min	max	rank	row_num
2	F	1	2	1	1
3	F	1	3	2	2
4	F	1	4	3	3
1	M	1	1	4	4
5	M	1	5	5	5
a little more windows + subquery
SELECT t.*, MIN(id + r00 + r01) OVER (ORDER BY id ROWS UNBOUNDED PRECEDING) AS min,
MAX(id + r00 + r01) OVER (ORDER BY id ROWS UNBOUNDED PRECEDING) AS max FROM (
SELECT t1.*,
RANK() OVER (ORDER BY sex, id) AS r00,
RANK() OVER (ORDER BY sex, id DESC) AS r01,
RANK() OVER (ORDER BY sex, id DESC) AS r02,
RANK() OVER (PARTITION BY id ORDER BY sex) AS r03,
RANK() OVER (ORDER BY sex,id) AS r04,
RANK() OVER (ORDER BY sex,id) AS r05,
RANK() OVER (ORDER BY sex, id) AS r06,
RANK() OVER (ORDER BY sex, id) AS r07,
RANK() OVER (ORDER BY sex, id) AS r08,
RANK() OVER (ORDER BY sex, id) AS r09,
RANK() OVER (ORDER BY sex, id) AS r10,
RANK() OVER (ORDER BY sex, id) AS r11,
RANK() OVER (ORDER BY sex, id) AS r12,
RANK() OVER (ORDER BY sex, id) AS r13,
RANK() OVER (ORDER BY sex, id) AS r14
FROM t1) t;
id	sex	r00	r01	r02	r03	r04	r05	r06	r07	r08	r09	r10	r11	r12	r13	r14	min	max
1	M	4	5	5	1	4	4	4	4	4	4	4	4	4	4	4	10	10
2	F	1	3	3	1	1	1	1	1	1	1	1	1	1	1	1	6	10
3	F	2	2	2	1	2	2	2	2	2	2	2	2	2	2	2	6	10
4	F	3	1	1	1	3	3	3	3	3	3	3	3	3	3	3	6	10
5	M	5	4	4	1	5	5	5	5	5	5	5	5	5	5	5	6	14
DROP TABLE t;
----------------------------------------------------------------------
-    MIN/MAX with frames
----------------------------------------------------------------------
SELECT AVG(id) OVER w, MIN(id) OVER w min, MAX(id) OVER w max FROM t1
WINDOW w AS (PARTITION BY sex);
AVG(id) OVER w	min	max
3.0000	2	4
3.0000	2	4
3.0000	2	4
3.0000	1	5
3.0000	1	5
SELECT * FROM (
SELECT id, SUM(id) OVER w, MIN(id) OVER w min, MAX(id) OVER w max, sex FROM t1
WINDOW w AS (PARTITION BY sex)
) alias ORDER BY id;
id	SUM(id) OVER w	min	max	sex
1	6	1	5	M
2	9	2	4	F
3	9	2	4	F
4	9	2	4	F
5	6	1	5	M
SELECT MIN(id) OVER w min FROM t1 WINDOW w AS (PARTITION BY sex);
min
2
2
2
1
1
SELECT MAX(id) OVER w max FROM t1 WINDOW w AS (PARTITION BY sex);
max
4
4
4
5
5
SELECT id, MIN(id) OVER w min, MAX(id) OVER w max, sex FROM t1
WINDOW w AS (PARTITION BY sex ORDER BY id
ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING);
id	min	max	sex
2	NULL	NULL	F
3	2	2	F
4	2	3	F
1	NULL	NULL	M
5	1	1	M
try the same as a view
CREATE VIEW v AS
SELECT id, SUM(id) OVER w, MIN(id) OVER w min, MAX(id) OVER w max, sex FROM t1
WINDOW w AS (PARTITION BY sex ORDER BY id
ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING);
SHOW CREATE VIEW v;
View	Create View	character_set_client	collation_connection
v	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select `t1`.`id` AS `id`,sum(`t1`.`id`) OVER `w` AS `SUM(id) OVER w`,min(`t1`.`id`) OVER `w` AS `min`,max(`t1`.`id`) OVER `w` AS `max`,`t1`.`sex` AS `sex` from `t1` window `w` AS (PARTITION BY `t1`.`sex` ORDER BY `t1`.`id` ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING) 	utf8mb3	utf8mb3_general_ci
SELECT * FROM v;
id	SUM(id) OVER w	min	max	sex
2	NULL	NULL	NULL	F
3	2	2	2	F
4	5	2	3	F
1	NULL	NULL	NULL	M
5	1	1	1	M
DROP VIEW v;
SELECT SUM(id) OVER w, MIN(id) OVER w min,
MAX(id) OVER w max FROM t1
WINDOW w AS (PARTITION BY sex ORDER BY id
ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING);
SUM(id) OVER w	min	max
NULL	NULL	NULL
2	2	2
5	2	3
NULL	NULL	NULL
1	1	1
SELECT id, SUM(id) OVER w, MIN(id) OVER w min,
MAX(id) OVER w max, sex FROM t1
WINDOW w AS (PARTITION BY sex ORDER BY id
ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING);
id	SUM(id) OVER w	min	max	sex
2	7	3	4	F
3	4	4	4	F
4	NULL	NULL	NULL	F
1	5	5	5	M
5	NULL	NULL	NULL	M
SELECT SUM(id) OVER w, COUNT(*) OVER w, MIN(id) OVER w min,
MAX(id) OVER w max FROM t1
WINDOW w AS (PARTITION BY sex ORDER BY id
ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING);
SUM(id) OVER w	COUNT(*) OVER w	min	max
7	2	3	4
4	1	4	4
NULL	0	NULL	NULL
5	1	5	5
NULL	0	NULL	NULL
SELECT id, AVG(id) OVER (ROWS UNBOUNDED PRECEDING) avg,
MIN(id) OVER (ROWS UNBOUNDED PRECEDING) min FROM t1;
id	avg	min
1	1.0000	1
2	1.5000	1
3	2.0000	1
4	2.5000	1
5	3.0000	1
SELECT id, AVG(id) OVER (ROWS UNBOUNDED PRECEDING),
MAX(id) OVER (ROWS UNBOUNDED PRECEDING) max FROM t1;
id	AVG(id) OVER (ROWS UNBOUNDED PRECEDING)	max
1	1.0000	1
2	1.5000	2
3	2.0000	3
4	2.5000	4
5	3.0000	5
SELECT id, AVG(id) OVER w avg, COUNT(id) OVER w count, MIN(id) OVER w min,
MAX(id) OVER w max FROM t1
WINDOW w AS (ORDER BY id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING);
id	avg	count	min	max
1	1.5000	2	1	2
2	2.0000	3	1	3
3	3.0000	3	2	4
4	4.0000	3	3	5
5	4.5000	2	4	5
MIN/MAX with double type
CREATE TABLE td(d DOUBLE);
INSERT INTO td VALUES (2);
INSERT INTO td VALUES (2);
INSERT INTO td VALUES (3);
INSERT INTO td VALUES (1);
INSERT INTO td VALUES (1.2);
INSERT INTO td VALUES (NULL);
SELECT d, MIN(d) OVER (ORDER BY d) min, MAX(d) OVER (ORDER BY d) max FROM td;
d	min	max
NULL	NULL	NULL
1	1	1
1.2	1	1.2
2	1	2
2	1	2
3	1	3
SELECT d, MIN(d) OVER () min, MAX(d) OVER () max FROM td;
d	min	max
2	1	3
2	1	3
3	1	3
1	1	3
1.2	1	3
NULL	1	3
SELECT d, MIN(d) OVER (ORDER BY d ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING) min FROM td;
d	min
NULL	1
1	1
1.2	1
2	1
2	1.2
3	2
SELECT d, MAX(d) OVER (ORDER BY d ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING) max FROM td;
d	max
NULL	1
1	1.2
1.2	2
2	2
2	3
3	3
TRUNCATE td;
INSERT INTO td VALUES (10);
INSERT INTO td VALUES (1);
INSERT INTO td VALUES (2);
INSERT INTO td VALUES (3);
INSERT INTO td VALUES (4);
INSERT INTO td VALUES (5);
INSERT INTO td VALUES (6);
INSERT INTO td VALUES (7);
INSERT INTO td VALUES (8);
INSERT INTO td VALUES (9);
SELECT d, MIN(d) OVER w min, MAX(d) OVER w max FROM td
WINDOW w AS (ORDER BY d RANGE BETWEEN 2 PRECEDING AND CURRENT ROW);
d	min	max
1	1	1
2	1	2
3	1	3
4	2	4
5	3	5
6	4	6
7	5	7
8	6	8
9	7	9
10	8	10
SELECT d, SUM(d) OVER w sum, MIN(d) OVER w min, MAX(d) OVER w max FROM td
WINDOW w AS (ORDER BY d RANGE BETWEEN 2 PRECEDING AND 2 FOLLOWING);
d	sum	min	max
1	6	1	3
2	10	1	4
3	15	1	5
4	20	2	6
5	25	3	7
6	30	4	8
7	35	5	9
8	40	6	10
9	34	7	10
10	27	8	10
SELECT d, SUM(d) OVER w sum, MIN(d) OVER w min, MAX(d) OVER w max FROM td
WINDOW w AS (ORDER BY d RANGE BETWEEN CURRENT ROW AND 2 FOLLOWING);
d	sum	min	max
1	6	1	3
2	9	2	4
3	12	3	5
4	15	4	6
5	18	5	7
6	21	6	8
7	24	7	9
8	27	8	10
9	19	9	10
10	10	10	10
INSERT INTO td SELECT * FROM td;
SELECT d, SUM(d) OVER w sum, MAX(d) OVER w max, MIN(d) OVER w min FROM td
WINDOW w AS (ORDER BY d RANGE BETWEEN 2 PRECEDING AND CURRENT ROW);
d	sum	max	min
1	2	1	1
1	2	1	1
2	6	2	1
2	6	2	1
3	12	3	1
3	12	3	1
4	18	4	2
4	18	4	2
5	24	5	3
5	24	5	3
6	30	6	4
6	30	6	4
7	36	7	5
7	36	7	5
8	42	8	6
8	42	8	6
9	48	9	7
9	48	9	7
10	54	10	8
10	54	10	8
SELECT d, SUM(d) OVER w sum, MAX(d) OVER w max, MIN(d) OVER w min FROM td
WINDOW w AS (ORDER BY d RANGE BETWEEN 2 PRECEDING AND 2 FOLLOWING);
d	sum	max	min
1	12	3	1
1	12	3	1
2	20	4	1
2	20	4	1
3	30	5	1
3	30	5	1
4	40	6	2
4	40	6	2
5	50	7	3
5	50	7	3
6	60	8	4
6	60	8	4
7	70	9	5
7	70	9	5
8	80	10	6
8	80	10	6
9	68	10	7
9	68	10	7
10	54	10	8
10	54	10	8
SELECT d, SUM(d) OVER w sum, MAX(d) OVER w max, MIN(d) OVER w min FROM td
WINDOW w AS (ORDER BY d RANGE BETWEEN CURRENT ROW AND 2 FOLLOWING);
d	sum	max	min
1	12	3	1
1	12	3	1
2	18	4	2
2	18	4	2
3	24	5	3
3	24	5	3
4	30	6	4
4	30	6	4
5	36	7	5
5	36	7	5
6	42	8	6
6	42	8	6
7	48	9	7
7	48	9	7
8	54	10	8
8	54	10	8
9	38	10	9
9	38	10	9
10	20	10	10
10	20	10	10
DROP TABLE td;
----------------------------------------------------------------------
-    Min/Max with frames in combination with non-framing window functions
-    ROW_NUMBER and RANK
----------------------------------------------------------------------
SELECT ROW_NUMBER() OVER w `row_number`, id, MIN(id) OVER w min, MAX(id) OVER w max, sex FROM t1
WINDOW w AS (PARTITION BY sex ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING);
row_number	id	min	max	sex
1	2	2	4	F
2	3	2	4	F
3	4	2	4	F
1	1	1	5	M
2	5	1	5	M
SELECT ROW_NUMBER() OVER w `row_number`, MIN(id) OVER w min,
MAX(id) OVER w max FROM t1
WINDOW w AS (PARTITION BY sex ORDER BY id
ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING);
row_number	min	max
1	3	4
2	4	4
3	NULL	NULL
1	5	5
2	NULL	NULL
INSERT INTO t1 VALUES (10, NULL);
SELECT RANK() OVER w `rank`, id, MIN(id) OVER w min, MAX(id) OVER w max, sex FROM t1
WINDOW w AS (PARTITION BY sex ORDER BY id);
rank	id	min	max	sex
1	10	10	10	NULL
1	2	2	2	F
2	3	2	3	F
3	4	2	4	F
1	1	1	1	M
2	5	1	5	M
SELECT RANK() OVER w `rank`, MIN(id) OVER w min,
MAX(id) OVER w max FROM t1
WINDOW w AS (PARTITION BY sex ORDER BY id
ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING);
rank	min	max
1	NULL	NULL
1	3	4
2	4	4
3	NULL	NULL
1	5	5
2	NULL	NULL
SELECT id, sex, MIN(id) OVER w min,
MAX(id) OVER w max,
ROW_NUMBER() OVER w `row_number`,
RANK() OVER w  `rank` FROM t1
WINDOW w AS (PARTITION BY sex ORDER BY id ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING);
id	sex	min	max	row_number	rank
10	NULL	NULL	NULL	1	1
2	F	NULL	NULL	1	1
3	F	2	2	2	2
4	F	2	3	3	3
1	M	NULL	NULL	1	1
5	M	1	1	2	2
SELECT id, sex, MIN(id) OVER w min,
MAX(id) OVER w max,
ROW_NUMBER() OVER w `row_number`,
CUME_DIST() OVER w  `cume_dist` FROM t1
WINDOW w AS (PARTITION BY sex ORDER BY id ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING);
id	sex	min	max	row_number	cume_dist
10	NULL	NULL	NULL	1	1
2	F	NULL	NULL	1	0.3333333333333333
3	F	2	2	2	0.6666666666666666
4	F	2	3	3	1
1	M	NULL	NULL	1	0.5
5	M	1	1	2	1
----------------------------------------------------------------------
-    Min/Max with RANGE frame specification
----------------------------------------------------------------------
Make t11 a clone of t1 but with an extra partitioning column, but other values
repeated, so we can test it the same frames work on more than one partition
CREATE TABLE t11 (id INTEGER, sex CHAR(1), p INTEGER DEFAULT 1);
INSERT INTO t11(id, sex) SELECT * FROM t1;
UPDATE t11 SET p=p+1;
INSERT INTO t11(id, sex) SELECT * FROM t1;
Make t22 a clone of t2 but with an extra partitioning column, but other values
repeated, so we can test it the same frames work on more than one partition
CREATE TABLE t22 (user_id INTEGER NOT NULL, date DATE, p INTEGER DEFAULT 1);
INSERT INTO t22(user_id, date) SELECT * FROM t2;
UPDATE t22 SET p=p+1;
INSERT INTO t22(user_id, date) SELECT * FROM t2;
SELECT id, MIN(id) OVER (ORDER BY id RANGE 2 PRECEDING) min FROM t1 ORDER BY id;
id	min
1	1
2	1
3	1
4	2
5	3
10	10
SELECT id, MIN(id) OVER (ORDER BY id RANGE 2 PRECEDING) max FROM t1 ORDER BY id;
id	max
1	1
2	1
3	1
4	2
5	3
10	10
SELECT id, MIN(id) OVER (ORDER BY id RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) min FROM t1 ORDER BY id;
id	min
1	1
2	1
3	2
4	3
5	4
10	10
SELECT id, MAX(id) OVER (ORDER BY id RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) max FROM t1 ORDER BY id;
id	max
1	2
2	3
3	4
4	5
5	5
10	10
SELECT id, MIN(id) OVER (ORDER BY id RANGE UNBOUNDED PRECEDING) min FROM t1 ORDER BY id;
id	min
1	1
2	1
3	1
4	1
5	1
10	1
SELECT id, MAX(id) OVER (ORDER BY id RANGE UNBOUNDED PRECEDING) max FROM t1 ORDER BY id;
id	max
1	1
2	2
3	3
4	4
5	5
10	10
SELECT p, id, MIN(id) OVER (PARTITION BY p ORDER BY id RANGE 2 PRECEDING) min FROM t11 ORDER BY p,id;
p	id	min
1	1	1
1	2	1
1	3	1
1	4	2
1	5	3
1	10	10
2	1	1
2	2	1
2	3	1
2	4	2
2	5	3
2	10	10
SELECT p, id, MAX(id) OVER (PARTITION BY p ORDER BY id RANGE 2 PRECEDING) max FROM t11 ORDER BY p,id;
p	id	max
1	1	1
1	2	2
1	3	3
1	4	4
1	5	5
1	10	10
2	1	1
2	2	2
2	3	3
2	4	4
2	5	5
2	10	10
SELECT p, id, MIN(id) OVER (PARTITION BY p ORDER BY id RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) min FROM t11 ORDER BY p,id;
p	id	min
1	1	1
1	2	1
1	3	2
1	4	3
1	5	4
1	10	10
2	1	1
2	2	1
2	3	2
2	4	3
2	5	4
2	10	10
SELECT p, id, MAX(id) OVER (PARTITION BY p ORDER BY id RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) max FROM t11 ORDER BY p,id;
p	id	max
1	1	2
1	2	3
1	3	4
1	4	5
1	5	5
1	10	10
2	1	2
2	2	3
2	3	4
2	4	5
2	5	5
2	10	10
SELECT p, id, MIN(id) OVER (PARTITION BY p ORDER BY id RANGE UNBOUNDED PRECEDING) min FROM t11 ORDER BY p,id;
p	id	min
1	1	1
1	2	1
1	3	1
1	4	1
1	5	1
1	10	1
2	1	1
2	2	1
2	3	1
2	4	1
2	5	1
2	10	1
SELECT p, id, MAX(id) OVER (PARTITION BY p ORDER BY id RANGE UNBOUNDED PRECEDING) max FROM t11 ORDER BY p,id;
p	id	max
1	1	1
1	2	2
1	3	3
1	4	4
1	5	5
1	10	10
2	1	1
2	2	2
2	3	3
2	4	4
2	5	5
2	10	10
Implicit frame due to ORDER BY, with last in peer group as upper bound
SELECT user_id, MIN(user_id) OVER w, MAX(user_id) OVER w FROM t2 WINDOW w AS (ORDER BY user_id);
user_id	MIN(user_id) OVER w	MAX(user_id) OVER w
1	1	1
1	1	1
2	1	2
3	1	3
4	1	4
4	1	4
5	1	5
SELECT p, user_id, MIN(user_id) OVER w, MAX(user_id) OVER w FROM t22 WINDOW w AS (PARTITION BY p ORDER BY user_id) ORDER BY p;
p	user_id	MIN(user_id) OVER w	MAX(user_id) OVER w
1	1	1	1
1	1	1	1
1	2	1	2
1	3	1	3
1	4	1	4
1	4	1	4
1	5	1	5
2	1	1	1
2	1	1	1
2	2	1	2
2	3	1	3
2	4	1	4
2	4	1	4
2	5	1	5
SELECT user_id, MIN(user_id) OVER w, MAX(user_id) OVER w1 FROM t2
WINDOW w AS (ORDER BY user_id), w1 AS (ORDER BY user_id);
user_id	MIN(user_id) OVER w	MAX(user_id) OVER w1
1	1	1
1	1	1
2	1	2
3	1	3
4	1	4
4	1	4
5	1	5
Check descending order by with RANGE: 2 PRECEDING in this case means larger than
current row.
SELECT NTILE(5) OVER w, ROW_NUMBER() OVER w, id, MIN(id) OVER w FROM t1
WINDOW w AS (ORDER BY id DESC RANGE 2 PRECEDING);
NTILE(5) OVER w	ROW_NUMBER() OVER w	id	MIN(id) OVER w
1	1	10	10
1	2	5	5
2	3	4	4
3	4	3	3
4	5	2	2
5	6	1	1
SELECT p, NTILE(5) OVER w, ROW_NUMBER() OVER w, id, MAX(id) OVER w FROM t11
WINDOW w AS (PARTITION BY p ORDER BY id DESC RANGE 2 PRECEDING);
p	NTILE(5) OVER w	ROW_NUMBER() OVER w	id	MAX(id) OVER w
1	1	1	10	10
1	1	2	5	5
1	2	3	4	5
1	3	4	3	5
1	4	5	2	4
1	5	6	1	3
2	1	1	10	10
2	1	2	5	5
2	2	3	4	5
2	3	4	3	5
2	4	5	2	4
2	5	6	1	3
SELECT NTILE(5) OVER w, ROW_NUMBER() OVER w, id, MIN(id) OVER w FROM t1
WINDOW w AS (ORDER BY id DESC RANGE INTERVAL 2 MONTH PRECEDING);
ERROR HY000: Window 'w' with RANGE frame has ORDER BY expression of numeric type, INTERVAL bound value not allowed.
DROP TABLE t11,t22,t1,t2;
CREATE TABLE t3(d DOUBLE);
INSERT INTO t3 VALUES (1.1);
INSERT INTO t3 VALUES (1.9);
INSERT INTO t3 VALUES (4.0);
INSERT INTO t3 VALUES (8.3);
INSERT INTO t3 VALUES (16.0);
INSERT INTO t3 VALUES (24.0);
INSERT INTO t3 VALUES (20.1);
INSERT INTO t3 VALUES (22.0);
INSERT INTO t3 VALUES (23.0);
SELECT d, MIN(d) OVER w min, MAX(d) OVER w max, COUNT(*) OVER w
FROM t3 WINDOW w AS (ORDER BY d RANGE BETWEEN 2.1 PRECEDING AND 1.1 FOLLOWING);
d	min	max	COUNT(*) OVER w
1.1	1.1	1.9	2
1.9	1.1	1.9	2
4	1.9	4	2
8.3	8.3	8.3	1
16	16	16	1
20.1	20.1	20.1	1
22	20.1	23	3
23	22	24	3
24	22	24	3
DROP TABLE t3;
----------------------------------------------------------------------
-    Min/Max over JSON
----------------------------------------------------------------------
CREATE TABLE tj(j JSON, i INT DEFAULT 7);
INSERT INTO tj(j) VALUES ('1');
INSERT INTO tj(j) VALUES ('2');
INSERT INTO tj(j) VALUES ('3');
INSERT INTO tj(j) VALUES ('4');
INSERT INTO tj(j) VALUES ('5');
INSERT INTO tj(j) VALUES (NULL);
INSERT INTO tj(j) VALUES ('3.14');
INSERT INTO tj(j) VALUES ('[1,2,3]');
INSERT INTO tj(j) VALUES (NULL);
SELECT MIN(j) OVER () AS JSON FROM tj;
JSON
1
1
1
1
1
1
1
1
1
SELECT j, JSON_TYPE(j), MIN(j) OVER (ORDER BY j ROWS 3 PRECEDING) FROM tj;
j	JSON_TYPE(j)	MIN(j) OVER (ORDER BY j ROWS 3 PRECEDING)
NULL	NULL	NULL
NULL	NULL	NULL
1	INTEGER	1
2	INTEGER	1
3	INTEGER	1
3.14	DOUBLE	1
4	INTEGER	2
5	INTEGER	3
[1, 2, 3]	ARRAY	3.14
Warnings:
Warning	1235	This version of MySQL doesn't yet support 'sorting of non-scalar JSON values'
SELECT j, JSON_TYPE(j), MAX(j) OVER (ORDER BY j ROWS 3 PRECEDING) FROM tj;
j	JSON_TYPE(j)	MAX(j) OVER (ORDER BY j ROWS 3 PRECEDING)
NULL	NULL	NULL
NULL	NULL	NULL
1	INTEGER	1
2	INTEGER	2
3	INTEGER	3
3.14	DOUBLE	3.14
4	INTEGER	4
5	INTEGER	5
[1, 2, 3]	ARRAY	[1, 2, 3]
Warnings:
Warning	1235	This version of MySQL doesn't yet support 'sorting of non-scalar JSON values'
SELECT j, JSON_TYPE(j), JSON_TYPE(MIN(j) OVER (ORDER BY j RANGE 3 PRECEDING)) FROM tj;
ERROR HY000: Window '<unnamed window>' with RANGE N PRECEDING/FOLLOWING frame requires exactly one ORDER BY expression, of numeric or temporal type
INSERT INTO tj(j) VALUES ('3.14');
SELECT j, JSON_TYPE(j), JSON_TYPE(MAX(j) OVER (ORDER BY j ROWS 3 PRECEDING)) FROM tj;
j	JSON_TYPE(j)	JSON_TYPE(MAX(j) OVER (ORDER BY j ROWS 3 PRECEDING))
NULL	NULL	NULL
NULL	NULL	NULL
1	INTEGER	INTEGER
2	INTEGER	INTEGER
3	INTEGER	INTEGER
3.14	DOUBLE	DOUBLE
3.14	DOUBLE	DOUBLE
4	INTEGER	INTEGER
5	INTEGER	INTEGER
[1, 2, 3]	ARRAY	ARRAY
Warnings:
Warning	1235	This version of MySQL doesn't yet support 'sorting of non-scalar JSON values'
INSERT INTO tj(j) VALUES ('[1,2,3]');
SELECT j,
JSON_TYPE(j),
MIN(CASE WHEN JSON_TYPE(j) = 'ARRAY' THEN j->"$[0]" ELSE j END)
OVER (ORDER BY j ROWS 3 PRECEDING)
FROM tj;
j	JSON_TYPE(j)	MIN(CASE WHEN JSON_TYPE(j) = 'ARRAY' THEN j->"$[0]" ELSE j END)
OVER (ORDER BY j ROWS 3 PRECEDING)
NULL	NULL	NULL
NULL	NULL	NULL
1	INTEGER	1
2	INTEGER	1
3	INTEGER	1
3.14	DOUBLE	1
3.14	DOUBLE	2
4	INTEGER	3
5	INTEGER	3.14
[1, 2, 3]	ARRAY	1
[1, 2, 3]	ARRAY	1
Warnings:
Warning	1235	This version of MySQL doesn't yet support 'sorting of non-scalar JSON values'
SELECT DISTINCT i,NTILE(3) OVER (ORDER BY i), MAX(i) OVER (), COUNT(*) OVER () FROM tj ORDER BY NTILE(3) OVER (ORDER BY i);
i	NTILE(3) OVER (ORDER BY i)	MAX(i) OVER ()	COUNT(*) OVER ()
7	1	7	11
7	2	7	11
7	3	7	11
UPDATE tj SET i=i+CASE WHEN JSON_TYPE(j) = 'ARRAY' THEN 1 ELSE j END;
UPDATE tj SET i=7 where i=8 AND JSON_TYPE(j) != 'ARRAY';
CREATE TABLE tj2 AS SELECT * FROM tj;
UPDATE tj2 SET i=MOD(i,3);
SELECT * FROM tj2;
j	i
1	1
2	0
3	1
4	2
5	0
NULL	NULL
3.14	1
[1, 2, 3]	2
NULL	NULL
3.14	1
[1, 2, 3]	2
With GROUP BY
SELECT          COUNT(*) OVER (), MOD(MIN(i),2) FROM tj2 GROUP BY i ;
COUNT(*) OVER ()	MOD(MIN(i),2)
4	0
4	0
4	1
4	NULL
SELECT DISTINCT COUNT(*) OVER (), MOD(MAX(i),2) FROM tj2 GROUP BY i ;
COUNT(*) OVER ()	MOD(MAX(i),2)
4	0
4	1
4	NULL
Bug fix GROUP BY with window function referring column used in grouping expression
SELECT i, MIN(i) OVER (), MOD(MIN(i),2) FROM tj2 GROUP BY i ;
i	MIN(i) OVER ()	MOD(MIN(i),2)
0	0	0
1	0	1
2	0	0
NULL	0	NULL
SELECT i, MAX(MAX(i)) OVER (), MAX(i) OVER (ORDER BY i), MOD(MAX(i),2), MAX(i) FROM tj2 GROUP BY i ;
i	MAX(MAX(i)) OVER ()	MAX(i) OVER (ORDER BY i)	MOD(MAX(i),2)	MAX(i)
0	2	0	0	0
1	2	1	1	1
2	2	2	0	2
NULL	2	NULL	NULL	NULL
SELECT i, MIN(i) OVER (ORDER BY i) FROM tj UNION ALL SELECT i, MIN(i) OVER (ORDER BY i) FROM tj;
i	MIN(i) OVER (ORDER BY i)
10	7
10	7
10	7
10	7
10	7
10	7
11	7
11	7
12	7
12	7
7	7
7	7
8	7
8	7
8	7
8	7
9	7
9	7
NULL	NULL
NULL	NULL
NULL	NULL
NULL	NULL
SELECT j,CAST(MIN(j) OVER (PARTITION BY i) AS JSON), CAST(MAX(j) OVER () AS JSON) FROM tj;
j	CAST(MIN(j) OVER (PARTITION BY i) AS JSON)	CAST(MAX(j) OVER () AS JSON)
1	1	[1, 2, 3]
2	2	[1, 2, 3]
3	3	[1, 2, 3]
3.14	3	[1, 2, 3]
3.14	3	[1, 2, 3]
4	4	[1, 2, 3]
5	5	[1, 2, 3]
NULL	NULL	[1, 2, 3]
NULL	NULL	[1, 2, 3]
[1, 2, 3]	[1, 2, 3]	[1, 2, 3]
[1, 2, 3]	[1, 2, 3]	[1, 2, 3]
SELECT j,CAST(MIN(j) OVER (PARTITION BY i ROWS UNBOUNDED PRECEDING) AS JSON), CAST(MAX(j) OVER (PARTITION BY i ROWS UNBOUNDED PRECEDING) AS JSON) FROM tj;
j	CAST(MIN(j) OVER (PARTITION BY i ROWS UNBOUNDED PRECEDING) AS JSON)	CAST(MAX(j) OVER (PARTITION BY i ROWS UNBOUNDED PRECEDING) AS JSON)
1	1	1
2	2	2
3	3	3
3.14	3	3.14
3.14	3	3.14
4	4	4
5	5	5
NULL	NULL	NULL
NULL	NULL	NULL
[1, 2, 3]	[1, 2, 3]	[1, 2, 3]
[1, 2, 3]	[1, 2, 3]	[1, 2, 3]
DROP TABLE tj,tj2;
CREATE TABLE t1 (i INTEGER, j INTEGER);
INSERT INTO t1 VALUES (NULL,3),(NULL,3),(NULL,30),(NULL,30),(4,3),
(2,5),(3,7),(1,10),(5,20),(3,30);
SELECT i, j, MIN(i) OVER (PARTITION BY j ORDER BY i) FROM t1;
i	j	MIN(i) OVER (PARTITION BY j ORDER BY i)
NULL	3	NULL
NULL	3	NULL
4	3	4
2	5	2
3	7	3
1	10	1
5	20	5
NULL	30	NULL
NULL	30	NULL
3	30	3
DROP TABLE t1;
CREATE TABLE t1(a INT);
INSERT INTO t1 VALUES(1),(1),(1),(1),(NULL),(NULL),(NULL),(NULL);
SELECT a, MAX(a) OVER (ORDER BY a DESC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) FROM t1;
a	MAX(a) OVER (ORDER BY a DESC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)
1	1
1	1
1	1
1	1
NULL	1
NULL	NULL
NULL	NULL
NULL	NULL
SELECT a, MAX(a) OVER (ORDER BY a DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM t1;
a	MAX(a) OVER (ORDER BY a DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
1	1
1	1
1	1
1	1
NULL	NULL
NULL	NULL
NULL	NULL
NULL	NULL
SELECT a, MIN(a) OVER (ORDER BY a DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM t1;
a	MIN(a) OVER (ORDER BY a DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
1	1
1	1
1	1
1	1
NULL	NULL
NULL	NULL
NULL	NULL
NULL	NULL
TRUNCATE t1;
INSERT INTO t1 VALUES (2), (1), (4), (3), (NULL), (NULL), (NULL), (NULL);
SELECT a, MIN(a) OVER w                FROM t1 WINDOW w AS (ORDER BY a DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING);
a	MIN(a) OVER w
4	1
3	1
2	1
1	1
NULL	NULL
NULL	NULL
NULL	NULL
NULL	NULL
SELECT a, MAX(a) OVER w, MIN(a) OVER w FROM t1 WINDOW w AS (ORDER BY a DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING);
a	MAX(a) OVER w	MIN(a) OVER w
4	4	1
3	3	1
2	2	1
1	1	1
NULL	NULL	NULL
NULL	NULL	NULL
NULL	NULL	NULL
NULL	NULL	NULL
SELECT a, COUNT(a) OVER w, MAX(a) OVER w, MIN(a) OVER w FROM t1 WINDOW w AS (ORDER BY a DESC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW);
a	COUNT(a) OVER w	MAX(a) OVER w	MIN(a) OVER w
4	1	4	4
3	2	4	3
2	2	3	2
1	2	2	1
NULL	1	1	1
NULL	0	NULL	NULL
NULL	0	NULL	NULL
NULL	0	NULL	NULL
SELECT a, COUNT(a) OVER w, MIN(a) OVER w                FROM t1 WINDOW w AS (ORDER BY a DESC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW);
a	COUNT(a) OVER w	MIN(a) OVER w
4	1	4
3	2	3
2	2	2
1	2	1
NULL	1	1
NULL	0	NULL
NULL	0	NULL
NULL	0	NULL
SELECT a, MIN(a) OVER w                FROM t1 WINDOW w AS (ORDER BY a DESC RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING);
a	MIN(a) OVER w
4	1
3	1
2	1
1	1
NULL	NULL
NULL	NULL
NULL	NULL
NULL	NULL
SELECT a, MAX(a) OVER w, MIN(a) OVER w FROM t1 WINDOW w AS (ORDER BY a DESC RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING);
a	MAX(a) OVER w	MIN(a) OVER w
4	4	1
3	3	1
2	2	1
1	1	1
NULL	NULL	NULL
NULL	NULL	NULL
NULL	NULL	NULL
NULL	NULL	NULL
SELECT a, COUNT(a) OVER w, MAX(a) OVER w, MIN(a) OVER w FROM t1 WINDOW w AS (ORDER BY a DESC RANGE BETWEEN 1 PRECEDING AND CURRENT ROW);
a	COUNT(a) OVER w	MAX(a) OVER w	MIN(a) OVER w
4	1	4	4
3	2	4	3
2	2	3	2
1	2	2	1
NULL	0	NULL	NULL
NULL	0	NULL	NULL
NULL	0	NULL	NULL
NULL	0	NULL	NULL
SELECT a, COUNT(a) OVER w, MIN(a) OVER w                FROM t1 WINDOW w AS (ORDER BY a DESC RANGE BETWEEN 1 PRECEDING AND CURRENT ROW);
a	COUNT(a) OVER w	MIN(a) OVER w
4	1	4
3	2	3
2	2	2
1	2	1
NULL	0	NULL
NULL	0	NULL
NULL	0	NULL
NULL	0	NULL
SELECT a, MIN(a) OVER w min, MAX(a) OVER w max FROM t1 WINDOW w AS (ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);
a	min	max
4	4	4
3	3	4
2	2	4
1	1	4
NULL	1	4
NULL	1	4
NULL	1	4
NULL	1	4
SELECT a, MIN(a) OVER w min, MAX(a) OVER w max FROM t1 WINDOW w AS (ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING);
a	min	max
NULL	1	4
NULL	1	4
NULL	1	4
NULL	1	4
1	1	4
2	2	4
3	3	4
4	4	4
TRUNCATE t1;
INSERT INTO t1 VALUES (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL);
SELECT a, MIN(a) OVER w                FROM t1 WINDOW w AS (ORDER BY a DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING);
a	MIN(a) OVER w
NULL	NULL
NULL	NULL
NULL	NULL
NULL	NULL
NULL	NULL
NULL	NULL
NULL	NULL
NULL	NULL
SELECT a, MAX(a) OVER w, MIN(a) OVER w FROM t1 WINDOW w AS (ORDER BY a DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING);
a	MAX(a) OVER w	MIN(a) OVER w
NULL	NULL	NULL
NULL	NULL	NULL
NULL	NULL	NULL
NULL	NULL	NULL
NULL	NULL	NULL
NULL	NULL	NULL
NULL	NULL	NULL
NULL	NULL	NULL
SELECT a, COUNT(a) OVER w, MAX(a) OVER w, MIN(a) OVER w FROM t1 WINDOW w AS (ORDER BY a DESC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW);
a	COUNT(a) OVER w	MAX(a) OVER w	MIN(a) OVER w
NULL	0	NULL	NULL
NULL	0	NULL	NULL
NULL	0	NULL	NULL
NULL	0	NULL	NULL
NULL	0	NULL	NULL
NULL	0	NULL	NULL
NULL	0	NULL	NULL
NULL	0	NULL	NULL
SELECT a, COUNT(a) OVER w, MIN(a) OVER w                FROM t1 WINDOW w AS (ORDER BY a DESC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW);
a	COUNT(a) OVER w	MIN(a) OVER w
NULL	0	NULL
NULL	0	NULL
NULL	0	NULL
NULL	0	NULL
NULL	0	NULL
NULL	0	NULL
NULL	0	NULL
NULL	0	NULL
SELECT a, MIN(a) OVER w                FROM t1 WINDOW w AS (ORDER BY a DESC RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING);
a	MIN(a) OVER w
NULL	NULL
NULL	NULL
NULL	NULL
NULL	NULL
NULL	NULL
NULL	NULL
NULL	NULL
NULL	NULL
SELECT a, MAX(a) OVER w, MIN(a) OVER w FROM t1 WINDOW w AS (ORDER BY a DESC RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING);
a	MAX(a) OVER w	MIN(a) OVER w
NULL	NULL	NULL
NULL	NULL	NULL
NULL	NULL	NULL
NULL	NULL	NULL
NULL	NULL	NULL
NULL	NULL	NULL
NULL	NULL	NULL
NULL	NULL	NULL
SELECT a, COUNT(a) OVER w, MAX(a) OVER w, MIN(a) OVER w FROM t1 WINDOW w AS (ORDER BY a DESC RANGE BETWEEN 1 PRECEDING AND CURRENT ROW);
a	COUNT(a) OVER w	MAX(a) OVER w	MIN(a) OVER w
NULL	0	NULL	NULL
NULL	0	NULL	NULL
NULL	0	NULL	NULL
NULL	0	NULL	NULL
NULL	0	NULL	NULL
NULL	0	NULL	NULL
NULL	0	NULL	NULL
NULL	0	NULL	NULL
SELECT a, COUNT(a) OVER w, MIN(a) OVER w                FROM t1 WINDOW w AS (ORDER BY a DESC RANGE BETWEEN 1 PRECEDING AND CURRENT ROW);
a	COUNT(a) OVER w	MIN(a) OVER w
NULL	0	NULL
NULL	0	NULL
NULL	0	NULL
NULL	0	NULL
NULL	0	NULL
NULL	0	NULL
NULL	0	NULL
NULL	0	NULL
DROP TABLE t1;
#
# Bug#31882291: WRONG RESULTS FROM MIN/MAX WINDOW FUNCTIONS
#               FOR TEMPORAL TYPES AND JSON
#
SELECT MIN(DATE'2000-01-01') OVER () AS c1,
MIN(TIME'12:00:00') OVER () AS c2,
MIN(TIMESTAMP'2000-01-01 12:00:00') OVER () AS c3,
JSON_ARRAY(MIN(CAST('123' AS JSON)) OVER ()) AS c4,
MIN(DATE'2000-01-01') OVER () = DATE'2000-01-01' AS c5,
MIN(TIME'12:00:00') OVER () = TIME '12:00:00' AS c6,
MIN(TIMESTAMP'2000-01-01 12:00:00') OVER () =
TIMESTAMP'2000-01-01 12:00:00' AS c7;
c1	c2	c3	c4	c5	c6	c7
2000-01-01	12:00:00	2000-01-01 12:00:00	[123]	1	1	1