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: |
578490c91331a386f84652db0b3bfd74 |
| 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
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 { |