Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Add tests to ensure that the window functions implementation is not generating code for unnecessary sorts. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | window-functions |
| Files: | files | file ages | folders |
| SHA3-256: | e195948a6876efe01b5cf2ed67bc9015 |
| User & Date: | dan 2019-03-19 17:45:31 |
| Wiki: | window-functions |
Context
|
2019-03-19
| ||
| 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 | |
| 16:49 | Add missing VdbeCoverage() macros to new code in window.c. check-in: 4f9b93e6 user: dan tags: window-functions | |
Changes
Changes to test/window1.test.
965 966 967 968 969 970 971 972 973 974 975 |
do_catchsql_test 22.$tn.2 "
WITH a(x, y) AS ( VALUES(1, 2) )
SELECT sum(x) OVER (
ORDER BY y RANGE BETWEEN UNBOUNDED PRECEDING AND $expr FOLLOWING
) FROM a
" $res
}
finish_test
|
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > |
965 966 967 968 969 970 971 972 973 974 975 976 977 978 979 980 981 982 983 984 985 986 987 988 989 990 991 992 993 994 995 996 997 998 999 1000 1001 1002 1003 1004 1005 1006 1007 1008 1009 1010 1011 1012 1013 1014 1015 1016 1017 1018 1019 1020 1021 1022 1023 1024 1025 1026 1027 1028 1029 1030 1031 1032 |
do_catchsql_test 22.$tn.2 "
WITH a(x, y) AS ( VALUES(1, 2) )
SELECT sum(x) OVER (
ORDER BY y RANGE BETWEEN UNBOUNDED PRECEDING AND $expr FOLLOWING
) FROM a
" $res
}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 23.0 {
CREATE TABLE t5(a, b, c);
CREATE INDEX t5ab ON t5(a, b);
}
proc do_ordercount_test {tn sql nOrderBy} {
set plan [execsql "EXPLAIN QUERY PLAN $sql"]
uplevel [list do_test $tn [list regexp -all ORDER $plan] $nOrderBy]
}
do_ordercount_test 23.1 {
SELECT
sum(c) OVER (ORDER BY a, b),
sum(c) OVER (PARTITION BY a ORDER BY b)
FROM t5
} 0
do_ordercount_test 23.2 {
SELECT
sum(c) OVER (ORDER BY b, a),
sum(c) OVER (PARTITION BY b ORDER BY a)
FROM t5
} 1
do_ordercount_test 23.3 {
SELECT
sum(c) OVER (ORDER BY b, a),
sum(c) OVER (ORDER BY c, b)
FROM t5
} 2
do_ordercount_test 23.4 {
SELECT
sum(c) OVER (ORDER BY b ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
sum(c) OVER (ORDER BY b RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
sum(c) OVER (ORDER BY b GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM t5
} 1
do_ordercount_test 23.5 {
SELECT
sum(c) OVER (ORDER BY b+1 ROWS UNBOUNDED PRECEDING),
sum(c) OVER (ORDER BY b+1 RANGE UNBOUNDED PRECEDING),
sum(c) OVER (ORDER BY b+1 GROUPS UNBOUNDED PRECEDING)
FROM t5
} 1
do_ordercount_test 23.6 {
SELECT
sum(c) OVER (ORDER BY b+1 ROWS UNBOUNDED PRECEDING),
sum(c) OVER (ORDER BY b+2 RANGE UNBOUNDED PRECEDING),
sum(c) OVER (ORDER BY b+3 GROUPS UNBOUNDED PRECEDING)
FROM t5
} 3
finish_test
|
Changes to test/window8.tcl.
161 162 163 164 165 166 167 168 169 170 171 172 173 174 |
7 { ORDER BY a RANGE BETWEEN 5.1 PRECEDING AND 5.3 FOLLOWING }
8 { ORDER BY a RANGE BETWEEN 10.2 PRECEDING AND 5.4 PRECEDING }
9 { ORDER BY a RANGE BETWEEN 2.6 FOLLOWING AND 3.5 FOLLOWING }
10 { ORDER BY a DESC RANGE BETWEEN 5.7 PRECEDING AND 5.8 FOLLOWING }
11 { ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND 5.9 PRECEDING }
12 { ORDER BY a DESC RANGE BETWEEN 2.1 FOLLOWING AND UNBOUNDED FOLLOWING }
} {
execsql_test 3.$tn "
SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ($frame)
"
}
==========
|
> |
161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 |
7 { ORDER BY a RANGE BETWEEN 5.1 PRECEDING AND 5.3 FOLLOWING }
8 { ORDER BY a RANGE BETWEEN 10.2 PRECEDING AND 5.4 PRECEDING }
9 { ORDER BY a RANGE BETWEEN 2.6 FOLLOWING AND 3.5 FOLLOWING }
10 { ORDER BY a DESC RANGE BETWEEN 5.7 PRECEDING AND 5.8 FOLLOWING }
11 { ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND 5.9 PRECEDING }
12 { ORDER BY a DESC RANGE BETWEEN 2.1 FOLLOWING AND UNBOUNDED FOLLOWING }
13 { ORDER BY a RANGE 5.1 PRECEDING }
} {
execsql_test 3.$tn "
SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ($frame)
"
}
==========
|
Changes to test/window8.test.
3490 3491 3492 3493 3494 3495 3496 3497 3498 3499 3500 3501 3502 3503 |
SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND 5.9 PRECEDING )
} {30 {} 22 90 20 90 15 170 13 210 13 210 10 210 5 292}
do_execsql_test 3.12 {
SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a DESC RANGE BETWEEN 2.1 FOLLOWING AND UNBOUNDED FOLLOWING )
} {30 232 22 112 20 112 15 30 13 30 13 30 10 10 5 {}}
#==========================================================================
do_execsql_test 4.0 {
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(a INTEGER, b INTEGER);
INSERT INTO t1 VALUES
(NULL, 1), (NULL, 2), (NULL, 3), (10, 4), (10, 5);
|
> > > > |
3490 3491 3492 3493 3494 3495 3496 3497 3498 3499 3500 3501 3502 3503 3504 3505 3506 3507 |
SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND 5.9 PRECEDING )
} {30 {} 22 90 20 90 15 170 13 210 13 210 10 210 5 292}
do_execsql_test 3.12 {
SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a DESC RANGE BETWEEN 2.1 FOLLOWING AND UNBOUNDED FOLLOWING )
} {30 232 22 112 20 112 15 30 13 30 13 30 10 10 5 {}}
do_execsql_test 3.13 {
SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a RANGE 5.1 PRECEDING )
} {5 10 10 30 13 72 13 72 15 102 20 70 22 120 30 90}
#==========================================================================
do_execsql_test 4.0 {
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(a INTEGER, b INTEGER);
INSERT INTO t1 VALUES
(NULL, 1), (NULL, 2), (NULL, 3), (10, 4), (10, 5);
|