/ Check-in [e0255063]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Fix a problem with EXCLUDE clauses on window frames with no ORDER BY.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | window-functions
Files: files | file ages | folders
SHA3-256:e0255063799a2a8531f0eaa8790334509591a5ababc5b915035b46d2faa80363
User & Date: dan 2019-03-19 19:19:53
Wiki:window-functions
Context
2019-03-19
19:39
Add further tests to window8.test. check-in: ec7e224f user: dan tags: window-functions
19:19
Fix a problem with EXCLUDE clauses on window frames with no ORDER BY. check-in: e0255063 user: dan tags: window-functions
17:45
Add tests to ensure that the window functions implementation is not generating code for unnecessary sorts. check-in: e195948a user: dan tags: window-functions
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/window.c.

1569
1570
1571
1572
1573
1574
1575
1576
1577

1578

1579
1580
1581

1582
1583
1584
1585
1586
1587



1588
1589
1590
1591
1592
1593
1594
  VdbeCoverageNeverNull(v);

  if( pMWin->eExclude==TK_CURRENT ){
    sqlite3VdbeAddOp3(v, OP_Eq, regCRowid, lblNext, regRowid);
  }else if( pMWin->eExclude!=TK_NO ){
    int addr;
    int addrEq = 0;
    KeyInfo *pKeyInfo;


    pKeyInfo = sqlite3KeyInfoFromExprList(pParse, pMWin->pOrderBy, 0, 0);

    if( pMWin->eExclude==TK_TIES ){
      addrEq = sqlite3VdbeAddOp3(v, OP_Eq, regCRowid, lblNext, regRowid);
    }

    windowReadPeerValues(p, csr, regPeer);
    sqlite3VdbeAddOp3(v, OP_Compare, regPeer, regCPeer, nPeer);
    sqlite3VdbeAppendP4(v, (void*)pKeyInfo, P4_KEYINFO);
    addr = sqlite3VdbeCurrentAddr(v)+1;
    sqlite3VdbeAddOp3(v, OP_Jump, addr, lblNext, addr);
    VdbeCoverageEqNe(v);



    if( addrEq ) sqlite3VdbeJumpHere(v, addrEq);
  }

  windowAggStep(pParse, pMWin, csr, 0, p->regArg);

  sqlite3VdbeResolveLabel(v, lblNext);
  sqlite3VdbeAddOp2(v, OP_Next, csr, addrNext);







|

>
|
>

|

>
|
|
|
|
|
|
>
>
>







1569
1570
1571
1572
1573
1574
1575
1576
1577
1578
1579
1580
1581
1582
1583
1584
1585
1586
1587
1588
1589
1590
1591
1592
1593
1594
1595
1596
1597
1598
1599
1600
  VdbeCoverageNeverNull(v);

  if( pMWin->eExclude==TK_CURRENT ){
    sqlite3VdbeAddOp3(v, OP_Eq, regCRowid, lblNext, regRowid);
  }else if( pMWin->eExclude!=TK_NO ){
    int addr;
    int addrEq = 0;
    KeyInfo *pKeyInfo = 0;

    if( pMWin->pOrderBy ){
      pKeyInfo = sqlite3KeyInfoFromExprList(pParse, pMWin->pOrderBy, 0, 0);
    }
    if( pMWin->eExclude==TK_TIES ){
      addrEq = sqlite3VdbeAddOp3(v, OP_Eq, regCRowid, 0, regRowid);
    }
    if( pKeyInfo ){
      windowReadPeerValues(p, csr, regPeer);
      sqlite3VdbeAddOp3(v, OP_Compare, regPeer, regCPeer, nPeer);
      sqlite3VdbeAppendP4(v, (void*)pKeyInfo, P4_KEYINFO);
      addr = sqlite3VdbeCurrentAddr(v)+1;
      sqlite3VdbeAddOp3(v, OP_Jump, addr, lblNext, addr);
      VdbeCoverageEqNe(v);
    }else{
      sqlite3VdbeAddOp2(v, OP_Goto, 0, lblNext);
    }
    if( addrEq ) sqlite3VdbeJumpHere(v, addrEq);
  }

  windowAggStep(pParse, pMWin, csr, 0, p->regArg);

  sqlite3VdbeResolveLabel(v, lblNext);
  sqlite3VdbeAddOp2(v, OP_Next, csr, addrNext);

Changes to test/window8.tcl.

217
218
219
220
221
222
223











































224
225
226
}

execsql_test 4.4.2 {
  SELECT sum(b) OVER (
    ORDER BY a DESC NULLS LAST ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) FROM t1 ORDER BY 1 NULLS FIRST;
}











































finish_test









>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>



217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
}

execsql_test 4.4.2 {
  SELECT sum(b) OVER (
    ORDER BY a DESC NULLS LAST ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) FROM t1 ORDER BY 1 NULLS FIRST;
}

==========

execsql_test 5.0 {
  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);
}

foreach {tn ex} {
  1  { EXCLUDE NO OTHERS }
  2  { EXCLUDE CURRENT ROW }
  3  { EXCLUDE GROUP }
  4  { EXCLUDE TIES }
} {
  foreach {tn2 frame} {
    1 { RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING }
    2 { ORDER BY a NULLS FIRST 
        RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING }
    3 { PARTITION BY coalesce(a, '') 
        RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING }
    4 { ORDER BY a NULLS FIRST GROUPS 6 PRECEDING }
    5 { ORDER BY c NULLS FIRST RANGE BETWEEN 6 PRECEDING AND 7 FOLLOWING }
  } {
    execsql_test 5.$tn.$tn2.1 "
      SELECT max(c) OVER win,
             min(c) OVER win
      FROM t3
      WINDOW win AS ( $frame $ex )
      ORDER BY 1 NULLS FIRST, 2 NULLS FIRST
    "

    execsql_test 5.$tn.$tn2.2 "
      SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
             rank() OVER win
      FROM t3
      WINDOW win AS ( $frame $ex )
      ORDER BY 1 NULLS FIRST, 2 NULLS FIRST
    "
  }
}

finish_test


Changes to test/window8.test.

3544
3545
3546
3547
3548
3549
3550
3551





















































































































































































































































































































































































































































































































































































































































































































































































































3552
} {3   6   9   9   12}

do_execsql_test 4.4.2 {
  SELECT sum(b) OVER (
    ORDER BY a DESC  ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) FROM t1 ORDER BY 1 ;
} {5   6   8   9   10}






















































































































































































































































































































































































































































































































































































































































































































































































































finish_test








>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

