Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Support some "ROWS BETWEEN N PRECEDING AND M FOLLOWING" window functions without caching entire partitions. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | window-functions |
| Files: | files | file ages | folders |
| SHA3-256: |
e7a91f12282afb5d5d7d78397a11d18e |
| User & Date: | dan 2019-03-04 21:07:11 |
| Wiki: | window-functions |
Context
|
2019-03-04
| ||
| 21:08 | Merge trunk changes into this branch. check-in: 9b4d561f user: dan tags: window-functions | |
| 21:07 | Support some "ROWS BETWEEN N PRECEDING AND M FOLLOWING" window functions without caching entire partitions. check-in: e7a91f12 user: dan tags: window-functions | |
|
2019-02-16
| ||
| 17:27 | Add support for chaining of WINDOW definitions. check-in: c155125f user: dan tags: window-functions | |
Changes
Changes to src/sqliteInt.h.
3573 3574 3575 3576 3577 3578 3579 3580 3581 3582 3583 3584 3585 3586 |
int csrApp; /* Function cursor (used by min/max) */
int regApp; /* Function register (also used by min/max) */
int regPart; /* First in a set of registers holding PARTITION BY
** and ORDER BY values for the window */
Expr *pOwner; /* Expression object this window is attached to */
int nBufferCol; /* Number of columns in buffer table */
int iArgCol; /* Offset of first argument for this function */
};
#ifndef SQLITE_OMIT_WINDOWFUNC
void sqlite3WindowDelete(sqlite3*, Window*);
void sqlite3WindowListDelete(sqlite3 *db, Window *p);
Window *sqlite3WindowAlloc(Parse*, int, int, Expr*, int , Expr*);
void sqlite3WindowAttach(Parse*, Expr*, Window*);
|
> > |
3573 3574 3575 3576 3577 3578 3579 3580 3581 3582 3583 3584 3585 3586 3587 3588 |
int csrApp; /* Function cursor (used by min/max) */
int regApp; /* Function register (also used by min/max) */
int regPart; /* First in a set of registers holding PARTITION BY
** and ORDER BY values for the window */
Expr *pOwner; /* Expression object this window is attached to */
int nBufferCol; /* Number of columns in buffer table */
int iArgCol; /* Offset of first argument for this function */
int regFirst;
};
#ifndef SQLITE_OMIT_WINDOWFUNC
void sqlite3WindowDelete(sqlite3*, Window*);
void sqlite3WindowListDelete(sqlite3 *db, Window *p);
Window *sqlite3WindowAlloc(Parse*, int, int, Expr*, int , Expr*);
void sqlite3WindowAttach(Parse*, Expr*, Window*);
|
Changes to src/window.c.
784 785 786 787 788 789 790 791 792 793 794 795 796 797 ... 839 840 841 842 843 844 845 846 847 848 849 850 851 852 .... 1082 1083 1084 1085 1086 1087 1088 1089 1090 1091 1092 1093 1094 1095 .... 1425 1426 1427 1428 1429 1430 1431 1432 1433 1434 1435 1436 1437 1438 1439 1440 1441 1442 .... 1829 1830 1831 1832 1833 1834 1835 1836 1837 1838 1839 1840 1841 1842 .... 2292 2293 2294 2295 2296 2297 2298 2299 2300 2301 2302 2303 2304 2305 2306 2307 .... 2319 2320 2321 2322 2323 2324 2325 2326 2327 2328 2329 2330 2331 2332 2333 |
}
}
/* Assign a cursor number for the ephemeral table used to buffer rows.
** The OpenEphemeral instruction is coded later, after it is known how
** many columns the table will have. */
pMWin->iEphCsr = pParse->nTab++;
selectWindowRewriteEList(pParse, pMWin, pSrc, p->pEList, &pSublist);
selectWindowRewriteEList(pParse, pMWin, pSrc, p->pOrderBy, &pSublist);
pMWin->nBufferCol = (pSublist ? pSublist->nExpr : 0);
/* Append the PARTITION BY and ORDER BY expressions to the to the
** sub-select expression list. They are required to figure out where
................................................................................
}else{
pSub->selFlags |= SF_Expanded;
p->selFlags &= ~SF_Aggregate;
sqlite3SelectPrep(pParse, pSub, 0);
}
sqlite3VdbeAddOp2(v, OP_OpenEphemeral, pMWin->iEphCsr, pSublist->nExpr);
}else{
sqlite3SelectDelete(db, pSub);
}
if( db->mallocFailed ) rc = SQLITE_NOMEM;
}
return rc;
................................................................................
nPart += (pMWin->pOrderBy ? pMWin->pOrderBy->nExpr : 0);
if( nPart ){
pMWin->regPart = pParse->nMem+1;
pParse->nMem += nPart;
sqlite3VdbeAddOp3(v, OP_Null, 0, pMWin->regPart, pMWin->regPart+nPart-1);
}
for(pWin=pMWin; pWin; pWin=pWin->pNextWin){
FuncDef *p = pWin->pFunc;
if( (p->funcFlags & SQLITE_FUNC_MINMAX) && pWin->eStart!=TK_UNBOUNDED ){
/* The inline versions of min() and max() require a single ephemeral
** table and 3 registers. The registers are used as follows:
**
** regApp+0: slot to copy min()/max() argument to for MakeRecord
................................................................................
VdbeCoverageNeverTaken(v);
sqlite3VdbeAddOp3(v, OP_Column, csr, pWin->iArgCol, pWin->regResult);
sqlite3VdbeResolveLabel(v, lbl);
sqlite3ReleaseTempReg(pParse, tmpReg);
}
else if( pFunc->zName==leadName || pFunc->zName==lagName ){
int nArg = pWin->pOwner->x.pList->nExpr;
int iEph = pMWin->iEphCsr;
int csr = pWin->csrApp;
int lbl = sqlite3VdbeMakeLabel(pParse);
int tmpReg = sqlite3GetTempReg(pParse);
if( nArg<3 ){
sqlite3VdbeAddOp2(v, OP_Null, 0, pWin->regResult);
}else{
sqlite3VdbeAddOp3(v, OP_Column, iEph, pWin->iArgCol+2, pWin->regResult);
}
sqlite3VdbeAddOp2(v, OP_Rowid, iEph, tmpReg);
................................................................................
sqlite3VdbeAddOp1(v, OP_ResetSorter, pMWin->iEphCsr);
sqlite3VdbeAddOp1(v, OP_Return, regFlushPart);
VdbeComment((v, "end flush_partition subroutine"));
/* Jump to here to skip over flush_partition */
sqlite3VdbeJumpHere(v, addrGoto);
}
/*
** This function does the work of sqlite3WindowCodeStep() for cases that
** would normally be handled by windowCodeDefaultStep() when there are
** one or more built-in window-functions that require the entire partition
** to be cached in a temp table before any rows can be returned. Additionally.
** "RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING" is always handled by
................................................................................
** windowCodeDefaultStep() is the only one of the three functions that
** does not cache each partition in a temp table before beginning to
** return rows.
*/
if( pMWin->eType==TK_ROWS
&& (pMWin->eStart!=TK_UNBOUNDED||pMWin->eEnd!=TK_CURRENT||!pMWin->pOrderBy)
){
VdbeModuleComment((pParse->pVdbe, "Begin RowExprStep()"));
windowCodeRowExprStep(pParse, p, pWInfo, regGosub, addrGosub);
}else{
Window *pWin;
int bCache = 0; /* True to use CacheStep() */
if( pMWin->eStart==TK_CURRENT && pMWin->eEnd==TK_UNBOUNDED ){
bCache = 1;
}else{
................................................................................
}
}
/* Otherwise, call windowCodeDefaultStep(). */
if( bCache ){
VdbeModuleComment((pParse->pVdbe, "Begin CacheStep()"));
windowCodeCacheStep(pParse, p, pWInfo, regGosub, addrGosub);
}else{
VdbeModuleComment((pParse->pVdbe, "Begin DefaultStep()"));
windowCodeDefaultStep(pParse, p, pWInfo, regGosub, addrGosub);
}
}
}
#endif /* SQLITE_OMIT_WINDOWFUNC */
|
> > > > > > > < > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | | > > > > > > > > |
784 785 786 787 788 789 790 791 792 793 794 795 796 797 798 ... 840 841 842 843 844 845 846 847 848 849 850 851 852 853 854 855 856 .... 1086 1087 1088 1089 1090 1091 1092 1093 1094 1095 1096 1097 1098 1099 1100 1101 1102 .... 1432 1433 1434 1435 1436 1437 1438 1439 1440 1441 1442 1443 1444 1445 1446 1447 1448 1449 .... 1836 1837 1838 1839 1840 1841 1842 1843 1844 1845 1846 1847 1848 1849 1850 1851 1852 1853 1854 1855 1856 1857 1858 1859 1860 1861 1862 1863 1864 1865 1866 1867 1868 1869 1870 1871 1872 1873 1874 1875 1876 1877 1878 1879 1880 1881 1882 1883 1884 1885 1886 1887 1888 1889 1890 1891 1892 1893 1894 1895 1896 1897 1898 1899 1900 1901 1902 1903 1904 1905 1906 1907 1908 1909 1910 1911 1912 1913 1914 1915 1916 1917 1918 1919 1920 1921 1922 1923 1924 1925 1926 1927 1928 1929 1930 1931 1932 1933 1934 1935 1936 1937 1938 1939 1940 1941 1942 1943 1944 1945 1946 1947 1948 1949 1950 1951 1952 1953 1954 1955 1956 1957 1958 1959 1960 1961 1962 1963 1964 1965 1966 1967 1968 1969 1970 1971 1972 1973 1974 1975 1976 1977 1978 1979 1980 1981 1982 1983 1984 1985 1986 1987 1988 1989 1990 1991 1992 1993 1994 1995 1996 1997 .... 2447 2448 2449 2450 2451 2452 2453 2454 2455 2456 2457 2458 2459 2460 2461 2462 2463 2464 2465 2466 2467 2468 2469 2470 2471 2472 2473 2474 2475 2476 2477 2478 2479 2480 2481 2482 2483 .... 2495 2496 2497 2498 2499 2500 2501 2502 2503 2504 2505 2506 2507 2508 2509 2510 2511 |
}
}
/* Assign a cursor number for the ephemeral table used to buffer rows.
** The OpenEphemeral instruction is coded later, after it is known how
** many columns the table will have. */
pMWin->iEphCsr = pParse->nTab++;
pParse->nTab += 3;
selectWindowRewriteEList(pParse, pMWin, pSrc, p->pEList, &pSublist);
selectWindowRewriteEList(pParse, pMWin, pSrc, p->pOrderBy, &pSublist);
pMWin->nBufferCol = (pSublist ? pSublist->nExpr : 0);
/* Append the PARTITION BY and ORDER BY expressions to the to the
** sub-select expression list. They are required to figure out where
................................................................................
}else{
pSub->selFlags |= SF_Expanded;
p->selFlags &= ~SF_Aggregate;
sqlite3SelectPrep(pParse, pSub, 0);
}
sqlite3VdbeAddOp2(v, OP_OpenEphemeral, pMWin->iEphCsr, pSublist->nExpr);
sqlite3VdbeAddOp2(v, OP_OpenDup, pMWin->iEphCsr+1, pMWin->iEphCsr);
sqlite3VdbeAddOp2(v, OP_OpenDup, pMWin->iEphCsr+2, pMWin->iEphCsr);
sqlite3VdbeAddOp2(v, OP_OpenDup, pMWin->iEphCsr+3, pMWin->iEphCsr);
}else{
sqlite3SelectDelete(db, pSub);
}
if( db->mallocFailed ) rc = SQLITE_NOMEM;
}
return rc;
................................................................................
nPart += (pMWin->pOrderBy ? pMWin->pOrderBy->nExpr : 0);
if( nPart ){
pMWin->regPart = pParse->nMem+1;
pParse->nMem += nPart;
sqlite3VdbeAddOp3(v, OP_Null, 0, pMWin->regPart, pMWin->regPart+nPart-1);
}
pMWin->regFirst = ++pParse->nMem;
sqlite3VdbeAddOp2(v, OP_Integer, 1, pMWin->regFirst);
for(pWin=pMWin; pWin; pWin=pWin->pNextWin){
FuncDef *p = pWin->pFunc;
if( (p->funcFlags & SQLITE_FUNC_MINMAX) && pWin->eStart!=TK_UNBOUNDED ){
/* The inline versions of min() and max() require a single ephemeral
** table and 3 registers. The registers are used as follows:
**
** regApp+0: slot to copy min()/max() argument to for MakeRecord
................................................................................
VdbeCoverageNeverTaken(v);
sqlite3VdbeAddOp3(v, OP_Column, csr, pWin->iArgCol, pWin->regResult);
sqlite3VdbeResolveLabel(v, lbl);
sqlite3ReleaseTempReg(pParse, tmpReg);
}
else if( pFunc->zName==leadName || pFunc->zName==lagName ){
int nArg = pWin->pOwner->x.pList->nExpr;
int csr = pWin->csrApp;
int lbl = sqlite3VdbeMakeLabel(pParse);
int tmpReg = sqlite3GetTempReg(pParse);
int iEph = pMWin->iEphCsr;
if( nArg<3 ){
sqlite3VdbeAddOp2(v, OP_Null, 0, pWin->regResult);
}else{
sqlite3VdbeAddOp3(v, OP_Column, iEph, pWin->iArgCol+2, pWin->regResult);
}
sqlite3VdbeAddOp2(v, OP_Rowid, iEph, tmpReg);
................................................................................
sqlite3VdbeAddOp1(v, OP_ResetSorter, pMWin->iEphCsr);
sqlite3VdbeAddOp1(v, OP_Return, regFlushPart);
VdbeComment((v, "end flush_partition subroutine"));
/* Jump to here to skip over flush_partition */
sqlite3VdbeJumpHere(v, addrGoto);
}
static void windowCodeStep(
Parse *pParse,
Select *p,
WhereInfo *pWInfo,
int regGosub,
int addrGosub
){
Window *pMWin = p->pWin;
Vdbe *v = sqlite3GetVdbe(pParse);
int regFlushPart; /* Register for "Gosub flush_partition" */
int regArg;
int csrCurrent = pMWin->iEphCsr;
int csrWrite = csrCurrent+1;
int csrStart = csrCurrent+2;
int csrEnd = csrCurrent+3;
int regStart; /* Value of <expr> PRECEDING */
int regEnd; /* Value of <expr> FOLLOWING */
int iSubCsr = p->pSrc->a[0].iCursor;
int nSub = p->pSrc->a[0].pTab->nCol;
int k;
int addrGoto;
int addrIf;
int addrIfEnd;
int addrIfStart;
int addrGosubFlush;
int addrInteger;
int addrGoto2;
int reg = pParse->nMem+1;
int regRecord = reg+nSub;
int regRowid = regRecord+1;
pParse->nMem += 1 + nSub + 1;
regFlushPart = ++pParse->nMem;
regStart = ++pParse->nMem;
regEnd = ++pParse->nMem;
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
);
/* Load the column values for the row returned by the sub-select
** into an array of registers starting at reg. Assemble them into
** a record in register regRecord. TODO: An optimization here? */
for(k=0; k<nSub; k++){
sqlite3VdbeAddOp3(v, OP_Column, iSubCsr, k, reg+k);
}
sqlite3VdbeAddOp3(v, OP_MakeRecord, reg, nSub, regRecord);
/* Check if the current iteration is the first row of a new partition */
if( pMWin->pPartition ){
int addr;
ExprList *pPart = pMWin->pPartition;
int nPart = pPart->nExpr;
int regNewPart = reg + pMWin->nBufferCol;
KeyInfo *pKeyInfo = sqlite3KeyInfoFromExprList(pParse, pPart, 0, 0);
addrIf = sqlite3VdbeAddOp1(v, OP_If, pMWin->regFirst);
addr = sqlite3VdbeAddOp3(v, OP_Compare, regNewPart, pMWin->regPart, nPart);
sqlite3VdbeAppendP4(v, (void*)pKeyInfo, P4_KEYINFO);
sqlite3VdbeAddOp3(v, OP_Jump, addr+2, addr+3, addr+2);
VdbeCoverageEqNe(v);
addrGosubFlush = sqlite3VdbeAddOp1(v, OP_Gosub, regFlushPart);
VdbeComment((v, "call flush_partition"));
sqlite3VdbeJumpHere(v, addrIf);
}
/* Insert the new row into the ephemeral table */
sqlite3VdbeAddOp2(v, OP_NewRowid, csrWrite, regRowid);
sqlite3VdbeAddOp3(v, OP_Insert, csrWrite, regRecord, regRowid);
/* This block is run for the first row of each partition */
addrIf = sqlite3VdbeAddOp1(v, OP_IfNot, pMWin->regFirst);
if( pMWin->pPartition ){
sqlite3VdbeAddOp3(v, OP_Copy,
reg+pMWin->nBufferCol, pMWin->regPart, pMWin->pPartition->nExpr-1
);
}
sqlite3VdbeAddOp2(v, OP_Rewind, csrStart, 1); sqlite3VdbeChangeP5(v, 1);
sqlite3VdbeAddOp2(v, OP_Rewind, csrCurrent, 1); sqlite3VdbeChangeP5(v, 1);
sqlite3VdbeAddOp2(v, OP_Rewind, csrEnd, 1);
regArg = windowInitAccum(pParse, pMWin);
sqlite3VdbeAddOp2(v, OP_Integer, 0, pMWin->regFirst);
sqlite3ExprCode(pParse, pMWin->pStart, regStart);
windowCheckIntValue(pParse, regStart, 0);
sqlite3ExprCode(pParse, pMWin->pEnd, regEnd);
windowCheckIntValue(pParse, regEnd, 1);
addrGoto = sqlite3VdbeAddOp0(v, OP_Goto);
/* This block is run for the second and subsequent rows of each partition */
sqlite3VdbeJumpHere(v, addrIf);
sqlite3VdbeAddOp2(v, OP_Next, csrEnd, sqlite3VdbeCurrentAddr(v)+1);
addrIfEnd = sqlite3VdbeAddOp3(v, OP_IfPos, regEnd, 0, 1);
windowAggFinal(pParse, pMWin, 0);
sqlite3VdbeAddOp2(v, OP_Next, csrCurrent, sqlite3VdbeCurrentAddr(v)+1);
windowReturnOneRow(pParse, pMWin, regGosub, addrGosub);
addrIfStart = sqlite3VdbeAddOp3(v, OP_IfPos, regStart, 0, 1);
sqlite3VdbeAddOp2(v, OP_Next, csrStart, sqlite3VdbeCurrentAddr(v)+1);
windowAggStep(pParse, pMWin, csrStart, 1, regArg, 0);
sqlite3VdbeJumpHere(v, addrIfStart);
sqlite3VdbeJumpHere(v, addrIfEnd);
sqlite3VdbeJumpHere(v, addrGoto);
windowAggStep(pParse, pMWin, csrEnd, 0, regArg, 0);
/* End of the main input loop */
sqlite3WhereEnd(pWInfo);
/* Fall through */
if( pMWin->pPartition ){
addrInteger = sqlite3VdbeAddOp2(v, OP_Integer, 0, regFlushPart);
sqlite3VdbeJumpHere(v, addrGosubFlush);
}
sqlite3VdbeAddOp2(v, OP_Next, csrCurrent, sqlite3VdbeCurrentAddr(v)+2);
addrGoto = sqlite3VdbeAddOp0(v, OP_Goto);
windowAggFinal(pParse, pMWin, 0);
windowReturnOneRow(pParse, pMWin, regGosub, addrGosub);
addrIfStart = sqlite3VdbeAddOp3(v, OP_IfPos, regStart, 0, 1);
sqlite3VdbeAddOp2(v, OP_Next, csrStart, sqlite3VdbeCurrentAddr(v)+2);
addrGoto2 = sqlite3VdbeAddOp0(v, OP_Goto);
windowAggStep(pParse, pMWin, csrStart, 1, regArg, 0);
sqlite3VdbeJumpHere(v, addrIfStart);
sqlite3VdbeAddOp2(v, OP_Goto, 0, addrGoto-1);
sqlite3VdbeJumpHere(v, addrGoto);
sqlite3VdbeJumpHere(v, addrGoto2);
sqlite3VdbeAddOp1(v, OP_ResetSorter, csrCurrent);
sqlite3VdbeAddOp2(v, OP_Integer, 1, pMWin->regFirst);
if( pMWin->pPartition ){
sqlite3VdbeChangeP1(v, addrInteger, sqlite3VdbeCurrentAddr(v));
sqlite3VdbeAddOp1(v, OP_Return, regFlushPart);
}
}
/*
** This function does the work of sqlite3WindowCodeStep() for cases that
** would normally be handled by windowCodeDefaultStep() when there are
** one or more built-in window-functions that require the entire partition
** to be cached in a temp table before any rows can be returned. Additionally.
** "RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING" is always handled by
................................................................................
** windowCodeDefaultStep() is the only one of the three functions that
** does not cache each partition in a temp table before beginning to
** return rows.
*/
if( pMWin->eType==TK_ROWS
&& (pMWin->eStart!=TK_UNBOUNDED||pMWin->eEnd!=TK_CURRENT||!pMWin->pOrderBy)
){
Window *pWin;
int bCache = 0; /* True to use CacheStep() */
for(pWin=pMWin; pWin; pWin=pWin->pNextWin){
FuncDef *pFunc = pWin->pFunc;
if( (pFunc->funcFlags & SQLITE_FUNC_WINDOW_SIZE)
|| (pFunc->zName==nth_valueName)
|| (pFunc->zName==first_valueName)
|| (pFunc->zName==leadName)
|| (pFunc->zName==lagName)
){
bCache = 1;
break;
}
}
if( bCache || pMWin->eStart!=TK_PRECEDING || pMWin->eEnd!=TK_FOLLOWING ){
VdbeModuleComment((pParse->pVdbe, "Begin RowExprStep()"));
windowCodeRowExprStep(pParse, p, pWInfo, regGosub, addrGosub);
VdbeModuleComment((pParse->pVdbe, "End RowExprStep()"));
}else{
VdbeModuleComment((pParse->pVdbe, "Begin windowCodeStep()"));
windowCodeStep(pParse, p, pWInfo, regGosub, addrGosub);
VdbeModuleComment((pParse->pVdbe, "End windowCodeStep()"));
}
}else{
Window *pWin;
int bCache = 0; /* True to use CacheStep() */
if( pMWin->eStart==TK_CURRENT && pMWin->eEnd==TK_UNBOUNDED ){
bCache = 1;
}else{
................................................................................
}
}
/* Otherwise, call windowCodeDefaultStep(). */
if( bCache ){
VdbeModuleComment((pParse->pVdbe, "Begin CacheStep()"));
windowCodeCacheStep(pParse, p, pWInfo, regGosub, addrGosub);
VdbeModuleComment((pParse->pVdbe, "End CacheStep()"));
}else{
VdbeModuleComment((pParse->pVdbe, "Begin DefaultStep()"));
windowCodeDefaultStep(pParse, p, pWInfo, regGosub, addrGosub);
VdbeModuleComment((pParse->pVdbe, "End DefaultStep()"));
}
}
}
#endif /* SQLITE_OMIT_WINDOWFUNC */
|
Changes to test/pg_common.tcl.
62 63 64 65 66 67 68 69 70 71 72 73 74 75 |
set res [execsql $sql]
set sql [string map {string_agg group_concat} $sql]
puts $::fd "do_execsql_test $tn {"
puts $::fd " [string trim $sql]"
puts $::fd "} {$res}"
puts $::fd ""
}
# Same as [execsql_test], except coerce all results to floating point values
# with two decimal points.
#
proc execsql_float_test {tn sql} {
set F "%.4f"
set T 0.0001
|
> > > > > > > > > > > > > |
62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 |
set res [execsql $sql]
set sql [string map {string_agg group_concat} $sql]
puts $::fd "do_execsql_test $tn {"
puts $::fd " [string trim $sql]"
puts $::fd "} {$res}"
puts $::fd ""
}
proc errorsql_test {tn sql} {
set rc [catch {execsql $sql} msg]
if {$rc==0} {
error "errorsql_test SQL did not cause an error!"
}
puts $::fd "# PG says \"[string trim $msg]\""
set sql [string map {string_agg group_concat} $sql]
puts $::fd "do_test $tn { catch { execsql {"
puts $::fd " [string trim $sql]"
puts $::fd "} } } 1"
puts $::fd ""
}
# Same as [execsql_test], except coerce all results to floating point values
# with two decimal points.
#
proc execsql_float_test {tn sql} {
set F "%.4f"
set T 0.0001
|
Added test/window7.tcl.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 |
# 2018 May 19 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # source [file join [file dirname $argv0] pg_common.tcl] #========================================================================= start_test window7 "2019 March 01" ifcapable !windowfunc execsql_test 1.0 { DROP TABLE IF EXISTS t3; CREATE TABLE t3(a INTEGER, b INTEGER); INSERT INTO t3 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5), (6, 6), (7, 7), (8, 8), (9, 9), (0, 10), (1, 11), (2, 12), (3, 13), (4, 14), (5, 15), (6, 16), (7, 17), (8, 18), (9, 19), (0, 20), (1, 21), (2, 22), (3, 23), (4, 24), (5, 25), (6, 26), (7, 27), (8, 28), (9, 29), (0, 30), (1, 31), (2, 32), (3, 33), (4, 34), (5, 35), (6, 36), (7, 37), (8, 38), (9, 39), (0, 40), (1, 41), (2, 42), (3, 43), (4, 44), (5, 45), (6, 46), (7, 47), (8, 48), (9, 49), (0, 50), (1, 51), (2, 52), (3, 53), (4, 54), (5, 55), (6, 56), (7, 57), (8, 58), (9, 59), (0, 60), (1, 61), (2, 62), (3, 63), (4, 64), (5, 65), (6, 66), (7, 67), (8, 68), (9, 69), (0, 70), (1, 71), (2, 72), (3, 73), (4, 74), (5, 75), (6, 76), (7, 77), (8, 78), (9, 79), (0, 80), (1, 81), (2, 82), (3, 83), (4, 84), (5, 85), (6, 86), (7, 87), (8, 88), (9, 89), (0, 90), (1, 91), (2, 92), (3, 93), (4, 94), (5, 95), (6, 96), (7, 97), (8, 98), (9, 99), (0, 100); } execsql_test 1.1 { SELECT a, sum(b) FROM t3 GROUP BY a ORDER BY 1; } execsql_test 1.2 { SELECT a, sum(b) OVER ( ORDER BY a GROUPS BETWEEN CURRENT ROW AND CURRENT ROW ) FROM t3 ORDER BY 1; } execsql_test 1.3 { SELECT a, sum(b) OVER ( ORDER BY a GROUPS BETWEEN 0 PRECEDING AND 0 FOLLOWING ) FROM t3 ORDER BY 1; } execsql_test 1.4 { SELECT a, sum(b) OVER ( ORDER BY a GROUPS BETWEEN 2 PRECEDING AND 2 FOLLOWING ) FROM t3 ORDER BY 1; } execsql_test 1.5 { SELECT a, sum(b) OVER ( ORDER BY a RANGE BETWEEN 0 PRECEDING AND 0 FOLLOWING ) FROM t3 ORDER BY 1; } execsql_test 1.6 { SELECT a, sum(b) OVER ( ORDER BY a RANGE BETWEEN 2 PRECEDING AND 2 FOLLOWING ) FROM t3 ORDER BY 1; } execsql_test 1.7 { SELECT a, sum(b) OVER ( ORDER BY a RANGE BETWEEN 2 PRECEDING AND 1 FOLLOWING ) FROM t3 ORDER BY 1; } execsql_test 1.8.1 { SELECT a, sum(b) OVER ( ORDER BY a RANGE BETWEEN 0 PRECEDING AND 1 FOLLOWING ) FROM t3 ORDER BY 1; } execsql_test 1.8.2 { SELECT a, sum(b) OVER ( ORDER BY a DESC RANGE BETWEEN 0 PRECEDING AND 1 FOLLOWING ) FROM t3 ORDER BY 1; } finish_test |
Added test/window7.test.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 |
# 2019 March 01 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. # #################################################### # DO NOT EDIT! THIS FILE IS AUTOMATICALLY GENERATED! #################################################### set testdir [file dirname $argv0] source $testdir/tester.tcl set testprefix window7 ifcapable !windowfunc { finish_test ; return } do_execsql_test 1.0 { DROP TABLE IF EXISTS t3; CREATE TABLE t3(a INTEGER, b INTEGER); INSERT INTO t3 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5), (6, 6), (7, 7), (8, 8), (9, 9), (0, 10), (1, 11), (2, 12), (3, 13), (4, 14), (5, 15), (6, 16), (7, 17), (8, 18), (9, 19), (0, 20), (1, 21), (2, 22), (3, 23), (4, 24), (5, 25), (6, 26), (7, 27), (8, 28), (9, 29), (0, 30), (1, 31), (2, 32), (3, 33), (4, 34), (5, 35), (6, 36), (7, 37), (8, 38), (9, 39), (0, 40), (1, 41), (2, 42), (3, 43), (4, 44), (5, 45), (6, 46), (7, 47), (8, 48), (9, 49), (0, 50), (1, 51), (2, 52), (3, 53), (4, 54), (5, 55), (6, 56), (7, 57), (8, 58), (9, 59), (0, 60), (1, 61), (2, 62), (3, 63), (4, 64), (5, 65), (6, 66), (7, 67), (8, 68), (9, 69), (0, 70), (1, 71), (2, 72), (3, 73), (4, 74), (5, 75), (6, 76), (7, 77), (8, 78), (9, 79), (0, 80), (1, 81), (2, 82), (3, 83), (4, 84), (5, 85), (6, 86), (7, 87), (8, 88), (9, 89), (0, 90), (1, 91), (2, 92), (3, 93), (4, 94), (5, 95), (6, 96), (7, 97), (8, 98), (9, 99), (0, 100); } {} do_execsql_test 1.1 { SELECT a, sum(b) FROM t3 GROUP BY a ORDER BY 1; } {0 550 1 460 2 470 3 480 4 490 5 500 6 510 7 520 8 530 9 540} do_execsql_test 1.2 { SELECT a, sum(b) OVER ( ORDER BY a GROUPS BETWEEN CURRENT ROW AND CURRENT ROW ) FROM t3 ORDER BY 1; } {0 550 0 550 0 550 0 550 0 550 0 550 0 550 0 550 0 550 0 550 1 460 1 460 1 460 1 460 1 460 1 460 1 460 1 460 1 460 1 460 2 470 2 470 2 470 2 470 2 470 2 470 2 470 2 470 2 470 2 470 3 480 3 480 3 480 3 480 3 480 3 480 3 480 3 480 3 480 3 480 4 490 4 490 4 490 4 490 4 490 4 490 4 490 4 490 4 490 4 490 5 500 5 500 5 500 5 500 5 500 5 500 5 500 5 500 5 500 5 500 6 510 6 510 6 510 6 510 6 510 6 510 6 510 6 510 6 510 6 510 7 520 7 520 7 520 7 520 7 520 7 520 7 520 7 520 7 520 7 520 8 530 8 530 8 530 8 530 8 530 8 530 8 530 8 530 8 530 8 530 9 540 9 540 9 540 9 540 9 540 9 540 9 540 9 540 9 540 9 540} do_execsql_test 1.3 { SELECT a, sum(b) OVER ( ORDER BY a GROUPS BETWEEN 0 PRECEDING AND 0 FOLLOWING ) FROM t3 ORDER BY 1; } {0 550 0 550 0 550 0 550 0 550 0 550 0 550 0 550 0 550 0 550 1 460 1 460 1 460 1 460 1 460 1 460 1 460 1 460 1 460 1 460 2 470 2 470 2 470 2 470 2 470 2 470 2 470 2 470 2 470 2 470 3 480 3 480 3 480 3 480 3 480 3 480 3 480 3 480 3 480 3 480 4 490 4 490 4 490 4 490 4 490 4 490 4 490 4 490 4 490 4 490 5 500 5 500 5 500 5 500 5 500 5 500 5 500 5 500 5 500 5 500 6 510 6 510 6 510 6 510 6 510 6 510 6 510 6 510 6 510 6 510 7 520 7 520 7 520 7 520 7 520 7 520 7 520 7 520 7 520 7 520 8 530 8 530 8 530 8 530 8 530 8 530 8 530 8 530 8 530 8 530 9 540 9 540 9 540 9 540 9 540 9 540 9 540 9 540 9 540 9 540} do_execsql_test 1.4 { SELECT a, sum(b) OVER ( ORDER BY a GROUPS BETWEEN 2 PRECEDING AND 2 FOLLOWING ) FROM t3 ORDER BY 1; } {0 1480 0 1480 0 1480 0 1480 0 1480 0 1480 0 1480 0 1480 0 1480 0 1480 1 1960 1 1960 1 1960 1 1960 1 1960 1 1960 1 1960 1 1960 1 1960 1 1960 2 2450 2 2450 2 2450 2 2450 2 2450 2 2450 2 2450 2 2450 2 2450 2 2450 3 2400 3 2400 3 2400 3 2400 3 2400 3 2400 3 2400 3 2400 3 2400 3 2400 4 2450 4 2450 4 2450 4 2450 4 2450 4 2450 4 2450 4 2450 4 2450 4 2450 5 2500 5 2500 5 2500 5 2500 5 2500 5 2500 5 2500 5 2500 5 2500 5 2500 6 2550 6 2550 6 2550 6 2550 6 2550 6 2550 6 2550 6 2550 6 2550 6 2550 7 2600 7 2600 7 2600 7 2600 7 2600 7 2600 7 2600 7 2600 7 2600 7 2600 8 2100 8 2100 8 2100 8 2100 8 2100 8 2100 8 2100 8 2100 8 2100 8 2100 9 1590 9 1590 9 1590 9 1590 9 1590 9 1590 9 1590 9 1590 9 1590 9 1590} do_execsql_test 1.5 { SELECT a, sum(b) OVER ( ORDER BY a RANGE BETWEEN 0 PRECEDING AND 0 FOLLOWING ) FROM t3 ORDER BY 1; } {0 550 0 550 0 550 0 550 0 550 0 550 0 550 0 550 0 550 0 550 1 460 1 460 1 460 1 460 1 460 1 460 1 460 1 460 1 460 1 460 2 470 2 470 2 470 2 470 2 470 2 470 2 470 2 470 2 470 2 470 3 480 3 480 3 480 3 480 3 480 3 480 3 480 3 480 3 480 3 480 4 490 4 490 4 490 4 490 4 490 4 490 4 490 4 490 4 490 4 490 5 500 5 500 5 500 5 500 5 500 5 500 5 500 5 500 5 500 5 500 6 510 6 510 6 510 6 510 6 510 6 510 6 510 6 510 6 510 6 510 7 520 7 520 7 520 7 520 7 520 7 520 7 520 7 520 7 520 7 520 8 530 8 530 8 530 8 530 8 530 8 530 8 530 8 530 8 530 8 530 9 540 9 540 9 540 9 540 9 540 9 540 9 540 9 540 9 540 9 540} do_execsql_test 1.6 { SELECT a, sum(b) OVER ( ORDER BY a RANGE BETWEEN 2 PRECEDING AND 2 FOLLOWING ) FROM t3 ORDER BY 1; } {0 1480 0 1480 0 1480 0 1480 0 1480 0 1480 0 1480 0 1480 0 1480 0 1480 1 1960 1 1960 1 1960 1 1960 1 1960 1 1960 1 1960 1 1960 1 1960 1 1960 2 2450 2 2450 2 2450 2 2450 2 2450 2 2450 2 2450 2 2450 2 2450 2 2450 3 2400 3 2400 3 2400 3 2400 3 2400 3 2400 3 2400 3 2400 3 2400 3 2400 4 2450 4 2450 4 2450 4 2450 4 2450 4 2450 4 2450 4 2450 4 2450 4 2450 5 2500 5 2500 5 2500 5 2500 5 2500 5 2500 5 2500 5 2500 5 2500 5 2500 6 2550 6 2550 6 2550 6 2550 6 2550 6 2550 6 2550 6 2550 6 2550 6 2550 7 2600 7 2600 7 2600 7 2600 7 2600 7 2600 7 2600 7 2600 7 2600 7 2600 8 2100 8 2100 8 2100 8 2100 8 2100 8 2100 8 2100 8 2100 8 2100 8 2100 9 1590 9 1590 9 1590 9 1590 9 1590 9 1590 9 1590 9 1590 9 1590 9 1590} do_execsql_test 1.7 { SELECT a, sum(b) OVER ( ORDER BY a RANGE BETWEEN 2 PRECEDING AND 1 FOLLOWING ) FROM t3 ORDER BY 1; } {0 1010 0 1010 0 1010 0 1010 0 1010 0 1010 0 1010 0 1010 0 1010 0 1010 1 1480 1 1480 1 1480 1 1480 1 1480 1 1480 1 1480 1 1480 1 1480 1 1480 2 1960 2 1960 2 1960 2 1960 2 1960 2 1960 2 1960 2 1960 2 1960 2 1960 3 1900 3 1900 3 1900 3 1900 3 1900 3 1900 3 1900 3 1900 3 1900 3 1900 4 1940 4 1940 4 1940 4 1940 4 1940 4 1940 4 1940 4 1940 4 1940 4 1940 5 1980 5 1980 5 1980 5 1980 5 1980 5 1980 5 1980 5 1980 5 1980 5 1980 6 2020 6 2020 6 2020 6 2020 6 2020 6 2020 6 2020 6 2020 6 2020 6 2020 7 2060 7 2060 7 2060 7 2060 7 2060 7 2060 7 2060 7 2060 7 2060 7 2060 8 2100 8 2100 8 2100 8 2100 8 2100 8 2100 8 2100 8 2100 8 2100 8 2100 9 1590 9 1590 9 1590 9 1590 9 1590 9 1590 9 1590 9 1590 9 1590 9 1590} do_execsql_test 1.8.1 { SELECT a, sum(b) OVER ( ORDER BY a RANGE BETWEEN 0 PRECEDING AND 1 FOLLOWING ) FROM t3 ORDER BY 1; } {0 1010 0 1010 0 1010 0 1010 0 1010 0 1010 0 1010 0 1010 0 1010 0 1010 1 930 1 930 1 930 1 930 1 930 1 930 1 930 1 930 1 930 1 930 2 950 2 950 2 950 2 950 2 950 2 950 2 950 2 950 2 950 2 950 3 970 3 970 3 970 3 970 3 970 3 970 3 970 3 970 3 970 3 970 4 990 4 990 4 990 4 990 4 990 4 990 4 990 4 990 4 990 4 990 5 1010 5 1010 5 1010 5 1010 5 1010 5 1010 5 1010 5 1010 5 1010 5 1010 6 1030 6 1030 6 1030 6 1030 6 1030 6 1030 6 1030 6 1030 6 1030 6 1030 7 1050 7 1050 7 1050 7 1050 7 1050 7 1050 7 1050 7 1050 7 1050 7 1050 8 1070 8 1070 8 1070 8 1070 8 1070 8 1070 8 1070 8 1070 8 1070 8 1070 9 540 9 540 9 540 9 540 9 540 9 540 9 540 9 540 9 540 9 540} do_execsql_test 1.8.2 { SELECT a, sum(b) OVER ( ORDER BY a DESC RANGE BETWEEN 0 PRECEDING AND 1 FOLLOWING ) FROM t3 ORDER BY 1; } {0 550 0 550 0 550 0 550 0 550 0 550 0 550 0 550 0 550 0 550 1 1010 1 1010 1 1010 1 1010 1 1010 1 1010 1 1010 1 1010 1 1010 1 1010 2 930 2 930 2 930 2 930 2 930 2 930 2 930 2 930 2 930 2 930 3 950 3 950 3 950 3 950 3 950 3 950 3 950 3 950 3 950 3 950 4 970 4 970 4 970 4 970 4 970 4 970 4 970 4 970 4 970 4 970 5 990 5 990 5 990 5 990 5 990 5 990 5 990 5 990 5 990 5 990 6 1010 6 1010 6 1010 6 1010 6 1010 6 1010 6 1010 6 1010 6 1010 6 1010 7 1030 7 1030 7 1030 7 1030 7 1030 7 1030 7 1030 7 1030 7 1030 7 1030 8 1050 8 1050 8 1050 8 1050 8 1050 8 1050 8 1050 8 1050 8 1050 8 1050 9 1070 9 1070 9 1070 9 1070 9 1070 9 1070 9 1070 9 1070 9 1070 9 1070} finish_test |
Added test/windowerr.tcl.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
# 2018 May 19 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # source [file join [file dirname $argv0] pg_common.tcl] #========================================================================= start_test windowerr "2019 March 01" ifcapable !windowfunc execsql_test 1.0 { DROP TABLE IF EXISTS t1; CREATE TABLE t1(a INTEGER, b INTEGER); } foreach {tn frame} { 1 "ORDER BY a ROWS BETWEEN -1 PRECEDING AND 1 FOLLOWING" 2 "ORDER BY a ROWS BETWEEN 1 PRECEDING AND -1 FOLLOWING" 3 "ORDER BY a RANGE BETWEEN -1 PRECEDING AND 1 FOLLOWING" 4 "ORDER BY a RANGE BETWEEN 1 PRECEDING AND -1 FOLLOWING" 5 "ORDER BY a GROUPS BETWEEN -1 PRECEDING AND 1 FOLLOWING" 6 "ORDER BY a GROUPS BETWEEN 1 PRECEDING AND -1 FOLLOWING" } { errorsql_test 1.$tn " SELECT a, sum(b) OVER ( $frame ) FROM t3 ORDER BY 1 " } finish_test |
Added test/windowerr.test.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 |
# 2019 March 01 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. # #################################################### # DO NOT EDIT! THIS FILE IS AUTOMATICALLY GENERATED! #################################################### set testdir [file dirname $argv0] source $testdir/tester.tcl set testprefix windowerr ifcapable !windowfunc { finish_test ; return } do_execsql_test 1.0 { DROP TABLE IF EXISTS t1; CREATE TABLE t1(a INTEGER, b INTEGER); } {} # PG says "ERROR: frame starting offset must not be negative" do_test 1.1 { catch { execsql { SELECT a, sum(b) OVER ( ORDER BY a ROWS BETWEEN -1 PRECEDING AND 1 FOLLOWING ) FROM t3 ORDER BY 1 } } } 1 # PG says "ERROR: frame ending offset must not be negative" do_test 1.2 { catch { execsql { SELECT a, sum(b) OVER ( ORDER BY a ROWS BETWEEN 1 PRECEDING AND -1 FOLLOWING ) FROM t3 ORDER BY 1 } } } 1 # PG says "ERROR: invalid preceding or following size in window function" do_test 1.3 { catch { execsql { SELECT a, sum(b) OVER ( ORDER BY a RANGE BETWEEN -1 PRECEDING AND 1 FOLLOWING ) FROM t3 ORDER BY 1 } } } 1 # PG says "ERROR: invalid preceding or following size in window function" do_test 1.4 { catch { execsql { SELECT a, sum(b) OVER ( ORDER BY a RANGE BETWEEN 1 PRECEDING AND -1 FOLLOWING ) FROM t3 ORDER BY 1 } } } 1 # PG says "ERROR: frame starting offset must not be negative" do_test 1.5 { catch { execsql { SELECT a, sum(b) OVER ( ORDER BY a GROUPS BETWEEN -1 PRECEDING AND 1 FOLLOWING ) FROM t3 ORDER BY 1 } } } 1 # PG says "ERROR: frame ending offset must not be negative" do_test 1.6 { catch { execsql { SELECT a, sum(b) OVER ( ORDER BY a GROUPS BETWEEN 1 PRECEDING AND -1 FOLLOWING ) FROM t3 ORDER BY 1 } } } 1 finish_test |