Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Fix a problem with a window function in a correlated sub-query where at least one reference to the outer SELECT appears in a FROM clause sub-select. |
|---|---|
| Downloads: | Tarball | ZIP archive |
| Timelines: | family | ancestors | descendants | both | window-functions |
| Files: | files | file ages | folders |
| SHA3-256: |
9aca86f98388ff66b2bccb3fbfc486a3 |
| User & Date: | dan 2019-03-22 13:56:49.356 |
Context
|
2019-03-26
| ||
| 13:08 | Add support for new window functions related features - GROUPS frames, RANGE frames with logical start and end points, the EXCLUDE clause and window chaining. (check-in: c6da39115d user: dan tags: trunk) | |
|
2019-03-22
| ||
| 13:56 | Fix a problem with a window function in a correlated sub-query where at least one reference to the outer SELECT appears in a FROM clause sub-select. (Closed-Leaf check-in: 9aca86f983 user: dan tags: window-functions) | |
|
2019-03-21
| ||
| 13:51 | Remove assert() statements based on the counter-factual proposition that 0 is not a valid cursor number. (check-in: c7b336181a user: dan tags: window-functions) | |
Changes
Changes to src/resolve.c.
| ︙ | ︙ | |||
1420 1421 1422 1423 1424 1425 1426 |
p->pOrderBy = 0;
}
/* Recursively resolve names in all subqueries
*/
for(i=0; i<p->pSrc->nSrc; i++){
struct SrcList_item *pItem = &p->pSrc->a[i];
| | | 1420 1421 1422 1423 1424 1425 1426 1427 1428 1429 1430 1431 1432 1433 1434 |
p->pOrderBy = 0;
}
/* Recursively resolve names in all subqueries
*/
for(i=0; i<p->pSrc->nSrc; i++){
struct SrcList_item *pItem = &p->pSrc->a[i];
if( pItem->pSelect && (pItem->pSelect->selFlags & SF_Resolved)==0 ){
NameContext *pNC; /* Used to iterate name contexts */
int nRef = 0; /* Refcount for pOuterNC and outer contexts */
const char *zSavedContext = pParse->zAuthContext;
/* Count the total number of references to pOuterNC and all of its
** parent contexts. After resolving references to expressions in
** pItem->pSelect, check if this value has changed. If so, then
|
| ︙ | ︙ |
Changes to test/window1.test.
| ︙ | ︙ | |||
716 717 718 719 720 721 722 |
do_execsql_test 17.3 {
SELECT 10+sum(a) OVER (ORDER BY a)
FROM t8
ORDER BY 10+sum(a) OVER (ORDER BY a) DESC;
} {16 13 11}
| < < < < < < < < < < < < < < < < < < < < < < < < < < | 716 717 718 719 720 721 722 723 724 725 726 727 728 729 |
do_execsql_test 17.3 {
SELECT 10+sum(a) OVER (ORDER BY a)
FROM t8
ORDER BY 10+sum(a) OVER (ORDER BY a) DESC;
} {16 13 11}
#-------------------------------------------------------------------------
# Test error cases from chaining window definitions.
#
reset_db
do_execsql_test 18.0 {
DROP TABLE IF EXISTS t1;
|
| ︙ | ︙ | |||
1030 1031 1032 1033 1034 1035 1036 1037 |
do_execsql_test 24.1 {
SELECT sum(44) OVER ()
} {44}
do_execsql_test 24.2 {
SELECT lead(44) OVER ()
} {{}}
| > | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 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 1033 1034 1035 1036 1037 1038 1039 1040 1041 1042 1043 1044 1045 1046 1047 1048 1049 1050 1051 1052 1053 1054 1055 1056 1057 1058 1059 1060 1061 1062 1063 1064 1065 1066 1067 1068 1069 1070 1071 1072 1073 1074 |
do_execsql_test 24.1 {
SELECT sum(44) OVER ()
} {44}
do_execsql_test 24.2 {
SELECT lead(44) OVER ()
} {{}}
#-------------------------------------------------------------------------
#
reset_db
do_execsql_test 25.0 {
CREATE TABLE t1 ( t1_id INTEGER PRIMARY KEY );
CREATE TABLE t2 ( t2_id INTEGER PRIMARY KEY );
CREATE TABLE t3 ( t3_id INTEGER PRIMARY KEY );
INSERT INTO t1 VALUES(1), (3), (5);
INSERT INTO t2 VALUES (3), (5);
INSERT INTO t3 VALUES(10), (11), (12);
}
do_execsql_test 25.1 {
SELECT t1.* FROM t1, t2 WHERE
t1_id=t2_id AND t1_id IN (
SELECT t1_id + row_number() OVER ( ORDER BY t1_id ) FROM t3
)
}
do_execsql_test 25.2 {
SELECT t1.* FROM t1, t2 WHERE
t1_id=t2_id AND t1_id IN (
SELECT row_number() OVER ( ORDER BY t1_id ) FROM t3
)
} {3}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 26.0 {
CREATE TABLE t1(x);
CREATE TABLE t2(c);
}
do_execsql_test 26.1 {
SELECT ( SELECT row_number() OVER () FROM ( SELECT c FROM t1 ) ) FROM t2
} {}
do_execsql_test 26.2 {
INSERT INTO t1 VALUES(1), (2), (3), (4);
INSERT INTO t2 VALUES(2), (6), (8), (4);
SELECT c, c IN (
SELECT row_number() OVER () FROM ( SELECT c FROM t1 )
) FROM t2
} {2 1 6 0 8 0 4 1}
do_execsql_test 26.3 {
DELETE FROM t1;
DELETE FROM t2;
INSERT INTO t2 VALUES(1), (2), (3), (4);
INSERT INTO t1 VALUES(1), (1), (2), (3), (3), (3), (3), (4), (4);
SELECT c, c IN (
SELECT row_number() OVER () FROM ( SELECT 1 FROM t1 WHERE x=c )
) FROM t2
} {1 1 2 0 3 1 4 0}
finish_test
|