3544
3545
3546
3547
3548
3549
3550
3551
3552
3553
3554
3555
3556
3557
3558
3559
3560
3561
3562
3563
3564
3565
3566
3567
3568
3569
3570
3571
3572
3573
3574
3575
3576
3577
3578
3579
3580
3581
3582
3583
3584
3585
3586
3587
3588
3589
3590
3591
3592
3593
3594
3595
3596
3597
3598
3599
3600
3601
3602
3603
3604
3605
3606
3607
3608
3609
3610
3611
3612
3613
3614
3615
3616
3617
3618
3619
3620
3621
3622
3623
3624
3625
3626
3627
3628
3629
3630
3631
3632
3633
3634
3635
3636
3637
3638
3639
3640
3641
3642
3643
3644
3645
3646
3647
3648
3649
3650
3651
3652
3653
3654
3655
3656
3657
3658
3659
3660
3661
3662
3663
3664
3665
3666
3667
3668
3669
3670
3671
3672
3673
3674
3675
3676
3677
3678
3679
3680
3681
3682
3683
3684
3685
3686
3687
3688
3689
3690
3691
3692
3693
3694
3695
3696
3697
3698
3699
3700
3701
3702
3703
3704
3705
3706
3707
3708
3709
3710
3711
3712
3713
3714
3715
3716
3717
3718
3719
3720
3721
3722
3723
3724
3725
3726
3727
3728
3729
3730
3731
3732
3733
3734
3735
3736
3737
3738
3739
3740
3741
3742
3743
3744
3745
3746
3747
3748
3749
3750
3751
3752
3753
3754
3755
3756
3757
3758
3759
3760
3761
3762
3763
3764
3765
3766
3767
3768
3769
3770
3771
3772
3773
3774
3775
3776
3777
3778
3779
3780
3781
3782
3783
3784
3785
3786
3787
3788
3789
3790
3791
3792
3793
3794
3795
3796
3797
3798
3799
3800
3801
3802
3803
3804
3805
3806
3807
3808
3809
3810
3811
3812
3813
3814
3815
3816
3817
3818
3819
3820
3821
3822
3823
3824
3825
3826
3827
3828
3829
3830
3831
3832
3833
3834
3835
3836
3837
3838
3839
3840
3841
3842
3843
3844
3845
3846
3847
3848
3849
3850
3851
3852
3853
3854
3855
3856
3857
3858
3859
3860
3861
3862
3863
3864
3865
3866
3867
3868
3869
3870
3871
3872
3873
3874
3875
3876
3877
3878
3879
3880
3881
3882
3883
3884
3885
3886
3887
3888
3889
3890
3891
3892
3893
3894
3895
3896
3897
3898
3899
3900
3901
3902
3903
3904
3905
3906
3907
3908
3909
3910
3911
3912
3913
3914
3915
3916
3917
3918
3919
3920
3921
3922
3923
3924
3925
3926
3927
3928
3929
3930
3931
3932
3933
3934
3935
3936
3937
3938
3939
3940
3941
3942
3943
3944
3945
3946
3947
3948
3949
3950
3951
3952
3953
3954
3955
3956
3957
3958
3959
3960
3961
3962
3963
3964
3965
3966
3967
3968
3969
3970
3971
3972
3973
3974
3975
3976
3977
3978
3979
3980
3981
3982
3983
3984
3985
3986
3987
3988
3989
3990
3991
3992
3993
3994
3995
3996
3997
3998
3999
4000
4001
4002
4003
4004
4005
4006
4007
4008
4009
4010
4011
4012
4013
4014
4015
4016
4017
4018
4019
4020
4021
4022
4023
4024
4025
4026
4027
4028
4029
4030
4031
4032
4033
4034
4035
4036
4037
4038
4039
4040
4041
4042
4043
4044
4045
4046
4047
4048
4049
4050
4051
4052
4053
4054
4055
4056
4057
4058
4059
4060
4061
4062
4063
4064
4065
4066
4067
4068
4069
4070
4071
4072
4073
4074
4075
4076
4077
4078
4079
4080
4081
4082
4083
4084
4085
4086
4087
4088
4089
4090
4091
4092
4093
4094
4095
4096
4097
4098
4099
4100
4101
4102
4103
4104
4105
4106
4107
4108
4109
4110
4111
4112
4113
4114
4115
4116
4117
4118
4119
4120
4121
4122
4123
4124
4125
4126
4127
4128
4129
4130
4131
4132
4133
4134
4135
4136
4137
4138
4139
4140
4141
4142
4143
4144
4145
4146
4147
4148
4149
4150
4151
4152
4153
4154
4155
4156
4157
4158
4159
4160
4161
4162
4163
4164
4165
4166
4167
4168
4169
4170
4171
4172
4173
4174
4175
4176
4177
4178
4179
4180
4181
4182
4183
4184
4185
4186
4187
4188
4189
4190
4191
4192
4193
4194
4195
4196
4197
4198
4199
4200
4201
4202
4203
4204
4205
4206
4207
4208
4209
4210
4211
4212
4213
4214
4215
4216
4217
4218
4219
4220
4221
4222
4223
4224
4225
4226
4227
4228
4229
4230
4231
4232
4233
4234
4235
4236
4237
4238
4239
4240
4241
4242
4243
4244
4245
4246
4247
4248
4249
4250
4251
4252
4253
4254
4255
4256
4257
4258
4259
4260
4261
4262
4263
4264
4265
4266
4267
4268
4269
4270
4271
4272
4273
4274
4275
4276
4277
4278
4279
4280
4281
4282
4283
4284
4285
4286
4287
4288
4289
4290
4291
4292
4293
4294
4295
4296
4297
4298
4299
4300
4301
4302
4303
4304
4305
4306
4307
4308
4309
4310
4311
4312
4313
4314
4315
4316
4317
4318
4319
4320
4321
4322
4323
4324
4325
4326
4327
4328
4329
4330
4331
4332
4333
4334
4335
4336
4337
4338
4339
4340
4341
} {3   6   9   9   12}

do_execsql_test 4.4.2 {
  SELECT sum(b) OVER (
    ORDER BY a DESC  ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) FROM t1 ORDER BY 1 ;
} {5   6   8   9   10}

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

