File: test_window_rows.test

package info (click to toggle)
duckdb 1.5.1-3
links: PTS, VCS
area: main
in suites:
size: 299,196 kB
sloc: cpp: 865,414; ansic: 57,292; python: 18,871; sql: 12,663; lisp: 11,751; yacc: 7,412; lex: 1,682; sh: 747; makefile: 564
file content (2786 lines) | stat: -rw-r--r-- 34,174 bytes
parent folder | download | duplicates (4)
# name: test/sql/window/test_window_rows.test
# description: ROWS functionality tests. Taken from sqlite test/window8.test
# group: [window]

statement ok
PRAGMA enable_verification

# do_execsql_test 1.0
statement ok
CREATE TABLE t3(a TEXT, b TEXT, c INTEGER);

statement ok
INSERT INTO t3 VALUES
    ('HH', 'bb', 355), ('CC', 'aa', 158), ('BB', 'aa', 399), 
    ('FF', 'bb', 938), ('HH', 'aa', 480), ('FF', 'bb', 870), 
    ('JJ', 'aa', 768), ('JJ', 'aa', 899), ('GG', 'bb', 929), 
    ('II', 'bb', 421), ('GG', 'bb', 844), ('FF', 'bb', 574), 
    ('CC', 'bb', 822), ('GG', 'bb', 938), ('BB', 'aa', 660), 
    ('HH', 'aa', 979), ('BB', 'bb', 792), ('DD', 'aa', 845), 
    ('JJ', 'bb', 354), ('FF', 'bb', 295), ('JJ', 'aa', 234), 
    ('BB', 'bb', 840), ('AA', 'aa', 934), ('EE', 'aa', 113), 
    ('AA', 'bb', 309), ('BB', 'aa', 412), ('AA', 'aa', 911), 
    ('AA', 'bb', 572), ('II', 'aa', 398), ('II', 'bb', 250), 
    ('II', 'aa', 652), ('BB', 'bb', 633), ('AA', 'aa', 239), 
    ('FF', 'aa', 670), ('BB', 'bb', 705), ('HH', 'bb', 963), 
    ('CC', 'bb', 346), ('II', 'bb', 671), ('BB', 'aa', 247), 
    ('AA', 'aa', 223), ('GG', 'aa', 480), ('HH', 'aa', 790), 
    ('FF', 'aa', 208), ('BB', 'bb', 711), ('EE', 'aa', 777), 
    ('DD', 'bb', 716), ('CC', 'aa', 759), ('CC', 'aa', 430), 
    ('CC', 'aa', 607), ('DD', 'bb', 794), ('GG', 'aa', 148), 
    ('GG', 'aa', 634), ('JJ', 'bb', 257), ('DD', 'bb', 959), 
    ('FF', 'bb', 726), ('BB', 'aa', 762), ('JJ', 'bb', 336), 
    ('GG', 'aa', 335), ('HH', 'bb', 330), ('GG', 'bb', 160), 
    ('JJ', 'bb', 839), ('FF', 'aa', 618), ('BB', 'aa', 393), 
    ('EE', 'bb', 629), ('FF', 'aa', 667), ('AA', 'bb', 870), 
    ('FF', 'bb', 102), ('JJ', 'aa', 113), ('DD', 'aa', 224), 
    ('AA', 'bb', 627), ('HH', 'bb', 730), ('II', 'bb', 443), 
    ('HH', 'bb', 133), ('EE', 'bb', 252), ('II', 'bb', 805), 
    ('BB', 'bb', 786), ('EE', 'bb', 768), ('HH', 'bb', 683), 
    ('DD', 'bb', 238), ('DD', 'aa', 256);

# do_execsql_test 2.1.1
query I
SELECT row_number() OVER win 
FROM t3
  WINDOW win AS (
      ORDER BY c, b, a
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING  EXCLUDE NO OTHERS 
    )
----
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80

# do_execsql_test 2.1.2
query I
SELECT nth_value(c, 14) OVER win 
FROM t3
  WINDOW win AS (
      ORDER BY c, b, a
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING  EXCLUDE NO OTHERS 
    )
----
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247

# do_execsql_test 2.1.3
query III
SELECT min(c) OVER win, max(c) OVER win, sum(c) OVER win FROM t3
  WINDOW win AS (
      ORDER BY c, b, a
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW  EXCLUDE NO OTHERS 
    ) ORDER BY a, b, c;
----
102	223	1358
102	239	2293
102	911	38097
102	934	39960
102	309	4159
102	572	10643
102	627	13069
102	870	35417
102	247	2540
102	393	6608
102	399	7405
102	412	7817
102	660	16277
102	762	24077
102	633	14331
102	705	19673
102	711	20384
102	786	27176
102	792	28758
102	840	32858
102	158	767
102	430	8668
102	607	11824
102	759	23315
102	346	5506
102	822	31179
102	224	1582
102	256	3298
102	845	34547
102	238	2054
102	716	21100
102	794	29552
102	959	42795
102	113	215
102	777	26390
102	252	3042
102	629	13698
102	768	25613
102	208	1135
102	618	12442
102	667	16944
102	670	17614
102	102	102
102	295	3850
102	574	11217
102	726	21826
102	870	36287
102	938	40898
102	148	609
102	335	4824
102	480	9591
102	634	14965
102	160	927
102	844	33702
102	929	39026
102	938	41836
102	480	10071
102	790	27966
102	979	44737
102	133	461
102	330	4489
102	355	6215
102	683	18968
102	730	22556
102	963	43758
102	398	7006
102	652	15617
102	250	2790
102	421	8238
102	443	9111
102	671	18285
102	805	30357
102	113	328
102	234	1816
102	768	24845
102	899	37186
102	257	3555
102	336	5160
102	354	5860
102	839	32018

