SQLite

Check-in [986efb7b12]
Login

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

Overview
Comment:Add the experimental EXPLAIN QUERY PLAN diagnostic capability. (CVS 2685)
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 986efb7b12643800805ad4b1f1e90e30fcf6d38a
User & Date: drh 2005-09-10 16:46:13.000
Context
2005-09-10
22:40
The shell does not output the sqlite_stat1 table on .dump or .schema. The ANALYZE command now gathers statistics on tables that have only a single index because this sometimes helps when reordering tables in a join. (CVS 2686) (check-in: 26565b8931 user: drh tags: trunk)
16:46
Add the experimental EXPLAIN QUERY PLAN diagnostic capability. (CVS 2685) (check-in: 986efb7b12 user: drh tags: trunk)
15:35
Avoid a memory leak and/or assertion failure when parsing a table declaration that contains a duplicate column name. Ticket #1418. (CVS 2684) (check-in: f43427742b user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/parse.y.
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
**
*************************************************************************
** This file contains SQLite's grammar for SQL.  Process this file
** using the lemon parser generator to generate C code that runs
** the parser.  Lemon will also generate a header file containing
** numeric codes for all of the tokens.
**
** @(#) $Id: parse.y,v 1.177 2005/09/09 01:33:19 drh Exp $
*/

// All token codes are small integers with #defines that begin with "TK_"
%token_prefix TK_

// The type of the data attached to each token is Token.  This is also the
// default type for non-terminals.







|







10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
**
*************************************************************************
** This file contains SQLite's grammar for SQL.  Process this file
** using the lemon parser generator to generate C code that runs
** the parser.  Lemon will also generate a header file containing
** numeric codes for all of the tokens.
**
** @(#) $Id: parse.y,v 1.178 2005/09/10 16:46:13 drh Exp $
*/

// All token codes are small integers with #defines that begin with "TK_"
%token_prefix TK_

// The type of the data attached to each token is Token.  This is also the
// default type for non-terminals.
100
101
102
103
104
105
106
107

108
109
110
111
112
113
114
cmdlist ::= cmdlist ecmd.
cmdlist ::= ecmd.
cmdx ::= cmd.           { sqlite3FinishCoding(pParse); }
ecmd ::= SEMI.
ecmd ::= explain cmdx SEMI.
explain ::= .           { sqlite3BeginParse(pParse, 0); }
%ifndef SQLITE_OMIT_EXPLAIN
explain ::= EXPLAIN.    { sqlite3BeginParse(pParse, 1); }

%endif

///////////////////// Begin and end transactions. ////////////////////////////
//

cmd ::= BEGIN transtype(Y) trans_opt.  {sqlite3BeginTransaction(pParse, Y);}
trans_opt ::= .







|
>







100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
cmdlist ::= cmdlist ecmd.
cmdlist ::= ecmd.
cmdx ::= cmd.           { sqlite3FinishCoding(pParse); }
ecmd ::= SEMI.
ecmd ::= explain cmdx SEMI.
explain ::= .           { sqlite3BeginParse(pParse, 0); }
%ifndef SQLITE_OMIT_EXPLAIN
explain ::= EXPLAIN.              { sqlite3BeginParse(pParse, 1); }
explain ::= EXPLAIN QUERY PLAN.   { sqlite3BeginParse(pParse, 2); }
%endif

///////////////////// Begin and end transactions. ////////////////////////////
//

cmd ::= BEGIN transtype(Y) trans_opt.  {sqlite3BeginTransaction(pParse, Y);}
trans_opt ::= .
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
// The following directive causes tokens ABORT, AFTER, ASC, etc. to
// fallback to ID if they will not parse as their original value.
// This obviates the need for the "id" nonterminal.
//
%fallback ID
  ABORT AFTER ANALYZE ASC ATTACH BEFORE BEGIN CASCADE CAST CONFLICT
  DATABASE DEFERRED DESC DETACH EACH END EXCLUSIVE EXPLAIN FAIL FOR
  IGNORE IMMEDIATE INITIALLY INSTEAD LIKE_KW MATCH KEY
  OF OFFSET PRAGMA RAISE REPLACE RESTRICT ROW STATEMENT
  TEMP TRIGGER VACUUM VIEW
%ifdef SQLITE_OMIT_COMPOUND_SELECT
  EXCEPT INTERSECT UNION
%endif
  REINDEX RENAME CTIME_KW ALTER
  .







|







169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
// The following directive causes tokens ABORT, AFTER, ASC, etc. to
// fallback to ID if they will not parse as their original value.
// This obviates the need for the "id" nonterminal.
//
%fallback ID
  ABORT AFTER ANALYZE ASC ATTACH BEFORE BEGIN CASCADE CAST CONFLICT
  DATABASE DEFERRED DESC DETACH EACH END EXCLUSIVE EXPLAIN FAIL FOR
  IGNORE IMMEDIATE INITIALLY INSTEAD LIKE_KW MATCH PLAN QUERY KEY
  OF OFFSET PRAGMA RAISE REPLACE RESTRICT ROW STATEMENT
  TEMP TRIGGER VACUUM VIEW
%ifdef SQLITE_OMIT_COMPOUND_SELECT
  EXCEPT INTERSECT UNION
%endif
  REINDEX RENAME CTIME_KW ALTER
  .
Changes to src/prepare.c.
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains the implementation of the sqlite3_prepare()
** interface, and routines that contribute to loading the database schema
** from disk.
**
** $Id: prepare.c,v 1.3 2005/08/19 00:14:42 drh Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>

/*
** Fill the InitData structure with an error message that indicates







|







9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains the implementation of the sqlite3_prepare()
** interface, and routines that contribute to loading the database schema
** from disk.
**
** $Id: prepare.c,v 1.4 2005/09/10 16:46:13 drh Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>

/*
** Fill the InitData structure with an error message that indicates
454
455
456
457
458
459
460






461
462
463
464
465
466

467
468
469
470
471
472
473
    sqlite3ResetInternalSchema(db, 0);
  }
  if( pzTail ) *pzTail = sParse.zTail;
  rc = sParse.rc;

#ifndef SQLITE_OMIT_EXPLAIN
  if( rc==SQLITE_OK && sParse.pVdbe && sParse.explain ){






    sqlite3VdbeSetNumCols(sParse.pVdbe, 5);
    sqlite3VdbeSetColName(sParse.pVdbe, 0, "addr", P3_STATIC);
    sqlite3VdbeSetColName(sParse.pVdbe, 1, "opcode", P3_STATIC);
    sqlite3VdbeSetColName(sParse.pVdbe, 2, "p1", P3_STATIC);
    sqlite3VdbeSetColName(sParse.pVdbe, 3, "p2", P3_STATIC);
    sqlite3VdbeSetColName(sParse.pVdbe, 4, "p3", P3_STATIC);

  } 
#endif

prepare_out:
  if( sqlite3SafetyOff(db) ){
    rc = SQLITE_MISUSE;
  }







>
>
>
>
>
>
|
|
|
|
|
|
>







454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
    sqlite3ResetInternalSchema(db, 0);
  }
  if( pzTail ) *pzTail = sParse.zTail;
  rc = sParse.rc;

#ifndef SQLITE_OMIT_EXPLAIN
  if( rc==SQLITE_OK && sParse.pVdbe && sParse.explain ){
    if( sParse.explain==2 ){
      sqlite3VdbeSetNumCols(sParse.pVdbe, 3);
      sqlite3VdbeSetColName(sParse.pVdbe, 0, "order", P3_STATIC);
      sqlite3VdbeSetColName(sParse.pVdbe, 1, "from", P3_STATIC);
      sqlite3VdbeSetColName(sParse.pVdbe, 2, "detail", P3_STATIC);
    }else{
      sqlite3VdbeSetNumCols(sParse.pVdbe, 5);
      sqlite3VdbeSetColName(sParse.pVdbe, 0, "addr", P3_STATIC);
      sqlite3VdbeSetColName(sParse.pVdbe, 1, "opcode", P3_STATIC);
      sqlite3VdbeSetColName(sParse.pVdbe, 2, "p1", P3_STATIC);
      sqlite3VdbeSetColName(sParse.pVdbe, 3, "p2", P3_STATIC);
      sqlite3VdbeSetColName(sParse.pVdbe, 4, "p3", P3_STATIC);
    }
  } 
#endif

prepare_out:
  if( sqlite3SafetyOff(db) ){
    rc = SQLITE_MISUSE;
  }
Changes to src/vdbe.c.
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
**
** Various scripts scan this source file in order to generate HTML
** documentation, headers files, or other derived files.  The formatting
** of the code in this file is, therefore, important.  See other comments
** in this file for details.  If in doubt, do not deviate from existing
** commenting and indentation practices when changing or adding code.
**
** $Id: vdbe.c,v 1.485 2005/09/08 14:17:20 drh Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>
#include "vdbeInt.h"

/*







|







39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
**
** Various scripts scan this source file in order to generate HTML
** documentation, headers files, or other derived files.  The formatting
** of the code in this file is, therefore, important.  See other comments
** in this file for details.  If in doubt, do not deviate from existing
** commenting and indentation practices when changing or adding code.
**
** $Id: vdbe.c,v 1.486 2005/09/10 16:46:13 drh Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>
#include "vdbeInt.h"

/*
1641
1642
1643
1644
1645
1646
1647







1648
1649
1650
1651
1652
1653
1654
}

/* Opcode: Noop * * *
**
** Do nothing.  This instruction is often useful as a jump
** destination.
*/







case OP_Noop: {            /* no-push */
  break;
}

/* Opcode: If P1 P2 *
**
** Pop a single boolean from the stack.  If the boolean popped is







>
>
>
>
>
>
>







1641
1642
1643
1644
1645
1646
1647
1648
1649
1650
1651
1652
1653
1654
1655
1656
1657
1658
1659
1660
1661
}

/* Opcode: Noop * * *
**
** Do nothing.  This instruction is often useful as a jump
** destination.
*/
/*
** The magic Explain opcode are only inserted when explain==2 (which
** is to say when the EXPLAIN QUERY PLAN syntax is used.)
** This opcode records information from the optimizer.  It is the
** the same as a no-op.  This opcodesnever appears in a real VM program.
*/
case OP_Explain:
case OP_Noop: {            /* no-push */
  break;
}

/* Opcode: If P1 P2 *
**
** Pop a single boolean from the stack.  If the boolean popped is
Changes to src/vdbeaux.c.
573
574
575
576
577
578
579
580
581

582
583
584
585
586
587
588
  ** sqlite3_column_text16(), causing a translation to UTF-16 encoding.
  */
  if( p->pTos==&p->aStack[4] ){
    releaseMemArray(p->aStack, 5);
  }
  p->resOnStack = 0;


  i = p->pc++;

  if( i>=p->nOp ){
    p->rc = SQLITE_OK;
    rc = SQLITE_DONE;
  }else if( db->flags & SQLITE_Interrupt ){
    db->flags &= ~SQLITE_Interrupt;
    p->rc = SQLITE_INTERRUPT;
    rc = SQLITE_ERROR;







|
|
>







573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
  ** sqlite3_column_text16(), causing a translation to UTF-16 encoding.
  */
  if( p->pTos==&p->aStack[4] ){
    releaseMemArray(p->aStack, 5);
  }
  p->resOnStack = 0;

  do{
    i = p->pc++;
  }while( i<p->nOp && p->explain==2 && p->aOp[i].opcode!=OP_Explain );
  if( i>=p->nOp ){
    p->rc = SQLITE_OK;
    rc = SQLITE_DONE;
  }else if( db->flags & SQLITE_Interrupt ){
    db->flags &= ~SQLITE_Interrupt;
    p->rc = SQLITE_INTERRUPT;
    rc = SQLITE_ERROR;
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
    pMem++;

    pMem->flags = MEM_Short|MEM_Str|MEM_Term;   /* P3 */
    pMem->z = displayP3(pOp, pMem->zShort, sizeof(pMem->zShort));
    pMem->type = SQLITE_TEXT;
    pMem->enc = SQLITE_UTF8;

    p->nResColumn = 5;
    p->pTos = pMem;
    p->rc = SQLITE_OK;
    p->resOnStack = 1;
    rc = SQLITE_ROW;
  }
  return rc;
}







|







614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
    pMem++;

    pMem->flags = MEM_Short|MEM_Str|MEM_Term;   /* P3 */
    pMem->z = displayP3(pOp, pMem->zShort, sizeof(pMem->zShort));
    pMem->type = SQLITE_TEXT;
    pMem->enc = SQLITE_UTF8;

    p->nResColumn = 5 - 2*(p->explain-1);
    p->pTos = pMem;
    p->rc = SQLITE_OK;
    p->resOnStack = 1;
    rc = SQLITE_ROW;
  }
  return rc;
}
Changes to src/where.c.
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
** This module contains C code that generates VDBE code used to process
** the WHERE clause of SQL statements.  This module is reponsible for
** generating the code that loops through a table looking for applicable
** rows.  Indices are selected and used to speed the search when doing
** so is applicable.  Because this module is responsible for selecting
** indices, you might also think of this module as the "query optimizer".
**
** $Id: where.c,v 1.170 2005/09/10 15:28:09 drh Exp $
*/
#include "sqliteInt.h"

