/ Check-in [e2fd6f49]
Login

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

Overview
Comment:Allow vector IN(SELECT ...) expressions to use an index if either all the indexed columns are declared NOT NULL or if there is no difference between the expression evaluating to 0 and NULL (as in a WHERE clause).
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | rowvalue
Files: files | file ages | folders
SHA1:e2fd6f49b1b145bec09c581cc982b89429643ae9
User & Date: dan 2016-07-23 20:24:06
Context
2016-07-26
18:06
Fix where.c handling of "IN (SELECT ...)" expressions when the SELECT returns more than one result column. Also error handling for other row value constructor cases. check-in: 061b8006 user: dan tags: rowvalue
2016-07-23
20:24
Allow vector IN(SELECT ...) expressions to use an index if either all the indexed columns are declared NOT NULL or if there is no difference between the expression evaluating to 0 and NULL (as in a WHERE clause). check-in: e2fd6f49 user: dan tags: rowvalue
2016-07-22
17:58
Merge latest trunk changes with this branch. check-in: 60fed5cd user: dan tags: rowvalue
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/expr.c.

319
320
321
322
323
324
325






326
327
328
329
330
331
332
....
1706
1707
1708
1709
1710
1711
1712







1713
1714
1715
1716
1717
1718
1719
....
1866
1867
1868
1869
1870
1871
1872
1873






1874
1875
1876
1877
1878
1879
1880
....
1883
1884
1885
1886
1887
1888
1889

1890
1891
1892
1893
1894
1895
1896
1897
1898
1899
....
1957
1958
1959
1960
1961
1962
1963

1964
1965
1966
1967
1968
1969
1970
....
2019
2020
2021
2022
2023
2024
2025






2026
2027
2028
2029
2030
2031
2032
....
2305
2306
2307
2308
2309
2310
2311
2312
2313
2314
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
2350
2351
2352
2353
2354
2355
2356
2357
2358
2359
2360
2361
2362
2363
2364
2365
2366
2367
2368
2369
2370
2371
2372
2373
2374
2375
2376
2377
2378
2379
2380
2381
2382
2383
2384
2385
2386
2387
....
2399
2400
2401
2402
2403
2404
2405
2406
2407
2408
2409
2410




2411
2412

2413






2414
2415


2416
2417

2418
2419
2420
2421
2422
2423
2424
2425
2426
2427
2428
2429

2430
2431


2432

2433
2434










2435









2436
2437
2438
2439
2440
2441
2442
....
2456
2457
2458
2459
2460
2461
2462
2463
2464
2465
2466
2467
2468
2469
2470
2471
2472
2473
2474
2475
....
2476
2477
2478
2479
2480
2481
2482
2483
2484
2485
2486
2487
2488
2489
2490
....
2495
2496
2497
2498
2499
2500
2501








































2502
2503
2504
2505
2506
2507
2508
2509
2510
2511
2512
....
2515
2516
2517
2518
2519
2520
2521

2522

2523
2524
2525
2526
2527
2528
2529
....
2541
2542
2543
2544
2545
2546
2547

2548
2549
2550
2551
2552
2553
2554
....
4006
4007
4008
4009
4010
4011
4012
4013
4014
4015
4016
4017
4018
4019
4020
....
4043
4044
4045
4046
4047
4048
4049


4050
4051
4052
4053
4054
4055
4056
....
4089
4090
4091
4092
4093
4094
4095

4096
4097
4098
4099
4100
4101
4102
  if( (pExpr->flags & EP_Vector)==0 ) return 1;
  if( pExpr->flags & EP_xIsSelect ){
    return pExpr->x.pSelect->pEList->nExpr;
  }
  return pExpr->x.pList->nExpr;
}







