SQLite

Check-in [89bbc9ba8f]
Login

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
Timelines: family | ancestors | descendants | both | exp-window-functions
Files: files | file ages | folders
SHA3-256: 89bbc9ba8f66853a7530453f146c9df1baacd8558468016cefa7602911f7578a
User & Date: dan 2018-06-08 11:45:28.408
Context
2018-06-08
16:11
Do not flatten sub-queries that contain window functions. (check-in: 236cb75bd1 user: dan tags: exp-window-functions)
11:45
Fixes to allow group_concat() to be used as a window function. (check-in: 89bbc9ba8f user: dan tags: exp-window-functions)
2018-06-07
20:35
Merge latest trunk changes with this branch. (check-in: 2510220342 user: dan tags: exp-window-functions)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/func.c.
1661
1662
1663
1664
1665
1666
1667
1668
1669
1670
1671
1672
1673
1674
1675
1676
1677
1678
1679
1680
1681
1682
1683























1684
1685
1686
1687
1688
1689
1690
  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 = ",";
        nSep = 1;
      }
      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);







|















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







1661
1662
1663
1664
1665
1666
1667
1668
1669
1670
1671
1672
1673
1674
1675
1676
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
  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 = ",";
        nSep = 1;
      }
      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);
1890
1891
1892
1893
1894
1895
1896
1897
1898
1899
1900
1901
1902
1903
1904
1905
1906
1907
1908
1909
1910
1911
1912
1913
    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),







|
|
|
<
|
|
|
|
|
|







1913
1914
1915
1916
1917
1918
1919
1920
1921
1922

1923
1924
1925
1926
1927
1928
1929
1930
1931
1932
1933
1934
1935
    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);