SQLite4
Check-in [856ed13e06]
Not logged in

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

Overview
Comment:Fixes to sqlite4FindInIndex so as to handle WHERE clauses of the form "x IN (SELECT a FROM tbl)" where there are usable indexes on both "a" and "x".
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 856ed13e069303457de5303280cb3e57afd1a69f
User & Date: dan 2013-07-25 12:12:30
Context
2013-07-25
14:18
Update test file eval.test to account for caching in the RowDecoder. check-in: 3bbe9e80b8 user: dan tags: trunk
12:12
Fixes to sqlite4FindInIndex so as to handle WHERE clauses of the form "x IN (SELECT a FROM tbl)" where there are usable indexes on both "a" and "x". check-in: 856ed13e06 user: dan tags: trunk
02:52
Update comments on OP_MakeRecord. No changes to code. check-in: 70d85ff051 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/build.c.

2232
2233
2234
2235
2236
2237
2238

























2239
2240
2241
2242
2243
2244
2245
....
2290
2291
2292
2293
2294
2295
2296
2297
2298
2299
2300
2301
2302
2303
2304
2305
2306
2307




2308

2309
2310
2311
2312
2313
2314
2315
2316
2317
2318
2319
2320
  Table *pTab;
  FKey *pFKey;
  if( (pTab = pParse->pNewTable)==0 || (pFKey = pTab->pFKey)==0 ) return;
  assert( isDeferred==0 || isDeferred==1 ); /* EV: R-30323-21917 */
  pFKey->isDeferred = (u8)isDeferred;
#endif
}


























/*
** Generate code that will erase and refill index *pIdx.  This is
** used to initialize a newly created index or to recompute the
** content of an index in response to a REINDEX command.
*/
static void sqlite4RefillIndex(Parse *pParse, Index *pIdx, int bCreate){
................................................................................

    sqlite4VdbeAddOp2(v, OP_RowKey, iTab, regKey);
    for(i=0; i<pTab->nCol; i++){
      sqlite4VdbeAddOp3(v, OP_Column, iTab, i, regData+i);
    }
    sqlite4Fts5CodeUpdate(pParse, pIdx, pParse->iNewidxReg, regKey, regData, 0);
  }else{
    sqlite4GetTempRange(pParse,2);
    regKey = sqlite4GetTempReg(pParse);
    sqlite4EncodeIndexKey(pParse, pPk, iTab, pIdx, iIdx, 0, regKey);
    if( pIdx->onError!=OE_None ){
      const char *zErr = "indexed columns are not unique";
      int addrTest;

      addrTest = sqlite4VdbeAddOp4Int(v, OP_IsUnique, iIdx, 0, regKey, 0);
      sqlite4HaltConstraint(pParse, OE_Abort, (char *)zErr, P4_STATIC);
      sqlite4VdbeJumpHere(v, addrTest);
    }




    sqlite4VdbeAddOp3(v, OP_IdxInsert, iIdx, 0, regKey);  

  }

  sqlite4VdbeAddOp2(v, OP_Next, iTab, addr1+1);
  sqlite4VdbeJumpHere(v, addr1);
  sqlite4ReleaseTempReg(pParse, regKey);

  sqlite4VdbeAddOp1(v, OP_Close, iTab);
  sqlite4VdbeAddOp1(v, OP_Close, iIdx);
}

/*
** The CreateIndex structure indicated by the first argument contains the







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







 







|
|









>
>
>
>
|
>




<







2232
2233
2234
2235
2236
2237
2238
2239
2240
2241
2242
2243
2244
2245
2246
2247
2248
2249
2250
2251
2252
2253
2254
2255
2256
2257
2258
2259
2260
2261
2262
2263
2264
2265
2266
2267
2268
2269
2270
....
2315
2316
2317
2318
2319
2320
2321
2322
2323
2324
2325
2326
2327
2328
2329
2330
2331
2332
2333
2334
2335
2336
2337
2338
2339
2340
2341
2342

2343
2344
2345
2346
2347
2348
2349
  Table *pTab;
  FKey *pFKey;
  if( (pTab = pParse->pNewTable)==0 || (pFKey = pTab->pFKey)==0 ) return;
  assert( isDeferred==0 || isDeferred==1 ); /* EV: R-30323-21917 */
  pFKey->isDeferred = (u8)isDeferred;
#endif
}

