/ Check-in [5fe15c1d83]
Login

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

Overview
Comment:Further simplifications to window-function code.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 5fe15c1d8383989257e11d1806e6b035acacd1b504ae385ba58d20db10f26eee
User & Date: dan 2019-09-25 16:41:44
Context
2019-09-25
17:47
Remove an unused variable. check-in: a19884455b user: drh tags: trunk
16:41
Further simplifications to window-function code. check-in: 5fe15c1d83 user: dan tags: trunk
11:49
In the previous check-in, the variable should be openMode, not openFlags. check-in: 77b0db22d6 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/window.c.

1478
1479
1480
1481
1482
1483
1484
1485
1486
1487
1488
1489
1490
1491
1492
1493
....
1595
1596
1597
1598
1599
1600
1601
1602
1603
1604
1605
1606
1607
1608
1609
1610
1611
1612
1613
1614
1615
1616
1617
1618
1619
1620
1621
1622
1623
1624
1625
1626
1627
1628
1629
1630
1631
1632
1633
1634
1635
1636
1637
1638
1639
1640
....
2176
2177
2178
2179
2180
2181
2182


















2183
2184
2185
2186
2187
2188
2189
....
2226
2227
2228
2229
2230
2231
2232
2233
2234
2235
2236
2237
2238
2239
2240
2241
2242
2243
2244
2245
2246
2247
2248
2249
2250
2251
2252
2253
2254
2255
2256
....
2607
2608
2609
2610
2611
2612
2613
2614
2615
2616
2617

2618
2619
2620
2621
2622
2623
2624
2625
2626
2627

2628
2629
2630
2631
2632
2633
2634
....
2685
2686
2687
2688
2689
2690
2691


2692
2693
2694
2695
2696
2697
2698
....
2755
2756
2757
2758
2759
2760
2761
2762
2763
2764
2765
2766
2767
2768
2769
2770
2771
2772
....
2813
2814
2815
2816
2817
2818
2819
2820
2821
2822
2823
2824
2825
2826
2827
2828
2829
2830
2831
2832
2833
2834
2835
2836
2837
2838
2839
2840
2841
2842
2843
2844
2845
2846
2847
2848
2849
2850
2851
....
2869
2870
2871
2872
2873
2874
2875
2876
2877
2878
2879
2880
2881
2882
2883
2884
2885
2886
2887
2888
2889
2890
2891
2892
2893
2894
2895
2896
2897
2898
2899
2900
2901
2902
2903
2904
2905
2906
2907
2908
2909
2910
2911
2912
2913
2914
2915
2916
2917
2918
2919
2920
2921
2922
2923
2924
....
2929
2930
2931
2932
2933
2934
2935
2936
2937
2938
2939
2940
2941
2942
2943
2944
2945
2946
2947
2948
2949
2950
2951
2952
2953
2954
2955
2956
2957
2958
2959
2960
2961
2962
2963
2964
2965
....
2966
2967
2968
2969
2970
2971
2972
2973
2974
2975
2976
2977
2978
2979
2980
  Parse *pParse;             /* Parse context */
  Window *pMWin;             /* First in list of functions being processed */
  Vdbe *pVdbe;               /* VDBE object */
  int addrGosub;             /* OP_Gosub to this address to return one row */
  int regGosub;              /* Register used with OP_Gosub(addrGosub) */
  int regArg;                /* First in array of accumulator registers */
  int eDelete;               /* See above */
  int regStart;              /* Value of <expr> PRECEDING */
  int regEnd;                /* Value of <expr> FOLLOWING */

  WindowCsrAndReg start;
  WindowCsrAndReg current;
  WindowCsrAndReg end;
};

