SQLite

Check-in [c0c4b6b396]
Login

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

Overview
Comment:When creating an automatic-index on a sub-query, add a unique integer to the end of each index key to ensure the entire key is unique. Fix for [8a2adec1].
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | branch-3.9
Files: files | file ages | folders
SHA1: c0c4b6b39648be9aa9b1e218e6d281ab17812536
User & Date: drh 2015-11-02 11:19:06.607
Context
2015-11-02
18:31
Version 3.9.2 (check-in: bda77dda96 user: drh tags: release, branch-3.9, version-3.9.2)
11:19
When creating an automatic-index on a sub-query, add a unique integer to the end of each index key to ensure the entire key is unique. Fix for [8a2adec1]. (check-in: c0c4b6b396 user: drh tags: branch-3.9)
11:10
Aborted releaset of 3.9.2. (check-in: c33a275bf1 user: drh tags: branch-3.9)
2015-10-24
20:31
When creating an automatic-index on a sub-query, add a unique integer to the end of each index key to ensure the entire key is unique. Fix for [8a2adec1]. (check-in: bfea226d0d user: dan tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/where.c.
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
  return N<=10 ? 0 : sqlite3LogEst(N) - 33;
}

/*
** Convert OP_Column opcodes to OP_Copy in previously generated code.
**
** This routine runs over generated VDBE code and translates OP_Column
** opcodes into OP_Copy, and OP_Rowid into OP_Null, when the table is being
** accessed via co-routine instead of via table lookup.





*/
static void translateColumnToCopy(
  Vdbe *v,            /* The VDBE containing code to translate */
  int iStart,         /* Translate from this opcode to the end */
  int iTabCur,        /* OP_Column/OP_Rowid references to this table */
  int iRegister       /* The first column is in this register */

){
  VdbeOp *pOp = sqlite3VdbeGetOp(v, iStart);
  int iEnd = sqlite3VdbeCurrentAddr(v);
  for(; iStart<iEnd; iStart++, pOp++){
    if( pOp->p1!=iTabCur ) continue;
    if( pOp->opcode==OP_Column ){
      pOp->opcode = OP_Copy;
      pOp->p1 = pOp->p2 + iRegister;
      pOp->p2 = pOp->p3;
      pOp->p3 = 0;
    }else if( pOp->opcode==OP_Rowid ){






      pOp->opcode = OP_Null;
      pOp->p1 = 0;
      pOp->p3 = 0;

    }
  }
}

/*
** Two routines for printing the content of an sqlite3_index_info
** structure.  Used for testing and debugging only.  If neither







|
|
>
>
>
>
>





|
>











>
>
>
>
>
>
|
|
|
>







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
  return N<=10 ? 0 : sqlite3LogEst(N) - 33;
}

/*
** Convert OP_Column opcodes to OP_Copy in previously generated code.
**
** This routine runs over generated VDBE code and translates OP_Column
** opcodes into OP_Copy when the table is being accessed via co-routine 
** instead of via table lookup.
**
** If the bIncrRowid parameter is 0, then any OP_Rowid instructions on
** cursor iTabCur are transformed into OP_Null. Or, if bIncrRowid is non-zero,
** then each OP_Rowid is transformed into an instruction to increment the
** value stored in its output register.
*/
static void translateColumnToCopy(
  Vdbe *v,            /* The VDBE containing code to translate */
  int iStart,         /* Translate from this opcode to the end */
  int iTabCur,        /* OP_Column/OP_Rowid references to this table */
  int iRegister,      /* The first column is in this register */
  int bIncrRowid      /* If non-zero, transform OP_rowid to OP_AddImm(1) */
){
  VdbeOp *pOp = sqlite3VdbeGetOp(v, iStart);
  int iEnd = sqlite3VdbeCurrentAddr(v);
  for(; iStart<iEnd; iStart++, pOp++){
    if( pOp->p1!=iTabCur ) continue;
    if( pOp->opcode==OP_Column ){
      pOp->opcode = OP_Copy;
      pOp->p1 = pOp->p2 + iRegister;
      pOp->p2 = pOp->p3;
      pOp->p3 = 0;
    }else if( pOp->opcode==OP_Rowid ){
      if( bIncrRowid ){
        /* Increment the value stored in the P2 operand of the OP_Rowid. */
        pOp->opcode = OP_AddImm;
        pOp->p1 = pOp->p2;
        pOp->p2 = 1;
      }else{
        pOp->opcode = OP_Null;
        pOp->p1 = 0;
        pOp->p3 = 0;
      }
    }
  }
}

