SQLite

#endif /* SQLITE_OMIT_WINDOWFUNC */
static void minMaxFinalize(sqlite3_context *context){
  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
** around for so long that we dare not, for fear of breaking something.
*/

        countFinalize, countFinalize, countInverse,
        SQLITE_FUNC_COUNT|SQLITE_FUNC_ANYORDER  ),
    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),
    LIKEFUNC(like, 3, &likeInfoAlt, SQLITE_FUNC_LIKE|SQLITE_FUNC_CASE),
#else

do_test aggnested-1.1 {
  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 string_agg(a1,'x') FROM t2) FROM t1;
  }
} {1x2x3}
do_test aggnested-1.2 {
  db eval {
    SELECT
     (SELECT string_agg(a1,'x') || '-' || string_agg(b1,'y') FROM t2)
    FROM t1;
  }
} {1x2x3-4y5}
do_test aggnested-1.3 {
  db eval {
    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;
  }
} {151 252 353}

  )
  FROM t2;
} {{}}

do_execsql_test 5.5 {
  CREATE TABLE a(b);
  WITH c AS(SELECT a)
    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;
}

do_execsql_test aggorderby-2.1 {
  SELECT group_concat(a ORDER BY c) FROM t1 WHERE b=1;
} {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 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}

do_execsql_test aggorderby-3.0 {
  SELECT group_concat(DISTINCT a ORDER BY a) FROM t1;

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 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 string_agg(a,d ORDER BY d DESC) FROM t3) FROM t1;
} {aaa-aaa bbb-bbb}
do_execsql_test aggorderby-5.3 {
  SELECT (SELECT string_agg(a,'#' ORDER BY d) FROM t3) FROM t1;
} {aaa#aaa bbb#bbb}

finish_test

    {CREATE INDEX i1 ON t1(a) WHERE "b"='bb'}

 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 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 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 r1 INSERT ON t1 BEGIN 
      $final;
    END
  }"
}

finish_test

} {
  CREATE TABLE t2(balls);
  SELECT DISTINCT c1 FROM t1, t2
} {{column 0 desc 0}} 2


finish_test

} {
  {eq 0}
  {limit 0}
}


finish_test

  
  do_test 1.$tn.9 {
    db total_changes
  } [expr {2*($nBig+1)}]
}

finish_test

sqlite3_finalize $::stmt

do_execsql_test 2.4 {
  SELECT * FROM log;
} {{2 changes} {2 changes}}

finish_test

#  execsql { INSERT INTO x1 DEFAULT VALUES; }
#} -test {
#  faultsim_test_result {0 {}}
#}


finish_test

do_test distinctagg-2.1 {
  catchsql {
    SELECT count(distinct) FROM t1;
  }
} {1 {DISTINCT aggregates must have exactly one argument}}
do_test distinctagg-2.2 {
  catchsql {
    SELECT string_agg(distinct a,b) FROM t1;
  }
} {1 {DISTINCT aggregates must have exactly one argument}}

#--------------------------------------------------------------------------
reset_db
do_execsql_test 3.0 {
  CREATE TABLE t1(a, b, c);

do_execsql_test 7.0 {
  CREATE TABLE v1 ( v2 UNIQUE, v3 AS( TYPEOF ( NULL ) ) UNIQUE ); 
  SELECT COUNT ( DISTINCT TRUE ) FROM v1 GROUP BY likelihood ( v3 , 0.100000 );
}


finish_test

    2  { ( SELECT x FROM t4 ORDER BY x )      }        integer 1
    3  { ( SELECT x FROM t4 ORDER BY y )      }        integer 1
    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 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
}

# EVIDENCE-OF: R-52325-25449 The value of a subquery expression is NULL