static Expr *exprVectorField(Expr *pVector, int i){
  if( (pVector->flags & EP_Vector)==0 ){
    assert( i==0 );
    return pVector;
  }else if( pVector->flags & EP_xIsSelect ){
    return pVector->x.pSelect->pEList->a[i].pExpr;
  }
................................................................................

/*
** pX is the RHS of an IN operator.  If pX is a SELECT statement 
** that can be simplified to a direct table access, then return
** a pointer to the SELECT statement.  If pX is not a SELECT statement,
** or if the SELECT statement needs to be manifested into a transient
** table, then return NULL.







*/
#ifndef SQLITE_OMIT_SUBQUERY
static Select *isCandidateForInOpt(Expr *pX, int bNullSensitive){
  Select *p;
  SrcList *pSrc;
  ExprList *pEList;
  Table *pTab;
................................................................................
**
** If a register is allocated and its location stored in *prRhsHasNull, then
** the value in that register will be NULL if the b-tree contains one or more
** NULL values, and it will be some non-NULL value if the b-tree contains no
** NULL values.
*/
#ifndef SQLITE_OMIT_SUBQUERY
int sqlite3FindInIndex(Parse *pParse, Expr *pX, u32 inFlags, int *prRhsHasNull){






  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;                     /* True if RHS must be unique */
  Vdbe *v = sqlite3GetVdbe(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.
  */
  if( pParse->nErr==0 && (p = isCandidateForInOpt(pX, prRhsHasNull!=0))!=0 ){
    sqlite3 *db = pParse->db;              /* Database connection */
    Table *pTab;                           /* Table <table>. */

    ExprList *pEList = p->pEList;
    int nExpr = pEList->nExpr;
    i16 iDb;                               /* Database idx for pTab */

    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;

    /* Code an OP_Transaction and OP_TableLock for <table>. */
................................................................................
          for(j=0; j<nExpr; j++){
            if( pIdx->aiColumn[j]!=pRhs->iColumn ) continue;
            assert( pIdx->azColl[j] );
            if( sqlite3StrICmp(pReq->zName, pIdx->azColl[j])!=0 ) continue;
            break;
          }
          if( j==nExpr ) break;

        }

        if( i==nExpr ){
          int iAddr = sqlite3CodeOnce(pParse); VdbeCoverage(v);
          sqlite3VdbeAddOp3(v, OP_OpenRead, iTab, pIdx->tnum, iDb);
          sqlite3VdbeSetP4KeyInfo(pParse, pIdx);
          VdbeComment((v, "%s", pIdx->zName));
................................................................................
      *prRhsHasNull = rMayHaveNull = ++pParse->nMem;
    }
    sqlite3CodeSubselect(pParse, pX, rMayHaveNull, eType==IN_INDEX_ROWID);
    pParse->nQueryLoop = savedNQueryLoop;
  }else{
    pX->iTable = iTab;
  }






  return eType;
}
#endif

static char *exprINAffinity(Parse *pParse, Expr *pExpr){
  Expr *pLeft = pExpr->pLeft;
  int nVal = sqlite3ExprVectorSize(pLeft);
................................................................................
  }
  sqlite3ExprCachePop(pParse);

  return rReg;
}
#endif /* SQLITE_OMIT_SUBQUERY */

#ifndef SQLITE_OMIT_SUBQUERY
void exprCodeVectorIN(
  Parse *pParse,        /* Parsing and code generating context */
  Expr *pExpr,          /* The IN expression */
  int destIfFalse,      /* Jump here if LHS is not contained in the RHS */
  int destIfNull        /* Jump here if the results are unknown due to NULLs */
){
  int i;
  int addrNext;
  int iSkip;
  int r1;
  int r2 = sqlite3GetTempReg(pParse);
  int r3 = sqlite3GetTempReg(pParse);
  int r4 = sqlite3GetTempReg(pParse);
  int regResult = sqlite3GetTempReg(pParse);
  int nVal = sqlite3ExprVectorSize(pExpr->pLeft);

  Expr *pLeft = pExpr->pLeft;
  Vdbe *v = pParse->pVdbe;

  /* Code the LHS, the <expr> from "<expr> IN (...)". Leave the results in
  ** an array of nVal registers starting at r1.  */
  sqlite3ExprCachePush(pParse);
  if( pLeft->flags & EP_xIsSelect ){
    r1 = sqlite3CodeSubselect(pParse, pLeft, 0, 0);
  }else{
    r1 = pParse->nMem + 1;
    pParse->nMem += nVal;
    sqlite3ExprCodeExprList(pParse, pLeft->x.pList, r1, 0, 0);
  }

  /* Generate an epheremal index containing the contents of the SELECT
  ** to the right of the "<expr> IN (SELECT ...)" expression. The cursor
  ** number for the epheremal table is left in pExpr->iTable.  */
  assert( pExpr->flags & EP_xIsSelect );
  sqlite3CodeSubselect(pParse, pExpr, 0, 0);

  sqlite3VdbeAddOp2(v, OP_Integer, 0, regResult);

  /* Iterate through the ephemeral table just populated */
  addrNext = 1 + sqlite3VdbeAddOp2(v, OP_Rewind, pExpr->iTable, destIfFalse);
  for(i=0; i<nVal; i++){
    Expr *p;
    CollSeq *pColl;
    p = exprVectorField(pLeft, i);
    pColl = sqlite3ExprCollSeq(pParse, p);
    sqlite3VdbeAddOp3(v, OP_Column, pExpr->iTable, i, r2);
    sqlite3VdbeAddOp4(v, OP_Eq, r1+i, i==0?r3:r4, r2, (void*)pColl,P4_COLLSEQ); 
    sqlite3VdbeChangeP5(v, SQLITE_STOREP2);
    VdbeCoverage(v);
    if( i!=0 ){
      sqlite3VdbeAddOp3(v, OP_And, r3, r4, r4);
    }
  }
  sqlite3VdbeAddOp2(v, OP_If, r4, sqlite3VdbeCurrentAddr(v)+6);
  sqlite3VdbeAddOp2(v, OP_IfNot, r4, sqlite3VdbeCurrentAddr(v)+2);
  sqlite3VdbeAddOp2(v, OP_Null, 0, regResult);
  sqlite3VdbeAddOp2(v, OP_Next, pExpr->iTable, addrNext);
  sqlite3VdbeAddOp3(v, OP_If, regResult, destIfNull, 1);
  sqlite3VdbeAddOp2(v, OP_Goto, 0, destIfFalse);

  sqlite3ReleaseTempReg(pParse, r2);
  sqlite3ReleaseTempReg(pParse, r3);
  sqlite3ReleaseTempReg(pParse, r4);
  sqlite3ReleaseTempReg(pParse, regResult);
  sqlite3ExprCachePop(pParse);
}
#endif

#ifndef SQLITE_OMIT_SUBQUERY
/*
** Generate code for an IN expression.
**
**      x IN (SELECT ...)
**      x IN (value, value, ...)
**
................................................................................
static void sqlite3ExprCodeIN(
  Parse *pParse,        /* Parsing and code generating context */
  Expr *pExpr,          /* The IN expression */
  int destIfFalse,      /* Jump here if LHS is not contained in the RHS */
  int destIfNull        /* Jump here if the results are unknown due to NULLs */
){
  int rRhsHasNull = 0;  /* Register that is true if RHS contains NULL values */
  char affinity;        /* Comparison affinity to use */
  int eType;            /* Type of the RHS */
  int r1;               /* Temporary use register */
  Vdbe *v;              /* Statement under construction */





  if( pExpr->pLeft->flags & EP_Vector ){
    return exprCodeVectorIN(pParse, pExpr, destIfFalse, destIfNull);

  }







  /* 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 = sqlite3FindInIndex(pParse, pExpr,
                             IN_INDEX_MEMBERSHIP | IN_INDEX_NOOP_OK,
                             destIfFalse==destIfNull ? 0 : &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);


  /* Code the LHS, the <expr> from "<expr> IN (...)".


  */

  sqlite3ExprCachePush(pParse);
  r1 = sqlite3GetTempReg(pParse);










  sqlite3ExprCode(pParse, pExpr->pLeft, r1);










  /* If sqlite3FindInIndex() did not find or create an index that is
  ** suitable for evaluating the IN operator, then evaluate using a
  ** sequence of comparisons.
  */
  if( eType==IN_INDEX_NOOP ){
    ExprList *pList = pExpr->x.pList;
................................................................................
        sqlite3VdbeAddOp3(v, OP_BitAnd, regCkNull, r2, regCkNull);
      }
      if( ii<pList->nExpr-1 || destIfNull!=destIfFalse ){
        sqlite3VdbeAddOp4(v, OP_Eq, r1, labelOk, r2,
                          (void*)pColl, P4_COLLSEQ);
        VdbeCoverageIf(v, ii<pList->nExpr-1);
        VdbeCoverageIf(v, ii==pList->nExpr-1);
        sqlite3VdbeChangeP5(v, affinity);
      }else{
        assert( destIfNull==destIfFalse );
        sqlite3VdbeAddOp4(v, OP_Ne, r1, destIfFalse, r2,
                          (void*)pColl, P4_COLLSEQ); VdbeCoverage(v);
        sqlite3VdbeChangeP5(v, affinity | SQLITE_JUMPIFNULL);
      }
      sqlite3ReleaseTempReg(pParse, regToFree);
    }
    if( regCkNull ){
      sqlite3VdbeAddOp2(v, OP_IsNull, regCkNull, destIfNull); VdbeCoverage(v);
      sqlite3VdbeGoto(v, destIfFalse);
    }
