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