/ Check-in [d45a97be]
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:Fix handling of "x IN (...)" and "x NOT IN (...)" expressions when the set contains an SQL NULL value. (CVS 5314)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: d45a97be71fa61ab4a692bd807ab762130f7f5b9
User & Date: danielk1977 2008-06-26 18:04:03
Context
2008-06-26
18:16
Documentation updates in sqlite.h.in. No changes to code. (CVS 5315) check-in: 168fd6f8 user: drh tags: trunk
18:04
Fix handling of "x IN (...)" and "x NOT IN (...)" expressions when the set contains an SQL NULL value. (CVS 5314) check-in: d45a97be user: danielk1977 tags: trunk
15:04
Document the rules for when an sqlite3_blob object expires. (CVS 5313) check-in: e1de2287 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/expr.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
....
1669
1670
1671
1672
1673
1674
1675
1676
1677
1678
1679
1680
1681
1682
1683
1684
1685




























1686
1687
1688
1689
1690
1691

1692
1693
1694
1695
1696
1697
1698
....
1760
1761
1762
1763
1764
1765
1766



1767
1768
1769
1770
1771
1772




1773
1774
1775
1776
1777
1778
1779
1780
....
1788
1789
1790
1791
1792
1793
1794
1795
1796
1797
1798
1799
1800
1801
1802
....
1816
1817
1818
1819
1820
1821
1822




1823
1824
1825
1826
1827
1828
1829
....
2531
2532
2533
2534
2535
2536
2537
2538
2539
2540
2541
2542
2543


2544
2545
2546
2547
2548



2549
2550
2551
2552
2553
2554
2555
....
2566
2567
2568
2569
2570
2571
2572

2573
2574





2575
2576
2577
2578












2579
























2580
2581
2582
2583
2584
2585
2586
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains routines used for analyzing expressions and
** for generating VDBE code that evaluates expressions in SQLite.
**
** $Id: expr.c,v 1.376 2008/06/24 12:46:31 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** Return the 'affinity' of the expression pExpr if any.
**
................................................................................
**                    populated epheremal table.
**
** An existing structure may only be used if the SELECT is of the simple
** form:
**
**     SELECT <column> FROM <table>
**
** If the mustBeUnique parameter is false, the structure will be used 
** for fast set membership tests. In this case an epheremal table must 
** be used unless <column> is an INTEGER PRIMARY KEY or an index can 
** be found with <column> as its left-most column.
**
** If mustBeUnique is true, then the structure will be used to iterate
** through the set members, skipping any duplicates. In this case an
** epheremal table must be used unless the selected <column> is guaranteed
** to be unique - either because it is an INTEGER PRIMARY KEY or it
** is unique by virtue of a constraint or implicit index.




