/*
** Two routines for printing the content of an sqlite3_index_info
** structure.  Used for testing and debugging only.  If neither
610
611
612
613
614
615
616


617
618
619
620
621
622
623
  char *zNotUsed;             /* Extra space on the end of pIdx */
  Bitmask idxCols;            /* Bitmap of columns used for indexing */
  Bitmask extraCols;          /* Bitmap of additional columns */
  u8 sentWarning = 0;         /* True if a warnning has been issued */
  Expr *pPartial = 0;         /* Partial Index Expression */
  int iContinue = 0;          /* Jump here to skip excluded rows */
  struct SrcList_item *pTabItem;  /* FROM clause term being indexed */



  /* Generate code to skip over the creation and initialization of the
  ** transient index on 2nd and subsequent iterations of the loop. */
  v = pParse->pVdbe;
  assert( v!=0 );
  addrInit = sqlite3CodeOnce(pParse); VdbeCoverage(v);








>
>







623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
  char *zNotUsed;             /* Extra space on the end of pIdx */
  Bitmask idxCols;            /* Bitmap of columns used for indexing */
  Bitmask extraCols;          /* Bitmap of additional columns */
  u8 sentWarning = 0;         /* True if a warnning has been issued */
  Expr *pPartial = 0;         /* Partial Index Expression */
  int iContinue = 0;          /* Jump here to skip excluded rows */
  struct SrcList_item *pTabItem;  /* FROM clause term being indexed */
  int addrCounter;            /* Address where integer counter is initialized */
  int regBase;                /* Array of registers where record is assembled */

  /* Generate code to skip over the creation and initialization of the
  ** transient index on 2nd and subsequent iterations of the loop. */
  v = pParse->pVdbe;
  assert( v!=0 );
  addrInit = sqlite3CodeOnce(pParse); VdbeCoverage(v);

738
739
740
741
742
743
744

745
746
747
748
749
750
751
752
753
754
755
756
757

758

759
760
761
762

763
764
765
766
767
768
769
770
  VdbeComment((v, "for %s", pTable->zName));

  /* Fill the automatic index with content */
  sqlite3ExprCachePush(pParse);
  pTabItem = &pWC->pWInfo->pTabList->a[pLevel->iFrom];
  if( pTabItem->fg.viaCoroutine ){
    int regYield = pTabItem->regReturn;

    sqlite3VdbeAddOp3(v, OP_InitCoroutine, regYield, 0, pTabItem->addrFillSub);
    addrTop =  sqlite3VdbeAddOp1(v, OP_Yield, regYield);
    VdbeCoverage(v);
    VdbeComment((v, "next row of \"%s\"", pTabItem->pTab->zName));
  }else{
    addrTop = sqlite3VdbeAddOp1(v, OP_Rewind, pLevel->iTabCur); VdbeCoverage(v);
  }
  if( pPartial ){
    iContinue = sqlite3VdbeMakeLabel(v);
    sqlite3ExprIfFalse(pParse, pPartial, iContinue, SQLITE_JUMPIFNULL);
    pLoop->wsFlags |= WHERE_PARTIALIDX;
  }
  regRecord = sqlite3GetTempReg(pParse);

  sqlite3GenerateIndexKey(pParse, pIdx, pLevel->iTabCur, regRecord, 0, 0, 0, 0);

  sqlite3VdbeAddOp2(v, OP_IdxInsert, pLevel->iIdxCur, regRecord);
  sqlite3VdbeChangeP5(v, OPFLAG_USESEEKRESULT);
  if( pPartial ) sqlite3VdbeResolveLabel(v, iContinue);
  if( pTabItem->fg.viaCoroutine ){

    translateColumnToCopy(v, addrTop, pLevel->iTabCur, pTabItem->regResult);
    sqlite3VdbeGoto(v, addrTop);
    pTabItem->fg.viaCoroutine = 0;
  }else{
    sqlite3VdbeAddOp2(v, OP_Next, pLevel->iTabCur, addrTop+1); VdbeCoverage(v);
  }
  sqlite3VdbeChangeP5(v, SQLITE_STMTSTATUS_AUTOINDEX);
  sqlite3VdbeJumpHere(v, addrTop);







