xref: /sqlite-3.40.0/test/windowC.test (revision 6e627c44) 
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
14set testdir [file dirname $argv0]
15source $testdir/tester.tcl
16set testprefix windowC
17
18ifcapable !windowfunc {
19  finish_test
20  return
21}
22
23do_execsql_test 1.0 {
24  CREATE TABLE x1(i INTEGER PRIMARY KEY, x);
25}
26
27foreach {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#
68reset_db
69do_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}
76reset_db
77do_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
85finish_test
86