/ Check-in [e7bced73]
Login

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

Overview
Comment:Fix problems with "RANGE ... ORDER BY <expr> DESC" window frames.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | window-functions
Files: files | file ages | folders
SHA3-256:e7bced731aa071c95bc398cdecd53c939841bf0c52fbcd06e47ba68f8c5cc35a
User & Date: dan 2019-03-11 11:12:34
Wiki:window-functions
Context
2019-03-11
18:17
Simplify the windows frame code some. Add a comment explaining some of the VM code generated by sqlite3WindowCodeStep(). check-in: 6bd1a079 user: dan tags: window-functions
11:12
Fix problems with "RANGE ... ORDER BY <expr> DESC" window frames. check-in: e7bced73 user: dan tags: window-functions
2019-03-09
20:49
Add support for RANGE window frames. Some cases still do not work. check-in: ffc32b24 user: dan tags: window-functions
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/window.c.

  1555   1555     int csr2,
  1556   1556     int lbl
  1557   1557   ){
  1558   1558     Parse *pParse = p->pParse;
  1559   1559     Vdbe *v = sqlite3GetVdbe(pParse);
  1560   1560     int reg1 = sqlite3GetTempReg(pParse);
  1561   1561     int reg2 = sqlite3GetTempReg(pParse);
         1562  +  int arith = OP_Add;
         1563  +
         1564  +  assert( op==OP_Ge || op==OP_Gt || op==OP_Le );
         1565  +  assert( p->pMWin->pOrderBy && p->pMWin->pOrderBy->nExpr==1 );
         1566  +  if( p->pMWin->pOrderBy->a[0].sortOrder ){
         1567  +    switch( op ){
         1568  +      case OP_Ge: op = OP_Le; break;
         1569  +      case OP_Gt: op = OP_Lt; break;
         1570  +      default: assert( op==OP_Le ); op = OP_Ge; break;
         1571  +    }
         1572  +    arith = OP_Subtract;
         1573  +  }
         1574  +
  1562   1575     windowReadPeerValues(p, csr1, reg1);
  1563   1576     windowReadPeerValues(p, csr2, reg2);
  1564         -  sqlite3VdbeAddOp3(v, OP_Add, reg1, regVal, reg1);
         1577  +  sqlite3VdbeAddOp3(v, arith, regVal, reg1, reg1);
  1565   1578     sqlite3VdbeAddOp3(v, op, reg2, lbl, reg1);
  1566   1579     sqlite3VdbeAddOp2(v, OP_Rowid, csr1, reg1);
  1567   1580     sqlite3VdbeAddOp2(v, OP_Rowid, csr2, reg2);
  1568   1581     sqlite3VdbeAddOp3(v, OP_Gt, reg2, lbl, reg1);
  1569   1582     sqlite3ReleaseTempReg(pParse, reg1);
  1570   1583     sqlite3ReleaseTempReg(pParse, reg2);
  1571         -
  1572         -  assert( op==OP_Ge || op==OP_Gt || op==OP_Lt || op==OP_Le );
  1573   1584   }
  1574   1585   
  1575   1586   static int windowCodeOp(
  1576   1587    WindowCodeArg *p,
  1577   1588    int op,
  1578   1589    int regCountdown,
  1579   1590    int jumpOnEof

Changes to test/window8.tcl.

    91     91   
    92     92   ==========
    93     93   
    94     94   execsql_test 2.0 {
    95     95     DROP TABLE IF EXISTS t1;
    96     96     CREATE TABLE t1(a INTEGER, b INTEGER);
    97     97     INSERT INTO t1 VALUES
    98         -      (13, 26), (15, 30);
           98  +      (5, 10), (10, 20), (13, 26), (13, 26), 
           99  +      (15, 30), (20, 40), (22,80), (30, 90);
    99    100   }
   100    101   
   101    102   foreach {tn frame} {
   102    103     1 { ORDER BY a RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING }
   103    104     2 { ORDER BY a RANGE BETWEEN 10 PRECEDING AND 5 PRECEDING }
   104    105     3 { ORDER BY a RANGE BETWEEN 2 FOLLOWING AND 3 FOLLOWING }
          106  +  4 { ORDER BY a DESC RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING }
          107  +  5 { ORDER BY a DESC RANGE BETWEEN 10 PRECEDING AND 5 PRECEDING }
          108  +  6 { ORDER BY a DESC RANGE BETWEEN 2 FOLLOWING AND 3 FOLLOWING }
   105    109   } {
   106    110     execsql_test 2.$tn "SELECT a, sum(b) OVER win FROM t1 WINDOW win AS ($frame)"
   107    111   }
   108    112   
   109    113   
   110    114   finish_test
   111    115   
   112    116   

Changes to test/window8.test.

   435    435   
   436    436   #==========================================================================
   437    437   
   438    438   do_execsql_test 2.0 {
   439    439     DROP TABLE IF EXISTS t1;
   440    440     CREATE TABLE t1(a INTEGER, b INTEGER);
   441    441     INSERT INTO t1 VALUES
   442         -      (13, 26), (15, 30);
          442  +      (5, 10), (10, 20), (13, 26), (13, 26), 
          443  +      (15, 30), (20, 40), (22,80), (30, 90);
   443    444   } {}
   444    445   
   445    446   do_execsql_test 2.1 {
   446    447     SELECT a, sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING )
   447         -} {13 56   15 56}
          448  +} {5 30   10 112   13 102   13 102   15 142   20 150   22 120   30 90}
   448    449   
   449    450   do_execsql_test 2.2 {
   450    451     SELECT a, sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a RANGE BETWEEN 10 PRECEDING AND 5 PRECEDING )
   451         -} {13 {}   15 {}}
          452  +} {5 {}   10 10   13 10   13 10   15 30   20 102   22 82   30 120}
   452    453   
   453    454   do_execsql_test 2.3 {
   454    455     SELECT a, sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a RANGE BETWEEN 2 FOLLOWING AND 3 FOLLOWING )
   455         -} {13 30   15 {}}
          456  +} {5 {}   10 52   13 30   13 30   15 {}   20 80   22 {}   30 {}}
          457  +
          458  +do_execsql_test 2.4 {
          459  +  SELECT a, sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a DESC RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING )
          460  +} {30 90   22 120   20 150   15 142   13 102   13 102   10 112   5 30}
          461  +
          462  +do_execsql_test 2.5 {
          463  +  SELECT a, sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a DESC RANGE BETWEEN 10 PRECEDING AND 5 PRECEDING )
          464  +} {30 {}   22 90   20 90   15 120   13 120   13 120   10 70   5 102}
          465  +
          466  +do_execsql_test 2.6 {
          467  +  SELECT a, sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a DESC RANGE BETWEEN 2 FOLLOWING AND 3 FOLLOWING )
          468  +} {30 {}   22 40   20 {}   15 52   13 20   13 20   10 {}   5 {}}
   456    469   
   457    470   finish_test