................................................................................
    sqlite3VdbeResolveLabel(v, labelOk);
    sqlite3ReleaseTempReg(pParse, regCkNull);
  }else{
  
    /* If the LHS is NULL, then the result is either false or NULL depending
    ** on whether the RHS is empty or not, respectively.
    */
    if( sqlite3ExprCanBeNull(pExpr->pLeft) ){
      if( destIfNull==destIfFalse ){
        /* Shortcut for the common case where the false and NULL outcomes are
        ** the same. */
        sqlite3VdbeAddOp2(v, OP_IsNull, r1, destIfNull); VdbeCoverage(v);
      }else{
        int addr1 = sqlite3VdbeAddOp1(v, OP_NotNull, r1); VdbeCoverage(v);
        sqlite3VdbeAddOp2(v, OP_Rewind, pExpr->iTable, destIfFalse);
................................................................................
    }
  
    if( eType==IN_INDEX_ROWID ){
      /* In this case, the RHS is the ROWID of table b-tree
      */
      sqlite3VdbeAddOp3(v, OP_SeekRowid, pExpr->iTable, destIfFalse, r1);
      VdbeCoverage(v);








































    }else{
      /* In this case, the RHS is an index b-tree.
      */
      sqlite3VdbeAddOp4(v, OP_Affinity, r1, 1, 0, &affinity, 1);
  
      /* 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.
      */
................................................................................
        /* This branch runs if it is known at compile time that the RHS
        ** cannot contain NULL values. This happens as the result
        ** of a "NOT NULL" constraint in the database schema.
        **
        ** Also run this branch if NULL is equivalent to FALSE
        ** for this particular IN operator.
        */

        sqlite3VdbeAddOp4Int(v, OP_NotFound, pExpr->iTable, destIfFalse, r1, 1);

        VdbeCoverage(v);
      }else{
        /* In this branch, the RHS of the IN might contain a NULL and
        ** the presence of a NULL on the RHS makes a difference in the
        ** outcome.
        */
        int addr1;
................................................................................
        sqlite3VdbeGoto(v, destIfFalse);
        sqlite3VdbeJumpHere(v, addr1);
      }
    }
  }
  sqlite3ReleaseTempReg(pParse, r1);
  sqlite3ExprCachePop(pParse);

  VdbeComment((v, "end IN expr"));
}
#endif /* SQLITE_OMIT_SUBQUERY */

