Index: src/func.c ================================================================== --- src/func.c +++ src/func.c @@ -2045,10 +2045,11 @@ minMaxValueFinalize(context, 0); } /* ** group_concat(EXPR, ?SEPARATOR?) +** string_agg(EXPR, SEPARATOR) ** ** The SEPARATOR goes before the EXPR string. This is tragic. The ** groupConcatInverse() implementation would have been easier if the ** SEPARATOR were appended after EXPR. And the order is undocumented, ** so we could change it, in theory. But the old behavior has been @@ -2668,10 +2669,12 @@ WAGGREGATE(count, 1,0,0, countStep, countFinalize, countFinalize, countInverse, SQLITE_FUNC_ANYORDER ), WAGGREGATE(group_concat, 1, 0, 0, groupConcatStep, groupConcatFinalize, groupConcatValue, groupConcatInverse, 0), WAGGREGATE(group_concat, 2, 0, 0, groupConcatStep, + groupConcatFinalize, groupConcatValue, groupConcatInverse, 0), + WAGGREGATE(string_agg, 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), Index: test/aggnested.test ================================================================== --- test/aggnested.test +++ test/aggnested.test @@ -23,23 +23,23 @@ db eval { CREATE TABLE t1(a1 INTEGER); INSERT INTO t1 VALUES(1), (2), (3); CREATE TABLE t2(b1 INTEGER); INSERT INTO t2 VALUES(4), (5); - SELECT (SELECT group_concat(a1,'x') FROM t2) FROM t1; + SELECT (SELECT string_agg(a1,'x') FROM t2) FROM t1; } } {1x2x3} do_test aggnested-1.2 { db eval { SELECT - (SELECT group_concat(a1,'x') || '-' || group_concat(b1,'y') FROM t2) + (SELECT string_agg(a1,'x') || '-' || string_agg(b1,'y') FROM t2) FROM t1; } } {1x2x3-4y5} do_test aggnested-1.3 { db eval { - SELECT (SELECT group_concat(b1,a1) FROM t2) FROM t1; + SELECT (SELECT string_agg(b1,a1) FROM t2) FROM t1; } } {415 425 435} do_test aggnested-1.4 { db eval { SELECT (SELECT group_concat(a1,b1) FROM t2) FROM t1; @@ -307,11 +307,11 @@ } {{}} do_execsql_test 5.5 { CREATE TABLE a(b); WITH c AS(SELECT a) - SELECT(SELECT(SELECT group_concat(b, b) + SELECT(SELECT(SELECT string_agg(b, b) LIMIT(SELECT 0.100000 * AVG(DISTINCT(SELECT 0 FROM a ORDER BY b, b, b)))) FROM a GROUP BY b, b, b) FROM a EXCEPT SELECT b FROM a ORDER BY b, b, b; Index: test/aggorderby.test ================================================================== --- test/aggorderby.test +++ test/aggorderby.test @@ -36,11 +36,11 @@ } {0,7,4,1,8,5,2,9,6,3} do_execsql_test aggorderby-2.2 { SELECT group_concat(a ORDER BY b, d) FROM t1; } {3,6,9,2,5,8,1,4,7,0,0,7,4,1,8,5,2,9,6,3} do_execsql_test aggorderby-2.3 { - SELECT group_concat(a ORDER BY b DESC, d) FROM t1; + SELECT string_agg(a, ',' ORDER BY b DESC, d) FROM t1; } {0,7,4,1,8,5,2,9,6,3,3,6,9,2,5,8,1,4,7,0} do_execsql_test aggorderby-2.4 { SELECT b, group_concat(a ORDER BY d) FROM t1 GROUP BY b ORDER BY b; } {1 3,6,9,2,5,8,1,4,7,0 2 0,7,4,1,8,5,2,9,6,3} @@ -62,18 +62,18 @@ do_execsql_test aggorderby-5.0 { DROP TABLE IF EXISTS t1; DROP TABLE IF EXISTS t3; CREATE TABLE t1(a TEXT); INSERT INTO t1 VALUES('aaa'),('bbb'); CREATE TABLE t3(d TEXT); INSERT INTO t3 VALUES('/'),('-'); - SELECT (SELECT group_concat(a,d) FROM t3) FROM t1; + SELECT (SELECT string_agg(a,d) FROM t3) FROM t1; } {aaa-aaa bbb-bbb} do_execsql_test aggorderby-5.1 { SELECT (SELECT group_concat(a,d ORDER BY d) FROM t3) FROM t1; } {aaa/aaa bbb/bbb} do_execsql_test aggorderby-5.2 { - SELECT (SELECT group_concat(a,d ORDER BY d DESC) FROM t3) FROM t1; + SELECT (SELECT string_agg(a,d ORDER BY d DESC) FROM t3) FROM t1; } {aaa-aaa bbb-bbb} do_execsql_test aggorderby-5.3 { - SELECT (SELECT group_concat(a,'#' ORDER BY d) FROM t3) FROM t1; + SELECT (SELECT string_agg(a,'#' ORDER BY d) FROM t3) FROM t1; } {aaa#aaa bbb#bbb} finish_test Index: test/alterqf.test ================================================================== --- test/alterqf.test +++ test/alterqf.test @@ -63,19 +63,19 @@ 10 {CREATE TABLE t2(abc, xyz CHECK (xyz != "123"))} {CREATE TABLE t2(abc, xyz CHECK (xyz != '123'))} 11 {CREATE TRIGGER ott AFTER UPDATE ON t1 BEGIN SELECT max("str", new."a") FROM t1 - WHERE group_concat("b", ",") OVER (ORDER BY c||"str"); + WHERE string_agg("b", ",") OVER (ORDER BY c||"str"); UPDATE t1 SET c= b + "str"; DELETE FROM t1 WHERE EXISTS ( SELECT 1 FROM t1 AS o WHERE o."a" = "o.a" AND t1.b IN("t1.b") ); END; } {CREATE TRIGGER ott AFTER UPDATE ON t1 BEGIN SELECT max('str', new."a") FROM t1 - WHERE group_concat("b", ',') OVER (ORDER BY c||'str'); + WHERE string_agg("b", ',') OVER (ORDER BY c||'str'); UPDATE t1 SET c= b + 'str'; DELETE FROM t1 WHERE EXISTS ( SELECT 1 FROM t1 AS o WHERE o."a" = 'o.a' AND t1.b IN('t1.b') ); END; Index: test/altertrig.test ================================================================== --- test/altertrig.test +++ test/altertrig.test @@ -158,6 +158,5 @@ END }" } finish_test - Index: test/bestindex9.test ================================================================== --- test/bestindex9.test +++ test/bestindex9.test @@ -100,9 +100,5 @@ SELECT DISTINCT c1 FROM t1, t2 } {{column 0 desc 0}} 2 finish_test - - - - Index: test/bestindexA.test ================================================================== --- test/bestindexA.test +++ test/bestindexA.test @@ -131,8 +131,5 @@ {limit 0} } finish_test - - - Index: test/changes.test ================================================================== --- test/changes.test +++ test/changes.test @@ -84,7 +84,5 @@ db total_changes } [expr {2*($nBig+1)}] } finish_test - - Index: test/changes2.test ================================================================== --- test/changes2.test +++ test/changes2.test @@ -90,6 +90,5 @@ do_execsql_test 2.4 { SELECT * FROM log; } {{2 changes} {2 changes}} finish_test - Index: test/dbpagefault.test ================================================================== --- test/dbpagefault.test +++ test/dbpagefault.test @@ -82,7 +82,5 @@ # faultsim_test_result {0 {}} #} finish_test - - Index: test/distinctagg.test ================================================================== --- test/distinctagg.test +++ test/distinctagg.test @@ -54,11 +54,11 @@ SELECT count(distinct) FROM t1; } } {1 {DISTINCT aggregates must have exactly one argument}} do_test distinctagg-2.2 { catchsql { - SELECT group_concat(distinct a,b) FROM t1; + SELECT string_agg(distinct a,b) FROM t1; } } {1 {DISTINCT aggregates must have exactly one argument}} #-------------------------------------------------------------------------- reset_db @@ -213,6 +213,5 @@ SELECT COUNT ( DISTINCT TRUE ) FROM v1 GROUP BY likelihood ( v3 , 0.100000 ); } finish_test - Index: test/e_expr.test ================================================================== --- test/e_expr.test +++ test/e_expr.test @@ -1928,11 +1928,11 @@ 4 { ( SELECT x FROM t4 ORDER BY x DESC ) } integer 3 5 { ( SELECT x FROM t4 ORDER BY y DESC ) } integer 2 6 { ( SELECT y FROM t4 ORDER BY y DESC ) } text two 7 { ( SELECT sum(x) FROM t4 ) } integer 6 - 8 { ( SELECT group_concat(y,'') FROM t4 ) } text onetwothree + 8 { ( SELECT string_agg(y,'') FROM t4 ) } text onetwothree 9 { ( SELECT max(x) FROM t4 WHERE y LIKE '___') } integer 2 } { do_expr_test e_expr-36.3.$tn $expr $restype $resval } Index: test/e_select.test ================================================================== --- test/e_select.test +++ test/e_select.test @@ -941,11 +941,11 @@ 2 "SELECT one, two, count(*) FROM a1 WHERE one<3" {1 1 2} 3 "SELECT one, two, count(*) FROM a1 WHERE one>3" {4 10 1} 4 "SELECT *, count(*) FROM a1 JOIN a2" {1 1 1 1 16} 5 "SELECT *, sum(three) FROM a1 NATURAL JOIN a2" {1 1 1 3} 6 "SELECT *, sum(three) FROM a1 NATURAL JOIN a2" {1 1 1 3} - 7 "SELECT group_concat(three, ''), a1.* FROM a1 NATURAL JOIN a2" {12 1 1} + 7 "SELECT string_agg(three, ''), a1.* FROM a1 NATURAL JOIN a2" {12 1 1} } # EVIDENCE-OF: R-04486-07266 Or, if the dataset contains zero rows, then # each non-aggregate expression is evaluated against a row consisting # entirely of NULL values. Index: test/filter2.test ================================================================== --- test/filter2.test +++ test/filter2.test @@ -111,11 +111,11 @@ ORDER BY 2 } {2 25 3 0 34 2 1 34 4 4 46 4 3 49 5} do_execsql_test 1.13 { SELECT - group_concat(CAST(b AS TEXT), '_') FILTER (WHERE b%2!=0), + string_agg(CAST(b AS TEXT), '_') FILTER (WHERE b%2!=0), group_concat(CAST(b AS TEXT), '_') FILTER (WHERE b%2!=1), count(*) FILTER (WHERE b%2!=0), count(*) FILTER (WHERE b%2!=1) FROM t1; } {7_3_5_23_27_3_17_33_25_47_13_45_31_11_37_21_3_7_29_3_3_23_5_11_25_15_23 30_26_26_36_36_22_14_16_50_38_36_12_4_46_48_24_46_18_18 27 19} Index: test/fts4merge.test ================================================================== --- test/fts4merge.test +++ test/fts4merge.test @@ -35,11 +35,11 @@ # Test cases 1.* # do_test 1.0 { fts3_build_db_1 -module $mod 1004 } {} do_test 1.1 { fts3_integrity_check t1 } {ok} do_execsql_test 1.1 { - SELECT level, group_concat(idx, ' ') FROM t1_segdir GROUP BY level + SELECT level, string_agg(idx, ' ') FROM t1_segdir GROUP BY level } { 0 {0 1 2 3 4 5 6 7 8 9 10 11} 1 {0 1 2 3 4 5 6 7 8 9 10 11 12 13} 2 {0 1 2} } @@ -65,11 +65,11 @@ SELECT docid FROM t1 WHERE t1 MATCH 'zero one two three' } {123 132 213 231 312 321} } do_execsql_test 1.5 { - SELECT level, group_concat(idx, ' ') FROM t1_segdir GROUP BY level + SELECT level, string_agg(idx, ' ') FROM t1_segdir GROUP BY level } { 3 0 } #------------------------------------------------------------------------- @@ -101,11 +101,11 @@ fts3_build_db_2 -module $mod 30040 } {} do_test 3.1 { fts3_integrity_check t2 } {ok} do_execsql_test 3.2 { - SELECT level, group_concat(idx, ' ') FROM t2_segdir GROUP BY level + SELECT level, string_agg(idx, ' ') FROM t2_segdir GROUP BY level } { 0 {0 1 2 3 4 5 6} 1 {0 1 2 3 4} 2 {0 1 2 3 4} 3 {0 1 2 3 4 5 6} @@ -130,11 +130,11 @@ do_test 4.2 { foreach x {a c b d e f g h i j k l m n o p} { execsql "INSERT INTO t4 VALUES('[string repeat $x 600]')" } - execsql {SELECT level, group_concat(idx, ' ') FROM t4_segdir GROUP BY level} + execsql {SELECT level, string_agg(idx, ' ') FROM t4_segdir GROUP BY level} } {0 {0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15}} foreach {tn expect} { 1 "0 {0 1 2 3 4 5 6 7 8 9 10 11 12 13} 1 0" 2 "0 {0 1 2 3 4 5 6 7 8 9 10 11 12} 1 0" @@ -158,11 +158,11 @@ sqlite3_db_config db DEFENSIVE 0 do_execsql_test 4.4.2 { DELETE FROM t4_stat WHERE rowid=1; INSERT INTO t4(t4) VALUES('merge=1,12'); - SELECT level, group_concat(idx, ' ') FROM t4_segdir GROUP BY level; + SELECT level, string_agg(idx, ' ') FROM t4_segdir GROUP BY level; } "0 {0 1 2 3 4 5} 1 0" #------------------------------------------------------------------------- # Test cases 5.* @@ -192,11 +192,11 @@ } do_execsql_test 5.3 { INSERT INTO t1(t1) VALUES('merge=1,5'); INSERT INTO t1(t1) VALUES('merge=1,5'); - SELECT level, group_concat(idx, ' ') FROM t1_segdir GROUP BY level; + SELECT level, string_agg(idx, ' ') FROM t1_segdir GROUP BY level; } { 1 {0 1 2 3 4 5 6 7 8 9 10 11 12 13 14} 2 {0 1 2 3} } @@ -247,11 +247,11 @@ 0 {0 1 2 3 4 5 6 7 8 9 10 11} 1 0 2 0 3 0 X'010E' } do_execsql_test 5.11 { INSERT INTO t1(t1) VALUES('merge=1,6'); - SELECT level, group_concat(idx, ' ') FROM t1_segdir GROUP BY level; + SELECT level, string_agg(idx, ' ') FROM t1_segdir GROUP BY level; SELECT quote(value) from t1_stat WHERE rowid=1; } { 1 {0 1} 2 0 3 0 X'010E' } Index: test/func.test ================================================================== --- test/func.test +++ test/func.test @@ -1134,22 +1134,22 @@ SELECT legacy_count() FROM t6; } } {3} } -# The group_concat() function. +# The group_concat() and string_agg() functions. # do_test func-24.1 { execsql { - SELECT group_concat(t1) FROM tbl1 + SELECT group_concat(t1), string_agg(t1,',') FROM tbl1 } -} {this,program,is,free,software} +} {this,program,is,free,software this,program,is,free,software} do_test func-24.2 { execsql { - SELECT group_concat(t1,' ') FROM tbl1 + SELECT group_concat(t1,' '), string_agg(t1,' ') FROM tbl1 } -} {{this program is free software}} +} {{this program is free software} {this program is free software}} do_test func-24.3 { execsql { SELECT group_concat(t1,' ' || rowid || ' ') FROM tbl1 } } {{this 2 program 3 is 4 free 5 software}} @@ -1158,13 +1158,13 @@ SELECT group_concat(NULL,t1) FROM tbl1 } } {{}} do_test func-24.5 { execsql { - SELECT group_concat(t1,NULL) FROM tbl1 + SELECT group_concat(t1,NULL), string_agg(t1,NULL) FROM tbl1 } -} {thisprogramisfreesoftware} +} {thisprogramisfreesoftware thisprogramisfreesoftware} do_test func-24.6 { execsql { SELECT 'BEGIN-'||group_concat(t1) FROM tbl1 } } {BEGIN-this,program,is,free,software} Index: test/gcfault.test ================================================================== --- test/gcfault.test +++ test/gcfault.test @@ -8,11 +8,11 @@ # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing OOM error handling within the built-in -# group_concat() function. +# group_concat() and string_agg() functions. # set testdir [file dirname $argv0] source $testdir/tester.tcl set testprefix gcfault @@ -38,11 +38,11 @@ do_faultsim_test 1.$enc.1 -faults oom* -body { execsql { SELECT group_concat(e, (SELECT s FROM s WHERE i=1)) FROM e } } do_faultsim_test 1.$enc.2 -faults oom-t* -body { - execsql { SELECT group_concat(e, (SELECT s FROM s WHERE i=2)) FROM e } + execsql { SELECT string_agg(e, (SELECT s FROM s WHERE i=2)) FROM e } } do_faultsim_test 1.$enc.3 -faults oom-t* -prep { set ::STMT [sqlite3_prepare db {SELECT group_concat(e, ?) FROM e} -1 dummy] sqlite3_bind_text $::STMT 1 ",0123456789," 12 Index: test/indexA.test ================================================================== --- test/indexA.test +++ test/indexA.test @@ -346,6 +346,5 @@ } { 1 4 1 2 4 2 } finish_test - Index: test/joinH.test ================================================================== --- test/joinH.test +++ test/joinH.test @@ -250,6 +250,5 @@ SELECT oid FROM wo2 JOIN (wo3 JOIN x3) } {0 99} finish_test - Index: test/memdb2.test ================================================================== --- test/memdb2.test +++ test/memdb2.test @@ -72,6 +72,5 @@ db close db2 close } finish_test - Index: test/memjournal2.test ================================================================== --- test/memjournal2.test +++ test/memjournal2.test @@ -57,7 +57,5 @@ } {ok} } finish_test - - Index: test/pendingrace.test ================================================================== --- test/pendingrace.test +++ test/pendingrace.test @@ -119,8 +119,5 @@ db2 close tvfs delete tvfs2 delete finish_test - - - Index: test/quickcheck.test ================================================================== --- test/quickcheck.test +++ test/quickcheck.test @@ -29,6 +29,5 @@ } { ok } finish_test - Index: test/rowvalue9.test ================================================================== --- test/rowvalue9.test +++ test/rowvalue9.test @@ -348,7 +348,5 @@ *SEARCH t1 USING COVERING INDEX i2* } finish_test - - Index: test/rowvalueA.test ================================================================== --- test/rowvalueA.test +++ test/rowvalueA.test @@ -72,6 +72,5 @@ do_catchsql_test 2.3 { SELECT 2 IN ( (1, 2), (3, 4), (5, 6) ) } {1 {row value misused}} finish_test - Index: test/scanstatus2.test ================================================================== --- test/scanstatus2.test +++ test/scanstatus2.test @@ -330,7 +330,5 @@ #explain_i { SELECT (a % 2), group_concat(b) FROM t1 GROUP BY 1 } #puts_debug_info { SELECT (a % 2), group_concat(b) FROM t1 GROUP BY 1 } finish_test - - Index: test/select3.test ================================================================== --- test/select3.test +++ test/select3.test @@ -432,6 +432,5 @@ } { hello 59 60 } finish_test - Index: test/statfault.test ================================================================== --- test/statfault.test +++ test/statfault.test @@ -50,6 +50,5 @@ } -test { faultsim_test_result {0 {}} } finish_test - Index: test/table.test ================================================================== --- test/table.test +++ test/table.test @@ -782,17 +782,17 @@ INSERT INTO t16(rowid) VALUES(123); SELECT rowid, x FROM t16; } {1 {unknown function: count()}} do_catchsql_test table-16.6 { DROP TABLE t16; - CREATE TABLE t16(x DEFAULT(group_concat('x',','))); + CREATE TABLE t16(x DEFAULT(string_agg('x',','))); INSERT INTO t16(rowid) VALUES(123); SELECT rowid, x FROM t16; -} {1 {unknown function: group_concat()}} +} {1 {unknown function: string_agg()}} do_catchsql_test table-16.7 { INSERT INTO t16 DEFAULT VALUES; -} {1 {unknown function: group_concat()}} +} {1 {unknown function: string_agg()}} # Ticket [https://www.sqlite.org/src/info/094d39a4c95ee4abbc417f04214617675ba15c63] # describes a assertion fault that occurs on a CREATE TABLE .. AS SELECT statement. # the following test verifies that the problem has been fixed. # Index: test/thread3.test ================================================================== --- test/thread3.test +++ test/thread3.test @@ -73,6 +73,5 @@ do_execsql_test "1.Total BUSY errors: $nTotalBusy .2" { SELECT count(*) FROM t1; } $nAttempt finish_test - Index: test/tkt-cbd054fa6b.test ================================================================== --- test/tkt-cbd054fa6b.test +++ test/tkt-cbd054fa6b.test @@ -63,11 +63,11 @@ PRAGMA writable_schema = 0; } } {} do_test tkt-cbd05-1.3 { execsql { - SELECT tbl,idx,group_concat(s(sample),' ') + SELECT tbl,idx,string_agg(s(sample),' ') FROM vvv WHERE idx = 't1_x' GROUP BY tbl,idx } } {t1 t1_x { A B C D E F G H I}} Index: test/trigger2.test ================================================================== --- test/trigger2.test +++ test/trigger2.test @@ -788,6 +788,5 @@ END; } {1 {trigger cannot use variables}} finish_test - Index: test/unhex.test ================================================================== --- test/unhex.test +++ test/unhex.test @@ -98,7 +98,5 @@ do_execsql_test 6.4.2 { SELECT typeof(unhex(NULL, ' ')) } {null} do_execsql_test 6.4.3 { SELECT typeof(unhex('1234', NULL)) } {null} finish_test - - Index: test/vacuum-into.test ================================================================== --- test/vacuum-into.test +++ test/vacuum-into.test @@ -183,8 +183,5 @@ db close tvfs delete finish_test - - - Index: test/walseh1.test ================================================================== --- test/walseh1.test +++ test/walseh1.test @@ -144,7 +144,5 @@ if {$testrc} { test_system_errno db $::seh_errno } } catch { db close } finish_test - - Index: test/window1.test ================================================================== --- test/window1.test +++ test/window1.test @@ -156,11 +156,11 @@ } do_execsql_test 4.10.1 { SELECT a, count() OVER (ORDER BY a DESC), - group_concat(a, '.') OVER (ORDER BY a DESC) + string_agg(a, '.') OVER (ORDER BY a DESC) FROM t2 ORDER BY a DESC } { 6 1 6 5 2 6.5 4 3 6.5.4 @@ -823,11 +823,11 @@ } { do_catchsql_test 18.2.$tn $sql [list 1 $error] } do_execsql_test 18.3.1 { - SELECT group_concat(c, '.') OVER (PARTITION BY b ORDER BY c) + SELECT string_agg(c, '.') OVER (PARTITION BY b ORDER BY c) FROM t1 } {four four.six four.six.two five five.one five.one.three} do_execsql_test 18.3.2 { SELECT group_concat(c, '.') OVER (win1 ORDER BY c) @@ -834,11 +834,11 @@ FROM t1 WINDOW win1 AS (PARTITION BY b) } {four four.six four.six.two five five.one five.one.three} do_execsql_test 18.3.3 { - SELECT group_concat(c, '.') OVER win2 + SELECT string_agg(c, '.') OVER win2 FROM t1 WINDOW win1 AS (PARTITION BY b), win2 AS (win1 ORDER BY c) } {four four.six four.six.two five five.one five.one.three} @@ -848,11 +848,11 @@ WINDOW win1 AS (PARTITION BY b), win2 AS (win1 ORDER BY c) } {four four.six four.six.two five five.one five.one.three} do_execsql_test 18.3.5 { - SELECT group_concat(c, '.') OVER win5 + SELECT string_agg(c, '.') OVER win5 FROM t1 WINDOW win1 AS (PARTITION BY b), win2 AS (win1), win3 AS (win2), win4 AS (win3), @@ -1125,11 +1125,11 @@ INSERT INTO t1 VALUES (3, 'C', 'cc', 1.0); INSERT INTO t1 VALUES (13,'M', 'cc', NULL); } do_execsql_test 28.1.2 { - SELECT group_concat(b,'') OVER w1 FROM t1 + SELECT string_agg(b,'') OVER w1 FROM t1 WINDOW w1 AS (ORDER BY a RANGE BETWEEN 3 PRECEDING AND 1 PRECEDING) } { {} {} } Index: test/window3.test ================================================================== --- test/window3.test +++ test/window3.test @@ -1179,11 +1179,11 @@ {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {} {}} do_execsql_test 1.1.14.1 { - SELECT group_concat(CAST(b AS TEXT), '.') OVER (ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM t2 + SELECT string_agg(CAST(b AS TEXT), '.') OVER (ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM t2 } {89 89.81 89.81.96 89.81.96.59 89.81.96.59.38 89.81.96.59.38.68 89.81.96.59.38.68.39 89.81.96.59.38.68.39.62 89.81.96.59.38.68.39.62.91 89.81.96.59.38.68.39.62.91.46 89.81.96.59.38.68.39.62.91.46.6 89.81.96.59.38.68.39.62.91.46.6.99 89.81.96.59.38.68.39.62.91.46.6.99.97 89.81.96.59.38.68.39.62.91.46.6.99.97.27 @@ -1469,11 +1469,11 @@ 89.59.39.99.29.59.89.89.29.9.79.49.59.29.59.19.39.9.9.99 89.59.39.99.29.59.89.89.29.9.79.49.59.29.59.19.39.9.9.99.69 89.59.39.99.29.59.89.89.29.9.79.49.59.29.59.19.39.9.9.99.69.39} do_execsql_test 1.1.14.3 { - SELECT group_concat(CAST(b AS TEXT), '.') OVER ( ORDER BY b,a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) FROM t2 + SELECT string_agg(CAST(b AS TEXT), '.') OVER ( ORDER BY b,a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) FROM t2 } {1 1.1 1.1.2 1.1.2.2 1.1.2.2.3 1.1.2.2.3.3 1.1.2.2.3.3.4 1.1.2.2.3.3.4.5 1.1.2.2.3.3.4.5.6 1.1.2.2.3.3.4.5.6.7 1.1.2.2.3.3.4.5.6.7.7 1.1.2.2.3.3.4.5.6.7.7.7 1.1.2.2.3.3.4.5.6.7.7.7.8 1.1.2.2.3.3.4.5.6.7.7.7.8.8 1.1.2.2.3.3.4.5.6.7.7.7.8.8.8 1.1.2.2.3.3.4.5.6.7.7.7.8.8.8.9 1.1.2.2.3.3.4.5.6.7.7.7.8.8.8.9.9 @@ -1756,11 +1756,11 @@ 9.9.9.19.29.29.29.39.39.39.49.59.59.59.59.69.79.89.89.89 9.9.9.19.29.29.29.39.39.39.49.59.59.59.59.69.79.89.89.89.99 9.9.9.19.29.29.29.39.39.39.49.59.59.59.59.69.79.89.89.89.99.99} do_execsql_test 1.1.14.5 { - SELECT group_concat(CAST(b AS TEXT), '.') OVER ( ORDER BY b%10,a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) FROM t2 + SELECT string_agg(CAST(b AS TEXT), '.') OVER ( ORDER BY b%10,a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) FROM t2 } {90 90.40 90.40.30 90.40.30.80 90.40.30.80.20 90.40.30.80.20.90 90.40.30.80.20.90.60 90.40.30.80.20.90.60.70 90.40.30.80.20.90.60.70.80 90.40.30.80.20.90.60.70.80.90 90.40.30.80.20.90.60.70.80.90.30 90.40.30.80.20.90.60.70.80.90.30.50 90.40.30.80.20.90.60.70.80.90.30.50.10 @@ -1958,11 +1958,11 @@ 55 75 77 7 85 57 29 59 19 39 47 47 9 33 93 75 81 9 23 37 13 91 91 33 15 99 3 95 69 33 21 39 83 27 17 7} do_execsql_test 1.1.14.7 { - SELECT group_concat(CAST(b AS TEXT), '.') OVER (win1 ORDER BY b%10 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) + SELECT string_agg(CAST(b AS TEXT), '.') OVER (win1 ORDER BY b%10 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM t2 WINDOW win1 AS (PARTITION BY b%2,a) ORDER BY 1 } {1 1 10 11 11 12 12 12 13 13 14 15 15 15 16 16 16 17 19 2 2 20 21 21 22 22 23 23 24 25 26 26