/ Check-in [723c84be]
Login

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

Overview
Comment:Fixes for RANGE windows and NULL values.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | window-functions
Files: files | file ages | folders
SHA3-256: 723c84be3ec5ae941b7abd2442cdb76ca3bd76a5ce2d830b0e648c6e1424885a
User & Date: dan 2019-03-18 16:51:24
Wiki:window-functions
Context
2019-03-18
18:55
Always evaluate window functions using the alternative path usually only used by EXCLUDE frames if the SQLITE_QueryFlattener test flag is set. check-in: 2879a691 user: dan tags: window-functions
16:51
Fixes for RANGE windows and NULL values. check-in: 723c84be user: dan tags: window-functions
2019-03-16
20:29
Fix problems with RANGE windows and string, blob and NULL values. check-in: cebe09e1 user: dan tags: window-functions
Changes
Hide Diffs Unified Diffs Show Whitespace Changes Patch

Changes to src/vdbe.c.

1920
1921
1922
1923
1924
1925
1926
1927
1928
1929
1930
1931
1932
1933
1934
1935
1936
1937
1938
1939
1940
1941
1942
1943
  if( (flags1 | flags3)&MEM_Null ){
    /* One or both operands are NULL */
    if( pOp->p5 & SQLITE_NULLEQ ){
      /* If SQLITE_NULLEQ is set (which will only happen if the operator is
      ** OP_Eq or OP_Ne) then take the jump or not depending on whether
      ** or not both operands are null.
      */
      assert( pOp->opcode==OP_Eq || pOp->opcode==OP_Ne );
      assert( (flags1 & MEM_Cleared)==0 );
      assert( (pOp->p5 & SQLITE_JUMPIFNULL)==0 || CORRUPT_DB );
      testcase( (pOp->p5 & SQLITE_JUMPIFNULL)!=0 );
      if( (flags1&flags3&MEM_Null)!=0
       && (flags3&MEM_Cleared)==0
      ){
        res = 0;  /* Operands are equal */
      }else{
        res = 1;  /* Operands are not equal */
      }
    }else{
      /* SQLITE_NULLEQ is clear and at least one operand is NULL,
      ** then the result is always NULL.
      ** The jump is taken if the SQLITE_JUMPIFNULL bit is set.
      */
      if( pOp->p5 & SQLITE_STOREP2 ){







<








|







1920
1921
1922
1923
1924
1925
1926

1927
1928
1929
1930
1931
1932
1933
1934
1935
1936
1937
1938
1939
1940
1941
1942
  if( (flags1 | flags3)&MEM_Null ){
    /* One or both operands are NULL */
    if( pOp->p5 & SQLITE_NULLEQ ){
      /* If SQLITE_NULLEQ is set (which will only happen if the operator is
      ** OP_Eq or OP_Ne) then take the jump or not depending on whether
      ** or not both operands are null.
      */

      assert( (flags1 & MEM_Cleared)==0 );
      assert( (pOp->p5 & SQLITE_JUMPIFNULL)==0 || CORRUPT_DB );
      testcase( (pOp->p5 & SQLITE_JUMPIFNULL)!=0 );
      if( (flags1&flags3&MEM_Null)!=0
       && (flags3&MEM_Cleared)==0
      ){
        res = 0;  /* Operands are equal */
      }else{
        res = ((flags3 & MEM_Null) ? -1 : +1);  /* Operands are not equal */
      }
    }else{
      /* SQLITE_NULLEQ is clear and at least one operand is NULL,
      ** then the result is always NULL.
      ** The jump is taken if the SQLITE_JUMPIFNULL bit is set.
      */
      if( pOp->p5 & SQLITE_STOREP2 ){

Changes to src/window.c.

1779
1780
1781
1782
1783
1784
1785
1786

1787
1788
1789
1790
1791
1792
1793
1794
1795
1796
1797
1798
1799
1800

1801
1802
1803
1804
1805
1806
1807
....
2363
2364
2365
2366
2367
2368
2369

2370
2371
2372
2373
2374
2375
2376

2377
2378
2379
2380
2381
2382
2383
....
2385
2386
2387
2388
2389
2390
2391


2392
2393
2394
2395
2396
2397
2398
    arith = OP_Subtract;
  }

  windowReadPeerValues(p, csr1, reg1);
  windowReadPeerValues(p, csr2, reg2);

  /* Check if the peer value for csr1 value is a text or blob by comparing
  ** it to the smallest possible string - ''. */

  sqlite3VdbeAddOp4(v, OP_String8, 0, regString, 0, "", P4_STATIC);
  addrGe = sqlite3VdbeAddOp3(v, OP_Ge, regString, 0, reg1);

  if( op==OP_Le ){
    sqlite3VdbeAddOp2(v, OP_IsNull, reg1, lbl);
  }
  if( op==OP_Ge ){
    sqlite3VdbeAddOp2(v, OP_NotNull, reg1, sqlite3VdbeCurrentAddr(v)+2);
    sqlite3VdbeAddOp2(v, OP_IsNull, reg2, lbl);
  }

  sqlite3VdbeAddOp3(v, arith, regVal, reg1, reg1);
  sqlite3VdbeJumpHere(v, addrGe);
  sqlite3VdbeAddOp3(v, op, reg2, lbl, reg1);


  sqlite3ReleaseTempReg(pParse, reg1);
  sqlite3ReleaseTempReg(pParse, reg2);
}

static int windowCodeOp(
 WindowCodeArg *p,
................................................................................
  ** are four options - they may never be deleted (eDelete==0), they may 
  ** be deleted as soon as they are no longer part of the window frame
  ** (eDelete==WINDOW_AGGINVERSE), they may be deleted as after the row 
  ** has been returned to the caller (WINDOW_RETURN_ROW), or they may
  ** be deleted after they enter the frame (WINDOW_AGGSTEP). */
  switch( pMWin->eStart ){
    case TK_FOLLOWING: {

      sqlite3 *db = pParse->db;
      sqlite3_value *pVal = 0;
      sqlite3ValueFromExpr(db, pMWin->pStart, db->enc,SQLITE_AFF_NUMERIC,&pVal);
      if( pVal && sqlite3_value_int(pVal)>0 ){
        s.eDelete = WINDOW_RETURN_ROW;
      }
      sqlite3ValueFree(pVal);

      break;
    }
    case TK_UNBOUNDED:
      if( windowCacheFrame(pMWin)==0 ){
        if( pMWin->eEnd==TK_PRECEDING ){
          s.eDelete = WINDOW_AGGSTEP;
        }else{
................................................................................
        }
      }
      break;
    default:
      s.eDelete = WINDOW_AGGINVERSE;
      break;
  }



  /* Allocate registers for the array of values from the sub-query, the
  ** samve values in record form, and the rowid used to insert said record
  ** into the ephemeral table.  */
  regNew = pParse->nMem+1;
  pParse->nMem += nInput;
  regRecord = ++pParse->nMem;







|
>


<
<
<
<
<
<
<
<
<



>







 







>







>







 







>
>







1779
1780
1781
1782
1783
1784
1785
1786
1787
1788
1789









1790
1791
1792
1793
1794
1795
1796
1797
1798
1799
1800
....
2356
2357
2358
2359
2360
2361
2362
2363
2364
2365
2366
2367
2368
2369
2370
2371
2372
2373
2374
2375
2376
2377
2378
....
2380
2381
2382
2383
2384
2385
2386
2387
2388
2389
2390
2391
2392
2393
2394
2395
    arith = OP_Subtract;
  }

  windowReadPeerValues(p, csr1, reg1);
  windowReadPeerValues(p, csr2, reg2);

  /* Check if the peer value for csr1 value is a text or blob by comparing
  ** it to the smallest possible string - ''. If it is, jump over the
  ** OP_Add or OP_Subtract operation and proceed directly to the comparison. */
  sqlite3VdbeAddOp4(v, OP_String8, 0, regString, 0, "", P4_STATIC);
  addrGe = sqlite3VdbeAddOp3(v, OP_Ge, regString, 0, reg1);









  sqlite3VdbeAddOp3(v, arith, regVal, reg1, reg1);
  sqlite3VdbeJumpHere(v, addrGe);
  sqlite3VdbeAddOp3(v, op, reg2, lbl, reg1);
  sqlite3VdbeChangeP5(v, SQLITE_NULLEQ);

  sqlite3ReleaseTempReg(pParse, reg1);
  sqlite3ReleaseTempReg(pParse, reg2);
}

static int windowCodeOp(
 WindowCodeArg *p,
................................................................................
  ** are four options - they may never be deleted (eDelete==0), they may 
  ** be deleted as soon as they are no longer part of the window frame
  ** (eDelete==WINDOW_AGGINVERSE), they may be deleted as after the row 
  ** has been returned to the caller (WINDOW_RETURN_ROW), or they may
  ** be deleted after they enter the frame (WINDOW_AGGSTEP). */
  switch( pMWin->eStart ){
    case TK_FOLLOWING: {
      if( pMWin->eType!=TK_RANGE ){
        sqlite3 *db = pParse->db;
        sqlite3_value *pVal = 0;
        sqlite3ValueFromExpr(db,pMWin->pStart,db->enc,SQLITE_AFF_NUMERIC,&pVal);
        if( pVal && sqlite3_value_int(pVal)>0 ){
          s.eDelete = WINDOW_RETURN_ROW;
        }
        sqlite3ValueFree(pVal);
      }
      break;
    }
    case TK_UNBOUNDED:
      if( windowCacheFrame(pMWin)==0 ){
        if( pMWin->eEnd==TK_PRECEDING ){
          s.eDelete = WINDOW_AGGSTEP;
        }else{
................................................................................
        }
      }
      break;
    default:
      s.eDelete = WINDOW_AGGINVERSE;
      break;
  }

  s.eDelete = 0;

  /* Allocate registers for the array of values from the sub-query, the
  ** samve values in record form, and the rowid used to insert said record
  ** into the ephemeral table.  */
  regNew = pParse->nMem+1;
  pParse->nMem += nInput;
  regRecord = ++pParse->nMem;

Changes to test/pg_common.tcl.

66
67
68
69
70
71
72


73
74
75
76
77
78
79

  set ret
}

proc execsql_test {tn sql} {
  set res [execsql $sql]
  set sql [string map {string_agg group_concat} $sql]


  puts $::fd "do_execsql_test $tn {"
  puts $::fd "  [string trim $sql]"
  puts $::fd "} {$res}"
  puts $::fd ""
}

proc errorsql_test {tn sql} {







>
>







66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81

  set ret
}

proc execsql_test {tn sql} {
  set res [execsql $sql]
  set sql [string map {string_agg group_concat} $sql]
  set sql [string map [list {NULLS FIRST} {}] $sql]
  set sql [string map [list {NULLS LAST} {}] $sql]
  puts $::fd "do_execsql_test $tn {"
  puts $::fd "  [string trim $sql]"
  puts $::fd "} {$res}"
  puts $::fd ""
}

proc errorsql_test {tn sql} {

Changes to test/window8.tcl.

167
168
169
170
171
172
173
174
175
176
177
178




179






























180
181
182
execsql_test 4.0 {
  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1(a INTEGER, b INTEGER);
  INSERT INTO t1 VALUES
    (NULL, 1), (NULL, 2), (NULL, 3), (10, 4), (10, 5);
}

execsql_test 4.1 {
  SELECT sum(b) OVER (
    ORDER BY a RANGE BETWEEN 5 PRECEDING AND 10 FOLLOWING
  ) FROM t1 ORDER BY 1;
}



































finish_test









|




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



167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
execsql_test 4.0 {
  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1(a INTEGER, b INTEGER);
  INSERT INTO t1 VALUES
    (NULL, 1), (NULL, 2), (NULL, 3), (10, 4), (10, 5);
}

execsql_test 4.1.1 {
  SELECT sum(b) OVER (
    ORDER BY a RANGE BETWEEN 5 PRECEDING AND 10 FOLLOWING
  ) FROM t1 ORDER BY 1;
}
execsql_test 4.1.2 {
  SELECT sum(b) OVER (
    ORDER BY a DESC RANGE BETWEEN 5 PRECEDING AND 10 FOLLOWING
  ) FROM t1 ORDER BY 1;
}

execsql_test 4.2.1 {
  SELECT sum(b) OVER (
    ORDER BY a RANGE BETWEEN 5 FOLLOWING AND 10 FOLLOWING
  ) FROM t1 ORDER BY 1 NULLS FIRST;
}

execsql_test 4.2.2 {
  SELECT sum(b) OVER (
    ORDER BY a DESC RANGE BETWEEN 5 FOLLOWING AND 10 FOLLOWING
  ) FROM t1 ORDER BY 1 NULLS FIRST;
}

execsql_test 4.3.1 {
  SELECT sum(b) OVER (
    ORDER BY a NULLS FIRST RANGE BETWEEN UNBOUNDED PRECEDING AND 10 FOLLOWING
  ) FROM t1 ORDER BY 1 NULLS FIRST;
}

execsql_test 4.4.1 {
  SELECT sum(b) OVER (
    ORDER BY a NULLS FIRST ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) FROM t1 ORDER BY 1 NULLS FIRST;
}

execsql_test 4.4.2 {
  SELECT sum(b) OVER (
    ORDER BY a DESC NULLS LAST ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) FROM t1 ORDER BY 1 NULLS FIRST;
}
finish_test


Changes to test/window8.test.

2618
2619
2620
2621
2622
2623
2624
2625
2626
2627
2628
2629
2630




































2631
do_execsql_test 4.0 {
  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1(a INTEGER, b INTEGER);
  INSERT INTO t1 VALUES
    (NULL, 1), (NULL, 2), (NULL, 3), (10, 4), (10, 5);
} {}

do_execsql_test 4.1 {
  SELECT sum(b) OVER (
    ORDER BY a RANGE BETWEEN 5 PRECEDING AND 10 FOLLOWING
  ) FROM t1 ORDER BY 1;
} {6   6   6   9   9}





































finish_test







|





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

2618
2619
2620
2621
2622
2623
2624
2625
2626
2627
2628
2629
2630
2631
2632
2633
2634
2635
2636
2637
2638
2639
2640
2641
2642
2643
2644
2645
2646
2647
2648
2649
2650
2651
2652
2653
2654
2655
2656
2657
2658
2659
2660
2661
2662
2663
2664
2665
2666
2667
do_execsql_test 4.0 {
  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1(a INTEGER, b INTEGER);
  INSERT INTO t1 VALUES
    (NULL, 1), (NULL, 2), (NULL, 3), (10, 4), (10, 5);
} {}

do_execsql_test 4.1.1 {
  SELECT sum(b) OVER (
    ORDER BY a RANGE BETWEEN 5 PRECEDING AND 10 FOLLOWING
  ) FROM t1 ORDER BY 1;
} {6   6   6   9   9}

do_execsql_test 4.1.2 {
  SELECT sum(b) OVER (
    ORDER BY a DESC RANGE BETWEEN 5 PRECEDING AND 10 FOLLOWING
  ) FROM t1 ORDER BY 1;
} {6   6   6   9   9}

do_execsql_test 4.2.1 {
  SELECT sum(b) OVER (
    ORDER BY a RANGE BETWEEN 5 FOLLOWING AND 10 FOLLOWING
  ) FROM t1 ORDER BY 1 ;
} {{}   {}   6   6   6}

do_execsql_test 4.2.2 {
  SELECT sum(b) OVER (
    ORDER BY a DESC RANGE BETWEEN 5 FOLLOWING AND 10 FOLLOWING
  ) FROM t1 ORDER BY 1 ;
} {{}   {}   6   6   6}

do_execsql_test 4.3.1 {
  SELECT sum(b) OVER (
    ORDER BY a  RANGE BETWEEN UNBOUNDED PRECEDING AND 10 FOLLOWING
  ) FROM t1 ORDER BY 1 ;
} {6   6   6   15   15}

do_execsql_test 4.4.1 {
  SELECT sum(b) OVER (
    ORDER BY a  ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) FROM t1 ORDER BY 1 ;
} {3   6   9   9   12}

do_execsql_test 4.4.2 {
  SELECT sum(b) OVER (
    ORDER BY a DESC  ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) FROM t1 ORDER BY 1 ;
} {5   6   8   9   10}

finish_test