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: |
5fe15c1d8383989257e11d1806e6b035 |
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
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 |