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 ifcapable !icu {
 151   do_execsql_test 6.0 {
 152     SELECT LIKE('!', '', '!') x WHERE x;
 153   } {}
 154   do_execsql_test 6.1 {
 155     SELECT LIKE("!","","!")""WHeRE"";
 156   } {}
 157   do_catchsql_test 6.2 {
 158     SELECT LIKE("!","","!")""window"";
 159   } {1 {near "window": syntax error}}
 160 }
 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 ifcapable !icu {
 170   do_execsql_test 7.1 {
 171     SELECT count(*) FROM t1 WHERE x LIKE '!' ESCAPE '!';
 172   } {0}
 173 }
 174
 175 #-------------------------------------------------------------------------
 176 #
 177 do_execsql_test 8.0 {
 178   CREATE TABLE IF NOT EXISTS "sample" (
 179       "id" INTEGER NOT NULL PRIMARY KEY,
 180       "counter" INTEGER NOT NULL,
 181       "value" REAL NOT NULL
 182   );
 183
 184   INSERT INTO "sample" (counter, value)
 185   VALUES (1, 10.), (1, 20.), (2, 1.), (2, 3.), (3, 100.);
 186 }
 187
 188 do_execsql_test 8.1 {
 189   SELECT "counter", "value", RANK() OVER w AS "rank"
 190   FROM "sample"
 191   WINDOW w AS (PARTITION BY "counter" ORDER BY "value" DESC)
 192   ORDER BY "counter", RANK() OVER w
 193 } {
 194   1 20.0 1   1 10.0 2   2 3.0 1   2 1.0 2  3 100.0 1
 195 }
 196
 197 do_execsql_test 8.2 {
 198   SELECT "counter", "value", SUM("value") OVER
 199   (ORDER BY "id" ROWS 2 PRECEDING)
 200     FROM "sample"
 201   ORDER BY "id"
 202 } {
 203   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
 204 }
 205
 206 do_execsql_test 8.3 {
 207   SELECT SUM("value") OVER
 208   (ORDER BY "id" ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
 209     FROM "sample"
 210   ORDER BY "id"
 211 } {
 212   10.0   30.0   31.0   24.0   104.0
 213 }
 214
 215 do_execsql_test 9.0 {
 216   WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<5)
 217   SELECT x, group_concat(x) OVER (ORDER BY x ROWS 2 PRECEDING)
 218   FROM c;
 219 } {
 220   1 1  2 1,2  3 1,2,3  4 2,3,4  5 3,4,5
 221 }
 222 #do_catchsql_test 9.1 {
 223 #  WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<5)
 224 #  SELECT x, group_concat(x) OVER (ORDER BY x RANGE 2 PRECEDING)
 225 #  FROM c;
 226 #} {1 {RANGE must use only UNBOUNDED or CURRENT ROW}}
 227 #
 228 #do_catchsql_test 9.2 {
 229 #  WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<5)
 230 #  SELECT x, group_concat(x) OVER (ORDER BY x RANGE BETWEEN UNBOUNDED PRECEDING AND 2 FOLLOWING)
 231 #  FROM c;
 232 #} {1 {RANGE must use only UNBOUNDED or CURRENT ROW}}
 233
 234 do_catchsql_test 9.3 {
 235   WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<5)
 236   SELECT count(DISTINCT x) OVER (ORDER BY x) FROM c;
 237 } {1 {DISTINCT is not supported for window functions}}
 238
 239 do_catchsql_test 9.4 {
 240   WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<5)
 241   SELECT count() OVER (ORDER BY x RANGE UNBOUNDED FOLLOWING) FROM c;
 242 } {1 {near "FOLLOWING": syntax error}}
 243
 244 do_catchsql_test 9.5 {
 245   WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<5)
 246   SELECT count() OVER (ORDER BY x RANGE BETWEEN UNBOUNDED FOLLOWING AND UNBOUNDED FOLLOWING) FROM c;
 247 } {1 {near "FOLLOWING": syntax error}}
 248
 249 do_catchsql_test 9.6 {
 250   WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<5)
 251   SELECT count() OVER (ORDER BY x RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED PRECEDING) FROM c;
 252 } {1 {near "PRECEDING": syntax error}}
 253
 254 foreach {tn frame} {
 255   1 "BETWEEN CURRENT ROW AND 4 PRECEDING"
 256   2 "4 FOLLOWING"
 257   3 "BETWEEN 4 FOLLOWING AND CURRENT ROW"
 258   4 "BETWEEN 4 FOLLOWING AND 2 PRECEDING"
 259 } {
 260   do_catchsql_test 9.7.$tn "
 261     WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<5)
 262     SELECT count() OVER (
 263         ORDER BY x ROWS $frame
 264     ) FROM c;
 265   " {1 {unsupported frame specification}}
 266 }
 267
 268 do_catchsql_test 9.8.1 {
 269   WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<5)
 270   SELECT count() OVER (
 271       ORDER BY x ROWS BETWEEN a PRECEDING AND 2 FOLLOWING
 272   ) FROM c;
 273 } {1 {frame starting offset must be a non-negative integer}}
 274 do_catchsql_test 9.8.2 {
 275   WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<5)
 276   SELECT count() OVER (
 277       ORDER BY x ROWS BETWEEN 2 PRECEDING AND a FOLLOWING
 278   ) FROM c;
 279 } {1 {frame ending offset must be a non-negative integer}}
 280
 281 do_execsql_test 10.0 {
 282   WITH t1(a,b) AS (VALUES(1,2))
 283   SELECT count() FILTER (where b<>5) OVER w1
 284     FROM t1
 285     WINDOW w1 AS (ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING);
 286 } {1}
 287
 288 foreach {tn stmt} {
 289   1 "SELECT nth_value(b, 0) OVER (ORDER BY a) FROM t1"
 290   2 "SELECT nth_value(b, -1) OVER (ORDER BY a) FROM t1"
 291   3 "SELECT nth_value(b, '4ab') OVER (ORDER BY a) FROM t1"
 292   4 "SELECT nth_value(b, NULL) OVER (ORDER BY a) FROM t1"
 293   5 "SELECT nth_value(b, 8.5) OVER (ORDER BY a) FROM t1"
 294 } {
 295   do_catchsql_test 10.1.$tn "
 296     WITH t1(a,b) AS ( VALUES(1, 2), (2, 3), (3, 4) )
 297     $stmt
 298   " {1 {second argument to nth_value must be a positive integer}}
 299 }
 300
 301 foreach {tn stmt res} {
 302   1 "SELECT nth_value(b, 1) OVER (ORDER BY a) FROM t1"         {2 2 2}
 303   2 "SELECT nth_value(b, 2) OVER (ORDER BY a) FROM t1"         {{} 3 3}
 304   3 "SELECT nth_value(b, '2') OVER (ORDER BY a) FROM t1"       {{} 3 3}
 305   4 "SELECT nth_value(b, 2.0) OVER (ORDER BY a) FROM t1"       {{} 3 3}
 306   5 "SELECT nth_value(b, '2.0') OVER (ORDER BY a) FROM t1"     {{} 3 3}
 307   6 "SELECT nth_value(b, 10000000) OVER (ORDER BY a) FROM t1"  {{} {} {}}
 308 } {
 309   do_execsql_test 10.2.$tn "
 310     WITH t1(a,b) AS ( VALUES(1, 2), (2, 3), (3, 4) )
 311     $stmt
 312   " $res
 313 }
 314
 315
 316 #-------------------------------------------------------------------------
 317 #
 318 reset_db
 319 do_execsql_test 11.0 {
 320   CREATE TABLE t1(a INT);
 321   INSERT INTO t1 VALUES(10),(15),(20),(20),(25),(30),(30),(50);
 322   CREATE TABLE t3(x INT, y VARCHAR);
 323   INSERT INTO t3(x,y) VALUES(10,'ten'),('15','fifteen'),(30,'thirty');
 324 }
 325
 326 do_execsql_test 11.1 {
 327   SELECT a, (SELECT y FROM t3 WHERE x=a) FROM t1 ORDER BY a;
 328 } {
 329   10 ten 15 fifteen 20 {} 20 {} 25 {} 30 thirty 30 thirty 50 {}
 330 }
 331
 332 do_execsql_test 11.2 {
 333   SELECT a, (SELECT y FROM t3 WHERE x=a), sum(a) OVER (ORDER BY a)
 334     FROM t1 ORDER BY a;
 335 } {
 336   10 ten 10   15 fifteen 25   20 {} 65        20 {} 65
 337   25 {} 90    30 thirty 150   30 thirty 150   50 {} 200
 338 }
 339
 340 do_execsql_test 11.3.1 {
 341   SELECT a, sum(a) OVER win FROM t1
 342   WINDOW win AS (ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
 343 } {
 344   10 10 15 25 20 45 20 65 25 90 30 120 30 150 50 200
 345 }
 346 do_execsql_test 11.3.2 {
 347   SELECT a, sum(a) OVER win FROM t1
 348   WINDOW win AS (ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND 0 FOLLOWING)
 349 } {
 350   10 10 15 25 20 45 20 65 25 90 30 120 30 150 50 200
 351 }
 352 do_execsql_test 11.3.3 {
 353   SELECT a, sum(a) OVER win FROM t1
 354   WINDOW win AS (ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND 0 PRECEDING)
 355 } {
 356   10 10 15 25 20 45 20 65 25 90 30 120 30 150 50 200
 357 }
 358
 359 do_execsql_test 11.4.1 {
 360   SELECT y, group_concat(y, '.') OVER win FROM t3
 361   WINDOW win AS (
 362     ORDER BY y RANGE BETWEEN UNBOUNDED PRECEDING AND 10 PRECEDING
 363   );
 364 } {
 365   fifteen fifteen
 366   ten     fifteen.ten
 367   thirty  fifteen.ten.thirty
 368 }
 369
 370 finish_test