/ Check-in [d59bcc8e]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Return an error if DISTINCT is used with a window-function (e.g. "count(DISTINCT <expr>) OVER (...)").
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: d59bcc8eea4fcf0ee3c2263d31ee42f9f26c28434d2f0045f2d3329f18791d1c
User & Date: dan 2018-07-06 07:42:42
Original Comment: Return an error if DISTINCT is used with a window-function (.i.e. "count(DISTINCT <expr>) OVER (...)".
Context
2018-07-06
13:25
Remove some bad assert() statements from the implementations of window functions percent_rank() and cume_dist(). check-in: 443f0c28 user: dan tags: trunk
07:42
Return an error if DISTINCT is used with a window-function (e.g. "count(DISTINCT <expr>) OVER (...)"). check-in: d59bcc8e user: dan tags: trunk
2018-07-05
21:22
Use separate opcodes, OP_AggValue and OP_AggInverse, for the new callbacks associated with Window Functions, for improved readability of EXPLAIN output. check-in: fa653805 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/parse.y.

1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034

1035
1036
1037
1038
1039
1040
1041
  over_opt(Z)
%endif
. {
  if( Y && Y->nExpr>pParse->db->aLimit[SQLITE_LIMIT_FUNCTION_ARG] ){
    sqlite3ErrorMsg(pParse, "too many arguments on function %T", &X);
  }
  A = sqlite3ExprFunction(pParse, Y, &X);
  sqlite3WindowAttach(pParse, A, Z);
  if( D==SF_Distinct && A ){
    A->flags |= EP_Distinct;
  }

}
expr(A) ::= id(X) LP STAR RP
%ifndef SQLITE_OMIT_WINDOWFUNC
  over_opt(Z)
%endif
. {
  A = sqlite3ExprFunction(pParse, 0, &X);







<



>







1024
1025
1026
1027
1028
1029
1030

1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
  over_opt(Z)
%endif
. {
  if( Y && Y->nExpr>pParse->db->aLimit[SQLITE_LIMIT_FUNCTION_ARG] ){
    sqlite3ErrorMsg(pParse, "too many arguments on function %T", &X);
  }
  A = sqlite3ExprFunction(pParse, Y, &X);

  if( D==SF_Distinct && A ){
    A->flags |= EP_Distinct;
  }
  sqlite3WindowAttach(pParse, A, Z);
}
expr(A) ::= id(X) LP STAR RP
%ifndef SQLITE_OMIT_WINDOWFUNC
  over_opt(Z)
%endif
. {
  A = sqlite3ExprFunction(pParse, 0, &X);

Changes to src/window.c.

863
864
865
866
867
868
869

870
871




872
873
874
875
876
877
878
}

/*
** Attach window object pWin to expression p.
*/
void sqlite3WindowAttach(Parse *pParse, Expr *p, Window *pWin){
  if( p ){

    p->pWin = pWin;
    if( pWin ) pWin->pOwner = p;




  }else{
    sqlite3WindowDelete(pParse->db, pWin);
  }
}

/*
** Return 0 if the two window objects are identical, or non-zero otherwise.







>
|
|
>
>
>
>







863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
}

/*
** Attach window object pWin to expression p.
*/
void sqlite3WindowAttach(Parse *pParse, Expr *p, Window *pWin){
  if( p ){
    if( pWin ){
      p->pWin = pWin;
      pWin->pOwner = p;
      if( p->flags & EP_Distinct ){
        sqlite3ErrorMsg(pParse,"DISTINCT is not supported for window functions");
      }
    }
  }else{
    sqlite3WindowDelete(pParse->db, pWin);
  }
}

/*
** Return 0 if the two window objects are identical, or non-zero otherwise.

Changes to test/window6.test.

218
219
220
221
222
223
224

225
226
227
228
229
230





231
232
  1 1  2 1,2  3 1,2,3  4 2,3,4  5 3,4,5
}
do_catchsql_test 9.1 {
  WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<5)
  SELECT x, group_concat(x) OVER (ORDER BY x RANGE 2 PRECEDING)
  FROM c;
} {1 {RANGE PRECEDING is only supported with UNBOUNDED}}

do_catchsql_test 9.2 {
  WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<5)
  SELECT x, group_concat(x) OVER (ORDER BY x RANGE BETWEEN UNBOUNDED PRECEDING AND 2 FOLLOWING)
  FROM c;
} {1 {RANGE FOLLOWING is only supported with UNBOUNDED}}






finish_test








>






>
>
>
>
>


218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
  1 1  2 1,2  3 1,2,3  4 2,3,4  5 3,4,5
}
do_catchsql_test 9.1 {
  WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<5)
  SELECT x, group_concat(x) OVER (ORDER BY x RANGE 2 PRECEDING)
  FROM c;
} {1 {RANGE PRECEDING is only supported with UNBOUNDED}}

do_catchsql_test 9.2 {
  WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<5)
  SELECT x, group_concat(x) OVER (ORDER BY x RANGE BETWEEN UNBOUNDED PRECEDING AND 2 FOLLOWING)
  FROM c;
} {1 {RANGE FOLLOWING is only supported with UNBOUNDED}}

do_catchsql_test 9.3 {
  WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<5)
  SELECT count(DISTINCT x) OVER (ORDER BY x) FROM c;
} {1 {DISTINCT is not supported for window functions}}

finish_test