# do_execsql_test 2.2.1
query I
SELECT row_number() OVER win 
FROM t3
  WINDOW win AS (
      ORDER BY c, b, a
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING  EXCLUDE CURRENT ROW 
    )
----
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80

# do_execsql_test 2.2.2
query I
SELECT nth_value(c, 14) OVER win 
FROM t3
  WINDOW win AS (
      ORDER BY c, b, a
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING  EXCLUDE CURRENT ROW 
    )
----
250
250
250
250
250
250
250
250
250
250
250
250
250
250
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247

# do_execsql_test 2.2.3
query III
SELECT min(c) OVER win, max(c) OVER win, sum(c) OVER win FROM t3
  WINDOW win AS (
      ORDER BY c, b, a
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW  EXCLUDE CURRENT ROW 
    ) ORDER BY a, b, c;
----
102	208	1135
102	238	2054
102	899	37186
102	929	39026
102	295	3850
102	480	10071
102	618	12442
102	845	34547
102	239	2293
102	355	6215
102	398	7006
102	399	7405
102	652	15617
102	759	23315
102	629	13698
102	683	18968
102	705	19673
102	777	26390
102	790	27966
102	839	32018
102	148	609
102	421	8238
102	574	11217
102	730	22556
102	336	5160
102	805	30357
102	223	1358
102	252	3042
102	844	33702
102	234	1816
102	711	20384
102	792	28758
102	938	41836
102	102	102
102	768	25613
102	250	2790
102	627	13069
102	768	24845
102	160	927
102	607	11824
102	660	16277
102	667	16944
NULL	NULL	NULL
102	257	3555
102	572	10643
102	716	21100
102	870	35417
102	934	39960
102	133	461
102	330	4489
102	443	9111
102	633	14331
102	158	767
102	840	32858
102	911	38097
102	938	40898
102	480	9591
102	786	27176
102	963	43758
102	113	328
102	309	4159
102	354	5860
102	671	18285
102	726	21826
102	959	42795
102	393	6608
102	634	14965
102	247	2540
102	412	7817
102	430	8668
102	670	17614
102	794	29552
102	113	215
102	224	1582
102	762	24077
102	870	36287
102	256	3298
102	335	4824
102	346	5506
102	822	31179

# do_execsql_test 2.3.1
query I
SELECT row_number() OVER win 
FROM t3
  WINDOW win AS (
      ORDER BY c, b, a
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING  EXCLUDE GROUP 
    )
----
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80

# do_execsql_test 2.3.2
query I
SELECT nth_value(c, 14) OVER win 
FROM t3
  WINDOW win AS (
      ORDER BY c, b, a
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING  EXCLUDE GROUP 
    )
----
250
250
250
250
250
250
250
250
250
250
250
250
250
250
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247

# do_execsql_test 2.3.3
query III
SELECT min(c) OVER win, max(c) OVER win, sum(c) OVER win FROM t3
  WINDOW win AS (
      ORDER BY c, b, a
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW  EXCLUDE GROUP 
    ) ORDER BY a, b, c;
----
102	208	1135
102	238	2054
102	899	37186
102	929	39026
102	295	3850
102	480	10071
102	618	12442
102	845	34547
102	239	2293
102	355	6215
102	398	7006
102	399	7405
102	652	15617
102	759	23315
102	629	13698
102	683	18968
102	705	19673
102	777	26390
102	790	27966
102	839	32018
102	148	609
102	421	8238
102	574	11217
102	730	22556
102	336	5160
102	805	30357
102	223	1358
102	252	3042
102	844	33702
102	234	1816
102	711	20384
102	792	28758
102	938	41836
102	102	102
102	768	25613
102	250	2790
102	627	13069
102	768	24845
102	160	927
102	607	11824
102	660	16277
102	667	16944
NULL	NULL	NULL
102	257	3555
102	572	10643
102	716	21100
102	870	35417
102	934	39960
102	133	461
102	330	4489
102	443	9111
102	633	14331
102	158	767
102	840	32858
102	911	38097
102	938	40898
102	480	9591
102	786	27176
102	963	43758
102	113	328
102	309	4159
102	354	5860
102	671	18285
102	726	21826
102	959	42795
102	393	6608
102	634	14965
102	247	2540
102	412	7817
102	430	8668
102	670	17614
102	794	29552
102	113	215
102	224	1582
102	762	24077
102	870	36287
102	256	3298
102	335	4824
102	346	5506
102	822	31179

# do_execsql_test 2.4.1
query I
SELECT row_number() OVER win 
FROM t3
  WINDOW win AS (
      ORDER BY c, b, a
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING  EXCLUDE TIES 
    )
----
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80

# do_execsql_test 2.4.2
query I
SELECT nth_value(c, 14) OVER win 
FROM t3
  WINDOW win AS (
      ORDER BY c, b, a
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING  EXCLUDE TIES 
    )
