/ 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 Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/vdbe.c.

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

Changes to src/window.c.

  1779   1779       arith = OP_Subtract;
  1780   1780     }
  1781   1781   
  1782   1782     windowReadPeerValues(p, csr1, reg1);
  1783   1783     windowReadPeerValues(p, csr2, reg2);
  1784   1784   
  1785   1785     /* Check if the peer value for csr1 value is a text or blob by comparing
  1786         -  ** it to the smallest possible string - ''. */
         1786  +  ** it to the smallest possible string - ''. If it is, jump over the
         1787  +  ** OP_Add or OP_Subtract operation and proceed directly to the comparison. */
  1787   1788     sqlite3VdbeAddOp4(v, OP_String8, 0, regString, 0, "", P4_STATIC);
  1788   1789     addrGe = sqlite3VdbeAddOp3(v, OP_Ge, regString, 0, reg1);
  1789         -
  1790         -  if( op==OP_Le ){
  1791         -    sqlite3VdbeAddOp2(v, OP_IsNull, reg1, lbl);
  1792         -  }
  1793         -  if( op==OP_Ge ){
  1794         -    sqlite3VdbeAddOp2(v, OP_NotNull, reg1, sqlite3VdbeCurrentAddr(v)+2);
  1795         -    sqlite3VdbeAddOp2(v, OP_IsNull, reg2, lbl);
  1796         -  }
  1797         -
  1798   1790     sqlite3VdbeAddOp3(v, arith, regVal, reg1, reg1);
  1799   1791     sqlite3VdbeJumpHere(v, addrGe);
  1800   1792     sqlite3VdbeAddOp3(v, op, reg2, lbl, reg1);
         1793  +  sqlite3VdbeChangeP5(v, SQLITE_NULLEQ);
  1801   1794   
  1802   1795     sqlite3ReleaseTempReg(pParse, reg1);
  1803   1796     sqlite3ReleaseTempReg(pParse, reg2);
  1804   1797   }
  1805   1798   
  1806   1799   static int windowCodeOp(
  1807   1800    WindowCodeArg *p,
................................................................................
  2363   2356     ** are four options - they may never be deleted (eDelete==0), they may 
  2364   2357     ** be deleted as soon as they are no longer part of the window frame
  2365   2358     ** (eDelete==WINDOW_AGGINVERSE), they may be deleted as after the row 
  2366   2359     ** has been returned to the caller (WINDOW_RETURN_ROW), or they may
  2367   2360     ** be deleted after they enter the frame (WINDOW_AGGSTEP). */
  2368   2361     switch( pMWin->eStart ){
  2369   2362       case TK_FOLLOWING: {
  2370         -      sqlite3 *db = pParse->db;
  2371         -      sqlite3_value *pVal = 0;
  2372         -      sqlite3ValueFromExpr(db, pMWin->pStart, db->enc,SQLITE_AFF_NUMERIC,&pVal);
  2373         -      if( pVal && sqlite3_value_int(pVal)>0 ){
  2374         -        s.eDelete = WINDOW_RETURN_ROW;
         2363  +      if( pMWin->eType!=TK_RANGE ){
         2364  +        sqlite3 *db = pParse->db;
         2365  +        sqlite3_value *pVal = 0;
         2366  +        sqlite3ValueFromExpr(db,pMWin->pStart,db->enc,SQLITE_AFF_NUMERIC,&pVal);
         2367  +        if( pVal && sqlite3_value_int(pVal)>0 ){
         2368  +          s.eDelete = WINDOW_RETURN_ROW;
         2369  +        }
         2370  +        sqlite3ValueFree(pVal);
  2375   2371         }
  2376         -      sqlite3ValueFree(pVal);
  2377   2372         break;
  2378   2373       }
  2379   2374       case TK_UNBOUNDED:
  2380   2375         if( windowCacheFrame(pMWin)==0 ){
  2381   2376           if( pMWin->eEnd==TK_PRECEDING ){
  2382   2377             s.eDelete = WINDOW_AGGSTEP;
  2383   2378           }else{
................................................................................
  2385   2380           }
  2386   2381         }
  2387   2382         break;
  2388   2383       default:
  2389   2384         s.eDelete = WINDOW_AGGINVERSE;
  2390   2385         break;
  2391   2386     }
         2387  +
         2388  +  s.eDelete = 0;
  2392   2389   
  2393   2390     /* Allocate registers for the array of values from the sub-query, the
  2394   2391     ** samve values in record form, and the rowid used to insert said record
  2395   2392     ** into the ephemeral table.  */
  2396   2393     regNew = pParse->nMem+1;
  2397   2394     pParse->nMem += nInput;
  2398   2395     regRecord = ++pParse->nMem;

Changes to test/pg_common.tcl.

    66     66   
    67     67     set ret
    68     68   }
    69     69   
    70     70   proc execsql_test {tn sql} {
    71     71     set res [execsql $sql]
    72     72     set sql [string map {string_agg group_concat} $sql]
           73  +  set sql [string map [list {NULLS FIRST} {}] $sql]
           74  +  set sql [string map [list {NULLS LAST} {}] $sql]
    73     75     puts $::fd "do_execsql_test $tn {"
    74     76     puts $::fd "  [string trim $sql]"
    75     77     puts $::fd "} {$res}"
    76     78     puts $::fd ""
    77     79   }
    78     80   
    79     81   proc errorsql_test {tn sql} {

Changes to test/window8.tcl.

   167    167   execsql_test 4.0 {
   168    168     DROP TABLE IF EXISTS t1;
   169    169     CREATE TABLE t1(a INTEGER, b INTEGER);
   170    170     INSERT INTO t1 VALUES
   171    171       (NULL, 1), (NULL, 2), (NULL, 3), (10, 4), (10, 5);
   172    172   }
   173    173   
   174         -execsql_test 4.1 {
          174  +execsql_test 4.1.1 {
   175    175     SELECT sum(b) OVER (
   176    176       ORDER BY a RANGE BETWEEN 5 PRECEDING AND 10 FOLLOWING
   177    177     ) FROM t1 ORDER BY 1;
   178    178   }
          179  +execsql_test 4.1.2 {
          180  +  SELECT sum(b) OVER (
          181  +    ORDER BY a DESC RANGE BETWEEN 5 PRECEDING AND 10 FOLLOWING
          182  +  ) FROM t1 ORDER BY 1;
          183  +}
          184  +
          185  +execsql_test 4.2.1 {
          186  +  SELECT sum(b) OVER (
          187  +    ORDER BY a RANGE BETWEEN 5 FOLLOWING AND 10 FOLLOWING
          188  +  ) FROM t1 ORDER BY 1 NULLS FIRST;
          189  +}
          190  +
          191  +execsql_test 4.2.2 {
          192  +  SELECT sum(b) OVER (
          193  +    ORDER BY a DESC RANGE BETWEEN 5 FOLLOWING AND 10 FOLLOWING
          194  +  ) FROM t1 ORDER BY 1 NULLS FIRST;
          195  +}
          196  +
          197  +execsql_test 4.3.1 {
          198  +  SELECT sum(b) OVER (
          199  +    ORDER BY a NULLS FIRST RANGE BETWEEN UNBOUNDED PRECEDING AND 10 FOLLOWING
          200  +  ) FROM t1 ORDER BY 1 NULLS FIRST;
          201  +}
          202  +
          203  +execsql_test 4.4.1 {
          204  +  SELECT sum(b) OVER (
          205  +    ORDER BY a NULLS FIRST ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
          206  +  ) FROM t1 ORDER BY 1 NULLS FIRST;
          207  +}
   179    208   
          209  +execsql_test 4.4.2 {
          210  +  SELECT sum(b) OVER (
          211  +    ORDER BY a DESC NULLS LAST ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
          212  +  ) FROM t1 ORDER BY 1 NULLS FIRST;
          213  +}
   180    214   finish_test
   181    215   
   182    216   

Changes to test/window8.test.

  2618   2618   do_execsql_test 4.0 {
  2619   2619     DROP TABLE IF EXISTS t1;
  2620   2620     CREATE TABLE t1(a INTEGER, b INTEGER);
  2621   2621     INSERT INTO t1 VALUES
  2622   2622       (NULL, 1), (NULL, 2), (NULL, 3), (10, 4), (10, 5);
  2623   2623   } {}
  2624   2624   
  2625         -do_execsql_test 4.1 {
         2625  +do_execsql_test 4.1.1 {
  2626   2626     SELECT sum(b) OVER (
  2627   2627       ORDER BY a RANGE BETWEEN 5 PRECEDING AND 10 FOLLOWING
  2628   2628     ) FROM t1 ORDER BY 1;
  2629   2629   } {6   6   6   9   9}
         2630  +
         2631  +do_execsql_test 4.1.2 {
         2632  +  SELECT sum(b) OVER (
         2633  +    ORDER BY a DESC RANGE BETWEEN 5 PRECEDING AND 10 FOLLOWING
         2634  +  ) FROM t1 ORDER BY 1;
         2635  +} {6   6   6   9   9}
         2636  +
         2637  +do_execsql_test 4.2.1 {
         2638  +  SELECT sum(b) OVER (
         2639  +    ORDER BY a RANGE BETWEEN 5 FOLLOWING AND 10 FOLLOWING
         2640  +  ) FROM t1 ORDER BY 1 ;
         2641  +} {{}   {}   6   6   6}
         2642  +
         2643  +do_execsql_test 4.2.2 {
         2644  +  SELECT sum(b) OVER (
         2645  +    ORDER BY a DESC RANGE BETWEEN 5 FOLLOWING AND 10 FOLLOWING
         2646  +  ) FROM t1 ORDER BY 1 ;
         2647  +} {{}   {}   6   6   6}
         2648  +
         2649  +do_execsql_test 4.3.1 {
         2650  +  SELECT sum(b) OVER (
         2651  +    ORDER BY a  RANGE BETWEEN UNBOUNDED PRECEDING AND 10 FOLLOWING
         2652  +  ) FROM t1 ORDER BY 1 ;
         2653  +} {6   6   6   15   15}
         2654  +
         2655  +do_execsql_test 4.4.1 {
         2656  +  SELECT sum(b) OVER (
         2657  +    ORDER BY a  ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
         2658  +  ) FROM t1 ORDER BY 1 ;
         2659  +} {3   6   9   9   12}
         2660  +
         2661  +do_execsql_test 4.4.2 {
         2662  +  SELECT sum(b) OVER (
         2663  +    ORDER BY a DESC  ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
         2664  +  ) FROM t1 ORDER BY 1 ;
         2665  +} {5   6   8   9   10}
  2630   2666   
  2631   2667   finish_test