test/window8.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 window8 "2019 March 01"
  18 ifcapable !windowfunc
  19
  20 execsql_test 1.0 {
  21   DROP TABLE IF EXISTS t3;
  22   CREATE TABLE t3(a TEXT, b TEXT, c INTEGER);
  23   INSERT INTO t3 VALUES
  24     ('HH', 'bb', 355), ('CC', 'aa', 158), ('BB', 'aa', 399),
  25     ('FF', 'bb', 938), ('HH', 'aa', 480), ('FF', 'bb', 870),
  26     ('JJ', 'aa', 768), ('JJ', 'aa', 899), ('GG', 'bb', 929),
  27     ('II', 'bb', 421), ('GG', 'bb', 844), ('FF', 'bb', 574),
  28     ('CC', 'bb', 822), ('GG', 'bb', 938), ('BB', 'aa', 660),
  29     ('HH', 'aa', 979), ('BB', 'bb', 792), ('DD', 'aa', 845),
  30     ('JJ', 'bb', 354), ('FF', 'bb', 295), ('JJ', 'aa', 234),
  31     ('BB', 'bb', 840), ('AA', 'aa', 934), ('EE', 'aa', 113),
  32     ('AA', 'bb', 309), ('BB', 'aa', 412), ('AA', 'aa', 911),
  33     ('AA', 'bb', 572), ('II', 'aa', 398), ('II', 'bb', 250),
  34     ('II', 'aa', 652), ('BB', 'bb', 633), ('AA', 'aa', 239),
  35     ('FF', 'aa', 670), ('BB', 'bb', 705), ('HH', 'bb', 963),
  36     ('CC', 'bb', 346), ('II', 'bb', 671), ('BB', 'aa', 247),
  37     ('AA', 'aa', 223), ('GG', 'aa', 480), ('HH', 'aa', 790),
  38     ('FF', 'aa', 208), ('BB', 'bb', 711), ('EE', 'aa', 777),
  39     ('DD', 'bb', 716), ('CC', 'aa', 759), ('CC', 'aa', 430),
  40     ('CC', 'aa', 607), ('DD', 'bb', 794), ('GG', 'aa', 148),
  41     ('GG', 'aa', 634), ('JJ', 'bb', 257), ('DD', 'bb', 959),
  42     ('FF', 'bb', 726), ('BB', 'aa', 762), ('JJ', 'bb', 336),
  43     ('GG', 'aa', 335), ('HH', 'bb', 330), ('GG', 'bb', 160),
  44     ('JJ', 'bb', 839), ('FF', 'aa', 618), ('BB', 'aa', 393),
  45     ('EE', 'bb', 629), ('FF', 'aa', 667), ('AA', 'bb', 870),
  46     ('FF', 'bb', 102), ('JJ', 'aa', 113), ('DD', 'aa', 224),
  47     ('AA', 'bb', 627), ('HH', 'bb', 730), ('II', 'bb', 443),
  48     ('HH', 'bb', 133), ('EE', 'bb', 252), ('II', 'bb', 805),
  49     ('BB', 'bb', 786), ('EE', 'bb', 768), ('HH', 'bb', 683),
  50     ('DD', 'bb', 238), ('DD', 'aa', 256);
  51 }
  52
  53 foreach {tn frame} {
  54   1  { GROUPS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING }
  55   2  { GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW }
  56   3  { GROUPS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING }
  57   4  { GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING }
  58   5  { GROUPS BETWEEN 1 PRECEDING         AND 2 PRECEDING }
  59   6  { GROUPS BETWEEN 2 PRECEDING         AND 1 PRECEDING }
  60   7  { GROUPS BETWEEN 3 PRECEDING         AND 1 PRECEDING }
  61   8  { GROUPS BETWEEN 3 PRECEDING         AND 0 PRECEDING }
  62   9  { GROUPS BETWEEN 2 PRECEDING         AND CURRENT ROW }
  63   10 { GROUPS BETWEEN 3 PRECEDING         AND 0 FOLLOWING }
  64   11 { GROUPS BETWEEN 2 PRECEDING         AND UNBOUNDED FOLLOWING }
  65   12 { GROUPS BETWEEN CURRENT ROW         AND 0 FOLLOWING }
  66   13 { GROUPS BETWEEN CURRENT ROW         AND 1 FOLLOWING }
  67   14 { GROUPS BETWEEN CURRENT ROW         AND 100 FOLLOWING }
  68   15 { GROUPS BETWEEN CURRENT ROW         AND UNBOUNDED FOLLOWING }
  69   16 { GROUPS BETWEEN 0 FOLLOWING         AND 0 FOLLOWING }
  70   17 { GROUPS BETWEEN 1 FOLLOWING         AND 0 FOLLOWING }
  71   18 { GROUPS BETWEEN 1 FOLLOWING         AND 5 FOLLOWING }
  72   19 { GROUPS BETWEEN 1 FOLLOWING         AND UNBOUNDED FOLLOWING }
  73
  74 } {
  75   execsql_test 1.$tn.1 "
  76     SELECT a, b, sum(c) OVER (ORDER BY a $frame) FROM t3 ORDER BY 1, 2, 3;
  77   "
  78   execsql_test 1.$tn.2 "
  79     SELECT a, b, sum(c) OVER (ORDER BY a,b $frame) FROM t3 ORDER BY 1, 2, 3;
  80   "
  81   execsql_test 1.$tn.3 "
  82     SELECT a, b, rank() OVER (ORDER BY a $frame) FROM t3 ORDER BY 1, 2, 3;
  83   "
  84   execsql_test 1.$tn.4 "
  85     SELECT a, b, max(c) OVER (ORDER BY a,b $frame) FROM t3 ORDER BY 1, 2, 3;
  86   "
  87   execsql_test 1.$tn.5 "
  88     SELECT a, b, min(c) OVER (ORDER BY a,b $frame) FROM t3 ORDER BY 1, 2, 3;
  89   "
  90
  91   set f2 "$frame EXCLUDE CURRENT ROW"
  92
  93   execsql_test 1.$tn.6 "
  94     SELECT a, b, sum(c) OVER (ORDER BY a $f2) FROM t3 ORDER BY 1, 2, 3;
  95   "
  96   execsql_test 1.$tn.7 "
  97     SELECT a, b, sum(c) OVER (ORDER BY a,b $f2) FROM t3 ORDER BY 1, 2, 3;
  98   "
  99
 100   execsql_test 1.$tn.8 "
 101     SELECT a, b,
 102       sum(c) OVER (ORDER BY a $f2),
 103       sum(c) OVER (ORDER BY a $frame),
 104       sum(c) OVER (ORDER BY a,b $f2),
 105       sum(c) OVER (ORDER BY a,b $frame)
 106     FROM t3 ORDER BY 1, 2, 3;
 107   "
 108 }
 109
 110
 111 foreach {tn ex} {
 112   1  { EXCLUDE NO OTHERS }
 113   2  { EXCLUDE CURRENT ROW }
 114   3  { EXCLUDE GROUP }
 115   4  { EXCLUDE TIES }
 116 } {
 117   execsql_test 2.$tn.1 "
 118     SELECT row_number() OVER win
 119     FROM t3
 120     WINDOW win AS (
 121       ORDER BY c, b, a
 122       ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING $ex
 123     )
 124   "
 125
 126   execsql_test 2.$tn.2 "
 127     SELECT nth_value(c, 14) OVER win
 128     FROM t3
 129     WINDOW win AS (
 130       ORDER BY c, b, a
 131       ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING $ex
 132     )
 133   "
 134
 135   execsql_test 2.$tn.3 "
 136     SELECT min(c) OVER win, max(c) OVER win, sum(c) OVER win FROM t3
 137     WINDOW win AS (
 138       ORDER BY c, b, a
 139       ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW $ex
 140     ) ORDER BY a, b, c;
 141   "
 142 }
 143
 144 ==========
 145
 146 execsql_test 3.0 {
 147   DROP TABLE IF EXISTS t1;
 148   CREATE TABLE t1(a REAL, b INTEGER);
 149   INSERT INTO t1 VALUES
 150       (5, 10), (10, 20), (13, 26), (13, 26),
 151       (15, 30), (20, 40), (22,80), (30, 90);
 152 }
 153
 154 foreach {tn frame} {
 155   1 { ORDER BY a RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING }
 156   2 { ORDER BY a RANGE BETWEEN 10 PRECEDING AND 5 PRECEDING }
 157   3 { ORDER BY a RANGE BETWEEN 2 FOLLOWING AND 3 FOLLOWING }
 158   4 { ORDER BY a DESC RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING }
 159   5 { ORDER BY a DESC RANGE BETWEEN 10 PRECEDING AND 5 PRECEDING }
 160   6 { ORDER BY a DESC RANGE BETWEEN 2 FOLLOWING AND 3 FOLLOWING }
 161
 162   7  { ORDER BY a RANGE BETWEEN 5.1 PRECEDING AND 5.3 FOLLOWING }
 163   8  { ORDER BY a RANGE BETWEEN 10.2 PRECEDING AND 5.4 PRECEDING }
 164   9  { ORDER BY a RANGE BETWEEN 2.6 FOLLOWING AND 3.5 FOLLOWING }
 165   10 { ORDER BY a DESC RANGE BETWEEN 5.7 PRECEDING AND 5.8 FOLLOWING }
 166   11 { ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND 5.9 PRECEDING }
 167   12 { ORDER BY a DESC RANGE BETWEEN 2.1 FOLLOWING AND UNBOUNDED FOLLOWING }
 168   13  { ORDER BY a RANGE 5.1 PRECEDING }
 169 } {
 170   execsql_test 3.$tn "
 171     SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ($frame)
 172   "
 173 }
 174
 175 ==========
 176
 177 execsql_test 4.0 {
 178   DROP TABLE IF EXISTS t1;
 179   CREATE TABLE t1(a INTEGER, b INTEGER);
 180   INSERT INTO t1 VALUES
 181     (NULL, 1), (NULL, 2), (NULL, 3), (10, 4), (10, 5);
 182 }
 183
 184 execsql_test 4.1.1 {
 185   SELECT sum(b) OVER (
 186     ORDER BY a RANGE BETWEEN 5 PRECEDING AND 10 FOLLOWING
 187   ) FROM t1 ORDER BY 1;
 188 }
 189 execsql_test 4.1.2 {
 190   SELECT sum(b) OVER (
 191     ORDER BY a DESC RANGE BETWEEN 5 PRECEDING AND 10 FOLLOWING
 192   ) FROM t1 ORDER BY 1;
 193 }
 194
 195 execsql_test 4.2.1 {
 196   SELECT sum(b) OVER (
 197     ORDER BY a RANGE BETWEEN 5 FOLLOWING AND 10 FOLLOWING
 198   ) FROM t1 ORDER BY 1 NULLS FIRST;
 199 }
 200 execsql_test 4.2.2 {
 201   SELECT sum(b) OVER (
 202     ORDER BY a RANGE BETWEEN 5 FOLLOWING AND 10 FOLLOWING
 203   ) FROM t1 ORDER BY 1 NULLS LAST;
 204 }
 205
 206 execsql_test 4.2.3 {
 207   SELECT sum(b) OVER (
 208     ORDER BY a DESC RANGE BETWEEN 5 FOLLOWING AND 10 FOLLOWING
 209   ) FROM t1 ORDER BY 1 NULLS FIRST;
 210 }
 211 execsql_test 4.2.4 {
 212   SELECT sum(b) OVER (
 213     ORDER BY a DESC RANGE BETWEEN 5 FOLLOWING AND 10 FOLLOWING
 214   ) FROM t1 ORDER BY 1 NULLS LAST;
 215 }
 216
 217 execsql_test 4.3.1 {
 218   SELECT sum(b) OVER (
 219     ORDER BY a NULLS FIRST RANGE BETWEEN UNBOUNDED PRECEDING AND 10 FOLLOWING
 220   ) FROM t1 ORDER BY 1 NULLS FIRST;
 221 }
 222 execsql_test 4.3.2 {
 223   SELECT sum(b) OVER (
 224     ORDER BY a NULLS LAST RANGE BETWEEN UNBOUNDED PRECEDING AND 10 FOLLOWING
 225   ) FROM t1 ORDER BY 1 NULLS LAST;
 226 }
 227
 228 execsql_test 4.4.1 {
 229   SELECT sum(b) OVER (
 230     ORDER BY a NULLS FIRST ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
 231   ) FROM t1 ORDER BY 1 NULLS FIRST;
 232 }
 233 execsql_test 4.4.2 {
 234   SELECT sum(b) OVER (
 235     ORDER BY a NULLS LAST ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
 236   ) FROM t1 ORDER BY 1 NULLS LAST;
 237 }
 238
 239 execsql_test 4.4.3 {
 240   SELECT sum(b) OVER (
 241     ORDER BY a DESC NULLS LAST ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
 242   ) FROM t1 ORDER BY 1 NULLS FIRST;
 243 }
 244 execsql_test 4.4.4 {
 245   SELECT sum(b) OVER (
 246     ORDER BY a DESC NULLS LAST ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
 247   ) FROM t1 ORDER BY 1 NULLS LAST;
 248 }
 249
 250 execsql_test 4.5.1 {
 251   SELECT sum(b) OVER (
 252     ORDER BY a ASC  NULLS LAST RANGE BETWEEN UNBOUNDED PRECEDING AND 10 FOLLOWING
 253   ) FROM t1 ORDER BY 1 NULLS LAST;
 254 }
 255 execsql_test 4.5.2 {
 256   SELECT sum(b) OVER (
 257     ORDER BY a DESC NULLS FIRST RANGE
 258     BETWEEN UNBOUNDED PRECEDING AND 10 FOLLOWING
 259   ) FROM t1 ORDER BY 1 NULLS LAST;
 260 }
 261
 262 ==========
 263
 264 execsql_test 5.0 {
 265   INSERT INTO t3 VALUES
 266     (NULL, 'bb', 355), (NULL, 'cc', 158), (NULL, 'aa', 399),
 267     ('JJ', NULL, 839), ('FF', NULL, 618), ('BB', NULL, 393),
 268     (NULL, 'bb', 629), (NULL, NULL, 667), (NULL, NULL, 870);
 269 }
 270
 271 foreach {tn ex} {
 272   1  { EXCLUDE NO OTHERS }
 273   2  { EXCLUDE CURRENT ROW }
 274   3  { EXCLUDE GROUP }
 275   4  { EXCLUDE TIES }
 276 } {
 277   foreach {tn2 frame} {
 278     1 { RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING }
 279     2 { ORDER BY a NULLS FIRST
 280         RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING }
 281     3 { PARTITION BY coalesce(a, '')
 282         RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING }
 283     4 { ORDER BY a NULLS FIRST GROUPS 6 PRECEDING }
 284     5 { ORDER BY c NULLS FIRST RANGE BETWEEN 6 PRECEDING AND 7 FOLLOWING }
 285     6 { ORDER BY c NULLS FIRST RANGE BETWEEN 0 PRECEDING AND 0 FOLLOWING }
 286     7 { ORDER BY c NULLS FIRST, b NULLS FIRST, a NULLS FIRST
 287         ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING }
 288
 289     8 { RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING }
 290     9 { ORDER BY a NULLS LAST
 291         RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING }
 292    10 { PARTITION BY coalesce(a, '')
 293         RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING }
 294    11 { ORDER BY a NULLS LAST GROUPS 6 PRECEDING }
 295    12 { ORDER BY c NULLS LAST RANGE BETWEEN 6 PRECEDING AND 7 FOLLOWING }
 296    13 { ORDER BY c NULLS LAST RANGE BETWEEN 0 PRECEDING AND 0 FOLLOWING }
 297    14 { ORDER BY c NULLS LAST, b NULLS LAST, a NULLS LAST
 298         ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING }
 299   } {
 300     execsql_test 5.$tn.$tn2.1 "
 301       SELECT max(c) OVER win,
 302              min(c) OVER win,
 303              count(a) OVER win
 304       FROM t3
 305       WINDOW win AS ( $frame $ex )
 306       ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
 307     "
 308
 309     execsql_test 5.$tn.$tn2.2 "
 310       SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
 311              rank() OVER win,
 312              dense_rank() OVER win
 313       FROM t3
 314       WINDOW win AS ( $frame $ex )
 315       ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
 316     "
 317   }
 318 }
 319
 320 ==========
 321
 322 execsql_test 6.0 {
 323   DROP TABLE IF EXISTS t2;
 324   CREATE TABLE t2(a TEXT, b INTEGER);
 325   INSERT INTO t2 VALUES('A', NULL);
 326   INSERT INTO t2 VALUES('B', NULL);
 327   INSERT INTO t2 VALUES('C', 1);
 328 }
 329
 330 execsql_test 6.1 {
 331   SELECT string_agg(a, '.') OVER (
 332     ORDER BY b NULLS FIRST RANGE BETWEEN 7 PRECEDING AND 2 PRECEDING
 333   )
 334   FROM t2
 335 }
 336
 337 execsql_test 6.2 {
 338   SELECT string_agg(a, '.') OVER (
 339     ORDER BY b DESC NULLS LAST RANGE BETWEEN 7 PRECEDING AND 2 PRECEDING
 340   )
 341   FROM t2
 342 }
 343
 344 ==========
 345
 346 execsql_test 7.0 {
 347   DROP TABLE IF EXISTS t2;
 348   CREATE TABLE t2(a INTEGER, b INTEGER);
 349
 350   INSERT INTO t2 VALUES(1, 65);
 351   INSERT INTO t2 VALUES(2, NULL);
 352   INSERT INTO t2 VALUES(3, NULL);
 353   INSERT INTO t2 VALUES(4, NULL);
 354   INSERT INTO t2 VALUES(5, 66);
 355   INSERT INTO t2 VALUES(6, 67);
 356 }
 357
 358 foreach {tn f ex} {
 359   1 sum ""
 360   2 min ""
 361   3 sum "EXCLUDE CURRENT ROW"
 362   4 max "EXCLUDE CURRENT ROW"
 363 } {
 364 execsql_test 7.$tn.1 "
 365   SELECT $f (a) OVER win FROM t2
 366   WINDOW win AS (
 367       ORDER BY b NULLS LAST RANGE BETWEEN 6 FOLLOWING AND UNBOUNDED FOLLOWING
 368   );
 369 "
 370 execsql_test 7.$tn.2 "
 371   SELECT $f (a) OVER win FROM t2
 372   WINDOW win AS (
 373       ORDER BY b NULLS LAST RANGE BETWEEN 1 PRECEDING AND 2 PRECEDING
 374   );
 375 "
 376 execsql_test 7.$tn.3 "
 377   SELECT $f (a) OVER win FROM t2
 378   WINDOW win AS (
 379       ORDER BY b NULLS LAST RANGE BETWEEN 2 FOLLOWING AND 1 FOLLOWING
 380   );
 381 "
 382 execsql_test 7.$tn.4 "
 383   SELECT $f (a) OVER win FROM t2
 384   WINDOW win AS (
 385       ORDER BY b NULLS FIRST RANGE BETWEEN 1 PRECEDING AND 2 PRECEDING
 386   );
 387 "
 388 execsql_test 7.$tn.5 "
 389   SELECT $f (a) OVER win FROM t2
 390   WINDOW win AS (
 391       ORDER BY b NULLS FIRST RANGE BETWEEN 2 FOLLOWING AND 1 FOLLOWING
 392   );
 393 "
 394
 395 execsql_test 7.$tn.6 "
 396   SELECT $f (a) OVER win FROM t2
 397   WINDOW win AS (
 398       ORDER BY b NULLS LAST RANGE BETWEEN 1000 PRECEDING AND 2 PRECEDING
 399   );
 400 "
 401 execsql_test 7.$tn.7 "
 402   SELECT $f (a) OVER win FROM t2
 403   WINDOW win AS (
 404       ORDER BY b NULLS LAST RANGE BETWEEN 2000 FOLLOWING AND 1000 FOLLOWING
 405   );
 406 "
 407 execsql_test 7.$tn.8 "
 408   SELECT $f (a) OVER win FROM t2
 409   WINDOW win AS (
 410       ORDER BY b NULLS FIRST RANGE BETWEEN 1000 PRECEDING AND 2000 PRECEDING
 411   );
 412 "
 413 execsql_test 7.$tn.9 "
 414   SELECT $f (a) OVER win FROM t2
 415   WINDOW win AS (
 416       ORDER BY b NULLS FIRST RANGE BETWEEN 2000 FOLLOWING AND 1000 FOLLOWING
 417   );
 418 "
 419 }
 420
 421 ==========
 422
 423 execsql_test 8.0 {
 424   DROP TABLE IF EXISTS tx;
 425   CREATE TABLE tx(a INTEGER PRIMARY KEY);
 426   INSERT INTO tx VALUES(1), (2), (3), (4), (5), (6);
 427
 428   DROP TABLE IF EXISTS map;
 429   CREATE TABLE map(v INTEGER PRIMARY KEY, t TEXT);
 430   INSERT INTO map VALUES
 431     (1, 'odd'), (2, 'even'), (3, 'odd'),
 432     (4, 'even'), (5, 'odd'), (6, 'even');
 433 }
 434
 435 execsql_test 8.1 {
 436   SELECT sum(a) OVER (
 437     PARTITION BY (
 438       SELECT t FROM map WHERE v=a
 439     ) ORDER BY a
 440   ) FROM tx;
 441 }
 442
 443 execsql_test 8.2 {
 444   SELECT sum(a) OVER win FROM tx
 445   WINDOW win AS (
 446     PARTITION BY (
 447       SELECT t FROM map WHERE v=a
 448     ) ORDER BY a
 449   );
 450 }
 451
 452 execsql_test 8.3 {
 453   WITH map2 AS (
 454     SELECT * FROM map
 455   )
 456   SELECT sum(a) OVER (
 457     PARTITION BY (
 458       SELECT t FROM map2 WHERE v=a
 459     ) ORDER BY a
 460   ) FROM tx;
 461 }
 462
 463 execsql_test 8.4 {
 464   WITH map2 AS (
 465     SELECT * FROM map
 466   )
 467   SELECT sum(a) OVER win FROM tx
 468   WINDOW win AS (
 469     PARTITION BY (
 470       SELECT t FROM map2 WHERE v=a
 471     ) ORDER BY a
 472   );
 473 }
 474
 475 ==========
 476
 477 execsql_test 9.1 {
 478   DROP TABLE IF EXISTS t1;
 479   DROP TABLE IF EXISTS t2;
 480   CREATE TABLE t1(a INTEGER);
 481   CREATE TABLE t2(y INTEGER);
 482 }
 483
 484 execsql_test 9.2 {
 485   SELECT (
 486     SELECT max(a) OVER ( ORDER BY (SELECT sum(a) FROM t1) )
 487          + min(a) OVER()
 488   )
 489   FROM t1
 490 }
 491
 492
 493 finish_test
 494
 495