----
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247
247

# do_execsql_test 2.4.3
query III
SELECT min(c) OVER win, max(c) OVER win, sum(c) OVER win FROM t3
  WINDOW win AS (
      ORDER BY c, b, a
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW  EXCLUDE TIES 
    ) ORDER BY a, b, c;
----
102	223	1358
102	239	2293
102	911	38097
102	934	39960
102	309	4159
102	572	10643
102	627	13069
102	870	35417
102	247	2540
102	393	6608
102	399	7405
102	412	7817
102	660	16277
102	762	24077
102	633	14331
102	705	19673
102	711	20384
102	786	27176
102	792	28758
102	840	32858
102	158	767
102	430	8668
102	607	11824
102	759	23315
102	346	5506
102	822	31179
102	224	1582
102	256	3298
102	845	34547
102	238	2054
102	716	21100
102	794	29552
102	959	42795
102	113	215
102	777	26390
102	252	3042
102	629	13698
102	768	25613
102	208	1135
102	618	12442
102	667	16944
102	670	17614
102	102	102
102	295	3850
102	574	11217
102	726	21826
102	870	36287
102	938	40898
102	148	609
102	335	4824
102	480	9591
102	634	14965
102	160	927
102	844	33702
102	929	39026
102	938	41836
102	480	10071
102	790	27966
102	979	44737
102	133	461
102	330	4489
102	355	6215
102	683	18968
102	730	22556
102	963	43758
102	398	7006
102	652	15617
102	250	2790
102	421	8238
102	443	9111
102	671	18285
102	805	30357
102	113	328
102	234	1816
102	768	24845
102	899	37186
102	257	3555
102	336	5160
102	354	5860
102	839	32018

#==========================================================================

# do_execsql_test 4.0
statement ok
DROP TABLE IF EXISTS t1;

statement ok
CREATE TABLE t1(a INTEGER, b INTEGER);

statement ok
INSERT INTO t1 VALUES
    (NULL, 1), (NULL, 2), (NULL, 3), (10, 4), (10, 5);

# do_execsql_test 4.4.1
query I
SELECT sum(b) OVER (
    ORDER BY a NULLS FIRST ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) FROM t1 ORDER BY 1 NULLS FIRST;
----
3
6
9
9
12

# do_execsql_test 4.4.2
query I
SELECT sum(b) OVER (
    ORDER BY a NULLS LAST ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) FROM t1 ORDER BY 1 NULLS LAST;
----
5
6
8
9
10

# do_execsql_test 4.4.3
query I
SELECT sum(b) OVER (
    ORDER BY a DESC NULLS LAST ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) FROM t1 ORDER BY 1 NULLS FIRST;
----
5
6
8
9
10

# do_execsql_test 4.4.4
query I
SELECT sum(b) OVER (
    ORDER BY a DESC NULLS LAST ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) FROM t1 ORDER BY 1 NULLS LAST;
----
5
6
8
9
10

#==========================================================================

# do_execsql_test 5.0
statement ok
INSERT INTO t3 VALUES
    (NULL, 'bb', 355), (NULL, 'cc', 158), (NULL, 'aa', 399), 
    ('JJ', NULL, 839), ('FF', NULL, 618), ('BB', NULL, 393), 
    (NULL, 'bb', 629), (NULL, NULL, 667), (NULL, NULL, 870);

# do_execsql_test 5.1.7.1
query III
SELECT max(c) OVER win,
             min(c) OVER win,
             count(a) OVER win
FROM t3
    WINDOW win AS (  ORDER BY c NULLS FIRST, b NULLS FIRST, a NULLS FIRST
        ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING   EXCLUDE NO OTHERS  )
      ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
----
979	102	83
979	102	83
979	102	83
979	102	83
979	102	83
979	102	83
979	102	83
979	113	81
979	113	82
979	133	80
979	148	79
979	158	77
979	158	78
979	160	77
979	208	76
979	223	75
979	224	74
979	234	73
979	238	72
979	239	71
979	247	70
979	250	69
979	252	68
979	256	67
979	257	66
979	295	65
979	309	64
979	330	63
979	335	62
979	336	61
979	346	60
979	354	59
979	355	58
979	355	58
979	393	56
979	393	57
979	398	55
979	399	54
979	399	54
979	412	53
979	421	52
979	430	51
979	443	50
979	480	48
979	480	49
979	572	47
979	574	46
979	607	45
979	618	43
979	618	44
979	627	42
979	629	41
979	629	41
979	633	40
979	634	39
979	652	38
979	660	37
979	667	36
979	667	36
979	670	35
979	671	34
979	683	33
979	705	32
979	711	31
979	716	30
979	726	29
979	730	28
979	759	27
979	762	26
979	768	24
979	768	25
979	777	23
979	786	22
979	790	21
979	792	20
979	794	19
979	805	18
979	822	17
979	839	15
979	839	16
979	840	14
979	844	13
979	845	12
979	870	10
979	870	11
979	870	11
979	899	9
979	911	8
979	929	7

# do_execsql_test 5.1.7.2
query III
SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
             rank() OVER win,
             dense_rank() OVER win
FROM t3
    WINDOW win AS (  ORDER BY c NULLS FIRST, b NULLS FIRST, a NULLS FIRST
        ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING   EXCLUDE NO OTHERS  )
      ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
