Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Detect misuse of aggregate functions in the ORDER BY clause of a query even if the query also contains window functions. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA3-256: |
0d11d777c8d368f0b6e9faf3afccf7da |
| User & Date: | drh 2021-05-07 15:46:37 |
Context
|
2021-05-08
| ||
| 11:57 | Fix help-text typo in the CLI. (check-in: 56075314 user: drh tags: trunk) | |
|
2021-05-07
| ||
| 15:46 | Detect misuse of aggregate functions in the ORDER BY clause of a query even if the query also contains window functions. (check-in: 0d11d777 user: drh tags: trunk) | |
|
2021-05-06
| ||
| 20:47 | Fixes for cksmvfs and rbu so that they work together. (check-in: f35f6972 user: dan tags: trunk) | |
Changes
Changes to src/window.c.
| ︙ | ︙ | |||
936 937 938 939 940 941 942 943 944 945 946 947 948 949 |
if( pExpr->op==TK_AGG_FUNCTION
&& pExpr->op2>=pWalker->walkerDepth
){
pExpr->op2++;
}
return WRC_Continue;
}
/*
** If the SELECT statement passed as the second argument does not invoke
** any SQL window functions, this function is a no-op. Otherwise, it
** rewrites the SELECT statement so that window function xStep functions
** are invoked in the correct order as described under "SELECT REWRITING"
** at the top of this file.
| > > > > > > > > | 936 937 938 939 940 941 942 943 944 945 946 947 948 949 950 951 952 953 954 955 956 957 |
if( pExpr->op==TK_AGG_FUNCTION
&& pExpr->op2>=pWalker->walkerDepth
){
pExpr->op2++;
}
return WRC_Continue;
}
static int disallowAggregatesInOrderByCb(Walker *pWalker, Expr *pExpr){
if( pExpr->op==TK_AGG_FUNCTION && pExpr->pAggInfo==0 ){
sqlite3ErrorMsg(pWalker->pParse,
"misuse of aggregate: %s()", pExpr->u.zToken);
}
return WRC_Continue;
}
/*
** If the SELECT statement passed as the second argument does not invoke
** any SQL window functions, this function is a no-op. Otherwise, it
** rewrites the SELECT statement so that window function xStep functions
** are invoked in the correct order as described under "SELECT REWRITING"
** at the top of this file.
|
| ︙ | ︙ | |||
970 971 972 973 974 975 976 977 978 979 980 981 982 983 |
pTab = sqlite3DbMallocZero(db, sizeof(Table));
if( pTab==0 ){
return sqlite3ErrorToParser(db, SQLITE_NOMEM);
}
sqlite3AggInfoPersistWalkerInit(&w, pParse);
sqlite3WalkSelect(&w, p);
p->pSrc = 0;
p->pWhere = 0;
p->pGroupBy = 0;
p->pHaving = 0;
p->selFlags &= ~SF_Aggregate;
p->selFlags |= SF_WinRewrite;
| > > > > | 978 979 980 981 982 983 984 985 986 987 988 989 990 991 992 993 994 995 |
pTab = sqlite3DbMallocZero(db, sizeof(Table));
if( pTab==0 ){
return sqlite3ErrorToParser(db, SQLITE_NOMEM);
}
sqlite3AggInfoPersistWalkerInit(&w, pParse);
sqlite3WalkSelect(&w, p);
if( (p->selFlags & SF_Aggregate)==0 ){
w.xExprCallback = disallowAggregatesInOrderByCb;
sqlite3WalkExprList(&w, p->pOrderBy);
}
p->pSrc = 0;
p->pWhere = 0;
p->pGroupBy = 0;
p->pHaving = 0;
p->selFlags &= ~SF_Aggregate;
p->selFlags |= SF_WinRewrite;
|
| ︙ | ︙ |
Changes to test/window1.test.
| ︙ | ︙ | |||
1911 1912 1913 1914 1915 1916 1917 | # 2020-06-07 test case generated by dbsqlfuzz showing how an AggInfo # object might be referenced after the sqlite3Select() call that created # it returns. This proves the need to persist all AggInfo objects until # the Parse object is destroyed. # reset_db | | | | 1911 1912 1913 1914 1915 1916 1917 1918 1919 1920 1921 1922 1923 1924 1925 1926 1927 1928 1929 |
# 2020-06-07 test case generated by dbsqlfuzz showing how an AggInfo
# object might be referenced after the sqlite3Select() call that created
# it returns. This proves the need to persist all AggInfo objects until
# the Parse object is destroyed.
#
reset_db
do_catchsql_test 61.1 {
CREATE TABLE t1(a);
INSERT INTO t1 VALUES(5),(NULL),('seventeen');
SELECT (SELECT max(x)OVER(ORDER BY x) % min(x)OVER(ORDER BY CASE x WHEN 889 THEN x WHEN x THEN x END)) FROM (SELECT (SELECT sum(CAST(a IN(SELECT (SELECT max(x)OVER(ORDER BY CASE x WHEN 889 THEN 299 WHEN 863 THEN 863 END)) FROM (SELECT (SELECT sum(CAST((SELECT (SELECT max(x)OVER(ORDER BY x) / min(x)OVER(ORDER BY CASE x WHEN 889 THEN 299 WHEN -true THEN 863 END)) FROM (SELECT (SELECT sum(CAST(a IN(SELECT (SELECT max(x) & sum ( a )OVER(ORDER BY CASE x WHEN -8 THEN 299 WHEN 863 THEN 863 END)) FROM (SELECT (SELECT sum(CAST(a AS )) FROM t1) AS x FROM t1)) AS t1 )) FROM t1) AS x FROM t1)) AS x )) FROM t1) AS x FROM t1)) AS real)) FROM t1) AS x FROM t1);
} {1 {misuse of aggregate: sum()}}
#-------------------------------------------------------------------------
reset_db
do_execsql_test 62.1 {
CREATE TABLE t1(a VARCHAR(20), b FLOAT);
INSERT INTO t1 VALUES('1',10.0);
}
|
| ︙ | ︙ | |||
2088 2089 2090 2091 2092 2093 2094 2095 |
do_catchsql_test 67.1 {
SELECT a,c,b FROM t1 INTERSECT SELECT a,b,c FROM t1 ORDER BY (
SELECT nth_value(a,2) OVER w1
WINDOW w1 AS ( ORDER BY ((SELECT 1 FROM v1)) )
)
} {1 {1st ORDER BY term does not match any column in the result set}}
finish_test
| > > > > > > > > > > > > | 2088 2089 2090 2091 2092 2093 2094 2095 2096 2097 2098 2099 2100 2101 2102 2103 2104 2105 2106 2107 |
do_catchsql_test 67.1 {
SELECT a,c,b FROM t1 INTERSECT SELECT a,b,c FROM t1 ORDER BY (
SELECT nth_value(a,2) OVER w1
WINDOW w1 AS ( ORDER BY ((SELECT 1 FROM v1)) )
)
} {1 {1st ORDER BY term does not match any column in the result set}}
# 2021-05-07
# Do not allow aggregate functions in the ORDER BY clause even if
# there are window functions in the result set.
# Forum: /forumpost/540fdfef77
#
reset_db
do_catchsql_test 68.0 {
CREATE TABLE t1(a,b);
INSERT INTO t1(a,b) VALUES(0,0),(1,1),(2,4),(3,9),(4,99);
SELECT rowid, a, b, sum(a)OVER() FROM t1 ORDER BY count(b);
} {1 {misuse of aggregate: count()}}
finish_test
|