test/windowE.test

   1 # 2022 October 18
   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 #
  12
  13 set testdir [file dirname $argv0]
  14 source $testdir/tester.tcl
  15 set testprefix windowE
  16
  17 proc custom {a b} { return [string compare $a $b] }
  18 db collate custom custom
  19
  20 do_execsql_test 1.0 {
  21   CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT COLLATE custom);
  22   INSERT INTO t1 VALUES(1, 'one');
  23   INSERT INTO t1 VALUES(2, 'two');
  24   INSERT INTO t1 VALUES(3, 'three');
  25   INSERT INTO t1 VALUES(4, 'four');
  26   INSERT INTO t1 VALUES(5, 'five');
  27   INSERT INTO t1 VALUES(6, 'six');
  28   CREATE INDEX t1b ON t1(b);
  29 }
  30
  31 do_execsql_test 1.1 {
  32   SELECT * FROM t1
  33 } {
  34   1 one 2 two 3 three 4 four 5 five 6 six
  35 }
  36
  37 do_execsql_test 1.2 {
  38   SELECT group_concat(a,',') OVER win FROM t1
  39   WINDOW win AS (
  40     ORDER BY b RANGE BETWEEN 1 PRECEDING AND 2 PRECEDING
  41   )
  42 } {
  43   5 4 1 6 3 2
  44 }
  45
  46 proc custom {a b} { return [string compare $b $a] }
  47
  48 do_execsql_test 1.3 {
  49   SELECT group_concat(a,',') OVER win FROM t1
  50   WINDOW win AS (
  51     ORDER BY b RANGE BETWEEN 1 PRECEDING AND 2 PRECEDING
  52   )
  53 } {
  54   5 5,4 5,4,1 5,4,1,6 5,4,1,6,3 5,4,1,6,3,2
  55 }
  56
  57 #-------------------------------------------------------------------------
  58 reset_db
  59 do_execsql_test 2.0 {
  60   CREATE TABLE t1(x);
  61 }
  62
  63 sqlite3_create_aggregate db
  64
  65 breakpoint
  66 do_catchsql_test 2.1 {
  67   SELECT min(x) OVER w1 FROM t1
  68     WINDOW w1 AS (PARTITION BY x_count(x) OVER w1);
  69 } {1 {x_count() may not be used as a window function}}
  70
  71 do_catchsql_test 2.2 {
  72   SELECT min(x) FILTER (WHERE x_count(x) OVER w1) OVER w1 FROM t1
  73     WINDOW w1 AS (PARTITION BY x OVER w1);
  74 } {1 {near "OVER": syntax error}}
  75
  76 #-------------------------------------------------------------------------
  77 reset_db
  78 do_execsql_test 3.0 {
  79   BEGIN TRANSACTION;
  80     CREATE TABLE t2(c1 INT, c2 REAL);
  81     INSERT INTO t2 VALUES
  82     (447,0.0), (448,0.0), (449,0.0), (452,0.0), (453,0.0), (454,0.0), (455,0.0),
  83     (456,0.0), (459,0.0), (460,0.0), (462,0.0), (463,0.0), (466,0.0), (467,0.0),
  84     (468,0.0), (469,0.0), (470,0.0), (473,0.0), (474,0.0), (475,0.0), (476,0.0),
  85     (477,0.0), (480,0.0), (481,0.0), (482,0.0), (483,0.0), (484,0.0), (487,0.0),
  86     (488,0.0), (489,0.0), (490,0.0), (491,0.0), (494,0.0), (495,0.0), (496,0.0),
  87     (497,0.0), (498,0.0), (501,0.0), (502,0.0), (503,0.0), (504,0.0), (505,0.0),
  88     (508,0.0), (509,0.0), (510,0.0), (511,0.0), (512,0.0), (515,0.0), (516,0.0),
  89     (517,0.0), (518,0.0), (519,0.0), (522,0.0), (523,0.0), (524,0.0), (525,0.0),
  90     (526,0.0), (529,0.0), (530,0.0), (531,0.0), (532,0.0), (533,0.0), (536,0.0),
  91     (537,1.0), (538,0.0), (539,0.0), (540,0.0), (543,0.0), (544,0.0);
  92   COMMIT;
  93 }
  94
  95 do_execsql_test 3.1 {
  96   select c1, max(c2) over (order by c1 range 366.0 preceding) from t2;
  97 } {
  98   447 0.0 448 0.0 449 0.0 452 0.0 453 0.0 454 0.0 455 0.0 456 0.0 459 0.0
  99   460 0.0 462 0.0 463 0.0 466 0.0 467 0.0 468 0.0 469 0.0 470 0.0 473 0.0
 100   474 0.0 475 0.0 476 0.0 477 0.0 480 0.0 481 0.0 482 0.0 483 0.0 484 0.0
 101   487 0.0 488 0.0 489 0.0 490 0.0 491 0.0 494 0.0 495 0.0 496 0.0 497 0.0
 102   498 0.0 501 0.0 502 0.0 503 0.0 504 0.0 505 0.0 508 0.0 509 0.0 510 0.0
 103   511 0.0 512 0.0 515 0.0 516 0.0 517 0.0 518 0.0 519 0.0 522 0.0 523 0.0
 104   524 0.0 525 0.0 526 0.0 529 0.0 530 0.0 531 0.0 532 0.0 533 0.0 536 0.0
 105   537 1.0 538 1.0 539 1.0 540 1.0 543 1.0 544 1.0
 106 }
 107
 108
 109 finish_test
 110