----
3830	89	89
4741	88	88
5640	84	84
5640	85	85
5640	86	86
5640	87	87
6485	81	81
6485	82	82
6485	83	83
7324	80	80
8163	78	78
8163	79	79
8968	73	73
8968	74	74
8968	75	75
8968	76	76
8968	77	77
9745	69	69
9745	70	70
9745	71	71
9745	72	72
10504	65	65
10504	66	66
10504	67	67
10504	68	68
11215	64	64
11920	63	63
12603	62	62
13274	60	60
13274	61	61
13941	59	59
14608	55	55
14608	56	56
14608	57	57
14608	58	58
15241	54	54
15870	53	53
16499	52	52
17126	49	49
17126	50	50
17126	51	51
17733	44	44
17733	45	45
17733	46	46
17733	47	47
17733	48	48
18176	42	42
18176	43	43
18597	40	40
18597	41	41
18996	39	39
19395	37	37
19395	38	38
19788	36	36
20181	35	35
20536	34	34
20891	30	30
20891	31	31
20891	32	32
20891	33	33
21226	28	28
21226	29	29
21535	27	27
21830	26	26
22087	22	22
22087	23	23
22087	24	24
22087	25	25
22334	21	21
22573	17	17
22573	18	18
22573	19	19
22573	20	20
22796	11	11
22796	12	12
22796	13	13
22796	14	14
22796	15	15
22796	16	16
22929	10	10
23042	9	9
23155	1	1
23155	2	2
23155	3	3
23155	4	4
23155	5	5
23155	6	6
23155	7	7
23155	8	8

# do_execsql_test 5.1.14.1
query III
SELECT max(c) OVER win,
             min(c) OVER win,
             count(a) OVER win
FROM t3
    WINDOW win AS (  ORDER BY c NULLS LAST, b NULLS LAST, a NULLS LAST
        ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING   EXCLUDE NO OTHERS  )
      ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
----
979	102	83
979	102	83
979	102	83
979	102	83
979	102	83
979	102	83
979	102	83
979	113	81
979	113	82
979	133	80
979	148	79
979	158	77
979	158	78
979	160	77
979	208	76
979	223	75
979	224	74
979	234	73
979	238	72
979	239	71
979	247	70
979	250	69
979	252	68
979	256	67
979	257	66
979	295	65
979	309	64
979	330	63
979	335	62
979	336	61
979	346	60
979	354	59
979	355	57
979	355	58
979	393	56
979	393	57
979	398	55
979	399	53
979	399	54
979	412	53
979	421	52
979	430	51
979	443	50
979	480	48
979	480	49
979	572	47
979	574	46
979	607	45
979	618	43
979	618	44
979	627	42
979	629	40
979	629	41
979	633	40
979	634	39
979	652	38
979	660	37
979	667	35
979	667	36
979	670	35
979	671	34
979	683	33
979	705	32
979	711	31
979	716	30
979	726	29
979	730	28
979	759	27
979	762	26
979	768	24
979	768	25
979	777	23
979	786	22
979	790	21
979	792	20
979	794	19
979	805	18
979	822	17
979	839	15
979	839	16
979	840	14
979	844	13
979	845	12
979	870	9
979	870	10
979	870	11
979	899	9
979	911	8
979	929	7

# do_execsql_test 5.1.14.2
query III
SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
             rank() OVER win,
             dense_rank() OVER win
FROM t3
    WINDOW win AS (  ORDER BY c NULLS LAST, b NULLS LAST, a NULLS LAST
        ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING   EXCLUDE NO OTHERS  )
      ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
----
3830	89	89
4741	88	88
5640	84	84
5640	85	85
5640	86	86
5640	87	87
6485	81	81
6485	82	82
6485	83	83
7324	80	80
8163	78	78
8163	79	79
8968	73	73
8968	74	74
8968	75	75
8968	76	76
8968	77	77
9745	69	69
9745	70	70
9745	71	71
9745	72	72
10504	65	65
10504	66	66
10504	67	67
10504	68	68
11215	64	64
11920	63	63
12603	62	62
13274	60	60
13274	61	61
13941	59	59
14608	55	55
14608	56	56
14608	57	57
14608	58	58
15241	54	54
15870	53	53
16499	52	52
17126	49	49
17126	50	50
17126	51	51
17733	44	44
17733	45	45
17733	46	46
17733	47	47
17733	48	48
18176	42	42
18176	43	43
18597	40	40
18597	41	41
18996	39	39
19395	37	37
19395	38	38
19788	36	36
20181	35	35
20536	34	34
20891	30	30
20891	31	31
20891	32	32
20891	33	33
21226	28	28
21226	29	29
21535	27	27
21830	26	26
22087	22	22
22087	23	23
22087	24	24
22087	25	25
22334	21	21
22573	17	17
22573	18	18
22573	19	19
22573	20	20
22796	11	11
22796	12	12
22796	13	13
22796	14	14
22796	15	15
22796	16	16
22929	10	10
23042	9	9
23155	1	1
23155	2	2
23155	3	3
23155	4	4
23155	5	5
23155	6	6
23155	7	7
23155	8	8

# do_execsql_test 5.2.7.1
query III
SELECT max(c) OVER win,
             min(c) OVER win,
             count(a) OVER win