/*
................................................................................
        assert( pWin->bExprArgs || nArg  ||pWin->pOwner->x.pList==0 );
        regTmp = sqlite3GetTempReg(pParse);
        sqlite3VdbeAddOp3(v, OP_Column, csr, pWin->iArgCol+nArg,regTmp);
        addrIf = sqlite3VdbeAddOp3(v, OP_IfNot, regTmp, 0, 1);
        VdbeCoverage(v);
        sqlite3ReleaseTempReg(pParse, regTmp);
      }

      
      /* If this is a (RANGE BETWEEN a PRECEDING AND b PRECEDING) or a
      ** (RANGE BETWEEN b FOLLOWING AND a FOLLOWING) frame and (b > a), 
      ** omit the OP_AggStep or OP_AggInverse if the peer value is numeric.
      ** A numeric peer value is one for which the following is true: 
      **
      **    (peer IS NOT NULL AND peer < '')
      */
      if( pWin->eFrmType==TK_RANGE 
       && pWin->eStart==pWin->eEnd 
       && pWin->eStart==TK_PRECEDING
      ){
        int regPeer = sqlite3GetTempReg(pParse);
        int regString = sqlite3GetTempReg(pParse);
        int lbl = sqlite3VdbeMakeLabel(pParse);
        VdbeModuleComment((v, "windowAggStep \"peer is numeric?\" test"));
        assert( pMWin->eStart==TK_PRECEDING ); /* because pWin same as pMWin */
        sqlite3VdbeAddOp3(v, OP_Le, p->regStart, lbl, p->regEnd);
        VdbeCoverageNeverNull(v); /* because <expr> values previously checked */
        windowReadPeerValues(p, csr, regPeer);
        sqlite3VdbeAddOp2(v, OP_IsNull, regPeer, lbl);
        sqlite3VdbeAddOp4(v, OP_String8, 0, regString, 0, "", P4_STATIC);
        addrIf2 = sqlite3VdbeAddOp3(v, OP_Lt, regString, 0, regPeer);
        sqlite3ReleaseTempReg(pParse, regPeer);
        sqlite3ReleaseTempReg(pParse, regString);
        sqlite3VdbeResolveLabel(v, lbl);
        VdbeModuleComment((v, "windowAggStep end \"peer is numeric?\""));
        assert( pWin->eStart==TK_PRECEDING || pWin->eStart==TK_FOLLOWING );
        assert( pMWin->pOrderBy && pMWin->pOrderBy->nExpr==1 );
      }

      if( pWin->bExprArgs ){
        int iStart = sqlite3VdbeCurrentAddr(v);
        VdbeOp *pOp, *pEnd;

        nArg = pWin->pOwner->x.pList->nExpr;
        regArg = sqlite3GetTempRange(pParse, nArg);
        sqlite3ExprCodeExprList(pParse, pWin->pOwner->x.pList, regArg, 0, 0);
................................................................................
    }
  }

  if( op==WINDOW_RETURN_ROW && pMWin->regStartRowid==0 ){
    windowAggFinal(p, 0);
  }
  addrContinue = sqlite3VdbeCurrentAddr(v);


















  switch( op ){
    case WINDOW_RETURN_ROW:
      csr = p->current.csr;
      reg = p->current.reg;
      windowReturnOneRow(p);
      break;

................................................................................
    if( bPeer ){
      sqlite3VdbeAddOp2(v, OP_Goto, 0, lblDone);
    }
  }

  if( bPeer ){
    int nReg = (pMWin->pOrderBy ? pMWin->pOrderBy->nExpr : 0);
    int regTmp;

    /* If this is a (RANGE BETWEEN a FOLLOWING AND b FOLLOWING), ensure
    ** the start cursor does not advance past the end cursor within the
    ** temporary table. It otherwise might, if (a>b).  */
    if( pMWin->eStart==TK_FOLLOWING && pMWin->eEnd==TK_FOLLOWING
     && pMWin->eFrmType==TK_RANGE && op==WINDOW_AGGINVERSE
    ){
      int regRowid1 = sqlite3GetTempReg(pParse);
      int regRowid2 = sqlite3GetTempReg(pParse);
      sqlite3VdbeAddOp2(v, OP_Rowid, p->start.csr, regRowid1);
      sqlite3VdbeAddOp2(v, OP_Rowid, p->end.csr, regRowid2);
      sqlite3VdbeAddOp3(v, OP_Ge, regRowid2, lblDone, regRowid1);
      sqlite3ReleaseTempReg(pParse, regRowid1);
      sqlite3ReleaseTempReg(pParse, regRowid2);
    }
    regTmp = (nReg ? sqlite3GetTempRange(pParse, nReg) : 0);
    windowReadPeerValues(p, csr, regTmp);
    windowIfNewPeer(pParse, pMWin->pOrderBy, regTmp, reg, addrContinue);
    sqlite3ReleaseTempRange(pParse, regTmp, nReg);
  }

  if( addrNextRange ){
    sqlite3VdbeAddOp2(v, OP_Goto, 0, addrNextRange);
................................................................................
**         Rewind(csrEnd) ; Rewind(csrStart) ; Rewind(csrCurrent)
**         regEnd = <expr2>
**         regStart = <expr1>
**       }else{
**         while( (csrEnd.key + regEnd) <= csrCurrent.key ){
**           AGGSTEP
**         }
**         RETURN_ROW
**         while( (csrStart.key + regStart) < csrCurrent.key ){
**           AGGINVERSE
**         }

**       }
**     }
**     flush:
**       while( (csrEnd.key + regEnd) <= csrCurrent.key ){
**         AGGSTEP
**       }
**       RETURN_ROW
**       while( (csrStart.key + regStart) < csrCurrent.key ){
**         AGGINVERSE
**       }

**
**   RANGE BETWEEN <expr1> FOLLOWING AND <expr2> FOLLOWING
**
**     ... loop started by sqlite3WhereBegin() ...
**       if( new partition ){
**         Gosub flush
**       }
................................................................................
  int regRecord;                  /* regNew array in record form */
  int regRowid;                   /* Rowid for regRecord in eph table */
  int regNewPeer = 0;             /* Peer values for new row (part of regNew) */
  int regPeer = 0;                /* Peer values for current row */
  int regFlushPart = 0;           /* Register for "Gosub flush_partition" */
  WindowCodeArg s;                /* Context object for sub-routines */
  int lblWhereEnd;                /* Label just before sqlite3WhereEnd() code */



  assert( pMWin->eStart==TK_PRECEDING || pMWin->eStart==TK_CURRENT 
       || pMWin->eStart==TK_FOLLOWING || pMWin->eStart==TK_UNBOUNDED 
  );
  assert( pMWin->eEnd==TK_FOLLOWING || pMWin->eEnd==TK_CURRENT 
       || pMWin->eEnd==TK_UNBOUNDED || pMWin->eEnd==TK_PRECEDING 
  );
