test/window6.test

   1 # 2018 May 8
   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 # This file implements regression tests for SQLite library. Specifically,
  12 # it tests the sqlite3_create_window_function() API.
  13 #
  14
  15 set testdir [file dirname $argv0]
  16 source $testdir/tester.tcl
  17 set testprefix window6
  18
  19 ifcapable !windowfunc {
  20   finish_test
  21   return
  22 }
  23
  24 set setup {
  25   CREATE TABLE %t1(%x, %y %typename);
  26   INSERT INTO %t1 VALUES(1, 'a');
  27   INSERT INTO %t1 VALUES(2, 'b');
  28   INSERT INTO %t1 VALUES(3, 'c');
  29   INSERT INTO %t1 VALUES(4, 'd');
  30   INSERT INTO %t1 VALUES(5, 'e');
  31 }
  32
  33 foreach {tn vars} {
  34   1 {}
  35   2 { set A(%t1) over }
  36   3 { set A(%x)  over }
  37   4 {
  38     set A(%alias)   over
  39     set A(%x)       following
  40     set A(%y)       over
  41   }
  42   5 {
  43     set A(%t1)      over
  44     set A(%x)       following
  45     set A(%y)       preceding
  46     set A(%w)       current
  47     set A(%alias)   filter
  48     set A(%typename)  window
  49   }
  50
  51   6 {
  52     set A(%x)       window
  53   }
  54 } {
  55   set A(%t1)    t1
  56   set A(%x)     x
  57   set A(%y)     y
  58   set A(%w)     w
  59   set A(%alias) alias
  60   set A(%typename) integer
  61   eval $vars
  62
  63   set MAP [array get A]
  64   set setup_sql [string map $MAP $setup]
  65   reset_db
  66   execsql $setup_sql
  67
  68   do_execsql_test 1.$tn.1 [string map $MAP {
  69     SELECT group_concat(%x, '.') OVER (ORDER BY %y) FROM %t1
  70   }] {1 1.2 1.2.3 1.2.3.4 1.2.3.4.5}
  71
  72   do_execsql_test 1.$tn.2 [string map $MAP {
  73     SELECT sum(%x) OVER %w FROM %t1 WINDOW %w AS (ORDER BY %y)
  74   }] {1 3 6 10 15}
  75
  76   do_execsql_test 1.$tn.3 [string map $MAP {
  77     SELECT sum(%alias.%x) OVER %w FROM %t1 %alias WINDOW %w AS (ORDER BY %y)
  78   }] {1 3 6 10 15}
  79
  80   do_execsql_test 1.$tn.4 [string map $MAP {
  81     SELECT sum(%x) %alias FROM %t1
  82   }] {15}
  83 }
  84
  85
  86 proc winproc {args} { return "window: $args" }
  87 db func window winproc
  88 do_execsql_test 2.0 {
  89   SELECT window('hello world');
  90 } {{window: {hello world}}}
  91
  92 proc wincmp {a b} { string compare $b $a }
  93 db collate window wincmp
  94 do_execsql_test 3.0 {
  95   CREATE TABLE window(x COLLATE window);
  96   INSERT INTO window VALUES('bob'), ('alice'), ('cate');
  97   SELECT * FROM window ORDER BY x COLLATE window;
  98 } {cate bob alice}
  99 do_execsql_test 3.1 {
 100   DROP TABLE window;
 101   CREATE TABLE x1(x);
 102   INSERT INTO x1 VALUES('bob'), ('alice'), ('cate');
 103   CREATE INDEX window ON x1(x COLLATE window);
 104   SELECT * FROM x1 ORDER BY x COLLATE window;
 105 } {cate bob alice}
 106
 107
 108 do_execsql_test 4.0 { CREATE TABLE t4(x, y); }
 109
 110 # do_execsql_test 4.1 { PRAGMA parser_trace = 1 }
 111 do_execsql_test 4.1 {
 112   SELECT * FROM t4 window, t4;
 113 }
 114
 115 #-------------------------------------------------------------------------
 116 reset_db
 117
 118 do_execsql_test 5.0 {
 119   CREATE TABLE over(x, over);
 120   CREATE TABLE window(x, window);
 121   INSERT INTO over VALUES(1, 2), (3, 4), (5, 6);
 122   INSERT INTO window VALUES(1, 2), (3, 4), (5, 6);
 123   SELECT sum(x) over FROM over
 124 } {9}
 125
 126 do_execsql_test 5.1 {
 127   SELECT sum(x) over over FROM over WINDOW over AS ()
 128 } {9 9 9}
 129
 130 do_execsql_test 5.2 {
 131   SELECT sum(over) over over over FROM over over WINDOW over AS (ORDER BY over)
 132 } {2 6 12}
 133
 134 do_execsql_test 5.3 {
 135   SELECT sum(over) over over over FROM over over WINDOW over AS (ORDER BY over);
 136 } {2 6 12}
 137
 138 do_execsql_test 5.4 {
 139   SELECT sum(window) OVER window window FROM window window window window AS (ORDER BY window);
 140 } {2 6 12}
 141
 142 do_execsql_test 5.5 {
 143   SELECT count(*) OVER win FROM over
 144   WINDOW win AS (ORDER BY x ROWS BETWEEN +2 FOLLOWING AND +3 FOLLOWING)
 145 } {1 0 0}
 146
 147 #-------------------------------------------------------------------------
 148 #
 149
 150 do_execsql_test 6.0 {
 151   SELECT LIKE('!', '', '!') x WHERE x;
 152 } {}
 153
 154 do_execsql_test 6.1 {
 155   SELECT LIKE("!","","!")""WHeRE"";
 156 } {}
 157
 158 do_catchsql_test 6.2 {
 159   SELECT LIKE("!","","!")""window"";
 160 } {1 {near "window": syntax error}}
 161
 162 reset_db
 163 do_execsql_test 7.0 {
 164   CREATE TABLE t1(x TEXT);
 165   CREATE INDEX i1 ON t1(x COLLATE nocase);
 166   INSERT INTO t1 VALUES('');
 167 }
 168
 169 do_execsql_test 7.1 {
 170   SELECT count(*) FROM t1 WHERE x LIKE '!' ESCAPE '!';
 171 } {0}
 172
 173 #-------------------------------------------------------------------------
 174 #
 175 do_execsql_test 8.0 {
 176   CREATE TABLE IF NOT EXISTS "sample" (
 177       "id" INTEGER NOT NULL PRIMARY KEY,
 178       "counter" INTEGER NOT NULL,
 179       "value" REAL NOT NULL
 180   );
 181
 182   INSERT INTO "sample" (counter, value)
 183   VALUES (1, 10.), (1, 20.), (2, 1.), (2, 3.), (3, 100.);
 184 }
 185
 186 do_execsql_test 8.1 {
 187   SELECT "counter", "value", RANK() OVER w AS "rank"
 188   FROM "sample"
 189   WINDOW w AS (PARTITION BY "counter" ORDER BY "value" DESC)
 190   ORDER BY "counter", RANK() OVER w
 191 } {
 192   1 20.0 1   1 10.0 2   2 3.0 1   2 1.0 2  3 100.0 1
 193 }
 194
 195 do_execsql_test 8.2 {
 196   SELECT "counter", "value", SUM("value") OVER
 197   (ORDER BY "id" ROWS 2 PRECEDING)
 198     FROM "sample"
 199   ORDER BY "id"
 200 } {
 201   1 10.0 10.0   1 20.0 30.0   2 1.0 31.0   2 3.0 24.0   3 100.0 104.0
 202 }
 203
 204 do_execsql_test 8.3 {
 205   SELECT SUM("value") OVER
 206   (ORDER BY "id" ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
 207     FROM "sample"
 208   ORDER BY "id"
 209 } {
 210   10.0   30.0   31.0   24.0   104.0
 211 }
 212
 213 do_execsql_test 9.0 {
 214   WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<5)
 215   SELECT x, group_concat(x) OVER (ORDER BY x ROWS 2 PRECEDING)
 216   FROM c;
 217 } {
 218   1 1  2 1,2  3 1,2,3  4 2,3,4  5 3,4,5
 219 }
 220 do_catchsql_test 9.1 {
 221   WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<5)
 222   SELECT x, group_concat(x) OVER (ORDER BY x RANGE 2 PRECEDING)
 223   FROM c;
 224 } {1 {RANGE PRECEDING is only supported with UNBOUNDED}}
 225
 226 do_catchsql_test 9.2 {
 227   WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<5)
 228   SELECT x, group_concat(x) OVER (ORDER BY x RANGE BETWEEN UNBOUNDED PRECEDING AND 2 FOLLOWING)
 229   FROM c;
 230 } {1 {RANGE FOLLOWING is only supported with UNBOUNDED}}
 231
 232 do_catchsql_test 9.3 {
 233   WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<5)
 234   SELECT count(DISTINCT x) OVER (ORDER BY x) FROM c;
 235 } {1 {DISTINCT is not supported for window functions}}
 236
 237 finish_test
 238