/ Check-in [e0255063]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Fix a problem with EXCLUDE clauses on window frames with no ORDER BY.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | window-functions
Files: files | file ages | folders
SHA3-256: e0255063799a2a8531f0eaa8790334509591a5ababc5b915035b46d2faa80363
User & Date: dan 2019-03-19 19:19:53
Wiki:window-functions
Context
2019-03-19
19:39
Add further tests to window8.test. check-in: ec7e224f user: dan tags: window-functions
19:19
Fix a problem with EXCLUDE clauses on window frames with no ORDER BY. check-in: e0255063 user: dan tags: window-functions
17:45
Add tests to ensure that the window functions implementation is not generating code for unnecessary sorts. check-in: e195948a user: dan tags: window-functions
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/window.c.

  1569   1569     VdbeCoverageNeverNull(v);
  1570   1570   
  1571   1571     if( pMWin->eExclude==TK_CURRENT ){
  1572   1572       sqlite3VdbeAddOp3(v, OP_Eq, regCRowid, lblNext, regRowid);
  1573   1573     }else if( pMWin->eExclude!=TK_NO ){
  1574   1574       int addr;
  1575   1575       int addrEq = 0;
  1576         -    KeyInfo *pKeyInfo;
         1576  +    KeyInfo *pKeyInfo = 0;
  1577   1577   
  1578         -    pKeyInfo = sqlite3KeyInfoFromExprList(pParse, pMWin->pOrderBy, 0, 0);
         1578  +    if( pMWin->pOrderBy ){
         1579  +      pKeyInfo = sqlite3KeyInfoFromExprList(pParse, pMWin->pOrderBy, 0, 0);
         1580  +    }
  1579   1581       if( pMWin->eExclude==TK_TIES ){
  1580         -      addrEq = sqlite3VdbeAddOp3(v, OP_Eq, regCRowid, lblNext, regRowid);
         1582  +      addrEq = sqlite3VdbeAddOp3(v, OP_Eq, regCRowid, 0, regRowid);
  1581   1583       }
  1582         -    windowReadPeerValues(p, csr, regPeer);
  1583         -    sqlite3VdbeAddOp3(v, OP_Compare, regPeer, regCPeer, nPeer);
  1584         -    sqlite3VdbeAppendP4(v, (void*)pKeyInfo, P4_KEYINFO);
  1585         -    addr = sqlite3VdbeCurrentAddr(v)+1;
  1586         -    sqlite3VdbeAddOp3(v, OP_Jump, addr, lblNext, addr);
  1587         -    VdbeCoverageEqNe(v);
         1584  +    if( pKeyInfo ){
         1585  +      windowReadPeerValues(p, csr, regPeer);
         1586  +      sqlite3VdbeAddOp3(v, OP_Compare, regPeer, regCPeer, nPeer);
         1587  +      sqlite3VdbeAppendP4(v, (void*)pKeyInfo, P4_KEYINFO);
         1588  +      addr = sqlite3VdbeCurrentAddr(v)+1;
         1589  +      sqlite3VdbeAddOp3(v, OP_Jump, addr, lblNext, addr);
         1590  +      VdbeCoverageEqNe(v);
         1591  +    }else{
         1592  +      sqlite3VdbeAddOp2(v, OP_Goto, 0, lblNext);
         1593  +    }
  1588   1594       if( addrEq ) sqlite3VdbeJumpHere(v, addrEq);
  1589   1595     }
  1590   1596   
  1591   1597     windowAggStep(pParse, pMWin, csr, 0, p->regArg);
  1592   1598   
  1593   1599     sqlite3VdbeResolveLabel(v, lblNext);
  1594   1600     sqlite3VdbeAddOp2(v, OP_Next, csr, addrNext);

Changes to test/window8.tcl.

   217    217   }
   218    218   
   219    219   execsql_test 4.4.2 {
   220    220     SELECT sum(b) OVER (
   221    221       ORDER BY a DESC NULLS LAST ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
   222    222     ) FROM t1 ORDER BY 1 NULLS FIRST;
   223    223   }
          224  +
          225  +==========
          226  +
          227  +execsql_test 5.0 {
          228  +  INSERT INTO t3 VALUES
          229  +    (NULL, 'bb', 355), (NULL, 'cc', 158), (NULL, 'aa', 399), 
          230  +    ('JJ', NULL, 839), ('FF', NULL, 618), ('BB', NULL, 393), 
          231  +    (NULL, 'bb', 629), (NULL, NULL, 667), (NULL, NULL, 870);
          232  +}
          233  +
          234  +foreach {tn ex} {
          235  +  1  { EXCLUDE NO OTHERS }
          236  +  2  { EXCLUDE CURRENT ROW }
          237  +  3  { EXCLUDE GROUP }
          238  +  4  { EXCLUDE TIES }
          239  +} {
          240  +  foreach {tn2 frame} {
          241  +    1 { RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING }
          242  +    2 { ORDER BY a NULLS FIRST 
          243  +        RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING }
          244  +    3 { PARTITION BY coalesce(a, '') 
          245  +        RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING }
          246  +    4 { ORDER BY a NULLS FIRST GROUPS 6 PRECEDING }
          247  +    5 { ORDER BY c NULLS FIRST RANGE BETWEEN 6 PRECEDING AND 7 FOLLOWING }
          248  +  } {
          249  +    execsql_test 5.$tn.$tn2.1 "
          250  +      SELECT max(c) OVER win,
          251  +             min(c) OVER win
          252  +      FROM t3
          253  +      WINDOW win AS ( $frame $ex )
          254  +      ORDER BY 1 NULLS FIRST, 2 NULLS FIRST
          255  +    "
          256  +
          257  +    execsql_test 5.$tn.$tn2.2 "
          258  +      SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
          259  +             rank() OVER win
          260  +      FROM t3
          261  +      WINDOW win AS ( $frame $ex )
          262  +      ORDER BY 1 NULLS FIRST, 2 NULLS FIRST
          263  +    "
          264  +  }
          265  +}
          266  +
   224    267   finish_test
   225    268   
   226    269   