................................................................................
  regRecord = ++pParse->nMem;
  regRowid = ++pParse->nMem;

  /* If the window frame contains an "<expr> PRECEDING" or "<expr> FOLLOWING"
  ** clause, allocate registers to store the results of evaluating each
  ** <expr>.  */
  if( pMWin->eStart==TK_PRECEDING || pMWin->eStart==TK_FOLLOWING ){
    s.regStart = ++pParse->nMem;
  }
  if( pMWin->eEnd==TK_PRECEDING || pMWin->eEnd==TK_FOLLOWING ){
    s.regEnd = ++pParse->nMem;
  }

  /* If this is not a "ROWS BETWEEN ..." frame, then allocate arrays of
  ** registers to store copies of the ORDER BY expressions (peer values) 
  ** for the main loop, and for each cursor (start, current and end). */
  if( pMWin->eFrmType!=TK_ROWS ){
    int nPeer = (pOrderBy ? pOrderBy->nExpr : 0);
................................................................................
  sqlite3VdbeAddOp3(v, OP_Insert, csrWrite, regRecord, regRowid);
  addrNe = sqlite3VdbeAddOp3(v, OP_Ne, pMWin->regOne, 0, regRowid);
  VdbeCoverageNeverNull(v);

  /* This block is run for the first row of each partition */
  s.regArg = windowInitAccum(pParse, pMWin);

  if( s.regStart ){
    sqlite3ExprCode(pParse, pMWin->pStart, s.regStart);
    windowCheckValue(pParse, s.regStart, 0 + (pMWin->eFrmType==TK_RANGE?3:0));
  }
  if( s.regEnd ){
    sqlite3ExprCode(pParse, pMWin->pEnd, s.regEnd);
    windowCheckValue(pParse, s.regEnd, 1 + (pMWin->eFrmType==TK_RANGE?3:0));
  }

  if( pMWin->eFrmType!=TK_RANGE && pMWin->eStart==pMWin->eEnd && s.regStart ){
    int op = ((pMWin->eStart==TK_FOLLOWING) ? OP_Ge : OP_Le);
    int addrGe = sqlite3VdbeAddOp3(v, op, s.regStart, 0, s.regEnd);
    VdbeCoverageNeverNullIf(v, op==OP_Ge); /* NeverNull because bound <expr> */
    VdbeCoverageNeverNullIf(v, op==OP_Le); /*   values previously checked */
    windowAggFinal(&s, 0);
    sqlite3VdbeAddOp2(v, OP_Rewind, s.current.csr, 1);
    VdbeCoverageNeverTaken(v);
    windowReturnOneRow(&s);
    sqlite3VdbeAddOp1(v, OP_ResetSorter, s.current.csr);
    sqlite3VdbeAddOp2(v, OP_Goto, 0, lblWhereEnd);
    sqlite3VdbeJumpHere(v, addrGe);
  }
  if( pMWin->eStart==TK_FOLLOWING && pMWin->eFrmType!=TK_RANGE && s.regEnd ){
    assert( pMWin->eEnd==TK_FOLLOWING );
    sqlite3VdbeAddOp3(v, OP_Subtract, s.regStart, s.regEnd, s.regStart);
  }

  if( pMWin->eStart!=TK_UNBOUNDED ){
    sqlite3VdbeAddOp2(v, OP_Rewind, s.start.csr, 1);
    VdbeCoverageNeverTaken(v);
  }
  sqlite3VdbeAddOp2(v, OP_Rewind, s.current.csr, 1);
................................................................................
  }
  if( pMWin->eStart==TK_FOLLOWING ){
    windowCodeOp(&s, WINDOW_AGGSTEP, 0, 0);
    if( pMWin->eEnd!=TK_UNBOUNDED ){
      if( pMWin->eFrmType==TK_RANGE ){
        int lbl = sqlite3VdbeMakeLabel(pParse);
        int addrNext = sqlite3VdbeCurrentAddr(v);
        windowCodeRangeTest(&s, OP_Ge, s.current.csr, s.regEnd, s.end.csr, lbl);
        windowCodeOp(&s, WINDOW_AGGINVERSE, s.regStart, 0);
        windowCodeOp(&s, WINDOW_RETURN_ROW, 0, 0);
        sqlite3VdbeAddOp2(v, OP_Goto, 0, addrNext);
        sqlite3VdbeResolveLabel(v, lbl);
      }else{
        windowCodeOp(&s, WINDOW_RETURN_ROW, s.regEnd, 0);
        windowCodeOp(&s, WINDOW_AGGINVERSE, s.regStart, 0);
      }
    }
  }else
  if( pMWin->eEnd==TK_PRECEDING ){
    int bRPS = (pMWin->eStart==TK_PRECEDING && pMWin->eFrmType==TK_RANGE);
    windowCodeOp(&s, WINDOW_AGGSTEP, s.regEnd, 0);
    if( bRPS ) windowCodeOp(&s, WINDOW_AGGINVERSE, s.regStart, 0);
    windowCodeOp(&s, WINDOW_RETURN_ROW, 0, 0);
    if( !bRPS ) windowCodeOp(&s, WINDOW_AGGINVERSE, s.regStart, 0);
  }else{
    int addr = 0;
    windowCodeOp(&s, WINDOW_AGGSTEP, 0, 0);
    if( pMWin->eEnd!=TK_UNBOUNDED ){
      if( pMWin->eFrmType==TK_RANGE ){
        int lbl = 0;
        addr = sqlite3VdbeCurrentAddr(v);
        if( s.regEnd ){
          lbl = sqlite3VdbeMakeLabel(pParse);
          windowCodeRangeTest(&s, OP_Ge, s.current.csr,s.regEnd,s.end.csr,lbl);
        }
        windowCodeOp(&s, WINDOW_RETURN_ROW, 0, 0);
        windowCodeOp(&s, WINDOW_AGGINVERSE, s.regStart, 0);
        if( s.regEnd ){
          sqlite3VdbeAddOp2(v, OP_Goto, 0, addr);
          sqlite3VdbeResolveLabel(v, lbl);
        }
      }else{
        if( s.regEnd ){
          addr = sqlite3VdbeAddOp3(v, OP_IfPos, s.regEnd, 0, 1);
          VdbeCoverage(v);
        }
        windowCodeOp(&s, WINDOW_RETURN_ROW, 0, 0);
        windowCodeOp(&s, WINDOW_AGGINVERSE, s.regStart, 0);
        if( s.regEnd ) sqlite3VdbeJumpHere(v, addr);
      }
    }
  }

  /* End of the main input loop */
  sqlite3VdbeResolveLabel(v, lblWhereEnd);
  sqlite3WhereEnd(pWInfo);
