/ Check-in [578490c9]
Login
☰Home Timeline Branches

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

Overview
Comment:Fix bugs in the new query plan instrumention logic. (CVS 2549)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 578490c91331a386f84652db0b3bfd74c82046e1
User & Date: drh 2005-07-15 23:24:24
Context
2005-07-16
13:33
Allow an unlimited number of terms in the WHERE clause. The old limit was 100. (CVS 2550) (check-in: ca69f368 user: drh tags: trunk)
2005-07-15
23:24
Fix bugs in the new query plan instrumention logic. (CVS 2549) (check-in: 578490c9 user: drh tags: trunk)
13:05
Add test instrumentation that will be needed during the development of forthcoming optimizer enhancements. (CVS 2548) (check-in: dfd5fd77 user: drh tags: trunk)
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/test1.c. 

9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
....
3090
3091
3092
3093
3094
3095
3096

3097
3098
3099
3100
3101
3102
3103
....
3111
3112
3113
3114
3115
3116
3117
3118
3119
3120
3121
3122
3123
3124
3125

**    May you share freely, never taking more than you give.
**
*************************************************************************
** Code for testing the printf() interface to SQLite.  This code
** is not included in the SQLite library.  It is used for automated
** testing of the SQLite library.
**
** $Id: test1.c,v 1.148 2005/07/15 13:05:21 drh Exp $
*/
#include "sqliteInt.h"
#include "tcl.h"
#include "os.h"
#include <stdlib.h>
#include <string.h>