FROM t3
    WINDOW win AS (  ORDER BY c NULLS FIRST, b NULLS FIRST, a NULLS FIRST
        ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING   EXCLUDE CURRENT ROW  )
      ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
----
963	929	6
979	102	82
979	102	82
979	102	82
979	102	82
979	102	82
979	102	83
979	113	80
979	113	81
979	113	82
979	133	79
979	148	78
979	158	76
979	158	77
979	160	76
979	208	75
979	223	74
979	224	73
979	234	72
979	238	71
979	239	70
979	247	69
979	250	68
979	252	67
979	256	66
979	257	65
979	295	64
979	309	64
979	330	62
979	335	61
979	336	60
979	346	59
979	354	59
979	355	57
979	355	57
979	393	55
979	393	56
979	398	54
979	399	53
979	399	53
979	412	52
979	421	51
979	430	50
979	443	49
979	480	47
979	480	48
979	572	47
979	574	45
979	607	44
979	618	42
979	618	43
979	627	41
979	629	40
979	629	41
979	633	39
979	634	38
979	652	37
979	660	36
979	667	35
979	667	35
979	670	34
979	671	33
979	683	32
979	705	31
979	711	30
979	716	29
979	726	28
979	730	27
979	759	26
979	762	25
979	768	23
979	768	24
979	777	22
979	786	21
979	790	20
979	792	19
979	794	18
979	805	17
979	822	17
979	839	14
979	839	15
979	840	13
979	844	12
979	845	11
979	870	9
979	870	10
979	870	10
979	899	8
979	911	7

# do_execsql_test 5.2.7.2
query III
SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
             rank() OVER win,
             dense_rank() OVER win
FROM t3
    WINDOW win AS (  ORDER BY c NULLS FIRST, b NULLS FIRST, a NULLS FIRST
        ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING   EXCLUDE CURRENT ROW  )
      ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
----
2851	89	89
3778	88	88
4681	87	87
5556	83	83
5574	82	82
5586	81	81
5640	84	84
5640	85	85
5640	86	86
7324	80	80
8123	77	77
8129	73	73
8129	74	74
8163	78	78
8163	79	79
8940	71	71
8968	75	75
8968	76	76
9727	66	66
9745	69	69
9745	70	70
9745	72	72
10504	65	65
10504	67	67
10504	68	68
11215	64	64
11844	62	62
11920	63	63
13274	60	60
13274	61	61
13897	58	58
13903	57	57
13925	56	56
13937	55	55
13941	59	59
15203	53	53
15241	54	54
15832	52	52
17100	48	48
17104	46	46
17104	47	47
17106	45	45
17126	49	49
17126	50	50
17126	51	51
17569	42	42
17733	44	44
18176	43	43
18597	40	40
18597	41	41
18952	37	37
18996	39	39
19395	38	38
19760	35	35
19788	36	36
20492	32	32
20492	33	33
20498	30	30
20536	34	34
20833	29	29
20871	28	28
20891	31	31
21180	27	27
21752	23	23
21830	26	26
22025	21	21
22087	22	22
22087	24	24
22087	25	25
22278	20	20
22316	19	19
22549	15	15
22557	14	14
22573	17	17
22573	18	18
22706	10	10
22796	11	11
22796	12	12
22796	13	13
22796	16	16
23022	4	4
23042	2	2
23042	3	3
23042	9	9
23155	1	1
23155	5	5
23155	6	6
23155	7	7
23155	8	8

# do_execsql_test 5.2.14.1
query III
SELECT max(c) OVER win,
             min(c) OVER win,
             count(a) OVER win
FROM t3
    WINDOW win AS (  ORDER BY c NULLS LAST, b NULLS LAST, a NULLS LAST
        ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING   EXCLUDE CURRENT ROW  )
      ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
----
963	929	6
979	102	82
979	102	82
979	102	82
979	102	82
979	102	82
979	102	83
979	113	80
979	113	81
979	113	82
979	133	79
979	148	78
979	158	76
979	158	77
979	160	76
979	208	75
979	223	74
979	224	73
979	234	72
979	238	71
979	239	70
979	247	69
979	250	68
979	252	67
979	256	66
979	257	65
979	295	64
979	309	63
979	330	63
979	335	61
979	336	60
979	346	59
979	354	58
979	355	56
979	355	58
979	393	55
979	393	56
979	398	54
979	399	52
979	399	53
979	412	52
979	421	51
979	430	50
979	443	49
979	480	47
979	480	48
979	572	46
979	574	46
979	607	44
979	618	42
979	618	43
979	627	41
979	629	40
979	629	40
979	633	39
979	634	38
979	652	37
979	660	36
979	667	34
979	667	35
979	670	34
979	671	33
979	683	32
979	705	31
979	711	30
979	716	29
979	726	28
979	730	27
979	759	26
979	762	25
979	768	23
979	768	24
979	777	22
979	786	21
979	790	20
979	792	19
979	794	18
979	805	17
979	822	16
979	839	15
979	839	15
979	840	13
979	844	12
979	845	11
979	870	8
979	870	9
979	870	10
979	899	8
979	911	7

# do_execsql_test 5.2.14.2
query III
SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
             rank() OVER win,
             dense_rank() OVER win
