/ Check-in [5ba4e5ad]
Login

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

Overview
Comment:Reuse registers better in the inner loop of a SELECT statement. (CVS 4719)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:5ba4e5adf638c6812a2813961ea30a11a670409c
User & Date: drh 2008-01-17 17:15:56
Context
2008-01-17
17:27
Remove unused p4 element from the {quote: VdbeOpList} object. (CVS 4720) check-in: 1c37d7b6 user: drh tags: trunk
17:15
Reuse registers better in the inner loop of a SELECT statement. (CVS 4719) check-in: 5ba4e5ad user: drh tags: trunk
16:22
Registerification of the VDBE is complete. The operand stack has been removed from the code. All instructions operate out of registers only. (CVS 4718) check-in: 706b41b7 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/expr.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
....
2158
2159
2160
2161
2162
2163
2164
2165
2166
2167
2168
2169
2170
2171
2172
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains routines used for analyzing expressions and
** for generating VDBE code that evaluates expressions in SQLite.
**
** $Id: expr.c,v 1.347 2008/01/17 16:22:15 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** Return the 'affinity' of the expression pExpr if any.
**
................................................................................
      pDef = sqlite3FindFunction(pParse->db, zId, nId, nExpr, enc, 0);
      assert( pDef!=0 );
      if( pList ){
        nExpr = pList->nExpr;
        r1 = sqlite3GetTempRange(pParse, nExpr);
        sqlite3ExprCodeExprList(pParse, pList, r1);
      }else{
        nExpr = 0;
      }
#ifndef SQLITE_OMIT_VIRTUALTABLE
      /* Possibly overload the function if the first argument is
      ** a virtual table column.
      **
      ** For infix functions (LIKE, GLOB, REGEXP, and MATCH) use the
      ** second argument, not the first, as the argument to test to







|







 







|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
....
2158
2159
2160
2161
2162
2163
2164
2165
2166
2167
2168
2169
2170
2171
2172
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains routines used for analyzing expressions and
** for generating VDBE code that evaluates expressions in SQLite.
**
** $Id: expr.c,v 1.348 2008/01/17 17:15:56 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** Return the 'affinity' of the expression pExpr if any.
**
................................................................................
      pDef = sqlite3FindFunction(pParse->db, zId, nId, nExpr, enc, 0);
      assert( pDef!=0 );
      if( pList ){
        nExpr = pList->nExpr;
        r1 = sqlite3GetTempRange(pParse, nExpr);
        sqlite3ExprCodeExprList(pParse, pList, r1);
      }else{
        nExpr = r1 = 0;
      }
#ifndef SQLITE_OMIT_VIRTUALTABLE
      /* Possibly overload the function if the first argument is
      ** a virtual table column.
      **
      ** For infix functions (LIKE, GLOB, REGEXP, and MATCH) use the
      ** second argument, not the first, as the argument to test to

Changes to src/select.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
512
513
514
515
516
517
518
519
520
521
522
523


524
525
526
527
528
529
530
...
532
533
534
535
536
537
538
539
540
541
542
543
544

545
546
547

548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
...
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
...
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
...
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
...
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
...
684
685
686
687
688
689
690
691
692
693
694

695
696
697
698
699
700
701
702
703
704
...
714
715
716
717
718
719
720

721
722
723
724
725
726
727
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle SELECT statements in SQLite.
**
** $Id: select.c,v 1.404 2008/01/17 16:22:15 drh Exp $
*/
#include "sqliteInt.h"