do_execsql_test 5.0 {
  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.1.1 {
  SELECT max(c) OVER win,
             min(c) OVER win
      FROM t3
      WINDOW win AS (  RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING   EXCLUDE NO OTHERS  )
      ORDER BY 1 , 2
} {979 102   979 102   979 102   979 102   979 102   979 102   979 102
  979 102   979 102   979 102   979 102   979 102   979 102   979 102
  979 102   979 102   979 102   979 102   979 102   979 102   979 102
  979 102   979 102   979 102   979 102   979 102   979 102   979 102
  979 102   979 102   979 102   979 102   979 102   979 102   979 102
  979 102   979 102   979 102   979 102   979 102   979 102   979 102
  979 102   979 102   979 102   979 102   979 102   979 102   979 102
  979 102   979 102   979 102   979 102   979 102   979 102   979 102
  979 102   979 102   979 102   979 102   979 102   979 102   979 102
  979 102   979 102   979 102   979 102   979 102   979 102   979 102
  979 102   979 102   979 102   979 102   979 102   979 102   979 102
  979 102   979 102   979 102   979 102   979 102   979 102   979 102
  979 102   979 102   979 102   979 102   979 102}

do_execsql_test 5.1.1.2 {
  SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
             rank() OVER win
      FROM t3
      WINDOW win AS (  RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING   EXCLUDE NO OTHERS  )
      ORDER BY 1 , 2
} {23155 1   23155 1   23155 1   23155 1   23155 1   23155 1   23155 1
  23155 1   23155 1   23155 1   23155 1   23155 1   23155 1   23155 1
  23155 1   23155 1   23155 1   23155 1   23155 1   23155 1   23155 1
  23155 1   23155 1   23155 1   23155 1   23155 1   23155 1   23155 1
  23155 1   23155 1   23155 1   23155 1   23155 1   23155 1   23155 1
  23155 1   23155 1   23155 1   23155 1   23155 1   23155 1   23155 1
  23155 1   23155 1   23155 1   23155 1   23155 1   23155 1   23155 1
  23155 1   23155 1   23155 1   23155 1   23155 1   23155 1   23155 1
  23155 1   23155 1   23155 1   23155 1   23155 1   23155 1   23155 1
  23155 1   23155 1   23155 1   23155 1   23155 1   23155 1   23155 1
  23155 1   23155 1   23155 1   23155 1   23155 1   23155 1   23155 1
  23155 1   23155 1   23155 1   23155 1   23155 1   23155 1   23155 1
  23155 1   23155 1   23155 1   23155 1   23155 1}

do_execsql_test 5.1.2.1 {
  SELECT max(c) OVER win,
             min(c) OVER win
      FROM t3
      WINDOW win AS (  ORDER BY a  
        RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING   EXCLUDE NO OTHERS  )
      ORDER BY 1 , 2
} {899 113   899 113   899 113   899 113   899 113   899 113   899 113
  899 113   899 113   899 113   899 113   899 113   899 113   899 113
  899 113   899 113   979 102   979 102   979 102   979 102   979 102
  979 102   979 102   979 102   979 102   979 102   979 102   979 102
  979 102   979 102   979 102   979 102   979 102   979 102   979 102
  979 102   979 102   979 102   979 102   979 102   979 102   979 102
  979 102   979 102   979 102   979 102   979 102   979 102   979 102
  979 102   979 102   979 102   979 102   979 102   979 102   979 102
  979 102   979 102   979 102   979 102   979 102   979 102   979 102
  979 102   979 102   979 102   979 102   979 102   979 102   979 102
  979 102   979 102   979 113   979 113   979 113   979 113   979 113
  979 113   979 113   979 113   979 113   979 113   979 113   979 113
  979 113   979 113   979 113   979 113   979 113}

do_execsql_test 5.1.2.2 {
  SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
             rank() OVER win
      FROM t3
      WINDOW win AS (  ORDER BY a  
        RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING   EXCLUDE NO OTHERS  )
      ORDER BY 1 , 2
} {2947 81   2947 81   2947 81   2947 81   2947 81   2947 81   2947 81
  2947 81   2947 81   5287 74   5287 74   5287 74   5287 74   5287 74
  5287 74   5287 74   8400 65   8400 65   8400 65   8400 65   8400 65
  8400 65   8400 65   8400 65   8400 65   9664 57   9664 57   9664 57
  9664 57   9664 57   9664 57   9664 57   9664 57   10626 46   10626 46
  10626 46   10626 46   10626 46   10626 46   10626 46   10626 46   10626 46
  10626 46   10626 46   12145 41   12145 41   12145 41   12145 41   12145 41
  13949 34   13949 34   13949 34   13949 34   13949 34   13949 34   13949 34
  15315 28   15315 28   15315 28   15315 28   15315 28   15315 28   18796 15
  18796 15   18796 15   18796 15   18796 15   18796 15   18796 15   18796 15
  18796 15   18796 15   18796 15   18796 15   18796 15   21105 7   21105 7
  21105 7   21105 7   21105 7   21105 7   21105 7   21105 7   23155 1
  23155 1   23155 1   23155 1   23155 1   23155 1}

do_execsql_test 5.1.3.1 {
  SELECT max(c) OVER win,
             min(c) OVER win
      FROM t3
      WINDOW win AS (  PARTITION BY coalesce(a, '') 
        RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING   EXCLUDE NO OTHERS  )
      ORDER BY 1 , 2
} {777 113   777 113   777 113   777 113   777 113   805 250   805 250
  805 250   805 250   805 250   805 250   805 250   822 158   822 158
  822 158   822 158   822 158   822 158   840 247   840 247   840 247
  840 247   840 247   840 247   840 247   840 247   840 247   840 247
  840 247   840 247   840 247   870 158   870 158   870 158   870 158
  870 158   870 158   899 113   899 113   899 113   899 113   899 113
  899 113   899 113   899 113   899 113   934 223   934 223   934 223
  934 223   934 223   934 223   934 223   934 223   938 102   938 102
  938 102   938 102   938 102   938 102   938 102   938 102   938 102
  938 102   938 102   938 148   938 148   938 148   938 148   938 148
  938 148   938 148   938 148   959 224   959 224   959 224   959 224
  959 224   959 224   959 224   979 133   979 133   979 133   979 133
  979 133   979 133   979 133   979 133   979 133}

do_execsql_test 5.1.3.2 {
  SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
             rank() OVER win
      FROM t3
      WINDOW win AS (  PARTITION BY coalesce(a, '') 
        RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING   EXCLUDE NO OTHERS  )
      ORDER BY 1 , 2
} {962 1   962 1   962 1   962 1   962 1   962 1   962 1   962 1   962 1
  962 1   962 1   1264 1   1264 1   1264 1   1264 1   1264 1   1264 1
  1264 1   1264 1   1366 1   1366 1   1366 1   1366 1   1366 1   1366 1
  1519 1   1519 1   1519 1   1519 1   1519 1   1804 1   1804 1   1804 1
  1804 1   1804 1   1804 1   1804 1   2050 1   2050 1   2050 1   2050 1
  2050 1   2050 1   2309 1   2309 1   2309 1   2309 1   2309 1   2309 1
  2309 1   2309 1   2340 1   2340 1   2340 1   2340 1   2340 1   2340 1
  2340 1   2947 1   2947 1   2947 1   2947 1   2947 1   2947 1   2947 1
  2947 1   2947 1   3113 1   3113 1   3113 1   3113 1   3113 1   3113 1
  3113 1   3113 1   3113 1   3481 1   3481 1   3481 1   3481 1   3481 1
  3481 1   3481 1   3481 1   3481 1   3481 1   3481 1   3481 1   3481 1}

do_execsql_test 5.1.4.1 {
  SELECT max(c) OVER win,
             min(c) OVER win
      FROM t3
      WINDOW win AS (  ORDER BY a  GROUPS 6 PRECEDING   EXCLUDE NO OTHERS  )
      ORDER BY 1 , 2
} {870 158   870 158   870 158   870 158   870 158   870 158   934 158
  934 158   934 158   934 158   934 158   934 158   934 158   934 158
  934 158   934 158   934 158   934 158   934 158   934 158   934 158
  934 158   934 158   934 158   934 158   934 158   934 158   934 158
  934 158   934 158   934 158   934 158   934 158   959 102   959 102
  959 102   959 102   959 102   959 102   959 102   959 102   959 102
  959 102   959 102   959 102   959 102   959 102   959 102   959 102
  959 102   959 102   959 102   959 113   959 113   959 113   959 113
  959 113   959 158   959 158   959 158   959 158   959 158   959 158
  959 158   979 102   979 102   979 102   979 102   979 102   979 102
  979 102   979 102   979 102   979 102   979 102   979 102   979 102
  979 102   979 102   979 102   979 102   979 102   979 102   979 102
  979 102   979 102   979 102   979 102   979 102}

do_execsql_test 5.1.4.2 {
  SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
             rank() OVER win
      FROM t3
      WINDOW win AS (  ORDER BY a  GROUPS 6 PRECEDING   EXCLUDE NO OTHERS  )
      ORDER BY 1 , 2
} {2050 1   2050 1   2050 1   2050 1   2050 1   2050 1   4359 7   4359 7
  4359 7   4359 7   4359 7   4359 7   4359 7   4359 7   7840 15   7840 15
  7840 15   7840 15   7840 15   7840 15   7840 15   7840 15   7840 15
  7840 15   7840 15   7840 15   7840 15   9206 28   9206 28   9206 28
  9206 28   9206 28   9206 28   11010 34   11010 34   11010 34   11010 34
  11010 34   11010 34   11010 34   12368 74   12368 74   12368 74   12368 74
  12368 74   12368 74   12368 74   12529 41   12529 41   12529 41   12529 41
  12529 41   12705 57   12705 57   12705 57   12705 57   12705 57   12705 57
  12705 57   12705 57   13491 46   13491 46   13491 46   13491 46   13491 46
  13491 46   13491 46   13491 46   13491 46   13491 46   13491 46   13509 65
  13509 65   13509 65   13509 65   13509 65   13509 65   13509 65   13509 65
  13509 65   13949 81   13949 81   13949 81   13949 81   13949 81   13949 81
  13949 81   13949 81   13949 81}

do_execsql_test 5.1.5.1 {
  SELECT max(c) OVER win,
             min(c) OVER win
      FROM t3
      WINDOW win AS (  ORDER BY c  RANGE BETWEEN 6 PRECEDING AND 7 FOLLOWING   EXCLUDE NO OTHERS  )
      ORDER BY 1 , 2
} {102 102   113 113   113 113   133 133   148 148   160 158   160 158
  160 158   208 208   224 223   224 223   239 234   239 234   239 234
  252 247   257 247   257 247   257 250   257 252   295 295   309 309
  336 330   336 330   336 330   346 346   355 354   355 354   355 354
  399 393   399 393   399 393   399 393   399 393   412 412   421 421
  430 430   443 443   480 480   480 480   574 572   574 572   607 607
  618 618   618 618   634 627   634 627   634 627   634 627   634 629
  652 652   667 660   671 667   671 667   671 667   671 667   683 683
  711 705   716 705   716 711   730 726   730 726   762 759   768 759
  768 762   768 762   777 777   792 786   794 786   794 786   794 790
  805 805   822 822   845 839   845 839   845 839   845 839   845 839
  870 870   870 870   870 870   899 899   911 911   934 929   938 929
  938 934   938 934   963 959   963 959   979 979}

do_execsql_test 5.1.5.2 {
  SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
             rank() OVER win
      FROM t3
      WINDOW win AS (  ORDER BY c  RANGE BETWEEN 6 PRECEDING AND 7 FOLLOWING   EXCLUDE NO OTHERS  )
      ORDER BY 1 , 2
} {{} 1   {} 5   {} 6   {} 6   {} 8   {} 9   {} 25   {} 34   {} 36   {} 38
  {} 38   {} 40   {} 41   {} 43   {} 43   {} 50   {} 60   {} 61   {} 64
  {} 64   {} 67   {} 68   {} 69   {} 70   {} 72   {} 78   {} 78   {} 78
  {} 85   {} 85   133 4   223 10   223 11   226 2   226 2   239 12   239 13
  239 14   247 15   257 18   257 19   295 20   309 21   335 22   335 23
  335 24   421 35   443 37   504 16   504 17   607 42   683 56   710 26
  710 27   710 27   711 59   759 62   759 63   777 66   805 71   899 81
  911 82   929 83   929 84   979 89   1334 51   1416 57   1416 58   1584 29
  1584 29   1584 31   1584 32   1584 32   1891 49   1922 87   1922 88
  2005 52   2005 52   2005 54   2005 55   2518 45   2518 46   2518 46
  2518 48   2523 73   2523 73   2523 75   2523 76   2523 77}

do_execsql_test 5.2.1.1 {
  SELECT max(c) OVER win,
             min(c) OVER win
      FROM t3
      WINDOW win AS (  RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING   EXCLUDE CURRENT ROW  )
      ORDER BY 1 , 2
} {963 102   979 102   979 102   979 102   979 102   979 102   979 102
  979 102   979 102   979 102   979 102   979 102   979 102   979 102
  979 102   979 102   979 102   979 102   979 102   979 102   979 102
  979 102   979 102   979 102   979 102   979 102   979 102   979 102
  979 102   979 102   979 102   979 102   979 102   979 102   979 102
  979 102   979 102   979 102   979 102   979 102   979 102   979 102
  979 102   979 102   979 102   979 102   979 102   979 102   979 102
  979 102   979 102   979 102   979 102   979 102   979 102   979 102
  979 102   979 102   979 102   979 102   979 102   979 102   979 102
  979 102   979 102   979 102   979 102   979 102   979 102   979 102
  979 102   979 102   979 102   979 102   979 102   979 102   979 102
  979 102   979 102   979 102   979 102   979 102   979 102   979 102
  979 102   979 102   979 102   979 102   979 113}

do_execsql_test 5.2.1.2 {
  SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
             rank() OVER win
      FROM t3
      WINDOW win AS (  RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING   EXCLUDE CURRENT ROW  )
      ORDER BY 1 , 2
} {22176 1   22192 1   22196 1   22226 1   22244 1   22256 1   22310 1
  22316 1   22316 1   22350 1   22378 1   22396 1   22444 1   22450 1
  22472 1   22484 1   22488 1   22488 1   22522 1   22526 1   22526 1
  22528 1   22548 1   22712 1   22734 1   22756 1   22756 1   22762 1
  22762 1   22800 1   22800 1   22820 1   22846 1   22860 1   22898 1
  22908 1   22916 1   22932 1   23022 1   23042 1   23042 1   23155 1
  23155 1   23155 1   23155 1   23155 1   23155 1   23155 1   23155 1
  23155 1   23155 1   23155 1   23155 1   23155 1   23155 1   23155 1
  23155 1   23155 1   23155 1   23155 1   23155 1   23155 1   23155 1
  23155 1   23155 1   23155 1   23155 1   23155 1   23155 1   23155 1
  23155 1   23155 1   23155 1   23155 1   23155 1   23155 1   23155 1
  23155 1   23155 1   23155 1   23155 1   23155 1   23155 1   23155 1
  23155 1   23155 1   23155 1   23155 1   23155 1}

do_execsql_test 5.2.2.1 {
  SELECT max(c) OVER win,
             min(c) OVER win
      FROM t3
      WINDOW win AS (  ORDER BY a  
        RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING   EXCLUDE CURRENT ROW  )
      ORDER BY 1 , 2
} {839 113   899 113   899 113   899 113   899 113   899 113   899 113
  899 113   899 113   899 113   899 113   899 113   899 113   899 113
  899 113   899 234   963 113   979 102   979 102   979 102   979 102
  979 102   979 102   979 102   979 102   979 102   979 102   979 102
  979 102   979 102   979 102   979 102   979 102   979 102   979 102
  979 102   979 102   979 102   979 102   979 102   979 102   979 102
  979 102   979 102   979 102   979 102   979 102   979 102   979 102
  979 102   979 102   979 102   979 102   979 102   979 102   979 102
  979 102   979 102   979 102   979 102   979 102   979 102   979 102
  979 102   979 102   979 102   979 102   979 102   979 102   979 102
  979 102   979 102   979 113   979 113   979 113   979 113   979 113
  979 113   979 113   979 113   979 113   979 113   979 113   979 113
  979 113   979 113   979 113   979 113   979 113}

do_execsql_test 5.2.2.2 {
  SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
             rank() OVER win
      FROM t3
      WINDOW win AS (  ORDER BY a  
        RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING   EXCLUDE CURRENT ROW  )
      ORDER BY 1 , 2
} {2048 81   2108 81   2108 81   2690 81   2834 81   2947 81   2947 81
  2947 81   2947 81   4482 74   4616 74   4844 74   4866 74   5287 74
  5287 74   5287 74   7421 65   7437 65   7717 65   8045 65   8267 65
  8400 65   8400 65   8400 65   8400 65   8735 57   9329 57   9664 57
  9664 57   9664 57   9664 57   9664 57   9664 57   9959 46   10331 46
  10626 46   10626 46   10626 46   10626 46   10626 46   10626 46   10626 46
  10626 46   10626 46   11368 41   11516 41   12032 41   12145 41   12145 41
  12990 34   13104 34   13949 34   13949 34   13949 34   13949 34   13949 34
  14556 28   14708 28   15315 28   15315 28   15315 28   15315 28   18085 15
  18091 15   18163 15   18397 15   18403 15   18403 15   18549 15   18796 15
  18796 15   18796 15   18796 15   18796 15   18796 15   20194 7   20478 7
  20796 7   20866 7   20882 7   21105 7   21105 7   21105 7   22488 1
  22526 1   22756 1   22800 1   23155 1   23155 1}

do_execsql_test 5.2.3.1 {
  SELECT max(c) OVER win,
             min(c) OVER win
      FROM t3
      WINDOW win AS (  PARTITION BY coalesce(a, '') 
        RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING   EXCLUDE CURRENT ROW  )
      ORDER BY 1 , 2
} {667 158   671 250   759 158   768 113   777 113   777 113   777 113
  777 252   792 247   805 250   805 250   805 250   805 250   805 250
  805 398   822 158   822 158   822 158   822 158   822 346   839 113
  840 247   840 247   840 247   840 247   840 247   840 247   840 247
  840 247   840 247   840 247   840 247   840 393   845 224   870 102
  870 158   870 158   870 158   870 158   870 355   899 113   899 113
  899 113   899 113   899 113   899 113   899 113   899 234   911 223
  929 148   934 223   934 223   934 223   934 223   934 223   934 223
  934 239   938 102   938 102   938 102   938 102   938 102   938 102
  938 102   938 102   938 102   938 148   938 148   938 148   938 148
  938 148   938 148   938 160   938 208   959 224   959 224   959 224
  959 224   959 224   959 238   963 133   979 133   979 133   979 133
  979 133   979 133   979 133   979 133   979 330}

do_execsql_test 5.2.3.2 {
  SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
             rank() OVER win
      FROM t3
      WINDOW win AS (  PARTITION BY coalesce(a, '') 
        RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING   EXCLUDE CURRENT ROW  )
      ORDER BY 1 , 2
} {295 1   335 1   607 1   667 1   742 1   759 1   845 1   890 1   929 1
  959 1   962 1   962 1   962 1   962 1   962 1   962 1   962 1   962 1
  962 1   1264 1   1264 1   1264 1   1264 1   1264 1   1264 1   1366 1
  1366 1   1366 1   1366 1   1383 1   1398 1   1406 1   1421 1   1519 1
  1519 1   1535 1   1651 1   1669 1   1682 1   1695 1   1804 1   1804 1
  1804 1   1804 1   1804 1   1897 1   1919 1   2000 1   2048 1   2050 1
  2050 1   2070 1   2086 1   2108 1   2108 1   2134 1   2150 1   2309 1
  2309 1   2309 1   2340 1   2340 1   2340 1   2430 1   2690 1   2758 1
  2770 1   2776 1   2834 1   2848 1   2947 1   2947 1   2947 1   2947 1
  2980 1   3082 1   3088 1   3088 1   3113 1   3113 1   3113 1   3113 1
  3234 1   3481 1   3481 1   3481 1   3481 1   3481 1   3481 1}

do_execsql_test 5.2.4.1 {
  SELECT max(c) OVER win,
             min(c) OVER win
      FROM t3
      WINDOW win AS (  ORDER BY a  GROUPS 6 PRECEDING   EXCLUDE CURRENT ROW  )
      ORDER BY 1 , 2
} {667 158   870 158   870 158   870 158   870 158   870 355   911 158
  934 158   934 158   934 158   934 158   934 158   934 158   934 158
  934 158   934 158   934 158   934 158   934 158   934 158   934 158
  934 158   934 158   934 158   934 158   934 158   934 158   934 158
  934 158   934 158   934 158   934 158   934 158   934 158   959 102
  959 102   959 102   959 102   959 102   959 102   959 102   959 102
  959 102   959 102   959 102   959 102   959 102   959 102   959 102
  959 102   959 102   959 102   959 113   959 113   959 113   959 113
  959 113   959 158   959 158   959 158   959 158   959 158   959 158
  959 158   963 102   979 102   979 102   979 102   979 102   979 102
  979 102   979 102   979 102   979 102   979 102   979 102   979 102
  979 102   979 102   979 102   979 102   979 102   979 102   979 102
  979 102   979 102   979 102   979 102   979 102}

do_execsql_test 5.2.4.2 {
  SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
             rank() OVER win
      FROM t3
      WINDOW win AS (  ORDER BY a  GROUPS 6 PRECEDING   EXCLUDE CURRENT ROW  )
      ORDER BY 1 , 2
} {1383 1   1421 1   1651 1   1695 1   2050 1   2050 1   3448 7   3732 7
  4050 7   4120 7   4136 7   4359 7   4359 7   4359 7   7129 15   7135 15
  7207 15   7441 15   7447 15   7447 15   7593 15   7840 15   7840 15
  7840 15   7840 15   7840 15   7840 15   8447 28   8599 28   9206 28
  9206 28   9206 28   9206 28   10051 34   10165 34   11010 34   11010 34
  11010 34   11010 34   11010 34   11563 74   11697 74   11752 41   11776 57
  11900 41   11925 74   11947 74   12368 74   12368 74   12368 74   12370 57
  12416 41   12529 41   12529 41   12530 65   12546 65   12705 57   12705 57
  12705 57   12705 57   12705 57   12705 57   12824 46   12826 65   13050 81
  13110 81   13110 81   13154 65   13196 46   13376 65   13491 46   13491 46
  13491 46   13491 46   13491 46   13491 46   13491 46   13491 46   13491 46
  13509 65   13509 65   13509 65   13509 65   13692 81   13836 81   13949 81
  13949 81   13949 81   13949 81}

do_execsql_test 5.2.5.1 {
  SELECT max(c) OVER win,
             min(c) OVER win
      FROM t3
      WINDOW win AS (  ORDER BY c  RANGE BETWEEN 6 PRECEDING AND 7 FOLLOWING   EXCLUDE CURRENT ROW  )
      ORDER BY 1 , 2
} {{} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}
  {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}
  {} {}   {} {}   113 113   113 113   158 158   160 158   160 158   223 223
  224 224   238 234   239 234   239 238   252 250   256 252   257 247
  257 247   257 250   335 330   336 330   336 335   355 354   355 354
  355 355   399 393   399 393   399 393   399 393   399 393   480 480
  480 480   572 572   574 574   618 618   618 618   633 629   634 627
  634 627   634 627   634 629   667 667   670 667   671 667   671 667
  671 667   711 711   711 711   716 705   726 726   730 730   762 762
  768 759   768 762   768 762   792 790   792 790   794 786   794 786
  844 839   845 839   845 839   845 839   845 839   870 870   870 870
  870 870   934 934   938 929   938 934   938 934   959 959   963 963}

