/ Check-in [0d11d777]
Login

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: 0d11d777c8d368f0b6e9faf3afccf7da1b041d303a68782e43aebd713aaecf51
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
Hide Diffs Unified Diffs Ignore Whitespace Patch

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
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_execsql_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);
} {{} {} {}}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 62.1 {
  CREATE TABLE t1(a VARCHAR(20), b FLOAT);
  INSERT INTO t1 VALUES('1',10.0);
}







|



|







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