/ Check-in [871796bb]
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:Fix error handling for the case where a window function is passed the wrong number of arguments.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 871796bb19e61c1282d8ac97a82d8b57bde50f2a2a08004ab53136d77c138df4
User & Date: dan 2019-07-13 17:45:25
Context
2019-07-13
17:54
Fix a typo in test file window1.test. check-in: 8c80e81d user: dan tags: trunk
17:45
Fix error handling for the case where a window function is passed the wrong number of arguments. check-in: 871796bb user: dan tags: trunk
17:21
Fix a double-quoted string literal used in the ".schema" command of the CLI. check-in: fcd937d9 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/resolve.c.

847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
...
874
875
876
877
878
879
880









881
882
883
884
885
886
887
            zType = "window";
          }else{
            zType = "aggregate";
          }
          sqlite3ErrorMsg(pParse, "misuse of %s function %.*s()",zType,nId,zId);
          pNC->nErr++;
          is_agg = 0;
        }else if( is_agg==0 && ExprHasProperty(pExpr, EP_WinFunc) ){
          assert( !IsWindowFunc(pExpr) );
          sqlite3ErrorMsg(pParse, 
              "filter clause may not be used with non-aggregate %.*s()", 
              nId, zId
          );
          pNC->nErr++;
        }
#else
        if( (is_agg && (pNC->ncFlags & NC_AllowAgg)==0) ){
          sqlite3ErrorMsg(pParse,"misuse of aggregate function %.*s()",nId,zId);
          pNC->nErr++;
          is_agg = 0;
        }
................................................................................
          sqlite3ErrorMsg(pParse, "no such function: %.*s", nId, zId);
          pNC->nErr++;
        }else if( wrong_num_args ){
          sqlite3ErrorMsg(pParse,"wrong number of arguments to function %.*s()",
               nId, zId);
          pNC->nErr++;
        }









        if( is_agg ){
          /* Window functions may not be arguments of aggregate functions.
          ** Or arguments of other window functions. But aggregate functions
          ** may be arguments for window functions.  */
#ifndef SQLITE_OMIT_WINDOWFUNC
          pNC->ncFlags &= ~(NC_AllowWin | (!pWin ? NC_AllowAgg : 0));
#else







<
<
<
<
<
<
<







 







>
>
>
>
>
>
>
>
>







847
848
849
850
851
852
853







854
855
856
857
858
859
860
...
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
            zType = "window";
          }else{
            zType = "aggregate";
          }
          sqlite3ErrorMsg(pParse, "misuse of %s function %.*s()",zType,nId,zId);
          pNC->nErr++;
          is_agg = 0;







        }
#else
        if( (is_agg && (pNC->ncFlags & NC_AllowAgg)==0) ){
          sqlite3ErrorMsg(pParse,"misuse of aggregate function %.*s()",nId,zId);
          pNC->nErr++;
          is_agg = 0;
        }
................................................................................
          sqlite3ErrorMsg(pParse, "no such function: %.*s", nId, zId);
          pNC->nErr++;
        }else if( wrong_num_args ){
          sqlite3ErrorMsg(pParse,"wrong number of arguments to function %.*s()",
               nId, zId);
          pNC->nErr++;
        }
#ifndef SQLITE_OMIT_WINDOWFUNC
        else if( is_agg==0 && ExprHasProperty(pExpr, EP_WinFunc) ){
          sqlite3ErrorMsg(pParse, 
              "FILTER may not be used with non-aggregate %.*s()", 
              nId, zId
          );
          pNC->nErr++;
        }