/*
** Cursor iPkCsr is open on a primary key index. This function generates
** code that creates the corresponding covering index record required
** by pIdx by reading values from this cursor. The record is stored in
** register regOut.
*/
static void encodeCoveringRecord(
  Parse *pParse,                  /* Parse context */
  int iPkCsr,                     /* Cursor open on primary key */
  Index *pIdx,                    /* Index to create record for */
  int regOut                      /* Register to write record to */
){
  Vdbe *v = pParse->pVdbe;        /* Generate code into this virtual machine */
  Table *pTab = pIdx->pTable;     /* The table that is indexed */
  int i;                          /* Used to iterate through columns */
  int reg;                        /* Array of pIdx->nCover temp registers */

  reg = sqlite4GetTempRange(pParse, pIdx->nCover);
  for(i=0; i<pIdx->nCover; i++){
    sqlite4ExprCodeGetColumnOfTable(v, pTab, iPkCsr, pIdx->aiCover[i], reg+i);
  }
  sqlite4VdbeAddOp3(v, OP_MakeRecord, reg, pIdx->nCover, regOut);
  sqlite4ReleaseTempRange(pParse, reg, pIdx->nCover);
}

/*
** Generate code that will erase and refill index *pIdx.  This is
** used to initialize a newly created index or to recompute the
** content of an index in response to a REINDEX command.
*/
static void sqlite4RefillIndex(Parse *pParse, Index *pIdx, int bCreate){
................................................................................

    sqlite4VdbeAddOp2(v, OP_RowKey, iTab, regKey);
    for(i=0; i<pTab->nCol; i++){
      sqlite4VdbeAddOp3(v, OP_Column, iTab, i, regData+i);
    }
    sqlite4Fts5CodeUpdate(pParse, pIdx, pParse->iNewidxReg, regKey, regData, 0);
  }else{
    int regData = 0;
    regKey = sqlite4GetTempRange(pParse, 2);
    sqlite4EncodeIndexKey(pParse, pPk, iTab, pIdx, iIdx, 0, regKey);
    if( pIdx->onError!=OE_None ){
      const char *zErr = "indexed columns are not unique";
      int addrTest;

      addrTest = sqlite4VdbeAddOp4Int(v, OP_IsUnique, iIdx, 0, regKey, 0);
      sqlite4HaltConstraint(pParse, OE_Abort, (char *)zErr, P4_STATIC);
      sqlite4VdbeJumpHere(v, addrTest);
    }
    if( pIdx->nCover>0 ){
      regData = regKey+1;
      encodeCoveringRecord(pParse, iTab, pIdx, regData);
    }
    sqlite4VdbeAddOp3(v, OP_IdxInsert, iIdx, regData, regKey);  
    sqlite4ReleaseTempRange(pParse, regKey, 2);
  }

  sqlite4VdbeAddOp2(v, OP_Next, iTab, addr1+1);
  sqlite4VdbeJumpHere(v, addr1);


  sqlite4VdbeAddOp1(v, OP_Close, iTab);
  sqlite4VdbeAddOp1(v, OP_Close, iIdx);
}

/*
** The CreateIndex structure indicated by the first argument contains the

Changes to src/expr.c.

1407
1408
1409
1410
1411
1412
1413
1414
1415
1416
1417
1418
1419
1420
1421
1422
1423
1424
1425
1426
1427
1428
1429
1430
1431
1432
1433
1434
1435
1436
1437
1438
1439
1440
1441
1442
1443
1444
1445
1446
1447
1448
1449
1450
1451
1452
1453
1454
1455
1456
1457
1458
1459
1460
1461
1462
1463
1464
1465
1466
1467
1468
1469
1470
1471
1472
1473
1474
1475
1476
1477
1478
1479
1480
1481
1482
1483
1484
1485
1486
1487
1488
1489
1490
1491
1492
1493
1494
1495
1496
1497
1498
1499
1500
1501
1502
1503
1504
1505
1506
1507
1508
1509
1510
1511
1512
1513
1514
1515
1516
1517
1518
1519
1520
1521
....
1572
1573
1574
1575
1576
1577
1578
1579
1580
1581
1582
1583
1584
1585
1586






1587
1588
1589
1590
1591
1592
1593
1594
1595
1596
1597
1598




1599
1600
1601
1602
1603
1604
1605
1606
1607
1608
1609
1610
1611
1612
1613
1614
1615
1616
1617
1618
1619
1620
1621
1622
1623
1624
1625
1626
1627
1628
1629
1630
1631
1632
1633
1634
1635
1636
1637
1638
1639
1640

1641
1642
1643
1644
1645
1646
1647
1648
1649













1650
1651
1652



1653
1654

1655
1656
1657
1658
1659
1660
1661
1662
1663
1664
1665
1666
....
1671
1672
1673
1674
1675
1676
1677

1678
1679
1680
1681
1682
1683
1684
....
1954
1955
1956
1957
1958
1959
1960
1961
1962
1963
1964
1965
1966
1967
1968
    }
  }

  return pIdx;
}
#endif /* SQLITE4_OMIT_SUBQUERY */

