test/window3.tcl

   1 # 2018 May 19
   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 source [file join [file dirname $argv0] pg_common.tcl]
  14
  15 #=========================================================================
  16
  17 start_test window3 "2018 May 31"
  18 ifcapable !windowfunc
  19
  20 execsql_test 1.0 {
  21   DROP TABLE IF EXISTS t2;
  22   CREATE TABLE t2(a INTEGER PRIMARY KEY, b INTEGER);
  23   INSERT INTO t2(a, b) VALUES
  24   (1,0), (2,74), (3,41), (4,74), (5,23), (6,99), (7,26), (8,33), (9,2),
  25   (10,89), (11,81), (12,96), (13,59), (14,38), (15,68), (16,39), (17,62),
  26   (18,91), (19,46), (20,6), (21,99), (22,97), (23,27), (24,46), (25,78),
  27   (26,54), (27,97), (28,8), (29,67), (30,29), (31,93), (32,84), (33,77),
  28   (34,23), (35,16), (36,16), (37,93), (38,65), (39,35), (40,47), (41,7),
  29   (42,86), (43,74), (44,61), (45,91), (46,85), (47,24), (48,85), (49,43),
  30   (50,59), (51,12), (52,32), (53,56), (54,3), (55,91), (56,22), (57,90),
  31   (58,55), (59,15), (60,28), (61,89), (62,25), (63,47), (64,1), (65,56),
  32   (66,40), (67,43), (68,56), (69,16), (70,75), (71,36), (72,89), (73,98),
  33   (74,76), (75,81), (76,4), (77,94), (78,42), (79,30), (80,78), (81,33),
  34   (82,29), (83,53), (84,63), (85,2), (86,87), (87,37), (88,80), (89,84),
  35   (90,72), (91,41), (92,9), (93,61), (94,73), (95,95), (96,65), (97,13),
  36   (98,58), (99,96), (100,98), (101,1), (102,21), (103,74), (104,65), (105,35),
  37   (106,5), (107,73), (108,11), (109,51), (110,87), (111,41), (112,12), (113,8),
  38   (114,20), (115,31), (116,31), (117,15), (118,95), (119,22), (120,73),
  39   (121,79), (122,88), (123,34), (124,8), (125,11), (126,49), (127,34),
  40   (128,90), (129,59), (130,96), (131,60), (132,55), (133,75), (134,77),
  41   (135,44), (136,2), (137,7), (138,85), (139,57), (140,74), (141,29), (142,70),
  42   (143,59), (144,19), (145,39), (146,26), (147,26), (148,47), (149,80),
  43   (150,90), (151,36), (152,58), (153,47), (154,9), (155,72), (156,72), (157,66),
  44   (158,33), (159,93), (160,75), (161,64), (162,81), (163,9), (164,23), (165,37),
  45   (166,13), (167,12), (168,14), (169,62), (170,91), (171,36), (172,91),
  46   (173,33), (174,15), (175,34), (176,36), (177,99), (178,3), (179,95), (180,69),
  47   (181,58), (182,52), (183,30), (184,50), (185,84), (186,10), (187,84),
  48   (188,33), (189,21), (190,39), (191,44), (192,58), (193,30), (194,38),
  49   (195,34), (196,83), (197,27), (198,82), (199,17), (200,7);
  50 }
  51
  52 execsql_test 1.1 {
  53   SELECT max(b) OVER (
  54     ORDER BY a
  55   ) FROM t2
  56 }
  57
  58 foreach {tn window} {
  59    1 "RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW"
  60    2 "RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING"
  61    3 "RANGE BETWEEN CURRENT ROW         AND CURRENT ROW"
  62    4 "RANGE BETWEEN CURRENT ROW         AND UNBOUNDED FOLLOWING"
  63    5 "ROWS BETWEEN UNBOUNDED PRECEDING AND 4 PRECEDING"
  64    6 "ROWS BETWEEN 4 PRECEDING    AND 2 PRECEDING"
  65    7 "ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW"
  66    8 "ROWS BETWEEN 4 PRECEDING    AND CURRENT ROW"
  67    9 "ROWS BETWEEN CURRENT ROW         AND CURRENT ROW"
  68   10 "ROWS BETWEEN UNBOUNDED PRECEDING AND 4 FOLLOWING"
  69   11 "ROWS BETWEEN 4 PRECEDING    AND 2 FOLLOWING"
  70   12 "ROWS BETWEEN CURRENT ROW         AND 4 FOLLOWING"
  71   13 "ROWS BETWEEN 2 FOLLOWING    AND 4 FOLLOWING"
  72   14 "ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING"
  73   15 "ROWS BETWEEN 4 PRECEDING    AND UNBOUNDED FOLLOWING"
  74   16 "ROWS BETWEEN CURRENT ROW         AND UNBOUNDED FOLLOWING"
  75   17 "ROWS BETWEEN 4 FOLLOWING    AND UNBOUNDED FOLLOWING"
  76 } {
  77   execsql_test 1.$tn.2.1 "SELECT max(b) OVER ( ORDER BY a $window ) FROM t2"
  78   execsql_test 1.$tn.2.2 "SELECT min(b) OVER ( ORDER BY a $window ) FROM t2"
  79
  80   execsql_test 1.$tn.3.1 "
  81     SELECT row_number() OVER ( ORDER BY a $window ) FROM t2
  82   "
  83   execsql_test 1.$tn.3.2 "
  84     SELECT row_number() OVER ( PARTITION BY b%10 ORDER BY a $window ) FROM t2
  85   "
  86   execsql_test 1.$tn.3.3 "
  87     SELECT row_number() OVER ( $window ) FROM t2
  88   "
  89
  90   execsql_test 1.$tn.4.1 "
  91     SELECT dense_rank() OVER ( ORDER BY a $window ) FROM t2
  92   "
  93   execsql_test 1.$tn.4.2 "
  94     SELECT dense_rank() OVER ( PARTITION BY b%10 ORDER BY a $window ) FROM t2
  95   "
  96   execsql_test 1.$tn.4.3 "
  97     SELECT dense_rank() OVER ( ORDER BY b $window ) FROM t2
  98   "
  99   execsql_test 1.$tn.4.4 "
 100     SELECT dense_rank() OVER ( PARTITION BY b%10 ORDER BY b $window ) FROM t2
 101   "
 102   execsql_test 1.$tn.4.5 "
 103     SELECT dense_rank() OVER ( ORDER BY b%10 $window ) FROM t2
 104   "
 105   execsql_test 1.$tn.4.6 "
 106     SELECT dense_rank() OVER ( PARTITION BY b%2 ORDER BY b%10 $window ) FROM t2
 107   "
 108
 109   execsql_test 1.$tn.5.1 "
 110     SELECT rank() OVER ( ORDER BY a $window ) FROM t2
 111   "
 112   execsql_test 1.$tn.5.2 "
 113     SELECT rank() OVER ( PARTITION BY b%10 ORDER BY a $window ) FROM t2
 114   "
 115   execsql_test 1.$tn.5.3 "
 116     SELECT rank() OVER ( ORDER BY b $window ) FROM t2
 117   "
 118   execsql_test 1.$tn.5.4 "
 119     SELECT rank() OVER ( PARTITION BY b%10 ORDER BY b $window ) FROM t2
 120   "
 121   execsql_test 1.$tn.5.5 "
 122     SELECT rank() OVER ( ORDER BY b%10 $window ) FROM t2
 123   "
 124   execsql_test 1.$tn.5.6 "
 125     SELECT rank() OVER ( PARTITION BY b%2 ORDER BY b%10 $window ) FROM t2
 126   "
 127
 128   execsql_test 1.$tn.6.1 "
 129     SELECT
 130       row_number() OVER ( PARTITION BY b%2 ORDER BY b%10 $window ),
 131       rank() OVER ( PARTITION BY b%2 ORDER BY b%10 $window ),
 132       dense_rank() OVER ( PARTITION BY b%2 ORDER BY b%10 $window )
 133     FROM t2
 134   "
 135
 136   execsql_float_test 1.$tn.7.1 "
 137     SELECT percent_rank() OVER ( ORDER BY a $window ) FROM t2
 138   "
 139   execsql_float_test 1.$tn.7.2 "
 140     SELECT percent_rank() OVER ( PARTITION BY b%10 ORDER BY a $window ) FROM t2
 141   "
 142   execsql_float_test 1.$tn.7.3 "
 143     SELECT percent_rank() OVER ( ORDER BY b $window ) FROM t2
 144   "
 145   execsql_float_test 1.$tn.7.4 "
 146     SELECT percent_rank() OVER ( PARTITION BY b%10 ORDER BY b $window ) FROM t2
 147   "
 148   execsql_float_test 1.$tn.7.5 "
 149     SELECT percent_rank() OVER ( ORDER BY b%10 $window ) FROM t2
 150   "
 151   execsql_float_test 1.$tn.7.6 "
 152     SELECT percent_rank() OVER (PARTITION BY b%2 ORDER BY b%10 $window) FROM t2
 153   "
 154
 155   execsql_float_test 1.$tn.8.1 "
 156     SELECT cume_dist() OVER ( ORDER BY a $window ) FROM t2
 157   "
 158   execsql_float_test 1.$tn.8.2 "
 159     SELECT cume_dist() OVER ( PARTITION BY b%10 ORDER BY a $window ) FROM t2
 160   "
 161   execsql_float_test 1.$tn.8.3 "
 162     SELECT cume_dist() OVER ( ORDER BY b $window ) FROM t2
 163   "
 164   execsql_float_test 1.$tn.8.4 "
 165     SELECT cume_dist() OVER ( PARTITION BY b%10 ORDER BY b $window ) FROM t2
 166   "
 167   execsql_float_test 1.$tn.8.5 "
 168     SELECT cume_dist() OVER ( ORDER BY b%10 $window ) FROM t2
 169   "
 170   execsql_float_test 1.$tn.8.6 "
 171     SELECT cume_dist() OVER ( PARTITION BY b%2 ORDER BY b%10 $window ) FROM t2
 172   "
 173
 174   execsql_float_test 1.$tn.8.1 "
 175     SELECT ntile(100) OVER ( ORDER BY a $window ) FROM t2
 176   "
 177   execsql_float_test 1.$tn.8.2 "
 178     SELECT ntile(101) OVER ( PARTITION BY b%10 ORDER BY a $window ) FROM t2
 179   "
 180   execsql_float_test 1.$tn.8.3 "
 181     SELECT ntile(102) OVER ( ORDER BY b,a $window ) FROM t2
 182   "
 183   execsql_float_test 1.$tn.8.4 "
 184     SELECT ntile(103) OVER ( PARTITION BY b%10 ORDER BY b,a $window ) FROM t2
 185   "
 186   execsql_float_test 1.$tn.8.5 "
 187     SELECT ntile(104) OVER ( ORDER BY b%10,a $window ) FROM t2
 188   "
 189   execsql_float_test 1.$tn.8.6 "
 190     SELECT ntile(105) OVER (PARTITION BY b%2,a ORDER BY b%10 $window) FROM t2
 191   "
 192   execsql_float_test 1.$tn.8.7 "
 193     SELECT ntile(105) OVER ( $window ) FROM t2
 194   "
 195
 196   execsql_test 1.$tn.9.1 "
 197     SELECT last_value(a+b) OVER ( ORDER BY a $window ) FROM t2
 198   "
 199   execsql_test 1.$tn.9.2 "
 200     SELECT last_value(a+b) OVER ( PARTITION BY b%10 ORDER BY a $window ) FROM t2
 201   "
 202   execsql_test 1.$tn.9.3 "
 203     SELECT last_value(a+b) OVER ( ORDER BY b,a $window ) FROM t2
 204   "
 205   execsql_test 1.$tn.9.4 "
 206     SELECT last_value(a+b) OVER ( PARTITION BY b%10 ORDER BY b,a $window ) FROM t2
 207   "
 208   execsql_test 1.$tn.9.5 "
 209     SELECT last_value(a+b) OVER ( ORDER BY b%10,a $window ) FROM t2
 210   "
 211   execsql_test 1.$tn.9.6 "
 212     SELECT last_value(a+b) OVER (PARTITION BY b%2,a ORDER BY b%10 $window) FROM t2
 213   "
 214
 215   execsql_test 1.$tn.10.1 "
 216     SELECT nth_value(b,b+1) OVER (ORDER BY a $window) FROM t2
 217   "
 218   execsql_test 1.$tn.10.2 "
 219     SELECT nth_value(b,b+1) OVER (PARTITION BY b%10 ORDER BY a $window) FROM t2
 220   "
 221   execsql_test 1.$tn.10.3 "
 222     SELECT nth_value(b,b+1) OVER ( ORDER BY b,a $window ) FROM t2
 223   "
 224   execsql_test 1.$tn.10.4 "
 225     SELECT nth_value(b,b+1) OVER ( PARTITION BY b%10 ORDER BY b,a $window ) FROM t2
 226   "
 227   execsql_test 1.$tn.10.5 "
 228     SELECT nth_value(b,b+1) OVER ( ORDER BY b%10,a $window ) FROM t2
 229   "
 230   execsql_test 1.$tn.10.6 "
 231     SELECT nth_value(b,b+1) OVER (PARTITION BY b%2,a ORDER BY b%10 $window) FROM t2
 232   "
 233
 234   execsql_test 1.$tn.11.1 "
 235     SELECT first_value(b) OVER (ORDER BY a $window) FROM t2
 236   "
 237   execsql_test 1.$tn.11.2 "
 238     SELECT first_value(b) OVER (PARTITION BY b%10 ORDER BY a $window) FROM t2
 239   "
 240   execsql_test 1.$tn.11.3 "
 241     SELECT first_value(b) OVER ( ORDER BY b,a $window ) FROM t2
 242   "
 243   execsql_test 1.$tn.11.4 "
 244     SELECT first_value(b) OVER ( PARTITION BY b%10 ORDER BY b,a $window ) FROM t2
 245   "
 246   execsql_test 1.$tn.11.5 "
 247     SELECT first_value(b) OVER ( ORDER BY b%10,a $window ) FROM t2
 248   "
 249   execsql_test 1.$tn.11.6 "
 250     SELECT first_value(b) OVER (PARTITION BY b%2,a ORDER BY b%10 $window) FROM t2
 251   "
 252
 253   execsql_test 1.$tn.12.1 "
 254     SELECT lead(b,b) OVER (ORDER BY a $window) FROM t2
 255   "
 256   execsql_test 1.$tn.12.2 "
 257     SELECT lead(b,b) OVER (PARTITION BY b%10 ORDER BY a $window) FROM t2
 258   "
 259   execsql_test 1.$tn.12.3 "
 260     SELECT lead(b,b) OVER ( ORDER BY b,a $window ) FROM t2
 261   "
 262   execsql_test 1.$tn.12.4 "
 263     SELECT lead(b,b) OVER ( PARTITION BY b%10 ORDER BY b,a $window ) FROM t2
 264   "
 265   execsql_test 1.$tn.12.5 "
 266     SELECT lead(b,b) OVER ( ORDER BY b%10,a $window ) FROM t2
 267   "
 268   execsql_test 1.$tn.12.6 "
 269     SELECT lead(b,b) OVER (PARTITION BY b%2,a ORDER BY b%10 $window) FROM t2
 270   "
 271
 272   execsql_test 1.$tn.13.1 "
 273     SELECT lag(b,b) OVER (ORDER BY a $window) FROM t2
 274   "
 275   execsql_test 1.$tn.13.2 "
 276     SELECT lag(b,b) OVER (PARTITION BY b%10 ORDER BY a $window) FROM t2
 277   "
 278   execsql_test 1.$tn.13.3 "
 279     SELECT lag(b,b) OVER ( ORDER BY b,a $window ) FROM t2
 280   "
 281   execsql_test 1.$tn.13.4 "
 282     SELECT lag(b,b) OVER ( PARTITION BY b%10 ORDER BY b,a $window ) FROM t2
 283   "
 284   execsql_test 1.$tn.13.5 "
 285     SELECT lag(b,b) OVER ( ORDER BY b%10,a $window ) FROM t2
 286   "
 287   execsql_test 1.$tn.13.6 "
 288     SELECT lag(b,b) OVER (PARTITION BY b%2,a ORDER BY b%10 $window) FROM t2
 289   "
 290
 291   execsql_test 1.$tn.14.1 "
 292     SELECT string_agg(CAST(b AS TEXT), '.') OVER (ORDER BY a $window) FROM t2
 293   "
 294   execsql_test 1.$tn.14.2 "
 295     SELECT string_agg(CAST(b AS TEXT), '.') OVER (PARTITION BY b%10 ORDER BY a $window) FROM t2
 296   "
 297   execsql_test 1.$tn.14.3 "
 298     SELECT string_agg(CAST(b AS TEXT), '.') OVER ( ORDER BY b,a $window ) FROM t2
 299   "
 300   execsql_test 1.$tn.14.4 "
 301     SELECT string_agg(CAST(b AS TEXT), '.') OVER ( PARTITION BY b%10 ORDER BY b,a $window ) FROM t2
 302   "
 303   execsql_test 1.$tn.14.5 "
 304     SELECT string_agg(CAST(b AS TEXT), '.') OVER ( ORDER BY b%10,a $window ) FROM t2
 305   "
 306   execsql_test 1.$tn.14.6 "
 307     SELECT string_agg(CAST(b AS TEXT), '.') OVER (PARTITION BY b%2,a ORDER BY b%10 $window) FROM t2
 308   "
 309
 310   execsql_test 1.$tn.15.1 "
 311     SELECT count(*) OVER win, string_agg(CAST(b AS TEXT), '.')
 312     FILTER (WHERE a%2=0) OVER win FROM t2
 313     WINDOW win AS (ORDER BY a $window)
 314   "
 315
 316   execsql_test 1.$tn.15.2 "
 317     SELECT count(*) OVER win, string_agg(CAST(b AS TEXT), '.')
 318     FILTER (WHERE 0=1) OVER win FROM t2
 319     WINDOW win AS (ORDER BY a $window)
 320   "
 321
 322   execsql_test 1.$tn.15.3 "
 323     SELECT count(*) OVER win, string_agg(CAST(b AS TEXT), '.')
 324     FILTER (WHERE 1=0) OVER win FROM t2
 325     WINDOW win AS (PARTITION BY (a%10) ORDER BY a $window)
 326   "
 327
 328   execsql_test 1.$tn.15.4 "
 329     SELECT count(*) OVER win, string_agg(CAST(b AS TEXT), '.')
 330     FILTER (WHERE a%2=0) OVER win FROM t2
 331     WINDOW win AS (PARTITION BY (a%10) ORDER BY a $window)
 332   "
 333
 334 }
 335
 336 finish_test
 337