SQLite

Check-in [e7bced731a]
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
Timelines: family | ancestors | descendants | both | window-functions
Files: files | file ages | folders
SHA3-256: e7bced731aa071c95bc398cdecd53c939841bf0c52fbcd06e47ba68f8c5cc35a
User & Date: dan 2019-03-11 11:12:34.831
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: 6bd1a07949 user: dan tags: window-functions)
11:12
Fix problems with "RANGE ... ORDER BY <expr> DESC" window frames. (check-in: e7bced731a user: dan tags: window-functions)
2019-03-09
20:49
Add support for RANGE window frames. Some cases still do not work. (check-in: ffc32b246d user: dan tags: window-functions)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/window.c.
︙︙
1555
1556
1557
1558
1559
1560
1561













1562
1563
1564
1565
1566
1567
1568
1569
1570
1571
1572
1573
1574
1575
1576
1577
1578
1579

  int csr2,
  int lbl
){
  Parse *pParse = p->pParse;
  Vdbe *v = sqlite3GetVdbe(pParse);
  int reg1 = sqlite3GetTempReg(pParse);
  int reg2 = sqlite3GetTempReg(pParse);













  windowReadPeerValues(p, csr1, reg1);
  windowReadPeerValues(p, csr2, reg2);
  sqlite3VdbeAddOp3(v, OP_Add, reg1, regVal, reg1);
  sqlite3VdbeAddOp3(v, op, reg2, lbl, reg1);
  sqlite3VdbeAddOp2(v, OP_Rowid, csr1, reg1);
  sqlite3VdbeAddOp2(v, OP_Rowid, csr2, reg2);
  sqlite3VdbeAddOp3(v, OP_Gt, reg2, lbl, reg1);
  sqlite3ReleaseTempReg(pParse, reg1);
  sqlite3ReleaseTempReg(pParse, reg2);

  assert( op==OP_Ge || op==OP_Gt || op==OP_Lt || op==OP_Le );
}

static int windowCodeOp(
 WindowCodeArg *p,
 int op,
 int regCountdown,
 int jumpOnEof








>
>
>
>
>
>
>
>
>
>
>
>
>


|






<
<








1555
1556
1557
1558
1559
1560
1561
1562
1563
1564
1565
1566
1567
1568
1569
1570
1571
1572
1573
1574
1575
1576
1577
1578
1579
1580
1581
1582
1583


1584
1585
1586
1587
1588
1589
1590

  int csr2,
  int lbl
){
  Parse *pParse = p->pParse;
  Vdbe *v = sqlite3GetVdbe(pParse);
  int reg1 = sqlite3GetTempReg(pParse);
  int reg2 = sqlite3GetTempReg(pParse);
  int arith = OP_Add;

  assert( op==OP_Ge || op==OP_Gt || op==OP_Le );
  assert( p->pMWin->pOrderBy && p->pMWin->pOrderBy->nExpr==1 );
  if( p->pMWin->pOrderBy->a[0].sortOrder ){
    switch( op ){
      case OP_Ge: op = OP_Le; break;
      case OP_Gt: op = OP_Lt; break;
      default: assert( op==OP_Le ); op = OP_Ge; break;
    }
    arith = OP_Subtract;
  }

  windowReadPeerValues(p, csr1, reg1);
  windowReadPeerValues(p, csr2, reg2);
  sqlite3VdbeAddOp3(v, arith, regVal, reg1, reg1);
  sqlite3VdbeAddOp3(v, op, reg2, lbl, reg1);
  sqlite3VdbeAddOp2(v, OP_Rowid, csr1, reg1);
  sqlite3VdbeAddOp2(v, OP_Rowid, csr2, reg2);
  sqlite3VdbeAddOp3(v, OP_Gt, reg2, lbl, reg1);
  sqlite3ReleaseTempReg(pParse, reg1);
  sqlite3ReleaseTempReg(pParse, reg2);


}

