/ Check-in [94b48064]
Login

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

Overview
Comment:Tighter VDBE code for the WHERE_DISTINCT_ORDERED case of DISTINCT keyword handling.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:94b48064db3cbb43e911fdf7183218b08146ec10
User & Date: drh 2012-09-19 21:15:46
Context
2012-09-20
14:26
Refactoring of DISTINCT code. Change the name of the local variable "distinct" to "distinctTab". Generate cleaner code w/o unnecessary P4 and P5 values on the OP_Null for WHERE_DISTINCT_ORDERED. check-in: 0cda241a user: drh tags: trunk
2012-09-19
21:15
Tighter VDBE code for the WHERE_DISTINCT_ORDERED case of DISTINCT keyword handling. check-in: 94b48064 user: drh tags: trunk
17:31
Add comments to the WHERE_DISTINCT_* macros. No changes to code. check-in: 82320501 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/select.c.

  4055   4055         assert( pWInfo->eDistinct==WHERE_DISTINCT_ORDERED 
  4056   4056              || pWInfo->eDistinct==WHERE_DISTINCT_UNIQUE 
  4057   4057         );
  4058   4058         distinct = -1;
  4059   4059         if( pWInfo->eDistinct==WHERE_DISTINCT_ORDERED ){
  4060   4060           int iJump;
  4061   4061           int iExpr;
  4062         -        int iFlag = ++pParse->nMem;
         4062  +        int nExpr = pEList->nExpr;
  4063   4063           int iBase = pParse->nMem+1;
  4064         -        int iBase2 = iBase + pEList->nExpr;
         4064  +        int iBase2 = iBase + nExpr;
  4065   4065           pParse->nMem += (pEList->nExpr*2);
  4066   4066   
  4067         -        /* Change the OP_OpenEphemeral coded earlier to an OP_Integer. The
  4068         -        ** OP_Integer initializes the "first row" flag.  */
  4069         -        pOp->opcode = OP_Integer;
         4067  +        /* Change the OP_OpenEphemeral coded earlier to an OP_Null
         4068  +        ** sets the MEM_Cleared bit on the first register of the
         4069  +        ** previous value.  This will cause the OP_Ne below to always
         4070  +        ** fail on the first iteration of the loop even if the first
         4071  +        ** row is all NULLs.
         4072  +        */
         4073  +        pOp->opcode = OP_Null;
  4070   4074           pOp->p1 = 1;
  4071         -        pOp->p2 = iFlag;
         4075  +        pOp->p2 = iBase2;
         4076  +        pOp->p3 = iBase2 + nExpr - 1;
  4072   4077   
  4073   4078           sqlite3ExprCodeExprList(pParse, pEList, iBase, 1);
  4074         -        iJump = sqlite3VdbeCurrentAddr(v) + 1 + pEList->nExpr + 1 + 1;
  4075         -        sqlite3VdbeAddOp2(v, OP_If, iFlag, iJump-1);
  4076         -        for(iExpr=0; iExpr<pEList->nExpr; iExpr++){
         4079  +        iJump = sqlite3VdbeCurrentAddr(v) + pEList->nExpr;
         4080  +        for(iExpr=0; iExpr<nExpr; iExpr++){
  4077   4081             CollSeq *pColl = sqlite3ExprCollSeq(pParse, pEList->a[iExpr].pExpr);
  4078         -          sqlite3VdbeAddOp3(v, OP_Ne, iBase+iExpr, iJump, iBase2+iExpr);
         4082  +          if( iExpr<nExpr-1 ){
         4083  +            sqlite3VdbeAddOp3(v, OP_Ne, iBase+iExpr, iJump, iBase2+iExpr);
         4084  +          }else{
         4085  +            sqlite3VdbeAddOp3(v, OP_Eq, iBase+iExpr, pWInfo->iContinue,
         4086  +                              iBase2+iExpr);
         4087  +          }
  4079   4088             sqlite3VdbeChangeP4(v, -1, (const char *)pColl, P4_COLLSEQ);
  4080   4089             sqlite3VdbeChangeP5(v, SQLITE_NULLEQ);
  4081   4090           }
  4082         -        sqlite3VdbeAddOp2(v, OP_Goto, 0, pWInfo->iContinue);
  4083         -
  4084         -        sqlite3VdbeAddOp2(v, OP_Integer, 0, iFlag);
  4085   4091           assert( sqlite3VdbeCurrentAddr(v)==iJump );
  4086   4092           sqlite3VdbeAddOp3(v, OP_Move, iBase, iBase2, pEList->nExpr);
  4087   4093         }else{
  4088   4094           pOp->opcode = OP_Noop;
  4089   4095         }
  4090   4096       }
  4091   4097   