#ifndef SQLITE_OMIT_FLOATING_POINT
/*
** Generate an instruction that will put the floating point
................................................................................
  assert( pExpr->op!=TK_NE || op==OP_Eq );
  assert( pExpr->op!=TK_EQ || op==OP_Ne );
  assert( pExpr->op!=TK_LT || op==OP_Ge );
  assert( pExpr->op!=TK_LE || op==OP_Gt );
  assert( pExpr->op!=TK_GT || op==OP_Le );
  assert( pExpr->op!=TK_GE || op==OP_Lt );

  switch( pExpr->op | (pExpr->pLeft ? (pExpr->pLeft->flags & EP_Vector) : 0)){
    case TK_AND: {
      testcase( jumpIfNull==0 );
      sqlite3ExprIfFalse(pParse, pExpr->pLeft, dest, jumpIfNull);
      sqlite3ExprCachePush(pParse);
      sqlite3ExprIfFalse(pParse, pExpr->pRight, dest, jumpIfNull);
      sqlite3ExprCachePop(pParse);
      break;
................................................................................
      /* Fall thru */
    case TK_LT:
    case TK_LE:
    case TK_GT:
    case TK_GE:
    case TK_NE:
    case TK_EQ: {


      testcase( jumpIfNull==0 );
      r1 = sqlite3ExprCodeTemp(pParse, pExpr->pLeft, &regFree1);
      r2 = sqlite3ExprCodeTemp(pParse, pExpr->pRight, &regFree2);
      codeCompare(pParse, pExpr->pLeft, pExpr->pRight, op,
                  r1, r2, dest, jumpIfNull);
      assert(TK_LT==OP_Lt); testcase(op==OP_Lt); VdbeCoverageIf(v,op==OP_Lt);
      assert(TK_LE==OP_Le); testcase(op==OP_Le); VdbeCoverageIf(v,op==OP_Le);
................................................................................
        sqlite3ExprCodeIN(pParse, pExpr, dest, destIfNull);
        sqlite3VdbeResolveLabel(v, destIfNull);
      }
      break;
    }