/*
** Delete all the content of a Select structure but do not deallocate
** the select structure itself.
................................................................................
  int distinct,           /* If >=0, make sure results are distinct */
  SelectDest *pDest,      /* How to dispose of the results */
  int iContinue,          /* Jump here to continue with next row */
  int iBreak,             /* Jump here to break out of the inner loop */
  char *aff               /* affinity string if eDest is SRT_Union */
){
  Vdbe *v = pParse->pVdbe;
  int i, n;
  int hasDistinct;        /* True if the DISTINCT keyword is present */
  int iMem;               /* Start of memory holding result set */
  int eDest = pDest->eDest;
  int iParm = pDest->iParm;



  if( v==0 ) return 0;
  assert( pEList!=0 );

  /* If there was a LIMIT clause on the SELECT statement, then do the check
  ** to see if this row should be output.
  */
................................................................................
  if( pOrderBy==0 && !hasDistinct ){
    codeOffset(v, p, iContinue);
  }

  /* Pull the requested columns.
  */
  if( nColumn>0 ){
    n = nColumn;
  }else{
    n = pEList->nExpr;
  }
  if( pDest->iMem>0 ){
    iMem = pDest->iMem;

  }else{
    pDest->iMem = iMem = pParse->nMem+1;
    pParse->nMem += n;

  }
  if( nColumn>0 ){
    for(i=0; i<nColumn; i++){
      sqlite3VdbeAddOp3(v, OP_Column, srcTab, i, iMem+i);
    }
  }else if( eDest!=SRT_Exists ){
    /* If the destination is an EXISTS(...) expression, the actual
    ** values returned by the SELECT are not required.
    */
    for(i=0; i<n; i++){
      sqlite3ExprCode(pParse, pEList->a[i].pExpr, iMem+i);
    }
  }
  nColumn = n;

  /* If the DISTINCT keyword was present on the SELECT statement
  ** and this row has been seen before, then do not make this row
  ** part of the result.
  */
  if( hasDistinct ){
    assert( pEList!=0 );
    assert( pEList->nExpr==nColumn );
    codeDistinct(pParse, distinct, iContinue, nColumn, iMem);
    if( pOrderBy==0 ){
      codeOffset(v, p, iContinue);
    }
  }

  if( checkForMultiColumnSelectError(pParse, pDest, pEList->nExpr) ){
    return 0;
................................................................................
    /* In this mode, write each query result to the key of the temporary
    ** table iParm.
    */
#ifndef SQLITE_OMIT_COMPOUND_SELECT
    case SRT_Union: {
      int r1;
      r1 = sqlite3GetTempReg(pParse);
      sqlite3VdbeAddOp3(v, OP_MakeRecord, iMem, nColumn, r1);
      if( aff ){
        sqlite3VdbeChangeP4(v, -1, aff, P4_STATIC);
      }
      sqlite3VdbeAddOp2(v, OP_IdxInsert, iParm, r1);
      sqlite3ReleaseTempReg(pParse, r1);
      break;
    }
................................................................................
    /* Construct a record from the query result, but instead of
    ** saving that record, use it as a key to delete elements from
    ** the temporary table iParm.
    */
    case SRT_Except: {
      int addr, r1;
      r1 = sqlite3GetTempReg(pParse);
      addr = sqlite3VdbeAddOp3(v, OP_MakeRecord, iMem, nColumn, r1);
      sqlite3VdbeChangeP4(v, -1, aff, P4_STATIC);
      sqlite3VdbeAddOp3(v, OP_NotFound, iParm, addr+3, r1);
      sqlite3VdbeAddOp1(v, OP_Delete, iParm);
      sqlite3ReleaseTempReg(pParse, r1);
      break;
    }
#endif

    /* Store the result as data using a unique key.
    */
    case SRT_Table:
    case SRT_EphemTab: {
      int r1 = sqlite3GetTempReg(pParse);
      sqlite3VdbeAddOp3(v, OP_MakeRecord, iMem, nColumn, r1);
      if( pOrderBy ){
        pushOntoSorter(pParse, pOrderBy, p, r1);
      }else{
        int r2 = sqlite3GetTempReg(pParse);
        sqlite3VdbeAddOp2(v, OP_NewRowid, iParm, r2);
        sqlite3VdbeAddOp3(v, OP_Insert, iParm, r1, r2);
        sqlite3VdbeChangeP5(v, OPFLAG_APPEND);
................................................................................
    ** then there should be a single item on the stack.  Write this
    ** item into the set table with bogus data.
    */
    case SRT_Set: {
      int addr2;

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

................................................................................
    /* If this is a scalar select that is part of an expression, then
    ** store the results in the appropriate memory cell and break out
    ** of the scan loop.
    */
    case SRT_Mem: {
      assert( nColumn==1 );
      if( pOrderBy ){
        pushOntoSorter(pParse, pOrderBy, p, iMem);
      }else{
        sqlite3VdbeAddOp2(v, OP_Move, iMem, iParm);
        /* The LIMIT clause will jump out of the loop for us */
      }
      break;
    }
#endif /* #ifndef SQLITE_OMIT_SUBQUERY */

    /* Send the data to the callback function or to a subroutine.  In the
................................................................................
    ** case of a subroutine, the subroutine itself is responsible for
    ** popping the data from the stack.
    */
    case SRT_Subroutine:
    case SRT_Callback: {
      if( pOrderBy ){
        int r1 = sqlite3GetTempReg(pParse);
        sqlite3VdbeAddOp3(v, OP_MakeRecord, iMem, nColumn, r1);
        pushOntoSorter(pParse, pOrderBy, p, r1);
        sqlite3ReleaseTempReg(pParse, r1);
      }else if( eDest==SRT_Subroutine ){

        sqlite3VdbeAddOp2(v, OP_Gosub, 0, iParm);
      }else{
        sqlite3VdbeAddOp2(v, OP_ResultRow, iMem, nColumn);
      }
      break;
    }

#if !defined(SQLITE_OMIT_TRIGGER)
    /* Discard the results.  This is used for SELECT statements inside
    ** the body of a TRIGGER.  The purpose of such selects is to call
................................................................................

  /* Jump to the end of the loop if the LIMIT is reached.
  */
  if( p->iLimit>=0 && pOrderBy==0 ){
    sqlite3VdbeAddOp2(v, OP_AddImm, p->iLimit, -1);
    sqlite3VdbeAddOp2(v, OP_IfZero, p->iLimit, iBreak);
  }

  return 0;
}

/*
** Given an expression list, generate a KeyInfo structure that records
** the collating sequence for each expression in that expression list.
**







|







 







|

|
|
|
>
>







 







|

|


|
>

<
|
>



|





|
|


|








|







 







|







 







|













|







 







|






|


|







 







|

|







 







|



>


|







 







>







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
...
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548

549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
...
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
...
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
...
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
...
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
...
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
...
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle SELECT statements in SQLite.
**
** $Id: select.c,v 1.405 2008/01/17 17:15:56 drh Exp $
*/
#include "sqliteInt.h"


/*
** Delete all the content of a Select structure but do not deallocate
** the select structure itself.
................................................................................
  int distinct,           /* If >=0, make sure results are distinct */
  SelectDest *pDest,      /* How to dispose of the results */
  int iContinue,          /* Jump here to continue with next row */
  int iBreak,             /* Jump here to break out of the inner loop */
  char *aff               /* affinity string if eDest is SRT_Union */
){
  Vdbe *v = pParse->pVdbe;
  int i;
  int hasDistinct;        /* True if the DISTINCT keyword is present */
  int regResult;              /* Start of memory holding result set */
  int eDest = pDest->eDest;   /* How to dispose of results */
  int iParm = pDest->iParm;   /* First argument to disposal method */
  int nResultCol;             /* Number of result columns */
  int nToFree;                /* Number of result columns to release */

  if( v==0 ) return 0;
  assert( pEList!=0 );

  /* If there was a LIMIT clause on the SELECT statement, then do the check
  ** to see if this row should be output.
  */
................................................................................
  if( pOrderBy==0 && !hasDistinct ){
    codeOffset(v, p, iContinue);
  }

  /* Pull the requested columns.
  */
  if( nColumn>0 ){
    nResultCol = nColumn;
  }else{
    nResultCol = pEList->nExpr;
  }
  if( pDest->iMem>0 ){
    regResult = pDest->iMem;
    nToFree = 0;
  }else{

    pDest->iMem = regResult = sqlite3GetTempRange(pParse, nResultCol);
    nToFree = nResultCol;
  }
  if( nColumn>0 ){
    for(i=0; i<nColumn; i++){
      sqlite3VdbeAddOp3(v, OP_Column, srcTab, i, regResult+i);
    }
  }else if( eDest!=SRT_Exists ){
    /* If the destination is an EXISTS(...) expression, the actual
    ** values returned by the SELECT are not required.
    */
    for(i=0; i<nResultCol; i++){
      sqlite3ExprCode(pParse, pEList->a[i].pExpr, regResult+i);
    }
  }
  nColumn = nResultCol;

  /* If the DISTINCT keyword was present on the SELECT statement
  ** and this row has been seen before, then do not make this row
  ** part of the result.
  */
  if( hasDistinct ){
    assert( pEList!=0 );
    assert( pEList->nExpr==nColumn );
    codeDistinct(pParse, distinct, iContinue, nColumn, regResult);
    if( pOrderBy==0 ){
      codeOffset(v, p, iContinue);
    }
  }

  if( checkForMultiColumnSelectError(pParse, pDest, pEList->nExpr) ){
    return 0;
................................................................................
    /* In this mode, write each query result to the key of the temporary
    ** table iParm.
    */
#ifndef SQLITE_OMIT_COMPOUND_SELECT
    case SRT_Union: {
      int r1;
      r1 = sqlite3GetTempReg(pParse);
      sqlite3VdbeAddOp3(v, OP_MakeRecord, regResult, nColumn, r1);
      if( aff ){
        sqlite3VdbeChangeP4(v, -1, aff, P4_STATIC);
      }
      sqlite3VdbeAddOp2(v, OP_IdxInsert, iParm, r1);
      sqlite3ReleaseTempReg(pParse, r1);
      break;
    }
................................................................................
    /* Construct a record from the query result, but instead of
    ** saving that record, use it as a key to delete elements from
    ** the temporary table iParm.
    */
    case SRT_Except: {
      int addr, r1;
      r1 = sqlite3GetTempReg(pParse);
      addr = sqlite3VdbeAddOp3(v, OP_MakeRecord, regResult, nColumn, r1);
      sqlite3VdbeChangeP4(v, -1, aff, P4_STATIC);
      sqlite3VdbeAddOp3(v, OP_NotFound, iParm, addr+3, r1);
      sqlite3VdbeAddOp1(v, OP_Delete, iParm);
      sqlite3ReleaseTempReg(pParse, r1);
      break;
    }
#endif

    /* Store the result as data using a unique key.
    */
    case SRT_Table:
    case SRT_EphemTab: {
      int r1 = sqlite3GetTempReg(pParse);
      sqlite3VdbeAddOp3(v, OP_MakeRecord, regResult, nColumn, r1);
      if( pOrderBy ){
        pushOntoSorter(pParse, pOrderBy, p, r1);
      }else{
        int r2 = sqlite3GetTempReg(pParse);
        sqlite3VdbeAddOp2(v, OP_NewRowid, iParm, r2);
        sqlite3VdbeAddOp3(v, OP_Insert, iParm, r1, r2);
        sqlite3VdbeChangeP5(v, OPFLAG_APPEND);
................................................................................
    ** then there should be a single item on the stack.  Write this
    ** item into the set table with bogus data.
    */
    case SRT_Set: {
      int addr2;

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

................................................................................
    /* If this is a scalar select that is part of an expression, then
    ** store the results in the appropriate memory cell and break out
    ** of the scan loop.
    */
    case SRT_Mem: {
      assert( nColumn==1 );
      if( pOrderBy ){
        pushOntoSorter(pParse, pOrderBy, p, regResult);
      }else{
        sqlite3VdbeAddOp2(v, OP_Move, regResult, iParm);
        /* The LIMIT clause will jump out of the loop for us */
      }
      break;
    }
#endif /* #ifndef SQLITE_OMIT_SUBQUERY */

    /* Send the data to the callback function or to a subroutine.  In the
................................................................................
    ** case of a subroutine, the subroutine itself is responsible for
    ** popping the data from the stack.
    */
    case SRT_Subroutine:
    case SRT_Callback: {
      if( pOrderBy ){
        int r1 = sqlite3GetTempReg(pParse);
        sqlite3VdbeAddOp3(v, OP_MakeRecord, regResult, nColumn, r1);
        pushOntoSorter(pParse, pOrderBy, p, r1);
        sqlite3ReleaseTempReg(pParse, r1);
      }else if( eDest==SRT_Subroutine ){
        nToFree = 0;  /* Preserve registers. Subroutine will need them. */
        sqlite3VdbeAddOp2(v, OP_Gosub, 0, iParm);
      }else{
        sqlite3VdbeAddOp2(v, OP_ResultRow, regResult, nColumn);
      }
      break;
    }

#if !defined(SQLITE_OMIT_TRIGGER)
    /* Discard the results.  This is used for SELECT statements inside
    ** the body of a TRIGGER.  The purpose of such selects is to call
................................................................................

  /* Jump to the end of the loop if the LIMIT is reached.
  */
  if( p->iLimit>=0 && pOrderBy==0 ){
    sqlite3VdbeAddOp2(v, OP_AddImm, p->iLimit, -1);
    sqlite3VdbeAddOp2(v, OP_IfZero, p->iLimit, iBreak);
  }
  sqlite3ReleaseTempRange(pParse, regResult, nToFree);
  return 0;
}

/*
** Given an expression list, generate a KeyInfo structure that records
** the collating sequence for each expression in that expression list.
**