/ Check-in [94b48064]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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 Unified Diffs Ignore Whitespace Patch

Changes to src/select.c.

4055
4056
4057
4058
4059
4060
4061
4062
4063
4064
4065
4066
4067
4068




4069
4070
4071

4072
4073
4074
4075
4076
4077

4078




4079
4080
4081
4082
4083
4084
4085
4086
4087
4088
4089
4090
4091
      assert( pWInfo->eDistinct==WHERE_DISTINCT_ORDERED 
           || pWInfo->eDistinct==WHERE_DISTINCT_UNIQUE 
      );
      distinct = -1;
      if( pWInfo->eDistinct==WHERE_DISTINCT_ORDERED ){
        int iJump;
        int iExpr;
        int iFlag = ++pParse->nMem;
        int iBase = pParse->nMem+1;
        int iBase2 = iBase + pEList->nExpr;
        pParse->nMem += (pEList->nExpr*2);

        /* Change the OP_OpenEphemeral coded earlier to an OP_Integer. The
        ** OP_Integer initializes the "first row" flag.  */




        pOp->opcode = OP_Integer;
        pOp->p1 = 1;
        pOp->p2 = iFlag;


        sqlite3ExprCodeExprList(pParse, pEList, iBase, 1);
        iJump = sqlite3VdbeCurrentAddr(v) + 1 + pEList->nExpr + 1 + 1;
        sqlite3VdbeAddOp2(v, OP_If, iFlag, iJump-1);
        for(iExpr=0; iExpr<pEList->nExpr; iExpr++){
          CollSeq *pColl = sqlite3ExprCollSeq(pParse, pEList->a[iExpr].pExpr);

          sqlite3VdbeAddOp3(v, OP_Ne, iBase+iExpr, iJump, iBase2+iExpr);




          sqlite3VdbeChangeP4(v, -1, (const char *)pColl, P4_COLLSEQ);
          sqlite3VdbeChangeP5(v, SQLITE_NULLEQ);
        }
        sqlite3VdbeAddOp2(v, OP_Goto, 0, pWInfo->iContinue);

        sqlite3VdbeAddOp2(v, OP_Integer, 0, iFlag);
        assert( sqlite3VdbeCurrentAddr(v)==iJump );
        sqlite3VdbeAddOp3(v, OP_Move, iBase, iBase2, pEList->nExpr);
      }else{
        pOp->opcode = OP_Noop;
      }
    }








|

|


|
|
>
>
>
>
|

|
>


|
<
|

>
|
>
>
>
>



<
<
<







4055
4056
4057
4058
4059
4060
4061
4062
4063
4064
4065
4066
4067
4068
4069
4070
4071
4072
4073
4074
4075
4076
4077
4078
4079

4080
4081
4082
4083
4084
4085
4086
4087
4088
4089
4090



4091
4092
4093
4094
4095
4096
4097
      assert( pWInfo->eDistinct==WHERE_DISTINCT_ORDERED 
           || pWInfo->eDistinct==WHERE_DISTINCT_UNIQUE 
      );
      distinct = -1;
      if( pWInfo->eDistinct==WHERE_DISTINCT_ORDERED ){
        int iJump;
        int iExpr;
        int nExpr = pEList->nExpr;
        int iBase = pParse->nMem+1;
        int iBase2 = iBase + nExpr;
        pParse->nMem += (pEList->nExpr*2);

        /* Change the OP_OpenEphemeral coded earlier to an OP_Null
        ** sets the MEM_Cleared bit on the first register of the
        ** previous value.  This will cause the OP_Ne below to always
        ** fail on the first iteration of the loop even if the first
        ** row is all NULLs.
        */
        pOp->opcode = OP_Null;
        pOp->p1 = 1;
        pOp->p2 = iBase2;
        pOp->p3 = iBase2 + nExpr - 1;

        sqlite3ExprCodeExprList(pParse, pEList, iBase, 1);
        iJump = sqlite3VdbeCurrentAddr(v) + pEList->nExpr;

        for(iExpr=0; iExpr<nExpr; iExpr++){
          CollSeq *pColl = sqlite3ExprCollSeq(pParse, pEList->a[iExpr].pExpr);
          if( iExpr<nExpr-1 ){
            sqlite3VdbeAddOp3(v, OP_Ne, iBase+iExpr, iJump, iBase2+iExpr);
          }else{
            sqlite3VdbeAddOp3(v, OP_Eq, iBase+iExpr, pWInfo->iContinue,
                              iBase2+iExpr);
          }
          sqlite3VdbeChangeP4(v, -1, (const char *)pColl, P4_COLLSEQ);
          sqlite3VdbeChangeP5(v, SQLITE_NULLEQ);
        }



        assert( sqlite3VdbeCurrentAddr(v)==iJump );
        sqlite3VdbeAddOp3(v, OP_Move, iBase, iBase2, pEList->nExpr);
      }else{
        pOp->opcode = OP_Noop;
      }
    }