FROM t3
    WINDOW win AS (  ORDER BY c NULLS LAST, b NULLS LAST, a NULLS LAST
        ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING   EXCLUDE CURRENT ROW  )
      ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
----
2851	89	89
3778	88	88
4681	87	87
5556	83	83
5574	82	82
5586	81	81
5640	84	84
5640	85	85
5640	86	86
7324	80	80
8123	77	77
8129	73	73
8129	74	74
8163	78	78
8163	79	79
8940	71	71
8968	75	75
8968	76	76
9727	66	66
9745	69	69
9745	70	70
9745	72	72
10504	65	65
10504	67	67
10504	68	68
11215	64	64
11844	62	62
11920	63	63
13274	60	60
13274	61	61
13897	58	58
13903	57	57
13925	56	56
13937	55	55
13941	59	59
15203	53	53
15241	54	54
15832	52	52
17100	48	48
17104	46	46
17104	47	47
17106	45	45
17126	49	49
17126	50	50
17126	51	51
17569	42	42
17733	44	44
18176	43	43
18597	40	40
18597	41	41
18952	37	37
18996	39	39
19395	38	38
19760	35	35
19788	36	36
20492	32	32
20492	33	33
20498	30	30
20536	34	34
20833	29	29
20871	28	28
20891	31	31
21180	27	27
21752	23	23
21830	26	26
22025	21	21
22087	22	22
22087	24	24
22087	25	25
22278	20	20
22316	19	19
22549	15	15
22557	14	14
22573	17	17
22573	18	18
22706	10	10
22796	11	11
22796	12	12
22796	13	13
22796	16	16
23022	4	4
23042	2	2
23042	3	3
23042	9	9
23155	1	1
23155	5	5
23155	6	6
23155	7	7
23155	8	8

# do_execsql_test 5.3.7.1
query III
SELECT max(c) OVER win,
             min(c) OVER win,
             count(a) OVER win
FROM t3
    WINDOW win AS (  ORDER BY c NULLS FIRST, b NULLS FIRST, a NULLS FIRST
        ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING   EXCLUDE GROUP  )
      ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
----
963	929	6
979	102	82
979	102	82
979	102	82
979	102	82
979	102	82
979	102	83
979	113	80
979	113	81
979	113	82
979	133	79
979	148	78
979	158	76
979	158	77
979	160	76
979	208	75
979	223	74
979	224	73
979	234	72
979	238	71
979	239	70
979	247	69
979	250	68
979	252	67
979	256	66
979	257	65
979	295	64
979	309	64
979	330	62
979	335	61
979	336	60
979	346	59
979	354	59
979	355	57
979	355	57
979	393	55
979	393	56
979	398	54
979	399	53
979	399	53
979	412	52
979	421	51
979	430	50
979	443	49
979	480	47
979	480	48
979	572	47
979	574	45
979	607	44
979	618	42
979	618	43
979	627	41
979	629	40
979	629	41
979	633	39
979	634	38
979	652	37
979	660	36
979	667	35
979	667	35
979	670	34
979	671	33
979	683	32
979	705	31
979	711	30
979	716	29
979	726	28
979	730	27
979	759	26
979	762	25
979	768	23
979	768	24
979	777	22
979	786	21
979	790	20
979	792	19
979	794	18
979	805	17
979	822	17
979	839	14
979	839	15
979	840	13
979	844	12
979	845	11
979	870	9
979	870	10
979	870	10
979	899	8
979	911	7

# do_execsql_test 5.3.7.2
query III
SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
             rank() OVER win,
             dense_rank() OVER win
FROM t3
    WINDOW win AS (  ORDER BY c NULLS FIRST, b NULLS FIRST, a NULLS FIRST
        ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING   EXCLUDE GROUP  )
      ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
----
2851	89	89
3778	88	88
4681	87	87
5556	83	83
5574	82	82
5586	81	81
5640	84	84
5640	85	85
5640	86	86
7324	80	80
8123	77	77
8129	73	73
8129	74	74
8163	78	78
8163	79	79
8940	71	71
8968	75	75
8968	76	76
9727	66	66
9745	69	69
9745	70	70
9745	72	72
10504	65	65
10504	67	67
10504	68	68
11215	64	64
11844	62	62
11920	63	63
13274	60	60
13274	61	61
13897	58	58
13903	57	57
13925	56	56
13937	55	55
13941	59	59
15203	53	53
15241	54	54
15832	52	52
17100	48	48
17104	46	46
17104	47	47
17106	45	45
17126	49	49
17126	50	50
17126	51	51
17569	42	42
17733	44	44
18176	43	43
18597	40	40
18597	41	41
18952	37	37
18996	39	39
19395	38	38
19760	35	35
19788	36	36
20492	32	32
20492	33	33
20498	30	30
20536	34	34
20833	29	29
20871	28	28
20891	31	31
21180	27	27
21752	23	23
21830	26	26
22025	21	21
22087	22	22
22087	24	24
22087	25	25
22278	20	20
22316	19	19
22549	15	15
22557	14	14
22573	17	17
22573	18	18
22706	10	10
22796	11	11
22796	12	12
22796	13	13
22796	16	16
23022	4	4
23042	2	2
23042	3	3
23042	9	9
23155	1	1
23155	5	5
23155	6	6
23155	7	7
23155	8	8