................................................................................
    sqlite3VdbeJumpHere(v, addrGosubFlush);
  }

  addrEmpty = sqlite3VdbeAddOp1(v, OP_Rewind, csrWrite);
  VdbeCoverage(v);
  if( pMWin->eEnd==TK_PRECEDING ){
    int bRPS = (pMWin->eStart==TK_PRECEDING && pMWin->eFrmType==TK_RANGE);
    windowCodeOp(&s, WINDOW_AGGSTEP, s.regEnd, 0);
    if( bRPS ) windowCodeOp(&s, WINDOW_AGGINVERSE, s.regStart, 0);
    windowCodeOp(&s, WINDOW_RETURN_ROW, 0, 0);
  }else if( pMWin->eStart==TK_FOLLOWING ){
    int addrStart;
    int addrBreak1;
    int addrBreak2;
    int addrBreak3;
    windowCodeOp(&s, WINDOW_AGGSTEP, 0, 0);
    if( pMWin->eFrmType==TK_RANGE ){
      addrStart = sqlite3VdbeCurrentAddr(v);
      addrBreak2 = windowCodeOp(&s, WINDOW_AGGINVERSE, s.regStart, 1);
      addrBreak1 = windowCodeOp(&s, WINDOW_RETURN_ROW, 0, 1);
    }else
    if( pMWin->eEnd==TK_UNBOUNDED ){
      addrStart = sqlite3VdbeCurrentAddr(v);
      addrBreak1 = windowCodeOp(&s, WINDOW_RETURN_ROW, s.regStart, 1);
      addrBreak2 = windowCodeOp(&s, WINDOW_AGGINVERSE, 0, 1);
    }else{
      assert( pMWin->eEnd==TK_FOLLOWING );
      addrStart = sqlite3VdbeCurrentAddr(v);
      addrBreak1 = windowCodeOp(&s, WINDOW_RETURN_ROW, s.regEnd, 1);
      addrBreak2 = windowCodeOp(&s, WINDOW_AGGINVERSE, s.regStart, 1);
    }
    sqlite3VdbeAddOp2(v, OP_Goto, 0, addrStart);
    sqlite3VdbeJumpHere(v, addrBreak2);
    addrStart = sqlite3VdbeCurrentAddr(v);
    addrBreak3 = windowCodeOp(&s, WINDOW_RETURN_ROW, 0, 1);
    sqlite3VdbeAddOp2(v, OP_Goto, 0, addrStart);
    sqlite3VdbeJumpHere(v, addrBreak1);
................................................................................
    sqlite3VdbeJumpHere(v, addrBreak3);
  }else{
    int addrBreak;
    int addrStart;
    windowCodeOp(&s, WINDOW_AGGSTEP, 0, 0);
    addrStart = sqlite3VdbeCurrentAddr(v);
    addrBreak = windowCodeOp(&s, WINDOW_RETURN_ROW, 0, 1);
    windowCodeOp(&s, WINDOW_AGGINVERSE, s.regStart, 0);
    sqlite3VdbeAddOp2(v, OP_Goto, 0, addrStart);
    sqlite3VdbeJumpHere(v, addrBreak);
  }
  sqlite3VdbeJumpHere(v, addrEmpty);

  sqlite3VdbeAddOp1(v, OP_ResetSorter, s.current.csr);
  if( pMWin->pPartition ){







<
<







 







|
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
|







 







<



>






<



>







 







>
>







 







|


|







 







|
|
|

|
|
|


|

|










|

|







 







|
|




|
|





|
|

|







|

|


|
|




|
|



|
|







 







|
|









|




|




|
|







 







|







1478
1479
1480
1481
1482
1483
1484


1485
1486
1487
1488
1489
1490
1491
....
1593
1594
1595
1596
1597
1598
1599
1600































1601
1602
1603
1604
1605
1606
1607
....
2143
2144
2145
2146
2147
2148
2149
2150
2151
2152
2153
2154
2155
2156
2157
2158
2159
2160
2161
2162
2163
2164
2165
2166
2167
2168
2169
2170
2171
2172
2173
2174
....
2211
2212
2213
2214
2215
2216
2217
















2218
2219
2220
2221
2222
2223
2224
2225
....
2576
2577
2578
2579
2580
2581
2582

2583
2584
2585
2586
2587
2588
2589
2590
2591
2592

2593
2594
2595
2596
2597
2598
2599
2600
2601
2602
2603
....
2654
2655
2656
2657
2658
2659
2660
2661
2662
2663
2664
2665
2666
2667
2668
2669
....
2726
2727
2728
2729
2730
2731
2732
2733
2734
2735
2736
2737
2738
2739
2740
2741
2742
2743
....
2784
2785
2786
2787
2788
2789
2790
2791
2792
2793
2794
2795
2796
2797
2798
2799
2800
2801
2802
2803
2804
2805
2806
2807
2808
2809
2810
2811
2812
2813
2814
2815
2816
2817
2818
2819
2820
2821
2822
....
2840
2841
2842
2843
2844
2845
2846
2847
2848
2849
2850
2851
2852
2853
2854
2855
2856
2857
2858
2859
2860
2861
2862
2863
2864
2865
2866
2867
2868
2869
2870
2871
2872
2873
2874
2875
2876
2877
2878
2879
2880
2881
2882
2883
2884
2885
2886
2887
2888
2889
2890
2891
2892
2893
2894
2895
....
2900
2901
2902
2903
2904
2905
2906
2907
2908
2909
2910
2911
2912
2913
2914
2915
2916
2917
2918
2919
2920
2921
2922
2923
2924
2925
2926
2927
2928
2929
2930
2931
2932
2933
2934
2935
2936
....
2937
2938
2939
2940
2941
2942
2943
2944
2945
2946
2947
2948
2949
2950
2951
  Parse *pParse;             /* Parse context */
  Window *pMWin;             /* First in list of functions being processed */
  Vdbe *pVdbe;               /* VDBE object */
  int addrGosub;             /* OP_Gosub to this address to return one row */
  int regGosub;              /* Register used with OP_Gosub(addrGosub) */
  int regArg;                /* First in array of accumulator registers */
  int eDelete;               /* See above */



  WindowCsrAndReg start;
  WindowCsrAndReg current;
  WindowCsrAndReg end;
};

