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: |
e0255063799a2a8531f0eaa879033450 |
| User & Date: | dan 2019-03-19 19:19:53 |
Context
|
2019-03-19
| ||
| 19:39 | Add further tests to window8.test. (check-in: ec7e224f50 user: dan tags: window-functions) | |
| 19:19 | Fix a problem with EXCLUDE clauses on window frames with no ORDER BY. (check-in: e025506379 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: e195948a68 user: dan tags: window-functions) | |
Changes
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
|