#endif
        if( is_agg ){
          /* Window functions may not be arguments of aggregate functions.
          ** Or arguments of other window functions. But aggregate functions
          ** may be arguments for window functions.  */
#ifndef SQLITE_OMIT_WINDOWFUNC
          pNC->ncFlags &= ~(NC_AllowWin | (!pWin ? NC_AllowAgg : 0));
#else

Changes to test/filter1.test.

87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
do_execsql_test 2.0 {
  CREATE TABLE t1(a);
  INSERT INTO t1 VALUES(1), (2), (3), (4), (5), (6), (7), (8), (9);
}

do_catchsql_test 2.1 {
  SELECT upper(a) FILTER (WHERE a=1) FROM t1
} {1 {filter clause may not be used with non-aggregate upper()}}

do_catchsql_test 2.2 {
  SELECT sum(a) FILTER (WHERE 1 - max(a) OVER () > 0) FROM t1
} {1 {misuse of window function max()}}

do_catchsql_test 2.3 {
  SELECT sum(a) FILTER (WHERE 1 - count(a)) FROM t1
} {1 {misuse of aggregate function count()}}

finish_test









|












87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
do_execsql_test 2.0 {
  CREATE TABLE t1(a);
  INSERT INTO t1 VALUES(1), (2), (3), (4), (5), (6), (7), (8), (9);
}

do_catchsql_test 2.1 {
  SELECT upper(a) FILTER (WHERE a=1) FROM t1
} {1 {FILTER may not be used with non-aggregate upper()}}

do_catchsql_test 2.2 {
  SELECT sum(a) FILTER (WHERE 1 - max(a) OVER () > 0) FROM t1
} {1 {misuse of window function max()}}

do_catchsql_test 2.3 {
  SELECT sum(a) FILTER (WHERE 1 - count(a)) FROM t1
} {1 {misuse of aggregate function count()}}

finish_test


Changes to test/window1.test.

253
254
255
256
257
258
259



260
261
262
263
264
265
266
} {1 {no such column: x}}
do_catchsql_test 7.1.6 {
  SELECT trim(x) OVER (ORDER BY y) FROM t1;
} {1 {trim() may not be used as a window function}}
do_catchsql_test 7.1.7 {
  SELECT max(x) OVER abc FROM t1 WINDOW def AS (ORDER BY y);
} {1 {no such window: abc}}




do_execsql_test 7.2 {
  SELECT 
    lead(y) OVER win, 
    lead(y, 2) OVER win, 
    lead(y, 3, 'default') OVER win
  FROM t1







>
>
>







253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
} {1 {no such column: x}}
do_catchsql_test 7.1.6 {
  SELECT trim(x) OVER (ORDER BY y) FROM t1;
} {1 {trim() may not be used as a window function}}
do_catchsql_test 7.1.7 {
  SELECT max(x) OVER abc FROM t1 WINDOW def AS (ORDER BY y);
} {1 {no such window: abc}}
do_catchsql_test 7.1.8 {
  SELECT row_number(x) OVER () FROM t1
} {1 {wrong number of arguments to function row_number}}

do_execsql_test 7.2 {
  SELECT 
    lead(y) OVER win, 
    lead(y, 2) OVER win, 
    lead(y, 3, 'default') OVER win
  FROM t1

Changes to test/windowerr.tcl.

60
61
62
63
64
65
66



67
68
69
  WINDOW win AS (ROWS BETWEEN 'hello' PRECEDING AND 10 FOLLOWING)
}
errorsql_test 3.2 {
  SELECT sum(a) OVER win FROM t1
  WINDOW win AS (ROWS BETWEEN 10 PRECEDING AND x'ABCD' FOLLOWING)
}





finish_test








>
>
>



60
61
62
63
64
65
66
67
68
69
70
71
72
  WINDOW win AS (ROWS BETWEEN 'hello' PRECEDING AND 10 FOLLOWING)
}
errorsql_test 3.2 {
  SELECT sum(a) OVER win FROM t1
  WINDOW win AS (ROWS BETWEEN 10 PRECEDING AND x'ABCD' FOLLOWING)
}

errorsql_test 3.3 {
  SELECT row_number(a) OVER () FROM t1;
}

finish_test

Changes to test/windowerr.test.

103
104
105
106
107
108
109
110





111
} } } 1

# PG says ERROR:  argument of ROWS must be type bigint, not type bit
do_test 3.2 { catch { execsql {
  SELECT sum(a) OVER win FROM t1
  WINDOW win AS (ROWS BETWEEN 10 PRECEDING AND x'ABCD' FOLLOWING)
} } } 1






finish_test








>
>
>
>
>

103
104
105
106
107
108
109
110
111
112
113
114
115
116
} } } 1

# PG says ERROR:  argument of ROWS must be type bigint, not type bit
do_test 3.2 { catch { execsql {
  SELECT sum(a) OVER win FROM t1
  WINDOW win AS (ROWS BETWEEN 10 PRECEDING AND x'ABCD' FOLLOWING)
} } } 1

# PG says ERROR:  function row_number(integer) does not exist
do_test 3.3 { catch { execsql {
  SELECT row_number(a) OVER () FROM t1;
} } } 1

finish_test