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 13source [file join [file dirname $argv0] pg_common.tcl] 14 15#========================================================================= 16 17start_test window3 "2018 May 31" 18ifcapable !windowfunc 19 20execsql_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 51execsql_test 1.1 { 52 SELECT max(b) OVER ( 53 ORDER BY a 54 ) FROM t2 55} 56 57foreach {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 362finish_test 363 364