#endif
    default: {

      if( exprAlwaysFalse(pExpr) ){
        sqlite3VdbeGoto(v, dest);
      }else if( exprAlwaysTrue(pExpr) ){
        /* no-op */
      }else{
        r1 = sqlite3ExprCodeTemp(pParse, pExpr, &regFree1);
        sqlite3VdbeAddOp3(v, OP_IfNot, r1, dest, jumpIfNull!=0);







>
>
>
>
>
>







 







>
>
>
>
>
>
>







 







|
>
>
>
>
>
>







 







>


<







 







>







 







>
>
>
>
>
>







 







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







 







<



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

<
>
>
|
<
>





|

|
|
<
<
<
>

|
>
>

>

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







 







|




|







 







|







 







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



|







 







>
|
>







 







>







 







|







 







>
>







 







>







319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
....
1712
1713
1714
1715
1716
1717
1718
1719
1720
1721
1722
1723
1724
1725
1726
1727
1728
1729
1730
1731
1732
....
1879
1880
1881
1882
1883
1884
1885
1886
1887
1888
1889
1890
1891
1892
1893
1894
1895
1896
1897
1898
1899
....
1902
1903
1904
1905
1906
1907
1908
1909
1910
1911

1912
1913
1914
1915
1916
1917
1918
....
1976
1977
1978
1979
1980
1981
1982
1983
1984
1985
1986
1987
1988
1989
1990
....
2039
2040
2041
2042
2043
2044
2045
2046
2047
2048
2049
2050
2051
2052
2053
2054
2055
2056
2057
2058
....
2331
2332
2333
2334
2335
2336
2337





































































2338
2339
2340
2341
2342
2343
2344
....
2356
2357
2358
2359
2360
2361
2362

2363
2364
2365

2366
2367
2368
2369
2370

2371
2372
2373
2374
2375
2376
2377
2378
2379

2380
2381
2382

2383
2384
2385
2386
2387
2388
2389
2390
2391
2392



2393
2394
2395
2396
2397
2398
2399
2400

2401
2402
2403
2404
2405
2406
2407
2408
2409
2410
2411
2412
2413
2414
2415
2416
2417
2418
2419
2420
2421
2422
2423
2424
2425
2426
2427
....
2441
2442
2443
2444
2445
2446
2447
2448
2449
2450
2451
2452
2453
2454
2455
2456
2457
2458
2459
2460
....
2461
2462
2463
2464
2465
2466
2467
2468
2469
2470
2471
2472
2473
2474
2475
....
2480
2481
2482
2483
2484
2485
2486
2487
2488
2489
2490
2491
2492
2493
2494
2495
2496
2497
2498
2499
2500
2501
2502
2503
2504
2505
2506
2507
2508
2509
2510
2511
2512
2513
2514
2515
2516
2517
2518
2519
2520
2521
2522
2523
2524
2525
2526
2527
2528
2529
2530
2531
2532
2533
2534
2535
2536
2537
....
2540
2541
2542
2543
2544
2545
2546
2547
2548
2549
2550
2551
2552
2553
2554
2555
2556
....
2568
2569
2570
2571
2572
2573
2574
2575
2576
2577
2578
2579
2580
2581
2582
....
4034
4035
4036
4037
4038
4039
4040
4041
4042
4043
4044
4045
4046
4047
4048
....
4071
4072
4073
4074
4075
4076
4077
4078
4079
4080
4081
4082
4083
4084
4085
4086
....
4119
4120
4121
4122
4123
4124
4125
4126
4127
4128
4129
4130
4131
4132
4133
  if( (pExpr->flags & EP_Vector)==0 ) return 1;
  if( pExpr->flags & EP_xIsSelect ){
    return pExpr->x.pSelect->pEList->nExpr;
  }
  return pExpr->x.pList->nExpr;
}

/*
** If the expression passed as the first argument is a TK_VECTOR, return
** a pointer to the i'th field of the vector. Or, if the first argument
** points to a sub-select, return a pointer to the i'th returned column 
** value. Otherwise, return a copy of the first argument.
*/
static Expr *exprVectorField(Expr *pVector, int i){
  if( (pVector->flags & EP_Vector)==0 ){
    assert( i==0 );
    return pVector;
  }else if( pVector->flags & EP_xIsSelect ){
    return pVector->x.pSelect->pEList->a[i].pExpr;
  }
................................................................................

/*
** pX is the RHS of an IN operator.  If pX is a SELECT statement 
** that can be simplified to a direct table access, then return
** a pointer to the SELECT statement.  If pX is not a SELECT statement,
** or if the SELECT statement needs to be manifested into a transient
** table, then return NULL.
** 
** If parameter bNullSensitive is 0, then this operation will be
** used in a context in which there is no difference between a result
** of 0 and one of NULL. For example:
**
**     ... WHERE (?,?) IN (SELECT ...)
**
*/
#ifndef SQLITE_OMIT_SUBQUERY
static Select *isCandidateForInOpt(Expr *pX, int bNullSensitive){
  Select *p;
  SrcList *pSrc;
  ExprList *pEList;
  Table *pTab;
................................................................................
**
** If a register is allocated and its location stored in *prRhsHasNull, then
** the value in that register will be NULL if the b-tree contains one or more
** NULL values, and it will be some non-NULL value if the b-tree contains no
** NULL values.
*/
#ifndef SQLITE_OMIT_SUBQUERY
int sqlite3FindInIndex(
  Parse *pParse, 
  Expr *pX, 
  u32 inFlags, 
  int *prRhsHasNull,
  int *aiMap
){
  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;                     /* True if RHS must be unique */
  Vdbe *v = sqlite3GetVdbe(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.
  */
  if( pParse->nErr==0 && (p = isCandidateForInOpt(pX, prRhsHasNull!=0))!=0 ){
    sqlite3 *db = pParse->db;              /* Database connection */
    Table *pTab;                           /* Table <table>. */
    i16 iDb;                               /* Database idx for pTab */
    ExprList *pEList = p->pEList;
    int nExpr = pEList->nExpr;


    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;

    /* Code an OP_Transaction and OP_TableLock for <table>. */
................................................................................
          for(j=0; j<nExpr; j++){
            if( pIdx->aiColumn[j]!=pRhs->iColumn ) continue;
            assert( pIdx->azColl[j] );
            if( sqlite3StrICmp(pReq->zName, pIdx->azColl[j])!=0 ) continue;
            break;
          }
          if( j==nExpr ) break;
          if( aiMap ) aiMap[i] = j;
        }

        if( i==nExpr ){
          int iAddr = sqlite3CodeOnce(pParse); VdbeCoverage(v);
          sqlite3VdbeAddOp3(v, OP_OpenRead, iTab, pIdx->tnum, iDb);
          sqlite3VdbeSetP4KeyInfo(pParse, pIdx);
          VdbeComment((v, "%s", pIdx->zName));
................................................................................
      *prRhsHasNull = rMayHaveNull = ++pParse->nMem;
    }
    sqlite3CodeSubselect(pParse, pX, rMayHaveNull, eType==IN_INDEX_ROWID);
    pParse->nQueryLoop = savedNQueryLoop;
  }else{
    pX->iTable = iTab;
  }

  if( aiMap && eType!=IN_INDEX_INDEX_ASC && eType!=IN_INDEX_INDEX_DESC ){
    int i, n;
    n = sqlite3ExprVectorSize(pX->pLeft);
    for(i=0; i<n; i++) aiMap[i] = i;
  }
  return eType;
}
#endif

static char *exprINAffinity(Parse *pParse, Expr *pExpr){
  Expr *pLeft = pExpr->pLeft;
  int nVal = sqlite3ExprVectorSize(pLeft);
................................................................................
  }
  sqlite3ExprCachePop(pParse);

  return rReg;
}
#endif /* SQLITE_OMIT_SUBQUERY */






































































#ifndef SQLITE_OMIT_SUBQUERY
/*
** Generate code for an IN expression.
**
**      x IN (SELECT ...)
**      x IN (value, value, ...)
**
................................................................................
static void sqlite3ExprCodeIN(
  Parse *pParse,        /* Parsing and code generating context */
  Expr *pExpr,          /* The IN expression */
  int destIfFalse,      /* Jump here if LHS is not contained in the RHS */
  int destIfNull        /* Jump here if the results are unknown due to NULLs */
){
  int rRhsHasNull = 0;  /* Register that is true if RHS contains NULL values */

  int eType;            /* Type of the RHS */
  int r1;               /* Temporary use register */
  Vdbe *v;              /* Statement under construction */

  int *aiMap = 0;       /* Map from vector field to index column */
  char *zAff = 0;       /* Affinity string for comparisons */
  int nVector;          /* Size of vectors for this IN(...) op */
  int regSelect = 0;
  Expr *pLeft = pExpr->pLeft;

  int i;

  nVector = sqlite3ExprVectorSize(pExpr->pLeft);
  aiMap = (int*)sqlite3DbMallocZero(
      pParse->db, nVector*(sizeof(int) + sizeof(char)) + 1
  );
  if( !aiMap ) return;
  zAff = (char*)&aiMap[nVector];


  /* Attempt to compute the RHS. After this step, if anything other than
  ** IN_INDEX_NOOP is returned, the table opened ith cursor pExpr->iTable 
  ** contains the values that make up the RHS. If IN_INDEX_NOOP is returned,

  ** the RHS has not yet been coded.  */
  v = pParse->pVdbe;
  assert( v!=0 );       /* OOM detected prior to this routine */
  VdbeNoopComment((v, "begin IN expr"));
  eType = sqlite3FindInIndex(pParse, pExpr,
                             IN_INDEX_MEMBERSHIP | IN_INDEX_NOOP_OK,
                             destIfFalse==destIfNull ? 0 : &rRhsHasNull, aiMap);

  assert( pParse->nErr || nVector==1 || eType==IN_INDEX_EPH
       || eType==IN_INDEX_INDEX_ASC || eType==IN_INDEX_INDEX_DESC 



  );

  /* Code the LHS, the <expr> from "<expr> IN (...)". If the LHS is a 
  ** vector, then it is stored in an array of nVector registers starting 
  ** at r1.
  */
  r1 = sqlite3GetTempRange(pParse, nVector);
  sqlite3ExprCachePush(pParse);

  if( nVector>1 && (pLeft->flags & EP_xIsSelect) ){
    regSelect = sqlite3CodeSubselect(pParse, pLeft, 0, 0);
  }
  for(i=0; i<nVector; i++){
    int iCol = aiMap[i];
    Expr *pLhs = exprVectorField(pLeft, i);

    if( regSelect ){
      sqlite3VdbeAddOp3(v, OP_Copy, regSelect+i, r1+iCol, 0);
    }else{
      sqlite3ExprCode(pParse, pLhs, r1+iCol);
    }

    zAff[iCol] = sqlite3ExprAffinity(pLhs);
    if( pExpr->flags & EP_xIsSelect ){
      zAff[iCol] = sqlite3CompareAffinity(
          pExpr->x.pSelect->pEList->a[iCol].pExpr, zAff[iCol]
      );
    }
  }

  /* If sqlite3FindInIndex() did not find or create an index that is
  ** suitable for evaluating the IN operator, then evaluate using a
  ** sequence of comparisons.
  */
  if( eType==IN_INDEX_NOOP ){
    ExprList *pList = pExpr->x.pList;
................................................................................
        sqlite3VdbeAddOp3(v, OP_BitAnd, regCkNull, r2, regCkNull);
      }
      if( ii<pList->nExpr-1 || destIfNull!=destIfFalse ){
        sqlite3VdbeAddOp4(v, OP_Eq, r1, labelOk, r2,
                          (void*)pColl, P4_COLLSEQ);
        VdbeCoverageIf(v, ii<pList->nExpr-1);
        VdbeCoverageIf(v, ii==pList->nExpr-1);
        sqlite3VdbeChangeP5(v, zAff[0]);
      }else{
        assert( destIfNull==destIfFalse );
        sqlite3VdbeAddOp4(v, OP_Ne, r1, destIfFalse, r2,
                          (void*)pColl, P4_COLLSEQ); VdbeCoverage(v);
        sqlite3VdbeChangeP5(v, zAff[0] | SQLITE_JUMPIFNULL);
      }
      sqlite3ReleaseTempReg(pParse, regToFree);
    }
    if( regCkNull ){
      sqlite3VdbeAddOp2(v, OP_IsNull, regCkNull, destIfNull); VdbeCoverage(v);
      sqlite3VdbeGoto(v, destIfFalse);
    }
................................................................................
    sqlite3VdbeResolveLabel(v, labelOk);
    sqlite3ReleaseTempReg(pParse, regCkNull);
  }else{
  
    /* If the LHS is NULL, then the result is either false or NULL depending
    ** on whether the RHS is empty or not, respectively.
    */
    if( nVector==1 && sqlite3ExprCanBeNull(pExpr->pLeft) ){
      if( destIfNull==destIfFalse ){
        /* Shortcut for the common case where the false and NULL outcomes are
        ** the same. */
        sqlite3VdbeAddOp2(v, OP_IsNull, r1, destIfNull); VdbeCoverage(v);
      }else{
        int addr1 = sqlite3VdbeAddOp1(v, OP_NotNull, r1); VdbeCoverage(v);
        sqlite3VdbeAddOp2(v, OP_Rewind, pExpr->iTable, destIfFalse);
................................................................................
    }
  
    if( eType==IN_INDEX_ROWID ){
      /* In this case, the RHS is the ROWID of table b-tree
      */
      sqlite3VdbeAddOp3(v, OP_SeekRowid, pExpr->iTable, destIfFalse, r1);
      VdbeCoverage(v);
    }else if( nVector>1 && eType==IN_INDEX_EPH ){
      int regNull = sqlite3GetTempReg(pParse);
      int r2 = sqlite3GetTempReg(pParse);
      int r3 = sqlite3GetTempReg(pParse);
      int r4 = sqlite3GetTempReg(pParse);
      int addrNext;
      int addrIf;

      if( destIfFalse!=destIfNull ){
        sqlite3VdbeAddOp2(v, OP_Integer, 0, regNull);
      }
      addrNext = sqlite3VdbeAddOp2(v, OP_Rewind, pExpr->iTable, destIfFalse);
      for(i=0; i<nVector; i++){
        Expr *p;
        CollSeq *pColl;
        p = exprVectorField(pLeft, i);
        pColl = sqlite3ExprCollSeq(pParse, p);

        sqlite3VdbeAddOp3(v, OP_Column, pExpr->iTable, i, r2);
        sqlite3VdbeAddOp4(v, OP_Eq, r1+i, i?r3:r4, r2, (void*)pColl,P4_COLLSEQ);
        sqlite3VdbeChangeP5(v, SQLITE_STOREP2);
        if( i!=0 ){
          sqlite3VdbeAddOp3(v, OP_And, r3, r4, r4);
        }
      }
      addrIf = sqlite3VdbeAddOp1(v, OP_If, r4);
      if( destIfNull!=destIfFalse ){
        sqlite3VdbeAddOp2(v, OP_IfNot, r4, sqlite3VdbeCurrentAddr(v)+2);
        sqlite3VdbeAddOp2(v, OP_Integer, 1, regNull);
      }
      sqlite3VdbeAddOp2(v, OP_Next, pExpr->iTable, addrNext+1);
      if( destIfNull!=destIfFalse ){
        sqlite3VdbeAddOp2(v, OP_If, regNull, destIfNull);
      }
      sqlite3VdbeAddOp2(v, OP_Goto, 0, destIfFalse);
      sqlite3VdbeChangeP2(v, addrIf, sqlite3VdbeCurrentAddr(v));
      sqlite3ReleaseTempReg(pParse, regNull);
      sqlite3ReleaseTempReg(pParse, r2);
      sqlite3ReleaseTempReg(pParse, r3);
      sqlite3ReleaseTempReg(pParse, r4);
    }else{
      /* In this case, the RHS is an index b-tree.
      */
      sqlite3VdbeAddOp4(v, OP_Affinity, r1, nVector, 0, zAff, nVector);
  
      /* 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.
      */
................................................................................
        /* This branch runs if it is known at compile time that the RHS
        ** cannot contain NULL values. This happens as the result
        ** of a "NOT NULL" constraint in the database schema.
        **
        ** Also run this branch if NULL is equivalent to FALSE
        ** for this particular IN operator.
        */
        sqlite3VdbeAddOp4Int(
            v, OP_NotFound, pExpr->iTable, destIfFalse, r1, nVector
        );
        VdbeCoverage(v);
      }else{
        /* In this branch, the RHS of the IN might contain a NULL and
        ** the presence of a NULL on the RHS makes a difference in the
        ** outcome.
        */
        int addr1;
................................................................................
        sqlite3VdbeGoto(v, destIfFalse);
        sqlite3VdbeJumpHere(v, addr1);
      }
    }
  }
  sqlite3ReleaseTempReg(pParse, r1);
  sqlite3ExprCachePop(pParse);
  sqlite3DbFree(pParse->db, aiMap);
  VdbeComment((v, "end IN expr"));
}
#endif /* SQLITE_OMIT_SUBQUERY */