/*
** This function is used by the implementation of the IN (...) operator.
** It's job is to find or create a b-tree structure that may be used
** either to test for membership of the (...) set or to iterate through
** its members, skipping duplicates.
**
** The index of the cursor opened on the b-tree (database table, database index 
** or ephermal table) is stored in pX->iTable before this function returns.
** The returned value of this function indicates the b-tree type, as follows:
**
**   IN_INDEX_ROWID - The cursor was opened on a database table.
**   IN_INDEX_INDEX - The cursor was opened on a database index.
**   IN_INDEX_EPH -   The cursor was opened on a specially created and
**                    populated epheremal table.
**
** An existing b-tree may only be used if the SELECT is of the simple
** form:
**
**     SELECT <column> FROM <table>
**
** If the prNotFound parameter is 0, then the b-tree 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
** has a UNIQUE constraint or UNIQUE index.
**
** If the prNotFound parameter is not 0, then the b-tree 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 b-tree is being used for membership tests, the calling function
** 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 any chance that the (...) might 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 (...) 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 (...) does not remain constant
** for the duration of the query (i.e. the SELECT within the (...)
** is a correlated subquery) then the value of the allocated register is
** reset to NULL each time the subquery is rerun. 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 SQLITE4_OMIT_SUBQUERY
#if 0
int sqlite4FindInIndex(Parse *pParse, Expr *pX, int *prNotFound){
  Index *pIdx;
  int eType = 0;                        /* Type of RHS table. IN_INDEX_* */
  int iTab = pParse->nTab++;            /* Cursor of the RHS table */
  Vdbe *v = sqlite4GetVdbe(pParse);     /* Virtual machine being coded */

  assert( pX->op==TK_IN );
  assert( prNotFound );

  /* Check to see if an existing table or index can be used to
  ** satisfy the query. This is preferable to generating a new 
  ** ephemeral table.  */
  pIdx = sqlite4FindExistingInIndex(pParse, pX, 0);
  if( pIdx ){
    int iAddr;
    char *pKey;
    int iDb;                      /* aDb[] Index of database containing pIdx */

    iDb = sqlite4SchemaToIndex(pParse->db, pIdx->pSchema);
    pKey = (char *)sqlite4IndexKeyinfo(pParse, pIdx);
    iAddr = sqlite4CodeOnce(pParse);
    sqlite4VdbeAddOp3(v, OP_OpenRead, iTab, pIdx->tnum, iDb);
    sqlite4VdbeChangeP4(v, -1 , pKey, P4_KEYINFO_HANDOFF);
    VdbeComment((v, "%s", pIdx->zName));
    sqlite4VdbeJumpHere(v, iAddr);

    *prNotFound = ++pParse->nMem;
    sqlite4VdbeAddOp2(v, OP_Null, 0, *prNotFound);
    pX->iTable = iTab;
  }else{
    /* Could not find an existing table or index to use as the RHS b-tree.
    ** We will have to generate an ephemeral table to do the job.  */
    double savedNQueryLoop = pParse->nQueryLoop;
    int rMayHaveNull = 0;
    eType = IN_INDEX_EPH;
    *prNotFound = rMayHaveNull = ++pParse->nMem;
    sqlite4VdbeAddOp2(v, OP_Null, 0, *prNotFound);
    sqlite4CodeSubselect(pParse, pX, rMayHaveNull, eType==IN_INDEX_ROWID);
    pParse->nQueryLoop = savedNQueryLoop;
  }
  
  return eType;
}
#endif

