SQLite4
Check-in [b9e5f3c6df]
Not logged in

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

Overview
Comment:Update the sorter logic to eliminate the need for pseudo-tables and remove the code that implemented them.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: b9e5f3c6df1cd09036a5da7cda2c8cefa474a1f5
User & Date: drh 2013-07-30 02:47:27
Context
2013-07-30
11:55
Reenable fts5 hooks in where.c. check-in: c748d90f94 user: dan tags: trunk
02:47
Update the sorter logic to eliminate the need for pseudo-tables and remove the code that implemented them. check-in: b9e5f3c6df user: drh tags: trunk
02:11
Provide more detail in the P4 column in EXPLAIN output. check-in: 34abc4149f user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/select.c.

930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
  iTab = pOrderBy->iECursor;
  regRow = sqlite4GetTempReg(pParse);
  if( eDest!=SRT_Output && eDest!=SRT_Coroutine ){
    regRowid = sqlite4GetTempReg(pParse);
  }

  if( p->selFlags & SF_UseSorter ){
    int regSortOut = ++pParse->nMem;
    int ptab2 = pParse->nTab++;
    sqlite4VdbeAddOp3(v, OP_OpenPseudo, ptab2, regSortOut, pOrderBy->nExpr+2);
    addr = 1 + sqlite4VdbeAddOp2(v, OP_SorterSort, iTab, addrBreak);
    codeOffset(v, p, addrContinue);
    sqlite4VdbeAddOp2(v, OP_SorterData, iTab, regSortOut);
    sqlite4VdbeAddOp3(v, OP_Column, ptab2, 0, regRow);
    sqlite4VdbeChangeP5(v, OPFLAG_CLEARCACHE);
  }else{
    addr = 1 + sqlite4VdbeAddOp2(v, OP_Sort, iTab, addrBreak);
    codeOffset(v, p, addrContinue);
    sqlite4VdbeAddOp3(v, OP_Column, iTab, 0, regRow);
  }
  switch( eDest ){







<
<
<


<
|







930
931
932
933
934
935
936



937
938

939
940
941
942
943
944
945
946
  iTab = pOrderBy->iECursor;
  regRow = sqlite4GetTempReg(pParse);
  if( eDest!=SRT_Output && eDest!=SRT_Coroutine ){
    regRowid = sqlite4GetTempReg(pParse);
  }

  if( p->selFlags & SF_UseSorter ){



    addr = 1 + sqlite4VdbeAddOp2(v, OP_SorterSort, iTab, addrBreak);
    codeOffset(v, p, addrContinue);

    sqlite4VdbeAddOp3(v, OP_Column, iTab, 0, regRow);
    sqlite4VdbeChangeP5(v, OPFLAG_CLEARCACHE);
  }else{
    addr = 1 + sqlite4VdbeAddOp2(v, OP_Sort, iTab, addrBreak);
    codeOffset(v, p, addrContinue);
    sqlite4VdbeAddOp3(v, OP_Column, iTab, 0, regRow);
  }
  switch( eDest ){

Changes to src/vdbe.c.

2810
2811
2812
2813
2814
2815
2816
2817
2818
2819
2820
2821
2822
2823
2824
2825
2826
2827
2828
2829
2830
2831
2832
2833
2834
2835
2836
2837
2838
2839
2840
2841
2842
2843
2844
2845
2846
2847
2848
2849
....
2969
2970
2971
2972
2973
2974
2975
2976
2977
2978
2979
2980
2981
2982
2983
....
3495
3496
3497
3498
3499
3500
3501
3502
3503
3504
3505
3506
3507
3508
3509
....
3551
3552
3553
3554
3555
3556
3557
3558
3559
3560
3561
3562
3563
3564
3565
....
3610
3611
3612
3613
3614
3615
3616
3617
3618
3619
3620
3621
3622
3623
3624
case OP_SorterOpen: {
  /* VdbeCursor *pCx; */
  pOp->opcode = OP_OpenEphemeral;
  pc--;
  break;
}

/* Opcode: OpenPseudo P1 P2 P3 * *
**
** Open a new cursor that points to a fake table that contains a single
** row of data.  The content of that one row is the content of memory
** register P2.  In other words, cursor P1 becomes an alias for the 
** MEM_Blob content contained in register P2.
**
** A pseudo-table created by this opcode is used to hold a single
** row output from the sorter so that the row can be decomposed into
** individual columns using the OP_Column opcode.  The OP_Column opcode
** is the only cursor opcode that works with a pseudo-table.
**
** P3 is the number of fields in the records that will be stored by
** the pseudo-table.
*/
case OP_OpenPseudo: {
  VdbeCursor *pCx;

  assert( pOp->p1>=0 );
  pCx = allocateCursor(p, pOp->p1, pOp->p3, -1, 0);
  if( pCx==0 ) goto no_mem;
  pCx->nullRow = 1;
  pCx->pPseudoTab = aMem + pOp->p2;
  break;
}

/* Opcode: Close P1 * * * *
**
** Close a cursor previously opened as P1.  If P1 is not
** currently open, this instruction is a no-op.
*/
case OP_Close: {
  assert( pOp->p1>=0 && pOp->p1<p->nCursor );
................................................................................
  pC->nullRow = 0;
  pC->sSeekKey.n = 0;
  pC->rowChnged = 1;

  assert( pOp->p1>=0 && pOp->p1<p->nCursor );
  assert( pOp->p2!=0 );
  assert( pC!=0 );
  assert( pC->pPseudoTab==0 );
  assert( OP_SeekLe == OP_SeekLt+1 );
  assert( OP_SeekGe == OP_SeekLt+2 );
  assert( OP_SeekGt == OP_SeekLt+3 );

  /* Encode a database key consisting of the contents of the P4 registers
  ** starting at register P3. Have the vdbecodec module allocate an extra
  ** free byte at the end of the database key (see below).  */
................................................................................

  /* Note that RowKey and RowData are really exactly the same instruction */
  assert( pOp->p1>=0 && pOp->p1<p->nCursor );
  pC = p->apCsr[pOp->p1];
  rc = sqlite4VdbeCursorMoveto(pC);
  if( rc!=SQLITE4_OK ) break;
  assert( pC->nullRow==0 );
  assert( pC->pPseudoTab==0 );
  assert( pC->pKVCur!=0 );
  pCrsr = pC->pKVCur;

  if( pOp->opcode==OP_RowKey ){
    rc = sqlite4KVCursorKey(pCrsr, &pData, &nData);
    if( pOp->p5 ){
      nData = sqlite4VdbeShortKey(pData, nData, 1, 0);
................................................................................

  pKey = &aMem[pOp->p2];
  aIncr = &aMem[pOp->p3];
  nTotal = pOp->p4.i;
  pC = p->apCsr[pOp->p1];
  assert( pC!=0 );
  assert( pC->nullRow==0 );
  assert( pC->pPseudoTab==0 );
  assert( pC->pKVCur!=0 );
  assert( pOp->p4type==P4_INT32 );

  rc = sqlite4KVCursorKey(pC->pKVCur, &pNew, &nNew);
  if( rc==SQLITE4_OK ){
    assert( pKey->flags & (MEM_Blob|MEM_Null) );
    if( pKey->flags & MEM_Blob ){
................................................................................
  sqlite4_num vNum;

  assert( pOp->p1>=0 && pOp->p1<p->nCursor );
  pC = p->apCsr[pOp->p1];
  rc = sqlite4VdbeCursorMoveto(pC);
  if( rc!=SQLITE4_OK ) break;
  assert( pC->sSeekKey.n==0 );
  assert( pC->pPseudoTab==0 );
  if( pC->nullRow ){
    pOut->flags = MEM_Null;
    break;
#ifndef SQLITE4_OMIT_VIRTUALTABLE
  }else if( pC->pVtabCursor ){
    pVtab = pC->pVtabCursor->pVtab;
    pModule = pVtab->pModule;







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







 







<







 







<







 







<







 







<







2810
2811
2812
2813
2814
2815
2816


























2817
2818
2819
2820
2821
2822
2823
....
2943
2944
2945
2946
2947
2948
2949

2950
2951
2952
2953
2954
2955
2956
....
3468
3469
3470
3471
3472
3473
3474

3475
3476
3477
3478
3479
3480
3481
....
3523
3524
3525
3526
3527
3528
3529

3530
3531
3532
3533
3534
3535
3536
....
3581
3582
3583
3584
3585
3586
3587

3588
3589
3590
3591
3592
3593
3594
case OP_SorterOpen: {
  /* VdbeCursor *pCx; */
  pOp->opcode = OP_OpenEphemeral;
  pc--;
  break;
}



























/* Opcode: Close P1 * * * *
**
** Close a cursor previously opened as P1.  If P1 is not
** currently open, this instruction is a no-op.
*/
case OP_Close: {
  assert( pOp->p1>=0 && pOp->p1<p->nCursor );
................................................................................
  pC->nullRow = 0;
  pC->sSeekKey.n = 0;
  pC->rowChnged = 1;

  assert( pOp->p1>=0 && pOp->p1<p->nCursor );
  assert( pOp->p2!=0 );
  assert( pC!=0 );

  assert( OP_SeekLe == OP_SeekLt+1 );
  assert( OP_SeekGe == OP_SeekLt+2 );
  assert( OP_SeekGt == OP_SeekLt+3 );

  /* Encode a database key consisting of the contents of the P4 registers
  ** starting at register P3. Have the vdbecodec module allocate an extra
  ** free byte at the end of the database key (see below).  */
................................................................................

  /* Note that RowKey and RowData are really exactly the same instruction */
  assert( pOp->p1>=0 && pOp->p1<p->nCursor );
  pC = p->apCsr[pOp->p1];
  rc = sqlite4VdbeCursorMoveto(pC);
  if( rc!=SQLITE4_OK ) break;
  assert( pC->nullRow==0 );

  assert( pC->pKVCur!=0 );
  pCrsr = pC->pKVCur;

  if( pOp->opcode==OP_RowKey ){
    rc = sqlite4KVCursorKey(pCrsr, &pData, &nData);
    if( pOp->p5 ){
      nData = sqlite4VdbeShortKey(pData, nData, 1, 0);
................................................................................

  pKey = &aMem[pOp->p2];
  aIncr = &aMem[pOp->p3];
  nTotal = pOp->p4.i;
  pC = p->apCsr[pOp->p1];
  assert( pC!=0 );
  assert( pC->nullRow==0 );

  assert( pC->pKVCur!=0 );
  assert( pOp->p4type==P4_INT32 );

  rc = sqlite4KVCursorKey(pC->pKVCur, &pNew, &nNew);
  if( rc==SQLITE4_OK ){
    assert( pKey->flags & (MEM_Blob|MEM_Null) );
    if( pKey->flags & MEM_Blob ){
................................................................................
  sqlite4_num vNum;

  assert( pOp->p1>=0 && pOp->p1<p->nCursor );
  pC = p->apCsr[pOp->p1];
  rc = sqlite4VdbeCursorMoveto(pC);
  if( rc!=SQLITE4_OK ) break;
  assert( pC->sSeekKey.n==0 );

  if( pC->nullRow ){
    pOut->flags = MEM_Null;
    break;
#ifndef SQLITE4_OMIT_VIRTUALTABLE
  }else if( pC->pVtabCursor ){
    pVtab = pC->pVtabCursor->pVtab;
    pModule = pVtab->pModule;

Changes to src/vdbeInt.h.

56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
  KeyInfo *pKeyInfo;    /* Info about index keys needed by index cursors */
  int iDb;              /* Index of cursor database in db->aDb[] (or -1) */
  int iRoot;            /* Root page of the table */
  int nField;           /* Number of fields in the header */
  Bool nullRow;         /* True if pointing to a row with no data */
  Bool rowChnged;       /* True if row has changed out from under pDecoder */
  i64 seqCount;         /* Sequence counter */
  Mem *pPseudoTab;      /* Register holding pseudotable content */
  VdbeSorter *pSorter;  /* Sorter object for OP_SorterOpen cursors */
  Fts5Cursor *pFts;     /* Fts5 cursor object (or NULL) */
  RowDecoder *pDecoder;              /* Decoder for row content */
  sqlite4_vtab_cursor *pVtabCursor;  /* The cursor for a virtual table */
  const sqlite4_module *pModule;     /* Module for cursor pVtabCursor */
  sqlite4_buffer sSeekKey;           /* Key for deferred seek */
};







<







56
57
58
59
60
61
62

63
64
65
66
67
68
69
  KeyInfo *pKeyInfo;    /* Info about index keys needed by index cursors */
  int iDb;              /* Index of cursor database in db->aDb[] (or -1) */
  int iRoot;            /* Root page of the table */
  int nField;           /* Number of fields in the header */
  Bool nullRow;         /* True if pointing to a row with no data */
  Bool rowChnged;       /* True if row has changed out from under pDecoder */
  i64 seqCount;         /* Sequence counter */

  VdbeSorter *pSorter;  /* Sorter object for OP_SorterOpen cursors */
  Fts5Cursor *pFts;     /* Fts5 cursor object (or NULL) */
  RowDecoder *pDecoder;              /* Decoder for row content */
  sqlite4_vtab_cursor *pVtabCursor;  /* The cursor for a virtual table */
  const sqlite4_module *pModule;     /* Module for cursor pVtabCursor */
  sqlite4_buffer sSeekKey;           /* Key for deferred seek */
};

Changes to src/vdbecodec.c.

85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
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
130
131
132
133
134
135
136
137
138
139
140
141
142
  if( pCur==0 ){
    rc = sqlite4KVCursorData(p->pKVCur, 0, -1, &p->a, &p->n);
    return rc;
  }
  if( pCur->rowChnged ){
    p->a = 0;
    p->aKey = 0;
    pCur->rowChnged = pCur->pPseudoTab!=0;
  }
  if( p->a ) return SQLITE4_OK;
  rc = sqlite4VdbeCursorMoveto(pCur);
  if( rc ) return rc;
  if( pCur->nullRow ){
    p->a = 0;
    p->n = 0;
    return SQLITE4_OK;
  }
  if( pCur->pKVCur ){
    rc = sqlite4KVCursorData(pCur->pKVCur, 0, -1, &p->a, &p->n);
  }else{
    Mem *pReg = pCur->pPseudoTab;
    assert( pReg!=0 );
    p->a = (const KVByteArray*)pReg->z;
    p->n = pReg->n;
    rc = SQLITE4_OK;
  }
  return rc;
}

/*
** Make sure the p->aKey and p->nKey fields are valid and current.
*/
static int decoderFetchKey(RowDecoder *p){
  VdbeCursor *pCur = p->pCur;
................................................................................
  int rc;
  if( pCur==0 ){
    rc = sqlite4KVCursorKey(p->pKVCur, &p->aKey, &p->nKey);
    return rc;
  }
  assert( p->a!=0 );
  if( p->aKey ) return SQLITE4_OK;
  if( pCur->pKVCur ){
    rc = sqlite4KVCursorKey(pCur->pKVCur, &p->aKey, &p->nKey);
  }else{
    Mem *pReg = pCur->pPseudoTab + 1;
    assert( pReg!=0 );
    p->aKey = (const KVByteArray*)pReg->z;
    p->nKey = pReg->n;
    rc = SQLITE4_OK;
  }
  return rc;
}

/*
** Decode a blob from a key.  The blob-key is in a[0] through a[n-1].
** xorMask is either 0x00 for ascending order or 0xff for descending.
** Store the blob in pOut.
*/







|









|
|
<
<
<
<
<
<
<
<







 







|
|
<
<
<
<
<
<
<
<







85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
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
  if( pCur==0 ){
    rc = sqlite4KVCursorData(p->pKVCur, 0, -1, &p->a, &p->n);
    return rc;
  }
  if( pCur->rowChnged ){
    p->a = 0;
    p->aKey = 0;
    pCur->rowChnged = 0;
  }
  if( p->a ) return SQLITE4_OK;
  rc = sqlite4VdbeCursorMoveto(pCur);
  if( rc ) return rc;
  if( pCur->nullRow ){
    p->a = 0;
    p->n = 0;
    return SQLITE4_OK;
  }
  assert( pCur->pKVCur!=0 );
  return sqlite4KVCursorData(pCur->pKVCur, 0, -1, &p->a, &p->n);








}

/*
** Make sure the p->aKey and p->nKey fields are valid and current.
*/
static int decoderFetchKey(RowDecoder *p){
  VdbeCursor *pCur = p->pCur;
................................................................................
  int rc;
  if( pCur==0 ){
    rc = sqlite4KVCursorKey(p->pKVCur, &p->aKey, &p->nKey);
    return rc;
  }
  assert( p->a!=0 );
  if( p->aKey ) return SQLITE4_OK;
  assert( pCur->pKVCur!=0 );
  return sqlite4KVCursorKey(pCur->pKVCur, &p->aKey, &p->nKey);








}

/*
** Decode a blob from a key.  The blob-key is in a[0] through a[n-1].
** xorMask is either 0x00 for ascending order or 0xff for descending.
** Store the blob in pOut.
*/