/*
................................................................................
        assert( pWin->bExprArgs || nArg  ||pWin->pOwner->x.pList==0 );
        regTmp = sqlite3GetTempReg(pParse);
        sqlite3VdbeAddOp3(v, OP_Column, csr, pWin->iArgCol+nArg,regTmp);
        addrIf = sqlite3VdbeAddOp3(v, OP_IfNot, regTmp, 0, 1);
        VdbeCoverage(v);
        sqlite3ReleaseTempReg(pParse, regTmp);
      }
      































      if( pWin->bExprArgs ){
        int iStart = sqlite3VdbeCurrentAddr(v);
        VdbeOp *pOp, *pEnd;

        nArg = pWin->pOwner->x.pList->nExpr;
        regArg = sqlite3GetTempRange(pParse, nArg);
        sqlite3ExprCodeExprList(pParse, pWin->pOwner->x.pList, regArg, 0, 0);
................................................................................
    }
  }

  if( op==WINDOW_RETURN_ROW && pMWin->regStartRowid==0 ){
    windowAggFinal(p, 0);
  }
  addrContinue = sqlite3VdbeCurrentAddr(v);

  /* If this is a (RANGE BETWEEN a FOLLOWING AND b FOLLOWING) or
  ** (RANGE BETWEEN b PRECEDING AND a PRECEDING) frame, ensure the 
  ** start cursor does not advance past the end cursor within the 
  ** temporary table. It otherwise might, if (a>b).  */
  if( pMWin->eStart==pMWin->eEnd && regCountdown
   && pMWin->eFrmType==TK_RANGE && op==WINDOW_AGGINVERSE
  ){
    int regRowid1 = sqlite3GetTempReg(pParse);
    int regRowid2 = sqlite3GetTempReg(pParse);
    sqlite3VdbeAddOp2(v, OP_Rowid, p->start.csr, regRowid1);
    sqlite3VdbeAddOp2(v, OP_Rowid, p->end.csr, regRowid2);
    sqlite3VdbeAddOp3(v, OP_Ge, regRowid2, lblDone, regRowid1);
    sqlite3ReleaseTempReg(pParse, regRowid1);
    sqlite3ReleaseTempReg(pParse, regRowid2);
    assert( pMWin->eStart==TK_PRECEDING || pMWin->eStart==TK_FOLLOWING );
  }

  switch( op ){
    case WINDOW_RETURN_ROW:
      csr = p->current.csr;
      reg = p->current.reg;
      windowReturnOneRow(p);
      break;

................................................................................
    if( bPeer ){
      sqlite3VdbeAddOp2(v, OP_Goto, 0, lblDone);
    }
  }

  if( bPeer ){
    int nReg = (pMWin->pOrderBy ? pMWin->pOrderBy->nExpr : 0);
















    int regTmp = (nReg ? sqlite3GetTempRange(pParse, nReg) : 0);
    windowReadPeerValues(p, csr, regTmp);
    windowIfNewPeer(pParse, pMWin->pOrderBy, regTmp, reg, addrContinue);
    sqlite3ReleaseTempRange(pParse, regTmp, nReg);
  }

  if( addrNextRange ){
    sqlite3VdbeAddOp2(v, OP_Goto, 0, addrNextRange);
................................................................................
**         Rewind(csrEnd) ; Rewind(csrStart) ; Rewind(csrCurrent)
**         regEnd = <expr2>
**         regStart = <expr1>
**       }else{
**         while( (csrEnd.key + regEnd) <= csrCurrent.key ){
**           AGGSTEP
**         }

**         while( (csrStart.key + regStart) < csrCurrent.key ){
**           AGGINVERSE
**         }
**         RETURN_ROW
**       }
**     }
**     flush:
**       while( (csrEnd.key + regEnd) <= csrCurrent.key ){
**         AGGSTEP
**       }

