SQLite

Check-in [2c7e277bbe]
Login

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

Overview
Comment:Enable the OR optimization for WITHOUT ROWID tables. Use a temp table instead of the RowSet object to track the rows that have already been included in the result set.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | without-rowid-or-opt
Files: files | file ages | folders
SHA1: 2c7e277bbebd5c93dec53d381d9737909d40d846
User & Date: dan 2014-05-26 20:06:45.481
Context
2014-05-26
20:25
Add a missing VdbeCoverage() macro on an OP_Found opcode added in the previous check-in. (check-in: b4980a07e2 user: drh tags: without-rowid-or-opt)
20:06
Enable the OR optimization for WITHOUT ROWID tables. Use a temp table instead of the RowSet object to track the rows that have already been included in the result set. (check-in: 2c7e277bbe user: dan tags: without-rowid-or-opt)
18:27
Fix a problem in the shell when importing CSV files. If the leftmost field of the first row in the CSV file was both zero bytes in size and unquoted, no data was imported. (check-in: 856d44a206 user: dan tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/where.c.
3303
3304
3305
3306
3307
3308
3309

3310
3311
3312
3313
3314
3315
3316
    int regRowset = 0;                        /* Register for RowSet object */
    int regRowid = 0;                         /* Register holding rowid */
    int iLoopBody = sqlite3VdbeMakeLabel(v);  /* Start of loop body */
    int iRetInit;                             /* Address of regReturn init */
    int untestedTerms = 0;             /* Some terms not completely tested */
    int ii;                            /* Loop counter */
    Expr *pAndExpr = 0;                /* An ".. AND (...)" expression */

   
    pTerm = pLoop->aLTerm[0];
    assert( pTerm!=0 );
    assert( pTerm->eOperator & WO_OR );
    assert( (pTerm->wtFlags & TERM_ORINFO)!=0 );
    pOrWc = &pTerm->u.pOrInfo->wc;
    pLevel->op = OP_Return;







>







3303
3304
3305
3306
3307
3308
3309
3310
3311
3312
3313
3314
3315
3316
3317
    int regRowset = 0;                        /* Register for RowSet object */
    int regRowid = 0;                         /* Register holding rowid */
    int iLoopBody = sqlite3VdbeMakeLabel(v);  /* Start of loop body */
    int iRetInit;                             /* Address of regReturn init */
    int untestedTerms = 0;             /* Some terms not completely tested */
    int ii;                            /* Loop counter */
    Expr *pAndExpr = 0;                /* An ".. AND (...)" expression */
    Table *pTab = pTabItem->pTab;
   
    pTerm = pLoop->aLTerm[0];
    assert( pTerm!=0 );
    assert( pTerm->eOperator & WO_OR );
    assert( (pTerm->wtFlags & TERM_ORINFO)!=0 );
    pOrWc = &pTerm->u.pOrInfo->wc;
    pLevel->op = OP_Return;
3346
3347
3348
3349
3350
3351
3352

3353
3354
3355







3356
3357
3358
3359
3360
3361
3362
    ** is required in a few obscure LEFT JOIN cases where control jumps
    ** over the top of the loop into the body of it. In this case the 
    ** correct response for the end-of-loop code (the OP_Return) is to 
    ** fall through to the next instruction, just as an OP_Next does if
    ** called on an uninitialized cursor.
    */
    if( (pWInfo->wctrlFlags & WHERE_DUPLICATES_OK)==0 ){

      regRowset = ++pParse->nMem;
      regRowid = ++pParse->nMem;
      sqlite3VdbeAddOp2(v, OP_Null, 0, regRowset);







    }
    iRetInit = sqlite3VdbeAddOp2(v, OP_Integer, 0, regReturn);

    /* If the original WHERE clause is z of the form:  (x1 OR x2 OR ...) AND y
    ** Then for every term xN, evaluate as the subexpression: xN AND z
    ** That way, terms in y that are factored into the disjunction will
    ** be picked up by the recursive calls to sqlite3WhereBegin() below.







>
|
<
|
>
>
>
>
>
>
>







3347
3348
3349
3350
3351
3352
3353
3354
3355

3356
3357
3358
3359
3360
3361
3362
3363
3364
3365
3366
3367
3368
3369
3370
    ** is required in a few obscure LEFT JOIN cases where control jumps
    ** over the top of the loop into the body of it. In this case the 
    ** correct response for the end-of-loop code (the OP_Return) is to 
    ** fall through to the next instruction, just as an OP_Next does if
    ** called on an uninitialized cursor.
    */
    if( (pWInfo->wctrlFlags & WHERE_DUPLICATES_OK)==0 ){
      if( HasRowid(pTab) ){
        regRowset = ++pParse->nMem;

        sqlite3VdbeAddOp2(v, OP_Null, 0, regRowset);
      }else{
        Index *pPk = sqlite3PrimaryKeyIndex(pTab);
        regRowset = pParse->nTab++;
        sqlite3VdbeAddOp2(v, OP_OpenEphemeral, regRowset, pPk->nKeyCol);
        sqlite3VdbeSetP4KeyInfo(pParse, pPk);
      }
      regRowid = ++pParse->nMem;
    }
    iRetInit = sqlite3VdbeAddOp2(v, OP_Integer, 0, regReturn);

    /* If the original WHERE clause is z of the form:  (x1 OR x2 OR ...) AND y
    ** Then for every term xN, evaluate as the subexpression: xN AND z
    ** That way, terms in y that are factored into the disjunction will
    ** be picked up by the recursive calls to sqlite3WhereBegin() below.
3404
3405
3406
3407
3408
3409
3410
3411
3412



3413
3414

3415
3416
3417






































3418
3419
3420
3421
3422
3423
3424
        assert( pSubWInfo || pParse->nErr || db->mallocFailed );
        if( pSubWInfo ){
          WhereLoop *pSubLoop;
          explainOneScan(
              pParse, pOrTab, &pSubWInfo->a[0], iLevel, pLevel->iFrom, 0
          );
          if( (pWInfo->wctrlFlags & WHERE_DUPLICATES_OK)==0 ){
            int iSet = ((ii==pOrWc->nTerm-1)?-1:ii);
            int r;



            r = sqlite3ExprCodeGetColumn(pParse, pTabItem->pTab, -1, iCur, 
                                         regRowid, 0);

            sqlite3VdbeAddOp4Int(v, OP_RowSetTest, regRowset,
                                 sqlite3VdbeCurrentAddr(v)+2, r, iSet);
            VdbeCoverage(v);






































          }
          sqlite3VdbeAddOp2(v, OP_Gosub, regReturn, iLoopBody);

          /* The pSubWInfo->untestedTerms flag means that this OR term
          ** contained one or more AND term from a notReady table.  The
          ** terms from the notReady table could not be tested and will
          ** need to be tested later.







<

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







3412
3413
3414
3415
3416
3417
3418

3419
3420
3421
3422
3423

3424
3425

3426
3427
3428
3429
3430
3431
3432
3433
3434
3435
3436
3437
3438
3439
3440
3441
3442
3443
3444
3445
3446
3447
3448
3449
3450
3451
3452
3453
3454
3455
3456
3457
3458
3459
3460
3461
3462
3463
3464
3465
3466
3467
3468
3469
3470
3471
        assert( pSubWInfo || pParse->nErr || db->mallocFailed );
        if( pSubWInfo ){
          WhereLoop *pSubLoop;
          explainOneScan(
              pParse, pOrTab, &pSubWInfo->a[0], iLevel, pLevel->iFrom, 0
          );
          if( (pWInfo->wctrlFlags & WHERE_DUPLICATES_OK)==0 ){

            int r;
            int addr;             /* Address just past Gosub coded below */
            int iSet = ((ii==pOrWc->nTerm-1)?-1:ii);
            if( HasRowid(pTab) ){
              r = sqlite3ExprCodeGetColumn(pParse, pTab, -1, iCur, regRowid, 0);

              addr = sqlite3VdbeCurrentAddr(v)+2;
              sqlite3VdbeAddOp4Int(v, OP_RowSetTest, regRowset, addr, r, iSet);

              VdbeCoverage(v);
            }else{
              Index *pPk = sqlite3PrimaryKeyIndex(pTab);
              int nPk = pPk->nKeyCol;
              int iPk;

              /* Read the PK into an array of temp registers. */
              r = sqlite3GetTempRange(pParse, nPk);
              for(iPk=0; iPk<nPk; iPk++){
                int iCol = pPk->aiColumn[iPk];
                sqlite3ExprCodeGetColumn(pParse, pTab, iCol, iCur, r+iPk, 0);
              }

              /* Check if the temp table already contains this key. If so,
              ** the row has already been included in the result set and
              ** can be ignored (by jumping past the Gosub below). Otherwise,
              ** insert the key into the temp table and proceed with processing
              ** the row.
              **
              ** Use some of the same optimizations as OP_RowSetTest: If iSet
              ** is zero, assume that the key cannot already be present in
              ** the temp table. And if iSet is -1, assume that there is no 
              ** need to insert the key into the temp table, as it will never 
              ** be tested for.  */ 
              if( iSet ){
                addr = sqlite3VdbeCurrentAddr(v) + 2 + ((iSet>0) ? 2 : 0);
                sqlite3VdbeAddOp4Int(v, OP_Found, regRowset, addr, r, nPk);
              }
              if( iSet>=0 ){
                sqlite3VdbeAddOp3(v, OP_MakeRecord, r, nPk, regRowid);
                sqlite3VdbeAddOp3(v, OP_IdxInsert, regRowset, regRowid, 0);
                if( iSet ) sqlite3VdbeChangeP5(v, OPFLAG_USESEEKRESULT);
              }

              /* Release the array of temp registers */
              sqlite3ReleaseTempRange(pParse, r, nPk);
            }
            assert( db->mallocFailed 
                 || iSet==0 || addr==(sqlite3VdbeCurrentAddr(v)+1) );
          }
          sqlite3VdbeAddOp2(v, OP_Gosub, regReturn, iLoopBody);

          /* The pSubWInfo->untestedTerms flag means that this OR term
          ** contained one or more AND term from a notReady table.  The
          ** terms from the notReady table could not be tested and will
          ** need to be tested later.
4762
4763
4764
4765
4766
4767
4768
4769
4770
4771
4772
4773
4774
4775
4776
  
  pWC = pBuilder->pWC;
  if( pWInfo->wctrlFlags & WHERE_AND_ONLY ) return SQLITE_OK;
  pWCEnd = pWC->a + pWC->nTerm;
  pNew = pBuilder->pNew;
  memset(&sSum, 0, sizeof(sSum));
  pItem = pWInfo->pTabList->a + pNew->iTab;
  if( !HasRowid(pItem->pTab) ) return SQLITE_OK;
  iCur = pItem->iCursor;

  for(pTerm=pWC->a; pTerm<pWCEnd && rc==SQLITE_OK; pTerm++){
    if( (pTerm->eOperator & WO_OR)!=0
     && (pTerm->u.pOrInfo->indexable & pNew->maskSelf)!=0 
    ){
      WhereClause * const pOrWC = &pTerm->u.pOrInfo->wc;







<







4809
4810
4811
4812
4813
4814
4815

4816
4817
4818
4819
4820
4821
4822
  
  pWC = pBuilder->pWC;
  if( pWInfo->wctrlFlags & WHERE_AND_ONLY ) return SQLITE_OK;
  pWCEnd = pWC->a + pWC->nTerm;
  pNew = pBuilder->pNew;
  memset(&sSum, 0, sizeof(sSum));
  pItem = pWInfo->pTabList->a + pNew->iTab;

  iCur = pItem->iCursor;

  for(pTerm=pWC->a; pTerm<pWCEnd && rc==SQLITE_OK; pTerm++){
    if( (pTerm->eOperator & WO_OR)!=0
     && (pTerm->u.pOrInfo->indexable & pNew->maskSelf)!=0 
    ){
      WhereClause * const pOrWC = &pTerm->u.pOrInfo->wc;
Added test/whereI.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
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
# 2014-03-31
#
# 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.
#
#***********************************************************************
# The focus of this file is testing the OR optimization on WITHOUT ROWID 
# tables.
#

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

do_execsql_test 1.0 {
  CREATE TABLE t1(a, b, c, PRIMARY KEY(a)) WITHOUT ROWID;
  INSERT INTO t1 VALUES(1, 'a', 'z');
  INSERT INTO t1 VALUES(2, 'b', 'y');
  INSERT INTO t1 VALUES(3, 'c', 'x');
  INSERT INTO t1 VALUES(4, 'd', 'w');
  CREATE INDEX i1 ON t1(b);
  CREATE INDEX i2 ON t1(c);
}

do_eqp_test 1.1 {
  SELECT a FROM t1 WHERE b='b' OR c='x'
} {
  0 0 0 {SEARCH TABLE t1 USING INDEX i1 (b=?)} 
  0 0 0 {SEARCH TABLE t1 USING INDEX i2 (c=?)}
}

do_execsql_test 1.2 {
  SELECT a FROM t1 WHERE b='b' OR c='x'
} {2 3}

do_execsql_test 1.3 {
  SELECT a FROM t1 WHERE b='a' OR c='z'
} {1}

#----------------------------------------------------------------------
# Try that again, this time with non integer PRIMARY KEY values.
#
do_execsql_test 2.0 {
  CREATE TABLE t2(a, b, c, PRIMARY KEY(a)) WITHOUT ROWID;
  INSERT INTO t2 VALUES('i', 'a', 'z');
  INSERT INTO t2 VALUES('ii', 'b', 'y');
  INSERT INTO t2 VALUES('iii', 'c', 'x');
  INSERT INTO t2 VALUES('iv', 'd', 'w');
  CREATE INDEX i3 ON t2(b);
  CREATE INDEX i4 ON t2(c);
}

do_eqp_test 2.1 {
  SELECT a FROM t2 WHERE b='b' OR c='x'
} {
  0 0 0 {SEARCH TABLE t2 USING INDEX i3 (b=?)} 
  0 0 0 {SEARCH TABLE t2 USING INDEX i4 (c=?)}
}

do_execsql_test 2.2 {
  SELECT a FROM t2 WHERE b='b' OR c='x'
} {ii iii}

do_execsql_test 2.3 {
  SELECT a FROM t2 WHERE b='a' OR c='z'
} {i}

finish_test