#ifndef SQLITE_OMIT_FLOATING_POINT
/*
** Generate an instruction that will put the floating point
................................................................................
  assert( pExpr->op!=TK_NE || op==OP_Eq );
  assert( pExpr->op!=TK_EQ || op==OP_Ne );
  assert( pExpr->op!=TK_LT || op==OP_Ge );
  assert( pExpr->op!=TK_LE || op==OP_Gt );
  assert( pExpr->op!=TK_GT || op==OP_Le );
  assert( pExpr->op!=TK_GE || op==OP_Lt );

  switch( pExpr->op ){
    case TK_AND: {
      testcase( jumpIfNull==0 );
      sqlite3ExprIfFalse(pParse, pExpr->pLeft, dest, jumpIfNull);
      sqlite3ExprCachePush(pParse);
      sqlite3ExprIfFalse(pParse, pExpr->pRight, dest, jumpIfNull);
      sqlite3ExprCachePop(pParse);
      break;
................................................................................
      /* Fall thru */
    case TK_LT:
    case TK_LE:
    case TK_GT:
    case TK_GE:
    case TK_NE:
    case TK_EQ: {
      if( pExpr->pLeft->flags & EP_Vector ) goto default_expr;

      testcase( jumpIfNull==0 );
      r1 = sqlite3ExprCodeTemp(pParse, pExpr->pLeft, &regFree1);
      r2 = sqlite3ExprCodeTemp(pParse, pExpr->pRight, &regFree2);
      codeCompare(pParse, pExpr->pLeft, pExpr->pRight, op,
                  r1, r2, dest, jumpIfNull);
      assert(TK_LT==OP_Lt); testcase(op==OP_Lt); VdbeCoverageIf(v,op==OP_Lt);
      assert(TK_LE==OP_Le); testcase(op==OP_Le); VdbeCoverageIf(v,op==OP_Le);
................................................................................
        sqlite3ExprCodeIN(pParse, pExpr, dest, destIfNull);
        sqlite3VdbeResolveLabel(v, destIfNull);
      }
      break;
    }