**       while( (csrStart.key + regStart) < csrCurrent.key ){
**         AGGINVERSE
**       }
**       RETURN_ROW
**
**   RANGE BETWEEN <expr1> FOLLOWING AND <expr2> FOLLOWING
**
**     ... loop started by sqlite3WhereBegin() ...
**       if( new partition ){
**         Gosub flush
**       }
................................................................................
  int regRecord;                  /* regNew array in record form */
  int regRowid;                   /* Rowid for regRecord in eph table */
  int regNewPeer = 0;             /* Peer values for new row (part of regNew) */
  int regPeer = 0;                /* Peer values for current row */
  int regFlushPart = 0;           /* Register for "Gosub flush_partition" */
  WindowCodeArg s;                /* Context object for sub-routines */
  int lblWhereEnd;                /* Label just before sqlite3WhereEnd() code */
  int regStart = 0;               /* Value of <expr> PRECEDING */
  int regEnd = 0;                 /* Value of <expr> FOLLOWING */

  assert( pMWin->eStart==TK_PRECEDING || pMWin->eStart==TK_CURRENT 
       || pMWin->eStart==TK_FOLLOWING || pMWin->eStart==TK_UNBOUNDED 
  );
  assert( pMWin->eEnd==TK_FOLLOWING || pMWin->eEnd==TK_CURRENT 
       || pMWin->eEnd==TK_UNBOUNDED || pMWin->eEnd==TK_PRECEDING 
  );
