/ Check-in [fbd17a89]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:Minor optimizations for sub-selects in EXISTS(...) expressions: Discard any DISTINCT or ORDER BY clauses and do not calculate any column values. (CVS 4658)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: fbd17a8976cc9b4dd7c7c903d8beade9a7ef095f
User & Date: danielk1977 2008-01-02 17:11:14
Context
2008-01-02
17:25
Add operands p4 and p5 to the VdbeOp structure. (CVS 4659) check-in: ad528735 user: drh tags: trunk
17:11
Minor optimizations for sub-selects in EXISTS(...) expressions: Discard any DISTINCT or ORDER BY clauses and do not calculate any column values. (CVS 4658) check-in: fbd17a89 user: danielk1977 tags: trunk
16:27
Combine the eDest and iParm arguments to sqlite3Select() into a single type - "SelectDest". (CVS 4657) check-in: 86dcdfe6 user: danielk1977 tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/select.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
544
545
546
547
548
549
550
551



552
553
554
555
556
557
558
....
3139
3140
3141
3142
3143
3144
3145







3146
3147
3148
3149
3150
3151
3152
**    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.376 2008/01/02 16:27:10 danielk1977 Exp $
*/
#include "sqliteInt.h"


/*
** Delete all the content of a Select structure but do not deallocate
** the select structure itself.
................................................................................
  iMem = pParse->nMem;
  pParse->nMem += n+1;
  sqlite3VdbeAddOp(v, OP_MemInt, n, iMem);
  if( nColumn>0 ){
    for(i=0; i<nColumn; i++){
      sqlite3VdbeOp3Int(v, OP_Column, srcTab, i, iMem+i+1);
    }
  }else{



    for(i=0; i<n; i++){
      sqlite3ExprIntoReg(pParse, pEList->a[i].pExpr, iMem+i+1);
    }
  }
  nColumn = n;

  /* If the DISTINCT keyword was present on the SELECT statement
................................................................................
  }
  if( sqlite3AuthCheck(pParse, SQLITE_SELECT, 0, 0, 0) ) return 1;
  memset(&sAggInfo, 0, sizeof(sAggInfo));

  pOrderBy = p->pOrderBy;
  if( IgnorableOrderby(pDest) ){
    p->pOrderBy = 0;







  }
  if( sqlite3SelectResolve(pParse, p, 0) ){
    goto select_end;
  }
  p->pOrderBy = pOrderBy;

#ifndef SQLITE_OMIT_COMPOUND_SELECT







|







 







|
>
>
>







 







>
>
>
>
>
>
>







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
....
3142
3143
3144
3145
3146
3147
3148
3149
3150
3151
3152
3153
3154
3155
3156
3157
3158
3159
3160
3161
3162
**    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.377 2008/01/02 17:11:14 danielk1977 Exp $
*/
#include "sqliteInt.h"


/*
** Delete all the content of a Select structure but do not deallocate
** the select structure itself.
................................................................................
  iMem = pParse->nMem;
  pParse->nMem += n+1;
  sqlite3VdbeAddOp(v, OP_MemInt, n, iMem);
  if( nColumn>0 ){
    for(i=0; i<nColumn; i++){
      sqlite3VdbeOp3Int(v, OP_Column, srcTab, i, iMem+i+1);
    }
  }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++){
      sqlite3ExprIntoReg(pParse, pEList->a[i].pExpr, iMem+i+1);
    }
  }
  nColumn = n;

  /* If the DISTINCT keyword was present on the SELECT statement
................................................................................
  }
  if( sqlite3AuthCheck(pParse, SQLITE_SELECT, 0, 0, 0) ) return 1;
  memset(&sAggInfo, 0, sizeof(sAggInfo));

  pOrderBy = p->pOrderBy;
  if( IgnorableOrderby(pDest) ){
    p->pOrderBy = 0;

    /* In these cases the DISTINCT operator makes no difference to the
    ** results, so remove it if it were specified.
    */
    assert(pDest->eDest==SRT_Exists || pDest->eDest==SRT_Union || 
           pDest->eDest==SRT_Except || pDest->eDest==SRT_Discard);
    p->isDistinct = 0;
  }
  if( sqlite3SelectResolve(pParse, p, 0) ){
    goto select_end;
  }
  p->pOrderBy = pOrderBy;

#ifndef SQLITE_OMIT_COMPOUND_SELECT

Changes to src/sqliteInt.h.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
....
1326
1327
1328
1329
1330
1331
1332

1333
1334
1335
1336
1337
1338
1339
1340
1341
1342
1343
1344
1345
1346
1347
1348
1349
1350
1351
**    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.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.629 2008/01/02 16:27:10 danielk1977 Exp $
*/
#ifndef _SQLITEINT_H_
#define _SQLITEINT_H_

/*
** The macro unlikely() is a hint that surrounds a boolean
** expression that is usually false.  Macro likely() surrounds
................................................................................
};

/*
** The results of a select can be distributed in several ways.
*/
#define SRT_Union        1  /* Store result as keys in an index */
#define SRT_Except       2  /* Remove result from a UNION index */

#define SRT_Discard      3  /* Do not save the results anywhere */

/* The ORDER BY clause is ignored for all of the above */
#define IgnorableOrderby(X) ((X->eDest)<=SRT_Discard)

#define SRT_Callback     4  /* Invoke a callback with each row of result */
#define SRT_Mem          5  /* Store result in a memory cell */
#define SRT_Set          6  /* Store non-null results as keys in an index */
#define SRT_Table        7  /* Store result as data with an automatic rowid */
#define SRT_EphemTab     8  /* Create transient tab and store like SRT_Table */
#define SRT_Subroutine   9  /* Call a subroutine to handle results */
#define SRT_Exists      10  /* Store 1 if the result is not empty */

/*
** A structure used to customize the behaviour of sqlite3Select(). See
** comments above sqlite3Select() for details.
*/
typedef struct SelectDest SelectDest;
struct SelectDest {







|







 







>
|




|
|
|
|
|
|
<







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
....
1326
1327
1328
1329
1330
1331
1332
1333
1334
1335
1336
1337
1338
1339
1340
1341
1342
1343
1344

1345
1346
1347
1348
1349
1350
1351
**    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.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.630 2008/01/02 17:11:14 danielk1977 Exp $
*/
#ifndef _SQLITEINT_H_
#define _SQLITEINT_H_

/*
** The macro unlikely() is a hint that surrounds a boolean
** expression that is usually false.  Macro likely() surrounds
................................................................................
};

/*
** The results of a select can be distributed in several ways.
*/
#define SRT_Union        1  /* Store result as keys in an index */
#define SRT_Except       2  /* Remove result from a UNION index */
#define SRT_Exists       3  /* Store 1 if the result is not empty */
#define SRT_Discard      4  /* Do not save the results anywhere */

/* The ORDER BY clause is ignored for all of the above */
#define IgnorableOrderby(X) ((X->eDest)<=SRT_Discard)

#define SRT_Callback     5  /* Invoke a callback with each row of result */
#define SRT_Mem          6  /* Store result in a memory cell */
#define SRT_Set          7  /* Store non-null results as keys in an index */
#define SRT_Table        8  /* Store result as data with an automatic rowid */
#define SRT_EphemTab     9  /* Create transient tab and store like SRT_Table */
#define SRT_Subroutine  10  /* Call a subroutine to handle results */


/*
** A structure used to customize the behaviour of sqlite3Select(). See
** comments above sqlite3Select() for details.
*/
typedef struct SelectDest SelectDest;
struct SelectDest {