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

Changes to src/window.c.

  2008   2008       *pp = sqlite3WindowDup(db, 0, pWin);
  2009   2009       if( *pp==0 ) break;
  2010   2010       pp = &((*pp)->pNextWin);
  2011   2011     }
  2012   2012   
  2013   2013     return pRet;
  2014   2014   }
         2015  +
         2016  +/*
         2017  +** Return true if it can be determined at compile time that expression 
         2018  +** pExpr evaluates to a value that, when cast to an integer, is greater 
         2019  +** than zero. False otherwise.
         2020  +**
         2021  +** If an OOM error occurs, this function sets the Parse.db.mallocFailed 
         2022  +** flag and returns zero.
         2023  +*/
         2024  +static int windowExprGtZero(Parse *pParse, Expr *pExpr){
         2025  +  int ret = 0;
         2026  +  sqlite3 *db = pParse->db;
         2027  +  sqlite3_value *pVal = 0;
         2028  +  sqlite3ValueFromExpr(db, pExpr, db->enc, SQLITE_AFF_NUMERIC, &pVal);
         2029  +  if( pVal && sqlite3_value_int(pVal)>0 ){
         2030  +    ret = 1;
         2031  +  }
         2032  +  sqlite3ValueFree(pVal);
         2033  +  return ret;
         2034  +}
  2015   2035   
  2016   2036   /*
  2017   2037   ** sqlite3WhereBegin() has already been called for the SELECT statement 
  2018   2038   ** passed as the second argument when this function is invoked. It generates
  2019   2039   ** code to populate the Window.regResult register for each window function 
  2020   2040   ** and invoke the sub-routine at instruction addrGosub once for each row.
  2021   2041   ** sqlite3WhereEnd() is always called before returning. 
................................................................................
  2402   2422     /* Figure out when rows may be deleted from the ephemeral table. There
  2403   2423     ** are four options - they may never be deleted (eDelete==0), they may 
  2404   2424     ** be deleted as soon as they are no longer part of the window frame
  2405   2425     ** (eDelete==WINDOW_AGGINVERSE), they may be deleted as after the row 
  2406   2426     ** has been returned to the caller (WINDOW_RETURN_ROW), or they may
  2407   2427     ** be deleted after they enter the frame (WINDOW_AGGSTEP). */
  2408   2428     switch( pMWin->eStart ){
  2409         -    case TK_FOLLOWING: {
  2410         -      if( pMWin->eType!=TK_RANGE ){
  2411         -        sqlite3 *db = pParse->db;
  2412         -        sqlite3_value *pVal = 0;
  2413         -        sqlite3ValueFromExpr(db,pMWin->pStart,db->enc,SQLITE_AFF_NUMERIC,&pVal);
  2414         -        if( pVal && sqlite3_value_int(pVal)>0 ){
  2415         -          s.eDelete = WINDOW_RETURN_ROW;
  2416         -        }
  2417         -        sqlite3ValueFree(pVal);
         2429  +    case TK_FOLLOWING:
         2430  +      if( pMWin->eType!=TK_RANGE && windowExprGtZero(pParse, pMWin->pStart) ){
         2431  +        s.eDelete = WINDOW_RETURN_ROW;
  2418   2432         }
  2419   2433         break;
  2420         -    }
  2421   2434       case TK_UNBOUNDED:
  2422   2435         if( windowCacheFrame(pMWin)==0 ){
  2423   2436           if( pMWin->eEnd==TK_PRECEDING ){
  2424         -          s.eDelete = WINDOW_AGGSTEP;
         2437  +          if( pMWin->eType!=TK_RANGE && windowExprGtZero(pParse, pMWin->pEnd) ){
         2438  +            s.eDelete = WINDOW_AGGSTEP;
         2439  +          }
  2425   2440           }else{
  2426   2441             s.eDelete = WINDOW_RETURN_ROW;
  2427   2442           }
  2428   2443         }
  2429   2444         break;
  2430   2445       default:
  2431   2446         s.eDelete = WINDOW_AGGINVERSE;

Changes to test/window6.test.

   332    332   do_execsql_test 11.2 {
   333    333     SELECT a, (SELECT y FROM t3 WHERE x=a), sum(a) OVER (ORDER BY a)
   334    334       FROM t1 ORDER BY a;
   335    335   } {
   336    336     10 ten 10   15 fifteen 25   20 {} 65        20 {} 65   
   337    337     25 {} 90    30 thirty 150   30 thirty 150   50 {} 200
   338    338   }
          339  +
          340  +do_execsql_test 11.3.1 {
          341  +  SELECT a, sum(a) OVER win FROM t1
          342  +  WINDOW win AS (ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
          343  +} {
          344  +  10 10 15 25 20 45 20 65 25 90 30 120 30 150 50 200
          345  +}
          346  +do_execsql_test 11.3.2 {
          347  +  SELECT a, sum(a) OVER win FROM t1
          348  +  WINDOW win AS (ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND 0 FOLLOWING)
          349  +} {
          350  +  10 10 15 25 20 45 20 65 25 90 30 120 30 150 50 200
          351  +}
          352  +do_execsql_test 11.3.3 {
          353  +  SELECT a, sum(a) OVER win FROM t1
          354  +  WINDOW win AS (ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND 0 PRECEDING)
          355  +} {
          356  +  10 10 15 25 20 45 20 65 25 90 30 120 30 150 50 200
          357  +}
          358  +
          359  +do_execsql_test 11.4.1 {
          360  +  SELECT y, group_concat(y, '.') OVER win FROM t3
          361  +  WINDOW win AS (
          362  +    ORDER BY y RANGE BETWEEN UNBOUNDED PRECEDING AND 10 PRECEDING
          363  +  );
          364  +} {
          365  +  fifteen fifteen 
          366  +  ten     fifteen.ten 
          367  +  thirty  fifteen.ten.thirty
          368  +}
   339    369   
   340    370   finish_test
          371  +