................................................................................
  regRecord = ++pParse->nMem;
  regRowid = ++pParse->nMem;

  /* If the window frame contains an "<expr> PRECEDING" or "<expr> FOLLOWING"
  ** clause, allocate registers to store the results of evaluating each
  ** <expr>.  */
  if( pMWin->eStart==TK_PRECEDING || pMWin->eStart==TK_FOLLOWING ){
    regStart = ++pParse->nMem;
  }
  if( pMWin->eEnd==TK_PRECEDING || pMWin->eEnd==TK_FOLLOWING ){
    regEnd = ++pParse->nMem;
  }

  /* If this is not a "ROWS BETWEEN ..." frame, then allocate arrays of
  ** registers to store copies of the ORDER BY expressions (peer values) 
  ** for the main loop, and for each cursor (start, current and end). */
  if( pMWin->eFrmType!=TK_ROWS ){
    int nPeer = (pOrderBy ? pOrderBy->nExpr : 0);
................................................................................
  sqlite3VdbeAddOp3(v, OP_Insert, csrWrite, regRecord, regRowid);
  addrNe = sqlite3VdbeAddOp3(v, OP_Ne, pMWin->regOne, 0, regRowid);
  VdbeCoverageNeverNull(v);

  /* This block is run for the first row of each partition */
  s.regArg = windowInitAccum(pParse, pMWin);

  if( regStart ){
    sqlite3ExprCode(pParse, pMWin->pStart, regStart);
    windowCheckValue(pParse, regStart, 0 + (pMWin->eFrmType==TK_RANGE?3:0));
  }
  if( regEnd ){
    sqlite3ExprCode(pParse, pMWin->pEnd, regEnd);
    windowCheckValue(pParse, regEnd, 1 + (pMWin->eFrmType==TK_RANGE?3:0));
  }

  if( pMWin->eFrmType!=TK_RANGE && pMWin->eStart==pMWin->eEnd && regStart ){
    int op = ((pMWin->eStart==TK_FOLLOWING) ? OP_Ge : OP_Le);
    int addrGe = sqlite3VdbeAddOp3(v, op, regStart, 0, regEnd);
    VdbeCoverageNeverNullIf(v, op==OP_Ge); /* NeverNull because bound <expr> */
    VdbeCoverageNeverNullIf(v, op==OP_Le); /*   values previously checked */
    windowAggFinal(&s, 0);
    sqlite3VdbeAddOp2(v, OP_Rewind, s.current.csr, 1);
    VdbeCoverageNeverTaken(v);
    windowReturnOneRow(&s);
    sqlite3VdbeAddOp1(v, OP_ResetSorter, s.current.csr);
    sqlite3VdbeAddOp2(v, OP_Goto, 0, lblWhereEnd);
    sqlite3VdbeJumpHere(v, addrGe);
  }
  if( pMWin->eStart==TK_FOLLOWING && pMWin->eFrmType!=TK_RANGE && regEnd ){
    assert( pMWin->eEnd==TK_FOLLOWING );
    sqlite3VdbeAddOp3(v, OP_Subtract, regStart, regEnd, regStart);
  }

  if( pMWin->eStart!=TK_UNBOUNDED ){
    sqlite3VdbeAddOp2(v, OP_Rewind, s.start.csr, 1);
    VdbeCoverageNeverTaken(v);
  }
  sqlite3VdbeAddOp2(v, OP_Rewind, s.current.csr, 1);
................................................................................
  }
  if( pMWin->eStart==TK_FOLLOWING ){
    windowCodeOp(&s, WINDOW_AGGSTEP, 0, 0);
    if( pMWin->eEnd!=TK_UNBOUNDED ){
      if( pMWin->eFrmType==TK_RANGE ){
        int lbl = sqlite3VdbeMakeLabel(pParse);
        int addrNext = sqlite3VdbeCurrentAddr(v);
        windowCodeRangeTest(&s, OP_Ge, s.current.csr, regEnd, s.end.csr, lbl);
        windowCodeOp(&s, WINDOW_AGGINVERSE, regStart, 0);
        windowCodeOp(&s, WINDOW_RETURN_ROW, 0, 0);
        sqlite3VdbeAddOp2(v, OP_Goto, 0, addrNext);
        sqlite3VdbeResolveLabel(v, lbl);
      }else{
        windowCodeOp(&s, WINDOW_RETURN_ROW, regEnd, 0);
        windowCodeOp(&s, WINDOW_AGGINVERSE, regStart, 0);
      }
    }
  }else
  if( pMWin->eEnd==TK_PRECEDING ){
    int bRPS = (pMWin->eStart==TK_PRECEDING && pMWin->eFrmType==TK_RANGE);
    windowCodeOp(&s, WINDOW_AGGSTEP, regEnd, 0);
    if( bRPS ) windowCodeOp(&s, WINDOW_AGGINVERSE, regStart, 0);
    windowCodeOp(&s, WINDOW_RETURN_ROW, 0, 0);
    if( !bRPS ) windowCodeOp(&s, WINDOW_AGGINVERSE, regStart, 0);
  }else{
    int addr = 0;
    windowCodeOp(&s, WINDOW_AGGSTEP, 0, 0);
    if( pMWin->eEnd!=TK_UNBOUNDED ){
      if( pMWin->eFrmType==TK_RANGE ){
        int lbl = 0;
        addr = sqlite3VdbeCurrentAddr(v);
        if( regEnd ){
          lbl = sqlite3VdbeMakeLabel(pParse);
          windowCodeRangeTest(&s, OP_Ge, s.current.csr, regEnd, s.end.csr, lbl);
        }
        windowCodeOp(&s, WINDOW_RETURN_ROW, 0, 0);
        windowCodeOp(&s, WINDOW_AGGINVERSE, regStart, 0);
        if( regEnd ){
          sqlite3VdbeAddOp2(v, OP_Goto, 0, addr);
          sqlite3VdbeResolveLabel(v, lbl);
        }
      }else{
        if( regEnd ){
          addr = sqlite3VdbeAddOp3(v, OP_IfPos, regEnd, 0, 1);
          VdbeCoverage(v);
        }
        windowCodeOp(&s, WINDOW_RETURN_ROW, 0, 0);
        windowCodeOp(&s, WINDOW_AGGINVERSE, regStart, 0);
        if( regEnd ) sqlite3VdbeJumpHere(v, addr);
      }
    }
  }

  /* End of the main input loop */
  sqlite3VdbeResolveLabel(v, lblWhereEnd);
  sqlite3WhereEnd(pWInfo);