do_select_tests e_select-4.6 {
  1 "SELECT one, two, count(*) FROM a1"                        {1 1 4}
  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 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.
#
do_select_tests e_select-4.7 {

    count(distinct b) FILTER (WHERE b<20 OR a=13) AS ccc
  FROM t1 GROUP BY (a%5)
  ORDER BY 2
} {2 25 3   0 34 2   1 34 4   4 46 4   3 49 5}

do_execsql_test 1.13 {
  SELECT 
    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}

  #-------------------------------------------------------------------------
  # 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, 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}
  }
  
  for {set i 0} {$i<20} {incr i} {

    do_test 1.4.$i.2 { fts3_integrity_check t1 } ok
    do_execsql_test 1.4.$i.3 { 
      SELECT docid FROM t1 WHERE t1 MATCH 'zero one two three'
    } {123 132 213 231 312 321}
  }
  
  do_execsql_test 1.5 { 
    SELECT level, string_agg(idx, ' ') FROM t1_segdir GROUP BY level 
  } {
    3 0
  }
  
  #-------------------------------------------------------------------------
  # Test cases 2.* test that errors in the xxx part of the 'merge=xxx' are
  # handled correctly.

    reset_db
    execsql { PRAGMA page_size = 512 }
    fts3_build_db_2 -module $mod 30040 
  } {}
  do_test 3.1 { fts3_integrity_check t2 } {ok}
  
  do_execsql_test 3.2 { 
    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}
  }
  

    PRAGMA main.page_size;
  " {512}
  
  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, 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"
    3  "0 {0 1 2 3 4 5 6 7 8 9 10 11}       1 0"
    4  "0 {0 1 2 3 4 5 6 7 8 9 10}          1 0"

    SELECT quote(value) FROM t4_stat WHERE rowid=1
  } {X'0006'}
  
  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, string_agg(idx, ' ') FROM t4_segdir GROUP BY level;
  } "0 {0 1 2 3 4 5}                     1 0"
  
  
  #-------------------------------------------------------------------------
  # Test cases 5.*
  #
  # Test that if a crisis-merge occurs that disrupts an ongoing incremental

    1 {0 1 2 3 4 5 6 7 8 9 10 11 12 13} 
    2 {0 1 2}
  }
  
  do_execsql_test 5.3 {
    INSERT INTO t1(t1) VALUES('merge=1,5');
    INSERT INTO t1(t1) VALUES('merge=1,5');
    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}
  }
  
  do_execsql_test 5.4 {SELECT quote(value) from t1_stat WHERE rowid=1} {X'010F'}
  do_test 5.5 {

    SELECT quote(value) from t1_stat WHERE rowid=1;
  } {
    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, 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'
  }
  
  #-------------------------------------------------------------------------
  # Test cases 6.*

    sqlite3_create_aggregate db
    execsql {
      SELECT legacy_count() FROM t6;
    }
  } {3}
}

# The group_concat() and string_agg() functions.
#
do_test func-24.1 {
  execsql {
    SELECT group_concat(t1), string_agg(t1,',') FROM tbl1
  }
} {this,program,is,free,software this,program,is,free,software}
do_test func-24.2 {
  execsql {
    SELECT group_concat(t1,' '), string_agg(t1,' ') FROM tbl1
  }
} {{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}}
do_test func-24.4 {
  execsql {
    SELECT group_concat(NULL,t1) FROM tbl1
  }
} {{}}
do_test func-24.5 {
  execsql {
    SELECT group_concat(t1,NULL), string_agg(t1,NULL) FROM tbl1
  }
} {thisprogramisfreesoftware thisprogramisfreesoftware}
do_test func-24.6 {
  execsql {
    SELECT 'BEGIN-'||group_concat(t1) FROM tbl1
  }
} {BEGIN-this,program,is,free,software}

# Ticket #3179:  Make sure aggregate functions can take many arguments.

# 2016 December 30
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    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() and string_agg() functions.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix gcfault

  } {}

  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 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
  } -body {
    while { "SQLITE_ROW"==[sqlite3_step $::STMT] } { }
  } -test {
    sqlite3_finalize $::STMT
  }
}

finish_test

do_execsql_test 8.1 {
  SELECT * FROM t1 WHERE b=4;
} {
  1 4 1  2 4 2
}

finish_test

} {0 99}
do_catchsql_test 9.11 {
  SELECT oid FROM wo2 JOIN (wo3 JOIN x3)
} {0 99}


finish_test

  } {1 2 3 4}
  
  db close
  db2 close
}

finish_test

    ROLLBACK TO one;
    RELEASE one;
  } {ok}
} 


finish_test

db close
db2 close
tvfs delete
tvfs2 delete

finish_test

do_execsql_test 1.1 {
  PRAGMA quick_check
} {
  ok
}

finish_test

  SELECT * FROM t1 WHERE (a, b) IN ( (3, 3), (2, 2) );
} {
  *SEARCH t1 USING COVERING INDEX i2*
}


finish_test

} {1 {IN(...) element has 2 terms - expected 3}}

do_catchsql_test 2.3 {
  SELECT 2 IN ( (1, 2), (3, 4), (5, 6) )
} {1 {row value misused}}

finish_test

--SCAN xy2 (nCycle=nnn)
}

#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

  SELECT group_concat(x), m, n FROM t1 
    LEFT JOIN (SELECT x, 59 AS m, 60 AS n FROM t2) GROUP BY a;
} {
  hello 59 60
}