Changes to src/vdbe.c.

   952    952     pOut->z = pOp->p4.z;
   953    953     pOut->n = pOp->p1;
   954    954     pOut->enc = encoding;
   955    955     UPDATE_MAX_BLOBSIZE(pOut);
   956    956     break;
   957    957   }
   958    958   
   959         -/* Opcode: Null * P2 P3 * *
          959  +/* Opcode: Null P1 P2 P3 * *
   960    960   **
   961    961   ** Write a NULL into registers P2.  If P3 greater than P2, then also write
   962         -** NULL into register P3 and ever register in between P2 and P3.  If P3
          962  +** NULL into register P3 and every register in between P2 and P3.  If P3
   963    963   ** is less than P2 (typically P3 is zero) then only register P2 is
   964         -** set to NULL
          964  +** set to NULL.
          965  +**
          966  +** If the P1 value is non-zero, then also set the MEM_Cleared flag so that
          967  +** NULL values will not compare equal even if SQLITE_NULLEQ is set on
          968  +** OP_Ne or OP_Eq.
   965    969   */
   966    970   case OP_Null: {           /* out2-prerelease */
   967    971     int cnt;
          972  +  u16 nullFlag;
   968    973     cnt = pOp->p3-pOp->p2;
   969    974     assert( pOp->p3<=p->nMem );
   970         -  pOut->flags = MEM_Null;
          975  +  pOut->flags = nullFlag = pOp->p1 ? (MEM_Null|MEM_Cleared) : MEM_Null;
   971    976     while( cnt>0 ){
   972    977       pOut++;
   973    978       memAboutToChange(p, pOut);
   974    979       VdbeMemRelease(pOut);
   975         -    pOut->flags = MEM_Null;
          980  +    pOut->flags = nullFlag;
   976    981       cnt--;
   977    982     }
   978    983     break;
   979    984   }
   980    985   
   981    986   
   982    987   /* Opcode: Blob P1 P2 * P4
................................................................................
  1733   1738   ** memcmp() is used to compare text string.  If both values are
  1734   1739   ** numeric, then a numeric comparison is used. If the two values
  1735   1740   ** are of different types, then numbers are considered less than
  1736   1741   ** strings and strings are considered less than blobs.
  1737   1742   **
  1738   1743   ** If the SQLITE_STOREP2 bit of P5 is set, then do not jump.  Instead,
  1739   1744   ** store a boolean result (either 0, or 1, or NULL) in register P2.
         1745  +**
         1746  +** If the SQLITE_NULLEQ bit is set in P5, then NULL values are considered
         1747  +** equal to one another, provided that they do not have their MEM_Cleared
         1748  +** bit set.
  1740   1749   */
  1741   1750   /* Opcode: Ne P1 P2 P3 P4 P5
  1742   1751   **
  1743   1752   ** This works just like the Lt opcode except that the jump is taken if
  1744   1753   ** the operands in registers P1 and P3 are not equal.  See the Lt opcode for
  1745   1754   ** additional information.
  1746   1755   **
................................................................................
  1799   1808       /* One or both operands are NULL */
  1800   1809       if( pOp->p5 & SQLITE_NULLEQ ){
  1801   1810         /* If SQLITE_NULLEQ is set (which will only happen if the operator is
  1802   1811         ** OP_Eq or OP_Ne) then take the jump or not depending on whether
  1803   1812         ** or not both operands are null.
  1804   1813         */
  1805   1814         assert( pOp->opcode==OP_Eq || pOp->opcode==OP_Ne );
  1806         -      res = (flags1 & flags3 & MEM_Null)==0;
         1815  +      assert( (flags1 & MEM_Cleared)==0 );
         1816  +      if( (flags1&MEM_Null)!=0
         1817  +       && (flags3&MEM_Null)!=0
         1818  +       && (flags3&MEM_Cleared)==0
         1819  +      ){
         1820  +        res = 0;  /* Results are equal */
         1821  +      }else{
         1822  +        res = 1;  /* Results are not equal */
         1823  +      }
  1807   1824       }else{
  1808   1825         /* SQLITE_NULLEQ is clear and at least one operand is NULL,
  1809   1826         ** then the result is always NULL.
  1810   1827         ** The jump is taken if the SQLITE_JUMPIFNULL bit is set.
  1811   1828         */
  1812   1829         if( pOp->p5 & SQLITE_STOREP2 ){
  1813   1830           pOut = &aMem[pOp->p2];

Changes to src/vdbeInt.h.

   183    183   #define MEM_Str       0x0002   /* Value is a string */
   184    184   #define MEM_Int       0x0004   /* Value is an integer */
   185    185   #define MEM_Real      0x0008   /* Value is a real number */
   186    186   #define MEM_Blob      0x0010   /* Value is a BLOB */
   187    187   #define MEM_RowSet    0x0020   /* Value is a RowSet object */
   188    188   #define MEM_Frame     0x0040   /* Value is a VdbeFrame object */
   189    189   #define MEM_Invalid   0x0080   /* Value is undefined */
   190         -#define MEM_TypeMask  0x00ff   /* Mask of type bits */
          190  +#define MEM_Cleared   0x0100   /* NULL set by OP_Null, not from data */
          191  +#define MEM_TypeMask  0x01ff   /* Mask of type bits */
          192  +
   191    193   
   192    194   /* Whenever Mem contains a valid string or blob representation, one of
   193    195   ** the following flags must be set to determine the memory management
   194    196   ** policy for Mem.z.  The MEM_Term flag tells us whether or not the
   195    197   ** string is \000 or \u0000 terminated
   196    198   */
   197    199   #define MEM_Term      0x0200   /* String rep is nul terminated */

Changes to test/distinct.test.

   174    174     do_temptables_test 2.$tn.2 "SELECT DISTINCT $sql" $temptables
   175    175   }
   176    176   
   177    177   do_execsql_test 2.A {
   178    178     SELECT (SELECT DISTINCT o.a FROM t1 AS i) FROM t1 AS o ORDER BY rowid;
   179    179   } {a A a A}
   180    180   
   181         -
   182         -
          181  +do_test 3.0 {
          182  +  db eval {
          183  +    CREATE TABLE t3(a INTEGER, b INTEGER, c, UNIQUE(a,b));
          184  +    INSERT INTO t3 VALUES
          185  +        (null, null, 1),
          186  +        (null, null, 2),
          187  +        (null, 3, 4),
          188  +        (null, 3, 5),
          189  +        (6, null, 7),
          190  +        (6, null, 8);
          191  +    SELECT DISTINCT a, b FROM t3 ORDER BY +a, +b;
          192  +  }
          193  +} {{} {} {} 3 6 {}}
          194  +do_test 3.1 {
          195  +  regexp {OpenEphemeral} [db eval {
          196  +    EXPLAIN SELECT DISTINCT a, b FROM t3 ORDER BY +a, +b;
          197  +  }]
          198  +} {0}
   183    199   
   184    200   finish_test