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

Changes to src/window.c.

   244    244     return;
   245    245   
   246    246    error_out:
   247    247     sqlite3_result_error(
   248    248         pCtx, "second argument to nth_value must be a positive integer", -1
   249    249     );
   250    250   }
   251         -static void nth_valueValueFunc(sqlite3_context *pCtx){
   252         -  struct NthValueCtx *p;
   253         -  p = (struct NthValueCtx*)sqlite3_aggregate_context(pCtx, sizeof(*p));
   254         -  if( p && p->pValue ){
   255         -    sqlite3_result_value(pCtx, p->pValue);
   256         -  }
   257         -}
   258    251   static void nth_valueFinalizeFunc(sqlite3_context *pCtx){
   259    252     struct NthValueCtx *p;
   260         -  p = (struct NthValueCtx*)sqlite3_aggregate_context(pCtx, sizeof(*p));
          253  +  p = (struct NthValueCtx*)sqlite3_aggregate_context(pCtx, 0);
   261    254     if( p && p->pValue ){
   262    255       sqlite3_result_value(pCtx, p->pValue);
   263    256       sqlite3_value_free(p->pValue);
   264    257       p->pValue = 0;
   265    258     }
   266    259   }
   267    260   #define nth_valueInvFunc noopStepFunc
          261  +#define nth_valueValueFunc noopValueFunc
   268    262   
   269    263   static void first_valueStepFunc(
   270    264     sqlite3_context *pCtx, 
   271    265     int nArg,
   272    266     sqlite3_value **apArg
   273    267   ){
   274    268     struct NthValueCtx *p;
................................................................................
   278    272       if( !p->pValue ){
   279    273         sqlite3_result_error_nomem(pCtx);
   280    274       }
   281    275     }
   282    276     UNUSED_PARAMETER(nArg);
   283    277     UNUSED_PARAMETER(apArg);
   284    278   }
   285         -static void first_valueValueFunc(sqlite3_context *pCtx){
   286         -  struct NthValueCtx *p;
   287         -  p = (struct NthValueCtx*)sqlite3_aggregate_context(pCtx, sizeof(*p));
   288         -  if( p && p->pValue ){
   289         -    sqlite3_result_value(pCtx, p->pValue);
   290         -  }
   291         -}
   292    279   static void first_valueFinalizeFunc(sqlite3_context *pCtx){
   293    280     struct NthValueCtx *p;
   294    281     p = (struct NthValueCtx*)sqlite3_aggregate_context(pCtx, sizeof(*p));
   295    282     if( p && p->pValue ){
   296    283       sqlite3_result_value(pCtx, p->pValue);
   297    284       sqlite3_value_free(p->pValue);
   298    285       p->pValue = 0;
   299    286     }
   300    287   }
   301    288   #define first_valueInvFunc noopStepFunc
          289  +#define first_valueValueFunc noopValueFunc
   302    290   
   303    291   /*
   304    292   ** Implementation of built-in window function rank(). Assumes that
   305    293   ** the window frame has been set to:
   306    294   **
   307    295   **   RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 
   308    296   */
