Create 'main' test directory and move 't' and 'r' there

1 files changed, 864 insertions, 0 deletions

diff --git a/mysql-test/main/win_min_max.result b/mysql-test/main/win_min_max.result
new file mode 100644
index 00000000000..b3a90262bf7
--- /dev/null
+++ b/mysql-test/main/win_min_max.result
@@ -0,0 +1,864 @@
+create table t1 (
+pk int primary key,
+a int,
+b int,
+c real
+);
+insert into t1 values
+(101 , 0, 10, 1.1),
+(102 , 0, 10, 2.1),
+(103 , 1, 10, 3.1),
+(104 , 1, 10, 4.1),
+(108 , 2, 10, 5.1),
+(105 , 2, 20, 6.1),
+(106 , 2, 20, 7.1),
+(107 , 2, 20, 8.15),
+(109 , 4, 20, 9.15),
+(110 , 4, 20, 10.15),
+(111 , 5, NULL, 11.15),
+(112 , 5, 1, 12.25),
+(113 , 5, NULL, 13.35),
+(114 , 5, NULL, 14.50),
+(115 , 5, NULL, 15.65),
+(116 , 6, 1, NULL),
+(117 , 6, 1, 10),
+(118 , 6, 1, 1.1),
+(119 , 6, 1, NULL),
+(120 , 6, 1, NULL),
+(121 , 6, 1, NULL),
+(122 , 6, 1, 2.2),
+(123 , 6, 1, 20.1),
+(124 , 6, 1, -10.4),
+(125 , 6, 1, NULL),
+(126 , 6, 1, NULL),
+(127 , 6, 1, NULL);
+select pk, a, b, min(b) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as min,
+max(b) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as max
+from t1;
+pk	a	b	min	max
+101	0	10	10	10
+102	0	10	10	10
+103	1	10	10	10
+104	1	10	10	10
+105	2	20	20	20
+106	2	20	20	20
+107	2	20	10	20
+108	2	10	10	20
+109	4	20	20	20
+110	4	20	20	20
+111	5	NULL	1	1
+112	5	1	1	1
+113	5	NULL	1	1
+114	5	NULL	NULL	NULL
+115	5	NULL	NULL	NULL
+116	6	1	1	1
+117	6	1	1	1
+118	6	1	1	1
+119	6	1	1	1
+120	6	1	1	1
+121	6	1	1	1
+122	6	1	1	1
+123	6	1	1	1
+124	6	1	1	1
+125	6	1	1	1
+126	6	1	1	1
+127	6	1	1	1
+select pk, a, c, min(c) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as min,
+max(c) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as max
+from t1;
+pk	a	c	min	max
+101	0	1.1	1.1	2.1
+102	0	2.1	1.1	2.1
+103	1	3.1	3.1	4.1
+104	1	4.1	3.1	4.1
+105	2	6.1	6.1	7.1
+106	2	7.1	6.1	8.15
+107	2	8.15	5.1	8.15
+108	2	5.1	5.1	8.15
+109	4	9.15	9.15	10.15
+110	4	10.15	9.15	10.15
+111	5	11.15	11.15	12.25
+112	5	12.25	11.15	13.35
+113	5	13.35	12.25	14.5
+114	5	14.5	13.35	15.65
+115	5	15.65	14.5	15.65
+116	6	NULL	10	10
+117	6	10	1.1	10
+118	6	1.1	1.1	10
+119	6	NULL	1.1	1.1
+120	6	NULL	NULL	NULL
+121	6	NULL	2.2	2.2
+122	6	2.2	2.2	20.1
+123	6	20.1	-10.4	20.1
+124	6	-10.4	-10.4	20.1
+125	6	NULL	-10.4	-10.4
+126	6	NULL	NULL	NULL
+127	6	NULL	NULL	NULL
+create table t2 (
+pk int primary key,
+a int,
+b int,
+c char(10)
+);
+insert into t2 values
+( 1, 0,  1, 'one'),
+( 2, 0,  2, 'two'),
+( 3, 0,  3, 'three'),
+( 4, 1, 20, 'four'),
+( 5, 1, 10, 'five'),
+( 6, 1, 40, 'six'),
+( 7, 1, 30, 'seven'),
+( 8, 4,300, 'eight'),
+( 9, 4,100, 'nine'),
+(10, 4,200, 'ten'),
+(11, 4,200, 'eleven');
+# First try some invalid argument queries.
+select pk, a, b, c,
+min(c) over (order by pk),
+max(c) over (order by pk),
+min(c) over (partition by a order by pk),
+max(c) over (partition by a order by pk)
+from t2;
+pk	a	b	c	min(c) over (order by pk)	max(c) over (order by pk)	min(c) over (partition by a order by pk)	max(c) over (partition by a order by pk)
+1	0	1	one	one	one	one	one
+2	0	2	two	one	two	one	two
+3	0	3	three	one	two	one	two
+4	1	20	four	four	two	four	four
+5	1	10	five	five	two	five	four
+6	1	40	six	five	two	five	six
+7	1	30	seven	five	two	five	six
+8	4	300	eight	eight	two	eight	eight
+9	4	100	nine	eight	two	eight	nine
+10	4	200	ten	eight	two	eight	ten
+11	4	200	eleven	eight	two	eight	ten
+# Empty frame
+select pk, a, b, c,
+min(b) over (order by pk rows between 2 following and 1 following) as min1,
+max(b) over (order by pk rows between 2 following and 1 following) as max1,
+min(b) over (partition by a order by pk rows between 2 following and 1 following) as min2,
+max(b) over (partition by a order by pk rows between 2 following and 1 following) as max2
+from t2;
+pk	a	b	c	min1	max1	min2	max2
+1	0	1	one	NULL	NULL	NULL	NULL
+2	0	2	two	NULL	NULL	NULL	NULL
+3	0	3	three	NULL	NULL	NULL	NULL
+4	1	20	four	NULL	NULL	NULL	NULL
+5	1	10	five	NULL	NULL	NULL	NULL
+6	1	40	six	NULL	NULL	NULL	NULL
+7	1	30	seven	NULL	NULL	NULL	NULL
+8	4	300	eight	NULL	NULL	NULL	NULL
+9	4	100	nine	NULL	NULL	NULL	NULL
+10	4	200	ten	NULL	NULL	NULL	NULL
+11	4	200	eleven	NULL	NULL	NULL	NULL
+select pk, a, b, c,
+min(b) over (order by pk range between 2 following and 1 following) as min1,
+max(b) over (order by pk range between 2 following and 1 following) as max1,
+min(b) over (partition by a order by pk range between 2 following and 1 following) as min2,
+max(b) over (partition by a order by pk range between 2 following and 1 following) as max2
+from t2;
+pk	a	b	c	min1	max1	min2	max2
+1	0	1	one	NULL	NULL	NULL	NULL
+2	0	2	two	NULL	NULL	NULL	NULL
+3	0	3	three	NULL	NULL	NULL	NULL
+4	1	20	four	NULL	NULL	NULL	NULL
+5	1	10	five	NULL	NULL	NULL	NULL
+6	1	40	six	NULL	NULL	NULL	NULL
+7	1	30	seven	NULL	NULL	NULL	NULL
+8	4	300	eight	NULL	NULL	NULL	NULL
+9	4	100	nine	NULL	NULL	NULL	NULL
+10	4	200	ten	NULL	NULL	NULL	NULL
+11	4	200	eleven	NULL	NULL	NULL	NULL
+select pk, a, b, c,
+min(b) over (order by pk rows between 1 preceding and 2 preceding) as min1,
+max(b) over (order by pk rows between 1 preceding and 2 preceding) as max1,
+min(b) over (partition by a order by pk rows between 1 preceding and 2 preceding) as min2,
+max(b) over (partition by a order by pk rows between 1 preceding and 2 preceding) as max2
+from t2;
+pk	a	b	c	min1	max1	min2	max2
+1	0	1	one	NULL	NULL	NULL	NULL
+2	0	2	two	NULL	NULL	NULL	NULL
+3	0	3	three	NULL	NULL	NULL	NULL
+4	1	20	four	NULL	NULL	NULL	NULL
+5	1	10	five	NULL	NULL	NULL	NULL
+6	1	40	six	NULL	NULL	NULL	NULL
+7	1	30	seven	NULL	NULL	NULL	NULL
+8	4	300	eight	NULL	NULL	NULL	NULL
+9	4	100	nine	NULL	NULL	NULL	NULL
+10	4	200	ten	NULL	NULL	NULL	NULL
+11	4	200	eleven	NULL	NULL	NULL	NULL
+select pk, a, b, c,
+min(b) over (order by pk range between 1 preceding and 2 preceding) as min1,
+max(b) over (order by pk range between 1 preceding and 2 preceding) as max1,
+min(b) over (partition by a order by pk range between 1 preceding and 2 preceding) as min2,
+max(b) over (partition by a order by pk range between 1 preceding and 2 preceding) as max2
+from t2;
+pk	a	b	c	min1	max1	min2	max2
+1	0	1	one	NULL	NULL	NULL	NULL
+2	0	2	two	NULL	NULL	NULL	NULL
+3	0	3	three	NULL	NULL	NULL	NULL
+4	1	20	four	NULL	NULL	NULL	NULL
+5	1	10	five	NULL	NULL	NULL	NULL
+6	1	40	six	NULL	NULL	NULL	NULL
+7	1	30	seven	NULL	NULL	NULL	NULL
+8	4	300	eight	NULL	NULL	NULL	NULL
+9	4	100	nine	NULL	NULL	NULL	NULL
+10	4	200	ten	NULL	NULL	NULL	NULL
+11	4	200	eleven	NULL	NULL	NULL	NULL
+select pk, a, b, c,
+min(b) over (order by pk rows between 1 following and 0 following) as min1,
+max(b) over (order by pk rows between 1 following and 0 following) as max1,
+min(b) over (partition by a order by pk rows between 1 following and 0 following) as min2,
+max(b) over (partition by a order by pk rows between 1 following and 0 following) as max2
+from t2;
+pk	a	b	c	min1	max1	min2	max2
+1	0	1	one	NULL	NULL	NULL	NULL
+2	0	2	two	NULL	NULL	NULL	NULL
+3	0	3	three	NULL	NULL	NULL	NULL
+4	1	20	four	NULL	NULL	NULL	NULL
+5	1	10	five	NULL	NULL	NULL	NULL
+6	1	40	six	NULL	NULL	NULL	NULL
+7	1	30	seven	NULL	NULL	NULL	NULL
+8	4	300	eight	NULL	NULL	NULL	NULL
+9	4	100	nine	NULL	NULL	NULL	NULL
+10	4	200	ten	NULL	NULL	NULL	NULL
+11	4	200	eleven	NULL	NULL	NULL	NULL
+select pk, a, b, c,
+min(b) over (order by pk range between 1 following and 0 following) as min1,
+max(b) over (order by pk range between 1 following and 0 following) as max1,
+min(b) over (partition by a order by pk range between 1 following and 0 following) as min2,
+max(b) over (partition by a order by pk range between 1 following and 0 following) as max2
+from t2;
+pk	a	b	c	min1	max1	min2	max2
+1	0	1	one	NULL	NULL	NULL	NULL
+2	0	2	two	NULL	NULL	NULL	NULL
+3	0	3	three	NULL	NULL	NULL	NULL
+4	1	20	four	NULL	NULL	NULL	NULL
+5	1	10	five	NULL	NULL	NULL	NULL
+6	1	40	six	NULL	NULL	NULL	NULL
+7	1	30	seven	NULL	NULL	NULL	NULL
+8	4	300	eight	NULL	NULL	NULL	NULL
+9	4	100	nine	NULL	NULL	NULL	NULL
+10	4	200	ten	NULL	NULL	NULL	NULL
+11	4	200	eleven	NULL	NULL	NULL	NULL
+select pk, a, b, c,
+min(b) over (order by pk rows between 1 following and 0 preceding) as min1,
+max(b) over (order by pk rows between 1 following and 0 preceding) as max1,
+min(b) over (partition by a order by pk rows between 1 following and 0 preceding) as min2,
+max(b) over (partition by a order by pk rows between 1 following and 0 preceding) as max2
+from t2;
+pk	a	b	c	min1	max1	min2	max2
+1	0	1	one	NULL	NULL	NULL	NULL
+2	0	2	two	NULL	NULL	NULL	NULL
+3	0	3	three	NULL	NULL	NULL	NULL
+4	1	20	four	NULL	NULL	NULL	NULL
+5	1	10	five	NULL	NULL	NULL	NULL
+6	1	40	six	NULL	NULL	NULL	NULL
+7	1	30	seven	NULL	NULL	NULL	NULL
+8	4	300	eight	NULL	NULL	NULL	NULL
+9	4	100	nine	NULL	NULL	NULL	NULL
+10	4	200	ten	NULL	NULL	NULL	NULL
+11	4	200	eleven	NULL	NULL	NULL	NULL
+select pk, a, b, c,
+min(b) over (order by pk range between 1 following and 0 preceding) as min1,
+max(b) over (order by pk range between 1 following and 0 preceding) as max1,
+min(b) over (partition by a order by pk range between 1 following and 0 preceding) as min2,
+max(b) over (partition by a order by pk range between 1 following and 0 preceding) as max2
+from t2;
+pk	a	b	c	min1	max1	min2	max2
+1	0	1	one	NULL	NULL	NULL	NULL
+2	0	2	two	NULL	NULL	NULL	NULL
+3	0	3	three	NULL	NULL	NULL	NULL
+4	1	20	four	NULL	NULL	NULL	NULL
+5	1	10	five	NULL	NULL	NULL	NULL
+6	1	40	six	NULL	NULL	NULL	NULL
+7	1	30	seven	NULL	NULL	NULL	NULL
+8	4	300	eight	NULL	NULL	NULL	NULL
+9	4	100	nine	NULL	NULL	NULL	NULL
+10	4	200	ten	NULL	NULL	NULL	NULL
+11	4	200	eleven	NULL	NULL	NULL	NULL
+select pk, a, b, c,
+min(b) over (order by pk rows between 0 following and 1 preceding) as min1,
+max(b) over (order by pk rows between 0 following and 1 preceding) as max1,
+min(b) over (partition by a order by pk rows between 0 following and 1 preceding) as min2,
+max(b) over (partition by a order by pk rows between 0 following and 1 preceding) as max2
+from t2;
+pk	a	b	c	min1	max1	min2	max2
+1	0	1	one	NULL	NULL	NULL	NULL
+2	0	2	two	NULL	NULL	NULL	NULL
+3	0	3	three	NULL	NULL	NULL	NULL
+4	1	20	four	NULL	NULL	NULL	NULL
+5	1	10	five	NULL	NULL	NULL	NULL
+6	1	40	six	NULL	NULL	NULL	NULL
+7	1	30	seven	NULL	NULL	NULL	NULL
+8	4	300	eight	NULL	NULL	NULL	NULL
+9	4	100	nine	NULL	NULL	NULL	NULL
+10	4	200	ten	NULL	NULL	NULL	NULL
+11	4	200	eleven	NULL	NULL	NULL	NULL
+select pk, a, b, c,
+min(b) over (order by pk range between 0 following and 1 preceding) as min1,
+max(b) over (order by pk range between 0 following and 1 preceding) as max1,
+min(b) over (partition by a order by pk range between 0 following and 1 preceding) as min2,
+max(b) over (partition by a order by pk range between 0 following and 1 preceding) as max2
+from t2;
+pk	a	b	c	min1	max1	min2	max2
+1	0	1	one	NULL	NULL	NULL	NULL
+2	0	2	two	NULL	NULL	NULL	NULL
+3	0	3	three	NULL	NULL	NULL	NULL
+4	1	20	four	NULL	NULL	NULL	NULL
+5	1	10	five	NULL	NULL	NULL	NULL
+6	1	40	six	NULL	NULL	NULL	NULL
+7	1	30	seven	NULL	NULL	NULL	NULL
+8	4	300	eight	NULL	NULL	NULL	NULL
+9	4	100	nine	NULL	NULL	NULL	NULL
+10	4	200	ten	NULL	NULL	NULL	NULL
+11	4	200	eleven	NULL	NULL	NULL	NULL
+# 1 row frame.
+select pk, a, b, c,
+min(b) over (order by pk rows between current row and current row) as min1,
+max(b) over (order by pk rows between current row and current row) as max1,
+min(b) over (partition by a order by pk rows between current row and current row) as min2,
+max(b) over (partition by a order by pk rows between current row and current row) as max2
+from t2;
+pk	a	b	c	min1	max1	min2	max2
+1	0	1	one	1	1	1	1
+2	0	2	two	2	2	2	2
+3	0	3	three	3	3	3	3
+4	1	20	four	20	20	20	20
+5	1	10	five	10	10	10	10
+6	1	40	six	40	40	40	40
+7	1	30	seven	30	30	30	30
+8	4	300	eight	300	300	300	300
+9	4	100	nine	100	100	100	100
+10	4	200	ten	200	200	200	200
+11	4	200	eleven	200	200	200	200
+select pk, a, b, c,
+min(b) over (order by pk rows between 0 preceding and current row) as min1,
+max(b) over (order by pk rows between 0 preceding and current row) as max1,
+min(b) over (partition by a order by pk rows between 0 preceding and current row) as min2,
+max(b) over (partition by a order by pk rows between 0 preceding and current row) as max2
+from t2;
+pk	a	b	c	min1	max1	min2	max2
+1	0	1	one	1	1	1	1
+2	0	2	two	2	2	2	2
+3	0	3	three	3	3	3	3
+4	1	20	four	20	20	20	20
+5	1	10	five	10	10	10	10
+6	1	40	six	40	40	40	40
+7	1	30	seven	30	30	30	30
+8	4	300	eight	300	300	300	300
+9	4	100	nine	100	100	100	100
+10	4	200	ten	200	200	200	200
+11	4	200	eleven	200	200	200	200
+select pk, a, b, c,
+min(b) over (order by pk rows between 0 preceding and 0 preceding) as min1,
+max(b) over (order by pk rows between 0 preceding and 0 preceding) as max1,
+min(b) over (partition by a order by pk rows between 0 preceding and 0 preceding) as min2,
+max(b) over (partition by a order by pk rows between 0 preceding and 0 preceding) as max2
+from t2;
+pk	a	b	c	min1	max1	min2	max2
+1	0	1	one	1	1	1	1
+2	0	2	two	2	2	2	2
+3	0	3	three	3	3	3	3
+4	1	20	four	20	20	20	20
+5	1	10	five	10	10	10	10
+6	1	40	six	40	40	40	40
+7	1	30	seven	30	30	30	30
+8	4	300	eight	300	300	300	300
+9	4	100	nine	100	100	100	100
+10	4	200	ten	200	200	200	200
+11	4	200	eleven	200	200	200	200
+select pk, a, b, c,
+min(b) over (order by pk rows between 1 preceding and 1 preceding) as min1,
+max(b) over (order by pk rows between 1 preceding and 1 preceding) as max1,
+min(b) over (partition by a order by pk rows between 1 preceding and 1 preceding) as min2,
+max(b) over (partition by a order by pk rows between 1 preceding and 1 preceding) as max2
+from t2;
+pk	a	b	c	min1	max1	min2	max2
+1	0	1	one	NULL	NULL	NULL	NULL
+2	0	2	two	1	1	1	1
+3	0	3	three	2	2	2	2
+4	1	20	four	3	3	NULL	NULL
+5	1	10	five	20	20	20	20
+6	1	40	six	10	10	10	10
+7	1	30	seven	40	40	40	40
+8	4	300	eight	30	30	NULL	NULL
+9	4	100	nine	300	300	300	300
+10	4	200	ten	100	100	100	100
+11	4	200	eleven	200	200	200	200
+select pk, a, b, c,
+min(b) over (order by pk rows between 1 following and 1 following) as min1,
+max(b) over (order by pk rows between 1 following and 1 following) as max1,
+min(b) over (partition by a order by pk rows between 1 following and 1 following) as min2,
+max(b) over (partition by a order by pk rows between 1 following and 1 following) as max2
+from t2;
+pk	a	b	c	min1	max1	min2	max2
+1	0	1	one	2	2	2	2
+2	0	2	two	3	3	3	3
+3	0	3	three	20	20	NULL	NULL
+4	1	20	four	10	10	10	10
+5	1	10	five	40	40	40	40
+6	1	40	six	30	30	30	30
+7	1	30	seven	300	300	NULL	NULL
+8	4	300	eight	100	100	100	100
+9	4	100	nine	200	200	200	200
+10	4	200	ten	200	200	200	200
+11	4	200	eleven	NULL	NULL	NULL	NULL
+# Try a larger offset.
+select pk, a, b, c,
+min(b) over (order by pk rows between 3 following and 3 following) as min1,
+max(b) over (order by pk rows between 3 following and 3 following) as max1,
+min(b) over (partition by a order by pk rows between 3 following and 3 following) as min2,
+max(b) over (partition by a order by pk rows between 3 following and 3 following) as max2
+from t2;
+pk	a	b	c	min1	max1	min2	max2
+1	0	1	one	20	20	NULL	NULL
+2	0	2	two	10	10	NULL	NULL
+3	0	3	three	40	40	NULL	NULL
+4	1	20	four	30	30	30	30
+5	1	10	five	300	300	NULL	NULL
+6	1	40	six	100	100	NULL	NULL
+7	1	30	seven	200	200	NULL	NULL
+8	4	300	eight	200	200	200	200
+9	4	100	nine	NULL	NULL	NULL	NULL
+10	4	200	ten	NULL	NULL	NULL	NULL
+11	4	200	eleven	NULL	NULL	NULL	NULL
+select pk, a, b, c,
+min(b) over (order by pk rows between 3 preceding and 3 preceding) as min1,
+max(b) over (order by pk rows between 3 preceding and 3 preceding) as max1,
+min(b) over (partition by a order by pk rows between 3 preceding and 3 preceding) as min2,
+max(b) over (partition by a order by pk rows between 3 preceding and 3 preceding) as max2
+from t2;
+pk	a	b	c	min1	max1	min2	max2
+1	0	1	one	NULL	NULL	NULL	NULL
+2	0	2	two	NULL	NULL	NULL	NULL
+3	0	3	three	NULL	NULL	NULL	NULL
+4	1	20	four	1	1	NULL	NULL
+5	1	10	five	2	2	NULL	NULL
+6	1	40	six	3	3	NULL	NULL
+7	1	30	seven	20	20	20	20
+8	4	300	eight	10	10	NULL	NULL
+9	4	100	nine	40	40	NULL	NULL
+10	4	200	ten	30	30	NULL	NULL
+11	4	200	eleven	300	300	300	300
+# 2 row frame.
+select pk, a, b, c,
+min(b) over (order by pk rows between current row and 1 following) as min1,
+max(b) over (order by pk rows between current row and 1 following) as max1,
+min(b) over (partition by a order by pk rows between current row and 1 following) as min2,
+max(b) over (partition by a order by pk rows between current row and 1 following) as max2
+from t2;
+pk	a	b	c	min1	max1	min2	max2
+1	0	1	one	1	2	1	2
+2	0	2	two	2	3	2	3
+3	0	3	three	3	20	3	3
+4	1	20	four	10	20	10	20
+5	1	10	five	10	40	10	40
+6	1	40	six	30	40	30	40
+7	1	30	seven	30	300	30	30
+8	4	300	eight	100	300	100	300
+9	4	100	nine	100	200	100	200
+10	4	200	ten	200	200	200	200
+11	4	200	eleven	200	200	200	200
+select pk, a, b, c,
+min(b) over (order by pk rows between 0 preceding and 1 following) as min1,
+max(b) over (order by pk rows between 0 preceding and 1 following) as max1,
+min(b) over (partition by a order by pk rows between 0 preceding and 1 following) as min2,
+max(b) over (partition by a order by pk rows between 0 preceding and 1 following) as max2
+from t2;
+pk	a	b	c	min1	max1	min2	max2
+1	0	1	one	1	2	1	2
+2	0	2	two	2	3	2	3
+3	0	3	three	3	20	3	3
+4	1	20	four	10	20	10	20
+5	1	10	five	10	40	10	40
+6	1	40	six	30	40	30	40
+7	1	30	seven	30	300	30	30
+8	4	300	eight	100	300	100	300
+9	4	100	nine	100	200	100	200
+10	4	200	ten	200	200	200	200
+11	4	200	eleven	200	200	200	200
+select pk, a, b, c,
+min(b) over (order by pk rows between 1 preceding and current row) as min1,
+max(b) over (order by pk rows between 1 preceding and current row) as max1,
+min(b) over (partition by a order by pk rows between 1 preceding and current row) as min2,
+max(b) over (partition by a order by pk rows between 1 preceding and current row) as max2
+from t2;
+pk	a	b	c	min1	max1	min2	max2
+1	0	1	one	1	1	1	1
+2	0	2	two	1	2	1	2
+3	0	3	three	2	3	2	3
+4	1	20	four	3	20	20	20
+5	1	10	five	10	20	10	20
+6	1	40	six	10	40	10	40
+7	1	30	seven	30	40	30	40
+8	4	300	eight	30	300	300	300
+9	4	100	nine	100	300	100	300
+10	4	200	ten	100	200	100	200
+11	4	200	eleven	200	200	200	200
+select pk, a, b, c,
+min(b) over (order by pk rows between 1 preceding and 0 preceding) as min1,
+max(b) over (order by pk rows between 1 preceding and 0 preceding) as max1,
+min(b) over (partition by a order by pk rows between 1 preceding and 0 preceding) as min2,
+max(b) over (partition by a order by pk rows between 1 preceding and 0 preceding) as max2
+from t2;
+pk	a	b	c	min1	max1	min2	max2
+1	0	1	one	1	1	1	1
+2	0	2	two	1	2	1	2
+3	0	3	three	2	3	2	3
+4	1	20	four	3	20	20	20
+5	1	10	five	10	20	10	20
+6	1	40	six	10	40	10	40
+7	1	30	seven	30	40	30	40
+8	4	300	eight	30	300	300	300
+9	4	100	nine	100	300	100	300
+10	4	200	ten	100	200	100	200
+11	4	200	eleven	200	200	200	200
+# Try a larger frame/offset.
+select pk, a, b, c,
+min(b) over (order by pk rows between current row and 3 following) as min1,
+max(b) over (order by pk rows between current row and 3 following) as max1,
+min(b) over (partition by a order by pk rows between current row and 3 following) as min2,
+max(b) over (partition by a order by pk rows between current row and 3 following) as max2
+from t2;
+pk	a	b	c	min1	max1	min2	max2
+1	0	1	one	1	20	1	3
+2	0	2	two	2	20	2	3
+3	0	3	three	3	40	3	3
+4	1	20	four	10	40	10	40
+5	1	10	five	10	300	10	40
+6	1	40	six	30	300	30	40
+7	1	30	seven	30	300	30	30
+8	4	300	eight	100	300	100	300
+9	4	100	nine	100	200	100	200
+10	4	200	ten	200	200	200	200
+11	4	200	eleven	200	200	200	200
+select pk, a, b, c,
+min(b) over (order by pk rows between 2 preceding and 1 following) as min1,
+max(b) over (order by pk rows between 2 preceding and 1 following) as max1,
+min(b) over (partition by a order by pk rows between 2 preceding and 1 following) as min2,
+max(b) over (partition by a order by pk rows between 2 preceding and 1 following) as max2
+from t2;
+pk	a	b	c	min1	max1	min2	max2
+1	0	1	one	1	2	1	2
+2	0	2	two	1	3	1	3
+3	0	3	three	1	20	1	3
+4	1	20	four	2	20	10	20
+5	1	10	five	3	40	10	40
+6	1	40	six	10	40	10	40
+7	1	30	seven	10	300	10	40
+8	4	300	eight	30	300	100	300
+9	4	100	nine	30	300	100	300
+10	4	200	ten	100	300	100	300
+11	4	200	eleven	100	200	100	200
+select pk, a, b, c,
+min(b) over (order by pk rows between 3 preceding and current row) as min1,
+max(b) over (order by pk rows between 3 preceding and current row) as max1,
+min(b) over (partition by a order by pk rows between 3 preceding and current row) as min2,
+max(b) over (partition by a order by pk rows between 3 preceding and current row) as max2
+from t2;
+pk	a	b	c	min1	max1	min2	max2
+1	0	1	one	1	1	1	1
+2	0	2	two	1	2	1	2
+3	0	3	three	1	3	1	3
+4	1	20	four	1	20	20	20
+5	1	10	five	2	20	10	20
+6	1	40	six	3	40	10	40
+7	1	30	seven	10	40	10	40
+8	4	300	eight	10	300	300	300
+9	4	100	nine	30	300	100	300
+10	4	200	ten	30	300	100	300
+11	4	200	eleven	100	300	100	300
+select pk, a, b, c,
+min(b) over (order by pk rows between 3 preceding and 0 preceding) as min1,
+max(b) over (order by pk rows between 3 preceding and 0 preceding) as max1,
+min(b) over (partition by a order by pk rows between 3 preceding and 0 preceding) as min2,
+max(b) over (partition by a order by pk rows between 3 preceding and 0 preceding) as max2
+from t2;
+pk	a	b	c	min1	max1	min2	max2
+1	0	1	one	1	1	1	1
+2	0	2	two	1	2	1	2
+3	0	3	three	1	3	1	3
+4	1	20	four	1	20	20	20
+5	1	10	five	2	20	10	20
+6	1	40	six	3	40	10	40
+7	1	30	seven	10	40	10	40
+8	4	300	eight	10	300	300	300
+9	4	100	nine	30	300	100	300
+10	4	200	ten	30	300	100	300
+11	4	200	eleven	100	300	100	300
+# Check range frame bounds
+select pk, a, b, c,
+min(b) over (order by pk range between current row and current row) as min1,
+max(b) over (order by pk range between current row and current row) as max1,
+min(b) over (partition by a order by pk range between current row and current row) as min2,
+max(b) over (partition by a order by pk range between current row and current row) as max2
+from t2;
+pk	a	b	c	min1	max1	min2	max2
+1	0	1	one	1	1	1	1
+2	0	2	two	2	2	2	2
+3	0	3	three	3	3	3	3
+4	1	20	four	20	20	20	20
+5	1	10	five	10	10	10	10
+6	1	40	six	40	40	40	40
+7	1	30	seven	30	30	30	30
+8	4	300	eight	300	300	300	300
+9	4	100	nine	100	100	100	100
+10	4	200	ten	200	200	200	200
+11	4	200	eleven	200	200	200	200
+select pk, a, b, c,
+min(b) over (order by pk range between 0 preceding and current row) as min1,
+max(b) over (order by pk range between 0 preceding and current row) as max1,
+min(b) over (partition by a order by pk range between 0 preceding and current row) as min2,
+max(b) over (partition by a order by pk range between 0 preceding and current row) as max2
+from t2;
+pk	a	b	c	min1	max1	min2	max2
+1	0	1	one	1	1	1	1
+2	0	2	two	2	2	2	2
+3	0	3	three	3	3	3	3
+4	1	20	four	20	20	20	20
+5	1	10	five	10	10	10	10
+6	1	40	six	40	40	40	40
+7	1	30	seven	30	30	30	30
+8	4	300	eight	300	300	300	300
+9	4	100	nine	100	100	100	100
+10	4	200	ten	200	200	200	200
+11	4	200	eleven	200	200	200	200
+select pk, a, b, c,
+min(b) over (order by pk range between 0 preceding and 0 preceding) as min1,
+max(b) over (order by pk range between 0 preceding and 0 preceding) as max1,
+min(b) over (partition by a order by pk range between 0 preceding and 0 preceding) as min2,
+max(b) over (partition by a order by pk range between 0 preceding and 0 preceding) as max2
+from t2;
+pk	a	b	c	min1	max1	min2	max2
+1	0	1	one	1	1	1	1
+2	0	2	two	2	2	2	2
+3	0	3	three	3	3	3	3
+4	1	20	four	20	20	20	20
+5	1	10	five	10	10	10	10
+6	1	40	six	40	40	40	40
+7	1	30	seven	30	30	30	30
+8	4	300	eight	300	300	300	300
+9	4	100	nine	100	100	100	100
+10	4	200	ten	200	200	200	200
+11	4	200	eleven	200	200	200	200
+select pk, a, b, c,
+min(b) over (order by pk range between 1 preceding and 1 preceding) as min1,
+max(b) over (order by pk range between 1 preceding and 1 preceding) as max1,
+min(b) over (partition by a order by pk range between 1 preceding and 1 preceding) as min2,
+max(b) over (partition by a order by pk range between 1 preceding and 1 preceding) as max2
+from t2;
+pk	a	b	c	min1	max1	min2	max2
+1	0	1	one	NULL	NULL	NULL	NULL
+2	0	2	two	1	1	1	1
+3	0	3	three	2	2	2	2
+4	1	20	four	3	3	NULL	NULL
+5	1	10	five	20	20	20	20
+6	1	40	six	10	10	10	10
+7	1	30	seven	40	40	40	40
+8	4	300	eight	30	30	NULL	NULL
+9	4	100	nine	300	300	300	300
+10	4	200	ten	100	100	100	100
+11	4	200	eleven	200	200	200	200
+select pk, a, b, c,
+min(b) over (order by pk range between 1 following and 1 following) as min1,
+max(b) over (order by pk range between 1 following and 1 following) as max1,
+min(b) over (partition by a order by pk range between 1 following and 1 following) as min2,
+max(b) over (partition by a order by pk range between 1 following and 1 following) as max2
+from t2;
+pk	a	b	c	min1	max1	min2	max2
+1	0	1	one	2	2	2	2
+2	0	2	two	3	3	3	3
+3	0	3	three	20	20	NULL	NULL
+4	1	20	four	10	10	10	10
+5	1	10	five	40	40	40	40
+6	1	40	six	30	30	30	30
+7	1	30	seven	300	300	NULL	NULL
+8	4	300	eight	100	100	100	100
+9	4	100	nine	200	200	200	200
+10	4	200	ten	200	200	200	200
+11	4	200	eleven	NULL	NULL	NULL	NULL
+# Try a larger offset.
+select pk, a, b, c,
+min(b) over (order by pk range between 3 following and 3 following) as min1,
+max(b) over (order by pk range between 3 following and 3 following) as max1,
+min(b) over (partition by a order by pk range between 3 following and 3 following) as min2,
+max(b) over (partition by a order by pk range between 3 following and 3 following) as max2
+from t2;
+pk	a	b	c	min1	max1	min2	max2
+1	0	1	one	20	20	NULL	NULL
+2	0	2	two	10	10	NULL	NULL
+3	0	3	three	40	40	NULL	NULL
+4	1	20	four	30	30	30	30
+5	1	10	five	300	300	NULL	NULL
+6	1	40	six	100	100	NULL	NULL
+7	1	30	seven	200	200	NULL	NULL
+8	4	300	eight	200	200	200	200
+9	4	100	nine	NULL	NULL	NULL	NULL
+10	4	200	ten	NULL	NULL	NULL	NULL
+11	4	200	eleven	NULL	NULL	NULL	NULL
+select pk, a, b, c,
+min(b) over (order by pk range between 3 preceding and 3 preceding) as min1,
+max(b) over (order by pk range between 3 preceding and 3 preceding) as max1,
+min(b) over (partition by a order by pk range between 3 preceding and 3 preceding) as min2,
+max(b) over (partition by a order by pk range between 3 preceding and 3 preceding) as max2
+from t2;
+pk	a	b	c	min1	max1	min2	max2
+1	0	1	one	NULL	NULL	NULL	NULL
+2	0	2	two	NULL	NULL	NULL	NULL
+3	0	3	three	NULL	NULL	NULL	NULL
+4	1	20	four	1	1	NULL	NULL
+5	1	10	five	2	2	NULL	NULL
+6	1	40	six	3	3	NULL	NULL
+7	1	30	seven	20	20	20	20
+8	4	300	eight	10	10	NULL	NULL
+9	4	100	nine	40	40	NULL	NULL
+10	4	200	ten	30	30	NULL	NULL
+11	4	200	eleven	300	300	300	300
+# 2 row frame.
+select pk, a, b, c,
+min(b) over (order by pk range between current row and 1 following) as min1,
+max(b) over (order by pk range between current row and 1 following) as max1,
+min(b) over (partition by a order by pk range between current row and 1 following) as min2,
+max(b) over (partition by a order by pk range between current row and 1 following) as max2
+from t2;
+pk	a	b	c	min1	max1	min2	max2
+1	0	1	one	1	2	1	2
+2	0	2	two	2	3	2	3
+3	0	3	three	3	20	3	3
+4	1	20	four	10	20	10	20
+5	1	10	five	10	40	10	40
+6	1	40	six	30	40	30	40
+7	1	30	seven	30	300	30	30
+8	4	300	eight	100	300	100	300
+9	4	100	nine	100	200	100	200
+10	4	200	ten	200	200	200	200
+11	4	200	eleven	200	200	200	200
+select pk, a, b, c,
+min(b) over (order by pk range between 0 preceding and 1 following) as min1,
+max(b) over (order by pk range between 0 preceding and 1 following) as max1,
+min(b) over (partition by a order by pk range between 0 preceding and 1 following) as min2,
+max(b) over (partition by a order by pk range between 0 preceding and 1 following) as max2
+from t2;
+pk	a	b	c	min1	max1	min2	max2
+1	0	1	one	1	2	1	2
+2	0	2	two	2	3	2	3
+3	0	3	three	3	20	3	3
+4	1	20	four	10	20	10	20
+5	1	10	five	10	40	10	40
+6	1	40	six	30	40	30	40
+7	1	30	seven	30	300	30	30
+8	4	300	eight	100	300	100	300
+9	4	100	nine	100	200	100	200
+10	4	200	ten	200	200	200	200
+11	4	200	eleven	200	200	200	200
+select pk, a, b, c,
+min(b) over (order by pk range between 1 preceding and current row) as min1,
+max(b) over (order by pk range between 1 preceding and current row) as max1,
+min(b) over (partition by a order by pk range between 1 preceding and current row) as min2,
+max(b) over (partition by a order by pk range between 1 preceding and current row) as max2
+from t2;
+pk	a	b	c	min1	max1	min2	max2
+1	0	1	one	1	1	1	1
+2	0	2	two	1	2	1	2
+3	0	3	three	2	3	2	3
+4	1	20	four	3	20	20	20
+5	1	10	five	10	20	10	20
+6	1	40	six	10	40	10	40
+7	1	30	seven	30	40	30	40
+8	4	300	eight	30	300	300	300
+9	4	100	nine	100	300	100	300
+10	4	200	ten	100	200	100	200
+11	4	200	eleven	200	200	200	200
+select pk, a, b, c,
+min(b) over (order by pk range between 1 preceding and 0 preceding) as min1,
+max(b) over (order by pk range between 1 preceding and 0 preceding) as max1,
+min(b) over (partition by a order by pk range between 1 preceding and 0 preceding) as min2,
+max(b) over (partition by a order by pk range between 1 preceding and 0 preceding) as max2
+from t2;
+pk	a	b	c	min1	max1	min2	max2
+1	0	1	one	1	1	1	1
+2	0	2	two	1	2	1	2
+3	0	3	three	2	3	2	3
+4	1	20	four	3	20	20	20
+5	1	10	five	10	20	10	20
+6	1	40	six	10	40	10	40
+7	1	30	seven	30	40	30	40
+8	4	300	eight	30	300	300	300
+9	4	100	nine	100	300	100	300
+10	4	200	ten	100	200	100	200
+11	4	200	eleven	200	200	200	200
+# Try a larger frame/offset.
+select pk, a, b, c,
+min(b) over (order by pk range between current row and 3 following) as min1,
+max(b) over (order by pk range between current row and 3 following) as max1,
+min(b) over (partition by a order by pk range between current row and 3 following) as min2,
+max(b) over (partition by a order by pk range between current row and 3 following) as max2
+from t2;
+pk	a	b	c	min1	max1	min2	max2
+1	0	1	one	1	20	1	3
+2	0	2	two	2	20	2	3
+3	0	3	three	3	40	3	3
+4	1	20	four	10	40	10	40
+5	1	10	five	10	300	10	40
+6	1	40	six	30	300	30	40
+7	1	30	seven	30	300	30	30
+8	4	300	eight	100	300	100	300
+9	4	100	nine	100	200	100	200
+10	4	200	ten	200	200	200	200
+11	4	200	eleven	200	200	200	200
+select pk, a, b, c,
+min(b) over (order by pk range between 2 preceding and 1 following) as min1,
+max(b) over (order by pk range between 2 preceding and 1 following) as max1,
+min(b) over (partition by a order by pk range between 2 preceding and 1 following) as min2,
+max(b) over (partition by a order by pk range between 2 preceding and 1 following) as max2
+from t2;
+pk	a	b	c	min1	max1	min2	max2
+1	0	1	one	1	2	1	2
+2	0	2	two	1	3	1	3
+3	0	3	three	1	20	1	3
+4	1	20	four	2	20	10	20
+5	1	10	five	3	40	10	40
+6	1	40	six	10	40	10	40
+7	1	30	seven	10	300	10	40
+8	4	300	eight	30	300	100	300
+9	4	100	nine	30	300	100	300
+10	4	200	ten	100	300	100	300
+11	4	200	eleven	100	200	100	200
+select pk, a, b, c,
+min(b) over (order by pk range between 3 preceding and current row) as min1,
+max(b) over (order by pk range between 3 preceding and current row) as max1,
+min(b) over (partition by a order by pk range between 3 preceding and current row) as min2,
+max(b) over (partition by a order by pk range between 3 preceding and current row) as max2
+from t2;
+pk	a	b	c	min1	max1	min2	max2
+1	0	1	one	1	1	1	1
+2	0	2	two	1	2	1	2
+3	0	3	three	1	3	1	3
+4	1	20	four	1	20	20	20
+5	1	10	five	2	20	10	20
+6	1	40	six	3	40	10	40
+7	1	30	seven	10	40	10	40
+8	4	300	eight	10	300	300	300
+9	4	100	nine	30	300	100	300
+10	4	200	ten	30	300	100	300
+11	4	200	eleven	100	300	100	300
+select pk, a, b, c,
+min(b) over (order by pk range between 3 preceding and 0 preceding) as min1,
+max(b) over (order by pk range between 3 preceding and 0 preceding) as max1,
+min(b) over (partition by a order by pk range between 3 preceding and 0 preceding) as min2,
+max(b) over (partition by a order by pk range between 3 preceding and 0 preceding) as max2
+from t2;
+pk	a	b	c	min1	max1	min2	max2
+1	0	1	one	1	1	1	1
+2	0	2	two	1	2	1	2
+3	0	3	three	1	3	1	3
+4	1	20	four	1	20	20	20
+5	1	10	five	2	20	10	20
+6	1	40	six	3	40	10	40
+7	1	30	seven	10	40	10	40
+8	4	300	eight	10	300	300	300
+9	4	100	nine	30	300	100	300
+10	4	200	ten	30	300	100	300
+11	4	200	eleven	100	300	100	300
+drop table t2;
+drop table t1;