/*
** The number of bits in a Bitmask.  "BMS" means "BitMask Size".
*/
#define BMS  (sizeof(Bitmask)*8)







|







12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
** This module contains C code that generates VDBE code used to process
** the WHERE clause of SQL statements.  This module is reponsible for
** generating the code that loops through a table looking for applicable
** rows.  Indices are selected and used to speed the search when doing
** so is applicable.  Because this module is responsible for selecting
** indices, you might also think of this module as the "query optimizer".
**
** $Id: where.c,v 1.171 2005/09/10 16:46:13 drh Exp $
*/
#include "sqliteInt.h"

/*
** The number of bits in a Bitmask.  "BMS" means "BitMask Size".
*/
#define BMS  (sizeof(Bitmask)*8)
1495
1496
1497
1498
1499
1500
1501














1502
1503
1504
1505
1506
1507
1508
  sqlite3CodeVerifySchema(pParse, -1); /* Insert the cookie verifier Goto */
  pLevel = pWInfo->a;
  for(i=0, pLevel=pWInfo->a; i<pTabList->nSrc; i++, pLevel++){
    Table *pTab;
    Index *pIx;
    int iIdxCur = pLevel->iIdxCur;















    pTabItem = &pTabList->a[pLevel->iFrom];
    pTab = pTabItem->pTab;
    if( pTab->isTransient || pTab->pSelect ) continue;
    if( (pLevel->flags & WHERE_IDX_ONLY)==0 ){
      sqlite3OpenTableForReading(v, pTabItem->iCursor, pTab);
    }
    pLevel->iTabCur = pTabItem->iCursor;







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







1495
1496
1497
1498
1499
1500
1501
1502
1503
1504
1505
1506
1507
1508
1509
1510
1511
1512
1513
1514
1515
1516
1517
1518
1519
1520
1521
1522
  sqlite3CodeVerifySchema(pParse, -1); /* Insert the cookie verifier Goto */
  pLevel = pWInfo->a;
  for(i=0, pLevel=pWInfo->a; i<pTabList->nSrc; i++, pLevel++){
    Table *pTab;
    Index *pIx;
    int iIdxCur = pLevel->iIdxCur;

#ifndef SQLITE_OMIT_EXPLAIN
    if( pParse->explain==2 ){
      char *zMsg;
      struct SrcList_item *pItem = &pTabList->a[pLevel->iFrom];
      zMsg = sqlite3MPrintf("TABLE %s", pItem->zName);
      if( pItem->zAlias ){
        zMsg = sqlite3MPrintf("%z AS %s", zMsg, pItem->zAlias);
      }
      if( (pIx = pLevel->pIdx)!=0 ){
        zMsg = sqlite3MPrintf("%z WITH INDEX %s", zMsg, pIx->zName);
      }
      sqlite3VdbeOp3(v, OP_Explain, i, pLevel->iFrom, zMsg, P3_DYNAMIC);
    }
#endif /* SQLITE_OMIT_EXPLAIN */
    pTabItem = &pTabList->a[pLevel->iFrom];
    pTab = pTabItem->pTab;
    if( pTab->isTransient || pTab->pSelect ) continue;
    if( (pLevel->flags & WHERE_IDX_ONLY)==0 ){
      sqlite3OpenTableForReading(v, pTabItem->iCursor, pTab);
    }
    pLevel->iTabCur = pTabItem->iCursor;
Changes to tool/mkkeywordhash.c.
189
190
191
192
193
194
195

196
197

198
199
200
201
202
203
204
  { "NULL",             "TK_NULL",         ALWAYS                 },
  { "OF",               "TK_OF",           ALWAYS                 },
  { "OFFSET",           "TK_OFFSET",       ALWAYS                 },
  { "ON",               "TK_ON",           ALWAYS                 },
  { "OR",               "TK_OR",           ALWAYS                 },
  { "ORDER",            "TK_ORDER",        ALWAYS                 },
  { "OUTER",            "TK_JOIN_KW",      ALWAYS                 },

  { "PRAGMA",           "TK_PRAGMA",       PRAGMA                 },
  { "PRIMARY",          "TK_PRIMARY",      ALWAYS                 },

  { "RAISE",            "TK_RAISE",        TRIGGER                },
  { "REFERENCES",       "TK_REFERENCES",   FKEY                   },
  { "REGEXP",           "TK_LIKE_KW",      ALWAYS                 },
  { "REINDEX",          "TK_REINDEX",      REINDEX                },
  { "RENAME",           "TK_RENAME",       ALTER                  },
  { "REPLACE",          "TK_REPLACE",      CONFLICT               },
  { "RESTRICT",         "TK_RESTRICT",     FKEY                   },







>


>







189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
  { "NULL",             "TK_NULL",         ALWAYS                 },
  { "OF",               "TK_OF",           ALWAYS                 },
  { "OFFSET",           "TK_OFFSET",       ALWAYS                 },
  { "ON",               "TK_ON",           ALWAYS                 },
  { "OR",               "TK_OR",           ALWAYS                 },
  { "ORDER",            "TK_ORDER",        ALWAYS                 },
  { "OUTER",            "TK_JOIN_KW",      ALWAYS                 },
  { "PLAN",             "TK_PLAN",         EXPLAIN                },
  { "PRAGMA",           "TK_PRAGMA",       PRAGMA                 },
  { "PRIMARY",          "TK_PRIMARY",      ALWAYS                 },
  { "QUERY",            "TK_QUERY",        EXPLAIN                },
  { "RAISE",            "TK_RAISE",        TRIGGER                },
  { "REFERENCES",       "TK_REFERENCES",   FKEY                   },
  { "REGEXP",           "TK_LIKE_KW",      ALWAYS                 },
  { "REINDEX",          "TK_REINDEX",      REINDEX                },
  { "RENAME",           "TK_RENAME",       ALTER                  },
  { "REPLACE",          "TK_REPLACE",      CONFLICT               },
  { "RESTRICT",         "TK_RESTRICT",     FKEY                   },