>













>
|
>




>
|







753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
  VdbeComment((v, "for %s", pTable->zName));

  /* Fill the automatic index with content */
  sqlite3ExprCachePush(pParse);
  pTabItem = &pWC->pWInfo->pTabList->a[pLevel->iFrom];
  if( pTabItem->fg.viaCoroutine ){
    int regYield = pTabItem->regReturn;
    addrCounter = sqlite3VdbeAddOp2(v, OP_Integer, 0, 0);
    sqlite3VdbeAddOp3(v, OP_InitCoroutine, regYield, 0, pTabItem->addrFillSub);
    addrTop =  sqlite3VdbeAddOp1(v, OP_Yield, regYield);
    VdbeCoverage(v);
    VdbeComment((v, "next row of \"%s\"", pTabItem->pTab->zName));
  }else{
    addrTop = sqlite3VdbeAddOp1(v, OP_Rewind, pLevel->iTabCur); VdbeCoverage(v);
  }
  if( pPartial ){
    iContinue = sqlite3VdbeMakeLabel(v);
    sqlite3ExprIfFalse(pParse, pPartial, iContinue, SQLITE_JUMPIFNULL);
    pLoop->wsFlags |= WHERE_PARTIALIDX;
  }
  regRecord = sqlite3GetTempReg(pParse);
  regBase = sqlite3GenerateIndexKey(
      pParse, pIdx, pLevel->iTabCur, regRecord, 0, 0, 0, 0
  );
  sqlite3VdbeAddOp2(v, OP_IdxInsert, pLevel->iIdxCur, regRecord);
  sqlite3VdbeChangeP5(v, OPFLAG_USESEEKRESULT);
  if( pPartial ) sqlite3VdbeResolveLabel(v, iContinue);
  if( pTabItem->fg.viaCoroutine ){
    sqlite3VdbeChangeP2(v, addrCounter, regBase+n);
    translateColumnToCopy(v, addrTop, pLevel->iTabCur, pTabItem->regResult, 1);
    sqlite3VdbeGoto(v, addrTop);
    pTabItem->fg.viaCoroutine = 0;
  }else{
    sqlite3VdbeAddOp2(v, OP_Next, pLevel->iTabCur, addrTop+1); VdbeCoverage(v);
  }
  sqlite3VdbeChangeP5(v, SQLITE_STMTSTATUS_AUTOINDEX);
  sqlite3VdbeJumpHere(v, addrTop);
