gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/windowC.test (about)

     1  # 2021-09-29
     2  #
     3  # The author disclaims copyright to this source code.  In place of
     4  # a legal notice, here is a blessing:
     5  #
     6  #    May you do good and not evil.
     7  #    May you find forgiveness for yourself and forgive others.
     8  #    May you share freely, never taking more than you give.
     9  #
    10  #***********************************************************************
    11  # Test cases for varying separator handling by group_concat().
    12  #
    13  
    14  set testdir [file dirname $argv0]
    15  source $testdir/tester.tcl
    16  set testprefix windowC
    17  
    18  ifcapable !windowfunc {
    19    finish_test
    20    return
    21  }
    22  
    23  do_execsql_test 1.0 {
    24    CREATE TABLE x1(i INTEGER PRIMARY KEY, x);
    25  }
    26  
    27  foreach {tn bBlob seps} {
    28    1 0 {a b c def g}
    29    2 0 {abcdefg {} {} abcdefg}
    30    3 0 {a bc def ghij klmno pqrstu}
    31    4 1 {a bc def ghij klmno pqrstu}
    32    5 1 {, , , , , , , , , , , , ....... , ,}
    33  } {
    34    foreach type {text blob} {
    35      do_test 1.$type.$tn.1 {
    36        execsql { DELETE FROM x1 }
    37        foreach s $seps {
    38          if {$type=="text"} {
    39            execsql {INSERT INTO x1 VALUES(NULL, $s)}
    40          } else {
    41            execsql {INSERT INTO x1 VALUES(NULL, CAST ($s AS blob))}
    42          }
    43        }
    44      } {}
    45  
    46      foreach {tn2 win} {
    47        1     "ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING"
    48        2     "ROWS BETWEEN 2 PRECEDING AND CURRENT ROW"
    49        3     "ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING"
    50      } {
    51        do_test 1.$type.$tn.2.$tn2 {
    52          db eval "
    53            SELECT group_concat('val', x) OVER ( ORDER BY i $win ) AS val FROM x1
    54            " {
    55              if {[string range $val 0 2]!="val"
    56                || [string range $val end-2 end]!="val"
    57              } {
    58                error "unexpected return value: $val"
    59              }
    60            }
    61        } {} 
    62      }
    63    }
    64  }
    65  
    66  # 2021-10-12 dbsqlfuzz 6c31db077a14149a7b22a1069294bdb068be8a96
    67  #
    68  reset_db
    69  do_execsql_test 2.0 {
    70    PRAGMA encoding=UTF16le;
    71    WITH separator(x) AS (VALUES(',a,'),(',bc,')),
    72         value(y) AS (VALUES(1),(x'5585d09013455178cd11ce4a'))
    73    SELECT group_concat(y,x) OVER (ORDER BY x ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)
    74    FROM separator, value;
    75  } {{} 1 蕕郐䔓硑ᇍ䫎 1}
    76  reset_db
    77  do_execsql_test 2.1 {
    78    PRAGMA encoding=UTF16be;
    79    WITH separator(x) AS (VALUES(',a,'),(',bc,')),
    80         value(y) AS (VALUES(1),(x'5585d09013455178cd11ce4a'))
    81    SELECT group_concat(y,x) OVER (ORDER BY x ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)
    82    FROM separator, value;
    83  } {{} 1 喅킐ፅ典촑칊 1}
    84  
    85  finish_test