Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Fixes to allow group_concat() to be used 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: |
89bbc9ba8f66853a7530453f146c9df1 |
| User & Date: | dan 2018-06-08 11:45:28 |
Context
|
2018-06-08
| ||
| 16:11 | Do not flatten sub-queries that contain window functions. check-in: 236cb75b user: dan tags: exp-window-functions | |
| 11:45 | Fixes to allow group_concat() to be used as a window function. check-in: 89bbc9ba user: dan tags: exp-window-functions | |
|
2018-06-07
| ||
| 20:35 | Merge latest trunk changes with this branch. check-in: 25102203 user: dan tags: exp-window-functions | |
Changes
Changes to src/func.c.
1661 1662 1663 1664 1665 1666 1667 1668 1669 1670 1671 1672 1673 1674 1675 .... 1677 1678 1679 1680 1681 1682 1683 1684 1685 1686 1687 1688 1689 1690 .... 1890 1891 1892 1893 1894 1895 1896 1897 1898 1899 1900 1901 1902 1903 1904 1905 1906 1907 1908 1909 1910 1911 1912 1913 |
int nVal, nSep;
assert( argc==1 || argc==2 );
if( sqlite3_value_type(argv[0])==SQLITE_NULL ) return;
pAccum = (StrAccum*)sqlite3_aggregate_context(context, sizeof(*pAccum));
if( pAccum ){
sqlite3 *db = sqlite3_context_db_handle(context);
int firstTerm = pAccum->mxAlloc==0;
pAccum->mxAlloc = db->aLimit[SQLITE_LIMIT_LENGTH];
if( !firstTerm ){
if( argc==2 ){
zSep = (char*)sqlite3_value_text(argv[1]);
nSep = sqlite3_value_bytes(argv[1]);
}else{
zSep = ",";
................................................................................
}
if( zSep ) sqlite3_str_append(pAccum, zSep, nSep);
}
zVal = (char*)sqlite3_value_text(argv[0]);
nVal = sqlite3_value_bytes(argv[0]);
if( zVal ) sqlite3_str_append(pAccum, zVal, nVal);
}
}
static void groupConcatFinalize(sqlite3_context *context){
StrAccum *pAccum;
pAccum = sqlite3_aggregate_context(context, 0);
if( pAccum ){
if( pAccum->accError==SQLITE_TOOBIG ){
sqlite3_result_error_toobig(context);
................................................................................
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, sumInverse, sumFinalize),
WAGGREGATE(total, 1, 0, 0, sumStep, sumInverse, totalFinalize ),
WAGGREGATE(avg, 1, 0, 0, sumStep, sumInverse, avgFinalize ),
AGGREGATE2(count, 0, 0, 0, countStep, countFinalize,
SQLITE_FUNC_COUNT ),
WAGGREGATE(count, 1, 0, 0, countStep, 0, countFinalize ),
AGGREGATE(group_concat, 1, 0, 0, groupConcatStep, groupConcatFinalize,
groupConcatValue),
AGGREGATE(group_concat, 2, 0, 0, groupConcatStep, groupConcatFinalize,
groupConcatValue),
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),
|
| > > > > > > > > > > > > > > > > > > > > > > > | | | < | < > | | | | |
1661 1662 1663 1664 1665 1666 1667 1668 1669 1670 1671 1672 1673 1674 1675 .... 1677 1678 1679 1680 1681 1682 1683 1684 1685 1686 1687 1688 1689 1690 1691 1692 1693 1694 1695 1696 1697 1698 1699 1700 1701 1702 1703 1704 1705 1706 1707 1708 1709 1710 1711 1712 1713 .... 1913 1914 1915 1916 1917 1918 1919 1920 1921 1922 1923 1924 1925 1926 1927 1928 1929 1930 1931 1932 1933 1934 1935 |
int nVal, nSep;
assert( argc==1 || argc==2 );
if( sqlite3_value_type(argv[0])==SQLITE_NULL ) return;
pAccum = (StrAccum*)sqlite3_aggregate_context(context, sizeof(*pAccum));
if( pAccum ){
sqlite3 *db = sqlite3_context_db_handle(context);
int firstTerm = pAccum->nChar==0;
pAccum->mxAlloc = db->aLimit[SQLITE_LIMIT_LENGTH];
if( !firstTerm ){
if( argc==2 ){
zSep = (char*)sqlite3_value_text(argv[1]);
nSep = sqlite3_value_bytes(argv[1]);
}else{
zSep = ",";
................................................................................
}
if( zSep ) sqlite3_str_append(pAccum, zSep, nSep);
}
zVal = (char*)sqlite3_value_text(argv[0]);
nVal = sqlite3_value_bytes(argv[0]);
if( zVal ) sqlite3_str_append(pAccum, zVal, nVal);
}
}
static void groupConcatInverse(
sqlite3_context *context,
int argc,
sqlite3_value **argv
){
int n;
assert( argc==1 || argc==2 );
StrAccum *pAccum;
if( sqlite3_value_type(argv[0])==SQLITE_NULL ) return;
pAccum = (StrAccum*)sqlite3_aggregate_context(context, sizeof(*pAccum));
if( pAccum ){
n = sqlite3_value_bytes(argv[0]);
if( argc==2 ){
n += sqlite3_value_bytes(argv[1]);
}
if( n>=pAccum->nChar ){
pAccum->nChar = 0;
}else{
pAccum->nChar -= n;
memmove(pAccum->zText, &pAccum->zText[n], pAccum->nChar);
}
}
}
static void groupConcatFinalize(sqlite3_context *context){
StrAccum *pAccum;
pAccum = sqlite3_aggregate_context(context, 0);
if( pAccum ){
if( pAccum->accError==SQLITE_TOOBIG ){
sqlite3_result_error_toobig(context);
................................................................................
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),
|
Changes to src/sqliteInt.h.
1721 1722 1723 1724 1725 1726 1727 1728 1729 1730 1731 1732 1733 1734 1735 1736 1737 |
#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, xInverse, xFinal) \
{nArg, SQLITE_UTF8|(nc*SQLITE_FUNC_NEEDCOLL), \
SQLITE_INT_TO_PTR(arg), 0, xStep,xFinal,xFinal,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.
*/
|
| | |
1721 1722 1723 1724 1725 1726 1727 1728 1729 1730 1731 1732 1733 1734 1735 1736 1737 |
#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.
*/
|
Changes to test/pg_common.tcl.
56 57 58 59 60 61 62 63 64 65 66 67 68 69 |
}
set ret
}
proc execsql_test {tn sql} {
set res [execsql $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
|
> |
56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 |
}
set ret
}
proc execsql_test {tn sql} {
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
|
Changes to test/window3.tcl.
276 277 278 279 280 281 282 283 284 285 286 |
"
execsql_test 1.$tn.13.5 "
SELECT lag(b,b) OVER ( ORDER BY b%10,a $window ) FROM t2
"
execsql_test 1.$tn.13.6 "
SELECT lag(b,b) OVER (PARTITION BY b%2,a ORDER BY b%10 $window) FROM t2
"
}
finish_test
|
> > > > > > > > > > > > > > > > > > > > |
276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 |
"
execsql_test 1.$tn.13.5 "
SELECT lag(b,b) OVER ( ORDER BY b%10,a $window ) FROM t2
"
execsql_test 1.$tn.13.6 "
SELECT lag(b,b) OVER (PARTITION BY b%2,a ORDER BY b%10 $window) FROM t2
"
execsql_test 1.$tn.14.1 "
SELECT string_agg(CAST(b AS TEXT), '.') OVER (ORDER BY a $window) FROM t2
"
execsql_test 1.$tn.14.2 "
SELECT string_agg(CAST(b AS TEXT), '.') OVER (PARTITION BY b%10 ORDER BY a $window) FROM t2
"
execsql_test 1.$tn.14.3 "
SELECT string_agg(CAST(b AS TEXT), '.') OVER ( ORDER BY b,a $window ) FROM t2
"
execsql_test 1.$tn.14.4 "
SELECT string_agg(CAST(b AS TEXT), '.') OVER ( PARTITION BY b%10 ORDER BY b,a $window ) FROM t2
"
execsql_test 1.$tn.14.5 "
SELECT string_agg(CAST(b AS TEXT), '.') OVER ( ORDER BY b%10,a $window ) FROM t2
"
execsql_test 1.$tn.14.6 "
SELECT string_agg(CAST(b AS TEXT), '.') OVER (PARTITION BY b%2,a ORDER BY b%10 $window) FROM t2
"
}
finish_test
|
Changes to test/window3.test.
cannot compute difference between binary files
Changes to test/window4.tcl.
61 62 63 64 65 66 67 68 69 70 71 72 73 74 |
}
execsql_test 2.3.2 {
SELECT a, lag(b, 2) OVER (ORDER BY a) FROM t4
}
execsql_test 2.3.3 {
SELECT a, lag(b, 3, 'abc') OVER (ORDER BY a) FROM t4
}
execsql_test 3.0 {
DROP TABLE IF EXISTS t5;
CREATE TABLE t5(a INTEGER PRIMARY KEY, b TEXT, c TEXT, d INTEGER);
INSERT INTO t5 VALUES(1, 'A', 'one', 5);
INSERT INTO t5 VALUES(2, 'B', 'two', 4);
INSERT INTO t5 VALUES(3, 'A', 'three', 3);
|
> > > > > > |
61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 |
}
execsql_test 2.3.2 {
SELECT a, lag(b, 2) OVER (ORDER BY a) FROM t4
}
execsql_test 2.3.3 {
SELECT a, lag(b, 3, 'abc') OVER (ORDER BY a) FROM t4
}
execsql_test 2.4.1 {
SELECT string_agg(b, '.') OVER (
ORDER BY a ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
) FROM t4
}
execsql_test 3.0 {
DROP TABLE IF EXISTS t5;
CREATE TABLE t5(a INTEGER PRIMARY KEY, b TEXT, c TEXT, d INTEGER);
INSERT INTO t5 VALUES(1, 'A', 'one', 5);
INSERT INTO t5 VALUES(2, 'B', 'two', 4);
INSERT INTO t5 VALUES(3, 'A', 'three', 3);
|
Changes to test/window4.test.
141 142 143 144 145 146 147 148 149 150 151 152 153 154 |
SELECT a, lag(b, 2) OVER (ORDER BY a) FROM t4
} {1 {} 2 {} 3 A 4 B 5 C 6 D 7 E 8 F 9 G 10 H}
do_execsql_test 2.3.3 {
SELECT a, lag(b, 3, 'abc') OVER (ORDER BY a) FROM t4
} {1 abc 2 abc 3 abc 4 A 5 B 6 C 7 D 8 E 9 F 10 G}
do_execsql_test 3.0 {
DROP TABLE IF EXISTS t5;
CREATE TABLE t5(a INTEGER PRIMARY KEY, b TEXT, c TEXT, d INTEGER);
INSERT INTO t5 VALUES(1, 'A', 'one', 5);
INSERT INTO t5 VALUES(2, 'B', 'two', 4);
INSERT INTO t5 VALUES(3, 'A', 'three', 3);
INSERT INTO t5 VALUES(4, 'B', 'four', 2);
|
> > > > > > |
141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 |
SELECT a, lag(b, 2) OVER (ORDER BY a) FROM t4
} {1 {} 2 {} 3 A 4 B 5 C 6 D 7 E 8 F 9 G 10 H}
do_execsql_test 2.3.3 {
SELECT a, lag(b, 3, 'abc') OVER (ORDER BY a) FROM t4
} {1 abc 2 abc 3 abc 4 A 5 B 6 C 7 D 8 E 9 F 10 G}
do_execsql_test 2.4.1 {
SELECT group_concat(b, '.') OVER (
ORDER BY a ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
) FROM t4
} {A.B.C.D.E.F.G.H.I.J B.C.D.E.F.G.H.I.J C.D.E.F.G.H.I.J D.E.F.G.H.I.J E.F.G.H.I.J F.G.H.I.J G.H.I.J H.I.J I.J J}
do_execsql_test 3.0 {
DROP TABLE IF EXISTS t5;
CREATE TABLE t5(a INTEGER PRIMARY KEY, b TEXT, c TEXT, d INTEGER);
INSERT INTO t5 VALUES(1, 'A', 'one', 5);
INSERT INTO t5 VALUES(2, 'B', 'two', 4);
INSERT INTO t5 VALUES(3, 'A', 'three', 3);
INSERT INTO t5 VALUES(4, 'B', 'four', 2);
|