Debian Codegraph @

test/windowC.test

raw
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