do_execsql_test 5.2.5.2 {
  SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
             rank() OVER win
      FROM t3
      WINDOW win AS (  ORDER BY c  RANGE BETWEEN 6 PRECEDING AND 7 FOLLOWING   EXCLUDE CURRENT ROW  )
      ORDER BY 1 , 2
} {{} 1   {} 4   {} 5   {} 6   {} 6   {} 8   {} 9   {} 10   {} 14   {} 15
  {} 19   {} 20   {} 21   {} 23   {} 25   {} 34   {} 35   {} 36   {} 37
  {} 38   {} 38   {} 40   {} 41   {} 42   {} 43   {} 43   {} 50   {} 56
  {} 60   {} 61   {} 62   {} 64   {} 64   {} 66   {} 67   {} 68   {} 69
  {} 70   {} 71   {} 72   {} 78   {} 78   {} 78   {} 81   {} 82   {} 83
  {} 85   {} 85   {} 89   113 2   113 2   223 11   239 12   239 13   257 18
  335 22   335 24   355 27   355 27   504 16   504 17   705 58   710 26
  711 57   711 59   759 63   929 84   959 88   963 87   1185 32   1185 32
  1191 29   1191 29   1334 51   1334 55   1338 52   1338 52   1584 31
  1678 77   1684 73   1684 73   1885 48   1889 46   1889 46   1891 45
  1891 49   2005 54   2523 75   2523 76}