................................................................................
    sqlite3VdbeJumpHere(v, addrGosubFlush);
  }

  addrEmpty = sqlite3VdbeAddOp1(v, OP_Rewind, csrWrite);
  VdbeCoverage(v);
  if( pMWin->eEnd==TK_PRECEDING ){
    int bRPS = (pMWin->eStart==TK_PRECEDING && pMWin->eFrmType==TK_RANGE);
    windowCodeOp(&s, WINDOW_AGGSTEP, regEnd, 0);
    if( bRPS ) windowCodeOp(&s, WINDOW_AGGINVERSE, regStart, 0);
    windowCodeOp(&s, WINDOW_RETURN_ROW, 0, 0);
  }else if( pMWin->eStart==TK_FOLLOWING ){
    int addrStart;
    int addrBreak1;
    int addrBreak2;
    int addrBreak3;
    windowCodeOp(&s, WINDOW_AGGSTEP, 0, 0);
    if( pMWin->eFrmType==TK_RANGE ){
      addrStart = sqlite3VdbeCurrentAddr(v);
      addrBreak2 = windowCodeOp(&s, WINDOW_AGGINVERSE, regStart, 1);
      addrBreak1 = windowCodeOp(&s, WINDOW_RETURN_ROW, 0, 1);
    }else
    if( pMWin->eEnd==TK_UNBOUNDED ){
      addrStart = sqlite3VdbeCurrentAddr(v);
      addrBreak1 = windowCodeOp(&s, WINDOW_RETURN_ROW, regStart, 1);
      addrBreak2 = windowCodeOp(&s, WINDOW_AGGINVERSE, 0, 1);
    }else{
      assert( pMWin->eEnd==TK_FOLLOWING );
      addrStart = sqlite3VdbeCurrentAddr(v);
      addrBreak1 = windowCodeOp(&s, WINDOW_RETURN_ROW, regEnd, 1);
      addrBreak2 = windowCodeOp(&s, WINDOW_AGGINVERSE, regStart, 1);
    }
    sqlite3VdbeAddOp2(v, OP_Goto, 0, addrStart);
    sqlite3VdbeJumpHere(v, addrBreak2);
    addrStart = sqlite3VdbeCurrentAddr(v);
    addrBreak3 = windowCodeOp(&s, WINDOW_RETURN_ROW, 0, 1);
    sqlite3VdbeAddOp2(v, OP_Goto, 0, addrStart);
    sqlite3VdbeJumpHere(v, addrBreak1);
................................................................................
    sqlite3VdbeJumpHere(v, addrBreak3);
  }else{
    int addrBreak;
    int addrStart;
    windowCodeOp(&s, WINDOW_AGGSTEP, 0, 0);
    addrStart = sqlite3VdbeCurrentAddr(v);
    addrBreak = windowCodeOp(&s, WINDOW_RETURN_ROW, 0, 1);
    windowCodeOp(&s, WINDOW_AGGINVERSE, regStart, 0);
    sqlite3VdbeAddOp2(v, OP_Goto, 0, addrStart);
    sqlite3VdbeJumpHere(v, addrBreak);
  }
  sqlite3VdbeJumpHere(v, addrEmpty);

  sqlite3VdbeAddOp1(v, OP_ResetSorter, s.current.csr);
  if( pMWin->pPartition ){

Changes to test/windowB.test.

292
293
294
295
296
297
298
299







































300
  ) FROM t1;
} {7 {} 8 {} abc 1001} 
do_execsql_test 6.2 {
  SELECT a, sum(c) OVER (
    ORDER BY a RANGE BETWEEN 2 FOLLOWING AND 0 FOLLOWING EXCLUDE NO OTHERS
  ) FROM t1;
} {7 {} 8 {} abc 1001} 








































finish_test








>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
  ) FROM t1;
} {7 {} 8 {} abc 1001} 
do_execsql_test 6.2 {
  SELECT a, sum(c) OVER (
    ORDER BY a RANGE BETWEEN 2 FOLLOWING AND 0 FOLLOWING EXCLUDE NO OTHERS
  ) FROM t1;
} {7 {} 8 {} abc 1001} 

#-------------------------------------------------------------------------
reset_db
do_execsql_test 7.0 {
  CREATE TABLE t1(a, c);
  CREATE INDEX i1 ON t1(a);

  INSERT INTO t1 VALUES(NULL, 46);
  INSERT INTO t1 VALUES(NULL, 45);
  INSERT INTO t1 VALUES(7,  997);
  INSERT INTO t1 VALUES(7,  1000);
  INSERT INTO t1 VALUES(8,  997);
  INSERT INTO t1 VALUES(8,  1000);
  INSERT INTO t1 VALUES('abc', 1001);
  INSERT INTO t1 VALUES('abc', 1004);
  INSERT INTO t1 VALUES('xyz', 3333);
}

do_execsql_test 7.1 {
  SELECT a, max(c) OVER (
    ORDER BY a RANGE BETWEEN 2 FOLLOWING AND 0 FOLLOWING
  ) FROM t1;
} {{} 46 {} 46  7 {} 7 {} 8 {} 8 {}  abc 1004 abc 1004 xyz 3333}
do_execsql_test 7.2 {
  SELECT a, min(c) OVER (
    ORDER BY a RANGE BETWEEN 2 FOLLOWING AND 0 FOLLOWING
  ) FROM t1;
} {{} 45 {} 45  7 {} 7 {} 8 {} 8 {}  abc 1001 abc 1001 xyz 3333}

do_execsql_test 7.3 {
  SELECT a, max(c) OVER (
    ORDER BY a RANGE BETWEEN 0 PRECEDING AND 2 PRECEDING
  ) FROM t1;
} {{} 46 {} 46  7 {} 7 {} 8 {} 8 {}  abc 1004 abc 1004 xyz 3333}
do_execsql_test 7.4 {
  SELECT a, min(c) OVER (
    ORDER BY a RANGE BETWEEN 0 PRECEDING AND 2 PRECEDING
  ) FROM t1;
} {{} 45 {} 45  7 {} 7 {} 8 {} 8 {}  abc 1001 abc 1001 xyz 3333}

finish_test