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