do_execsql_test 5.3.1.1 {
  SELECT max(c) OVER win,
             min(c) OVER win
      FROM t3
      WINDOW win AS (  RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING   EXCLUDE GROUP  )
      ORDER BY 1 , 2
} {{} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}
  {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}
  {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}
  {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}
  {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}
  {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}
  {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}
  {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}
  {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}
  {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}}

do_execsql_test 5.3.1.2 {
  SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
             rank() OVER win
      FROM t3
      WINDOW win AS (  RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING   EXCLUDE GROUP  )
      ORDER BY 1 , 2
} {{} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1
  {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1
  {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1
  {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1
  {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1
  {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1
  {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1
  {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1
  {} 1}

do_execsql_test 5.3.2.1 {
  SELECT max(c) OVER win,
             min(c) OVER win
      FROM t3
      WINDOW win AS (  ORDER BY a  
        RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING   EXCLUDE GROUP  )
      ORDER BY 1 , 2
} {{} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}
  899 113   899 113   899 113   899 113   899 113   899 113   899 113
  899 113   899 113   899 113   899 113   899 113   899 113   899 113
  899 113   899 113   979 102   979 102   979 102   979 102   979 102
  979 102   979 102   979 102   979 102   979 102   979 102   979 102
  979 102   979 102   979 102   979 102   979 102   979 102   979 102
  979 102   979 102   979 102   979 102   979 102   979 102   979 102
  979 102   979 102   979 102   979 102   979 102   979 102   979 102
  979 102   979 102   979 102   979 102   979 102   979 102   979 102
  979 102   979 102   979 102   979 102   979 102   979 113   979 113
  979 113   979 113   979 113   979 113   979 113   979 113   979 113
  979 113   979 113   979 113   979 113   979 113   979 113   979 113
  979 113   979 113   979 113}

do_execsql_test 5.3.2.2 {
  SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
             rank() OVER win
      FROM t3
      WINDOW win AS (  ORDER BY a  
        RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING   EXCLUDE GROUP  )
      ORDER BY 1 , 2
} {{} 81   {} 81   {} 81   {} 81   {} 81   {} 81   {} 81   {} 81   {} 81
  2947 74   2947 74   2947 74   2947 74   2947 74   2947 74   2947 74
  5287 65   5287 65   5287 65   5287 65   5287 65   5287 65   5287 65
  5287 65   5287 65   8400 57   8400 57   8400 57   8400 57   8400 57
  8400 57   8400 57   8400 57   9664 46   9664 46   9664 46   9664 46
  9664 46   9664 46   9664 46   9664 46   9664 46   9664 46   9664 46
  10626 41   10626 41   10626 41   10626 41   10626 41   12145 34   12145 34
  12145 34   12145 34   12145 34   12145 34   12145 34   13949 28   13949 28
  13949 28   13949 28   13949 28   13949 28   15315 15   15315 15   15315 15
  15315 15   15315 15   15315 15   15315 15   15315 15   15315 15   15315 15
  15315 15   15315 15   15315 15   18796 7   18796 7   18796 7   18796 7
  18796 7   18796 7   18796 7   18796 7   21105 1   21105 1   21105 1
  21105 1   21105 1   21105 1}

do_execsql_test 5.3.3.1 {
  SELECT max(c) OVER win,
             min(c) OVER win
      FROM t3
      WINDOW win AS (  PARTITION BY coalesce(a, '') 
        RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING   EXCLUDE GROUP  )
      ORDER BY 1 , 2
} {{} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}
  {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}
  {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}
  {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}
  {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}
  {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}
  {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}
  {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}
  {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}
  {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}}

do_execsql_test 5.3.3.2 {
  SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
             rank() OVER win
      FROM t3
      WINDOW win AS (  PARTITION BY coalesce(a, '') 
        RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING   EXCLUDE GROUP  )
      ORDER BY 1 , 2
} {{} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1
  {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1
  {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1
  {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1
  {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1
  {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1
  {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1
  {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1
  {} 1}

do_execsql_test 5.3.4.1 {
  SELECT max(c) OVER win,
             min(c) OVER win
      FROM t3
      WINDOW win AS (  ORDER BY a  GROUPS 6 PRECEDING   EXCLUDE GROUP  )
      ORDER BY 1 , 2
} {{} {}   {} {}   {} {}   {} {}   {} {}   {} {}   870 158   870 158   870 158
  870 158   870 158   870 158   870 158   870 158   934 158   934 158
  934 158   934 158   934 158   934 158   934 158   934 158   934 158
  934 158   934 158   934 158   934 158   934 158   934 158   934 158
  934 158   934 158   934 158   934 158   934 158   934 158   934 158
  934 158   934 158   934 158   959 102   959 102   959 102   959 102
  959 102   959 102   959 102   959 102   959 102   959 102   959 102
  959 102   959 102   959 102   959 102   959 102   959 102   959 113
  959 113   959 113   959 113   959 113   959 113   959 113   959 113
  959 113   959 113   959 113   959 158   959 158   959 158   959 158
  959 158   979 102   979 102   979 102   979 102   979 102   979 102
  979 102   979 102   979 102   979 102   979 102   979 102   979 102
  979 102   979 102   979 102}

do_execsql_test 5.3.4.2 {
  SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
             rank() OVER win
      FROM t3
      WINDOW win AS (  ORDER BY a  GROUPS 6 PRECEDING   EXCLUDE GROUP  )
      ORDER BY 1 , 2
} {{} 1   {} 1   {} 1   {} 1   {} 1   {} 1   2050 7   2050 7   2050 7   2050 7
  2050 7   2050 7   2050 7   2050 7   4359 15   4359 15   4359 15   4359 15
  4359 15   4359 15   4359 15   4359 15   4359 15   4359 15   4359 15
  4359 15   4359 15   7840 28   7840 28   7840 28   7840 28   7840 28
  7840 28   9206 34   9206 34   9206 34   9206 34   9206 34   9206 34
  9206 34   10028 74   10028 74   10028 74   10028 74   10028 74   10028 74
  10028 74   10396 65   10396 65   10396 65   10396 65   10396 65   10396 65
  10396 65   10396 65   10396 65   11002 81   11002 81   11002 81   11002 81
  11002 81   11002 81   11002 81   11002 81   11002 81   11010 41   11010 41
  11010 41   11010 41   11010 41   11441 57   11441 57   11441 57   11441 57
  11441 57   11441 57   11441 57   11441 57   12529 46   12529 46   12529 46
  12529 46   12529 46   12529 46   12529 46   12529 46   12529 46   12529 46
  12529 46}

do_execsql_test 5.3.5.1 {
  SELECT max(c) OVER win,
             min(c) OVER win
      FROM t3
      WINDOW win AS (  ORDER BY c  RANGE BETWEEN 6 PRECEDING AND 7 FOLLOWING   EXCLUDE GROUP  )
      ORDER BY 1 , 2
} {{} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}
  {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}
  {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}
  {} {}   {} {}   158 158   160 160   160 160   223 223   224 224   238 234
  239 234   239 238   252 250   256 252   257 247   257 247   257 250
  335 330   336 330   336 335   354 354   354 354   355 355   398 393
  398 393   399 393   399 398   399 398   572 572   574 574   633 629
  634 627   634 627   634 627   634 629   667 667   670 667   671 667
  671 670   671 670   711 711   711 711   716 705   726 726   730 730
  762 762   762 762   762 762   768 759   792 790   792 790   794 786
  794 786   844 839   845 839   845 839   845 840   845 840   934 934
  934 934   934 934   938 929   959 959   963 963}

do_execsql_test 5.3.5.2 {
  SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
             rank() OVER win
      FROM t3
      WINDOW win AS (  ORDER BY c  RANGE BETWEEN 6 PRECEDING AND 7 FOLLOWING   EXCLUDE GROUP  )
      ORDER BY 1 , 2
} {{} 1   {} 2   {} 2   {} 4   {} 5   {} 6   {} 6   {} 8   {} 9   {} 10
  {} 14   {} 15   {} 19   {} 20   {} 21   {} 23   {} 25   {} 27   {} 27
  {} 34   {} 35   {} 36   {} 37   {} 38   {} 38   {} 40   {} 41   {} 42
  {} 43   {} 43   {} 50   {} 56   {} 60   {} 61   {} 62   {} 64   {} 64
  {} 66   {} 67   {} 68   {} 69   {} 70   {} 71   {} 72   {} 78   {} 78
  {} 78   {} 81   {} 82   {} 83   {} 85   {} 85   {} 89   223 11   239 12
  239 13   257 18   335 22   335 24   504 16   504 17   671 52   671 52
  705 58   710 26   711 57   711 59   759 63   786 32   786 32   798 29
  798 29   845 73   845 73   929 84   959 88   963 87   1260 46   1260 46
  1334 51   1334 55   1584 31   1678 77   1885 48   1891 45   1891 49
  2005 54   2523 75   2523 76}

do_execsql_test 5.4.1.1 {
  SELECT max(c) OVER win,
             min(c) OVER win
      FROM t3
      WINDOW win AS (  RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING   EXCLUDE TIES  )
      ORDER BY 1 , 2
} {102 102   113 113   113 113   133 133   148 148   158 158   158 158
  160 160   208 208   223 223   224 224   234 234   238 238   239 239
  247 247   250 250   252 252   256 256   257 257   295 295   309 309
  330 330   335 335   336 336   346 346   354 354   355 355   355 355
  393 393   393 393   398 398   399 399   399 399   412 412   421 421
  430 430   443 443   480 480   480 480   572 572   574 574   607 607
  618 618   618 618   627 627   629 629   629 629   633 633   634 634
  652 652   660 660   667 667   667 667   670 670   671 671   683 683
  705 705   711 711   716 716   726 726   730 730   759 759   762 762
  768 768   768 768   777 777   786 786   790 790   792 792   794 794
  805 805   822 822   839 839   839 839   840 840   844 844   845 845
  870 870   870 870   870 870   899 899   911 911   929 929   934 934
  938 938   938 938   959 959   963 963   979 979}

do_execsql_test 5.4.1.2 {
  SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
             rank() OVER win
      FROM t3
      WINDOW win AS (  RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING   EXCLUDE TIES  )
      ORDER BY 1 , 2
} {{} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1
  {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1
  {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1
  {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1
  {} 1   {} 1   {} 1   {} 1   113 1   113 1   133 1   223 1   239 1   247 1
  257 1   295 1   309 1   335 1   355 1   355 1   393 1   393 1   399 1
  399 1   421 1   443 1   607 1   627 1   629 1   629 1   633 1   667 1
  667 1   671 1   683 1   705 1   711 1   759 1   777 1   805 1   839 1
  839 1   845 1   899 1   911 1   929 1   959 1   963 1   979 1}

do_execsql_test 5.4.2.1 {
  SELECT max(c) OVER win,
             min(c) OVER win
      FROM t3
      WINDOW win AS (  ORDER BY a  
        RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING   EXCLUDE TIES  )
      ORDER BY 1 , 2
} {113 113   234 234   257 257   336 336   354 354   768 768   839 839
  839 839   899 113   899 113   899 113   899 113   899 113   899 113
  899 113   899 113   899 113   899 113   899 113   899 113   899 113
  899 113   899 899   963 113   979 102   979 102   979 102   979 102
  979 102   979 102   979 102   979 102   979 102   979 102   979 102
  979 102   979 102   979 102   979 102   979 102   979 102   979 102
  979 102   979 102   979 102   979 102   979 102   979 102   979 102
  979 102   979 102   979 102   979 102   979 102   979 102   979 102
  979 102   979 102   979 102   979 102   979 102   979 102   979 102
  979 102   979 102   979 102   979 102   979 102   979 102   979 102
  979 113   979 113   979 113   979 113   979 113   979 113   979 113
  979 113   979 113   979 113   979 113   979 113   979 113   979 113
  979 113   979 113   979 113   979 113   979 113}

do_execsql_test 5.4.2.2 {
  SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
             rank() OVER win
      FROM t3
      WINDOW win AS (  ORDER BY a  
        RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING   EXCLUDE TIES  )
      ORDER BY 1 , 2
} {{} 81   {} 81   {} 81   {} 81   113 81   257 81   839 81   839 81   899 81
  2947 74   2947 74   2947 74   3368 74   3390 74   3618 74   3752 74
  5287 65   5287 65   5287 65   5287 65   5420 65   5642 65   5970 65
  6250 65   6266 65   8400 57   8400 57   8400 57   8400 57   8400 57
  8400 57   8735 57   9329 57   9664 46   9664 46   9664 46   9664 46
  9664 46   9664 46   9664 46   9664 46   9664 46   9959 46   10331 46
  10626 41   10626 41   10739 41   11255 41   11403 41   12145 34   12145 34
  12145 34   12145 34   12145 34   12990 34   13104 34   13949 28   13949 28
  13949 28   13949 28   14556 28   14708 28   15315 15   15315 15   15315 15
  15315 15   15315 15   15315 15   15562 15   15708 15   15708 15   15714 15
  15948 15   16020 15   16026 15   18796 7   18796 7   18796 7   19019 7
  19035 7   19105 7   19423 7   19707 7   21105 1   21105 1   21460 1
  21504 1   21734 1   21772 1}

do_execsql_test 5.4.3.1 {
  SELECT max(c) OVER win,
             min(c) OVER win
      FROM t3
      WINDOW win AS (  PARTITION BY coalesce(a, '') 
        RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING   EXCLUDE TIES  )
      ORDER BY 1 , 2
} {102 102   113 113   113 113   133 133   148 148   158 158   158 158
  160 160   208 208   223 223   224 224   234 234   238 238   239 239
  247 247   250 250   252 252   256 256   257 257   295 295   309 309
  330 330   335 335   336 336   346 346   354 354   355 355   355 355
  393 393   393 393   398 398   399 399   399 399   412 412   421 421
  430 430   443 443   480 480   480 480   572 572   574 574   607 607
  618 618   618 618   627 627   629 629   629 629   633 633   634 634
  652 652   660 660   667 667   667 667   670 670   671 671   683 683
  705 705   711 711   716 716   726 726   730 730   759 759   762 762
  768 768   768 768   777 777   786 786   790 790   792 792   794 794
  805 805   822 822   839 839   839 839   840 840   844 844   845 845
  870 870   870 870   870 870   899 899   911 911   929 929   934 934
  938 938   938 938   959 959   963 963   979 979}

do_execsql_test 5.4.3.2 {
  SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
             rank() OVER win
      FROM t3
      WINDOW win AS (  PARTITION BY coalesce(a, '') 
        RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING   EXCLUDE TIES  )
      ORDER BY 1 , 2
} {{} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1
  {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1
  {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1
  {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1
  {} 1   {} 1   {} 1   {} 1   113 1   113 1   133 1   223 1   239 1   247 1
  257 1   295 1   309 1   335 1   355 1   355 1   393 1   393 1   399 1
  399 1   421 1   443 1   607 1   627 1   629 1   629 1   633 1   667 1
  667 1   671 1   683 1   705 1   711 1   759 1   777 1   805 1   839 1
  839 1   845 1   899 1   911 1   929 1   959 1   963 1   979 1}

do_execsql_test 5.4.4.1 {
  SELECT max(c) OVER win,
             min(c) OVER win
      FROM t3
      WINDOW win AS (  ORDER BY a  GROUPS 6 PRECEDING   EXCLUDE TIES  )
      ORDER BY 1 , 2
} {158 158   355 355   399 399   629 629   667 667   870 158   870 158
  870 158   870 158   870 158   870 158   870 870   911 158   934 158
  934 158   934 158   934 158   934 158   934 158   934 158   934 158
  934 158   934 158   934 158   934 158   934 158   934 158   934 158
  934 158   934 158   934 158   934 158   934 158   934 158   934 158
  934 158   934 158   934 158   934 158   959 102   959 102   959 102
  959 102   959 102   959 102   959 102   959 102   959 102   959 102
  959 102   959 102   959 102   959 102   959 102   959 102   959 113
  959 113   959 113   959 113   959 113   959 113   959 113   959 113
  959 113   959 113   959 113   959 158   959 158   959 158   959 158
  959 158   963 102   979 102   979 102   979 102   979 102   979 102
  979 102   979 102   979 102   979 102   979 102   979 102   979 102
  979 102   979 102   979 102   979 102   979 102}

do_execsql_test 5.4.4.2 {
  SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
             rank() OVER win
      FROM t3
      WINDOW win AS (  ORDER BY a  GROUPS 6 PRECEDING   EXCLUDE TIES  )
      ORDER BY 1 , 2
} {{} 1   {} 1   355 1   399 1   629 1   667 1   2050 7   2050 7   2050 7
  2273 7   2289 7   2359 7   2677 7   2961 7   4359 15   4359 15   4359 15
  4359 15   4359 15   4359 15   4606 15   4752 15   4752 15   4758 15
  4992 15   5064 15   5070 15   7840 28   7840 28   7840 28   7840 28
  8447 28   8599 28   9206 34   9206 34   9206 34   9206 34   9206 34
  10028 74   10028 74   10028 74   10051 34   10165 34   10396 65   10396 65
  10396 65   10396 65   10449 74   10471 74   10529 65   10699 74   10751 65
  10833 74   11002 81   11002 81   11002 81   11002 81   11010 41   11010 41
  11079 65   11115 81   11123 41   11259 81   11359 65   11375 65   11441 57
  11441 57   11441 57   11441 57   11441 57   11441 57   11639 41   11776 57
  11787 41   11841 81   11841 81   11901 81   12370 57   12529 46   12529 46
  12529 46   12529 46   12529 46   12529 46   12529 46   12529 46   12529 46
  12824 46   13196 46}

do_execsql_test 5.4.5.1 {
  SELECT max(c) OVER win,
             min(c) OVER win
      FROM t3
      WINDOW win AS (  ORDER BY c  RANGE BETWEEN 6 PRECEDING AND 7 FOLLOWING   EXCLUDE TIES  )
      ORDER BY 1 , 2
} {102 102   113 113   113 113   133 133   148 148   160 158   160 158
  160 158   208 208   224 223   224 223   239 234   239 234   239 234
  252 247   257 247   257 247   257 250   257 252   295 295   309 309
  336 330   336 330   336 330   346 346   355 354   355 354   355 354
  399 393   399 393   399 393   399 393   399 393   412 412   421 421
  430 430   443 443   480 480   480 480   574 572   574 572   607 607
  618 618   618 618   634 627   634 627   634 627   634 627   634 629
  652 652   667 660   671 667   671 667   671 667   671 667   683 683
  711 705   716 705   716 711   730 726   730 726   762 759   768 759
  768 762   768 762   777 777   792 786   794 786   794 786   794 790
  805 805   822 822   845 839   845 839   845 839   845 839   845 839
  870 870   870 870   870 870   899 899   911 911   934 929   938 929
  938 934   938 934   963 959   963 959   979 979}

do_execsql_test 5.4.5.2 {
  SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
             rank() OVER win
      FROM t3
      WINDOW win AS (  ORDER BY c  RANGE BETWEEN 6 PRECEDING AND 7 FOLLOWING   EXCLUDE TIES  )
      ORDER BY 1 , 2
} {{} 1   {} 5   {} 6   {} 6   {} 8   {} 9   {} 25   {} 34   {} 36   {} 38
  {} 38   {} 40   {} 41   {} 43   {} 43   {} 50   {} 60   {} 61   {} 64
  {} 64   {} 67   {} 68   {} 69   {} 70   {} 72   {} 78   {} 78   {} 78
  {} 85   {} 85   113 2   113 2   133 4   223 10   223 11   239 12   239 13
  239 14   247 15   257 18   257 19   295 20   309 21   335 22   335 23
  335 24   355 27   355 27   421 35   443 37   504 16   504 17   607 42
  683 56   710 26   711 59   759 62   759 63   777 66   805 71   899 81
  911 82   929 83   929 84   979 89   1185 32   1185 32   1191 29   1191 29
  1334 51   1338 52   1338 52   1416 57   1416 58   1584 31   1684 73
  1684 73   1889 46   1889 46   1891 49   1922 87   1922 88   2005 54
  2005 55   2518 45   2518 48   2523 75   2523 76   2523 77}

finish_test