SQLite

Check-in [ad8bc68197]
Login

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

Overview
Comment:Add the ability to disable constant factoring using sqlite3_test_control(). Add a TCL interface to this new capability and add tests cases to the TCL test scripts to actually use the new capability.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: ad8bc68197f2b47435149c3dbc035f4e7210fc76
User & Date: drh 2010-12-06 21:06:09.000
References
2010-12-16
19:52
Fix an assertion fault that can only occur if SQLITE_ENABLE_STAT2 is defined and the constant folding optimization is disabled using sqlite3_test_control(). Problem introduced by [ad8bc68197f2b4] but we missed it prior to the 3.7.4 release due to taking shortcuts and skipping tests in the release checklist. (check-in: 70a3d81742 user: drh tags: trunk)
Context
2010-12-06
21:09
Fix the build so that it once again works with SQLITE_OMIT_SHARED_CACHE and SQLITE_OMIT_AUTOVACUUM. (check-in: fabcb6b95e user: drh tags: trunk)
21:06
Add the ability to disable constant factoring using sqlite3_test_control(). Add a TCL interface to this new capability and add tests cases to the TCL test scripts to actually use the new capability. (check-in: ad8bc68197 user: drh tags: trunk)
18:59
Back out part of the previous change that was not really necessary in order to fix [80ba201079ea60], and which in fact serves no useful purpose. (check-in: fa9eef865f user: drh tags: trunk)
Changes
Side-by-Side Diff Show Whitespace Changes Patch
Changes to src/expr.c.
3036
3037
3038
3039
3040
3041
3042











3043
3044
3045
3046

3047
3048
3049
3050
3051
3052
3053
3036
3037
3038
3039
3040
3041
3042
3043
3044
3045
3046
3047
3048
3049
3050
3051
3052
3053
3054
3055
3056
3057
3058
3059
3060
3061
3062
3063
3064
3065







+
+
+
+
+
+
+
+
+
+
+




+







  return WRC_Continue;
}

/*
** Preevaluate constant subexpressions within pExpr and store the
** results in registers.  Modify pExpr so that the constant subexpresions
** are TK_REGISTER opcodes that refer to the precomputed values.
**
** This routine is a no-op if the jump to the cookie-check code has
** already occur.  Since the cookie-check jump is generated prior to
** any other serious processing, this check ensures that there is no
** way to accidently bypass the constant initializations.
**
** This routine is also a no-op if the SQLITE_FactorOutConst optimization
** is disabled via the sqlite3_test_control(SQLITE_TESTCTRL_OPTIMIZATIONS)
** interface.  This allows test logic to verify that the same answer is
** obtained for queries regardless of whether or not constants are
** precomputed into registers or if they are inserted in-line.
*/
void sqlite3ExprCodeConstants(Parse *pParse, Expr *pExpr){
  Walker w;
  if( pParse->cookieGoto ) return;
  if( (pParse->db->flags & SQLITE_FactorOutConst)!=0 ) return;
  w.xExprCallback = evalConstExpr;
  w.xSelectCallback = 0;
  w.pParse = pParse;
  sqlite3WalkExpr(&w, pExpr);
}


Changes to src/sqliteInt.h.
930
931
932
933
934
935
936

937
938
939
940
941
942
943
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944







+







*/
#define SQLITE_QueryFlattener 0x01        /* Disable query flattening */
#define SQLITE_ColumnCache    0x02        /* Disable the column cache */
#define SQLITE_IndexSort      0x04        /* Disable indexes for sorting */
#define SQLITE_IndexSearch    0x08        /* Disable indexes for searching */
#define SQLITE_IndexCover     0x10        /* Disable index covering table */
#define SQLITE_GroupByOrder   0x20        /* Disable GROUPBY cover of ORDERBY */
#define SQLITE_FactorOutConst 0x40        /* Disable factoring out constants */
#define SQLITE_OptMask        0xff        /* Mask of all disablable opts */