Changes to src/vdbe.c.

952
953
954
955
956
957
958
959
960
961
962
963
964




965
966
967

968
969
970

971
972
973
974
975
976
977
978
979
980
981
982
....
1733
1734
1735
1736
1737
1738
1739




1740
1741
1742
1743
1744
1745
1746
....
1799
1800
1801
1802
1803
1804
1805
1806








1807
1808
1809
1810
1811
1812
1813
  pOut->z = pOp->p4.z;
  pOut->n = pOp->p1;
  pOut->enc = encoding;
  UPDATE_MAX_BLOBSIZE(pOut);
  break;
}

/* Opcode: Null * P2 P3 * *
**
** Write a NULL into registers P2.  If P3 greater than P2, then also write
** NULL into register P3 and ever register in between P2 and P3.  If P3
** is less than P2 (typically P3 is zero) then only register P2 is
** set to NULL




*/
case OP_Null: {           /* out2-prerelease */
  int cnt;

  cnt = pOp->p3-pOp->p2;
  assert( pOp->p3<=p->nMem );
  pOut->flags = MEM_Null;

  while( cnt>0 ){
    pOut++;
    memAboutToChange(p, pOut);
    VdbeMemRelease(pOut);
    pOut->flags = MEM_Null;
    cnt--;
  }
  break;
}


/* Opcode: Blob P1 P2 * P4
................................................................................
** memcmp() is used to compare text string.  If both values are
** numeric, then a numeric comparison is used. If the two values
** are of different types, then numbers are considered less than
** strings and strings are considered less than blobs.
**
** If the SQLITE_STOREP2 bit of P5 is set, then do not jump.  Instead,
** store a boolean result (either 0, or 1, or NULL) in register P2.




*/
/* Opcode: Ne P1 P2 P3 P4 P5
**
** This works just like the Lt opcode except that the jump is taken if
** the operands in registers P1 and P3 are not equal.  See the Lt opcode for
** additional information.
**
................................................................................
    /* 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 );
      res = (flags1 & flags3 & MEM_Null)==0;








    }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 ){
        pOut = &aMem[pOp->p2];







|


|

|
>
>
>
>



>


<
>




|







 







>
>
>
>







 







|
>
>
>
>
>
>
>
>







952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974

975
976
977
978
979
980
981
982
983
984
985
986
987
....
1738
1739
1740
1741
1742
1743
1744
1745
1746
1747
1748
1749
1750
1751
1752
1753
1754
1755
....
1808
1809
1810
1811
1812
1813
1814
1815
1816
1817
1818
1819
1820
1821
1822
1823
1824
1825
1826
1827
1828
1829
1830
  pOut->z = pOp->p4.z;
  pOut->n = pOp->p1;
  pOut->enc = encoding;
  UPDATE_MAX_BLOBSIZE(pOut);
  break;
}

/* Opcode: Null P1 P2 P3 * *
**
** Write a NULL into registers P2.  If P3 greater than P2, then also write
** NULL into register P3 and every register in between P2 and P3.  If P3
** is less than P2 (typically P3 is zero) then only register P2 is
** set to NULL.
**
** If the P1 value is non-zero, then also set the MEM_Cleared flag so that
** NULL values will not compare equal even if SQLITE_NULLEQ is set on
** OP_Ne or OP_Eq.
*/
case OP_Null: {           /* out2-prerelease */
  int cnt;
  u16 nullFlag;
  cnt = pOp->p3-pOp->p2;
  assert( pOp->p3<=p->nMem );

  pOut->flags = nullFlag = pOp->p1 ? (MEM_Null|MEM_Cleared) : MEM_Null;
  while( cnt>0 ){
    pOut++;
    memAboutToChange(p, pOut);
    VdbeMemRelease(pOut);
    pOut->flags = nullFlag;
    cnt--;
  }
  break;
}


