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