SQLite

Check-in [4d336e9ef5]
Login

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

Overview
Comment:Changes directed toward optimizing IS NULL terms in WHERE clauses. (CVS 3492)
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 4d336e9ef5f65b95959e7d01cd0357d46e9b1fa5
User & Date: drh 2006-10-27 14:06:58.000
Context
2006-10-27
14:21
Fix the ".dump" command in the command-line shell so that it shows TRIGGERs and VIEWs. Ticket #2044. (CVS 3493) (check-in: 58171a41f7 user: drh tags: trunk)
14:06
Changes directed toward optimizing IS NULL terms in WHERE clauses. (CVS 3492) (check-in: 4d336e9ef5 user: drh tags: trunk)
2006-10-26
18:15
Bring CVS output into more commonly accepted practice. Tickets #2030, #1573. Add command-line options -bail and ".bail" commands. Default behavior is to continue after encountering an error. Ticket #2045. (CVS 3491) (check-in: 517712d6fb user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/vdbe.c.
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
**
** Various scripts scan this source file in order to generate HTML
** documentation, headers files, or other derived files.  The formatting
** of the code in this file is, therefore, important.  See other comments
** in this file for details.  If in doubt, do not deviate from existing
** commenting and indentation practices when changing or adding code.
**
** $Id: vdbe.c,v 1.577 2006/09/23 20:36:02 drh Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>
#include "vdbeInt.h"

/*







|







39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
**
** Various scripts scan this source file in order to generate HTML
** documentation, headers files, or other derived files.  The formatting
** of the code in this file is, therefore, important.  See other comments
** in this file for details.  If in doubt, do not deviate from existing
** commenting and indentation practices when changing or adding code.
**
** $Id: vdbe.c,v 1.578 2006/10/27 14:06:58 drh Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>
#include "vdbeInt.h"

/*
1808
1809
1810
1811
1812
1813
1814
1815


1816
1817
1818
1819
1820
1821
1822
1823
1824
1825
1826
1827
1828
1829


1830
1831
1832


1833
1834
1835
1836
1837
1838

1839
1840
1841
1842
1843
1844
1845
1846
1847
  pTos--;
  if( c ) pc = pOp->p2-1;
  break;
}

/* Opcode: IsNull P1 P2 *
**
** If any of the top abs(P1) values on the stack are NULL, then jump


** to P2.  Pop the stack P1 times if P1>0.   If P1<0 leave the stack
** unchanged.
*/
case OP_IsNull: {            /* same as TK_ISNULL, no-push */
  int i, cnt;
  Mem *pTerm;
  cnt = pOp->p1;
  if( cnt<0 ) cnt = -cnt;
  pTerm = &pTos[1-cnt];
  assert( pTerm>=p->aStack );
  for(i=0; i<cnt; i++, pTerm++){
    if( pTerm->flags & MEM_Null ){
      pc = pOp->p2-1;
      break;


    }
  }
  if( pOp->p1>0 ) popStack(&pTos, cnt);


  break;
}

/* Opcode: NotNull P1 P2 *
**
** Jump to P2 if the top P1 values on the stack are all not NULL.  Pop the

** stack if P1 times if P1 is greater than zero.  If P1 is less than
** zero then leave the stack unchanged.
*/
case OP_NotNull: {            /* same as TK_NOTNULL, no-push */
  int i, cnt;
  cnt = pOp->p1;
  if( cnt<0 ) cnt = -cnt;
  assert( &pTos[1-cnt] >= p->aStack );
  for(i=0; i<cnt && (pTos[1+i-cnt].flags & MEM_Null)==0; i++){}







|
>
>
|
|


<
<
<
<
<
<
<
|
|
<
>
>


|
>
>





|
>
|
|







1808
1809
1810
1811
1812
1813
1814
1815
1816
1817
1818
1819
1820
1821







1822
1823

1824
1825
1826
1827
1828
1829
1830
1831
1832
1833
1834
1835
1836
1837
1838
1839
1840
1841
1842
1843
1844
1845
1846
  pTos--;
  if( c ) pc = pOp->p2-1;
  break;
}