*/
#ifndef SQLITE_OMIT_SUBQUERY
int sqlite3FindInIndex(Parse *pParse, Expr *pX, int mustBeUnique){
  Select *p;
  int eType = 0;
  int iTab = pParse->nTab++;


  /* The follwing if(...) expression is true if the SELECT is of the 
  ** simple form:
  **
  **     SELECT <column> FROM <table>
  **
  ** If this is the case, it may be possible to use an existing table
................................................................................
          sqlite3VdbeAddOp2(v, OP_SetNumColumns, 0, pIdx->nColumn);
          sqlite3VdbeAddOp4(v, OP_OpenRead, iTab, pIdx->tnum, iDb,
                               pKey,P4_KEYINFO_HANDOFF);
          VdbeComment((v, "%s", pIdx->zName));
          eType = IN_INDEX_INDEX;

          sqlite3VdbeJumpHere(v, iAddr);



        }
      }
    }
  }

  if( eType==0 ){




    sqlite3CodeSubselect(pParse, pX);
    eType = IN_INDEX_EPH;
  }else{
    pX->iTable = iTab;
  }
  return eType;
}
#endif
................................................................................
**     x IN (4,5,11)              -- IN operator with list on right-hand side
**     x IN (SELECT a FROM b)     -- IN operator with subquery on the right
**
** The pExpr parameter describes the expression that contains the IN
** operator or subquery.
*/
#ifndef SQLITE_OMIT_SUBQUERY
void sqlite3CodeSubselect(Parse *pParse, Expr *pExpr){
  int testAddr = 0;                       /* One-time test address */
  Vdbe *v = sqlite3GetVdbe(pParse);
  if( v==0 ) return;


  /* This code must be run in its entirety every time it is encountered
  ** if any of the following is true:
................................................................................
  }

  switch( pExpr->op ){
    case TK_IN: {
      char affinity;
      KeyInfo keyInfo;
      int addr;        /* Address of OP_OpenEphemeral instruction */





      affinity = sqlite3ExprAffinity(pExpr->pLeft);

      /* Whether this is an 'x IN(SELECT...)' or an 'x IN(<exprlist>)'
      ** expression it is handled the same way. A virtual table is 
      ** filled with single-field index keys representing the results
      ** from the SELECT or the <exprlist>.
................................................................................
    }
#ifndef SQLITE_OMIT_SUBQUERY
    case TK_EXISTS:
    case TK_SELECT: {
      testcase( op==TK_EXISTS );
      testcase( op==TK_SELECT );
      if( pExpr->iColumn==0 ){
        sqlite3CodeSubselect(pParse, pExpr);
      }
      inReg = pExpr->iColumn;
      break;
    }
    case TK_IN: {


      int j1, j2, j3, j4, j5;
      char affinity;
      int eType;

      eType = sqlite3FindInIndex(pParse, pExpr, 0);




      /* Figure out the affinity to use to create a key from the results
      ** of the expression. affinityStr stores a static string suitable for
      ** P4 of OP_MakeRecord.
      */
      affinity = comparisonAffinity(pExpr);

................................................................................
      sqlite3VdbeJumpHere(v, j1);
      if( eType==IN_INDEX_ROWID ){
        j3 = sqlite3VdbeAddOp1(v, OP_MustBeInt, r1);
        j4 = sqlite3VdbeAddOp3(v, OP_NotExists, pExpr->iTable, 0, r1);
        j5 = sqlite3VdbeAddOp0(v, OP_Goto);
        sqlite3VdbeJumpHere(v, j3);
        sqlite3VdbeJumpHere(v, j4);

      }else{
        r2 = regFree2 = sqlite3GetTempReg(pParse);





        sqlite3VdbeAddOp4(v, OP_MakeRecord, r1, 1, r2, &affinity, 1);
        sqlite3ExprCacheAffinityChange(pParse, r1, 1);
        j5 = sqlite3VdbeAddOp3(v, OP_Found, pExpr->iTable, 0, r2);
      }












      sqlite3VdbeAddOp2(v, OP_AddImm, target, -1);
























      sqlite3VdbeJumpHere(v, j2);
      sqlite3VdbeJumpHere(v, j5);
      break;
    }
#endif
    /*
    **    x BETWEEN y AND z







|







 







<
<
<
<
<
|




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


|



>







 







>
>
>






>
>
>
>
|







 







|







 







>
>
>
>







 







|





>
>




|
>
>
>







 







>


>
>
>
>
>



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







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
....
1669
1670
1671
1672
1673
1674
1675





1676
1677
1678
1679
1680
1681
1682
1683
1684
1685
1686
1687
1688
1689
1690
1691
1692
1693
1694
1695
1696
1697
1698
1699
1700
1701
1702
1703
1704
1705
1706
1707
1708
1709
1710
1711
1712
1713
1714
1715
1716
1717
1718
1719
1720
1721
1722
....
1784
1785
1786
1787
1788
1789
1790
1791
1792
1793
1794
1795
1796
1797
1798
1799
1800
1801
1802
1803
1804
1805
1806
1807
1808
1809
1810
1811
....
1819
1820
1821
1822
1823
1824
1825
1826
1827
1828
1829
1830
1831
1832
1833
....
1847
1848
1849
1850
1851
1852
1853
1854
1855
1856
1857
1858
1859
1860
1861
1862
1863
1864
....
2566
2567
2568
2569
2570
2571
2572
2573
2574
2575
2576
2577
2578
2579
2580
2581
2582
2583
2584
2585
2586
2587
2588
2589
2590
2591
2592
2593
2594
2595
....
2606
2607
2608
2609
2610
2611
2612
2613
2614
2615
2616
2617
2618
2619
2620
2621
2622
2623
2624
2625
2626
2627
2628
2629
2630
2631
2632
2633
2634
2635
2636
2637
2638
2639
2640
2641
2642
2643
2644
2645
2646
2647
2648
2649
2650
2651
2652
2653
2654
2655
2656
2657
2658
2659
2660
2661
2662
2663
2664
2665
2666
2667
2668
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains routines used for analyzing expressions and
** for generating VDBE code that evaluates expressions in SQLite.
**
** $Id: expr.c,v 1.377 2008/06/26 18:04:03 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** Return the 'affinity' of the expression pExpr if any.
**
................................................................................
**                    populated epheremal table.
**
** An existing structure may only be used if the SELECT is of the simple
** form:
**
**     SELECT <column> FROM <table>
**





** If prNotFound parameter is 0, then the structure will be used to iterate
** through the set members, skipping any duplicates. In this case an
** epheremal table must be used unless the selected <column> is guaranteed
** to be unique - either because it is an INTEGER PRIMARY KEY or it
** is unique by virtue of a constraint or implicit index.
**
** If the prNotFound parameter is not 0, then the structure will be used 
** for fast set membership tests. In this case an epheremal table must 
** be used unless <column> is an INTEGER PRIMARY KEY or an index can 
** be found with <column> as its left-most column.
**
** When the structure is being used for set membership tests, the user
** needs to know whether or not the structure contains an SQL NULL 
** value in order to correctly evaluate expressions like "X IN (Y, Z)".
** If there is a chance that the structure may contain a NULL value at
** runtime, then a register is allocated and the register number written
** to *prNotFound. If there is no chance that the structure contains a
** NULL value, then *prNotFound is left unchanged.
**
** If a register is allocated and its location stored in *prNotFound, then
** its initial value is NULL. If the structure does not remain constant
** for the duration of the query (i.e. the set is a correlated sub-select), 
** the value of the allocated register is reset to NULL each time the 
** structure is repopulated. This allows the caller to use vdbe code 
** equivalent to the following:
**
**   if( register==NULL ){
**     has_null = <test if data structure contains null>
**     register = 1
**   }
**
** in order to avoid running the <test if data structure contains null>
** test more often than is necessary.
*/
#ifndef SQLITE_OMIT_SUBQUERY
int sqlite3FindInIndex(Parse *pParse, Expr *pX, int *prNotFound){
  Select *p;
  int eType = 0;
  int iTab = pParse->nTab++;
  int mustBeUnique = !prNotFound;

  /* The follwing if(...) expression is true if the SELECT is of the 
  ** simple form:
  **
  **     SELECT <column> FROM <table>
  **
  ** If this is the case, it may be possible to use an existing table
................................................................................
          sqlite3VdbeAddOp2(v, OP_SetNumColumns, 0, pIdx->nColumn);
          sqlite3VdbeAddOp4(v, OP_OpenRead, iTab, pIdx->tnum, iDb,
                               pKey,P4_KEYINFO_HANDOFF);
          VdbeComment((v, "%s", pIdx->zName));
          eType = IN_INDEX_INDEX;

          sqlite3VdbeJumpHere(v, iAddr);
          if( prNotFound && !pTab->aCol[iCol].notNull ){
            *prNotFound = ++pParse->nMem;
          }
        }
      }
    }
  }

  if( eType==0 ){
    int rMayHaveNull = 0;
    if( prNotFound ){
      *prNotFound = rMayHaveNull = ++pParse->nMem;
    }
    sqlite3CodeSubselect(pParse, pX, rMayHaveNull);
    eType = IN_INDEX_EPH;
  }else{
    pX->iTable = iTab;
  }
  return eType;
}
#endif
................................................................................
**     x IN (4,5,11)              -- IN operator with list on right-hand side
**     x IN (SELECT a FROM b)     -- IN operator with subquery on the right
**
** The pExpr parameter describes the expression that contains the IN
** operator or subquery.
*/
#ifndef SQLITE_OMIT_SUBQUERY
void sqlite3CodeSubselect(Parse *pParse, Expr *pExpr, int rMayHaveNull){
  int testAddr = 0;                       /* One-time test address */
  Vdbe *v = sqlite3GetVdbe(pParse);
  if( v==0 ) return;


  /* This code must be run in its entirety every time it is encountered
  ** if any of the following is true:
................................................................................
  }

  switch( pExpr->op ){
    case TK_IN: {
      char affinity;
      KeyInfo keyInfo;
      int addr;        /* Address of OP_OpenEphemeral instruction */

      if( rMayHaveNull ){
        sqlite3VdbeAddOp2(v, OP_Null, 0, rMayHaveNull);
      }

      affinity = sqlite3ExprAffinity(pExpr->pLeft);

      /* Whether this is an 'x IN(SELECT...)' or an 'x IN(<exprlist>)'
      ** expression it is handled the same way. A virtual table is 
      ** filled with single-field index keys representing the results
      ** from the SELECT or the <exprlist>.
................................................................................
    }
#ifndef SQLITE_OMIT_SUBQUERY
    case TK_EXISTS:
    case TK_SELECT: {
      testcase( op==TK_EXISTS );
      testcase( op==TK_SELECT );
      if( pExpr->iColumn==0 ){
        sqlite3CodeSubselect(pParse, pExpr, 0);
      }
      inReg = pExpr->iColumn;
      break;
    }
    case TK_IN: {
      int rNotFound = 0;
      int rMayHaveNull = 0;
      int j1, j2, j3, j4, j5;
      char affinity;
      int eType;

      eType = sqlite3FindInIndex(pParse, pExpr, &rMayHaveNull);
      if( rMayHaveNull ){
        rNotFound = ++pParse->nMem;
      }

      /* Figure out the affinity to use to create a key from the results
      ** of the expression. affinityStr stores a static string suitable for
      ** P4 of OP_MakeRecord.
      */
      affinity = comparisonAffinity(pExpr);

................................................................................
      sqlite3VdbeJumpHere(v, j1);
      if( eType==IN_INDEX_ROWID ){
        j3 = sqlite3VdbeAddOp1(v, OP_MustBeInt, r1);
        j4 = sqlite3VdbeAddOp3(v, OP_NotExists, pExpr->iTable, 0, r1);
        j5 = sqlite3VdbeAddOp0(v, OP_Goto);
        sqlite3VdbeJumpHere(v, j3);
        sqlite3VdbeJumpHere(v, j4);
        sqlite3VdbeAddOp2(v, OP_Integer, 0, target);
      }else{
        r2 = regFree2 = sqlite3GetTempReg(pParse);

        /* Create a record and test for set membership. If the set contains
        ** the value, then jump to the end of the test code. The target
        ** register still contains the true (1) value written to it earlier.
        */
        sqlite3VdbeAddOp4(v, OP_MakeRecord, r1, 1, r2, &affinity, 1);
        sqlite3ExprCacheAffinityChange(pParse, r1, 1);
        j5 = sqlite3VdbeAddOp3(v, OP_Found, pExpr->iTable, 0, r2);

        /* If the set membership test fails, then the result of the 
        ** "x IN (...)" expression must be either 0 or NULL. If the set
        ** contains no NULL values, then the result is 0. If the set 
        ** contains one or more NULL values, then the result of the
        ** expression is also NULL.
        */
        if( rNotFound==0 ){
          /* This branch runs if it is known at compile time (now) that 
          ** the set contains no NULL values. This happens as the result
          ** of a "NOT NULL" constraint in the database schema. No need
          ** to test the data structure at runtime in this case.
          */
          sqlite3VdbeAddOp2(v, OP_Integer, 0, target);
        }else{
          /* This block populates the rNotFound register with either NULL
          ** or 0 (an integer value). If the data structure contains one
          ** or more NULLs, then set rNotFound to NULL. Otherwise, set it
          ** to 0. If register rMayHaveNull is already set to some value
          ** other than NULL, then the test has already been run and 
          ** rNotFound is already populated.
          */
          j3 = sqlite3VdbeAddOp1(v, OP_NotNull, rMayHaveNull);
          sqlite3VdbeAddOp2(v, OP_Null, 0, rNotFound);
          sqlite3VdbeAddOp2(v, OP_Integer, 1, rMayHaveNull);
	  sqlite3VdbeAddOp4(v, OP_MakeRecord, rNotFound, 1, r2, 0, 1);
          j4 = sqlite3VdbeAddOp3(v, OP_Found, pExpr->iTable, 0, r2);
          sqlite3VdbeAddOp2(v, OP_Integer, 0, rNotFound);
          sqlite3VdbeJumpHere(v, j4);
          sqlite3VdbeJumpHere(v, j3);

          /* Copy the value of register rNotFound (which is either NULL or 0)
	  ** into the target register. This will be the result of the
          ** expression.
          */
          sqlite3VdbeAddOp2(v, OP_Copy, rNotFound, target);
        }
      }
      sqlite3VdbeJumpHere(v, j2);
      sqlite3VdbeJumpHere(v, j5);
      break;
    }
#endif
    /*
    **    x BETWEEN y AND z

Changes to src/select.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
...
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
...
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
....
2387
2388
2389
2390
2391
2392
2393
2394
2395
2396
2397
2398
2399
2400
2401
2402
2403
2404
2405
2406
2407
2408
2409
....
3573
3574
3575
3576
3577
3578
3579
3580
3581
3582
3583
3584
3585
3586
3587
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle SELECT statements in SQLite.
**
** $Id: select.c,v 1.436 2008/06/25 00:12:41 drh Exp $
*/
#include "sqliteInt.h"


/*
** Delete all the content of a Select structure but do not deallocate
** the select structure itself.
................................................................................
    ** then there should be a single item on the stack.  Write this
    ** item into the set table with bogus data.
    */
    case SRT_Set: {
      int addr2;

      assert( nColumn==1 );
      addr2 = sqlite3VdbeAddOp1(v, OP_IsNull, regResult);
      p->affinity = sqlite3CompareAffinity(pEList->a[0].pExpr, pDest->affinity);
      if( pOrderBy ){
        /* At first glance you would think we could optimize out the
        ** ORDER BY in this case since the order of entries in the set
        ** does not matter.  But there might be a LIMIT clause, in which
        ** case the order does matter */
        pushOntoSorter(pParse, pOrderBy, p, regResult);
................................................................................
      }else{
        int r1 = sqlite3GetTempReg(pParse);
        sqlite3VdbeAddOp4(v, OP_MakeRecord, regResult, 1, r1, &p->affinity, 1);
        sqlite3ExprCacheAffinityChange(pParse, regResult, 1);
        sqlite3VdbeAddOp2(v, OP_IdxInsert, iParm, r1);
        sqlite3ReleaseTempReg(pParse, r1);
      }
      sqlite3VdbeJumpHere(v, addr2);
      break;
    }

    /* If any row exist in the result set, record that fact and abort.
    */
    case SRT_Exists: {
      sqlite3VdbeAddOp2(v, OP_Integer, 1, iParm);
................................................................................
      sqlite3VdbeAddOp2(v, OP_NewRowid, iParm, regRowid);
      sqlite3VdbeAddOp3(v, OP_Insert, iParm, regRow, regRowid);
      sqlite3VdbeChangeP5(v, OPFLAG_APPEND);
      break;
    }
#ifndef SQLITE_OMIT_SUBQUERY
    case SRT_Set: {
      int j1;
      assert( nColumn==1 );
      j1 = sqlite3VdbeAddOp1(v, OP_IsNull, regRow);
      sqlite3VdbeAddOp4(v, OP_MakeRecord, regRow, 1, regRowid, &p->affinity, 1);
      sqlite3ExprCacheAffinityChange(pParse, regRow, 1);
      sqlite3VdbeAddOp2(v, OP_IdxInsert, iParm, regRowid);
      sqlite3VdbeJumpHere(v, j1);
      break;
    }
    case SRT_Mem: {
      assert( nColumn==1 );
      sqlite3ExprCodeMove(pParse, regRow, iParm, 1);
      /* The LIMIT clause will terminate the loop for us */
      break;
................................................................................
    /* If we are creating a set for an "expr IN (SELECT ...)" construct,
    ** then there should be a single item on the stack.  Write this
    ** item into the set table with bogus data.
    */
    case SRT_Set: {
      int addr2, r1;
      assert( pIn->nMem==1 );
      addr2 = sqlite3VdbeAddOp1(v, OP_IsNull, pIn->iMem);
      p->affinity = 
         sqlite3CompareAffinity(p->pEList->a[0].pExpr, pDest->affinity);
      r1 = sqlite3GetTempReg(pParse);
      sqlite3VdbeAddOp4(v, OP_MakeRecord, pIn->iMem, 1, r1, &p->affinity, 1);
      sqlite3ExprCacheAffinityChange(pParse, pIn->iMem, 1);
      sqlite3VdbeAddOp2(v, OP_IdxInsert, pDest->iParm, r1);
      sqlite3ReleaseTempReg(pParse, r1);
      sqlite3VdbeJumpHere(v, addr2);
      break;
    }

    /* If any row exist in the result set, record that fact and abort.
    */
    case SRT_Exists: {
      sqlite3VdbeAddOp2(v, OP_Integer, 1, pDest->iParm);
................................................................................
**
**     pDest->eDest    Result
**     ------------    -------------------------------------------
**     SRT_Callback    Invoke the callback for each row of the result.
**
**     SRT_Mem         Store first result in memory cell pDest->iParm
**
**     SRT_Set         Store non-null results as keys of table pDest->iParm. 
**                     Apply the affinity pDest->affinity before storing them.
**
**     SRT_Union       Store results as a key in a temporary table pDest->iParm.
**
**     SRT_Except      Remove results from the temporary table pDest->iParm.
**
**     SRT_Table       Store results in temporary table pDest->iParm







|







 







<







 







<







 







<

<



<







 







<







<







 







|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
651
652
653
654
655
656
657

658
659
660
661
662
663
664
...
665
666
667
668
669
670
671

672
673
674
675
676
677
678
...
822
823
824
825
826
827
828

829

830
831
832

833
834
835
836
837
838
839
....
2382
2383
2384
2385
2386
2387
2388

2389
2390
2391
2392
2393
2394
2395

2396
2397
2398
2399
2400
2401
2402
....
3566
3567
3568
3569
3570
3571
3572
3573
3574
3575
3576
3577
3578
3579
3580
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle SELECT statements in SQLite.
**
** $Id: select.c,v 1.437 2008/06/26 18:04:03 danielk1977 Exp $
*/
#include "sqliteInt.h"


/*
** Delete all the content of a Select structure but do not deallocate
** the select structure itself.
................................................................................
    ** then there should be a single item on the stack.  Write this
    ** item into the set table with bogus data.
    */
    case SRT_Set: {
      int addr2;

      assert( nColumn==1 );

      p->affinity = sqlite3CompareAffinity(pEList->a[0].pExpr, pDest->affinity);
      if( pOrderBy ){
        /* At first glance you would think we could optimize out the
        ** ORDER BY in this case since the order of entries in the set
        ** does not matter.  But there might be a LIMIT clause, in which
        ** case the order does matter */
        pushOntoSorter(pParse, pOrderBy, p, regResult);
................................................................................
      }else{
        int r1 = sqlite3GetTempReg(pParse);
        sqlite3VdbeAddOp4(v, OP_MakeRecord, regResult, 1, r1, &p->affinity, 1);
        sqlite3ExprCacheAffinityChange(pParse, regResult, 1);
        sqlite3VdbeAddOp2(v, OP_IdxInsert, iParm, r1);
        sqlite3ReleaseTempReg(pParse, r1);
      }

      break;
    }

    /* If any row exist in the result set, record that fact and abort.
    */
    case SRT_Exists: {
      sqlite3VdbeAddOp2(v, OP_Integer, 1, iParm);
................................................................................
      sqlite3VdbeAddOp2(v, OP_NewRowid, iParm, regRowid);
      sqlite3VdbeAddOp3(v, OP_Insert, iParm, regRow, regRowid);
      sqlite3VdbeChangeP5(v, OPFLAG_APPEND);
      break;
    }
#ifndef SQLITE_OMIT_SUBQUERY
    case SRT_Set: {

      assert( nColumn==1 );

      sqlite3VdbeAddOp4(v, OP_MakeRecord, regRow, 1, regRowid, &p->affinity, 1);
      sqlite3ExprCacheAffinityChange(pParse, regRow, 1);
      sqlite3VdbeAddOp2(v, OP_IdxInsert, iParm, regRowid);

      break;
    }
    case SRT_Mem: {
      assert( nColumn==1 );
      sqlite3ExprCodeMove(pParse, regRow, iParm, 1);
      /* The LIMIT clause will terminate the loop for us */
      break;
................................................................................
    /* If we are creating a set for an "expr IN (SELECT ...)" construct,
    ** then there should be a single item on the stack.  Write this
    ** item into the set table with bogus data.
    */
    case SRT_Set: {
      int addr2, r1;
      assert( pIn->nMem==1 );

      p->affinity = 
         sqlite3CompareAffinity(p->pEList->a[0].pExpr, pDest->affinity);
      r1 = sqlite3GetTempReg(pParse);
      sqlite3VdbeAddOp4(v, OP_MakeRecord, pIn->iMem, 1, r1, &p->affinity, 1);
      sqlite3ExprCacheAffinityChange(pParse, pIn->iMem, 1);
      sqlite3VdbeAddOp2(v, OP_IdxInsert, pDest->iParm, r1);
      sqlite3ReleaseTempReg(pParse, r1);

      break;
    }

    /* If any row exist in the result set, record that fact and abort.
    */
    case SRT_Exists: {
      sqlite3VdbeAddOp2(v, OP_Integer, 1, pDest->iParm);
................................................................................
**
**     pDest->eDest    Result
**     ------------    -------------------------------------------
**     SRT_Callback    Invoke the callback for each row of the result.
**
**     SRT_Mem         Store first result in memory cell pDest->iParm
**
**     SRT_Set         Store results as keys of table pDest->iParm. 
**                     Apply the affinity pDest->affinity before storing them.
**
**     SRT_Union       Store results as a key in a temporary table pDest->iParm.
**
**     SRT_Except      Remove results from the temporary table pDest->iParm.
**
**     SRT_Table       Store results in temporary table pDest->iParm

Changes to src/sqliteInt.h.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
....
1431
1432
1433
1434
1435
1436
1437
1438
1439
1440
1441
1442
1443
1444
1445
....
2114
2115
2116
2117
2118
2119
2120
2121
2122
2123
2124
2125
2126
2127
2128
....
2220
2221
2222
2223
2224
2225
2226
2227
2228
2229
2230
2231
2232
2233
2234
**    May you do good and not evil.
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.731 2008/06/26 10:54:12 danielk1977 Exp $
*/
#ifndef _SQLITEINT_H_
#define _SQLITEINT_H_

/*
** Include the configuration header output by 'configure' if we're using the
** autoconf-based build
................................................................................
#define SRT_Discard      4  /* Do not save the results anywhere */

/* The ORDER BY clause is ignored for all of the above */
#define IgnorableOrderby(X) ((X->eDest)<=SRT_Discard)

#define SRT_Callback     5  /* Invoke a callback with each row of result */
#define SRT_Mem          6  /* Store result in a memory cell */
#define SRT_Set          7  /* Store non-null results as keys in an index */
#define SRT_Table        8  /* Store result as data with an automatic rowid */
#define SRT_EphemTab     9  /* Create transient tab and store like SRT_Table */
#define SRT_Coroutine   10  /* Generate a single row of result */

/*
** A structure used to customize the behaviour of sqlite3Select(). See
** comments above sqlite3Select() for details.
................................................................................
void sqlite3RootPageMoved(Db*, int, int);
void sqlite3Reindex(Parse*, Token*, Token*);
void sqlite3AlterFunctions(sqlite3*);
void sqlite3AlterRenameTable(Parse*, SrcList*, Token*);
int sqlite3GetToken(const unsigned char *, int *);
void sqlite3NestedParse(Parse*, const char*, ...);
void sqlite3ExpirePreparedStatements(sqlite3*);
void sqlite3CodeSubselect(Parse *, Expr *);
int sqlite3SelectResolve(Parse *, Select *, NameContext *);
void sqlite3ColumnDefault(Vdbe *, Table *, int);
void sqlite3AlterFinishAddColumn(Parse *, Token *);
void sqlite3AlterBeginAddColumn(Parse *, SrcList *);
CollSeq *sqlite3GetCollSeq(sqlite3*, CollSeq *, const char *, int);
char sqlite3AffinityType(const Token*);
void sqlite3Analyze(Parse*, Token*, Token*);
................................................................................
  #define sqlite3BeginBenignMalloc()
  #define sqlite3EndBenignMalloc()
#endif

#define IN_INDEX_ROWID           1
#define IN_INDEX_EPH             2
#define IN_INDEX_INDEX           3
int sqlite3FindInIndex(Parse *, Expr *, int);

#ifdef SQLITE_ENABLE_ATOMIC_WRITE
  int sqlite3JournalOpen(sqlite3_vfs *, const char *, sqlite3_file *, int, int);
  int sqlite3JournalSize(sqlite3_vfs *);
  int sqlite3JournalCreate(sqlite3_file *);
#else
  #define sqlite3JournalSize(pVfs) ((pVfs)->szOsFile)







|







 







|







 







|







 







|







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
....
1431
1432
1433
1434
1435
1436
1437
1438
1439
1440
1441
1442
1443
1444
1445
....
2114
2115
2116
2117
2118
2119
2120
2121
2122
2123
2124
2125
2126
2127
2128
....
2220
2221
2222
2223
2224
2225
2226
2227
2228
2229
2230
2231
2232
2233
2234
**    May you do good and not evil.
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.732 2008/06/26 18:04:03 danielk1977 Exp $
*/
#ifndef _SQLITEINT_H_
#define _SQLITEINT_H_

/*
** Include the configuration header output by 'configure' if we're using the
** autoconf-based build
................................................................................
#define SRT_Discard      4  /* Do not save the results anywhere */

/* The ORDER BY clause is ignored for all of the above */
#define IgnorableOrderby(X) ((X->eDest)<=SRT_Discard)

#define SRT_Callback     5  /* Invoke a callback with each row of result */
#define SRT_Mem          6  /* Store result in a memory cell */
#define SRT_Set          7  /* Store results as keys in an index */
#define SRT_Table        8  /* Store result as data with an automatic rowid */
#define SRT_EphemTab     9  /* Create transient tab and store like SRT_Table */
#define SRT_Coroutine   10  /* Generate a single row of result */

/*
** A structure used to customize the behaviour of sqlite3Select(). See
** comments above sqlite3Select() for details.
................................................................................
void sqlite3RootPageMoved(Db*, int, int);
void sqlite3Reindex(Parse*, Token*, Token*);
void sqlite3AlterFunctions(sqlite3*);
void sqlite3AlterRenameTable(Parse*, SrcList*, Token*);
int sqlite3GetToken(const unsigned char *, int *);
void sqlite3NestedParse(Parse*, const char*, ...);
void sqlite3ExpirePreparedStatements(sqlite3*);
void sqlite3CodeSubselect(Parse *, Expr *, int);
int sqlite3SelectResolve(Parse *, Select *, NameContext *);
void sqlite3ColumnDefault(Vdbe *, Table *, int);
void sqlite3AlterFinishAddColumn(Parse *, Token *);
void sqlite3AlterBeginAddColumn(Parse *, SrcList *);
CollSeq *sqlite3GetCollSeq(sqlite3*, CollSeq *, const char *, int);
char sqlite3AffinityType(const Token*);
void sqlite3Analyze(Parse*, Token*, Token*);
................................................................................
  #define sqlite3BeginBenignMalloc()
  #define sqlite3EndBenignMalloc()
#endif

#define IN_INDEX_ROWID           1
#define IN_INDEX_EPH             2
#define IN_INDEX_INDEX           3
int sqlite3FindInIndex(Parse *, Expr *, int*);

#ifdef SQLITE_ENABLE_ATOMIC_WRITE
  int sqlite3JournalOpen(sqlite3_vfs *, const char *, sqlite3_file *, int, int);
  int sqlite3JournalSize(sqlite3_vfs *);
  int sqlite3JournalCreate(sqlite3_file *);
#else
  #define sqlite3JournalSize(pVfs) ((pVfs)->szOsFile)

Changes to src/vdbe.c.

39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
....
1424
1425
1426
1427
1428
1429
1430
1431
1432
1433
1434
1435
1436
1437
1438
**
** 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.755 2008/06/25 00:12:41 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>
#include "vdbeInt.h"

/*
** The following global variable is incremented every time a cursor
................................................................................
  pOut->u.i = a;
  MemSetTypeFlag(pOut, MEM_Int);
  break;
}

/* Opcode: AddImm  P1 P2 * * *
** 
** Add the constant P2 the value in register P1.
** The result is always an integer.
**
** To force any register to be an integer, just add 0.
*/
case OP_AddImm: {            /* in1 */
  sqlite3VdbeMemIntegerify(pIn1);
  pIn1->u.i += pOp->p2;







|







 







|







39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
....
1424
1425
1426
1427
1428
1429
1430
1431
1432
1433
1434
1435
1436
1437
1438
**
** 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.756 2008/06/26 18:04:03 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>
#include "vdbeInt.h"

/*
** The following global variable is incremented every time a cursor
................................................................................
  pOut->u.i = a;
  MemSetTypeFlag(pOut, MEM_Int);
  break;
}

/* Opcode: AddImm  P1 P2 * * *
** 
** Add the constant P2 to the value in register P1.
** The result is always an integer.
**
** To force any register to be an integer, just add 0.
*/
case OP_AddImm: {            /* in1 */
  sqlite3VdbeMemIntegerify(pIn1);
  pIn1->u.i += pOp->p2;

Changes to src/where.c.

12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
....
1783
1784
1785
1786
1787
1788
1789
1790
1791
1792
1793
1794
1795
1796
1797
** This module contains C code that generates VDBE code used to process
** the WHERE clause of SQL statements.  This module is responsible 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.310 2008/06/25 02:47:57 drh Exp $
*/
#include "sqliteInt.h"

/*
** The number of bits in a Bitmask.  "BMS" means "BitMask Size".
*/
#define BMS  (sizeof(Bitmask)*8)
................................................................................
  }else{
    int eType;
    int iTab;
    struct InLoop *pIn;

    assert( pX->op==TK_IN );
    iReg = iTarget;
    eType = sqlite3FindInIndex(pParse, pX, 1);
    iTab = pX->iTable;
    sqlite3VdbeAddOp2(v, OP_Rewind, iTab, 0);
    VdbeComment((v, "%.*s", pX->span.n, pX->span.z));
    if( pLevel->nIn==0 ){
      pLevel->nxt = sqlite3VdbeMakeLabel(v);
    }
    pLevel->nIn++;







|







 







|







12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
....
1783
1784
1785
1786
1787
1788
1789
1790
1791
1792
1793
1794
1795
1796
1797
** This module contains C code that generates VDBE code used to process
** the WHERE clause of SQL statements.  This module is responsible 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.311 2008/06/26 18:04:03 danielk1977 Exp $
*/
#include "sqliteInt.h"

/*
** The number of bits in a Bitmask.  "BMS" means "BitMask Size".
*/
#define BMS  (sizeof(Bitmask)*8)
................................................................................
  }else{
    int eType;
    int iTab;
    struct InLoop *pIn;

    assert( pX->op==TK_IN );
    iReg = iTarget;
    eType = sqlite3FindInIndex(pParse, pX, 0);
    iTab = pX->iTable;
    sqlite3VdbeAddOp2(v, OP_Rewind, iTab, 0);
    VdbeComment((v, "%.*s", pX->span.n, pX->span.z));
    if( pLevel->nIn==0 ){
      pLevel->nxt = sqlite3VdbeMakeLabel(v);
    }
    pLevel->nIn++;

Changes to test/in.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
425
426
427
428
429
430
431


















































































































































432
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the IN and BETWEEN operator.
#
# $Id: in.test,v 1.20 2008/06/24 12:46:31 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Generate the test data we will need for the first squences of tests.
#
do_test in-1.0 {
................................................................................
  catchsql {
    SELECT * FROM t2 WHERE a IN (
      SELECT a FROM t3 INTERSECT SELECT a, b FROM t2
    );
  }
} {1 {SELECTs to the left and right of INTERSECT do not have the same number of result columns}}



















































































































































finish_test







|







 







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

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the IN and BETWEEN operator.
#
# $Id: in.test,v 1.21 2008/06/26 18:04:03 danielk1977 Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Generate the test data we will need for the first squences of tests.
#
do_test in-1.0 {
................................................................................
  catchsql {
    SELECT * FROM t2 WHERE a IN (
      SELECT a FROM t3 INTERSECT SELECT a, b FROM t2
    );
  }
} {1 {SELECTs to the left and right of INTERSECT do not have the same number of result columns}}


#------------------------------------------------------------------------
# The following tests check that NULL is handled correctly when it 
# appears as part of a set of values on the right-hand side of an
# IN or NOT IN operator.
#
# When it appears in such a set, NULL is handled as an "unknown value".
# If, because of the unknown value in the set, the result of the expression 
# cannot be determined, then it itself evaluates to NULL.
#

# Warm body test to demonstrate the principles being tested:
#
do_test in-13.1 {
  db nullvalue "null"
  execsql { SELECT 
    1 IN (NULL, 1, 2),     -- The value 1 is a member of the set, return true.
    3 IN (NULL, 1, 2),     -- Ambiguous, return NULL.
    1 NOT IN (NULL, 1, 2), -- The value 1 is a member of the set, return false.
    3 NOT IN (NULL, 1, 2)  -- Ambiguous, return NULL.
  }
} {1 null 0 null}

do_test in-13.2 {
  execsql { 
    CREATE TABLE t7(a, b, c NOT NULL);
    INSERT INTO t7 VALUES(1,    1, 1);
    INSERT INTO t7 VALUES(2,    2, 2);
    INSERT INTO t7 VALUES(3,    3, 3);
    INSERT INTO t7 VALUES(NULL, 4, 4);
    INSERT INTO t7 VALUES(NULL, 5, 5);
  }
} {}

do_test in-13.3 {
  execsql { SELECT 2 IN (SELECT a FROM t7) }
} {1}
do_test in-13.4 {
  execsql { SELECT 6 IN (SELECT a FROM t7) }
} {null}

do_test in-13.5 {
  execsql { SELECT 2 IN (SELECT b FROM t7) }
} {1}
do_test in-13.6 {
  execsql { SELECT 6 IN (SELECT b FROM t7) }
} {0}

do_test in-13.7 {
  execsql { SELECT 2 IN (SELECT c FROM t7) }
} {1}
do_test in-13.8 {
  execsql { SELECT 6 IN (SELECT c FROM t7) }
} {0}

do_test in-13.9 {
  execsql {
    SELECT
      2 NOT IN (SELECT a FROM t7),
      6 NOT IN (SELECT a FROM t7),
      2 NOT IN (SELECT b FROM t7),
      6 NOT IN (SELECT b FROM t7),
      2 NOT IN (SELECT c FROM t7),
      6 NOT IN (SELECT c FROM t7)
  } 
} {0 null 0 1 0 1}

do_test in-13.10 {
  execsql { 
    SELECT b IN (
      SELECT inside.a 
      FROM t7 AS inside 
      WHERE inside.b BETWEEN outside.b+1 AND outside.b+2
    )
    FROM t7 AS outside ORDER BY b;
  }
} {0 null null null 0}

do_test in-13.11 {
  execsql {
    SELECT b NOT IN (
      SELECT inside.a 
      FROM t7 AS inside 
      WHERE inside.b BETWEEN outside.b+1 AND outside.b+2
    )
    FROM t7 AS outside ORDER BY b;
  }
} {1 null null null 1}

do_test in-13.12 {
  execsql {
    CREATE INDEX i1 ON t7(a);
    CREATE INDEX i2 ON t7(b);
    CREATE INDEX i3 ON t7(c);
  }
  execsql {
    SELECT
      2 IN (SELECT a FROM t7),
      6 IN (SELECT a FROM t7),
      2 IN (SELECT b FROM t7),
      6 IN (SELECT b FROM t7),
      2 IN (SELECT c FROM t7),
      6 IN (SELECT c FROM t7)
  } 
} {1 null 1 0 1 0}

do_test in-13.13 {
  execsql {
    SELECT
      2 NOT IN (SELECT a FROM t7),
      6 NOT IN (SELECT a FROM t7),
      2 NOT IN (SELECT b FROM t7),
      6 NOT IN (SELECT b FROM t7),
      2 NOT IN (SELECT c FROM t7),
      6 NOT IN (SELECT c FROM t7)
  } 
} {0 null 0 1 0 1}

do_test in-13.14 {
  execsql {
    BEGIN TRANSACTION;
    CREATE TABLE a(id INTEGER);
    INSERT INTO a VALUES(1);
    INSERT INTO a VALUES(2);
    INSERT INTO a VALUES(3);
    CREATE TABLE b(id INTEGER);
    INSERT INTO b VALUES(NULL);
    INSERT INTO b VALUES(3);
    INSERT INTO b VALUES(4);
    INSERT INTO b VALUES(5);
    COMMIT;
    SELECT * FROM a WHERE id NOT IN (SELECT id FROM b);
  }
} {}
do_test in-13.14 {
  execsql {
    CREATE INDEX i5 ON b(id);
    SELECT * FROM a WHERE id NOT IN (SELECT id FROM b);
  }
} {}


do_test in-13.X {
  db nullvalue ""
} {}

finish_test

Changes to test/incrblob2.test.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
348
349
350
351
352
353
354
355







356
#    May you share freely, never taking more than you give.
#
#***********************************************************************
#
# Test that it is possible to have two open blob handles on a single
# blob object.
#
# $Id: incrblob2.test,v 1.6 2008/06/25 14:31:53 drh Exp $
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl

ifcapable {!autovacuum || !pragma || !incrblob} {
  finish_test
................................................................................
do_test incrblob2-7.4 {
  expr {[sqlite3_memory_highwater]<(5 * 1024 * 1024)}
} {1}

do_test incrblob2-7.5 {
  close $h
} {}








finish_test







|







 








>
>
>
>
>
>
>

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
#    May you share freely, never taking more than you give.
#
#***********************************************************************
#
# Test that it is possible to have two open blob handles on a single
# blob object.
#
# $Id: incrblob2.test,v 1.7 2008/06/26 18:04:03 danielk1977 Exp $
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl

ifcapable {!autovacuum || !pragma || !incrblob} {
  finish_test
................................................................................
do_test incrblob2-7.4 {
  expr {[sqlite3_memory_highwater]<(5 * 1024 * 1024)}
} {1}

do_test incrblob2-7.5 {
  close $h
} {}

#do_test incrblob2-8.5 {
  #execsql BEGIN
  #db eval {SELECT * FROM t2} {
  #execsql "DROP TABLE t2"
  #}
#} {}

finish_test