finish_test

} -body {
  db eval { SELECT * FROM sss } { db eval { SELECT randomblob(5000) } }
} -test {
  faultsim_test_result {0 {}} 
}

finish_test

  DROP TABLE t16;
  CREATE TABLE t16(x DEFAULT(count()));
  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(string_agg('x',',')));
  INSERT INTO t16(rowid) VALUES(123);
  SELECT rowid, x FROM t16;
} {1 {unknown function: string_agg()}}
do_catchsql_test table-16.7 {
  INSERT INTO t16 DEFAULT VALUES;
} {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.
#
do_execsql_test table-17.1 {
  DROP TABLE IF EXISTS t1;

} {}

do_execsql_test "1.Total BUSY errors: $nTotalBusy .2" {
  SELECT count(*) FROM t1;
} $nAttempt

finish_test

    SELECT tbl,idx,neq,nlt,ndlt,test_extract(sample,0) AS sample
    FROM sqlite_stat4;
    PRAGMA writable_schema = 0;
  }
} {}
do_test tkt-cbd05-1.3 {
  execsql { 
    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}}

do_test tkt-cbd05-2.1 {

      ON CONFLICT(b,a) DO NOTHING
      ON CONFLICT DO UPDATE SET b=?1;
  END;
} {1 {trigger cannot use variables}}


finish_test

#
do_execsql_test 6.4.1 { SELECT typeof(unhex(NULL)) } {null}
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

}

db close
tvfs delete


finish_test

} -test {
  faultsim_test_result {0 {1 2 3 4 7 8}} 
  if {$testrc} { test_system_errno db $::seh_errno }
}
catch { db close }

finish_test

  5  15      2.5
  6  21      3.0
}

do_execsql_test 4.10.1 {
  SELECT a, 
    count() 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
  3 4 6.5.4.3
  2 5 6.5.4.3.2

      WINDOW win1 AS (ORDER BY b)
  } {cannot override ORDER BY clause of window: win1}
} {
  do_catchsql_test 18.2.$tn $sql [list 1 $error]
}

do_execsql_test 18.3.1 {
  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)
  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 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}

do_execsql_test 18.3.4 {
  SELECT group_concat(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}

do_execsql_test 18.3.5 {
  SELECT string_agg(c, '.') OVER win5
  FROM t1
  WINDOW win1 AS (PARTITION BY b),
         win2 AS (win1),
         win3 AS (win2),
         win4 AS (win3),
         win5 AS (win4 ORDER BY c)
} {four four.six four.six.two five five.one five.one.three}

do_execsql_test 28.1.1 {
  CREATE TABLE t1(a INTEGER PRIMARY KEY, b CHAR(1), c CHAR(2), d ANY);
  INSERT INTO t1 VALUES (3, 'C', 'cc', 1.0);
  INSERT INTO t1 VALUES (13,'M', 'cc', NULL);
}

do_execsql_test 28.1.2 {
  SELECT string_agg(b,'') OVER w1 FROM t1
    WINDOW w1 AS (ORDER BY a RANGE BETWEEN 3 PRECEDING AND 1 PRECEDING)
} {
  {} {}
}

do_execsql_test 28.2.1 {
  CREATE TABLE t2(a TEXT, b INTEGER);

  {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}
  {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}
  {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}
  {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}
  {}   {}   {}   {}   {}   {}   {}   {}   {}   {}   {}}

do_execsql_test 1.1.14.1 {
  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
  89.81.96.59.38.68.39.62.91.46.6.99.97.27.46
  89.81.96.59.38.68.39.62.91.46.6.99.97.27.46.78

  89.59.39.99.29.59.89.89.29.9.79.49.59.29.59.19.39.9
  89.59.39.99.29.59.89.89.29.9.79.49.59.29.59.19.39.9.9
  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 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
  1.1.2.2.3.3.4.5.6.7.7.7.8.8.8.9.9.9
  1.1.2.2.3.3.4.5.6.7.7.7.8.8.8.9.9.9.10

  9.9.9.19.29.29.29.39.39.39.49.59.59.59.59.69.79.89
  9.9.9.19.29.29.29.39.39.39.49.59.59.59.59.69.79.89.89
  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 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
  90.40.30.80.20.90.60.70.80.90.30.50.10.30
  90.40.30.80.20.90.60.70.80.90.30.50.10.30.81

  29   53   63   87   37   41   9   61   73   95   65   13   1   21   65   35
  5   73   11   51   87   41   31   31   15   95   73   79   11   49   59
  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 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
  27   27   28   29   29   29   3   3   30   30   30   31   31   32   33   33
  33   33   34   34   34   34   35   35   36   36   36   36   37   37   38