................................................................................
   400    388     struct CallCount *p;
   401    389     UNUSED_PARAMETER(nArg); assert( nArg==0 );
   402    390     p = (struct CallCount*)sqlite3_aggregate_context(pCtx, sizeof(*p));
   403    391     p->nStep++;
   404    392   }
   405    393   static void cume_distValueFunc(sqlite3_context *pCtx){
   406    394     struct CallCount *p;
   407         -  p = (struct CallCount*)sqlite3_aggregate_context(pCtx, sizeof(*p));
   408         -  if( p && p->nTotal ){
          395  +  p = (struct CallCount*)sqlite3_aggregate_context(pCtx, 0);
          396  +  if( p ){
   409    397       double r = (double)(p->nStep) / (double)(p->nTotal);
   410    398       sqlite3_result_double(pCtx, r);
   411    399     }
   412    400   }
   413    401   #define cume_distFinalizeFunc cume_distValueFunc
   414    402   
   415    403   /*
................................................................................
   525    513         sqlite3_value_free(p->pVal);
   526    514         p->pVal = 0;
   527    515       }
   528    516     }
   529    517   }
   530    518   static void last_valueValueFunc(sqlite3_context *pCtx){
   531    519     struct LastValueCtx *p;
   532         -  p = (struct LastValueCtx*)sqlite3_aggregate_context(pCtx, sizeof(*p));
          520  +  p = (struct LastValueCtx*)sqlite3_aggregate_context(pCtx, 0);
   533    521     if( p && p->pVal ){
   534    522       sqlite3_result_value(pCtx, p->pVal);
   535    523     }
   536    524   }
   537    525   static void last_valueFinalizeFunc(sqlite3_context *pCtx){
   538    526     struct LastValueCtx *p;
   539    527     p = (struct LastValueCtx*)sqlite3_aggregate_context(pCtx, sizeof(*p));
................................................................................
  1401   1389         sqlite3VdbeJumpHere(v, addrIsNull);
  1402   1390       }else if( pWin->regApp ){
  1403   1391         assert( pFunc->zName==nth_valueName
  1404   1392              || pFunc->zName==first_valueName
  1405   1393         );
  1406   1394         assert( bInverse==0 || bInverse==1 );
  1407   1395         sqlite3VdbeAddOp2(v, OP_AddImm, pWin->regApp+1-bInverse, 1);
  1408         -    }else if( pFunc->zName==leadName || pFunc->zName==lagName ){
  1409         -      /* no-op */
  1410   1396       }else if( pFunc->xSFunc!=noopStepFunc ){
  1411   1397         int addrIf = 0;
  1412   1398         if( pWin->pFilter ){
  1413   1399           int regTmp;
  1414   1400           assert( nArg==0 || nArg==pWin->pOwner->x.pList->nExpr );
  1415   1401           assert( nArg || pWin->pOwner->x.pList==0 );
  1416   1402           regTmp = sqlite3GetTempReg(pParse);
................................................................................
  1502   1488        && (pWin->eStart!=TK_UNBOUNDED)
  1503   1489       ){
  1504   1490         sqlite3VdbeAddOp2(v, OP_Null, 0, pWin->regResult);
  1505   1491         sqlite3VdbeAddOp1(v, OP_Last, pWin->csrApp);
  1506   1492         VdbeCoverage(v);
  1507   1493         sqlite3VdbeAddOp3(v, OP_Column, pWin->csrApp, 0, pWin->regResult);
  1508   1494         sqlite3VdbeJumpHere(v, sqlite3VdbeCurrentAddr(v)-2);
  1509         -      if( bFin ){
  1510         -        sqlite3VdbeAddOp1(v, OP_ResetSorter, pWin->csrApp);
  1511         -      }
  1512   1495       }else if( pWin->regApp ){
  1513   1496         assert( pMWin->regStartRowid==0 );
  1514   1497       }else{
  1515   1498         int nArg = windowArgCount(pWin);
  1516   1499         if( bFin ){
  1517   1500           sqlite3VdbeAddOp2(v, OP_AggFinal, pWin->regAccum, nArg);
  1518   1501           sqlite3VdbeAppendP4(v, pWin->pFunc, P4_FUNCDEF);
................................................................................
  1522   1505           sqlite3VdbeAddOp3(v, OP_AggValue,pWin->regAccum,nArg,pWin->regResult);
  1523   1506           sqlite3VdbeAppendP4(v, pWin->pFunc, P4_FUNCDEF);
  1524   1507         }
  1525   1508       }
  1526   1509     }
  1527   1510   }
  1528   1511   
         1512  +/*
         1513  +** Generate code to calculate the current values of all window functions in the
         1514  +** p->pMWin list by doing a full scan of the current window frame. Store the
         1515  +** results in the Window.regResult registers, ready to return the upper
         1516  +** layer.
         1517  +*/
  1529   1518   static void windowFullScan(WindowCodeArg *p){
  1530   1519     Window *pWin;
  1531   1520     Parse *pParse = p->pParse;
  1532   1521     Window *pMWin = p->pMWin;
  1533   1522     Vdbe *v = p->pVdbe;
  1534   1523   
  1535   1524     int regCRowid = 0;              /* Current rowid value */
................................................................................
  1824   1813     sqlite3VdbeAddOp3(v, op, reg2, lbl, reg1);
  1825   1814     sqlite3VdbeChangeP5(v, SQLITE_NULLEQ);
  1826   1815   
  1827   1816     sqlite3ReleaseTempReg(pParse, reg1);
  1828   1817     sqlite3ReleaseTempReg(pParse, reg2);
  1829   1818   }
  1830   1819   
         1820  +/*
         1821  +** Helper function for sqlite3WindowCodeStep(). Each call to this function
         1822  +** generates VM code for a single RETURN_ROW, AGGSTEP or AGGINVERSE 
         1823  +** operation. Refer to the header comment for sqlite3WindowCodeStep() for
         1824  +** details.
         1825  +*/
  1831   1826   static int windowCodeOp(
  1832         - WindowCodeArg *p,
  1833         - int op,
  1834         - int regCountdown,
  1835         - int jumpOnEof
         1827  + WindowCodeArg *p,                /* Context object */
         1828  + int op,                          /* WINDOW_RETURN_ROW, AGGSTEP or AGGINVERSE */
         1829  + int regCountdown,                /* Register for OP_IfPos countdown */
         1830  + int jumpOnEof                    /* Jump here if stepped cursor reaches EOF */
  1836   1831   ){
  1837   1832     int csr, reg;
  1838   1833     Parse *pParse = p->pParse;
  1839   1834     Window *pMWin = p->pMWin;
  1840   1835     int ret = 0;
  1841   1836     Vdbe *v = p->pVdbe;
  1842   1837     int addrIf = 0; 
................................................................................
  1853   1848       assert( regCountdown==0 && jumpOnEof==0 );
  1854   1849       return 0;
  1855   1850     }
  1856   1851   
  1857   1852     if( regCountdown>0 ){
  1858   1853       if( pMWin->eType==TK_RANGE ){
  1859   1854         addrNextRange = sqlite3VdbeCurrentAddr(v);
  1860         -
  1861         -      switch( op ){
  1862         -        case WINDOW_RETURN_ROW: {
  1863         -          assert( 0 );
  1864         -          break;
  1865         -        }
  1866         -
  1867         -        case WINDOW_AGGINVERSE: {
  1868         -          if( pMWin->eStart==TK_FOLLOWING ){
  1869         -            windowCodeRangeTest(
  1870         -                p, OP_Le, p->current.csr, regCountdown, p->start.csr, lblDone
  1871         -            );
  1872         -          }else{
  1873         -            windowCodeRangeTest(
  1874         -                p, OP_Ge, p->start.csr, regCountdown, p->current.csr, lblDone
  1875         -            );
  1876         -          }
  1877         -          break;
         1855  +      assert( op==WINDOW_AGGINVERSE || op==WINDOW_AGGSTEP );
         1856  +      if( op==WINDOW_AGGINVERSE ){
         1857  +        if( pMWin->eStart==TK_FOLLOWING ){
         1858  +          windowCodeRangeTest(
         1859  +              p, OP_Le, p->current.csr, regCountdown, p->start.csr, lblDone
         1860  +          );
         1861  +        }else{
         1862  +          windowCodeRangeTest(
         1863  +              p, OP_Ge, p->start.csr, regCountdown, p->current.csr, lblDone
         1864  +          );
  1878   1865           }
  1879         -
  1880         -        case WINDOW_AGGSTEP: {
  1881         -          windowCodeRangeTest(
         1866  +      }else{
         1867  +        windowCodeRangeTest(
  1882   1868               p, OP_Gt, p->end.csr, regCountdown, p->current.csr, lblDone
  1883         -          );
  1884         -          break;
  1885         -        }
         1869  +        );
  1886   1870         }
  1887         -
  1888   1871       }else{
  1889   1872         addrIf = sqlite3VdbeAddOp3(v, OP_IfPos, regCountdown, 0, 1);
  1890   1873       }
  1891   1874     }
  1892   1875   
  1893         -  if( op==WINDOW_RETURN_ROW ){
         1876  +  if( op==WINDOW_RETURN_ROW && pMWin->regStartRowid==0 ){
  1894   1877       windowAggFinal(p, 0);
  1895   1878     }
  1896   1879     addrContinue = sqlite3VdbeCurrentAddr(v);
  1897   1880     switch( op ){
  1898   1881       case WINDOW_RETURN_ROW:
  1899   1882         csr = p->current.csr;
  1900   1883         reg = p->current.reg;
................................................................................
  1908   1891           assert( pMWin->regEndRowid );
  1909   1892           sqlite3VdbeAddOp2(v, OP_AddImm, pMWin->regStartRowid, 1);
  1910   1893         }else{
  1911   1894           windowAggStep(pParse, pMWin, csr, 1, p->regArg);
  1912   1895         }
  1913   1896         break;
  1914   1897   
  1915         -    case WINDOW_AGGSTEP:
         1898  +    default:
         1899  +      assert( op==WINDOW_AGGSTEP );
  1916   1900         csr = p->end.csr;
  1917   1901         reg = p->end.reg;
  1918   1902         if( pMWin->regStartRowid ){
  1919   1903           assert( pMWin->regEndRowid );
  1920   1904           sqlite3VdbeAddOp2(v, OP_AddImm, pMWin->regEndRowid, 1);
  1921   1905         }else{
  1922   1906           windowAggStep(pParse, pMWin, csr, 0, p->regArg);
................................................................................
  2417   2401         }
  2418   2402         break;
  2419   2403       default:
  2420   2404         s.eDelete = WINDOW_AGGINVERSE;
  2421   2405         break;
  2422   2406     }
  2423   2407   
  2424         -  s.eDelete = 0;
  2425         -
  2426   2408     /* Allocate registers for the array of values from the sub-query, the
  2427   2409     ** samve values in record form, and the rowid used to insert said record
  2428   2410     ** into the ephemeral table.  */
  2429   2411     regNew = pParse->nMem+1;
  2430   2412     pParse->nMem += nInput;
  2431   2413     regRecord = ++pParse->nMem;
  2432   2414     regRowid = ++pParse->nMem;
................................................................................
  2497   2479       windowCheckValue(pParse, regStart, 0 + (pMWin->eType==TK_RANGE ? 3 : 0));
  2498   2480     }
  2499   2481     if( regEnd ){
  2500   2482       sqlite3ExprCode(pParse, pMWin->pEnd, regEnd);
  2501   2483       windowCheckValue(pParse, regEnd, 1 + (pMWin->eType==TK_RANGE ? 3 : 0));
  2502   2484     }
  2503   2485   
  2504         -  if( pMWin->eStart==pMWin->eEnd && regStart && regEnd ){
         2486  +  if( pMWin->eStart==pMWin->eEnd && regStart ){
  2505   2487       int op = ((pMWin->eStart==TK_FOLLOWING) ? OP_Ge : OP_Le);
  2506   2488       int addrGe = sqlite3VdbeAddOp3(v, op, regStart, 0, regEnd);
  2507   2489       windowAggFinal(&s, 0);
  2508   2490       sqlite3VdbeAddOp2(v, OP_Rewind, s.current.csr, 1);
  2509   2491       windowReturnOneRow(&s);
  2510   2492       sqlite3VdbeAddOp1(v, OP_ResetSorter, s.current.csr);
  2511   2493       sqlite3VdbeAddOp2(v, OP_Goto, 0, lblWhereEnd);

Changes to test/window3.test.

cannot compute difference between binary files

Changes to test/windowerr.tcl.

    34     34     3 "ORDER BY a RANGE BETWEEN -1 PRECEDING AND 1 FOLLOWING"
    35     35     4 "ORDER BY a RANGE BETWEEN  1 PRECEDING AND -1 FOLLOWING"
    36     36   
    37     37     5 "ORDER BY a GROUPS BETWEEN -1 PRECEDING AND 1 FOLLOWING"
    38     38     6 "ORDER BY a GROUPS BETWEEN  1 PRECEDING AND -1 FOLLOWING"
    39     39   
    40     40     7 "ORDER BY a,b RANGE BETWEEN  1 PRECEDING AND 1 FOLLOWING"
           41  +
           42  +  8 "PARTITION BY a RANGE BETWEEN  1 PRECEDING AND 1 FOLLOWING"
    41     43   } {
    42     44     errorsql_test 1.$tn "
    43     45     SELECT a, sum(b) OVER (
    44     46       $frame
    45     47     ) FROM t1 ORDER BY 1
    46     48     "
    47     49   }
    48     50   
    49     51   finish_test
    50     52   

Changes to test/windowerr.test.

    74     74   
    75     75   # PG says ERROR:  RANGE with offset PRECEDING/FOLLOWING requires exactly one ORDER BY column
    76     76   do_test 1.7 { catch { execsql {
    77     77     SELECT a, sum(b) OVER (
    78     78       ORDER BY a,b RANGE BETWEEN  1 PRECEDING AND 1 FOLLOWING
    79     79     ) FROM t1 ORDER BY 1
    80     80   } } } 1
           81  +
           82  +# PG says ERROR:  RANGE with offset PRECEDING/FOLLOWING requires exactly one ORDER BY column
           83  +do_test 1.8 { catch { execsql {
           84  +  SELECT a, sum(b) OVER (
           85  +    PARTITION BY a RANGE BETWEEN  1 PRECEDING AND 1 FOLLOWING
           86  +  ) FROM t1 ORDER BY 1
           87  +} } } 1
    81     88   
    82     89   finish_test