/* Opcode: Blob P1 P2 * P4
................................................................................
** memcmp() is used to compare text string.  If both values are
** numeric, then a numeric comparison is used. If the two values
** are of different types, then numbers are considered less than
** strings and strings are considered less than blobs.
**
** If the SQLITE_STOREP2 bit of P5 is set, then do not jump.  Instead,
** store a boolean result (either 0, or 1, or NULL) in register P2.
**
** If the SQLITE_NULLEQ bit is set in P5, then NULL values are considered
** equal to one another, provided that they do not have their MEM_Cleared
** bit set.
*/
/* Opcode: Ne P1 P2 P3 P4 P5
**
** This works just like the Lt opcode except that the jump is taken if
** the operands in registers P1 and P3 are not equal.  See the Lt opcode for
** additional information.
**
................................................................................
    /* 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 );
      if( (flags1&MEM_Null)!=0
       && (flags3&MEM_Null)!=0
       && (flags3&MEM_Cleared)==0
      ){
        res = 0;  /* Results are equal */
      }else{
        res = 1;  /* Results 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 ){
        pOut = &aMem[pOp->p2];

Changes to src/vdbeInt.h.

183
184
185
186
187
188
189

190

191
192
193
194
195
196
197
#define MEM_Str       0x0002   /* Value is a string */
#define MEM_Int       0x0004   /* Value is an integer */
#define MEM_Real      0x0008   /* Value is a real number */
#define MEM_Blob      0x0010   /* Value is a BLOB */
#define MEM_RowSet    0x0020   /* Value is a RowSet object */
#define MEM_Frame     0x0040   /* Value is a VdbeFrame object */
#define MEM_Invalid   0x0080   /* Value is undefined */

#define MEM_TypeMask  0x00ff   /* Mask of type bits */


/* Whenever Mem contains a valid string or blob representation, one of
** the following flags must be set to determine the memory management
** policy for Mem.z.  The MEM_Term flag tells us whether or not the
** string is \000 or \u0000 terminated
*/
#define MEM_Term      0x0200   /* String rep is nul terminated */







>
|
>







183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
#define MEM_Str       0x0002   /* Value is a string */
#define MEM_Int       0x0004   /* Value is an integer */
#define MEM_Real      0x0008   /* Value is a real number */
#define MEM_Blob      0x0010   /* Value is a BLOB */
#define MEM_RowSet    0x0020   /* Value is a RowSet object */
#define MEM_Frame     0x0040   /* Value is a VdbeFrame object */
#define MEM_Invalid   0x0080   /* Value is undefined */
#define MEM_Cleared   0x0100   /* NULL set by OP_Null, not from data */
#define MEM_TypeMask  0x01ff   /* Mask of type bits */


/* Whenever Mem contains a valid string or blob representation, one of
** the following flags must be set to determine the memory management
** policy for Mem.z.  The MEM_Term flag tells us whether or not the
** string is \000 or \u0000 terminated
*/
#define MEM_Term      0x0200   /* String rep is nul terminated */

Changes to test/distinct.test.

174
175
176
177
178
179
180











181






182
183
184
  do_temptables_test 2.$tn.2 "SELECT DISTINCT $sql" $temptables
}

do_execsql_test 2.A {
  SELECT (SELECT DISTINCT o.a FROM t1 AS i) FROM t1 AS o ORDER BY rowid;
} {a A a A}





















finish_test







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

<

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
  do_temptables_test 2.$tn.2 "SELECT DISTINCT $sql" $temptables
}

do_execsql_test 2.A {
  SELECT (SELECT DISTINCT o.a FROM t1 AS i) FROM t1 AS o ORDER BY rowid;
} {a A a A}

do_test 3.0 {
  db eval {
    CREATE TABLE t3(a INTEGER, b INTEGER, c, UNIQUE(a,b));
    INSERT INTO t3 VALUES
        (null, null, 1),
        (null, null, 2),
        (null, 3, 4),
        (null, 3, 5),
        (6, null, 7),
        (6, null, 8);
    SELECT DISTINCT a, b FROM t3 ORDER BY +a, +b;
  }
} {{} {} {} 3 6 {}}
do_test 3.1 {
  regexp {OpenEphemeral} [db eval {
    EXPLAIN SELECT DISTINCT a, b FROM t3 ORDER BY +a, +b;
  }]
} {0}


finish_test