static int windowCodeOp(
 WindowCodeArg *p,
 int op,
 int regCountdown,
 int jumpOnEof
︙︙
Changes to test/window8.tcl.
︙︙
91
92
93
94
95
96
97
98

99
100
101
102
103
104



105
106
107
108
109
110
111


==========

execsql_test 2.0 {
  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1(a INTEGER, b INTEGER);
  INSERT INTO t1 VALUES
      (13, 26), (15, 30);

}

foreach {tn frame} {
  1 { ORDER BY a RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING }
  2 { ORDER BY a RANGE BETWEEN 10 PRECEDING AND 5 PRECEDING }
  3 { ORDER BY a RANGE BETWEEN 2 FOLLOWING AND 3 FOLLOWING }



} {
  execsql_test 2.$tn "SELECT a, sum(b) OVER win FROM t1 WINDOW win AS ($frame)"
}


finish_test









|
>






>
>
>








91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115


==========

execsql_test 2.0 {
  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1(a INTEGER, b INTEGER);
  INSERT INTO t1 VALUES
      (5, 10), (10, 20), (13, 26), (13, 26), 
      (15, 30), (20, 40), (22,80), (30, 90);
}

foreach {tn frame} {
  1 { ORDER BY a RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING }
  2 { ORDER BY a RANGE BETWEEN 10 PRECEDING AND 5 PRECEDING }
  3 { ORDER BY a RANGE BETWEEN 2 FOLLOWING AND 3 FOLLOWING }
  4 { ORDER BY a DESC RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING }
  5 { ORDER BY a DESC RANGE BETWEEN 10 PRECEDING AND 5 PRECEDING }
  6 { ORDER BY a DESC RANGE BETWEEN 2 FOLLOWING AND 3 FOLLOWING }
} {
  execsql_test 2.$tn "SELECT a, sum(b) OVER win FROM t1 WINDOW win AS ($frame)"
}


finish_test
︙︙
Changes to test/window8.test.
︙︙
435
436
437
438
439
440
441
442

443
444
445
446
447
448
449
450
451
452
453
454

455











456
457


#==========================================================================

do_execsql_test 2.0 {
  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1(a INTEGER, b INTEGER);
  INSERT INTO t1 VALUES
      (13, 26), (15, 30);

} {}

do_execsql_test 2.1 {
  SELECT a, sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING )
} {13 56   15 56}

do_execsql_test 2.2 {
  SELECT a, sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a RANGE BETWEEN 10 PRECEDING AND 5 PRECEDING )
} {13 {}   15 {}}

do_execsql_test 2.3 {
  SELECT a, sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a RANGE BETWEEN 2 FOLLOWING AND 3 FOLLOWING )

} {13 30   15 {}}












finish_test








|
>




|



|



>
|
>
>
>
>
>
>
>
>
>
>
>



435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470


#==========================================================================

do_execsql_test 2.0 {
  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1(a INTEGER, b INTEGER);
  INSERT INTO t1 VALUES
      (5, 10), (10, 20), (13, 26), (13, 26), 
      (15, 30), (20, 40), (22,80), (30, 90);
} {}

do_execsql_test 2.1 {
  SELECT a, sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING )
} {5 30   10 112   13 102   13 102   15 142   20 150   22 120   30 90}

do_execsql_test 2.2 {
  SELECT a, sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a RANGE BETWEEN 10 PRECEDING AND 5 PRECEDING )
} {5 {}   10 10   13 10   13 10   15 30   20 102   22 82   30 120}

do_execsql_test 2.3 {
  SELECT a, sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a RANGE BETWEEN 2 FOLLOWING AND 3 FOLLOWING )
} {5 {}   10 52   13 30   13 30   15 {}   20 80   22 {}   30 {}}

do_execsql_test 2.4 {
  SELECT a, sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a DESC RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING )
} {30 90   22 120   20 150   15 142   13 102   13 102   10 112   5 30}

do_execsql_test 2.5 {
  SELECT a, sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a DESC RANGE BETWEEN 10 PRECEDING AND 5 PRECEDING )
} {30 {}   22 90   20 90   15 120   13 120   13 120   10 70   5 102}

do_execsql_test 2.6 {
  SELECT a, sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a DESC RANGE BETWEEN 2 FOLLOWING AND 3 FOLLOWING )
} {30 {}   22 40   20 {}   15 52   13 20   13 20   10 {}   5 {}}

finish_test