/ Check-in [801074ce]
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 a problem with using min() or max() as a window function.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | exp-window-functions
Files: files | file ages | folders
SHA3-256: 801074ce63d3f4825cc9fa508c42629a9f74e7f9e35c5f238343bb1cff4fbae1
User & Date: dan 2018-06-19 17:13:11
Context
2018-06-19
18:11
Fix a problem caused by a sub-query containing a window function in the FROM clause of a query that itself uses a window function. check-in: f4b1b6f8 user: dan tags: exp-window-functions
17:13
Fix a problem with using min() or max() as a window function. check-in: 801074ce user: dan tags: exp-window-functions
2018-06-18
20:34
Fix problems with using window functions in CREATE VIEW statements. check-in: 943bccd2 user: dan tags: exp-window-functions
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/func.c.

  1606   1606     Mem *pArg  = (Mem *)argv[0];
  1607   1607     Mem *pBest;
  1608   1608     UNUSED_PARAMETER(NotUsed);
  1609   1609   
  1610   1610     pBest = (Mem *)sqlite3_aggregate_context(context, sizeof(*pBest));
  1611   1611     if( !pBest ) return;
  1612   1612   
  1613         -  if( sqlite3_value_type(argv[0])==SQLITE_NULL ){
         1613  +  if( sqlite3_value_type(pArg)==SQLITE_NULL ){
  1614   1614       if( pBest->flags ) sqlite3SkipAccumulatorLoad(context);
  1615   1615     }else if( pBest->flags ){
  1616   1616       int max;
  1617   1617       int cmp;
  1618   1618       CollSeq *pColl = sqlite3GetFuncCollSeq(context);
  1619   1619       /* This step function is used for both the min() and max() aggregates,
  1620   1620       ** the only difference between the two being that the sense of the
................................................................................
  1632   1632         sqlite3SkipAccumulatorLoad(context);
  1633   1633       }
  1634   1634     }else{
  1635   1635       pBest->db = sqlite3_context_db_handle(context);
  1636   1636       sqlite3VdbeMemCopy(pBest, pArg);
  1637   1637     }
  1638   1638   }
  1639         -static void minMaxFinalize(sqlite3_context *context){
         1639  +static void minMaxValueFinalize(sqlite3_context *context, int bValue){
  1640   1640     sqlite3_value *pRes;
  1641   1641     pRes = (sqlite3_value *)sqlite3_aggregate_context(context, 0);
  1642   1642     if( pRes ){
  1643   1643       if( pRes->flags ){
  1644   1644         sqlite3_result_value(context, pRes);
  1645   1645       }
  1646         -    sqlite3VdbeMemRelease(pRes);
         1646  +    if( bValue==0 ) sqlite3VdbeMemRelease(pRes);
  1647   1647     }
         1648  +}
         1649  +static void minMaxValue(sqlite3_context *context){
         1650  +  return minMaxValueFinalize(context, 1);
         1651  +}
         1652  +static void minMaxFinalize(sqlite3_context *context){
         1653  +  return minMaxValueFinalize(context, 0);
  1648   1654   }
  1649   1655   
  1650   1656   /*
  1651   1657   ** group_concat(EXPR, ?SEPARATOR?)
  1652   1658   */
  1653   1659   static void groupConcatStep(
  1654   1660     sqlite3_context *context,
................................................................................
  1879   1885       FUNCTION(ltrim,              2, 1, 0, trimFunc         ),
  1880   1886       FUNCTION(rtrim,              1, 2, 0, trimFunc         ),
  1881   1887       FUNCTION(rtrim,              2, 2, 0, trimFunc         ),
  1882   1888       FUNCTION(trim,               1, 3, 0, trimFunc         ),
  1883   1889       FUNCTION(trim,               2, 3, 0, trimFunc         ),
  1884   1890       FUNCTION(min,               -1, 0, 1, minmaxFunc       ),
  1885   1891       FUNCTION(min,                0, 0, 1, 0                ),
  1886         -    AGGREGATE2(min,              1, 0, 1, minmaxStep,      minMaxFinalize,
         1892  +    WAGGREGATE(min, 1, 0, 1, minmaxStep, minMaxFinalize, minMaxValue, 0,
  1887   1893                                             SQLITE_FUNC_MINMAX ),
  1888   1894       FUNCTION(max,               -1, 1, 1, minmaxFunc       ),
  1889   1895       FUNCTION(max,                0, 1, 1, 0                ),
  1890         -    AGGREGATE2(max,              1, 1, 1, minmaxStep,      minMaxFinalize,
         1896  +    WAGGREGATE(max, 1, 1, 1, minmaxStep, minMaxFinalize, minMaxValue, 0,
  1891   1897                                             SQLITE_FUNC_MINMAX ),
  1892   1898       FUNCTION2(typeof,            1, 0, 0, typeofFunc,  SQLITE_FUNC_TYPEOF),
  1893   1899       FUNCTION2(length,            1, 0, 0, lengthFunc,  SQLITE_FUNC_LENGTH),
  1894   1900       FUNCTION(instr,              2, 0, 0, instrFunc        ),
  1895   1901       FUNCTION(printf,            -1, 0, 0, printfFunc       ),
  1896   1902       FUNCTION(unicode,            1, 0, 0, unicodeFunc      ),
  1897   1903       FUNCTION(char,              -1, 0, 0, charFunc         ),
................................................................................
  1914   1920       VFUNCTION(last_insert_rowid, 0, 0, 0, last_insert_rowid),
  1915   1921       VFUNCTION(changes,           0, 0, 0, changes          ),
  1916   1922       VFUNCTION(total_changes,     0, 0, 0, total_changes    ),
  1917   1923       FUNCTION(replace,            3, 0, 0, replaceFunc      ),
  1918   1924       FUNCTION(zeroblob,           1, 0, 0, zeroblobFunc     ),
  1919   1925       FUNCTION(substr,             2, 0, 0, substrFunc       ),
  1920   1926       FUNCTION(substr,             3, 0, 0, substrFunc       ),
  1921         -    WAGGREGATE(sum,   1,0,0, sumStep, sumFinalize, sumFinalize, sumInverse),
  1922         -    WAGGREGATE(total, 1,0,0, sumStep, totalFinalize, totalFinalize, sumInverse),
  1923         -    WAGGREGATE(avg,   1,0,0, sumStep, avgFinalize, avgFinalize, sumInverse),
         1927  +    WAGGREGATE(sum,   1,0,0, sumStep, sumFinalize, sumFinalize, sumInverse, 0),
         1928  +    WAGGREGATE(total, 1,0,0, sumStep,totalFinalize,totalFinalize,sumInverse, 0),
         1929  +    WAGGREGATE(avg,   1,0,0, sumStep, avgFinalize, avgFinalize, sumInverse, 0),
  1924   1930       AGGREGATE2(count, 0,0,0, countStep, countFinalize, SQLITE_FUNC_COUNT  ),
  1925         -    WAGGREGATE(count, 1,0,0, countStep, countFinalize, 0, 0 ),
         1931  +    WAGGREGATE(count, 1,0,0, countStep, countFinalize, 0, 0, 0 ),
  1926   1932       WAGGREGATE(group_concat, 1, 0, 0, groupConcatStep, 
  1927         -        groupConcatFinalize, groupConcatValue, groupConcatInverse),
         1933  +        groupConcatFinalize, groupConcatValue, groupConcatInverse, 0),
  1928   1934       WAGGREGATE(group_concat, 2, 0, 0, groupConcatStep, 
  1929         -        groupConcatFinalize, groupConcatValue, groupConcatInverse),
         1935  +        groupConcatFinalize, groupConcatValue, groupConcatInverse, 0),
  1930   1936     
  1931   1937       LIKEFUNC(glob, 2, &globInfo, SQLITE_FUNC_LIKE|SQLITE_FUNC_CASE),
  1932   1938   #ifdef SQLITE_CASE_SENSITIVE_LIKE
  1933   1939       LIKEFUNC(like, 2, &likeInfoAlt, SQLITE_FUNC_LIKE|SQLITE_FUNC_CASE),
  1934   1940       LIKEFUNC(like, 3, &likeInfoAlt, SQLITE_FUNC_LIKE|SQLITE_FUNC_CASE),
  1935   1941   #else
  1936   1942       LIKEFUNC(like, 2, &likeInfoNorm, SQLITE_FUNC_LIKE),

Changes to src/sqliteInt.h.

  1758   1758   #define AGGREGATE(zName, nArg, arg, nc, xStep, xFinal, xValue) \
  1759   1759     {nArg, SQLITE_UTF8|(nc*SQLITE_FUNC_NEEDCOLL), \
  1760   1760      SQLITE_INT_TO_PTR(arg), 0, xStep,xFinal,xValue,0,#zName, {0}}
  1761   1761   #define AGGREGATE2(zName, nArg, arg, nc, xStep, xFinal, extraFlags) \
  1762   1762     {nArg, SQLITE_UTF8|(nc*SQLITE_FUNC_NEEDCOLL)|extraFlags, \
  1763   1763      SQLITE_INT_TO_PTR(arg), 0, xStep,xFinal,xFinal,0,#zName, {0}}
  1764   1764   
  1765         -#define WAGGREGATE(zName, nArg, arg, nc, xStep, xFinal, xValue, xInverse) \
  1766         -  {nArg, SQLITE_UTF8|(nc*SQLITE_FUNC_NEEDCOLL), \
         1765  +#define WAGGREGATE(zName, nArg, arg, nc, xStep, xFinal, xValue, xInverse, f) \
         1766  +  {nArg, SQLITE_UTF8|(nc*SQLITE_FUNC_NEEDCOLL)|f, \
  1767   1767      SQLITE_INT_TO_PTR(arg), 0, xStep,xFinal,xValue,xInverse,#zName, {0}}
  1768   1768   
  1769   1769   /*
  1770   1770   ** All current savepoints are stored in a linked list starting at
  1771   1771   ** sqlite3.pSavepoint. The first element in the list is the most recently
  1772   1772   ** opened savepoint. Savepoints are added to the list by the vdbe
  1773   1773   ** OP_Savepoint instruction.

Changes to test/window1.test.

   265    265     4 6 8   6 8 10   8 10 default   10 {} default   {} {} default
   266    266   }
   267    267   
   268    268   do_execsql_test 7.3 {
   269    269     SELECT row_number() OVER (ORDER BY x) FROM t1
   270    270   } {1 2 3 4 5}
   271    271   
   272         -breakpoint
   273    272   do_execsql_test 7.4 {
   274    273     SELECT 
   275    274       row_number() OVER win,
   276    275       lead(x) OVER win
   277    276     FROM t1
   278    277     WINDOW win AS (ORDER BY x ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
   279    278   } {1 3  2 5  3 7  4 9   5 {}}
................................................................................
   313    312   do_execsql_test 8.2.1 {
   314    313     SELECT * FROM v1
   315    314   } {1 1 1  3 1 2  6 1 3  10 1 4  15 1 5  21 1 6}
   316    315   do_execsql_test 8.2.2 {
   317    316     SELECT * FROM v2
   318    317   } {1 1 1  3 1 2  6 1 3  10 1 4  15 1 5  21 1 6}
   319    318   
          319  +#-------------------------------------------------------------------------
          320  +# Attempt to use a window function in a trigger.
          321  +#
          322  +do_execsql_test 9.0 {
          323  +  CREATE TABLE t4(x, y);
          324  +  INSERT INTO t4 VALUES(1, 'g');
          325  +  INSERT INTO t4 VALUES(2, 'i');
          326  +  INSERT INTO t4 VALUES(3, 'l');
          327  +  INSERT INTO t4 VALUES(4, 'g');
          328  +  INSERT INTO t4 VALUES(5, 'a');
          329  +
          330  +  CREATE TABLE t5(x, y, m);
          331  +  CREATE TRIGGER t4i AFTER INSERT ON t4 BEGIN
          332  +    DELETE FROM t5;
          333  +    INSERT INTO t5 
          334  +      SELECT x, y, max(y) OVER xyz FROM t4
          335  +      WINDOW xyz AS (PARTITION BY (x%2) ORDER BY x);
          336  +  END;
          337  +}
          338  +
          339  +do_execsql_test 9.1.1 {
          340  +  SELECT x, y, max(y) OVER xyz FROM t4
          341  +      WINDOW xyz AS (PARTITION BY (x%2) ORDER BY x) ORDER BY 1
          342  +} {1 g g   2 i i   3 l l   4 g i   5 a l}
          343  +
          344  +do_execsql_test 9.1.2 {
          345  +  INSERT INTO t4 VALUES(6, 'm');
          346  +  SELECT x, y, max(y) OVER xyz FROM t4
          347  +      WINDOW xyz AS (PARTITION BY (x%2) ORDER BY x) ORDER BY 1
          348  +} {1 g g   2 i i   3 l l   4 g i   5 a l   6 m m}
          349  +
          350  +do_execsql_test 9.1.3 {
          351  +  SELECT * FROM t5 ORDER BY 1
          352  +} {1 g g   2 i i   3 l l   4 g i   5 a l   6 m m}
   320    353   
   321    354   
   322    355   finish_test
   323    356