test/windowB.test

   1 # 2019-08-30
   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 RANGE BETWEEN and especially with NULLS LAST
  12 # and for varying separator handling by group_concat().
  13 #
  14
  15 set testdir [file dirname $argv0]
  16 source $testdir/tester.tcl
  17 set testprefix windowB
  18
  19 ifcapable !windowfunc {
  20   finish_test
  21   return
  22 }
  23
  24 do_execsql_test 1.0 {
  25   CREATE TABLE t1(a, b);
  26   INSERT INTO t1 VALUES(NULL, 1);
  27   INSERT INTO t1 VALUES(NULL, 2);
  28   INSERT INTO t1 VALUES(NULL, 3);
  29 } {}
  30
  31 foreach {tn win} {
  32   1 { ORDER BY a RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING }
  33   2 { ORDER BY a NULLS LAST RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING }
  34   3 { ORDER BY a DESC RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING }
  35   4 { ORDER BY a DESC NULLS FIRST RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING }
  36
  37   5 { ORDER BY a      NULLS LAST  RANGE BETWEEN 1 FOLLOWING AND 2 FOLLOWING }
  38   6 { ORDER BY a DESC NULLS FIRST RANGE BETWEEN 1 FOLLOWING AND 2 FOLLOWING }
  39
  40   7 { ORDER BY a      NULLS LAST  RANGE BETWEEN 2 PRECEDING AND 1 PRECEDING }
  41   8 { ORDER BY a DESC NULLS FIRST RANGE BETWEEN 2 PRECEDING AND 1 PRECEDING }
  42 } {
  43   do_execsql_test 1.$tn "
  44     SELECT sum(b) OVER win FROM t1
  45     WINDOW win AS ( $win )
  46   " {6 6 6}
  47 }
  48
  49 do_execsql_test 1.2 {
  50   SELECT sum(b) OVER win FROM t1
  51   WINDOW win AS (
  52     ORDER BY a DESC NULLS FIRST RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING
  53   )
  54 } {6 6 6}
  55
  56 #-------------------------------------------------------------------------
  57 reset_db
  58 do_execsql_test 2.0 {
  59   CREATE TABLE t1(a, b);
  60   INSERT INTO t1 VALUES(1, NULL);
  61   INSERT INTO t1 VALUES(2, 45);
  62   INSERT INTO t1 VALUES(3, 66.2);
  63   INSERT INTO t1 VALUES(4, 'hello world');
  64   INSERT INTO t1 VALUES(5, 'hello world');
  65   INSERT INTO t1 VALUES(6, X'1234');
  66   INSERT INTO t1 VALUES(7, X'1234');
  67   INSERT INTO t1 VALUES(8, NULL);
  68 }
  69
  70 foreach {tn win} {
  71   1 "ORDER BY b RANGE BETWEEN 1 PRECEDING AND 2 PRECEDING"
  72   2 "ORDER BY b RANGE BETWEEN 2 FOLLOWING AND 2 FOLLOWING"
  73   3 "ORDER BY b NULLS LAST RANGE BETWEEN 1 PRECEDING AND 2 PRECEDING"
  74   4 "ORDER BY b NULLS LAST RANGE BETWEEN 2 FOLLOWING AND 2 FOLLOWING"
  75 } {
  76   do_execsql_test 2.1.$tn "
  77     SELECT a, sum(a) OVER win FROM t1
  78     WINDOW win AS ( $win )
  79     ORDER BY 1
  80   " {1 9   2 {}  3 {}  4 9  5 9  6 13  7 13  8 9}
  81 }
  82
  83 #-------------------------------------------------------------------------
  84 ifcapable json1 {
  85   reset_db
  86   do_execsql_test 3.0 {
  87     CREATE TABLE testjson(id INTEGER PRIMARY KEY, j TEXT, x TEXT);
  88     INSERT INTO testjson VALUES(1, '{"a":1}', 'a');
  89     INSERT INTO testjson VALUES(2, '{"b":2}', 'b');
  90     INSERT INTO testjson VALUES(3, '{"c":3}', 'c');
  91     INSERT INTO testjson VALUES(4, '{"d":4}', 'd');
  92   }
  93
  94   do_execsql_test 3.1 {
  95     SELECT json_group_array(json(j)) FROM testjson;
  96   } {
  97     {[{"a":1},{"b":2},{"c":3},{"d":4}]}
  98   }
  99
 100   do_execsql_test 3.2 {
 101     SELECT json_group_array(json(j)) OVER (ORDER BY id) FROM testjson;
 102   } {
 103     {[{"a":1}]}
 104     {[{"a":1},{"b":2}]}
 105     {[{"a":1},{"b":2},{"c":3}]}
 106     {[{"a":1},{"b":2},{"c":3},{"d":4}]}
 107   }
 108
 109   do_execsql_test 3.3 {
 110     SELECT json_group_array(json(j)) OVER (
 111       ORDER BY id RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
 112       EXCLUDE TIES
 113     ) FROM testjson;
 114   } {
 115     {[{"a":1}]}
 116     {[{"a":1},{"b":2}]}
 117     {[{"a":1},{"b":2},{"c":3}]}
 118     {[{"a":1},{"b":2},{"c":3},{"d":4}]}
 119   }
 120
 121   do_execsql_test 3.4 {
 122     SELECT json_group_array(json(j)) OVER (
 123       ORDER BY id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
 124     ) FROM testjson;
 125   } {
 126     {[{"a":1},{"b":2}]}
 127     {[{"a":1},{"b":2},{"c":3}]}
 128     {[{"b":2},{"c":3},{"d":4}]}
 129     {[{"c":3},{"d":4}]}
 130   }
 131
 132   do_execsql_test 3.5 {
 133     SELECT json_group_array(json(j)) OVER (
 134       ORDER BY id ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING
 135     ) FROM testjson;
 136   } {
 137     {[]}
 138     {[{"a":1}]}
 139     {[{"a":1},{"b":2}]}
 140     {[{"b":2},{"c":3}]}
 141   }
 142
 143   do_execsql_test 3.5a {
 144     UPDATE testjson SET j = replace(j,char(125),',"e":9'||char(125));
 145     SELECT j FROM testjson;
 146   } {
 147     {{"a":1,"e":9}}
 148     {{"b":2,"e":9}}
 149     {{"c":3,"e":9}}
 150     {{"d":4,"e":9}}
 151   }
 152   do_execsql_test 3.5b {
 153     SELECT group_concat(x,'') OVER (
 154       ORDER BY id ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING
 155     ) FROM testjson ORDER BY id;
 156   } {bc cd d {}}
 157   do_execsql_test 3.5c {
 158     SELECT json_group_array(json(j)) OVER (
 159       ORDER BY id ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING
 160     ) FROM testjson;
 161   } {
 162     {[{"b":2,"e":9},{"c":3,"e":9}]}
 163     {[{"c":3,"e":9},{"d":4,"e":9}]}
 164     {[{"d":4,"e":9}]}
 165     {[]}
 166   }
 167   do_execsql_test 3.5d {
 168     SELECT json_group_object(x,json(j)) OVER (
 169       ORDER BY id ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING
 170     ) FROM testjson;
 171   } {
 172     {{"b":{"b":2,"e":9},"c":{"c":3,"e":9}}}
 173     {{"c":{"c":3,"e":9},"d":{"d":4,"e":9}}}
 174     {{"d":{"d":4,"e":9}}}
 175     {{}}
 176   }
 177
 178   do_execsql_test 3.7b {
 179     SELECT group_concat(x,'') FILTER (WHERE id!=2) OVER (
 180       ORDER BY id ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING
 181     ) FROM testjson;
 182   } {{} a a c}
 183
 184   do_execsql_test 3.7c {
 185     SELECT json_group_array(json(j)) FILTER (WHERE id!=2) OVER (
 186       ORDER BY id ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING
 187     ) FROM testjson
 188   } {
 189     {[]}
 190     {[{"a":1,"e":9}]}
 191     {[{"a":1,"e":9}]}
 192     {[{"c":3,"e":9}]}
 193   }
 194   do_execsql_test 3.7d {
 195     SELECT json_group_object(x,json(j)) FILTER (WHERE id!=2) OVER (
 196       ORDER BY id ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING
 197     ) FROM testjson
 198   } {
 199     {{}}
 200     {{"a":{"a":1,"e":9}}}
 201     {{"a":{"a":1,"e":9}}}
 202     {{"c":{"c":3,"e":9}}}
 203   }
 204 }
 205
 206 #-------------------------------------------------------------------------
 207 reset_db
 208 do_execsql_test 4.0 {
 209   CREATE TABLE x(a);
 210   INSERT INTO x VALUES(1);
 211   INSERT INTO x VALUES(2);
 212 }
 213
 214 do_execsql_test 4.1 {
 215   WITH y AS (
 216       SELECT Row_Number() OVER (win) FROM x WINDOW win AS (PARTITION BY a)
 217   )
 218   SELECT * FROM y;
 219 } {
 220   1 1
 221 }
 222
 223 do_catchsql_test 4.2 {
 224   WITH y AS (
 225     SELECT Row_Number() OVER (win) FROM x WINDOW win AS (PARTITION
 226   BY fake_column))
 227   SELECT * FROM y;
 228 } {1 {no such column: fake_column}}
 229
 230 do_catchsql_test 4.3 {
 231   SELECT 1 WINDOW win AS (PARTITION BY fake_column);
 232 } {0 1}
 233
 234 #-------------------------------------------------------------------------
 235 reset_db
 236 do_execsql_test 5.0 {
 237   CREATE TABLE t1(a, c);
 238   CREATE INDEX i1 ON t1(a);
 239
 240   INSERT INTO t1 VALUES(0, 421);
 241   INSERT INTO t1 VALUES(1, 844);
 242   INSERT INTO t1 VALUES(2, 1001);
 243 }
 244
 245 do_execsql_test 5.1 {
 246   SELECT a, sum(c) OVER (
 247     ORDER BY a RANGE BETWEEN 0 PRECEDING AND 3 PRECEDING
 248   ) FROM t1;
 249 } {0 {} 1 {} 2 {}}
 250
 251 do_execsql_test 5.2 {
 252   INSERT INTO t1 VALUES(NULL, 123);
 253   INSERT INTO t1 VALUES(NULL, 111);
 254   INSERT INTO t1 VALUES('xyz', 222);
 255   INSERT INTO t1 VALUES('xyz', 333);
 256
 257   SELECT a, sum(c) OVER (
 258     ORDER BY a RANGE BETWEEN 0 PRECEDING AND 3 PRECEDING
 259   ) FROM t1;
 260 } {{} 234 {} 234 0 {} 1 {} 2 {} xyz 555 xyz 555}
 261
 262 do_execsql_test 5.3 {
 263   SELECT a, sum(c) OVER (
 264     ORDER BY a RANGE BETWEEN 2 FOLLOWING AND 0 FOLLOWING
 265   ) FROM t1;
 266 } {{} 234 {} 234 0 {} 1 {} 2 {} xyz 555 xyz 555}
 267
 268 do_execsql_test 5.4 {
 269   SELECT a, sum(c) OVER (
 270     ORDER BY a RANGE BETWEEN 0 PRECEDING AND 3 PRECEDING EXCLUDE NO OTHERS
 271   ) FROM t1;
 272 } {{} 234 {} 234 0 {} 1 {} 2 {} xyz 555 xyz 555}
 273
 274 do_execsql_test 5.5 {
 275   SELECT a, sum(c) OVER (
 276     ORDER BY a RANGE BETWEEN 2 FOLLOWING AND 0 FOLLOWING EXCLUDE NO OTHERS
 277   ) FROM t1;
 278 } {{} 234 {} 234 0 {} 1 {} 2 {} xyz 555 xyz 555}
 279
 280 #-------------------------------------------------------------------------
 281 reset_db
 282 do_execsql_test 6.0 {
 283   CREATE TABLE t1(a, c);
 284   CREATE INDEX i1 ON t1(a);
 285
 286   INSERT INTO t1 VALUES(7,  997);
 287   INSERT INTO t1 VALUES(8,  997);
 288   INSERT INTO t1 VALUES('abc', 1001);
 289 }
 290 do_execsql_test 6.1 {
 291   SELECT a, sum(c) OVER (
 292     ORDER BY a RANGE BETWEEN 2 FOLLOWING AND 0 FOLLOWING
 293   ) FROM t1;
 294 } {7 {} 8 {} abc 1001}
 295 do_execsql_test 6.2 {
 296   SELECT a, sum(c) OVER (
 297     ORDER BY a RANGE BETWEEN 2 FOLLOWING AND 0 FOLLOWING EXCLUDE NO OTHERS
 298   ) FROM t1;
 299 } {7 {} 8 {} abc 1001}
 300
 301 #-------------------------------------------------------------------------
 302 reset_db
 303 do_execsql_test 7.0 {
 304   CREATE TABLE t1(a, c);
 305   CREATE INDEX i1 ON t1(a);
 306
 307   INSERT INTO t1 VALUES(NULL, 46);
 308   INSERT INTO t1 VALUES(NULL, 45);
 309   INSERT INTO t1 VALUES(7,  997);
 310   INSERT INTO t1 VALUES(7,  1000);
 311   INSERT INTO t1 VALUES(8,  997);
 312   INSERT INTO t1 VALUES(8,  1000);
 313   INSERT INTO t1 VALUES('abc', 1001);
 314   INSERT INTO t1 VALUES('abc', 1004);
 315   INSERT INTO t1 VALUES('xyz', 3333);
 316 }
 317
 318 do_execsql_test 7.1 {
 319   SELECT a, max(c) OVER (
 320     ORDER BY a RANGE BETWEEN 2 FOLLOWING AND 0 FOLLOWING
 321   ) FROM t1;
 322 } {{} 46 {} 46  7 {} 7 {} 8 {} 8 {}  abc 1004 abc 1004 xyz 3333}
 323 do_execsql_test 7.2 {
 324   SELECT a, min(c) OVER (
 325     ORDER BY a RANGE BETWEEN 2 FOLLOWING AND 0 FOLLOWING
 326   ) FROM t1;
 327 } {{} 45 {} 45  7 {} 7 {} 8 {} 8 {}  abc 1001 abc 1001 xyz 3333}
 328
 329 do_execsql_test 7.3 {
 330   SELECT a, max(c) OVER (
 331     ORDER BY a RANGE BETWEEN 0 PRECEDING AND 2 PRECEDING
 332   ) FROM t1;
 333 } {{} 46 {} 46  7 {} 7 {} 8 {} 8 {}  abc 1004 abc 1004 xyz 3333}
 334 do_execsql_test 7.4 {
 335   SELECT a, min(c) OVER (
 336     ORDER BY a RANGE BETWEEN 0 PRECEDING AND 2 PRECEDING
 337   ) FROM t1;
 338 } {{} 45 {} 45  7 {} 7 {} 8 {} 8 {}  abc 1001 abc 1001 xyz 3333}
 339
 340 #-------------------------------------------------------------------------
 341 reset_db
 342 do_execsql_test 8.0 {
 343   BEGIN TRANSACTION;
 344     CREATE TABLE t1(a, c);
 345     INSERT INTO t1 VALUES('aa', 111);
 346     INSERT INTO t1 VALUES('BB', 660);
 347     INSERT INTO t1 VALUES('CC', 938);
 348     INSERT INTO t1 VALUES('dd', 979);
 349   COMMIT;
 350
 351   CREATE INDEX i1 ON t1(a COLLATE nocase);
 352 }
 353
 354 do_execsql_test 8.1 {
 355   SELECT sum(c) OVER
 356     (ORDER BY a COLLATE nocase RANGE BETWEEN 10.0 PRECEDING AND 5.0 PRECEDING)
 357   FROM t1;
 358 } {111 660 938 979}
 359
 360 do_execsql_test 9.0 {
 361   CREATE TABLE seps(x);
 362   INSERT INTO seps(x) VALUES ('1'), ('22'), ('333'), ('4444');
 363   SELECT group_concat('-', x)
 364     OVER ( ORDER BY x ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING )
 365   FROM seps;
 366 } {-22- -22-333- -333-4444- -4444-}
 367
 368 #-------------------------------------------------------------------------
 369 reset_db
 370 do_execsql_test 10.1 {
 371   CREATE TABLE t1(i INTEGER PRIMARY KEY, v);
 372   INSERT INTO t1 VALUES( 1, 'one' );
 373   INSERT INTO t1 VALUES( 2, 'two' );
 374 }
 375
 376 do_execsql_test 10.2 {
 377   SELECT
 378     json_group_array( v ) OVER w,
 379     json_group_array( v ) OVER w
 380   FROM t1
 381   window w as (
 382     range between unbounded preceding and unbounded following
 383   )
 384 } {
 385   {["one","two"]}
 386   {["one","two"]}
 387   {["one","two"]}
 388   {["one","two"]}
 389 }
 390
 391 do_execsql_test 10.3 {
 392   SELECT
 393     group_concat( v ) OVER w,
 394     json_group_array( v ) OVER w,
 395     json_group_array( v ) OVER w,
 396     group_concat( v ) OVER w
 397   FROM t1
 398   window w as (
 399     range between unbounded preceding and unbounded following
 400   )
 401 } {
 402   one,two
 403   {["one","two"]}
 404   {["one","two"]}
 405   one,two
 406
 407   one,two
 408   {["one","two"]}
 409   {["one","two"]}
 410   one,two
 411 }
 412
 413 finish_test