#endif
    default: {
    default_expr: 
      if( exprAlwaysFalse(pExpr) ){
        sqlite3VdbeGoto(v, dest);
      }else if( exprAlwaysTrue(pExpr) ){
        /* no-op */
      }else{
        r1 = sqlite3ExprCodeTemp(pParse, pExpr, &regFree1);
        sqlite3VdbeAddOp3(v, OP_IfNot, r1, dest, jumpIfNull!=0);

Changes to src/sqliteInt.h.

4130
4131
4132
4133
4134
4135
4136
4137
4138
4139
4140
4141
4142
4143
4144
#define IN_INDEX_NOOP         5   /* No table available. Use comparisons */
/*
** Allowed flags for the 3rd parameter to sqlite3FindInIndex().
*/
#define IN_INDEX_NOOP_OK     0x0001  /* OK to return IN_INDEX_NOOP */
#define IN_INDEX_MEMBERSHIP  0x0002  /* IN operator used for membership test */
#define IN_INDEX_LOOP        0x0004  /* IN operator used as a loop */
int sqlite3FindInIndex(Parse *, Expr *, u32, int*);

int sqlite3JournalOpen(sqlite3_vfs *, const char *, sqlite3_file *, int, int);
int sqlite3JournalSize(sqlite3_vfs *);
#ifdef SQLITE_ENABLE_ATOMIC_WRITE
  int sqlite3JournalCreate(sqlite3_file *);
#endif








|







4130
4131
4132
4133
4134
4135
4136
4137
4138
4139
4140
4141
4142
4143
4144
#define IN_INDEX_NOOP         5   /* No table available. Use comparisons */
/*
** Allowed flags for the 3rd parameter to sqlite3FindInIndex().
*/
#define IN_INDEX_NOOP_OK     0x0001  /* OK to return IN_INDEX_NOOP */
#define IN_INDEX_MEMBERSHIP  0x0002  /* IN operator used for membership test */
#define IN_INDEX_LOOP        0x0004  /* IN operator used as a loop */
int sqlite3FindInIndex(Parse *, Expr *, u32, int*, int*);

int sqlite3JournalOpen(sqlite3_vfs *, const char *, sqlite3_file *, int, int);
int sqlite3JournalSize(sqlite3_vfs *);
#ifdef SQLITE_ENABLE_ATOMIC_WRITE
  int sqlite3JournalCreate(sqlite3_file *);
#endif

Changes to src/wherecode.c.

375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
    ){
      testcase( iEq==0 );
      testcase( bRev );
      bRev = !bRev;
    }
    assert( pX->op==TK_IN );
    iReg = iTarget;
    eType = sqlite3FindInIndex(pParse, pX, IN_INDEX_LOOP, 0);
    if( eType==IN_INDEX_INDEX_DESC ){
      testcase( bRev );
      bRev = !bRev;
    }
    iTab = pX->iTable;
    sqlite3VdbeAddOp2(v, bRev ? OP_Last : OP_Rewind, iTab, 0);
    VdbeCoverageIf(v, bRev);







|







375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
    ){
      testcase( iEq==0 );
      testcase( bRev );
      bRev = !bRev;
    }
    assert( pX->op==TK_IN );
    iReg = iTarget;
    eType = sqlite3FindInIndex(pParse, pX, IN_INDEX_LOOP, 0, 0);
    if( eType==IN_INDEX_INDEX_DESC ){
      testcase( bRev );
      bRev = !bRev;
    }
    iTab = pX->iTable;
    sqlite3VdbeAddOp2(v, bRev ? OP_Last : OP_Rewind, iTab, 0);
    VdbeCoverageIf(v, bRev);