# do_execsql_test 5.3.14.1
query III
SELECT max(c) OVER win,
             min(c) OVER win,
             count(a) OVER win
FROM t3
    WINDOW win AS (  ORDER BY c NULLS LAST, b NULLS LAST, a NULLS LAST
        ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING   EXCLUDE GROUP  )
      ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
----
963	929	6
979	102	82
979	102	82
979	102	82
979	102	82
979	102	82
979	102	83
979	113	80
979	113	81
979	113	82
979	133	79
979	148	78
979	158	76
979	158	77
979	160	76
979	208	75
979	223	74
979	224	73
979	234	72
979	238	71
979	239	70
979	247	69
979	250	68
979	252	67
979	256	66
979	257	65
979	295	64
979	309	63
979	330	63
979	335	61
979	336	60
979	346	59
979	354	58
979	355	56
979	355	58
979	393	55
979	393	56
979	398	54
979	399	52
979	399	53
979	412	52
979	421	51
979	430	50
979	443	49
979	480	47
979	480	48
979	572	46
979	574	46
979	607	44
979	618	42
979	618	43
979	627	41
979	629	40
979	629	40
979	633	39
979	634	38
979	652	37
979	660	36
979	667	34
979	667	35
979	670	34
979	671	33
979	683	32
979	705	31
979	711	30
979	716	29
979	726	28
979	730	27
979	759	26
979	762	25
979	768	23
979	768	24
979	777	22
979	786	21
979	790	20
979	792	19
979	794	18
979	805	17
979	822	16
979	839	15
979	839	15
979	840	13
979	844	12
979	845	11
979	870	8
979	870	9
979	870	10
979	899	8
979	911	7

# do_execsql_test 5.3.14.2
query III
SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
             rank() OVER win,
             dense_rank() OVER win
FROM t3
    WINDOW win AS (  ORDER BY c NULLS LAST, b NULLS LAST, a NULLS LAST
        ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING   EXCLUDE GROUP  )
      ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
----
2851	89	89
3778	88	88
4681	87	87
5556	83	83
5574	82	82
5586	81	81
5640	84	84
5640	85	85
5640	86	86
7324	80	80
8123	77	77
8129	73	73
8129	74	74
8163	78	78
8163	79	79
8940	71	71
8968	75	75
8968	76	76
9727	66	66
9745	69	69
9745	70	70
9745	72	72
10504	65	65
10504	67	67
10504	68	68
11215	64	64
11844	62	62
11920	63	63
13274	60	60
13274	61	61
13897	58	58
13903	57	57
13925	56	56
13937	55	55
13941	59	59
15203	53	53
15241	54	54
15832	52	52
17100	48	48
17104	46	46
17104	47	47
17106	45	45
17126	49	49
17126	50	50
17126	51	51
17569	42	42
17733	44	44
18176	43	43
18597	40	40
18597	41	41
18952	37	37
18996	39	39
19395	38	38
19760	35	35
19788	36	36
20492	32	32
20492	33	33
20498	30	30
20536	34	34
20833	29	29
20871	28	28
20891	31	31
21180	27	27
21752	23	23
21830	26	26
22025	21	21
22087	22	22
22087	24	24
22087	25	25
22278	20	20
22316	19	19
22549	15	15
22557	14	14
22573	17	17
22573	18	18
22706	10	10
22796	11	11
22796	12	12
22796	13	13
22796	16	16
23022	4	4
23042	2	2
23042	3	3
23042	9	9
23155	1	1
23155	5	5
23155	6	6
23155	7	7
23155	8	8

# do_execsql_test 5.4.7.1
query III
SELECT max(c) OVER win,
             min(c) OVER win,
             count(a) OVER win
FROM t3
    WINDOW win AS (  ORDER BY c NULLS FIRST, b NULLS FIRST, a NULLS FIRST
        ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING   EXCLUDE TIES  )
      ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
----
979	102	83
979	102	83
979	102	83
979	102	83
979	102	83
979	102	83
979	102	83
979	113	81
979	113	82
979	133	80
979	148	79
979	158	77
979	158	78
979	160	77
979	208	76
979	223	75
979	224	74
979	234	73
979	238	72
979	239	71
979	247	70
979	250	69
979	252	68
979	256	67
979	257	66
979	295	65
979	309	64
979	330	63
979	335	62
979	336	61
979	346	60
979	354	59
979	355	58
979	355	58
979	393	56
979	393	57
979	398	55
979	399	54
979	399	54
979	412	53
979	421	52
979	430	51
979	443	50
979	480	48
979	480	49
979	572	47
979	574	46
979	607	45
979	618	43
979	618	44
979	627	42
979	629	41
979	629	41
979	633	40
979	634	39
979	652	38
979	660	37
979	667	36
979	667	36
979	670	35
979	671	34
979	683	33
979	705	32
979	711	31
979	716	30
979	726	29
979	730	28
979	759	27
979	762	26
979	768	24
979	768	25
979	777	23
979	786	22
979	790	21
979	792	20
979	794	19
979	805	18
979	822	17
979	839	15
979	839	16
979	840	14
979	844	13
979	845	12
979	870	10
979	870	11
979	870	11
979	899	9
979	911	8
979	929	7

# do_execsql_test 5.4.7.2
query III
SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
             rank() OVER win,
             dense_rank() OVER win
