SQLite

Check-in [25ff7091cb]
Login

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

Overview
Comment:Allow real values to be used in PRECEDING and FOLLOWING expressions for RANGE window frames.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | window-functions
Files: files | file ages | folders
SHA3-256: 25ff7091cb12c63b1864ce68a9151f8432af5804b5ae905a2175761ab4b9fdd8
User & Date: dan 2019-03-12 18:28:51.117
Context
2019-03-13
08:28
Minor optimization in sqlite3WindowCodeStep(). (check-in: b1322ffb6e user: dan tags: window-functions)
2019-03-12
18:28
Allow real values to be used in PRECEDING and FOLLOWING expressions for RANGE window frames. (check-in: 25ff7091cb user: dan tags: window-functions)
15:21
Expand on header comment for sqlite3WindowCodeStep(). Further simplify the implementation of the same. (check-in: 5129bcc996 user: dan tags: window-functions)
Changes
Side-by-Side Diff Ignore Whitespace Patch
Changes to src/vdbe.c.
︙
1719
1720
1721
1722
1723
1724
1725
1726

1727
1728
1729
1730



1731




1732
1733


1734
1735

1736
1737
1738


1739
1740
1741
1742
1743
1744
1745
1746
1747

1748
1749
1750
1751
1752
1753
1754

1719
1720
1721
1722
1723
1724
1725

1726
1727



1728
1729
1730
1731
1732
1733
1734
1735
1736
1737
1738
1739
1740

1741
1742


1743
1744
1745
1746
1747
1748
1749
1750
1751
1752

1753
1754
1755
1756
1757
1758
1759
1760








-
+

-
-
-
+
+
+

+
+
+
+


+
+

-
+

-
-
+
+








-
+








  pIn1 = &aMem[pOp->p1];
  memAboutToChange(p, pIn1);
  sqlite3VdbeMemIntegerify(pIn1);
  pIn1->u.i += pOp->p2;
  break;
}