/* Opcode: IsNull P1 P2 *
**
** Check the top of the stack and jump to P2 if the top of the stack
** is NULL.  If P1 is positive, then pop P1 elements from the stack
** regardless of whether or not the jump is taken.  If P1 is negative,
** pop -P1 elements from the stack only if the jump is taken and leave
** the stack unchanged if the jump is not taken.
*/
case OP_IsNull: {            /* same as TK_ISNULL, no-push */







  if( pTos->flags & MEM_Null ){
    pc = pOp->p2-1;

    if( pOp->p1<0 ){
      popStack(&pTos, -pOp->p1);
    }
  }
  if( pOp->p1>0 ){
    popStack(&pTos, pOp->p1);
  }
  break;
}

/* Opcode: NotNull P1 P2 *
**
** Jump to P2 if the top abs(P1) values on the stack are all not NULL.  
** Regardless of whether or not the jump is taken, pop the stack
** P1 times if P1 is greater than zero.  But if P1 is negative,
** leave the stack unchanged.
*/
case OP_NotNull: {            /* same as TK_NOTNULL, no-push */
  int i, cnt;
  cnt = pOp->p1;
  if( cnt<0 ) cnt = -cnt;
  assert( &pTos[1-cnt] >= p->aStack );
  for(i=0; i<cnt && (pTos[1+i-cnt].flags & MEM_Null)==0; i++){}
Changes to src/vdbeaux.c.
1888
1889
1890
1891
1892
1893
1894
1895
1896
1897
1898
1899
1900
1901
1902


1903
1904
1905
1906
1907
1908
1909

    /* Read the serial types for the next element in each key. */
    idx1 += GetVarint( aKey1+idx1, serial_type1 );
    if( d1>=nKey1 && sqlite3VdbeSerialTypeLen(serial_type1)>0 ) break;
    idx2 += GetVarint( aKey2+idx2, serial_type2 );
    if( d2>=nKey2 && sqlite3VdbeSerialTypeLen(serial_type2)>0 ) break;

    /* Assert that there is enough space left in each key for the blob of
    ** data to go with the serial type just read. This assert may fail if
    ** the file is corrupted.  Then read the value from each key into mem1
    ** and mem2 respectively.
    */
    d1 += sqlite3VdbeSerialGet(&aKey1[d1], serial_type1, &mem1);
    d2 += sqlite3VdbeSerialGet(&aKey2[d2], serial_type2, &mem2);



    rc = sqlite3MemCompare(&mem1, &mem2, i<nField ? pKeyInfo->aColl[i] : 0);
    if( mem1.flags & MEM_Dyn ) sqlite3VdbeMemRelease(&mem1);
    if( mem2.flags & MEM_Dyn ) sqlite3VdbeMemRelease(&mem2);
    if( rc!=0 ){
      break;
    }
    i++;







<
<
|
<




>
>







1888
1889
1890
1891
1892
1893
1894


1895

1896
1897
1898
1899
1900
1901
1902
1903
1904
1905
1906
1907
1908

    /* Read the serial types for the next element in each key. */
    idx1 += GetVarint( aKey1+idx1, serial_type1 );
    if( d1>=nKey1 && sqlite3VdbeSerialTypeLen(serial_type1)>0 ) break;
    idx2 += GetVarint( aKey2+idx2, serial_type2 );
    if( d2>=nKey2 && sqlite3VdbeSerialTypeLen(serial_type2)>0 ) break;



    /* Extract the values to be compared.

    */
    d1 += sqlite3VdbeSerialGet(&aKey1[d1], serial_type1, &mem1);
    d2 += sqlite3VdbeSerialGet(&aKey2[d2], serial_type2, &mem2);

    /* Do the comparison
    */
    rc = sqlite3MemCompare(&mem1, &mem2, i<nField ? pKeyInfo->aColl[i] : 0);
    if( mem1.flags & MEM_Dyn ) sqlite3VdbeMemRelease(&mem1);
    if( mem2.flags & MEM_Dyn ) sqlite3VdbeMemRelease(&mem2);
    if( rc!=0 ){
      break;
    }
    i++;
Changes to src/where.c.
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
** This module contains C code that generates VDBE code used to process
** the WHERE clause of SQL statements.  This module is reponsible for
** generating the code that loops through a table looking for applicable
** rows.  Indices are selected and used to speed the search when doing
** so is applicable.  Because this module is responsible for selecting
** indices, you might also think of this module as the "query optimizer".
**
** $Id: where.c,v 1.229 2006/10/18 23:26:39 drh Exp $
*/
#include "sqliteInt.h"

/*
** The number of bits in a Bitmask.  "BMS" means "BitMask Size".
*/
#define BMS  (sizeof(Bitmask)*8)







|







12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
** This module contains C code that generates VDBE code used to process
** the WHERE clause of SQL statements.  This module is reponsible for
** generating the code that loops through a table looking for applicable
** rows.  Indices are selected and used to speed the search when doing
** so is applicable.  Because this module is responsible for selecting
** indices, you might also think of this module as the "query optimizer".
**
** $Id: where.c,v 1.230 2006/10/27 14:06:59 drh Exp $
*/
#include "sqliteInt.h"

/*
** The number of bits in a Bitmask.  "BMS" means "BitMask Size".
*/
#define BMS  (sizeof(Bitmask)*8)
1491
1492
1493
1494
1495
1496
1497
1498
1499
1500
1501
1502
1503
1504
1505
1506
1507
1508
1509
**    *  Construct a probe entry from the top nColumn entries in
**       the stack with affinities appropriate for index pIdx. 
**       Only nColumn elements are popped from the stack in this case
**       (by OP_MakeRecord).
**
*/
static void buildIndexProbe(
  Vdbe *v, 
  int nColumn, 
  int nExtra, 
  int brk, 
  Index *pIdx
){
  sqlite3VdbeAddOp(v, OP_NotNull, -nColumn, sqlite3VdbeCurrentAddr(v)+3);
  sqlite3VdbeAddOp(v, OP_Pop, nColumn+nExtra, 0);
  sqlite3VdbeAddOp(v, OP_Goto, 0, brk);
  sqlite3VdbeAddOp(v, OP_MakeRecord, nColumn, 0);
  sqlite3IndexAffinityStr(v, pIdx);
}







|
|
|
|
|







1491
1492
1493
1494
1495
1496
1497
1498
1499
1500
1501
1502
1503
1504
1505
1506
1507
1508
1509
**    *  Construct a probe entry from the top nColumn entries in
**       the stack with affinities appropriate for index pIdx. 
**       Only nColumn elements are popped from the stack in this case
**       (by OP_MakeRecord).
**
*/
static void buildIndexProbe(
  Vdbe *v,        /* Generate code into this VM */
  int nColumn,    /* The number of columns to check for NULL */
  int nExtra,     /* Number of extra values beyond nColumn */
  int brk,        /* Jump to here if no match is possible */
  Index *pIdx     /* Index that we will be searching */
){
  sqlite3VdbeAddOp(v, OP_NotNull, -nColumn, sqlite3VdbeCurrentAddr(v)+3);
  sqlite3VdbeAddOp(v, OP_Pop, nColumn+nExtra, 0);
  sqlite3VdbeAddOp(v, OP_Goto, 0, brk);
  sqlite3VdbeAddOp(v, OP_MakeRecord, nColumn, 0);
  sqlite3IndexAffinityStr(v, pIdx);
}
1609
1610
1611
1612
1613
1614
1615



1616
1617
1618
1619
1620
1621
1622
  */
  for(j=0; j<pIdx->nColumn; j++){
    int k = pIdx->aiColumn[j];
    pTerm = findTerm(pWC, iCur, k, notReady, WO_EQ|WO_IN, pIdx);
    if( pTerm==0 ) break;
    assert( (pTerm->flags & TERM_CODED)==0 );
    codeEqualityTerm(pParse, pTerm, brk, pLevel);



    if( termsInMem ){
      sqlite3VdbeAddOp(v, OP_MemStore, pLevel->iMem+j+1, 1);
    }
  }
  assert( j==nEq );

  /* Make sure all the constraint values are on the top of the stack







>
>
>







1609
1610
1611
1612
1613
1614
1615
1616
1617
1618
1619
1620
1621
1622
1623
1624
1625
  */
  for(j=0; j<pIdx->nColumn; j++){
    int k = pIdx->aiColumn[j];
    pTerm = findTerm(pWC, iCur, k, notReady, WO_EQ|WO_IN, pIdx);
    if( pTerm==0 ) break;
    assert( (pTerm->flags & TERM_CODED)==0 );
    codeEqualityTerm(pParse, pTerm, brk, pLevel);
#if 0  /* WORK IN PROGRESS */
    sqlite3VdbeAddOp(v, OP_IsNull, termsInMem ? -1 : -(j+1), brk);
#endif
    if( termsInMem ){
      sqlite3VdbeAddOp(v, OP_MemStore, pLevel->iMem+j+1, 1);
    }
  }
  assert( j==nEq );

  /* Make sure all the constraint values are on the top of the stack