Changes to test/window8.test.

  3544   3544   } {3   6   9   9   12}
  3545   3545   
  3546   3546   do_execsql_test 4.4.2 {
  3547   3547     SELECT sum(b) OVER (
  3548   3548       ORDER BY a DESC  ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  3549   3549     ) FROM t1 ORDER BY 1 ;
  3550   3550   } {5   6   8   9   10}
         3551  +
         3552  +#==========================================================================
         3553  +
         3554  +do_execsql_test 5.0 {
         3555  +  INSERT INTO t3 VALUES
         3556  +    (NULL, 'bb', 355), (NULL, 'cc', 158), (NULL, 'aa', 399), 
         3557  +    ('JJ', NULL, 839), ('FF', NULL, 618), ('BB', NULL, 393), 
         3558  +    (NULL, 'bb', 629), (NULL, NULL, 667), (NULL, NULL, 870);
         3559  +} {}
         3560  +
         3561  +do_execsql_test 5.1.1.1 {
         3562  +  SELECT max(c) OVER win,
         3563  +             min(c) OVER win
         3564  +      FROM t3
         3565  +      WINDOW win AS (  RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING   EXCLUDE NO OTHERS  )
         3566  +      ORDER BY 1 , 2
         3567  +} {979 102   979 102   979 102   979 102   979 102   979 102   979 102
         3568  +  979 102   979 102   979 102   979 102   979 102   979 102   979 102
         3569  +  979 102   979 102   979 102   979 102   979 102   979 102   979 102
         3570  +  979 102   979 102   979 102   979 102   979 102   979 102   979 102
         3571  +  979 102   979 102   979 102   979 102   979 102   979 102   979 102
         3572  +  979 102   979 102   979 102   979 102   979 102   979 102   979 102
         3573  +  979 102   979 102   979 102   979 102   979 102   979 102   979 102
         3574  +  979 102   979 102   979 102   979 102   979 102   979 102   979 102
         3575  +  979 102   979 102   979 102   979 102   979 102   979 102   979 102
         3576  +  979 102   979 102   979 102   979 102   979 102   979 102   979 102
         3577  +  979 102   979 102   979 102   979 102   979 102   979 102   979 102
         3578  +  979 102   979 102   979 102   979 102   979 102   979 102   979 102
         3579  +  979 102   979 102   979 102   979 102   979 102}
         3580  +
         3581  +do_execsql_test 5.1.1.2 {
         3582  +  SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
         3583  +             rank() OVER win
         3584  +      FROM t3
         3585  +      WINDOW win AS (  RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING   EXCLUDE NO OTHERS  )
         3586  +      ORDER BY 1 , 2
         3587  +} {23155 1   23155 1   23155 1   23155 1   23155 1   23155 1   23155 1
         3588  +  23155 1   23155 1   23155 1   23155 1   23155 1   23155 1   23155 1
         3589  +  23155 1   23155 1   23155 1   23155 1   23155 1   23155 1   23155 1
         3590  +  23155 1   23155 1   23155 1   23155 1   23155 1   23155 1   23155 1
         3591  +  23155 1   23155 1   23155 1   23155 1   23155 1   23155 1   23155 1
         3592  +  23155 1   23155 1   23155 1   23155 1   23155 1   23155 1   23155 1
         3593  +  23155 1   23155 1   23155 1   23155 1   23155 1   23155 1   23155 1
         3594  +  23155 1   23155 1   23155 1   23155 1   23155 1   23155 1   23155 1
         3595  +  23155 1   23155 1   23155 1   23155 1   23155 1   23155 1   23155 1
         3596  +  23155 1   23155 1   23155 1   23155 1   23155 1   23155 1   23155 1
         3597  +  23155 1   23155 1   23155 1   23155 1   23155 1   23155 1   23155 1
         3598  +  23155 1   23155 1   23155 1   23155 1   23155 1   23155 1   23155 1
         3599  +  23155 1   23155 1   23155 1   23155 1   23155 1}
         3600  +
         3601  +do_execsql_test 5.1.2.1 {
         3602  +  SELECT max(c) OVER win,
         3603  +             min(c) OVER win
         3604  +      FROM t3
         3605  +      WINDOW win AS (  ORDER BY a  
         3606  +        RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING   EXCLUDE NO OTHERS  )
         3607  +      ORDER BY 1 , 2
         3608  +} {899 113   899 113   899 113   899 113   899 113   899 113   899 113
         3609  +  899 113   899 113   899 113   899 113   899 113   899 113   899 113
         3610  +  899 113   899 113   979 102   979 102   979 102   979 102   979 102
         3611  +  979 102   979 102   979 102   979 102   979 102   979 102   979 102
         3612  +  979 102   979 102   979 102   979 102   979 102   979 102   979 102
         3613  +  979 102   979 102   979 102   979 102   979 102   979 102   979 102
         3614  +  979 102   979 102   979 102   979 102   979 102   979 102   979 102
         3615  +  979 102   979 102   979 102   979 102   979 102   979 102   979 102
         3616  +  979 102   979 102   979 102   979 102   979 102   979 102   979 102
         3617  +  979 102   979 102   979 102   979 102   979 102   979 102   979 102
         3618  +  979 102   979 102   979 113   979 113   979 113   979 113   979 113
         3619  +  979 113   979 113   979 113   979 113   979 113   979 113   979 113
         3620  +  979 113   979 113   979 113   979 113   979 113}
         3621  +
         3622  +do_execsql_test 5.1.2.2 {
         3623  +  SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
         3624  +             rank() OVER win
         3625  +      FROM t3
         3626  +      WINDOW win AS (  ORDER BY a  
         3627  +        RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING   EXCLUDE NO OTHERS  )
         3628  +      ORDER BY 1 , 2
         3629  +} {2947 81   2947 81   2947 81   2947 81   2947 81   2947 81   2947 81
         3630  +  2947 81   2947 81   5287 74   5287 74   5287 74   5287 74   5287 74
         3631  +  5287 74   5287 74   8400 65   8400 65   8400 65   8400 65   8400 65
         3632  +  8400 65   8400 65   8400 65   8400 65   9664 57   9664 57   9664 57
         3633  +  9664 57   9664 57   9664 57   9664 57   9664 57   10626 46   10626 46
         3634  +  10626 46   10626 46   10626 46   10626 46   10626 46   10626 46   10626 46
         3635  +  10626 46   10626 46   12145 41   12145 41   12145 41   12145 41   12145 41
         3636  +  13949 34   13949 34   13949 34   13949 34   13949 34   13949 34   13949 34
         3637  +  15315 28   15315 28   15315 28   15315 28   15315 28   15315 28   18796 15
         3638  +  18796 15   18796 15   18796 15   18796 15   18796 15   18796 15   18796 15
         3639  +  18796 15   18796 15   18796 15   18796 15   18796 15   21105 7   21105 7
         3640  +  21105 7   21105 7   21105 7   21105 7   21105 7   21105 7   23155 1
         3641  +  23155 1   23155 1   23155 1   23155 1   23155 1}
         3642  +
         3643  +do_execsql_test 5.1.3.1 {
         3644  +  SELECT max(c) OVER win,
         3645  +             min(c) OVER win
         3646  +      FROM t3
         3647  +      WINDOW win AS (  PARTITION BY coalesce(a, '') 
         3648  +        RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING   EXCLUDE NO OTHERS  )
         3649  +      ORDER BY 1 , 2
         3650  +} {777 113   777 113   777 113   777 113   777 113   805 250   805 250
         3651  +  805 250   805 250   805 250   805 250   805 250   822 158   822 158
         3652  +  822 158   822 158   822 158   822 158   840 247   840 247   840 247
         3653  +  840 247   840 247   840 247   840 247   840 247   840 247   840 247
         3654  +  840 247   840 247   840 247   870 158   870 158   870 158   870 158
         3655  +  870 158   870 158   899 113   899 113   899 113   899 113   899 113
         3656  +  899 113   899 113   899 113   899 113   934 223   934 223   934 223
         3657  +  934 223   934 223   934 223   934 223   934 223   938 102   938 102
         3658  +  938 102   938 102   938 102   938 102   938 102   938 102   938 102
         3659  +  938 102   938 102   938 148   938 148   938 148   938 148   938 148
         3660  +  938 148   938 148   938 148   959 224   959 224   959 224   959 224
         3661  +  959 224   959 224   959 224   979 133   979 133   979 133   979 133
         3662  +  979 133   979 133   979 133   979 133   979 133}
         3663  +
         3664  +do_execsql_test 5.1.3.2 {
         3665  +  SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
         3666  +             rank() OVER win
         3667  +      FROM t3
         3668  +      WINDOW win AS (  PARTITION BY coalesce(a, '') 
         3669  +        RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING   EXCLUDE NO OTHERS  )
         3670  +      ORDER BY 1 , 2
         3671  +} {962 1   962 1   962 1   962 1   962 1   962 1   962 1   962 1   962 1
         3672  +  962 1   962 1   1264 1   1264 1   1264 1   1264 1   1264 1   1264 1
         3673  +  1264 1   1264 1   1366 1   1366 1   1366 1   1366 1   1366 1   1366 1
         3674  +  1519 1   1519 1   1519 1   1519 1   1519 1   1804 1   1804 1   1804 1
         3675  +  1804 1   1804 1   1804 1   1804 1   2050 1   2050 1   2050 1   2050 1
         3676  +  2050 1   2050 1   2309 1   2309 1   2309 1   2309 1   2309 1   2309 1
         3677  +  2309 1   2309 1   2340 1   2340 1   2340 1   2340 1   2340 1   2340 1
         3678  +  2340 1   2947 1   2947 1   2947 1   2947 1   2947 1   2947 1   2947 1
         3679  +  2947 1   2947 1   3113 1   3113 1   3113 1   3113 1   3113 1   3113 1
         3680  +  3113 1   3113 1   3113 1   3481 1   3481 1   3481 1   3481 1   3481 1
         3681  +  3481 1   3481 1   3481 1   3481 1   3481 1   3481 1   3481 1   3481 1}
         3682  +
         3683  +do_execsql_test 5.1.4.1 {
         3684  +  SELECT max(c) OVER win,
         3685  +             min(c) OVER win
         3686  +      FROM t3
         3687  +      WINDOW win AS (  ORDER BY a  GROUPS 6 PRECEDING   EXCLUDE NO OTHERS  )
         3688  +      ORDER BY 1 , 2
         3689  +} {870 158   870 158   870 158   870 158   870 158   870 158   934 158
         3690  +  934 158   934 158   934 158   934 158   934 158   934 158   934 158
         3691  +  934 158   934 158   934 158   934 158   934 158   934 158   934 158
         3692  +  934 158   934 158   934 158   934 158   934 158   934 158   934 158
         3693  +  934 158   934 158   934 158   934 158   934 158   959 102   959 102
         3694  +  959 102   959 102   959 102   959 102   959 102   959 102   959 102
         3695  +  959 102   959 102   959 102   959 102   959 102   959 102   959 102
         3696  +  959 102   959 102   959 102   959 113   959 113   959 113   959 113
         3697  +  959 113   959 158   959 158   959 158   959 158   959 158   959 158
         3698  +  959 158   979 102   979 102   979 102   979 102   979 102   979 102
         3699  +  979 102   979 102   979 102   979 102   979 102   979 102   979 102
         3700  +  979 102   979 102   979 102   979 102   979 102   979 102   979 102
         3701  +  979 102   979 102   979 102   979 102   979 102}
         3702  +
         3703  +do_execsql_test 5.1.4.2 {
         3704  +  SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
         3705  +             rank() OVER win
         3706  +      FROM t3
         3707  +      WINDOW win AS (  ORDER BY a  GROUPS 6 PRECEDING   EXCLUDE NO OTHERS  )
         3708  +      ORDER BY 1 , 2
         3709  +} {2050 1   2050 1   2050 1   2050 1   2050 1   2050 1   4359 7   4359 7
         3710  +  4359 7   4359 7   4359 7   4359 7   4359 7   4359 7   7840 15   7840 15
         3711  +  7840 15   7840 15   7840 15   7840 15   7840 15   7840 15   7840 15
         3712  +  7840 15   7840 15   7840 15   7840 15   9206 28   9206 28   9206 28
         3713  +  9206 28   9206 28   9206 28   11010 34   11010 34   11010 34   11010 34
         3714  +  11010 34   11010 34   11010 34   12368 74   12368 74   12368 74   12368 74
         3715  +  12368 74   12368 74   12368 74   12529 41   12529 41   12529 41   12529 41
         3716  +  12529 41   12705 57   12705 57   12705 57   12705 57   12705 57   12705 57
         3717  +  12705 57   12705 57   13491 46   13491 46   13491 46   13491 46   13491 46
         3718  +  13491 46   13491 46   13491 46   13491 46   13491 46   13491 46   13509 65
         3719  +  13509 65   13509 65   13509 65   13509 65   13509 65   13509 65   13509 65
         3720  +  13509 65   13949 81   13949 81   13949 81   13949 81   13949 81   13949 81
         3721  +  13949 81   13949 81   13949 81}
         3722  +
         3723  +do_execsql_test 5.1.5.1 {
         3724  +  SELECT max(c) OVER win,
         3725  +             min(c) OVER win
         3726  +      FROM t3
         3727  +      WINDOW win AS (  ORDER BY c  RANGE BETWEEN 6 PRECEDING AND 7 FOLLOWING   EXCLUDE NO OTHERS  )
         3728  +      ORDER BY 1 , 2
         3729  +} {102 102   113 113   113 113   133 133   148 148   160 158   160 158
         3730  +  160 158   208 208   224 223   224 223   239 234   239 234   239 234
         3731  +  252 247   257 247   257 247   257 250   257 252   295 295   309 309
         3732  +  336 330   336 330   336 330   346 346   355 354   355 354   355 354
         3733  +  399 393   399 393   399 393   399 393   399 393   412 412   421 421
         3734  +  430 430   443 443   480 480   480 480   574 572   574 572   607 607
         3735  +  618 618   618 618   634 627   634 627   634 627   634 627   634 629
         3736  +  652 652   667 660   671 667   671 667   671 667   671 667   683 683
         3737  +  711 705   716 705   716 711   730 726   730 726   762 759   768 759
         3738  +  768 762   768 762   777 777   792 786   794 786   794 786   794 790
         3739  +  805 805   822 822   845 839   845 839   845 839   845 839   845 839
         3740  +  870 870   870 870   870 870   899 899   911 911   934 929   938 929
         3741  +  938 934   938 934   963 959   963 959   979 979}
         3742  +
         3743  +do_execsql_test 5.1.5.2 {
         3744  +  SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
         3745  +             rank() OVER win
         3746  +      FROM t3
         3747  +      WINDOW win AS (  ORDER BY c  RANGE BETWEEN 6 PRECEDING AND 7 FOLLOWING   EXCLUDE NO OTHERS  )
         3748  +      ORDER BY 1 , 2
         3749  +} {{} 1   {} 5   {} 6   {} 6   {} 8   {} 9   {} 25   {} 34   {} 36   {} 38
         3750  +  {} 38   {} 40   {} 41   {} 43   {} 43   {} 50   {} 60   {} 61   {} 64
         3751  +  {} 64   {} 67   {} 68   {} 69   {} 70   {} 72   {} 78   {} 78   {} 78
         3752  +  {} 85   {} 85   133 4   223 10   223 11   226 2   226 2   239 12   239 13
         3753  +  239 14   247 15   257 18   257 19   295 20   309 21   335 22   335 23
         3754  +  335 24   421 35   443 37   504 16   504 17   607 42   683 56   710 26
         3755  +  710 27   710 27   711 59   759 62   759 63   777 66   805 71   899 81
         3756  +  911 82   929 83   929 84   979 89   1334 51   1416 57   1416 58   1584 29
         3757  +  1584 29   1584 31   1584 32   1584 32   1891 49   1922 87   1922 88
         3758  +  2005 52   2005 52   2005 54   2005 55   2518 45   2518 46   2518 46
         3759  +  2518 48   2523 73   2523 73   2523 75   2523 76   2523 77}
         3760  +
         3761  +do_execsql_test 5.2.1.1 {
         3762  +  SELECT max(c) OVER win,
         3763  +             min(c) OVER win
         3764  +      FROM t3
         3765  +      WINDOW win AS (  RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING   EXCLUDE CURRENT ROW  )
         3766  +      ORDER BY 1 , 2
         3767  +} {963 102   979 102   979 102   979 102   979 102   979 102   979 102
         3768  +  979 102   979 102   979 102   979 102   979 102   979 102   979 102
         3769  +  979 102   979 102   979 102   979 102   979 102   979 102   979 102
         3770  +  979 102   979 102   979 102   979 102   979 102   979 102   979 102
         3771  +  979 102   979 102   979 102   979 102   979 102   979 102   979 102
         3772  +  979 102   979 102   979 102   979 102   979 102   979 102   979 102
         3773  +  979 102   979 102   979 102   979 102   979 102   979 102   979 102
         3774  +  979 102   979 102   979 102   979 102   979 102   979 102   979 102
         3775  +  979 102   979 102   979 102   979 102   979 102   979 102   979 102
         3776  +  979 102   979 102   979 102   979 102   979 102   979 102   979 102
         3777  +  979 102   979 102   979 102   979 102   979 102   979 102   979 102
         3778  +  979 102   979 102   979 102   979 102   979 102   979 102   979 102
         3779  +  979 102   979 102   979 102   979 102   979 113}
         3780  +
         3781  +do_execsql_test 5.2.1.2 {
         3782  +  SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
         3783  +             rank() OVER win
         3784  +      FROM t3
         3785  +      WINDOW win AS (  RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING   EXCLUDE CURRENT ROW  )
         3786  +      ORDER BY 1 , 2
         3787  +} {22176 1   22192 1   22196 1   22226 1   22244 1   22256 1   22310 1
         3788  +  22316 1   22316 1   22350 1   22378 1   22396 1   22444 1   22450 1
         3789  +  22472 1   22484 1   22488 1   22488 1   22522 1   22526 1   22526 1
         3790  +  22528 1   22548 1   22712 1   22734 1   22756 1   22756 1   22762 1
         3791  +  22762 1   22800 1   22800 1   22820 1   22846 1   22860 1   22898 1
         3792  +  22908 1   22916 1   22932 1   23022 1   23042 1   23042 1   23155 1
         3793  +  23155 1   23155 1   23155 1   23155 1   23155 1   23155 1   23155 1
         3794  +  23155 1   23155 1   23155 1   23155 1   23155 1   23155 1   23155 1
         3795  +  23155 1   23155 1   23155 1   23155 1   23155 1   23155 1   23155 1
         3796  +  23155 1   23155 1   23155 1   23155 1   23155 1   23155 1   23155 1
         3797  +  23155 1   23155 1   23155 1   23155 1   23155 1   23155 1   23155 1
         3798  +  23155 1   23155 1   23155 1   23155 1   23155 1   23155 1   23155 1
         3799  +  23155 1   23155 1   23155 1   23155 1   23155 1}
         3800  +
         3801  +do_execsql_test 5.2.2.1 {
         3802  +  SELECT max(c) OVER win,
         3803  +             min(c) OVER win
         3804  +      FROM t3
         3805  +      WINDOW win AS (  ORDER BY a  
         3806  +        RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING   EXCLUDE CURRENT ROW  )
         3807  +      ORDER BY 1 , 2
         3808  +} {839 113   899 113   899 113   899 113   899 113   899 113   899 113
         3809  +  899 113   899 113   899 113   899 113   899 113   899 113   899 113
         3810  +  899 113   899 234   963 113   979 102   979 102   979 102   979 102
         3811  +  979 102   979 102   979 102   979 102   979 102   979 102   979 102
         3812  +  979 102   979 102   979 102   979 102   979 102   979 102   979 102
         3813  +  979 102   979 102   979 102   979 102   979 102   979 102   979 102
         3814  +  979 102   979 102   979 102   979 102   979 102   979 102   979 102
         3815  +  979 102   979 102   979 102   979 102   979 102   979 102   979 102
         3816  +  979 102   979 102   979 102   979 102   979 102   979 102   979 102
         3817  +  979 102   979 102   979 102   979 102   979 102   979 102   979 102
         3818  +  979 102   979 102   979 113   979 113   979 113   979 113   979 113
         3819  +  979 113   979 113   979 113   979 113   979 113   979 113   979 113
         3820  +  979 113   979 113   979 113   979 113   979 113}
         3821  +
         3822  +do_execsql_test 5.2.2.2 {
         3823  +  SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
         3824  +             rank() OVER win
         3825  +      FROM t3
         3826  +      WINDOW win AS (  ORDER BY a  
         3827  +        RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING   EXCLUDE CURRENT ROW  )
         3828  +      ORDER BY 1 , 2
         3829  +} {2048 81   2108 81   2108 81   2690 81   2834 81   2947 81   2947 81
         3830  +  2947 81   2947 81   4482 74   4616 74   4844 74   4866 74   5287 74
         3831  +  5287 74   5287 74   7421 65   7437 65   7717 65   8045 65   8267 65
         3832  +  8400 65   8400 65   8400 65   8400 65   8735 57   9329 57   9664 57
         3833  +  9664 57   9664 57   9664 57   9664 57   9664 57   9959 46   10331 46
         3834  +  10626 46   10626 46   10626 46   10626 46   10626 46   10626 46   10626 46
         3835  +  10626 46   10626 46   11368 41   11516 41   12032 41   12145 41   12145 41
         3836  +  12990 34   13104 34   13949 34   13949 34   13949 34   13949 34   13949 34
         3837  +  14556 28   14708 28   15315 28   15315 28   15315 28   15315 28   18085 15
         3838  +  18091 15   18163 15   18397 15   18403 15   18403 15   18549 15   18796 15
         3839  +  18796 15   18796 15   18796 15   18796 15   18796 15   20194 7   20478 7
         3840  +  20796 7   20866 7   20882 7   21105 7   21105 7   21105 7   22488 1
         3841  +  22526 1   22756 1   22800 1   23155 1   23155 1}
         3842  +
         3843  +do_execsql_test 5.2.3.1 {
         3844  +  SELECT max(c) OVER win,
         3845  +             min(c) OVER win
         3846  +      FROM t3
         3847  +      WINDOW win AS (  PARTITION BY coalesce(a, '') 
         3848  +        RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING   EXCLUDE CURRENT ROW  )
         3849  +      ORDER BY 1 , 2
         3850  +} {667 158   671 250   759 158   768 113   777 113   777 113   777 113
         3851  +  777 252   792 247   805 250   805 250   805 250   805 250   805 250
         3852  +  805 398   822 158   822 158   822 158   822 158   822 346   839 113
         3853  +  840 247   840 247   840 247   840 247   840 247   840 247   840 247
         3854  +  840 247   840 247   840 247   840 247   840 393   845 224   870 102
         3855  +  870 158   870 158   870 158   870 158   870 355   899 113   899 113
         3856  +  899 113   899 113   899 113   899 113   899 113   899 234   911 223
         3857  +  929 148   934 223   934 223   934 223   934 223   934 223   934 223
         3858  +  934 239   938 102   938 102   938 102   938 102   938 102   938 102
         3859  +  938 102   938 102   938 102   938 148   938 148   938 148   938 148
         3860  +  938 148   938 148   938 160   938 208   959 224   959 224   959 224
         3861  +  959 224   959 224   959 238   963 133   979 133   979 133   979 133
         3862  +  979 133   979 133   979 133   979 133   979 330}
         3863  +
         3864  +do_execsql_test 5.2.3.2 {
         3865  +  SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
         3866  +             rank() OVER win
         3867  +      FROM t3
         3868  +      WINDOW win AS (  PARTITION BY coalesce(a, '') 
         3869  +        RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING   EXCLUDE CURRENT ROW  )
         3870  +      ORDER BY 1 , 2
         3871  +} {295 1   335 1   607 1   667 1   742 1   759 1   845 1   890 1   929 1
         3872  +  959 1   962 1   962 1   962 1   962 1   962 1   962 1   962 1   962 1
         3873  +  962 1   1264 1   1264 1   1264 1   1264 1   1264 1   1264 1   1366 1
         3874  +  1366 1   1366 1   1366 1   1383 1   1398 1   1406 1   1421 1   1519 1
         3875  +  1519 1   1535 1   1651 1   1669 1   1682 1   1695 1   1804 1   1804 1
         3876  +  1804 1   1804 1   1804 1   1897 1   1919 1   2000 1   2048 1   2050 1
         3877  +  2050 1   2070 1   2086 1   2108 1   2108 1   2134 1   2150 1   2309 1
         3878  +  2309 1   2309 1   2340 1   2340 1   2340 1   2430 1   2690 1   2758 1
         3879  +  2770 1   2776 1   2834 1   2848 1   2947 1   2947 1   2947 1   2947 1
         3880  +  2980 1   3082 1   3088 1   3088 1   3113 1   3113 1   3113 1   3113 1
         3881  +  3234 1   3481 1   3481 1   3481 1   3481 1   3481 1   3481 1}
         3882  +
         3883  +do_execsql_test 5.2.4.1 {
         3884  +  SELECT max(c) OVER win,
         3885  +             min(c) OVER win
         3886  +      FROM t3
         3887  +      WINDOW win AS (  ORDER BY a  GROUPS 6 PRECEDING   EXCLUDE CURRENT ROW  )
         3888  +      ORDER BY 1 , 2
         3889  +} {667 158   870 158   870 158   870 158   870 158   870 355   911 158
         3890  +  934 158   934 158   934 158   934 158   934 158   934 158   934 158
         3891  +  934 158   934 158   934 158   934 158   934 158   934 158   934 158
         3892  +  934 158   934 158   934 158   934 158   934 158   934 158   934 158
         3893  +  934 158   934 158   934 158   934 158   934 158   934 158   959 102
         3894  +  959 102   959 102   959 102   959 102   959 102   959 102   959 102
         3895  +  959 102   959 102   959 102   959 102   959 102   959 102   959 102
         3896  +  959 102   959 102   959 102   959 113   959 113   959 113   959 113
         3897  +  959 113   959 158   959 158   959 158   959 158   959 158   959 158
         3898  +  959 158   963 102   979 102   979 102   979 102   979 102   979 102
         3899  +  979 102   979 102   979 102   979 102   979 102   979 102   979 102
         3900  +  979 102   979 102   979 102   979 102   979 102   979 102   979 102
         3901  +  979 102   979 102   979 102   979 102   979 102}
         3902  +
         3903  +do_execsql_test 5.2.4.2 {
         3904  +  SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
         3905  +             rank() OVER win
         3906  +      FROM t3
         3907  +      WINDOW win AS (  ORDER BY a  GROUPS 6 PRECEDING   EXCLUDE CURRENT ROW  )
         3908  +      ORDER BY 1 , 2
         3909  +} {1383 1   1421 1   1651 1   1695 1   2050 1   2050 1   3448 7   3732 7
         3910  +  4050 7   4120 7   4136 7   4359 7   4359 7   4359 7   7129 15   7135 15
         3911  +  7207 15   7441 15   7447 15   7447 15   7593 15   7840 15   7840 15
         3912  +  7840 15   7840 15   7840 15   7840 15   8447 28   8599 28   9206 28
         3913  +  9206 28   9206 28   9206 28   10051 34   10165 34   11010 34   11010 34
         3914  +  11010 34   11010 34   11010 34   11563 74   11697 74   11752 41   11776 57
         3915  +  11900 41   11925 74   11947 74   12368 74   12368 74   12368 74   12370 57
         3916  +  12416 41   12529 41   12529 41   12530 65   12546 65   12705 57   12705 57
         3917  +  12705 57   12705 57   12705 57   12705 57   12824 46   12826 65   13050 81
         3918  +  13110 81   13110 81   13154 65   13196 46   13376 65   13491 46   13491 46
         3919  +  13491 46   13491 46   13491 46   13491 46   13491 46   13491 46   13491 46
         3920  +  13509 65   13509 65   13509 65   13509 65   13692 81   13836 81   13949 81
         3921  +  13949 81   13949 81   13949 81}
         3922  +
         3923  +do_execsql_test 5.2.5.1 {
         3924  +  SELECT max(c) OVER win,
         3925  +             min(c) OVER win
         3926  +      FROM t3
         3927  +      WINDOW win AS (  ORDER BY c  RANGE BETWEEN 6 PRECEDING AND 7 FOLLOWING   EXCLUDE CURRENT ROW  )
         3928  +      ORDER BY 1 , 2
         3929  +} {{} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}
         3930  +  {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}
         3931  +  {} {}   {} {}   113 113   113 113   158 158   160 158   160 158   223 223
         3932  +  224 224   238 234   239 234   239 238   252 250   256 252   257 247
         3933  +  257 247   257 250   335 330   336 330   336 335   355 354   355 354
         3934  +  355 355   399 393   399 393   399 393   399 393   399 393   480 480
         3935  +  480 480   572 572   574 574   618 618   618 618   633 629   634 627
         3936  +  634 627   634 627   634 629   667 667   670 667   671 667   671 667
         3937  +  671 667   711 711   711 711   716 705   726 726   730 730   762 762
         3938  +  768 759   768 762   768 762   792 790   792 790   794 786   794 786
         3939  +  844 839   845 839   845 839   845 839   845 839   870 870   870 870
         3940  +  870 870   934 934   938 929   938 934   938 934   959 959   963 963}
         3941  +
         3942  +do_execsql_test 5.2.5.2 {
         3943  +  SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
         3944  +             rank() OVER win
         3945  +      FROM t3
         3946  +      WINDOW win AS (  ORDER BY c  RANGE BETWEEN 6 PRECEDING AND 7 FOLLOWING   EXCLUDE CURRENT ROW  )
         3947  +      ORDER BY 1 , 2
         3948  +} {{} 1   {} 4   {} 5   {} 6   {} 6   {} 8   {} 9   {} 10   {} 14   {} 15
         3949  +  {} 19   {} 20   {} 21   {} 23   {} 25   {} 34   {} 35   {} 36   {} 37
         3950  +  {} 38   {} 38   {} 40   {} 41   {} 42   {} 43   {} 43   {} 50   {} 56
         3951  +  {} 60   {} 61   {} 62   {} 64   {} 64   {} 66   {} 67   {} 68   {} 69
         3952  +  {} 70   {} 71   {} 72   {} 78   {} 78   {} 78   {} 81   {} 82   {} 83
         3953  +  {} 85   {} 85   {} 89   113 2   113 2   223 11   239 12   239 13   257 18
         3954  +  335 22   335 24   355 27   355 27   504 16   504 17   705 58   710 26
         3955  +  711 57   711 59   759 63   929 84   959 88   963 87   1185 32   1185 32
         3956  +  1191 29   1191 29   1334 51   1334 55   1338 52   1338 52   1584 31
         3957  +  1678 77   1684 73   1684 73   1885 48   1889 46   1889 46   1891 45
         3958  +  1891 49   2005 54   2523 75   2523 76}
         3959  +
         3960  +do_execsql_test 5.3.1.1 {
         3961  +  SELECT max(c) OVER win,
         3962  +             min(c) OVER win
         3963  +      FROM t3
         3964  +      WINDOW win AS (  RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING   EXCLUDE GROUP  )
         3965  +      ORDER BY 1 , 2
         3966  +} {{} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}
         3967  +  {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}
         3968  +  {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}
         3969  +  {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}
         3970  +  {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}
         3971  +  {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}
         3972  +  {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}
         3973  +  {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}
         3974  +  {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}
         3975  +  {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}}
         3976  +
         3977  +do_execsql_test 5.3.1.2 {
         3978  +  SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
         3979  +             rank() OVER win
         3980  +      FROM t3
         3981  +      WINDOW win AS (  RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING   EXCLUDE GROUP  )
         3982  +      ORDER BY 1 , 2
         3983  +} {{} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1
         3984  +  {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1
         3985  +  {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1
         3986  +  {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1
         3987  +  {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1
         3988  +  {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1
         3989  +  {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1
         3990  +  {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1
         3991  +  {} 1}
         3992  +
         3993  +do_execsql_test 5.3.2.1 {
         3994  +  SELECT max(c) OVER win,
         3995  +             min(c) OVER win
         3996  +      FROM t3
         3997  +      WINDOW win AS (  ORDER BY a  
         3998  +        RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING   EXCLUDE GROUP  )
         3999  +      ORDER BY 1 , 2
         4000  +} {{} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}
         4001  +  899 113   899 113   899 113   899 113   899 113   899 113   899 113
         4002  +  899 113   899 113   899 113   899 113   899 113   899 113   899 113
         4003  +  899 113   899 113   979 102   979 102   979 102   979 102   979 102
         4004  +  979 102   979 102   979 102   979 102   979 102   979 102   979 102
         4005  +  979 102   979 102   979 102   979 102   979 102   979 102   979 102
         4006  +  979 102   979 102   979 102   979 102   979 102   979 102   979 102
         4007  +  979 102   979 102   979 102   979 102   979 102   979 102   979 102
         4008  +  979 102   979 102   979 102   979 102   979 102   979 102   979 102
         4009  +  979 102   979 102   979 102   979 102   979 102   979 113   979 113
         4010  +  979 113   979 113   979 113   979 113   979 113   979 113   979 113
         4011  +  979 113   979 113   979 113   979 113   979 113   979 113   979 113
         4012  +  979 113   979 113   979 113}
         4013  +
         4014  +do_execsql_test 5.3.2.2 {
         4015  +  SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
         4016  +             rank() OVER win
         4017  +      FROM t3
         4018  +      WINDOW win AS (  ORDER BY a  
         4019  +        RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING   EXCLUDE GROUP  )
         4020  +      ORDER BY 1 , 2
         4021  +} {{} 81   {} 81   {} 81   {} 81   {} 81   {} 81   {} 81   {} 81   {} 81
         4022  +  2947 74   2947 74   2947 74   2947 74   2947 74   2947 74   2947 74
         4023  +  5287 65   5287 65   5287 65   5287 65   5287 65   5287 65   5287 65
         4024  +  5287 65   5287 65   8400 57   8400 57   8400 57   8400 57   8400 57
         4025  +  8400 57   8400 57   8400 57   9664 46   9664 46   9664 46   9664 46
         4026  +  9664 46   9664 46   9664 46   9664 46   9664 46   9664 46   9664 46
         4027  +  10626 41   10626 41   10626 41   10626 41   10626 41   12145 34   12145 34
         4028  +  12145 34   12145 34   12145 34   12145 34   12145 34   13949 28   13949 28
         4029  +  13949 28   13949 28   13949 28   13949 28   15315 15   15315 15   15315 15
         4030  +  15315 15   15315 15   15315 15   15315 15   15315 15   15315 15   15315 15
         4031  +  15315 15   15315 15   15315 15   18796 7   18796 7   18796 7   18796 7
         4032  +  18796 7   18796 7   18796 7   18796 7   21105 1   21105 1   21105 1
         4033  +  21105 1   21105 1   21105 1}
         4034  +
         4035  +do_execsql_test 5.3.3.1 {
         4036  +  SELECT max(c) OVER win,
         4037  +             min(c) OVER win
         4038  +      FROM t3
         4039  +      WINDOW win AS (  PARTITION BY coalesce(a, '') 
         4040  +        RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING   EXCLUDE GROUP  )
         4041  +      ORDER BY 1 , 2
         4042  +} {{} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}
         4043  +  {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}
         4044  +  {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}
         4045  +  {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}
         4046  +  {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}
         4047  +  {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}
         4048  +  {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}
         4049  +  {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}
         4050  +  {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}
         4051  +  {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}}
         4052  +
         4053  +do_execsql_test 5.3.3.2 {
         4054  +  SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
         4055  +             rank() OVER win
         4056  +      FROM t3
         4057  +      WINDOW win AS (  PARTITION BY coalesce(a, '') 
         4058  +        RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING   EXCLUDE GROUP  )
         4059  +      ORDER BY 1 , 2
         4060  +} {{} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1
         4061  +  {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1
         4062  +  {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1
         4063  +  {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1
         4064  +  {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1
         4065  +  {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1
         4066  +  {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1
         4067  +  {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1
         4068  +  {} 1}
         4069  +
         4070  +do_execsql_test 5.3.4.1 {
         4071  +  SELECT max(c) OVER win,
         4072  +             min(c) OVER win
         4073  +      FROM t3
         4074  +      WINDOW win AS (  ORDER BY a  GROUPS 6 PRECEDING   EXCLUDE GROUP  )
         4075  +      ORDER BY 1 , 2
         4076  +} {{} {}   {} {}   {} {}   {} {}   {} {}   {} {}   870 158   870 158   870 158
         4077  +  870 158   870 158   870 158   870 158   870 158   934 158   934 158
         4078  +  934 158   934 158   934 158   934 158   934 158   934 158   934 158
         4079  +  934 158   934 158   934 158   934 158   934 158   934 158   934 158
         4080  +  934 158   934 158   934 158   934 158   934 158   934 158   934 158
         4081  +  934 158   934 158   934 158   959 102   959 102   959 102   959 102
         4082  +  959 102   959 102   959 102   959 102   959 102   959 102   959 102
         4083  +  959 102   959 102   959 102   959 102   959 102   959 102   959 113
         4084  +  959 113   959 113   959 113   959 113   959 113   959 113   959 113
         4085  +  959 113   959 113   959 113   959 158   959 158   959 158   959 158
         4086  +  959 158   979 102   979 102   979 102   979 102   979 102   979 102
         4087  +  979 102   979 102   979 102   979 102   979 102   979 102   979 102
         4088  +  979 102   979 102   979 102}
         4089  +
         4090  +do_execsql_test 5.3.4.2 {
         4091  +  SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
         4092  +             rank() OVER win
         4093  +      FROM t3
         4094  +      WINDOW win AS (  ORDER BY a  GROUPS 6 PRECEDING   EXCLUDE GROUP  )
         4095  +      ORDER BY 1 , 2
         4096  +} {{} 1   {} 1   {} 1   {} 1   {} 1   {} 1   2050 7   2050 7   2050 7   2050 7
         4097  +  2050 7   2050 7   2050 7   2050 7   4359 15   4359 15   4359 15   4359 15
         4098  +  4359 15   4359 15   4359 15   4359 15   4359 15   4359 15   4359 15
         4099  +  4359 15   4359 15   7840 28   7840 28   7840 28   7840 28   7840 28
         4100  +  7840 28   9206 34   9206 34   9206 34   9206 34   9206 34   9206 34
         4101  +  9206 34   10028 74   10028 74   10028 74   10028 74   10028 74   10028 74
         4102  +  10028 74   10396 65   10396 65   10396 65   10396 65   10396 65   10396 65
         4103  +  10396 65   10396 65   10396 65   11002 81   11002 81   11002 81   11002 81
         4104  +  11002 81   11002 81   11002 81   11002 81   11002 81   11010 41   11010 41
         4105  +  11010 41   11010 41   11010 41   11441 57   11441 57   11441 57   11441 57
         4106  +  11441 57   11441 57   11441 57   11441 57   12529 46   12529 46   12529 46
         4107  +  12529 46   12529 46   12529 46   12529 46   12529 46   12529 46   12529 46
         4108  +  12529 46}
         4109  +
         4110  +do_execsql_test 5.3.5.1 {
         4111  +  SELECT max(c) OVER win,
         4112  +             min(c) OVER win
         4113  +      FROM t3
         4114  +      WINDOW win AS (  ORDER BY c  RANGE BETWEEN 6 PRECEDING AND 7 FOLLOWING   EXCLUDE GROUP  )
         4115  +      ORDER BY 1 , 2
         4116  +} {{} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}
         4117  +  {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}
         4118  +  {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}   {} {}
         4119  +  {} {}   {} {}   158 158   160 160   160 160   223 223   224 224   238 234
         4120  +  239 234   239 238   252 250   256 252   257 247   257 247   257 250
         4121  +  335 330   336 330   336 335   354 354   354 354   355 355   398 393
         4122  +  398 393   399 393   399 398   399 398   572 572   574 574   633 629
         4123  +  634 627   634 627   634 627   634 629   667 667   670 667   671 667
         4124  +  671 670   671 670   711 711   711 711   716 705   726 726   730 730
         4125  +  762 762   762 762   762 762   768 759   792 790   792 790   794 786
         4126  +  794 786   844 839   845 839   845 839   845 840   845 840   934 934
         4127  +  934 934   934 934   938 929   959 959   963 963}
         4128  +
         4129  +do_execsql_test 5.3.5.2 {
         4130  +  SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
         4131  +             rank() OVER win
         4132  +      FROM t3
         4133  +      WINDOW win AS (  ORDER BY c  RANGE BETWEEN 6 PRECEDING AND 7 FOLLOWING   EXCLUDE GROUP  )
         4134  +      ORDER BY 1 , 2
         4135  +} {{} 1   {} 2   {} 2   {} 4   {} 5   {} 6   {} 6   {} 8   {} 9   {} 10
         4136  +  {} 14   {} 15   {} 19   {} 20   {} 21   {} 23   {} 25   {} 27   {} 27
         4137  +  {} 34   {} 35   {} 36   {} 37   {} 38   {} 38   {} 40   {} 41   {} 42
         4138  +  {} 43   {} 43   {} 50   {} 56   {} 60   {} 61   {} 62   {} 64   {} 64
         4139  +  {} 66   {} 67   {} 68   {} 69   {} 70   {} 71   {} 72   {} 78   {} 78
         4140  +  {} 78   {} 81   {} 82   {} 83   {} 85   {} 85   {} 89   223 11   239 12
         4141  +  239 13   257 18   335 22   335 24   504 16   504 17   671 52   671 52
         4142  +  705 58   710 26   711 57   711 59   759 63   786 32   786 32   798 29
         4143  +  798 29   845 73   845 73   929 84   959 88   963 87   1260 46   1260 46
         4144  +  1334 51   1334 55   1584 31   1678 77   1885 48   1891 45   1891 49
         4145  +  2005 54   2523 75   2523 76}
         4146  +
         4147  +do_execsql_test 5.4.1.1 {
         4148  +  SELECT max(c) OVER win,
         4149  +             min(c) OVER win
         4150  +      FROM t3
         4151  +      WINDOW win AS (  RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING   EXCLUDE TIES  )
         4152  +      ORDER BY 1 , 2
         4153  +} {102 102   113 113   113 113   133 133   148 148   158 158   158 158
         4154  +  160 160   208 208   223 223   224 224   234 234   238 238   239 239
         4155  +  247 247   250 250   252 252   256 256   257 257   295 295   309 309
         4156  +  330 330   335 335   336 336   346 346   354 354   355 355   355 355
         4157  +  393 393   393 393   398 398   399 399   399 399   412 412   421 421
         4158  +  430 430   443 443   480 480   480 480   572 572   574 574   607 607
         4159  +  618 618   618 618   627 627   629 629   629 629   633 633   634 634
         4160  +  652 652   660 660   667 667   667 667   670 670   671 671   683 683
         4161  +  705 705   711 711   716 716   726 726   730 730   759 759   762 762
         4162  +  768 768   768 768   777 777   786 786   790 790   792 792   794 794
         4163  +  805 805   822 822   839 839   839 839   840 840   844 844   845 845
         4164  +  870 870   870 870   870 870   899 899   911 911   929 929   934 934
         4165  +  938 938   938 938   959 959   963 963   979 979}
         4166  +
         4167  +do_execsql_test 5.4.1.2 {
         4168  +  SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
         4169  +             rank() OVER win
         4170  +      FROM t3
         4171  +      WINDOW win AS (  RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING   EXCLUDE TIES  )
         4172  +      ORDER BY 1 , 2
         4173  +} {{} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1
         4174  +  {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1
         4175  +  {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1
         4176  +  {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1
         4177  +  {} 1   {} 1   {} 1   {} 1   113 1   113 1   133 1   223 1   239 1   247 1
         4178  +  257 1   295 1   309 1   335 1   355 1   355 1   393 1   393 1   399 1
         4179  +  399 1   421 1   443 1   607 1   627 1   629 1   629 1   633 1   667 1
         4180  +  667 1   671 1   683 1   705 1   711 1   759 1   777 1   805 1   839 1
         4181  +  839 1   845 1   899 1   911 1   929 1   959 1   963 1   979 1}
         4182  +
         4183  +do_execsql_test 5.4.2.1 {
         4184  +  SELECT max(c) OVER win,
         4185  +             min(c) OVER win
         4186  +      FROM t3
         4187  +      WINDOW win AS (  ORDER BY a  
         4188  +        RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING   EXCLUDE TIES  )
         4189  +      ORDER BY 1 , 2
         4190  +} {113 113   234 234   257 257   336 336   354 354   768 768   839 839
         4191  +  839 839   899 113   899 113   899 113   899 113   899 113   899 113
         4192  +  899 113   899 113   899 113   899 113   899 113   899 113   899 113
         4193  +  899 113   899 899   963 113   979 102   979 102   979 102   979 102
         4194  +  979 102   979 102   979 102   979 102   979 102   979 102   979 102
         4195  +  979 102   979 102   979 102   979 102   979 102   979 102   979 102
         4196  +  979 102   979 102   979 102   979 102   979 102   979 102   979 102
         4197  +  979 102   979 102   979 102   979 102   979 102   979 102   979 102
         4198  +  979 102   979 102   979 102   979 102   979 102   979 102   979 102
         4199  +  979 102   979 102   979 102   979 102   979 102   979 102   979 102
         4200  +  979 113   979 113   979 113   979 113   979 113   979 113   979 113
         4201  +  979 113   979 113   979 113   979 113   979 113   979 113   979 113
         4202  +  979 113   979 113   979 113   979 113   979 113}
         4203  +
         4204  +do_execsql_test 5.4.2.2 {
         4205  +  SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
         4206  +             rank() OVER win
         4207  +      FROM t3
         4208  +      WINDOW win AS (  ORDER BY a  
         4209  +        RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING   EXCLUDE TIES  )
         4210  +      ORDER BY 1 , 2
         4211  +} {{} 81   {} 81   {} 81   {} 81   113 81   257 81   839 81   839 81   899 81
         4212  +  2947 74   2947 74   2947 74   3368 74   3390 74   3618 74   3752 74
         4213  +  5287 65   5287 65   5287 65   5287 65   5420 65   5642 65   5970 65
         4214  +  6250 65   6266 65   8400 57   8400 57   8400 57   8400 57   8400 57
         4215  +  8400 57   8735 57   9329 57   9664 46   9664 46   9664 46   9664 46
         4216  +  9664 46   9664 46   9664 46   9664 46   9664 46   9959 46   10331 46
         4217  +  10626 41   10626 41   10739 41   11255 41   11403 41   12145 34   12145 34
         4218  +  12145 34   12145 34   12145 34   12990 34   13104 34   13949 28   13949 28
         4219  +  13949 28   13949 28   14556 28   14708 28   15315 15   15315 15   15315 15
         4220  +  15315 15   15315 15   15315 15   15562 15   15708 15   15708 15   15714 15
         4221  +  15948 15   16020 15   16026 15   18796 7   18796 7   18796 7   19019 7
         4222  +  19035 7   19105 7   19423 7   19707 7   21105 1   21105 1   21460 1
         4223  +  21504 1   21734 1   21772 1}
         4224  +
         4225  +do_execsql_test 5.4.3.1 {
         4226  +  SELECT max(c) OVER win,
         4227  +             min(c) OVER win
         4228  +      FROM t3
         4229  +      WINDOW win AS (  PARTITION BY coalesce(a, '') 
         4230  +        RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING   EXCLUDE TIES  )
         4231  +      ORDER BY 1 , 2
         4232  +} {102 102   113 113   113 113   133 133   148 148   158 158   158 158
         4233  +  160 160   208 208   223 223   224 224   234 234   238 238   239 239
         4234  +  247 247   250 250   252 252   256 256   257 257   295 295   309 309
         4235  +  330 330   335 335   336 336   346 346   354 354   355 355   355 355
         4236  +  393 393   393 393   398 398   399 399   399 399   412 412   421 421
         4237  +  430 430   443 443   480 480   480 480   572 572   574 574   607 607
         4238  +  618 618   618 618   627 627   629 629   629 629   633 633   634 634
         4239  +  652 652   660 660   667 667   667 667   670 670   671 671   683 683
         4240  +  705 705   711 711   716 716   726 726   730 730   759 759   762 762
         4241  +  768 768   768 768   777 777   786 786   790 790   792 792   794 794
         4242  +  805 805   822 822   839 839   839 839   840 840   844 844   845 845
         4243  +  870 870   870 870   870 870   899 899   911 911   929 929   934 934
         4244  +  938 938   938 938   959 959   963 963   979 979}
         4245  +
         4246  +do_execsql_test 5.4.3.2 {
         4247  +  SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
         4248  +             rank() OVER win
         4249  +      FROM t3
         4250  +      WINDOW win AS (  PARTITION BY coalesce(a, '') 
         4251  +        RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING   EXCLUDE TIES  )
         4252  +      ORDER BY 1 , 2
         4253  +} {{} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1
         4254  +  {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1
         4255  +  {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1
         4256  +  {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1   {} 1
         4257  +  {} 1   {} 1   {} 1   {} 1   113 1   113 1   133 1   223 1   239 1   247 1
         4258  +  257 1   295 1   309 1   335 1   355 1   355 1   393 1   393 1   399 1
         4259  +  399 1   421 1   443 1   607 1   627 1   629 1   629 1   633 1   667 1
         4260  +  667 1   671 1   683 1   705 1   711 1   759 1   777 1   805 1   839 1
         4261  +  839 1   845 1   899 1   911 1   929 1   959 1   963 1   979 1}
         4262  +
         4263  +do_execsql_test 5.4.4.1 {
         4264  +  SELECT max(c) OVER win,
         4265  +             min(c) OVER win
         4266  +      FROM t3
         4267  +      WINDOW win AS (  ORDER BY a  GROUPS 6 PRECEDING   EXCLUDE TIES  )
         4268  +      ORDER BY 1 , 2
         4269  +} {158 158   355 355   399 399   629 629   667 667   870 158   870 158
         4270  +  870 158   870 158   870 158   870 158   870 870   911 158   934 158
         4271  +  934 158   934 158   934 158   934 158   934 158   934 158   934 158
         4272  +  934 158   934 158   934 158   934 158   934 158   934 158   934 158
         4273  +  934 158   934 158   934 158   934 158   934 158   934 158   934 158
         4274  +  934 158   934 158   934 158   934 158   959 102   959 102   959 102
         4275  +  959 102   959 102   959 102   959 102   959 102   959 102   959 102
         4276  +  959 102   959 102   959 102   959 102   959 102   959 102   959 113
         4277  +  959 113   959 113   959 113   959 113   959 113   959 113   959 113
         4278  +  959 113   959 113   959 113   959 158   959 158   959 158   959 158
         4279  +  959 158   963 102   979 102   979 102   979 102   979 102   979 102
         4280  +  979 102   979 102   979 102   979 102   979 102   979 102   979 102
         4281  +  979 102   979 102   979 102   979 102   979 102}
         4282  +
         4283  +do_execsql_test 5.4.4.2 {
         4284  +  SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
         4285  +             rank() OVER win
         4286  +      FROM t3
         4287  +      WINDOW win AS (  ORDER BY a  GROUPS 6 PRECEDING   EXCLUDE TIES  )
         4288  +      ORDER BY 1 , 2
         4289  +} {{} 1   {} 1   355 1   399 1   629 1   667 1   2050 7   2050 7   2050 7
         4290  +  2273 7   2289 7   2359 7   2677 7   2961 7   4359 15   4359 15   4359 15
         4291  +  4359 15   4359 15   4359 15   4606 15   4752 15   4752 15   4758 15
         4292  +  4992 15   5064 15   5070 15   7840 28   7840 28   7840 28   7840 28
         4293  +  8447 28   8599 28   9206 34   9206 34   9206 34   9206 34   9206 34
         4294  +  10028 74   10028 74   10028 74   10051 34   10165 34   10396 65   10396 65
         4295  +  10396 65   10396 65   10449 74   10471 74   10529 65   10699 74   10751 65
         4296  +  10833 74   11002 81   11002 81   11002 81   11002 81   11010 41   11010 41
         4297  +  11079 65   11115 81   11123 41   11259 81   11359 65   11375 65   11441 57
         4298  +  11441 57   11441 57   11441 57   11441 57   11441 57   11639 41   11776 57
         4299  +  11787 41   11841 81   11841 81   11901 81   12370 57   12529 46   12529 46
         4300  +  12529 46   12529 46   12529 46   12529 46   12529 46   12529 46   12529 46
         4301  +  12824 46   13196 46}
         4302  +
         4303  +do_execsql_test 5.4.5.1 {
         4304  +  SELECT max(c) OVER win,
         4305  +             min(c) OVER win
         4306  +      FROM t3
         4307  +      WINDOW win AS (  ORDER BY c  RANGE BETWEEN 6 PRECEDING AND 7 FOLLOWING   EXCLUDE TIES  )
         4308  +      ORDER BY 1 , 2
         4309  +} {102 102   113 113   113 113   133 133   148 148   160 158   160 158
         4310  +  160 158   208 208   224 223   224 223   239 234   239 234   239 234
         4311  +  252 247   257 247   257 247   257 250   257 252   295 295   309 309
         4312  +  336 330   336 330   336 330   346 346   355 354   355 354   355 354
         4313  +  399 393   399 393   399 393   399 393   399 393   412 412   421 421
         4314  +  430 430   443 443   480 480   480 480   574 572   574 572   607 607
         4315  +  618 618   618 618   634 627   634 627   634 627   634 627   634 629
         4316  +  652 652   667 660   671 667   671 667   671 667   671 667   683 683
         4317  +  711 705   716 705   716 711   730 726   730 726   762 759   768 759
         4318  +  768 762   768 762   777 777   792 786   794 786   794 786   794 790
         4319  +  805 805   822 822   845 839   845 839   845 839   845 839   845 839
         4320  +  870 870   870 870   870 870   899 899   911 911   934 929   938 929
         4321  +  938 934   938 934   963 959   963 959   979 979}
         4322  +
         4323  +do_execsql_test 5.4.5.2 {
         4324  +  SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
         4325  +             rank() OVER win
         4326  +      FROM t3
         4327  +      WINDOW win AS (  ORDER BY c  RANGE BETWEEN 6 PRECEDING AND 7 FOLLOWING   EXCLUDE TIES  )
         4328  +      ORDER BY 1 , 2
         4329  +} {{} 1   {} 5   {} 6   {} 6   {} 8   {} 9   {} 25   {} 34   {} 36   {} 38
         4330  +  {} 38   {} 40   {} 41   {} 43   {} 43   {} 50   {} 60   {} 61   {} 64
         4331  +  {} 64   {} 67   {} 68   {} 69   {} 70   {} 72   {} 78   {} 78   {} 78
         4332  +  {} 85   {} 85   113 2   113 2   133 4   223 10   223 11   239 12   239 13
         4333  +  239 14   247 15   257 18   257 19   295 20   309 21   335 22   335 23
         4334  +  335 24   355 27   355 27   421 35   443 37   504 16   504 17   607 42
         4335  +  683 56   710 26   711 59   759 62   759 63   777 66   805 71   899 81
         4336  +  911 82   929 83   929 84   979 89   1185 32   1185 32   1191 29   1191 29
         4337  +  1334 51   1338 52   1338 52   1416 57   1416 58   1584 31   1684 73
         4338  +  1684 73   1889 46   1889 46   1891 49   1922 87   1922 88   2005 54
         4339  +  2005 55   2518 45   2518 48   2523 75   2523 76   2523 77}
  3551   4340   
  3552   4341   finish_test