................................................................................
  };
  static int bitmask_size = sizeof(Bitmask)*8;
  int i;
  extern int sqlite3_os_trace;
  extern int sqlite3_sync_count, sqlite3_fullsync_count;
  extern int sqlite3_opentemp_count;
  extern char sqlite3_query_plan[];


  for(i=0; i<sizeof(aCmd)/sizeof(aCmd[0]); i++){
    Tcl_CreateCommand(interp, aCmd[i].zName, aCmd[i].xProc, 0, 0);
  }
  for(i=0; i<sizeof(aObjCmd)/sizeof(aObjCmd[0]); i++){
    Tcl_CreateObjCommand(interp, aObjCmd[i].zName, 
        aObjCmd[i].xProc, aObjCmd[i].clientData, 0);
................................................................................
  Tcl_LinkVar(interp, "sqlite_open_file_count", 
      (char*)&sqlite3_open_file_count, TCL_LINK_INT);
  Tcl_LinkVar(interp, "sqlite_current_time", 
      (char*)&sqlite3_current_time, TCL_LINK_INT);
  Tcl_LinkVar(interp, "sqlite_os_trace",
      (char*)&sqlite3_os_trace, TCL_LINK_INT);
  Tcl_LinkVar(interp, "sqlite_query_plan",
      sqlite3_query_plan, TCL_LINK_STRING|TCL_LINK_READ_ONLY);
#ifndef SQLITE_OMIT_DISKIO
  Tcl_LinkVar(interp, "sqlite_opentemp_count",
      (char*)&sqlite3_opentemp_count, TCL_LINK_INT);
#endif
  Tcl_LinkVar(interp, "sqlite_static_bind_value",
      (char*)&sqlite_static_bind_value, TCL_LINK_STRING);
  Tcl_LinkVar(interp, "sqlite_temp_directory",








|







 







>







 







|








9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
....
3090
3091
3092
3093
3094
3095
3096
3097
3098
3099
3100
3101
3102
3103
3104
....
3112
3113
3114
3115
3116
3117
3118
3119
3120
3121
3122
3123
3124
3125
3126

**    May you share freely, never taking more than you give.
**
*************************************************************************
** Code for testing the printf() interface to SQLite.  This code
** is not included in the SQLite library.  It is used for automated
** testing of the SQLite library.
**
** $Id: test1.c,v 1.149 2005/07/15 23:24:24 drh Exp $
*/
#include "sqliteInt.h"
#include "tcl.h"
#include "os.h"
#include <stdlib.h>
#include <string.h>

................................................................................
  };
  static int bitmask_size = sizeof(Bitmask)*8;
  int i;
  extern int sqlite3_os_trace;
  extern int sqlite3_sync_count, sqlite3_fullsync_count;
  extern int sqlite3_opentemp_count;
  extern char sqlite3_query_plan[];
  static char *query_plan = sqlite3_query_plan;

  for(i=0; i<sizeof(aCmd)/sizeof(aCmd[0]); i++){
    Tcl_CreateCommand(interp, aCmd[i].zName, aCmd[i].xProc, 0, 0);
  }
  for(i=0; i<sizeof(aObjCmd)/sizeof(aObjCmd[0]); i++){
    Tcl_CreateObjCommand(interp, aObjCmd[i].zName, 
        aObjCmd[i].xProc, aObjCmd[i].clientData, 0);
................................................................................
  Tcl_LinkVar(interp, "sqlite_open_file_count", 
      (char*)&sqlite3_open_file_count, TCL_LINK_INT);
  Tcl_LinkVar(interp, "sqlite_current_time", 
      (char*)&sqlite3_current_time, TCL_LINK_INT);
  Tcl_LinkVar(interp, "sqlite_os_trace",
      (char*)&sqlite3_os_trace, TCL_LINK_INT);
  Tcl_LinkVar(interp, "sqlite_query_plan",
      (char*)&query_plan, TCL_LINK_STRING|TCL_LINK_READ_ONLY);
#ifndef SQLITE_OMIT_DISKIO
  Tcl_LinkVar(interp, "sqlite_opentemp_count",
      (char*)&sqlite3_opentemp_count, TCL_LINK_INT);
#endif
  Tcl_LinkVar(interp, "sqlite_static_bind_value",
      (char*)&sqlite_static_bind_value, TCL_LINK_STRING);
  Tcl_LinkVar(interp, "sqlite_temp_directory",

Changes to src/where.c. 

12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
...
964
965
966
967
968
969
970
971
972
973
974



975
976

977
978
979

980

981
982
983
984
985
986
987

** 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.143 2005/07/15 13:05:21 drh Exp $
*/
#include "sqliteInt.h"

/*
** The query generator uses an array of instances of this structure to
** help it analyze the subexpressions of the WHERE clause.  Each WHERE
** clause subexpression is separated from the others by an AND operator.
................................................................................
      sqlite3VdbeAddOp(v, OP_SetNumColumns, iIdxCur, pIx->nColumn+1);
    }
    sqlite3CodeVerifySchema(pParse, pTab->iDb);

#ifdef SQLITE_TEST
    /* Record in the query plan information about the current table
    ** and the index used to access it (if any).  If the table itself
    ** is not used, its name is followed by '*'.  If no index is used
    ** the index is listed as "{}"
    */
    {



      int n = strlen(pTab->zName);
      if( n+nQPlan < sizeof(sqlite3_query_plan)-10 ){

        strcpy(&sqlite3_query_plan[nQPlan], pTab->zName);
        nQPlan += n;
        if( (pLevel->score & 1)==0 ){

          sqlite3_query_plan[nQPlan++] = '*';

        }
        sqlite3_query_plan[nQPlan++] = ' ';
      }
      if( pIx==0 ){
        strcpy(&sqlite3_query_plan[nQPlan], " {}");
        nQPlan += 3;
      }else{








|







 







|



>
>
>
|

>
|
|
<
>
|
>








12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
...
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982

983
984
985
986
987
988
989
990
991
992

** 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.144 2005/07/15 23:24:24 drh Exp $
*/
#include "sqliteInt.h"

/*
** The query generator uses an array of instances of this structure to
** help it analyze the subexpressions of the WHERE clause.  Each WHERE
** clause subexpression is separated from the others by an AND operator.
................................................................................
      sqlite3VdbeAddOp(v, OP_SetNumColumns, iIdxCur, pIx->nColumn+1);
    }
    sqlite3CodeVerifySchema(pParse, pTab->iDb);

#ifdef SQLITE_TEST
    /* Record in the query plan information about the current table
    ** and the index used to access it (if any).  If the table itself
    ** is not used, its name is just '{}'.  If no index is used
    ** the index is listed as "{}"
    */
    {
      char *z = pTabItem->zAlias;
      int n;
      if( z==0 ) z = pTab->zName;
      n = strlen(z);
      if( n+nQPlan < sizeof(sqlite3_query_plan)-10 ){
        if( (pLevel->score & 1)!=0 ){
          strcpy(&sqlite3_query_plan[nQPlan], "{}");
          nQPlan += 2;

        }else{
          strcpy(&sqlite3_query_plan[nQPlan], z);
          nQPlan += n;
        }
        sqlite3_query_plan[nQPlan++] = ' ';
      }
      if( pIx==0 ){
        strcpy(&sqlite3_query_plan[nQPlan], " {}");
        nQPlan += 3;
      }else{

Changes to test/where.test. 

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
..
61
62
63
64
65
66
67



68
69
70
71
72
73
74
75
76



77
78
79



80
81
82
83
84
85
86
87
88







89
90
91
92
93
94
95

#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the use of indices in WHERE clases.
#
# $Id: where.test,v 1.29 2005/06/06 17:27:19 drh Exp $

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

# Build some test data
#
do_test where-1.0 {
................................................................................
# "sqlite_search_count" which tallys the number of executions of MoveTo
# and Next operators in the VDBE.  By verifing that the search count is
# small we can be assured that indices are being used properly.
#
do_test where-1.1 {
  count {SELECT x, y FROM t1 WHERE w=10}
} {3 121 3}



do_test where-1.2 {
  count {SELECT x, y FROM t1 WHERE w=11}
} {3 144 3}
do_test where-1.3 {
  count {SELECT x, y FROM t1 WHERE 11=w}
} {3 144 3}
do_test where-1.4 {
  count {SELECT x, y FROM t1 WHERE 11=w AND x>2}
} {3 144 3}



do_test where-1.5 {
  count {SELECT x, y FROM t1 WHERE y<200 AND w=11 AND x>2}
} {3 144 3}



do_test where-1.6 {
  count {SELECT x, y FROM t1 WHERE y<200 AND x>2 AND w=11}
} {3 144 3}
do_test where-1.7 {
  count {SELECT x, y FROM t1 WHERE w=11 AND y<200 AND x>2}
} {3 144 3}
do_test where-1.8 {
  count {SELECT x, y FROM t1 WHERE w>10 AND y=144 AND x=3}
} {3 144 3}







do_test where-1.9 {
  count {SELECT x, y FROM t1 WHERE y=144 AND w>10 AND x=3}
} {3 144 3}
do_test where-1.10 {
  count {SELECT x, y FROM t1 WHERE x=3 AND w>=10 AND y=121}
} {3 121 3}
do_test where-1.11 {








|







 







>
>
>









>
>
>



>
>
>









>
>
>
>
>
>
>








7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
..
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
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

#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the use of indices in WHERE clases.
#
# $Id: where.test,v 1.30 2005/07/15 23:24:25 drh Exp $

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

# Build some test data
#
do_test where-1.0 {
................................................................................
# "sqlite_search_count" which tallys the number of executions of MoveTo
# and Next operators in the VDBE.  By verifing that the search count is
# small we can be assured that indices are being used properly.
#
do_test where-1.1 {
  count {SELECT x, y FROM t1 WHERE w=10}
} {3 121 3}
do_test where-1.1.2 {
  set sqlite_query_plan
} {t1 i1w}
do_test where-1.2 {
  count {SELECT x, y FROM t1 WHERE w=11}
} {3 144 3}
do_test where-1.3 {
  count {SELECT x, y FROM t1 WHERE 11=w}
} {3 144 3}
do_test where-1.4 {
  count {SELECT x, y FROM t1 WHERE 11=w AND x>2}
} {3 144 3}
do_test where-1.4.2 {
   set sqlite_query_plan
} {t1 i1w}
do_test where-1.5 {
  count {SELECT x, y FROM t1 WHERE y<200 AND w=11 AND x>2}
} {3 144 3}
do_test where-1.5.2 {
  set sqlite_query_plan
} {t1 i1w}
do_test where-1.6 {
  count {SELECT x, y FROM t1 WHERE y<200 AND x>2 AND w=11}
} {3 144 3}
do_test where-1.7 {
  count {SELECT x, y FROM t1 WHERE w=11 AND y<200 AND x>2}
} {3 144 3}
do_test where-1.8 {
  count {SELECT x, y FROM t1 WHERE w>10 AND y=144 AND x=3}
} {3 144 3}
do_test where-1.8.2 {
  set sqlite_query_plan
} {t1 i1xy}
do_test where-1.8.3 {
  count {SELECT x, y FROM t1 WHERE y=144 AND x=3}
  set sqlite_query_plan
} {{} i1xy}
do_test where-1.9 {
  count {SELECT x, y FROM t1 WHERE y=144 AND w>10 AND x=3}
} {3 144 3}
do_test where-1.10 {
  count {SELECT x, y FROM t1 WHERE x=3 AND w>=10 AND y=121}
} {3 121 3}
do_test where-1.11 {

This page was generated in about 0.051s by Fossil 2.12 [9ec3274f35] 2020-08-08 18:19:35