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 (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 52execsql_test 1.1 { 53 SELECT max(b) OVER ( 54 ORDER BY a 55 ) FROM t2 56} 57 58foreach {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 336finish_test 337 338