/ Check-in [89bbc9ba]
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 | SQL 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
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
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/func.c.

  1661   1661     int nVal, nSep;
  1662   1662     assert( argc==1 || argc==2 );
  1663   1663     if( sqlite3_value_type(argv[0])==SQLITE_NULL ) return;
  1664   1664     pAccum = (StrAccum*)sqlite3_aggregate_context(context, sizeof(*pAccum));
  1665   1665   
  1666   1666     if( pAccum ){
  1667   1667       sqlite3 *db = sqlite3_context_db_handle(context);
  1668         -    int firstTerm = pAccum->mxAlloc==0;
         1668  +    int firstTerm = pAccum->nChar==0;
  1669   1669       pAccum->mxAlloc = db->aLimit[SQLITE_LIMIT_LENGTH];
  1670   1670       if( !firstTerm ){
  1671   1671         if( argc==2 ){
  1672   1672           zSep = (char*)sqlite3_value_text(argv[1]);
  1673   1673           nSep = sqlite3_value_bytes(argv[1]);
  1674   1674         }else{
  1675   1675           zSep = ",";
................................................................................
  1677   1677         }
  1678   1678         if( zSep ) sqlite3_str_append(pAccum, zSep, nSep);
  1679   1679       }
  1680   1680       zVal = (char*)sqlite3_value_text(argv[0]);
  1681   1681       nVal = sqlite3_value_bytes(argv[0]);
  1682   1682       if( zVal ) sqlite3_str_append(pAccum, zVal, nVal);
  1683   1683     }
         1684  +}
         1685  +static void groupConcatInverse(
         1686  +  sqlite3_context *context,
         1687  +  int argc,
         1688  +  sqlite3_value **argv
         1689  +){
         1690  +  int n;
         1691  +  assert( argc==1 || argc==2 );
         1692  +  StrAccum *pAccum;
         1693  +  if( sqlite3_value_type(argv[0])==SQLITE_NULL ) return;
         1694  +  pAccum = (StrAccum*)sqlite3_aggregate_context(context, sizeof(*pAccum));
         1695  +  if( pAccum ){
         1696  +    n = sqlite3_value_bytes(argv[0]);
         1697  +    if( argc==2 ){
         1698  +      n += sqlite3_value_bytes(argv[1]);
         1699  +    }
         1700  +    if( n>=pAccum->nChar ){
         1701  +      pAccum->nChar = 0;
         1702  +    }else{
         1703  +      pAccum->nChar -= n;
         1704  +      memmove(pAccum->zText, &pAccum->zText[n], pAccum->nChar);
         1705  +    }
         1706  +  }
  1684   1707   }
  1685   1708   static void groupConcatFinalize(sqlite3_context *context){
  1686   1709     StrAccum *pAccum;
  1687   1710     pAccum = sqlite3_aggregate_context(context, 0);
  1688   1711     if( pAccum ){
  1689   1712       if( pAccum->accError==SQLITE_TOOBIG ){
  1690   1713         sqlite3_result_error_toobig(context);
................................................................................
  1890   1913       VFUNCTION(last_insert_rowid, 0, 0, 0, last_insert_rowid),
  1891   1914       VFUNCTION(changes,           0, 0, 0, changes          ),
  1892   1915       VFUNCTION(total_changes,     0, 0, 0, total_changes    ),
  1893   1916       FUNCTION(replace,            3, 0, 0, replaceFunc      ),
  1894   1917       FUNCTION(zeroblob,           1, 0, 0, zeroblobFunc     ),
  1895   1918       FUNCTION(substr,             2, 0, 0, substrFunc       ),
  1896   1919       FUNCTION(substr,             3, 0, 0, substrFunc       ),
  1897         -    WAGGREGATE(sum,        1, 0, 0, sumStep, sumInverse,   sumFinalize),
  1898         -    WAGGREGATE(total,      1, 0, 0, sumStep, sumInverse,   totalFinalize    ),
  1899         -    WAGGREGATE(avg,        1, 0, 0, sumStep, sumInverse,   avgFinalize    ),
  1900         -    AGGREGATE2(count,            0, 0, 0, countStep,       countFinalize,
  1901         -               SQLITE_FUNC_COUNT  ),
  1902         -    WAGGREGATE(count,             1, 0, 0, countStep, 0,    countFinalize  ),
  1903         -    AGGREGATE(group_concat,      1, 0, 0, groupConcatStep, groupConcatFinalize,
  1904         -        groupConcatValue),
  1905         -    AGGREGATE(group_concat,      2, 0, 0, groupConcatStep, groupConcatFinalize,
  1906         -        groupConcatValue),
         1920  +    WAGGREGATE(sum,   1,0,0, sumStep, sumFinalize, sumFinalize, sumInverse),
         1921  +    WAGGREGATE(total, 1,0,0, sumStep, totalFinalize, totalFinalize, sumInverse),
         1922  +    WAGGREGATE(avg,   1,0,0, sumStep, avgFinalize, avgFinalize, sumInverse),
         1923  +    AGGREGATE2(count, 0,0,0, countStep, countFinalize, SQLITE_FUNC_COUNT  ),
         1924  +    WAGGREGATE(count, 1,0,0, countStep, countFinalize, 0, 0 ),
         1925  +    WAGGREGATE(group_concat, 1, 0, 0, groupConcatStep, 
         1926  +        groupConcatFinalize, groupConcatValue, groupConcatInverse),
         1927  +    WAGGREGATE(group_concat, 2, 0, 0, groupConcatStep, 
         1928  +        groupConcatFinalize, groupConcatValue, groupConcatInverse),
  1907   1929     
  1908   1930       LIKEFUNC(glob, 2, &globInfo, SQLITE_FUNC_LIKE|SQLITE_FUNC_CASE),
  1909   1931   #ifdef SQLITE_CASE_SENSITIVE_LIKE
  1910   1932       LIKEFUNC(like, 2, &likeInfoAlt, SQLITE_FUNC_LIKE|SQLITE_FUNC_CASE),
  1911   1933       LIKEFUNC(like, 3, &likeInfoAlt, SQLITE_FUNC_LIKE|SQLITE_FUNC_CASE),
  1912   1934   #else
  1913   1935       LIKEFUNC(like, 2, &likeInfoNorm, SQLITE_FUNC_LIKE),

Changes to src/sqliteInt.h.

  1721   1721   #define AGGREGATE(zName, nArg, arg, nc, xStep, xFinal, xValue) \
  1722   1722     {nArg, SQLITE_UTF8|(nc*SQLITE_FUNC_NEEDCOLL), \
  1723   1723      SQLITE_INT_TO_PTR(arg), 0, xStep,xFinal,xValue,0,#zName, {0}}
  1724   1724   #define AGGREGATE2(zName, nArg, arg, nc, xStep, xFinal, extraFlags) \
  1725   1725     {nArg, SQLITE_UTF8|(nc*SQLITE_FUNC_NEEDCOLL)|extraFlags, \
  1726   1726      SQLITE_INT_TO_PTR(arg), 0, xStep,xFinal,xFinal,0,#zName, {0}}
  1727   1727   
  1728         -#define WAGGREGATE(zName, nArg, arg, nc, xStep, xInverse, xFinal) \
         1728  +#define WAGGREGATE(zName, nArg, arg, nc, xStep, xFinal, xValue, xInverse) \
  1729   1729     {nArg, SQLITE_UTF8|(nc*SQLITE_FUNC_NEEDCOLL), \
  1730         -   SQLITE_INT_TO_PTR(arg), 0, xStep,xFinal,xFinal,xInverse,#zName, {0}}
         1730  +   SQLITE_INT_TO_PTR(arg), 0, xStep,xFinal,xValue,xInverse,#zName, {0}}
  1731   1731   
  1732   1732   /*
  1733   1733   ** All current savepoints are stored in a linked list starting at
  1734   1734   ** sqlite3.pSavepoint. The first element in the list is the most recently
  1735   1735   ** opened savepoint. Savepoints are added to the list by the vdbe
  1736   1736   ** OP_Savepoint instruction.
  1737   1737   */

Changes to test/pg_common.tcl.

    56     56     }
    57     57   
    58     58     set ret
    59     59   }
    60     60   
    61     61   proc execsql_test {tn sql} {
    62     62     set res [execsql $sql]
           63  +  set sql [string map {string_agg group_concat} $sql]
    63     64     puts $::fd "do_execsql_test $tn {"
    64     65     puts $::fd "  [string trim $sql]"
    65     66     puts $::fd "} {$res}"
    66     67     puts $::fd ""
    67     68   }
    68     69   
    69     70   # Same as [execsql_test], except coerce all results to floating point values

Changes to test/window3.tcl.

   276    276     "
   277    277     execsql_test 1.$tn.13.5 "
   278    278       SELECT lag(b,b) OVER ( ORDER BY b%10,a $window ) FROM t2
   279    279     "
   280    280     execsql_test 1.$tn.13.6 "
   281    281       SELECT lag(b,b) OVER (PARTITION BY b%2,a ORDER BY b%10 $window) FROM t2
   282    282     "
          283  +
          284  +  execsql_test 1.$tn.14.1 "
          285  +    SELECT string_agg(CAST(b AS TEXT), '.') OVER (ORDER BY a $window) FROM t2
          286  +  "
          287  +
          288  +  execsql_test 1.$tn.14.2 "
          289  +    SELECT string_agg(CAST(b AS TEXT), '.') OVER (PARTITION BY b%10 ORDER BY a $window) FROM t2
          290  +  "
          291  +  execsql_test 1.$tn.14.3 "
          292  +    SELECT string_agg(CAST(b AS TEXT), '.') OVER ( ORDER BY b,a $window ) FROM t2
          293  +  "
          294  +  execsql_test 1.$tn.14.4 "
          295  +    SELECT string_agg(CAST(b AS TEXT), '.') OVER ( PARTITION BY b%10 ORDER BY b,a $window ) FROM t2
          296  +  "
          297  +  execsql_test 1.$tn.14.5 "
          298  +    SELECT string_agg(CAST(b AS TEXT), '.') OVER ( ORDER BY b%10,a $window ) FROM t2
          299  +  "
          300  +  execsql_test 1.$tn.14.6 "
          301  +    SELECT string_agg(CAST(b AS TEXT), '.') OVER (PARTITION BY b%2,a ORDER BY b%10 $window) FROM t2
          302  +  "
   283    303   }
   284    304   
   285    305   finish_test
   286    306   

Changes to test/window3.test.

cannot compute difference between binary files

Changes to test/window4.tcl.

    61     61   }
    62     62   execsql_test 2.3.2 {
    63     63     SELECT a, lag(b, 2) OVER (ORDER BY a) FROM t4
    64     64   }
    65     65   execsql_test 2.3.3 {
    66     66     SELECT a, lag(b, 3, 'abc') OVER (ORDER BY a) FROM t4
    67     67   }
           68  +
           69  +execsql_test 2.4.1 {
           70  +  SELECT string_agg(b, '.') OVER (
           71  +    ORDER BY a ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
           72  +  ) FROM t4
           73  +}
    68     74   
    69     75   execsql_test 3.0 {
    70     76     DROP TABLE IF EXISTS t5;
    71     77     CREATE TABLE t5(a INTEGER PRIMARY KEY, b TEXT, c TEXT, d INTEGER);
    72     78     INSERT INTO t5 VALUES(1, 'A', 'one',   5);
    73     79     INSERT INTO t5 VALUES(2, 'B', 'two',   4);
    74     80     INSERT INTO t5 VALUES(3, 'A', 'three', 3);

Changes to test/window4.test.

   141    141     SELECT a, lag(b, 2) OVER (ORDER BY a) FROM t4
   142    142   } {1 {}   2 {}   3 A   4 B   5 C   6 D   7 E   8 F   9 G   10 H}
   143    143   
   144    144   do_execsql_test 2.3.3 {
   145    145     SELECT a, lag(b, 3, 'abc') OVER (ORDER BY a) FROM t4
   146    146   } {1 abc   2 abc   3 abc   4 A   5 B   6 C   7 D   8 E   9 F   10 G}
   147    147   
          148  +do_execsql_test 2.4.1 {
          149  +  SELECT group_concat(b, '.') OVER (
          150  +    ORDER BY a ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
          151  +  ) FROM t4
          152  +} {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}
          153  +
   148    154   do_execsql_test 3.0 {
   149    155     DROP TABLE IF EXISTS t5;
   150    156     CREATE TABLE t5(a INTEGER PRIMARY KEY, b TEXT, c TEXT, d INTEGER);
   151    157     INSERT INTO t5 VALUES(1, 'A', 'one',   5);
   152    158     INSERT INTO t5 VALUES(2, 'B', 'two',   4);
   153    159     INSERT INTO t5 VALUES(3, 'A', 'three', 3);
   154    160     INSERT INTO t5 VALUES(4, 'B', 'four',  2);