/* Opcode: MustBeInt P1 P2 * * *
/* Opcode: MustBeInt P1 P2 * * P5
** 
** Force the value in register P1 to be an integer.  If the value
** in P1 is not an integer and cannot be converted into an integer
** without data loss, then jump immediately to P2, or if P2==0
** If P5 is 0, force the value in register P1 to be an integer. If 
** the value in P1 is not an integer and cannot be converted into an 
** integer without data loss, then jump immediately to P2, or if P2==0
** raise an SQLITE_MISMATCH exception.
**
** Or, if P5 is non-zero, then force the register in P1 to be a number
** (real or integer). Jump to P2 if this cannot be accomplished without
** data loss. P2 must be non-zero in this case.
*/
case OP_MustBeInt: {            /* jump, in1 */
  u8 f;
  f = (pOp->p5 ? (MEM_Int|MEM_Real) : MEM_Int);
  pIn1 = &aMem[pOp->p1];
  if( (pIn1->flags & MEM_Int)==0 ){
  if( (pIn1->flags & f)==0 ){
    applyAffinity(pIn1, SQLITE_AFF_NUMERIC, encoding);
    VdbeBranchTaken((pIn1->flags&MEM_Int)==0, 2);
    if( (pIn1->flags & MEM_Int)==0 ){
    VdbeBranchTaken((pIn1->flags&f)==0, 2);
    if( (pIn1->flags & f)==0 ){
      if( pOp->p2==0 ){
        rc = SQLITE_MISMATCH;
        goto abort_due_to_error;
      }else{
        goto jump_to_p2;
      }
    }
  }
  MemSetTypeFlag(pIn1, MEM_Int);
  if( f==MEM_Int ) MemSetTypeFlag(pIn1, MEM_Int);
  break;
}

#ifndef SQLITE_OMIT_FLOATING_POINT
/* Opcode: RealAffinity P1 * * * *
**
** If register P1 holds an integer convert it to a real value.
︙
Changes to src/window.c.
︙
1164
1165
1166
1167
1168
1169
1170






1171
1172
1173
1174
1175
1176
1177
1178

1179
1180
1181
1182



1183
1184

1185
1186
1187

1188
1189

1190
1191
1192
1193
1194
1195
1196

1164
1165
1166
1167
1168
1169
1170
1171
1172
1173
1174
1175
1176
1177
1178
1179
1180
1181
1182
1183

1184
1185
1186
1187

1188
1189
1190
1191

1192
1193
1194

1195
1196
1197
1198
1199
1200
1201
1202
1203
1204
1205








+
+
+
+
+
+







-
+



-
+
+
+

-
+


-
+


+








      assert( pMWin->iEphCsr );
      pWin->csrApp = pParse->nTab++;
      sqlite3VdbeAddOp2(v, OP_OpenDup, pWin->csrApp, pMWin->iEphCsr);
    }
  }
}

#define WINDOW_STARTING_INT  0
#define WINDOW_ENDING_INT    1
#define WINDOW_NTH_VALUE_INT 2
#define WINDOW_STARTING_NUM  3
#define WINDOW_ENDING_NUM    4

/*
** A "PRECEDING <expr>" (eCond==0) or "FOLLOWING <expr>" (eCond==1) or the
** value of the second argument to nth_value() (eCond==2) has just been
** evaluated and the result left in register reg. This function generates VM
** code to check that the value is a non-negative integer and throws an
** exception if it is not.
*/
static void windowCheckIntValue(Parse *pParse, int reg, int eCond){
static void windowCheckValue(Parse *pParse, int reg, int eCond){
  static const char *azErr[] = {
    "frame starting offset must be a non-negative integer",
    "frame ending offset must be a non-negative integer",
    "second argument to nth_value must be a positive integer"
    "second argument to nth_value must be a positive integer",
    "frame starting offset must be a non-negative number",
    "frame ending offset must be a non-negative number",
  };
  static int aOp[] = { OP_Ge, OP_Ge, OP_Gt };
  static int aOp[] = { OP_Ge, OP_Ge, OP_Gt, OP_Ge, OP_Ge };
  Vdbe *v = sqlite3GetVdbe(pParse);
  int regZero = sqlite3GetTempReg(pParse);
  assert( eCond==0 || eCond==1 || eCond==2 );
  assert( eCond>=0 && eCond<ArraySize(azErr) );
  sqlite3VdbeAddOp2(v, OP_Integer, 0, regZero);
  sqlite3VdbeAddOp2(v, OP_MustBeInt, reg, sqlite3VdbeCurrentAddr(v)+2);
  if( eCond>=WINDOW_STARTING_NUM ) sqlite3VdbeChangeP5(v, 1);
  VdbeCoverageIf(v, eCond==0);
  VdbeCoverageIf(v, eCond==1);
  VdbeCoverageIf(v, eCond==2);
  sqlite3VdbeAddOp3(v, aOp[eCond], regZero, sqlite3VdbeCurrentAddr(v)+2, reg);
  VdbeCoverageNeverNullIf(v, eCond==0);
  VdbeCoverageNeverNullIf(v, eCond==1);
  VdbeCoverageNeverNullIf(v, eCond==2);
︙
1379
1380
1381
1382
1383
1384
1385
1386

1387
1388
1389
1390
1391
1392
1393

1388
1389
1390
1391
1392
1393
1394

1395
1396
1397
1398
1399
1400
1401
1402








-
+








      int csr = pWin->csrApp;
      int lbl = sqlite3VdbeMakeLabel(pParse);
      int tmpReg = sqlite3GetTempReg(pParse);
      sqlite3VdbeAddOp2(v, OP_Null, 0, pWin->regResult);

      if( pFunc->zName==nth_valueName ){
        sqlite3VdbeAddOp3(v, OP_Column, pMWin->iEphCsr, pWin->iArgCol+1,tmpReg);
        windowCheckIntValue(pParse, tmpReg, 2);
        windowCheckValue(pParse, tmpReg, 2);
      }else{
        sqlite3VdbeAddOp2(v, OP_Integer, 1, tmpReg);
      }
      sqlite3VdbeAddOp3(v, OP_Add, tmpReg, pWin->regApp, tmpReg);
      sqlite3VdbeAddOp3(v, OP_Gt, pWin->regApp+1, lbl, tmpReg);
      VdbeCoverageNeverNull(v);
      sqlite3VdbeAddOp3(v, OP_SeekRowid, csr, 0, tmpReg);
︙
2210
2211
2212
2213
2214
2215
2216
2217

2218
2219
2220
2221

2222
2223
2224
2225
2226
2227
2228

2219
2220
2221
2222
2223
2224
2225

2226
2227
2228
2229

2230
2231
2232
2233
2234
2235
2236
2237








-
+



-
+








  addrIfNot = sqlite3VdbeAddOp1(v, OP_IfNot, pMWin->regFirst);

  /* This block is run for the first row of each partition */
  s.regArg = windowInitAccum(pParse, pMWin);

  if( regStart ){
    sqlite3ExprCode(pParse, pMWin->pStart, regStart);
    windowCheckIntValue(pParse, regStart, 0);
    windowCheckValue(pParse, regStart, 0 + (pMWin->eType==TK_RANGE ? 3 : 0));
  }
  if( regEnd ){
    sqlite3ExprCode(pParse, pMWin->pEnd, regEnd);
    windowCheckIntValue(pParse, regEnd, 1);
    windowCheckValue(pParse, regEnd, 1 + (pMWin->eType==TK_RANGE ? 3 : 0));
  }

  if( pMWin->eStart==pMWin->eEnd && regStart && regEnd ){
    int op = ((pMWin->eStart==TK_FOLLOWING) ? OP_Ge : OP_Le);
    int addrGe = sqlite3VdbeAddOp3(v, op, regStart, 0, regEnd);
    windowAggFinal(pParse, pMWin, 0);
    sqlite3VdbeAddOp2(v, OP_Rewind, s.current.csr, 1);
︙
Changes to test/window8.tcl.
︙
89
90
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
116

89
90
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
116
117

118
119
120
121
122
123
124
125








-
+












+
+
+
+
+
+
+

+
-
+
+







  "
}

==========

execsql_test 2.0 {
  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1(a INTEGER, b INTEGER);
  CREATE TABLE t1(a REAL, 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 }

  7  { ORDER BY a RANGE BETWEEN 5.1 PRECEDING AND 5.3 FOLLOWING }
  8  { ORDER BY a RANGE BETWEEN 10.2 PRECEDING AND 5.4 PRECEDING }
  9  { ORDER BY a RANGE BETWEEN 2.6 FOLLOWING AND 3.5 FOLLOWING }
  10 { ORDER BY a DESC RANGE BETWEEN 5.7 PRECEDING AND 5.8 FOLLOWING }
  11 { ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND 5.9 PRECEDING }
  12 { ORDER BY a DESC RANGE BETWEEN 2.1 FOLLOWING AND UNBOUNDED FOLLOWING }
} {
  execsql_test 2.$tn "
  execsql_test 2.$tn "SELECT a, sum(b) OVER win FROM t1 WINDOW win AS ($frame)"
    SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ($frame)
  "
}


finish_test
Changes to test/window8.test.
︙
433
434
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

433
434
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
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494








-
+






-
+



-
+



-
+



-
+



-
+



-
+


+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+


  SELECT a, b, min(c) OVER (ORDER BY a,b  GROUPS BETWEEN 1 FOLLOWING         AND UNBOUNDED FOLLOWING ) FROM t3 ORDER BY 1, 2, 3;
} {AA aa 102   AA aa 102   AA aa 102   AA aa 102   AA bb 102   AA bb 102   AA bb 102   AA bb 102   BB aa 102   BB aa 102   BB aa 102   BB aa 102   BB aa 102   BB aa 102   BB bb 102   BB bb 102   BB bb 102   BB bb 102   BB bb 102   BB bb 102   CC aa 102   CC aa 102   CC aa 102   CC aa 102   CC bb 102   CC bb 102   DD aa 102   DD aa 102   DD aa 102   DD bb 102   DD bb 102   DD bb 102   DD bb 102   EE aa 102   EE aa 102   EE bb 102   EE bb 102   EE bb 102   FF aa 102   FF aa 102   FF aa 102   FF aa 102   FF bb 113   FF bb 113   FF bb 113   FF bb 113   FF bb 113   FF bb 113   GG aa 113   GG aa 113   GG aa 113   GG aa 113   GG bb 113   GG bb 113   GG bb 113   GG bb 113   HH aa 113   HH aa 113   HH aa 113   HH bb 113   HH bb 113   HH bb 113   HH bb 113   HH bb 113   HH bb 113   II aa 113   II aa 113   II bb 113   II bb 113   II bb 113   II bb 113   II bb 113   JJ aa 257   JJ aa 257   JJ aa 257   JJ aa 257   JJ bb {}   JJ bb {}   JJ bb {}   JJ bb {}}

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

do_execsql_test 2.0 {
  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1(a INTEGER, b INTEGER);
  CREATE TABLE t1(a REAL, 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 )
  SELECT CAST(a AS INTEGER), 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 )
  SELECT CAST(a AS INTEGER), 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 )
  SELECT CAST(a AS INTEGER), 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 )
  SELECT CAST(a AS INTEGER), 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 )
  SELECT CAST(a AS INTEGER), 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 )
  SELECT CAST(a AS INTEGER), 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 {}}

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

do_execsql_test 2.8 {
  SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a RANGE BETWEEN 10.2 PRECEDING AND 5.4 PRECEDING )
} {5 {}   10 {}   13 10   13 10   15 10   20 72   22 82   30 120}

do_execsql_test 2.9 {
  SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a RANGE BETWEEN 2.6 FOLLOWING AND 3.5 FOLLOWING )
} {5 {}   10 52   13 {}   13 {}   15 {}   20 {}   22 {}   30 {}}

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

do_execsql_test 2.11 {
  SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND 5.9 PRECEDING )
} {30 {}   22 90   20 90   15 170   13 210   13 210   10 210   5 292}

do_execsql_test 2.12 {
  SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a DESC RANGE BETWEEN 2.1 FOLLOWING AND UNBOUNDED FOLLOWING )
} {30 232   22 112   20 112   15 30   13 30   13 30   10 10   5 {}}

finish_test