Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Fix a problem with using min() or max() as a window function. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | exp-window-functions |
| Files: | files | file ages | folders |
| SHA3-256: | 801074ce63d3f4825cc9fa508c42629a9f74e7f9e35c5f238343bb1cff4fbae1 |
| User & Date: | dan 2018-06-19 17:13:11 |
Context
|
2018-06-19
| ||
| 18:11 | Fix a problem caused by a sub-query containing a window function in the FROM clause of a query that itself uses a window function. check-in: f4b1b6f8 user: dan tags: exp-window-functions | |
| 17:13 | Fix a problem with using min() or max() as a window function. check-in: 801074ce user: dan tags: exp-window-functions | |
|
2018-06-18
| ||
| 20:34 | Fix problems with using window functions in CREATE VIEW statements. check-in: 943bccd2 user: dan tags: exp-window-functions | |
Changes
Changes to src/func.c.
1606 1607 1608 1609 1610 1611 1612 1613 1614 1615 1616 1617 1618 1619 1620 .... 1632 1633 1634 1635 1636 1637 1638 1639 1640 1641 1642 1643 1644 1645 1646 1647 1648 1649 1650 1651 1652 1653 1654 .... 1879 1880 1881 1882 1883 1884 1885 1886 1887 1888 1889 1890 1891 1892 1893 1894 1895 1896 1897 .... 1914 1915 1916 1917 1918 1919 1920 1921 1922 1923 1924 1925 1926 1927 1928 1929 1930 1931 1932 1933 1934 1935 1936 |
Mem *pArg = (Mem *)argv[0]; Mem *pBest; UNUSED_PARAMETER(NotUsed); pBest = (Mem *)sqlite3_aggregate_context(context, sizeof(*pBest)); if( !pBest ) return; if( sqlite3_value_type(argv[0])==SQLITE_NULL ){ if( pBest->flags ) sqlite3SkipAccumulatorLoad(context); }else if( pBest->flags ){ int max; int cmp; CollSeq *pColl = sqlite3GetFuncCollSeq(context); /* This step function is used for both the min() and max() aggregates, ** the only difference between the two being that the sense of the ................................................................................ sqlite3SkipAccumulatorLoad(context); } }else{ pBest->db = sqlite3_context_db_handle(context); sqlite3VdbeMemCopy(pBest, pArg); } } static void minMaxFinalize(sqlite3_context *context){ sqlite3_value *pRes; pRes = (sqlite3_value *)sqlite3_aggregate_context(context, 0); if( pRes ){ if( pRes->flags ){ sqlite3_result_value(context, pRes); } sqlite3VdbeMemRelease(pRes); } } /* ** group_concat(EXPR, ?SEPARATOR?) */ static void groupConcatStep( sqlite3_context *context, ................................................................................ FUNCTION(ltrim, 2, 1, 0, trimFunc ), FUNCTION(rtrim, 1, 2, 0, trimFunc ), FUNCTION(rtrim, 2, 2, 0, trimFunc ), FUNCTION(trim, 1, 3, 0, trimFunc ), FUNCTION(trim, 2, 3, 0, trimFunc ), FUNCTION(min, -1, 0, 1, minmaxFunc ), FUNCTION(min, 0, 0, 1, 0 ), AGGREGATE2(min, 1, 0, 1, minmaxStep, minMaxFinalize, SQLITE_FUNC_MINMAX ), FUNCTION(max, -1, 1, 1, minmaxFunc ), FUNCTION(max, 0, 1, 1, 0 ), AGGREGATE2(max, 1, 1, 1, minmaxStep, minMaxFinalize, SQLITE_FUNC_MINMAX ), FUNCTION2(typeof, 1, 0, 0, typeofFunc, SQLITE_FUNC_TYPEOF), FUNCTION2(length, 1, 0, 0, lengthFunc, SQLITE_FUNC_LENGTH), FUNCTION(instr, 2, 0, 0, instrFunc ), FUNCTION(printf, -1, 0, 0, printfFunc ), FUNCTION(unicode, 1, 0, 0, unicodeFunc ), FUNCTION(char, -1, 0, 0, charFunc ), ................................................................................ VFUNCTION(last_insert_rowid, 0, 0, 0, last_insert_rowid), VFUNCTION(changes, 0, 0, 0, changes ), VFUNCTION(total_changes, 0, 0, 0, total_changes ), FUNCTION(replace, 3, 0, 0, replaceFunc ), FUNCTION(zeroblob, 1, 0, 0, zeroblobFunc ), FUNCTION(substr, 2, 0, 0, substrFunc ), FUNCTION(substr, 3, 0, 0, substrFunc ), WAGGREGATE(sum, 1,0,0, sumStep, sumFinalize, sumFinalize, sumInverse), WAGGREGATE(total, 1,0,0, sumStep, totalFinalize, totalFinalize, sumInverse), WAGGREGATE(avg, 1,0,0, sumStep, avgFinalize, avgFinalize, sumInverse), AGGREGATE2(count, 0,0,0, countStep, countFinalize, SQLITE_FUNC_COUNT ), WAGGREGATE(count, 1,0,0, countStep, countFinalize, 0, 0 ), WAGGREGATE(group_concat, 1, 0, 0, groupConcatStep, groupConcatFinalize, groupConcatValue, groupConcatInverse), WAGGREGATE(group_concat, 2, 0, 0, groupConcatStep, groupConcatFinalize, groupConcatValue, groupConcatInverse), LIKEFUNC(glob, 2, &globInfo, SQLITE_FUNC_LIKE|SQLITE_FUNC_CASE), #ifdef SQLITE_CASE_SENSITIVE_LIKE LIKEFUNC(like, 2, &likeInfoAlt, SQLITE_FUNC_LIKE|SQLITE_FUNC_CASE), LIKEFUNC(like, 3, &likeInfoAlt, SQLITE_FUNC_LIKE|SQLITE_FUNC_CASE), #else LIKEFUNC(like, 2, &likeInfoNorm, SQLITE_FUNC_LIKE), |
| | | > > > > > > | | | | | | | | |
1606 1607 1608 1609 1610 1611 1612 1613 1614 1615 1616 1617 1618 1619 1620 .... 1632 1633 1634 1635 1636 1637 1638 1639 1640 1641 1642 1643 1644 1645 1646 1647 1648 1649 1650 1651 1652 1653 1654 1655 1656 1657 1658 1659 1660 .... 1885 1886 1887 1888 1889 1890 1891 1892 1893 1894 1895 1896 1897 1898 1899 1900 1901 1902 1903 .... 1920 1921 1922 1923 1924 1925 1926 1927 1928 1929 1930 1931 1932 1933 1934 1935 1936 1937 1938 1939 1940 1941 1942 |
Mem *pArg = (Mem *)argv[0]; Mem *pBest; UNUSED_PARAMETER(NotUsed); pBest = (Mem *)sqlite3_aggregate_context(context, sizeof(*pBest)); if( !pBest ) return; if( sqlite3_value_type(pArg)==SQLITE_NULL ){ if( pBest->flags ) sqlite3SkipAccumulatorLoad(context); }else if( pBest->flags ){ int max; int cmp; CollSeq *pColl = sqlite3GetFuncCollSeq(context); /* This step function is used for both the min() and max() aggregates, ** the only difference between the two being that the sense of the ................................................................................ sqlite3SkipAccumulatorLoad(context); } }else{ pBest->db = sqlite3_context_db_handle(context); sqlite3VdbeMemCopy(pBest, pArg); } } static void minMaxValueFinalize(sqlite3_context *context, int bValue){ sqlite3_value *pRes; pRes = (sqlite3_value *)sqlite3_aggregate_context(context, 0); if( pRes ){ if( pRes->flags ){ sqlite3_result_value(context, pRes); } if( bValue==0 ) sqlite3VdbeMemRelease(pRes); } } static void minMaxValue(sqlite3_context *context){ return minMaxValueFinalize(context, 1); } static void minMaxFinalize(sqlite3_context *context){ return minMaxValueFinalize(context, 0); } /* ** group_concat(EXPR, ?SEPARATOR?) */ static void groupConcatStep( sqlite3_context *context, ................................................................................ FUNCTION(ltrim, 2, 1, 0, trimFunc ), FUNCTION(rtrim, 1, 2, 0, trimFunc ), FUNCTION(rtrim, 2, 2, 0, trimFunc ), FUNCTION(trim, 1, 3, 0, trimFunc ), FUNCTION(trim, 2, 3, 0, trimFunc ), FUNCTION(min, -1, 0, 1, minmaxFunc ), FUNCTION(min, 0, 0, 1, 0 ), WAGGREGATE(min, 1, 0, 1, minmaxStep, minMaxFinalize, minMaxValue, 0, SQLITE_FUNC_MINMAX ), FUNCTION(max, -1, 1, 1, minmaxFunc ), FUNCTION(max, 0, 1, 1, 0 ), WAGGREGATE(max, 1, 1, 1, minmaxStep, minMaxFinalize, minMaxValue, 0, SQLITE_FUNC_MINMAX ), FUNCTION2(typeof, 1, 0, 0, typeofFunc, SQLITE_FUNC_TYPEOF), FUNCTION2(length, 1, 0, 0, lengthFunc, SQLITE_FUNC_LENGTH), FUNCTION(instr, 2, 0, 0, instrFunc ), FUNCTION(printf, -1, 0, 0, printfFunc ), FUNCTION(unicode, 1, 0, 0, unicodeFunc ), FUNCTION(char, -1, 0, 0, charFunc ), ................................................................................ VFUNCTION(last_insert_rowid, 0, 0, 0, last_insert_rowid), VFUNCTION(changes, 0, 0, 0, changes ), VFUNCTION(total_changes, 0, 0, 0, total_changes ), FUNCTION(replace, 3, 0, 0, replaceFunc ), FUNCTION(zeroblob, 1, 0, 0, zeroblobFunc ), FUNCTION(substr, 2, 0, 0, substrFunc ), FUNCTION(substr, 3, 0, 0, substrFunc ), WAGGREGATE(sum, 1,0,0, sumStep, sumFinalize, sumFinalize, sumInverse, 0), WAGGREGATE(total, 1,0,0, sumStep,totalFinalize,totalFinalize,sumInverse, 0), WAGGREGATE(avg, 1,0,0, sumStep, avgFinalize, avgFinalize, sumInverse, 0), AGGREGATE2(count, 0,0,0, countStep, countFinalize, SQLITE_FUNC_COUNT ), WAGGREGATE(count, 1,0,0, countStep, countFinalize, 0, 0, 0 ), WAGGREGATE(group_concat, 1, 0, 0, groupConcatStep, groupConcatFinalize, groupConcatValue, groupConcatInverse, 0), WAGGREGATE(group_concat, 2, 0, 0, groupConcatStep, groupConcatFinalize, groupConcatValue, groupConcatInverse, 0), LIKEFUNC(glob, 2, &globInfo, SQLITE_FUNC_LIKE|SQLITE_FUNC_CASE), #ifdef SQLITE_CASE_SENSITIVE_LIKE LIKEFUNC(like, 2, &likeInfoAlt, SQLITE_FUNC_LIKE|SQLITE_FUNC_CASE), LIKEFUNC(like, 3, &likeInfoAlt, SQLITE_FUNC_LIKE|SQLITE_FUNC_CASE), #else LIKEFUNC(like, 2, &likeInfoNorm, SQLITE_FUNC_LIKE), |
Changes to src/sqliteInt.h.
1758 1759 1760 1761 1762 1763 1764 1765 1766 1767 1768 1769 1770 1771 1772 1773 |
#define AGGREGATE(zName, nArg, arg, nc, xStep, xFinal, xValue) \
{nArg, SQLITE_UTF8|(nc*SQLITE_FUNC_NEEDCOLL), \
SQLITE_INT_TO_PTR(arg), 0, xStep,xFinal,xValue,0,#zName, {0}}
#define AGGREGATE2(zName, nArg, arg, nc, xStep, xFinal, extraFlags) \
{nArg, SQLITE_UTF8|(nc*SQLITE_FUNC_NEEDCOLL)|extraFlags, \
SQLITE_INT_TO_PTR(arg), 0, xStep,xFinal,xFinal,0,#zName, {0}}
#define WAGGREGATE(zName, nArg, arg, nc, xStep, xFinal, xValue, xInverse) \
{nArg, SQLITE_UTF8|(nc*SQLITE_FUNC_NEEDCOLL), \
SQLITE_INT_TO_PTR(arg), 0, xStep,xFinal,xValue,xInverse,#zName, {0}}
/*
** All current savepoints are stored in a linked list starting at
** sqlite3.pSavepoint. The first element in the list is the most recently
** opened savepoint. Savepoints are added to the list by the vdbe
** OP_Savepoint instruction.
|
| | |
1758 1759 1760 1761 1762 1763 1764 1765 1766 1767 1768 1769 1770 1771 1772 1773 |
#define AGGREGATE(zName, nArg, arg, nc, xStep, xFinal, xValue) \
{nArg, SQLITE_UTF8|(nc*SQLITE_FUNC_NEEDCOLL), \
SQLITE_INT_TO_PTR(arg), 0, xStep,xFinal,xValue,0,#zName, {0}}
#define AGGREGATE2(zName, nArg, arg, nc, xStep, xFinal, extraFlags) \
{nArg, SQLITE_UTF8|(nc*SQLITE_FUNC_NEEDCOLL)|extraFlags, \
SQLITE_INT_TO_PTR(arg), 0, xStep,xFinal,xFinal,0,#zName, {0}}
#define WAGGREGATE(zName, nArg, arg, nc, xStep, xFinal, xValue, xInverse, f) \
{nArg, SQLITE_UTF8|(nc*SQLITE_FUNC_NEEDCOLL)|f, \
SQLITE_INT_TO_PTR(arg), 0, xStep,xFinal,xValue,xInverse,#zName, {0}}
/*
** All current savepoints are stored in a linked list starting at
** sqlite3.pSavepoint. The first element in the list is the most recently
** opened savepoint. Savepoints are added to the list by the vdbe
** OP_Savepoint instruction.
|
Changes to test/window1.test.
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
...
313
314
315
316
317
318
319
320
321
322
323
|
4 6 8 6 8 10 8 10 default 10 {} default {} {} default
}
do_execsql_test 7.3 {
SELECT row_number() OVER (ORDER BY x) FROM t1
} {1 2 3 4 5}
breakpoint
do_execsql_test 7.4 {
SELECT
row_number() OVER win,
lead(x) OVER win
FROM t1
WINDOW win AS (ORDER BY x ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
} {1 3 2 5 3 7 4 9 5 {}}
................................................................................
do_execsql_test 8.2.1 {
SELECT * FROM v1
} {1 1 1 3 1 2 6 1 3 10 1 4 15 1 5 21 1 6}
do_execsql_test 8.2.2 {
SELECT * FROM v2
} {1 1 1 3 1 2 6 1 3 10 1 4 15 1 5 21 1 6}
finish_test
|
<
>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|
265
266
267
268
269
270
271
272
273
274
275
276
277
278
...
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
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
|
4 6 8 6 8 10 8 10 default 10 {} default {} {} default
}
do_execsql_test 7.3 {
SELECT row_number() OVER (ORDER BY x) FROM t1
} {1 2 3 4 5}
do_execsql_test 7.4 {
SELECT
row_number() OVER win,
lead(x) OVER win
FROM t1
WINDOW win AS (ORDER BY x ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
} {1 3 2 5 3 7 4 9 5 {}}
................................................................................
do_execsql_test 8.2.1 {
SELECT * FROM v1
} {1 1 1 3 1 2 6 1 3 10 1 4 15 1 5 21 1 6}
do_execsql_test 8.2.2 {
SELECT * FROM v2
} {1 1 1 3 1 2 6 1 3 10 1 4 15 1 5 21 1 6}
#-------------------------------------------------------------------------
# Attempt to use a window function in a trigger.
#
do_execsql_test 9.0 {
CREATE TABLE t4(x, y);
INSERT INTO t4 VALUES(1, 'g');
INSERT INTO t4 VALUES(2, 'i');
INSERT INTO t4 VALUES(3, 'l');
INSERT INTO t4 VALUES(4, 'g');
INSERT INTO t4 VALUES(5, 'a');
CREATE TABLE t5(x, y, m);
CREATE TRIGGER t4i AFTER INSERT ON t4 BEGIN
DELETE FROM t5;
INSERT INTO t5
SELECT x, y, max(y) OVER xyz FROM t4
WINDOW xyz AS (PARTITION BY (x%2) ORDER BY x);
END;
}
do_execsql_test 9.1.1 {
SELECT x, y, max(y) OVER xyz FROM t4
WINDOW xyz AS (PARTITION BY (x%2) ORDER BY x) ORDER BY 1
} {1 g g 2 i i 3 l l 4 g i 5 a l}
do_execsql_test 9.1.2 {
INSERT INTO t4 VALUES(6, 'm');
SELECT x, y, max(y) OVER xyz FROM t4
WINDOW xyz AS (PARTITION BY (x%2) ORDER BY x) ORDER BY 1
} {1 g g 2 i i 3 l l 4 g i 5 a l 6 m m}
do_execsql_test 9.1.3 {
SELECT * FROM t5 ORDER BY 1
} {1 g g 2 i i 3 l l 4 g i 5 a l 6 m m}
finish_test
|