Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Fix a couple of problems with "RANGE BETWEEN <expr> PRECEDING AND <expr> PRECEDING" frames. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA3-256: | 39225cc77579896214dceb93b7f224b4 |
| User & Date: | dan 2019-04-03 16:27:44 |
Context
|
2019-04-03
| ||
| 17:48 | Add a defense-in-depth NEVER() test to the WAL cleanup code. check-in: 8d3af201 user: drh tags: trunk | |
| 16:27 | Fix a couple of problems with "RANGE BETWEEN <expr> PRECEDING AND <expr> PRECEDING" frames. check-in: 39225cc7 user: dan tags: trunk | |
|
2019-04-02
| ||
| 18:12 | Small optimizations to the grammar for window functions save about 120 bytes of space in the parser tables. check-in: bce01d95 user: drh tags: trunk | |
Changes
Changes to src/window.c.
2319 2320 2321 2322 2323 2324 2325 2326 2327 2328 2329 2330 2331 2332 2333 2334 2335 2336 2337 2338 2339 2340 2341 2342 2343 2344 .... 2572 2573 2574 2575 2576 2577 2578 2579 2580 2581 2582 2583 2584 2585 .... 2593 2594 2595 2596 2597 2598 2599 2600 2601 2602 2603 2604 2605 2606 2607 2608 2609 .... 2638 2639 2640 2641 2642 2643 2644 2645 2646 2647 2648 2649 2650 2651 2652 |
** }
** Insert new row into eph table.
** if( first row of partition ){
** Rewind(csrEnd) ; Rewind(csrStart) ; Rewind(csrCurrent)
** regEnd = <expr2>
** regStart = <expr1>
** }else{
** while( (csrEnd.key + regEnd) <= csrCurrent.key ){
** AGGSTEP
** }
** RETURN_ROW
** while( (csrStart.key + regStart) < csrCurrent.key ){
** AGGINVERSE
** }
** }
** }
** flush:
** while( (csrEnd.key + regEnd) <= csrCurrent.key ){
** AGGSTEP
** }
** RETURN_ROW
**
** RANGE BETWEEN <expr1> FOLLOWING AND <expr2> FOLLOWING
**
** ... loop started by sqlite3WhereBegin() ...
** if( new partition ){
................................................................................
sqlite3VdbeAddOp3(v, OP_Copy, regPeer, s.current.reg, pOrderBy->nExpr-1);
sqlite3VdbeAddOp3(v, OP_Copy, regPeer, s.end.reg, pOrderBy->nExpr-1);
}
sqlite3VdbeAddOp2(v, OP_Goto, 0, lblWhereEnd);
sqlite3VdbeJumpHere(v, addrNe);
if( regPeer ){
windowIfNewPeer(pParse, pOrderBy, regNewPeer, regPeer, lblWhereEnd);
}
if( pMWin->eStart==TK_FOLLOWING ){
windowCodeOp(&s, WINDOW_AGGSTEP, 0, 0);
if( pMWin->eEnd!=TK_UNBOUNDED ){
if( pMWin->eFrmType==TK_RANGE ){
................................................................................
}else{
windowCodeOp(&s, WINDOW_RETURN_ROW, regEnd, 0);
windowCodeOp(&s, WINDOW_AGGINVERSE, regStart, 0);
}
}
}else
if( pMWin->eEnd==TK_PRECEDING ){
windowCodeOp(&s, WINDOW_AGGSTEP, regEnd, 0);
windowCodeOp(&s, WINDOW_RETURN_ROW, 0, 0);
windowCodeOp(&s, WINDOW_AGGINVERSE, regStart, 0);
}else{
int addr = 0;
windowCodeOp(&s, WINDOW_AGGSTEP, 0, 0);
if( pMWin->eEnd!=TK_UNBOUNDED ){
if( pMWin->eFrmType==TK_RANGE ){
int lbl = 0;
addr = sqlite3VdbeCurrentAddr(v);
................................................................................
addrInteger = sqlite3VdbeAddOp2(v, OP_Integer, 0, regFlushPart);
sqlite3VdbeJumpHere(v, addrGosubFlush);
}
addrEmpty = sqlite3VdbeAddOp1(v, OP_Rewind, csrWrite);
VdbeCoverage(v);
if( pMWin->eEnd==TK_PRECEDING ){
windowCodeOp(&s, WINDOW_AGGSTEP, regEnd, 0);
windowCodeOp(&s, WINDOW_RETURN_ROW, 0, 0);
}else if( pMWin->eStart==TK_FOLLOWING ){
int addrStart;
int addrBreak1;
int addrBreak2;
int addrBreak3;
windowCodeOp(&s, WINDOW_AGGSTEP, 0, 0);
|
| < > > > > > > > > | > > |
2319 2320 2321 2322 2323 2324 2325 2326 2327 2328 2329 2330 2331 2332 2333 2334 2335 2336 2337 2338 2339 2340 2341 2342 2343 2344 2345 2346 2347 .... 2575 2576 2577 2578 2579 2580 2581 2582 2583 2584 2585 2586 2587 2588 2589 2590 .... 2598 2599 2600 2601 2602 2603 2604 2605 2606 2607 2608 2609 2610 2611 2612 2613 2614 2615 2616 .... 2645 2646 2647 2648 2649 2650 2651 2652 2653 2654 2655 2656 2657 2658 2659 2660 2661 |
** }
** Insert new row into eph table.
** if( first row of partition ){
** Rewind(csrEnd) ; Rewind(csrStart) ; Rewind(csrCurrent)
** regEnd = <expr2>
** regStart = <expr1>
** }else{
** if( (csrEnd.key + regEnd) <= csrCurrent.key ){
** AGGSTEP
** }
** while( (csrStart.key + regStart) < csrCurrent.key ){
** AGGINVERSE
** }
** RETURN_ROW
** }
** }
** flush:
** while( (csrEnd.key + regEnd) <= csrCurrent.key ){
** AGGSTEP
** }
** while( (csrStart.key + regStart) < csrCurrent.key ){
** AGGINVERSE
** }
** RETURN_ROW
**
** RANGE BETWEEN <expr1> FOLLOWING AND <expr2> FOLLOWING
**
** ... loop started by sqlite3WhereBegin() ...
** if( new partition ){
................................................................................
sqlite3VdbeAddOp3(v, OP_Copy, regPeer, s.current.reg, pOrderBy->nExpr-1);
sqlite3VdbeAddOp3(v, OP_Copy, regPeer, s.end.reg, pOrderBy->nExpr-1);
}
sqlite3VdbeAddOp2(v, OP_Goto, 0, lblWhereEnd);
sqlite3VdbeJumpHere(v, addrNe);
/* Beginning of the block executed for the second and subsequent rows. */
if( regPeer ){
windowIfNewPeer(pParse, pOrderBy, regNewPeer, regPeer, lblWhereEnd);
}
if( pMWin->eStart==TK_FOLLOWING ){
windowCodeOp(&s, WINDOW_AGGSTEP, 0, 0);
if( pMWin->eEnd!=TK_UNBOUNDED ){
if( pMWin->eFrmType==TK_RANGE ){
................................................................................
}else{
windowCodeOp(&s, WINDOW_RETURN_ROW, regEnd, 0);
windowCodeOp(&s, WINDOW_AGGINVERSE, regStart, 0);
}
}
}else
if( pMWin->eEnd==TK_PRECEDING ){
int bRPS = (pMWin->eStart==TK_PRECEDING && pMWin->eFrmType==TK_RANGE);
windowCodeOp(&s, WINDOW_AGGSTEP, regEnd, 0);
if( bRPS ) windowCodeOp(&s, WINDOW_AGGINVERSE, regStart, 0);
windowCodeOp(&s, WINDOW_RETURN_ROW, 0, 0);
if( !bRPS ) windowCodeOp(&s, WINDOW_AGGINVERSE, regStart, 0);
}else{
int addr = 0;
windowCodeOp(&s, WINDOW_AGGSTEP, 0, 0);
if( pMWin->eEnd!=TK_UNBOUNDED ){
if( pMWin->eFrmType==TK_RANGE ){
int lbl = 0;
addr = sqlite3VdbeCurrentAddr(v);
................................................................................
addrInteger = sqlite3VdbeAddOp2(v, OP_Integer, 0, regFlushPart);
sqlite3VdbeJumpHere(v, addrGosubFlush);
}
addrEmpty = sqlite3VdbeAddOp1(v, OP_Rewind, csrWrite);
VdbeCoverage(v);
if( pMWin->eEnd==TK_PRECEDING ){
int bRPS = (pMWin->eStart==TK_PRECEDING && pMWin->eFrmType==TK_RANGE);
windowCodeOp(&s, WINDOW_AGGSTEP, regEnd, 0);
if( bRPS ) windowCodeOp(&s, WINDOW_AGGINVERSE, regStart, 0);
windowCodeOp(&s, WINDOW_RETURN_ROW, 0, 0);
}else if( pMWin->eStart==TK_FOLLOWING ){
int addrStart;
int addrBreak1;
int addrBreak2;
int addrBreak3;
windowCodeOp(&s, WINDOW_AGGSTEP, 0, 0);
|
Changes to test/window1.test.
1077 1078 1079 1080 1081 1082 1083 1084 1085 1086 1087 |
SELECT min(x) FROM t1;
} {1}
do_execsql_test 27.2 {
SELECT min(x) OVER win FROM t1
WINDOW win AS (ORDER BY rowid ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
} {1 1 1 2 3 4}
finish_test
|
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > |
1077 1078 1079 1080 1081 1082 1083 1084 1085 1086 1087 1088 1089 1090 1091 1092 1093 1094 1095 1096 1097 1098 1099 1100 1101 1102 1103 1104 1105 1106 1107 1108 1109 1110 1111 1112 1113 1114 1115 1116 1117 1118 1119 1120 1121 1122 1123 1124 1125 1126 1127 1128 1129 1130 1131 1132 1133 1134 1135 1136 1137 1138 1139 1140 1141 1142 1143 1144 1145 1146 1147 1148 1149 1150 1151 1152 1153 1154 1155 1156 1157 1158 1159 1160 1161 1162 1163 1164 1165 1166 1167 1168 1169 1170 1171 1172 |
SELECT min(x) FROM t1;
} {1}
do_execsql_test 27.2 {
SELECT min(x) OVER win FROM t1
WINDOW win AS (ORDER BY rowid ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
} {1 1 1 2 3 4}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 28.1.1 {
CREATE TABLE t1(a INTEGER PRIMARY KEY, b CHAR(1), c CHAR(2), d ANY);
INSERT INTO t1 VALUES (3, 'C', 'cc', 1.0);
INSERT INTO t1 VALUES (13,'M', 'cc', NULL);
}
do_execsql_test 28.1.2 {
SELECT group_concat(b,'') OVER w1 FROM t1
WINDOW w1 AS (ORDER BY a RANGE BETWEEN 3 PRECEDING AND 1 PRECEDING)
} {
{} {}
}
do_execsql_test 28.2.1 {
CREATE TABLE t2(a TEXT, b INTEGER);
INSERT INTO t2 VALUES('A', NULL);
INSERT INTO t2 VALUES('B', NULL);
}
do_execsql_test 28.2.1 {
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(a INTEGER PRIMARY KEY, b CHAR(1), c CHAR(2), d ANY);
INSERT INTO t1 VALUES
(10,'J', 'cc', NULL),
(11,'K', 'cc', 'xyz'),
(13,'M', 'cc', NULL);
}
do_execsql_test 28.2.2 {
SELECT a, b, c, quote(d), group_concat(b,'') OVER w1, '|' FROM t1
WINDOW w1 AS
(ORDER BY d DESC RANGE BETWEEN 7.0 PRECEDING AND 2.5 PRECEDING)
ORDER BY c, d, a;
} {
10 J cc NULL JM |
13 M cc NULL JM |
11 K cc 'xyz' K |
}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 29.1 {
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(a INTEGER PRIMARY KEY, b CHAR(1), c CHAR(2), d ANY);
INSERT INTO t1 VALUES
(1, 'A', 'aa', 2.5),
(2, 'B', 'bb', 3.75),
(3, 'C', 'cc', 1.0),
(4, 'D', 'cc', 8.25),
(5, 'E', 'bb', 6.5),
(6, 'F', 'aa', 6.5),
(7, 'G', 'aa', 6.0),
(8, 'H', 'bb', 9.0),
(9, 'I', 'aa', 3.75),
(10,'J', 'cc', NULL),
(11,'K', 'cc', 'xyz'),
(12,'L', 'cc', 'xyZ'),
(13,'M', 'cc', NULL);
}
do_execsql_test 29.2 {
SELECT a, b, c, quote(d), group_concat(b,'') OVER w1, '|' FROM t1
WINDOW w1 AS
(PARTITION BY c ORDER BY d DESC
RANGE BETWEEN 7.0 PRECEDING AND 2.5 PRECEDING)
ORDER BY c, d, a;
} {
1 A aa 2.5 FG |
9 I aa 3.75 F |
7 G aa 6 {} |
6 F aa 6.5 {} |
2 B bb 3.75 HE |
5 E bb 6.5 H |
8 H bb 9 {} |
10 J cc NULL JM |
13 M cc NULL JM |
3 C cc 1 {} |
4 D cc 8.25 {} |
12 L cc 'xyZ' L |
11 K cc 'xyz' K |
}
finish_test
|
Changes to test/window8.tcl.
264 265 266 267 268 269 270 271 272 273 274 |
dense_rank() OVER win
FROM t3
WINDOW win AS ( $frame $ex )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
"
}
}
finish_test
|
> > > > > > > > > > > > > > > > > > > > > > > > > |
264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 |
dense_rank() OVER win
FROM t3
WINDOW win AS ( $frame $ex )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
"
}
}
==========
execsql_test 6.0 {
DROP TABLE IF EXISTS t2;
CREATE TABLE t2(a TEXT, b INTEGER);
INSERT INTO t2 VALUES('A', NULL);
INSERT INTO t2 VALUES('B', NULL);
INSERT INTO t2 VALUES('C', 1);
}
execsql_test 6.1 {
SELECT string_agg(a, '.') OVER (
ORDER BY b NULLS FIRST RANGE BETWEEN 7 PRECEDING AND 2 PRECEDING
)
FROM t2
}
execsql_test 6.2 {
SELECT string_agg(a, '.') OVER (
ORDER BY b DESC NULLS LAST RANGE BETWEEN 7 PRECEDING AND 2 PRECEDING
)
FROM t2
}
finish_test
|
Changes to test/window8.test.
3837 3838 3839 3840 3841 3842 3843 3844 3845 3846 3847 3848 3849 3850 3851 .... 3861 3862 3863 3864 3865 3866 3867 3868 3869 3870 3871 3872 3873 3874 3875 .... 4162 4163 4164 4165 4166 4167 4168 4169 4170 4171 4172 4173 4174 4175 4176 .... 4186 4187 4188 4189 4190 4191 4192 4193 4194 4195 4196 4197 4198 4199 4200 .... 4477 4478 4479 4480 4481 4482 4483 4484 4485 4486 4487 4488 4489 4490 4491 .... 4501 4502 4503 4504 4505 4506 4507 4508 4509 4510 4511 4512 4513 4514 4515 .... 4800 4801 4802 4803 4804 4805 4806 4807 4808 4809 4810 4811 4812 4813 4814 .... 4824 4825 4826 4827 4828 4829 4830 4831 4832 4833 4834 4835 4836 4837 4838 .... 4843 4844 4845 4846 4847 4848 4849 4850 4851 |
do_execsql_test 5.1.7.1 {
SELECT max(c) OVER win,
min(c) OVER win,
count(a) OVER win
FROM t3
WINDOW win AS ( ORDER BY c , b , a
ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE NO OTHERS )
ORDER BY 1 , 2 , 3
} {979 102 83 979 102 83 979 102 83 979 102 83 979 102 83 979 102 83
979 102 83 979 113 81 979 113 82 979 133 80 979 148 79 979 158 77
979 158 78 979 160 77 979 208 76 979 223 75 979 224 74 979 234 73
979 238 72 979 239 71 979 247 70 979 250 69 979 252 68 979 256 67
979 257 66 979 295 65 979 309 64 979 330 63 979 335 62 979 336 61
979 346 60 979 354 59 979 355 58 979 355 58 979 393 56 979 393 57
................................................................................
do_execsql_test 5.1.7.2 {
SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
rank() OVER win,
dense_rank() OVER win
FROM t3
WINDOW win AS ( ORDER BY c , b , a
ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE NO OTHERS )
ORDER BY 1 , 2 , 3
} {3830 89 89 4741 88 88 5640 84 84 5640 85 85 5640 86 86 5640 87 87
6485 81 81 6485 82 82 6485 83 83 7324 80 80 8163 78 78 8163 79 79
8968 73 73 8968 74 74 8968 75 75 8968 76 76 8968 77 77 9745 69 69
9745 70 70 9745 71 71 9745 72 72 10504 65 65 10504 66 66
10504 67 67 10504 68 68 11215 64 64 11920 63 63 12603 62 62
13274 60 60 13274 61 61 13941 59 59 14608 55 55 14608 56 56
................................................................................
do_execsql_test 5.2.7.1 {
SELECT max(c) OVER win,
min(c) OVER win,
count(a) OVER win
FROM t3
WINDOW win AS ( ORDER BY c , b , a
ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE CURRENT ROW )
ORDER BY 1 , 2 , 3
} {963 929 6 979 102 82 979 102 82 979 102 82 979 102 82 979 102 82
979 102 83 979 113 80 979 113 81 979 113 82 979 133 79 979 148 78
979 158 76 979 158 77 979 160 76 979 208 75 979 223 74 979 224 73
979 234 72 979 238 71 979 239 70 979 247 69 979 250 68 979 252 67
979 256 66 979 257 65 979 295 64 979 309 64 979 330 62 979 335 61
979 336 60 979 346 59 979 354 59 979 355 57 979 355 57 979 393 55
................................................................................
do_execsql_test 5.2.7.2 {
SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
rank() OVER win,
dense_rank() OVER win
FROM t3
WINDOW win AS ( ORDER BY c , b , a
ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE CURRENT ROW )
ORDER BY 1 , 2 , 3
} {2851 89 89 3778 88 88 4681 87 87 5556 83 83 5574 82 82 5586 81 81
5640 84 84 5640 85 85 5640 86 86 7324 80 80 8123 77 77 8129 73 73
8129 74 74 8163 78 78 8163 79 79 8940 71 71 8968 75 75 8968 76 76
9727 66 66 9745 69 69 9745 70 70 9745 72 72 10504 65 65
10504 67 67 10504 68 68 11215 64 64 11844 62 62 11920 63 63
13274 60 60 13274 61 61 13897 58 58 13903 57 57 13925 56 56
................................................................................
do_execsql_test 5.3.7.1 {
SELECT max(c) OVER win,
min(c) OVER win,
count(a) OVER win
FROM t3
WINDOW win AS ( ORDER BY c , b , a
ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE GROUP )
ORDER BY 1 , 2 , 3
} {963 929 6 979 102 82 979 102 82 979 102 82 979 102 82 979 102 82
979 102 83 979 113 80 979 113 81 979 113 82 979 133 79 979 148 78
979 158 76 979 158 77 979 160 76 979 208 75 979 223 74 979 224 73
979 234 72 979 238 71 979 239 70 979 247 69 979 250 68 979 252 67
979 256 66 979 257 65 979 295 64 979 309 64 979 330 62 979 335 61
979 336 60 979 346 59 979 354 59 979 355 57 979 355 57 979 393 55
................................................................................
do_execsql_test 5.3.7.2 {
SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
rank() OVER win,
dense_rank() OVER win
FROM t3
WINDOW win AS ( ORDER BY c , b , a
ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE GROUP )
ORDER BY 1 , 2 , 3
} {2851 89 89 3778 88 88 4681 87 87 5556 83 83 5574 82 82 5586 81 81
5640 84 84 5640 85 85 5640 86 86 7324 80 80 8123 77 77 8129 73 73
8129 74 74 8163 78 78 8163 79 79 8940 71 71 8968 75 75 8968 76 76
9727 66 66 9745 69 69 9745 70 70 9745 72 72 10504 65 65
10504 67 67 10504 68 68 11215 64 64 11844 62 62 11920 63 63
13274 60 60 13274 61 61 13897 58 58 13903 57 57 13925 56 56
................................................................................
do_execsql_test 5.4.7.1 {
SELECT max(c) OVER win,
min(c) OVER win,
count(a) OVER win
FROM t3
WINDOW win AS ( ORDER BY c , b , a
ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE TIES )
ORDER BY 1 , 2 , 3
} {979 102 83 979 102 83 979 102 83 979 102 83 979 102 83 979 102 83
979 102 83 979 113 81 979 113 82 979 133 80 979 148 79 979 158 77
979 158 78 979 160 77 979 208 76 979 223 75 979 224 74 979 234 73
979 238 72 979 239 71 979 247 70 979 250 69 979 252 68 979 256 67
979 257 66 979 295 65 979 309 64 979 330 63 979 335 62 979 336 61
979 346 60 979 354 59 979 355 58 979 355 58 979 393 56 979 393 57
................................................................................
do_execsql_test 5.4.7.2 {
SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
rank() OVER win,
dense_rank() OVER win
FROM t3
WINDOW win AS ( ORDER BY c , b , a
ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE TIES )
ORDER BY 1 , 2 , 3
} {3830 89 89 4741 88 88 5640 84 84 5640 85 85 5640 86 86 5640 87 87
6485 81 81 6485 82 82 6485 83 83 7324 80 80 8163 78 78 8163 79 79
8968 73 73 8968 74 74 8968 75 75 8968 76 76 8968 77 77 9745 69 69
9745 70 70 9745 71 71 9745 72 72 10504 65 65 10504 66 66
10504 67 67 10504 68 68 11215 64 64 11920 63 63 12603 62 62
13274 60 60 13274 61 61 13941 59 59 14608 55 55 14608 56 56
................................................................................
19788 36 36 20181 35 35 20536 34 34 20891 30 30 20891 31 31
20891 32 32 20891 33 33 21226 28 28 21226 29 29 21535 27 27
21830 26 26 22087 22 22 22087 23 23 22087 24 24 22087 25 25
22334 21 21 22573 17 17 22573 18 18 22573 19 19 22573 20 20
22796 11 11 22796 12 12 22796 13 13 22796 14 14 22796 15 15
22796 16 16 22929 10 10 23042 9 9 23155 1 1 23155 2 2 23155 3 3
23155 4 4 23155 5 5 23155 6 6 23155 7 7 23155 8 8}
finish_test
|
| | | | | | | | > > > > > > > > > > > > > > > > > > > > > > > > |
3837 3838 3839 3840 3841 3842 3843 3844 3845 3846 3847 3848 3849 3850 3851 .... 3861 3862 3863 3864 3865 3866 3867 3868 3869 3870 3871 3872 3873 3874 3875 .... 4162 4163 4164 4165 4166 4167 4168 4169 4170 4171 4172 4173 4174 4175 4176 .... 4186 4187 4188 4189 4190 4191 4192 4193 4194 4195 4196 4197 4198 4199 4200 .... 4477 4478 4479 4480 4481 4482 4483 4484 4485 4486 4487 4488 4489 4490 4491 .... 4501 4502 4503 4504 4505 4506 4507 4508 4509 4510 4511 4512 4513 4514 4515 .... 4800 4801 4802 4803 4804 4805 4806 4807 4808 4809 4810 4811 4812 4813 4814 .... 4824 4825 4826 4827 4828 4829 4830 4831 4832 4833 4834 4835 4836 4837 4838 .... 4843 4844 4845 4846 4847 4848 4849 4850 4851 4852 4853 4854 4855 4856 4857 4858 4859 4860 4861 4862 4863 4864 4865 4866 4867 4868 4869 4870 4871 4872 4873 4874 4875 |
do_execsql_test 5.1.7.1 {
SELECT max(c) OVER win,
min(c) OVER win,
count(a) OVER win
FROM t3
WINDOW win AS ( ORDER BY c , b , a
ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE NO OTHERS )
ORDER BY 1 , 2 , 3
} {979 102 83 979 102 83 979 102 83 979 102 83 979 102 83 979 102 83
979 102 83 979 113 81 979 113 82 979 133 80 979 148 79 979 158 77
979 158 78 979 160 77 979 208 76 979 223 75 979 224 74 979 234 73
979 238 72 979 239 71 979 247 70 979 250 69 979 252 68 979 256 67
979 257 66 979 295 65 979 309 64 979 330 63 979 335 62 979 336 61
979 346 60 979 354 59 979 355 58 979 355 58 979 393 56 979 393 57
................................................................................
do_execsql_test 5.1.7.2 {
SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
rank() OVER win,
dense_rank() OVER win
FROM t3
WINDOW win AS ( ORDER BY c , b , a
ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE NO OTHERS )
ORDER BY 1 , 2 , 3
} {3830 89 89 4741 88 88 5640 84 84 5640 85 85 5640 86 86 5640 87 87
6485 81 81 6485 82 82 6485 83 83 7324 80 80 8163 78 78 8163 79 79
8968 73 73 8968 74 74 8968 75 75 8968 76 76 8968 77 77 9745 69 69
9745 70 70 9745 71 71 9745 72 72 10504 65 65 10504 66 66
10504 67 67 10504 68 68 11215 64 64 11920 63 63 12603 62 62
13274 60 60 13274 61 61 13941 59 59 14608 55 55 14608 56 56
................................................................................
do_execsql_test 5.2.7.1 {
SELECT max(c) OVER win,
min(c) OVER win,
count(a) OVER win
FROM t3
WINDOW win AS ( ORDER BY c , b , a
ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE CURRENT ROW )
ORDER BY 1 , 2 , 3
} {963 929 6 979 102 82 979 102 82 979 102 82 979 102 82 979 102 82
979 102 83 979 113 80 979 113 81 979 113 82 979 133 79 979 148 78
979 158 76 979 158 77 979 160 76 979 208 75 979 223 74 979 224 73
979 234 72 979 238 71 979 239 70 979 247 69 979 250 68 979 252 67
979 256 66 979 257 65 979 295 64 979 309 64 979 330 62 979 335 61
979 336 60 979 346 59 979 354 59 979 355 57 979 355 57 979 393 55
................................................................................
do_execsql_test 5.2.7.2 {
SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
rank() OVER win,
dense_rank() OVER win
FROM t3
WINDOW win AS ( ORDER BY c , b , a
ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE CURRENT ROW )
ORDER BY 1 , 2 , 3
} {2851 89 89 3778 88 88 4681 87 87 5556 83 83 5574 82 82 5586 81 81
5640 84 84 5640 85 85 5640 86 86 7324 80 80 8123 77 77 8129 73 73
8129 74 74 8163 78 78 8163 79 79 8940 71 71 8968 75 75 8968 76 76
9727 66 66 9745 69 69 9745 70 70 9745 72 72 10504 65 65
10504 67 67 10504 68 68 11215 64 64 11844 62 62 11920 63 63
13274 60 60 13274 61 61 13897 58 58 13903 57 57 13925 56 56
................................................................................
do_execsql_test 5.3.7.1 {
SELECT max(c) OVER win,
min(c) OVER win,
count(a) OVER win
FROM t3
WINDOW win AS ( ORDER BY c , b , a
ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE GROUP )
ORDER BY 1 , 2 , 3
} {963 929 6 979 102 82 979 102 82 979 102 82 979 102 82 979 102 82
979 102 83 979 113 80 979 113 81 979 113 82 979 133 79 979 148 78
979 158 76 979 158 77 979 160 76 979 208 75 979 223 74 979 224 73
979 234 72 979 238 71 979 239 70 979 247 69 979 250 68 979 252 67
979 256 66 979 257 65 979 295 64 979 309 64 979 330 62 979 335 61
979 336 60 979 346 59 979 354 59 979 355 57 979 355 57 979 393 55
................................................................................
do_execsql_test 5.3.7.2 {
SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
rank() OVER win,
dense_rank() OVER win
FROM t3
WINDOW win AS ( ORDER BY c , b , a
ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE GROUP )
ORDER BY 1 , 2 , 3
} {2851 89 89 3778 88 88 4681 87 87 5556 83 83 5574 82 82 5586 81 81
5640 84 84 5640 85 85 5640 86 86 7324 80 80 8123 77 77 8129 73 73
8129 74 74 8163 78 78 8163 79 79 8940 71 71 8968 75 75 8968 76 76
9727 66 66 9745 69 69 9745 70 70 9745 72 72 10504 65 65
10504 67 67 10504 68 68 11215 64 64 11844 62 62 11920 63 63
13274 60 60 13274 61 61 13897 58 58 13903 57 57 13925 56 56
................................................................................
do_execsql_test 5.4.7.1 {
SELECT max(c) OVER win,
min(c) OVER win,
count(a) OVER win
FROM t3
WINDOW win AS ( ORDER BY c , b , a
ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE TIES )
ORDER BY 1 , 2 , 3
} {979 102 83 979 102 83 979 102 83 979 102 83 979 102 83 979 102 83
979 102 83 979 113 81 979 113 82 979 133 80 979 148 79 979 158 77
979 158 78 979 160 77 979 208 76 979 223 75 979 224 74 979 234 73
979 238 72 979 239 71 979 247 70 979 250 69 979 252 68 979 256 67
979 257 66 979 295 65 979 309 64 979 330 63 979 335 62 979 336 61
979 346 60 979 354 59 979 355 58 979 355 58 979 393 56 979 393 57
................................................................................
do_execsql_test 5.4.7.2 {
SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
rank() OVER win,
dense_rank() OVER win
FROM t3
WINDOW win AS ( ORDER BY c , b , a
ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE TIES )
ORDER BY 1 , 2 , 3
} {3830 89 89 4741 88 88 5640 84 84 5640 85 85 5640 86 86 5640 87 87
6485 81 81 6485 82 82 6485 83 83 7324 80 80 8163 78 78 8163 79 79
8968 73 73 8968 74 74 8968 75 75 8968 76 76 8968 77 77 9745 69 69
9745 70 70 9745 71 71 9745 72 72 10504 65 65 10504 66 66
10504 67 67 10504 68 68 11215 64 64 11920 63 63 12603 62 62
13274 60 60 13274 61 61 13941 59 59 14608 55 55 14608 56 56
................................................................................
19788 36 36 20181 35 35 20536 34 34 20891 30 30 20891 31 31
20891 32 32 20891 33 33 21226 28 28 21226 29 29 21535 27 27
21830 26 26 22087 22 22 22087 23 23 22087 24 24 22087 25 25
22334 21 21 22573 17 17 22573 18 18 22573 19 19 22573 20 20
22796 11 11 22796 12 12 22796 13 13 22796 14 14 22796 15 15
22796 16 16 22929 10 10 23042 9 9 23155 1 1 23155 2 2 23155 3 3
23155 4 4 23155 5 5 23155 6 6 23155 7 7 23155 8 8}
#==========================================================================
do_execsql_test 6.0 {
DROP TABLE IF EXISTS t2;
CREATE TABLE t2(a TEXT, b INTEGER);
INSERT INTO t2 VALUES('A', NULL);
INSERT INTO t2 VALUES('B', NULL);
INSERT INTO t2 VALUES('C', 1);
} {}
do_execsql_test 6.1 {
SELECT group_concat(a, '.') OVER (
ORDER BY b RANGE BETWEEN 7 PRECEDING AND 2 PRECEDING
)
FROM t2
} {A.B A.B {}}
do_execsql_test 6.2 {
SELECT group_concat(a, '.') OVER (
ORDER BY b DESC RANGE BETWEEN 7 PRECEDING AND 2 PRECEDING
)
FROM t2
} {{} A.B A.B}
finish_test
|