Added test/rowvalue3.test.









































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
# 2016 June 17
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    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.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing "(...) IN (SELECT ...)" expressions
# where the SELECT statement returns more than one column.
#

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


do_execsql_test 1.0 {
  CREATE TABLE t1(a, b, c);
  CREATE INDEX i1 ON t1(a, b);
  INSERT INTO t1 VALUES(1, 2, 3);
  INSERT INTO t1 VALUES(4, 5, 6);
  INSERT INTO t1 VALUES(7, 8, 9);
}

foreach {tn sql res} {
  1  "SELECT 1 WHERE (4, 5) IN (SELECT a, b FROM t1)"  1
  2  "SELECT 1 WHERE (5, 5) IN (SELECT a, b FROM t1)"  {}
  3  "SELECT 1 WHERE (5, 4) IN (SELECT a, b FROM t1)"  {}
  4  "SELECT 1 WHERE (5, 4) IN (SELECT b, a FROM t1)"  1
  5  "SELECT 1 WHERE (SELECT a, b FROM t1 WHERE c=6) IN (SELECT a, b FROM t1)" 1
  6  "SELECT (5, 4) IN (SELECT a, b FROM t1)" 0
  7  "SELECT 1 WHERE (5, 4) IN (SELECT +b, +a FROM t1)"  1
  8  "SELECT (5, 4) IN (SELECT +b, +a FROM t1)"  1
  9  "SELECT (1, 2) IN (SELECT rowid, b FROM t1)"  1
  10 "SELECT 1 WHERE (1, 2) IN (SELECT rowid, b FROM t1)"  1
  11 "SELECT 1 WHERE (1, NULL) IN (SELECT rowid, b FROM t1)"  {}
} {
  do_execsql_test 1.$tn $sql $res
}

#explain_i { SELECT (4, NULL) IN (SELECT a, b FROM t1) } 
#do_execsql_test 2 { SELECT (4, NULL) IN (SELECT a, b FROM t1) } {}




finish_test