FROM t3
    WINDOW win AS (  ORDER BY c NULLS FIRST, b NULLS FIRST, a NULLS FIRST
        ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING   EXCLUDE TIES  )
      ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
----
3830	89	89
4741	88	88
5640	84	84
5640	85	85
5640	86	86
5640	87	87
6485	81	81
6485	82	82
6485	83	83
7324	80	80
8163	78	78
8163	79	79
8968	73	73
8968	74	74
8968	75	75
8968	76	76
8968	77	77
9745	69	69
9745	70	70
9745	71	71
9745	72	72
10504	65	65
10504	66	66
10504	67	67
10504	68	68
11215	64	64
11920	63	63
12603	62	62
13274	60	60
13274	61	61
13941	59	59
14608	55	55
14608	56	56
14608	57	57
14608	58	58
15241	54	54
15870	53	53
16499	52	52
17126	49	49
17126	50	50
17126	51	51
17733	44	44
17733	45	45
17733	46	46
17733	47	47
17733	48	48
18176	42	42
18176	43	43
18597	40	40
18597	41	41
18996	39	39
19395	37	37
19395	38	38
19788	36	36
20181	35	35
20536	34	34
20891	30	30
20891	31	31
20891	32	32
20891	33	33
21226	28	28
21226	29	29
21535	27	27
21830	26	26
22087	22	22
22087	23	23
22087	24	24
22087	25	25
22334	21	21
22573	17	17
22573	18	18
22573	19	19
22573	20	20
22796	11	11
22796	12	12
22796	13	13
22796	14	14
22796	15	15
22796	16	16
22929	10	10
23042	9	9
23155	1	1
23155	2	2
23155	3	3
23155	4	4
23155	5	5
23155	6	6
23155	7	7
23155	8	8

# do_execsql_test 5.4.14.1
query III
SELECT max(c) OVER win,
             min(c) OVER win,
             count(a) OVER win
FROM t3
  WINDOW win AS (  ORDER BY c NULLS LAST, b NULLS LAST, a NULLS LAST
        ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING   EXCLUDE TIES  )
      ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
----
979	102	83
979	102	83
979	102	83
979	102	83
979	102	83
979	102	83
979	102	83
979	113	81
979	113	82
979	133	80
979	148	79
979	158	77
979	158	78
979	160	77
979	208	76
979	223	75
979	224	74
979	234	73
979	238	72
979	239	71
979	247	70
979	250	69
979	252	68
979	256	67
979	257	66
979	295	65
979	309	64
979	330	63
979	335	62
979	336	61
979	346	60
979	354	59
979	355	57
979	355	58
979	393	56
979	393	57
979	398	55
979	399	53
979	399	54
979	412	53
979	421	52
979	430	51
979	443	50
979	480	48
979	480	49
979	572	47
979	574	46
979	607	45
979	618	43
979	618	44
979	627	42
979	629	40
979	629	41
979	633	40
979	634	39
979	652	38
979	660	37
979	667	35
979	667	36
979	670	35
979	671	34
979	683	33
979	705	32
979	711	31
979	716	30
979	726	29
979	730	28
979	759	27
979	762	26
979	768	24
979	768	25
979	777	23
979	786	22
979	790	21
979	792	20
979	794	19
979	805	18
979	822	17
979	839	15
979	839	16
979	840	14
979	844	13
979	845	12
979	870	9
979	870	10
979	870	11
979	899	9
979	911	8
979	929	7

# do_execsql_test 5.4.14.2
query III
SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
             rank() OVER win,
             dense_rank() OVER win
FROM t3
    WINDOW win AS (  ORDER BY c NULLS LAST, b NULLS LAST, a NULLS LAST
        ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING   EXCLUDE TIES  )
      ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
----
3830	89	89
4741	88	88
5640	84	84
5640	85	85
5640	86	86
5640	87	87
6485	81	81
6485	82	82
6485	83	83
7324	80	80
8163	78	78
8163	79	79
8968	73	73
8968	74	74
8968	75	75
8968	76	76
8968	77	77
9745	69	69
9745	70	70
9745	71	71
9745	72	72
10504	65	65
10504	66	66
10504	67	67
10504	68	68
11215	64	64
11920	63	63
12603	62	62
13274	60	60
13274	61	61
13941	59	59
14608	55	55
14608	56	56
14608	57	57
14608	58	58
15241	54	54
15870	53	53
16499	52	52
17126	49	49
17126	50	50
17126	51	51
17733	44	44
17733	45	45
17733	46	46
17733	47	47
17733	48	48
18176	42	42
18176	43	43
18597	40	40
18597	41	41
18996	39	39
19395	37	37
19395	38	38
19788	36	36
20181	35	35
20536	34	34
20891	30	30
20891	31	31
20891	32	32
20891	33	33
21226	28	28
21226	29	29
21535	27	27
21830	26	26
22087	22	22
22087	23	23
22087	24	24
22087	25	25
22334	21	21
22573	17	17
22573	18	18
22573	19	19
22573	20	20
22796	11	11
22796	12	12
22796	13	13
22796	14	14
22796	15	15
22796	16	16
22929	10	10
23042	9	9
23155	1	1
23155	2	2
23155	3	3
23155	4	4
23155	5	5
23155	6	6
23155	7	7
23155	8	8