/*
** Possible values for the sqlite.magic field.
** The numbers are obtained at random and have no special meaning, other
** than being distinct from one another.
*/
Changes to src/test1.c.
5312
5313
5314
5315
5316
5317
5318


























































5319
5320
5321
5322
5323
5324
5325
5312
5313
5314
5315
5316
5317
5318
5319
5320
5321
5322
5323
5324
5325
5326
5327
5328
5329
5330
5331
5332
5333
5334
5335
5336
5337
5338
5339
5340
5341
5342
5343
5344
5345
5346
5347
5348
5349
5350
5351
5352
5353
5354
5355
5356
5357
5358
5359
5360
5361
5362
5363
5364
5365
5366
5367
5368
5369
5370
5371
5372
5373
5374
5375
5376
5377
5378
5379
5380
5381
5382
5383







+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+







  }
  if( getStmtPointer(interp, Tcl_GetString(objv[1]), &pStmt) ) return TCL_ERROR;
  rc = printExplainQueryPlan(pStmt);
  Tcl_SetResult(interp, (char *)t1ErrorName(rc), 0);
  return TCL_OK;
}
#endif /* SQLITE_OMIT_EXPLAIN */

/*
**      optimization_control DB OPT BOOLEAN
**
** Enable or disable query optimizations using the sqlite3_test_control()
** interface.  Disable if BOOLEAN is false and enable if BOOLEAN is true.
** OPT is the name of the optimization to be disabled.
*/
static int optimization_control(
  void * clientData,
  Tcl_Interp *interp,
  int objc,
  Tcl_Obj *CONST objv[]
){
  int i;
  sqlite3 *db;
  const char *zOpt;
  int onoff;
  int mask;
  static const struct {
    const char *zOptName;
    int mask;
  } aOpt[] = {
    { "all",              SQLITE_OptMask        },
    { "query-flattener",  SQLITE_QueryFlattener },
    { "column-cache",     SQLITE_ColumnCache    },
    { "index-sort",       SQLITE_IndexSort      },
    { "index-search",     SQLITE_IndexSearch    },
    { "index-cover",      SQLITE_IndexCover     },
    { "groupby-order",    SQLITE_GroupByOrder   },
    { "factor-constants", SQLITE_FactorOutConst },
  };

  if( objc!=4 ){
    Tcl_WrongNumArgs(interp, 1, objv, "DB OPT BOOLEAN");
    return TCL_ERROR;
  }
  if( getDbPointer(interp, Tcl_GetString(objv[1]), &db) ) return TCL_ERROR;
  if( Tcl_GetBooleanFromObj(interp, objv[3], &onoff) ) return TCL_ERROR;
  zOpt = Tcl_GetString(objv[2]);
  for(i=0; i<sizeof(aOpt)/sizeof(aOpt[0]); i++){
    if( strcmp(zOpt, aOpt[i].zOptName)==0 ){
      mask = aOpt[i].mask;
      break;
    }
  }
  if( onoff ) mask = ~mask;
  if( i>=sizeof(aOpt)/sizeof(aOpt[0]) ){
    Tcl_AppendResult(interp, "unknown optimization - should be one of:",
                     (char*)0);
    for(i=0; i<sizeof(aOpt)/sizeof(aOpt[0]); i++){
      Tcl_AppendResult(interp, " ", aOpt[i].zOptName);
    }
    return TCL_ERROR;
  }
  sqlite3_test_control(SQLITE_TESTCTRL_OPTIMIZATIONS, db, mask);
  return TCL_OK;
}

