/ Check-in [b76f35b0]
Login

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

Overview
Comment:Fix a problem with the handling of NULL values in the min() window function.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256:b76f35b09235d44dc3d176377bbb9c18b7cdc9392800103ff53c54730a427a5c
User & Date: dan 2018-07-07 17:30:44
Context
2018-07-07
17:38
Add missing VdbeCoverage() macro to window.c. check-in: 63f4d306 user: dan tags: trunk
17:30
Fix a problem with the handling of NULL values in the min() window function. check-in: b76f35b0 user: dan tags: trunk
2018-07-06
17:19
Try to improve the error messages for misformed frame specifications in window definitions. check-in: 927b95a0 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/window.c.

1081
1082
1083
1084
1085
1086
1087


1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098

1099
1100
1101
1102
1103
1104
1105
      assert( !(flags & SQLITE_FUNC_WINDOW_SIZE) );
      regArg = reg + pWin->iArgCol;
    }

    if( (pWin->pFunc->funcFlags & SQLITE_FUNC_MINMAX) 
      && pWin->eStart!=TK_UNBOUNDED 
    ){


      if( bInverse==0 ){
        sqlite3VdbeAddOp2(v, OP_AddImm, pWin->regApp+1, 1);
        sqlite3VdbeAddOp2(v, OP_SCopy, regArg, pWin->regApp);
        sqlite3VdbeAddOp3(v, OP_MakeRecord, pWin->regApp, 2, pWin->regApp+2);
        sqlite3VdbeAddOp2(v, OP_IdxInsert, pWin->csrApp, pWin->regApp+2);
      }else{
        sqlite3VdbeAddOp4Int(v, OP_SeekGE, pWin->csrApp, 0, regArg, 1);
        VdbeCoverage(v);
        sqlite3VdbeAddOp1(v, OP_Delete, pWin->csrApp);
        sqlite3VdbeJumpHere(v, sqlite3VdbeCurrentAddr(v)-2);
      }

    }else if( pWin->regApp ){
      assert( pWin->pFunc->xSFunc==nth_valueStepFunc 
           || pWin->pFunc->xSFunc==first_valueStepFunc 
      );
      assert( bInverse==0 || bInverse==1 );
      sqlite3VdbeAddOp2(v, OP_AddImm, pWin->regApp+1-bInverse, 1);
    }else if( pWin->pFunc->xSFunc==leadStepFunc 







>
>











>







1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
      assert( !(flags & SQLITE_FUNC_WINDOW_SIZE) );
      regArg = reg + pWin->iArgCol;
    }

    if( (pWin->pFunc->funcFlags & SQLITE_FUNC_MINMAX) 
      && pWin->eStart!=TK_UNBOUNDED 
    ){
      int addrIsNull = sqlite3VdbeAddOp1(v, OP_IsNull, regArg);
      VdbeCoverage(v);
      if( bInverse==0 ){
        sqlite3VdbeAddOp2(v, OP_AddImm, pWin->regApp+1, 1);
        sqlite3VdbeAddOp2(v, OP_SCopy, regArg, pWin->regApp);
        sqlite3VdbeAddOp3(v, OP_MakeRecord, pWin->regApp, 2, pWin->regApp+2);
        sqlite3VdbeAddOp2(v, OP_IdxInsert, pWin->csrApp, pWin->regApp+2);
      }else{
        sqlite3VdbeAddOp4Int(v, OP_SeekGE, pWin->csrApp, 0, regArg, 1);
        VdbeCoverage(v);
        sqlite3VdbeAddOp1(v, OP_Delete, pWin->csrApp);
        sqlite3VdbeJumpHere(v, sqlite3VdbeCurrentAddr(v)-2);
      }
      sqlite3VdbeJumpHere(v, addrIsNull);
    }else if( pWin->regApp ){
      assert( pWin->pFunc->xSFunc==nth_valueStepFunc 
           || pWin->pFunc->xSFunc==first_valueStepFunc 
      );
      assert( bInverse==0 || bInverse==1 );
      sqlite3VdbeAddOp2(v, OP_AddImm, pWin->regApp+1-bInverse, 1);
    }else if( pWin->pFunc->xSFunc==leadStepFunc 

Changes to test/window4.tcl.

335
336
337
338
339
340
341












342
343
344
execsql_float_test 9.6 {
  SELECT percent_rank() OVER () FROM t1
}

execsql_float_test 9.7 {
  SELECT cume_dist() OVER () FROM t1
}













finish_test








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



335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
execsql_float_test 9.6 {
  SELECT percent_rank() OVER () FROM t1
}

execsql_float_test 9.7 {
  SELECT cume_dist() OVER () FROM t1
}

execsql_test 10.0 {
  DROP TABLE IF EXISTS t7;
  CREATE TABLE t7(id INTEGER PRIMARY KEY, a INTEGER, b INTEGER);
  INSERT INTO t7(id, a, b) VALUES
    (1, 1, 2), (2, 1, NULL), (3, 1, 4),
    (4, 3, NULL), (5, 3, 8), (6, 3, 1);
}
execsql_test 10.1 {
  SELECT id, min(b) OVER (PARTITION BY a ORDER BY id) FROM t7;
}


finish_test

Changes to test/window4.test.

1245
1246
1247
1248
1249
1250
1251












1252
  set myres {}
  foreach r [db eval {SELECT cume_dist() OVER () FROM t1}] {
    lappend myres [format %.2f [set r]]
  }
  set myres
} {1.00 1.00 1.00}













finish_test







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

1245
1246
1247
1248
1249
1250
1251
1252
1253
1254
1255
1256
1257
1258
1259
1260
1261
1262
1263
1264
  set myres {}
  foreach r [db eval {SELECT cume_dist() OVER () FROM t1}] {
    lappend myres [format %.2f [set r]]
  }
  set myres
} {1.00 1.00 1.00}

do_execsql_test 10.0 {
  DROP TABLE IF EXISTS t7;
  CREATE TABLE t7(id INTEGER PRIMARY KEY, a INTEGER, b INTEGER);
  INSERT INTO t7(id, a, b) VALUES
    (1, 1, 2), (2, 1, NULL), (3, 1, 4),
    (4, 3, NULL), (5, 3, 8), (6, 3, 1);
} {}

do_execsql_test 10.1 {
  SELECT id, min(b) OVER (PARTITION BY a ORDER BY id) FROM t7;
} {1 2   2 2   3 2   4 {}   5 8   6 1}

finish_test