/*
** This function is used by the implementation of the IN (...) operator.
** The pX parameter is the expression on the RHS of the IN operator, which
** might be either a list of expressions or a subquery.
**
** The job of this routine is to find or create a b-tree object that can
** be used either to test for membership in the RHS set or to iterate through
................................................................................
**     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.
*/
int sqlite4FindInIndex(Parse *pParse, Expr *pX, int *prNotFound){
  Select *p;                            /* SELECT to the right of IN operator */
  int eType = 0;                        /* Type of RHS table. IN_INDEX_* */
  int iTab = pParse->nTab++;            /* Cursor of the RHS table */
  int mustBeUnique = (prNotFound==0);   /* True if RHS must be unique */
  Vdbe *v = sqlite4GetVdbe(pParse);     /* Virtual machine being coded */

  assert( pX->op==TK_IN );







  /* Check to see if an existing table or index can be used to
  ** satisfy the query.  This is preferable to generating a new 
  ** ephemeral table.
  */
  p = (ExprHasProperty(pX, EP_xIsSelect) ? pX->x.pSelect : 0);
  if( ALWAYS(pParse->nErr==0) && isCandidateForInOpt(p) && prNotFound ){
    sqlite4 *db = pParse->db;              /* Database connection */
    Table *pTab;                           /* Table <table>. */
    Expr *pExpr;                           /* Expression <column> */
    int iCol;                              /* Index of column <column> */
    int iDb;                               /* Database idx for pTab */





    assert( p );                        /* Because of isCandidateForInOpt(p) */
    assert( p->pEList!=0 );             /* Because of isCandidateForInOpt(p) */
    assert( p->pEList->a[0].pExpr!=0 ); /* Because of isCandidateForInOpt(p) */
    assert( p->pSrc!=0 );               /* Because of isCandidateForInOpt(p) */
    pTab = p->pSrc->a[0].pTab;
    pExpr = p->pEList->a[0].pExpr;
    iCol = pExpr->iColumn;
   
    /* Code an OP_VerifyCookie for <table>. */
    iDb = sqlite4SchemaToIndex(db, pTab->pSchema);
    sqlite4CodeVerifySchema(pParse, iDb);

    /* This function is only called from two places. In both cases the vdbe
    ** has already been allocated. So assume sqlite4GetVdbe() is always
    ** successful here.
    */
    assert(v);
#if 0
    if( iCol<0 ){
      int iAddr;

      iAddr = sqlite4CodeOnce(pParse);

      sqlite4OpenTable(pParse, iTab, iDb, pTab, OP_OpenRead);
      eType = IN_INDEX_ROWID;

      sqlite4VdbeJumpHere(v, iAddr);
    }else
#endif
    {
      Index *pIdx;                         /* Iterator variable */

      /* The collation sequence used by the comparison. If an index is to
      ** be used in place of a temp-table, it must be ordered according
      ** to this collation sequence.  */
      CollSeq *pReq = sqlite4BinaryCompareCollSeq(pParse, pX->pLeft, pExpr);

      /* Check that the affinity that will be used to perform the 
      ** comparison is the same as the affinity of the column. If
      ** it is not, it is not possible to use any index.
      */

      int affinity_ok = sqlite4IndexAffinityOk(pX, pTab->aCol[iCol].affinity);

      for(pIdx=pTab->pIndex; pIdx && eType==0 && affinity_ok; pIdx=pIdx->pNext){
        if( (pIdx->aiColumn[0]==iCol)
         && sqlite4FindCollSeq(db, pIdx->azColl[0], 0)==pReq
         && (!mustBeUnique || (pIdx->nColumn==1 && pIdx->onError!=OE_None))
        ){
          int iAddr;
  













          iAddr = sqlite4CodeOnce(pParse);
          sqlite4OpenIndex(pParse, iTab, iDb, pIdx, OP_OpenRead);
          assert( IN_INDEX_INDEX_DESC == IN_INDEX_INDEX_ASC+1 );



          eType = IN_INDEX_INDEX_ASC + pIdx->aSortOrder[0];


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

  if( eType==0 ){
    /* Could not found an existing table or index to use as the RHS b-tree.
................................................................................
    eType = IN_INDEX_EPH;
    if( prNotFound ){
      *prNotFound = rMayHaveNull = ++pParse->nMem;
      sqlite4VdbeAddOp2(v, OP_Null, 0, *prNotFound);
    }else{
      testcase( pParse->nQueryLoop>0 );
      pParse->nQueryLoop = 0;

    }
    sqlite4CodeSubselect(pParse, pX, rMayHaveNull, eType==IN_INDEX_ROWID);
    pParse->nQueryLoop = savedNQueryLoop;
  }else{
    pX->iTable = iTab;
  }
  return eType;
................................................................................

  /* Compute the RHS.   After this step, the table with cursor
  ** pExpr->iTable will contains the values that make up the RHS.
  */
  v = pParse->pVdbe;
  assert( v!=0 );       /* OOM detected prior to this routine */
  VdbeNoopComment((v, "begin IN expr"));
  eType = sqlite4FindInIndex(pParse, pExpr, &rRhsHasNull);

  /* 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);








<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<

<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<







 







|







>
>
>
>
>
>






|
|
|
|
|
|
>
>
>
>













<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
|
|
|
|

|
|
|
<
>
|

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

|
>
|
|
|
|
<







 







>







 







|







1407
1408
1409
1410
1411
1412
1413






















































1414














































1415
1416
1417
1418
1419
1420
1421
....
1472
1473
1474
1475
1476
1477
1478
1479
1480
1481
1482
1483
1484
1485
1486
1487
1488
1489
1490
1491
1492
1493
1494
1495
1496
1497
1498
1499
1500
1501
1502
1503
1504
1505
1506
1507
1508
1509
1510
1511
1512
1513
1514
1515
1516
1517
1518
1519
1520
1521




















1522
1523
1524
1525
1526
1527
1528
1529

1530
1531
1532
1533
1534
1535
1536
1537
1538
1539
1540
1541
1542
1543
1544
1545
1546
1547
1548
1549
1550
1551
1552
1553
1554
1555
1556
1557
1558
1559
1560
1561
1562
1563
1564
1565

1566
1567
1568
1569
1570
1571
1572
....
1577
1578
1579
1580
1581
1582
1583
1584
1585
1586
1587
1588
1589
1590
1591
....
1861
1862
1863
1864
1865
1866
1867
1868
1869
1870
1871
1872
1873
1874
1875
    }
  }

  return pIdx;
}
#endif /* SQLITE4_OMIT_SUBQUERY */























































#ifndef SQLITE4_OMIT_SUBQUERY














































/*
** This function is used by the implementation of the IN (...) operator.
** The pX parameter is the expression on the RHS of the IN operator, which
** might be either a list of expressions or a subquery.
**
** The job of this routine is to find or create a b-tree object that can
** be used either to test for membership in the RHS set or to iterate through
................................................................................
**     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.
*/
int sqlite4FindInIndex(Parse *pParse, Expr *pX, int *prNotFound, int *piCov){
  Select *p;                            /* SELECT to the right of IN operator */
  int eType = 0;                        /* Type of RHS table. IN_INDEX_* */
  int iTab = pParse->nTab++;            /* Cursor of the RHS table */
  int mustBeUnique = (prNotFound==0);   /* True if RHS must be unique */
  Vdbe *v = sqlite4GetVdbe(pParse);     /* Virtual machine being coded */

  assert( pX->op==TK_IN );
  assert( mustBeUnique==(piCov!=0) );

  /* This function is only called from two places. In both cases the vdbe
  ** has already been allocated. So assume sqlite4GetVdbe() is always
  ** successful here.  */
  assert(v);

  /* Check to see if an existing table or index can be used to
  ** satisfy the query.  This is preferable to generating a new 
  ** ephemeral table.
  */
  p = (ExprHasProperty(pX, EP_xIsSelect) ? pX->x.pSelect : 0);
  if( ALWAYS(pParse->nErr==0) && isCandidateForInOpt(p) ){
    sqlite4 *db = pParse->db;     /* Database connection */
    Table *pTab;                  /* Table <table>. */
    Expr *pExpr;                  /* Expression <column> */
    int iCol;                     /* Index of column <column> */
    int iDb;                      /* Database idx for pTab */
    Index *pIdx;                  /* Used to iterate through indexes on pTab */
    CollSeq *pReq;                /* Collation sequence for comparison */
    char affinity;                /* Affinity of selected column */
    int affinity_ok;              /* True if the affinity matches */

    assert( p );                        /* Because of isCandidateForInOpt(p) */
    assert( p->pEList!=0 );             /* Because of isCandidateForInOpt(p) */
    assert( p->pEList->a[0].pExpr!=0 ); /* Because of isCandidateForInOpt(p) */
    assert( p->pSrc!=0 );               /* Because of isCandidateForInOpt(p) */
    pTab = p->pSrc->a[0].pTab;
    pExpr = p->pEList->a[0].pExpr;
    iCol = pExpr->iColumn;
   
    /* Code an OP_VerifyCookie for <table>. */
    iDb = sqlite4SchemaToIndex(db, pTab->pSchema);
    sqlite4CodeVerifySchema(pParse, iDb);





















    /* The collation sequence used by the comparison. If an index is to
    ** be used in place of a temp-table, it must be ordered according
    ** to this collation sequence.  */
    pReq = sqlite4BinaryCompareCollSeq(pParse, pX->pLeft, pExpr);

    /* Check that the affinity that will be used to perform the 
    ** comparison is the same as the affinity of the column. If
    ** it is not, it is not possible to use any index.  */

    affinity = (iCol<0?SQLITE4_AFF_NUMERIC:pTab->aCol[iCol].affinity);
    affinity_ok = sqlite4IndexAffinityOk(pX, affinity);

    for(pIdx=pTab->pIndex; pIdx && eType==0 && affinity_ok; pIdx=pIdx->pNext){
      if( (pIdx->aiColumn[0]==iCol)
       && (iCol<0 || sqlite4FindCollSeq(db, pIdx->azColl[0], 0)==pReq)
       && (!mustBeUnique || (pIdx->nColumn==1 && pIdx->onError!=OE_None))
      ){
        int iAddr;

        if( mustBeUnique ){
          if( pIdx->eIndexType!=SQLITE4_INDEX_PRIMARYKEY ){
            int i;
            for(i=0; i<pIdx->nCover; i++){
              if( pIdx->aiCover[i]==iCol ) break;
            }
            if( i==pIdx->nCover ) continue;
            *piCov = i;
          }else{
            *piCov = iCol;
          }
        }

        iAddr = sqlite4CodeOnce(pParse);
        sqlite4OpenIndex(pParse, iTab, iDb, pIdx, OP_OpenRead);
        assert( IN_INDEX_INDEX_DESC == IN_INDEX_INDEX_ASC+1 );
        if( iCol<0 ){
          eType = IN_INDEX_ROWID;
        }else{
          eType = IN_INDEX_INDEX_ASC + pIdx->aSortOrder[0];
        }

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

        }
      }
    }
  }

  if( eType==0 ){
    /* Could not found an existing table or index to use as the RHS b-tree.
................................................................................
    eType = IN_INDEX_EPH;
    if( prNotFound ){
      *prNotFound = rMayHaveNull = ++pParse->nMem;
      sqlite4VdbeAddOp2(v, OP_Null, 0, *prNotFound);
    }else{
      testcase( pParse->nQueryLoop>0 );
      pParse->nQueryLoop = 0;
      *piCov = 0;
    }
    sqlite4CodeSubselect(pParse, pX, rMayHaveNull, eType==IN_INDEX_ROWID);
    pParse->nQueryLoop = savedNQueryLoop;
  }else{
    pX->iTable = iTab;
  }
  return eType;
................................................................................

  /* Compute the RHS.   After this step, the table with cursor
  ** pExpr->iTable will contains the values that make up the RHS.
  */
  v = pParse->pVdbe;
  assert( v!=0 );       /* OOM detected prior to this routine */
  VdbeNoopComment((v, "begin IN expr"));
  eType = sqlite4FindInIndex(pParse, pExpr, &rRhsHasNull, 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);

Changes to src/sqliteInt.h.

3131
3132
3133
3134
3135
3136
3137
3138
3139
3140
3141
3142
3143
3144
3145
  #define sqlite4EndBenignMalloc(X)
#endif

#define IN_INDEX_ROWID           1
#define IN_INDEX_EPH             2
#define IN_INDEX_INDEX_ASC       3
#define IN_INDEX_INDEX_DESC      4
int sqlite4FindInIndex(Parse *, Expr *, int*);
Index *sqlite4FindExistingInIndex(Parse *, Expr *, int);


#if SQLITE4_MAX_EXPR_DEPTH>0
  void sqlite4ExprSetHeight(Parse *pParse, Expr *p);
  int sqlite4SelectExprHeight(Select *);
  int sqlite4ExprCheckHeight(Parse*, int);







|







3131
3132
3133
3134
3135
3136
3137
3138
3139
3140
3141
3142
3143
3144
3145
  #define sqlite4EndBenignMalloc(X)
#endif

#define IN_INDEX_ROWID           1
#define IN_INDEX_EPH             2
#define IN_INDEX_INDEX_ASC       3
#define IN_INDEX_INDEX_DESC      4
int sqlite4FindInIndex(Parse *, Expr *, int*, int*);
Index *sqlite4FindExistingInIndex(Parse *, Expr *, int);


#if SQLITE4_MAX_EXPR_DEPTH>0
  void sqlite4ExprSetHeight(Parse *pParse, Expr *p);
  int sqlite4SelectExprHeight(Select *);
  int sqlite4ExprCheckHeight(Parse*, int);

Changes to src/where.c.

2920
2921
2922
2923
2924
2925
2926

2927
2928
2929
2930
2931
2932
2933
2934
2935
2936
2937
2938
2939
2940
2941
2942
2943
2944
2945
2946
2947
....
2956
2957
2958
2959
2960
2961
2962
2963
2964
2965
2966
2967
2968
2969
2970
  }else if( pX->op==TK_ISNULL ){
    iReg = iTarget;
    sqlite4VdbeAddOp2(v, OP_Null, 0, iReg);
#ifndef SQLITE4_OMIT_SUBQUERY
  }else{
    int eType;
    int iTab;

    struct InLoop *pIn;
    WhereLoop *pLoop = pLevel->pWLoop;

    if( (pLoop->wsFlags & WHERE_VIRTUALTABLE)==0
      && pLoop->u.btree.pIndex!=0
      && pLoop->u.btree.pIndex->aSortOrder[iEq]
    ){
      testcase( iEq==0 );
      testcase( bRev );
      bRev = !bRev;
    }
    assert( pX->op==TK_IN );
    iReg = iTarget;
    eType = sqlite4FindInIndex(pParse, pX, 0);
    if( eType==IN_INDEX_INDEX_DESC ){
      testcase( bRev );
      bRev = !bRev;
    }
    iTab = pX->iTable;
    sqlite4VdbeAddOp2(v, bRev ? OP_Last : OP_Rewind, iTab, 0);
    assert( (pLoop->wsFlags & WHERE_MULTI_OR)==0 );
................................................................................
    pIn = pLevel->u.in.aInLoop;
    if( pIn ){
      pIn += pLevel->u.in.nIn - 1;
      pIn->iCur = iTab;
      if( eType==IN_INDEX_ROWID ){
        pIn->addrInTop = sqlite4VdbeAddOp2(v, OP_Rowid, iTab, iReg);
      }else{
        pIn->addrInTop = sqlite4VdbeAddOp3(v, OP_Column, iTab, 0, iReg);
      }
      pIn->eEndLoopOp = bRev ? OP_Prev : OP_Next;
      sqlite4VdbeAddOp1(v, OP_IsNull, iReg);
    }else{
      pLevel->u.in.nIn = 0;
    }
#endif







>













|







 







|







2920
2921
2922
2923
2924
2925
2926
2927
2928
2929
2930
2931
2932
2933
2934
2935
2936
2937
2938
2939
2940
2941
2942
2943
2944
2945
2946
2947
2948
....
2957
2958
2959
2960
2961
2962
2963
2964
2965
2966
2967
2968
2969
2970
2971
  }else if( pX->op==TK_ISNULL ){
    iReg = iTarget;
    sqlite4VdbeAddOp2(v, OP_Null, 0, iReg);
#ifndef SQLITE4_OMIT_SUBQUERY
  }else{
    int eType;
    int iTab;
    int iCov;
    struct InLoop *pIn;
    WhereLoop *pLoop = pLevel->pWLoop;

    if( (pLoop->wsFlags & WHERE_VIRTUALTABLE)==0
      && pLoop->u.btree.pIndex!=0
      && pLoop->u.btree.pIndex->aSortOrder[iEq]
    ){
      testcase( iEq==0 );
      testcase( bRev );
      bRev = !bRev;
    }
    assert( pX->op==TK_IN );
    iReg = iTarget;
    eType = sqlite4FindInIndex(pParse, pX, 0, &iCov);
    if( eType==IN_INDEX_INDEX_DESC ){
      testcase( bRev );
      bRev = !bRev;
    }
    iTab = pX->iTable;
    sqlite4VdbeAddOp2(v, bRev ? OP_Last : OP_Rewind, iTab, 0);
    assert( (pLoop->wsFlags & WHERE_MULTI_OR)==0 );
................................................................................
    pIn = pLevel->u.in.aInLoop;
    if( pIn ){
      pIn += pLevel->u.in.nIn - 1;
      pIn->iCur = iTab;
      if( eType==IN_INDEX_ROWID ){
        pIn->addrInTop = sqlite4VdbeAddOp2(v, OP_Rowid, iTab, iReg);
      }else{
        pIn->addrInTop = sqlite4VdbeAddOp3(v, OP_Column, iTab, iCov, iReg);
      }
      pIn->eEndLoopOp = bRev ? OP_Prev : OP_Next;
      sqlite4VdbeAddOp1(v, OP_IsNull, iReg);
    }else{
      pLevel->u.in.nIn = 0;
    }
#endif

Changes to test/in3.test.

252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
  exec_neph { SELECT 'TEXT' COLLATE nocase IN (SELECT b FROM t3) }
} {1 1}
do_test in3-4.4 {
  # A temp table must be used because t3_i.b is not guaranteed to be unique.
  exec_neph { SELECT b FROM t3 WHERE b IN (SELECT b FROM t3) }
} {1 none numeric real text}
do_test in3-4.5 {
  execsql { CREATE UNIQUE INDEX t3_i2 ON t3(b) }
  exec_neph { SELECT b FROM t3 WHERE b IN (SELECT b FROM t3) ORDER BY b}
} {0 none numeric real text}
do_test in3-4.6 {
  execsql { DROP INDEX t3_i2 }
} {}

# The following two test cases verify that ticket #2991 has been fixed.







|







252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
  exec_neph { SELECT 'TEXT' COLLATE nocase IN (SELECT b FROM t3) }
} {1 1}
do_test in3-4.4 {
  # A temp table must be used because t3_i.b is not guaranteed to be unique.
  exec_neph { SELECT b FROM t3 WHERE b IN (SELECT b FROM t3) }
} {1 none numeric real text}
do_test in3-4.5 {
  execsql { CREATE UNIQUE INDEX t3_i2 ON t3(b) COVERING (b) }
  exec_neph { SELECT b FROM t3 WHERE b IN (SELECT b FROM t3) ORDER BY b}
} {0 none numeric real text}
do_test in3-4.6 {
  execsql { DROP INDEX t3_i2 }
} {}

# The following two test cases verify that ticket #2991 has been fixed.

Changes to test/simple.test.

1663
1664
1665
1666
1667
1668
1669
1670

























































1671
1672
do_execsql_test 89.1 {
  CREATE TABLE t1(a COLLATE NOCASE);
  CREATE INDEX i1 ON t1(a);
}
do_eqp_test 89.2 {
  SELECT * FROM t1 ORDER BY a;
} {0 0 0 {SCAN TABLE t1 USING INDEX i1}}


























































finish_test









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


1663
1664
1665
1666
1667
1668
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
1723
1724
1725
1726
1727
1728
1729
do_execsql_test 89.1 {
  CREATE TABLE t1(a COLLATE NOCASE);
  CREATE INDEX i1 ON t1(a);
}
do_eqp_test 89.2 {
  SELECT * FROM t1 ORDER BY a;
} {0 0 0 {SCAN TABLE t1 USING INDEX i1}}

#-------------------------------------------------------------------------

proc nEphemeral {sql} {
  set nEph 0
  foreach op [execsql "EXPLAIN $sql"] {
    if {$op eq "OpenEphemeral"} {incr nEph}
  }
  set nEph
}

foreach {tn schema} {
  1 {
    CREATE TABLE t1(a PRIMARY KEY, b);
    CREATE TABLE t2(x PRIMARY KEY, y);
  }
  2 {
    CREATE TABLE t1(b, a PRIMARY KEY);
    CREATE TABLE t2(x PRIMARY KEY, y);
  }
  3 {
    CREATE TABLE t1(b, a);
    CREATE UNIQUE INDEX i1 ON t1(a) COVERING (b, a);
    CREATE TABLE t2(x PRIMARY KEY, y);
  }
  4 {
    CREATE TABLE t1(b, a);
    CREATE UNIQUE INDEX i1 ON t1(a) COVERING (a, b);
    CREATE TABLE t2(y, x PRIMARY KEY);
  }
} {
  reset_db

  do_execsql_test 90.$tn.0 $schema

  do_execsql_test 90.$tn.1 {
    INSERT INTO t2(x, y) VALUES(1, 'one');
    INSERT INTO t2(x, y) VALUES(2, 'two');
    INSERT INTO t2(x, y) VALUES(3, 'three');
    INSERT INTO t2(x, y) VALUES(4, 'four');
    INSERT INTO t2(x, y) VALUES(5, 'five');
    INSERT INTO t2(x, y) VALUES(6, 'six');

    INSERT INTO t1(a, b) VALUES(2, 'two');
    INSERT INTO t1(a, b) VALUES(3, 'three');
    INSERT INTO t1(a, b) VALUES(5, 'five');
  }

  do_execsql_test 90.$tn.2 {
    SELECT y FROM t2 WHERE x IN (SELECT a FROM t1);
  } {two three five}

  do_test 90.$tn.3 {
    nEphemeral "SELECT y FROM t2 WHERE x IN (SELECT a FROM t1)"
  } 0
}


finish_test