/*
** Register commands with the TCL interpreter.
*/
int Sqlitetest1_Init(Tcl_Interp *interp){
  extern int sqlite3_search_count;
  extern int sqlite3_found_count;
5430
5431
5432
5433
5434
5435
5436

5437
5438
5439
5440
5441
5442
5443
5488
5489
5490
5491
5492
5493
5494
5495
5496
5497
5498
5499
5500
5501
5502







+







     { "sqlite3_enable_load_extension", test_enable_load,        0},
     { "sqlite3_extended_result_codes", test_extended_result_codes, 0},
     { "sqlite3_limit",                 test_limit,                 0},

     { "save_prng_state",               save_prng_state,    0 },
     { "restore_prng_state",            restore_prng_state, 0 },
     { "reset_prng_state",              reset_prng_state,   0 },
     { "optimization_control",          optimization_control,0},
     { "tcl_objproc",                   runAsObjProc,       0 },

     /* sqlite3_column_*() API */
     { "sqlite3_column_count",          test_column_count  ,0 },
     { "sqlite3_data_count",            test_data_count    ,0 },
     { "sqlite3_column_type",           test_column_type   ,0 },
     { "sqlite3_column_blob",           test_column_blob   ,0 },
Changes to test/tkt-80ba201079.test.
35
36
37
38
39
40
41





























42
43
44
45
46
47
48
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77







+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+







  db eval {
    CREATE INDEX i1 ON t1(a);
    SELECT * FROM t1, t2
     WHERE (a='A' AND b='X')
        OR (a='A' AND EXISTS (SELECT * FROM t3 WHERE c='C'));
  }
} {A B}
do_test tkt-80ba2-102 {
  optimization_control db factor-constants 0
  db cache flush
  db eval {
    SELECT * FROM t1, t2
     WHERE (a='A' AND b='X')
        OR (a='A' AND EXISTS (SELECT * FROM t3 WHERE c='C'));
  }
} {A B}
optimization_control db all 1

# Verify that the optimization_control command is actually working
#
do_test tkt-80ba2-150 {
  optimization_control db factor-constants 1
  db cache flush
  set x1 [db eval {EXPLAIN 
    SELECT * FROM t1, t2
     WHERE (a='A' AND b='X')
        OR (a='A' AND EXISTS (SELECT * FROM t3 WHERE c='C'));}]
  optimization_control db factor-constants 0
  db cache flush
  set x2 [db eval {EXPLAIN 
    SELECT * FROM t1, t2
     WHERE (a='A' AND b='X')
        OR (a='A' AND EXISTS (SELECT * FROM t3 WHERE c='C'));}]

  expr {$x1==$x2}
} {0}

do_test tkt-80ba2-200 {
  db eval {
    CREATE TABLE entry_types (
                        id     integer primary key,
                        name   text
                    );
77
78
79
80
81
82
83















84
85
86
87
88
89
90
91
92
93
94
95
96
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







+
+
+
+
+
+
+
+
+
+
+
+
+
+
+













                              FROM object_changes
                               WHERE obj_context = 'exported_pools'));
  }
} {300 object_change 2048}
do_test tkt-80ba2-201 {
  db eval {
    CREATE INDEX timeline_entry_id_idx on timeline(entry_id);
    SELECT entry_type,
           entry_types.name,
           entry_id
      FROM timeline JOIN entry_types ON entry_type = entry_types.id
     WHERE (entry_types.name = 'cli_command' AND entry_id=2114)
        OR (entry_types.name = 'object_change'
             AND entry_id IN (SELECT change_id
                              FROM object_changes
                               WHERE obj_context = 'exported_pools'));
  }
} {300 object_change 2048}
do_test tkt-80ba2-202 {
  optimization_control db factor-constants 0
  db cache flush
  db eval {
    SELECT entry_type,
           entry_types.name,
           entry_id
      FROM timeline JOIN entry_types ON entry_type = entry_types.id
     WHERE (entry_types.name = 'cli_command' AND entry_id=2114)
        OR (entry_types.name = 'object_change'
             AND entry_id IN (SELECT change_id
                              FROM object_changes
                               WHERE obj_context = 'exported_pools'));
  }
} {300 object_change 2048}

finish_test