4505
4506
4507
4508
4509
4510
4511
4512
4513
4514
4515
4516
4517
4518
4519

    /* For a co-routine, change all OP_Column references to the table of
    ** the co-routine into OP_Copy of result contained in a register.
    ** OP_Rowid becomes OP_Null.
    */
    if( pTabItem->fg.viaCoroutine && !db->mallocFailed ){
      translateColumnToCopy(v, pLevel->addrBody, pLevel->iTabCur,
                            pTabItem->regResult);
      continue;
    }

    /* Close all of the cursors that were opened by sqlite3WhereBegin.
    ** Except, do not close cursors that will be reused by the OR optimization
    ** (WHERE_OMIT_OPEN_CLOSE).  And do not close the OP_OpenWrite cursors
    ** created for the ONEPASS optimization.







|







4524
4525
4526
4527
4528
4529
4530
4531
4532
4533
4534
4535
4536
4537
4538

    /* For a co-routine, change all OP_Column references to the table of
    ** the co-routine into OP_Copy of result contained in a register.
    ** OP_Rowid becomes OP_Null.
    */
    if( pTabItem->fg.viaCoroutine && !db->mallocFailed ){
      translateColumnToCopy(v, pLevel->addrBody, pLevel->iTabCur,
                            pTabItem->regResult, 0);
      continue;
    }

    /* Close all of the cursors that were opened by sqlite3WhereBegin.
    ** Except, do not close cursors that will be reused by the OR optimization
    ** (WHERE_OMIT_OPEN_CLOSE).  And do not close the OP_OpenWrite cursors
    ** created for the ONEPASS optimization.
Changes to test/autoindex5.test.
13
14
15
16
17
18
19

20
21
22
23
24
25
26
# focus of this script is testing automatic index creation logic,
# and specifically ensuring that automatic indexes can be used with
# co-routine subqueries.
#

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


# Schema is from the Debian security database
#
do_execsql_test autoindex5-1.0 {
  CREATE TABLE source_package_status
          (bug_name TEXT NOT NULL,
           package INTEGER NOT NULL,







>







13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
# focus of this script is testing automatic index creation logic,
# and specifically ensuring that automatic indexes can be used with
# co-routine subqueries.
#

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

# Schema is from the Debian security database
#
do_execsql_test autoindex5-1.0 {
  CREATE TABLE source_package_status
          (bug_name TEXT NOT NULL,
           package INTEGER NOT NULL,
99
100
101
102
103
104
105




















106
107
108
     sp.rowid = st.package
     AND st.bug_name = bugs.name
     AND ( st.bug_name LIKE 'CVE-%' OR st.bug_name LIKE 'TEMP-%' )
     AND ( sp.release = 'sid' OR sp.release = 'stretch' OR sp.release = 'jessie'
            OR sp.release = 'wheezy' OR sp.release = 'squeeze' )
  ORDER BY sp.name, st.bug_name, sp.release, sp.subrelease;
} {/SEARCH SUBQUERY 2 USING AUTOMATIC COVERING INDEX .bug_name=/}




















    

finish_test







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



100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
     sp.rowid = st.package
     AND st.bug_name = bugs.name
     AND ( st.bug_name LIKE 'CVE-%' OR st.bug_name LIKE 'TEMP-%' )
     AND ( sp.release = 'sid' OR sp.release = 'stretch' OR sp.release = 'jessie'
            OR sp.release = 'wheezy' OR sp.release = 'squeeze' )
  ORDER BY sp.name, st.bug_name, sp.release, sp.subrelease;
} {/SEARCH SUBQUERY 2 USING AUTOMATIC COVERING INDEX .bug_name=/}

#-------------------------------------------------------------------------
# Test that ticket [8a2adec1] has been fixed.
#
do_execsql_test 2.1 {
  CREATE TABLE one(o);
  INSERT INTO one DEFAULT VALUES;

  CREATE TABLE t1(x, z);
  INSERT INTO t1 VALUES('aaa', 4.0);
  INSERT INTO t1 VALUES('aaa', 4.0);
  CREATE VIEW vvv AS
    SELECT * FROM t1
    UNION ALL
    SELECT 0, 0 WHERE 0;

  SELECT (
      SELECT sum(z) FROM vvv WHERE x='aaa'
  ) FROM one;
} {8.0}
    

finish_test