SQLite

Check-in [bf383e665a]
Login

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

Overview
Comment:Updated testing of cursor-hints. Remove the test_cursorhint.c file and associated logic in the core and do tests based purely on the newly enhanced EXPLAIN output.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | cursor-hints
Files: files | file ages | folders
SHA1: bf383e665a191a4f33a540d1240960a922e22813
User & Date: drh 2015-08-14 20:08:13.528
Context
2015-08-14
23:57
Always provide the BTREE_BULKLOAD hint, even when SQLITE_ENABLE_CURSOR_HINTS is not defined, as that hint gives a 4% performance increase. (check-in: 83a844357e user: drh tags: cursor-hints)
20:08
Updated testing of cursor-hints. Remove the test_cursorhint.c file and associated logic in the core and do tests based purely on the newly enhanced EXPLAIN output. (check-in: bf383e665a user: drh tags: cursor-hints)
18:50
Fix the cursor hint mechanism so that it does the right thing for indexed lookups. (check-in: 581e3d4988 user: drh tags: cursor-hints)
Changes
Unified Diff Show Whitespace Changes Patch
Changes to main.mk.
︙︙
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272

  $(TOP)/src/test9.c \
  $(TOP)/src/test_autoext.c \
  $(TOP)/src/test_async.c \
  $(TOP)/src/test_backup.c \
  $(TOP)/src/test_blob.c \
  $(TOP)/src/test_btree.c \
  $(TOP)/src/test_config.c \
  $(TOP)/src/test_cursorhint.c \
  $(TOP)/src/test_demovfs.c \
  $(TOP)/src/test_devsym.c \
  $(TOP)/src/test_fs.c \
  $(TOP)/src/test_func.c \
  $(TOP)/src/test_hexio.c \
  $(TOP)/src/test_init.c \
  $(TOP)/src/test_intarray.c \








<








258
259
260
261
262
263
264

265
266
267
268
269
270
271

  $(TOP)/src/test9.c \
  $(TOP)/src/test_autoext.c \
  $(TOP)/src/test_async.c \
  $(TOP)/src/test_backup.c \
  $(TOP)/src/test_blob.c \
  $(TOP)/src/test_btree.c \
  $(TOP)/src/test_config.c \

  $(TOP)/src/test_demovfs.c \
  $(TOP)/src/test_devsym.c \
  $(TOP)/src/test_fs.c \
  $(TOP)/src/test_func.c \
  $(TOP)/src/test_hexio.c \
  $(TOP)/src/test_init.c \
  $(TOP)/src/test_intarray.c \
︙︙
Changes to src/tclsqlite.c.
︙︙
3735
3736
3737
3738
3739
3740
3741
3742
3743
3744
3745
3746
3747
3748
3749

    extern int Sqlitetest6_Init(Tcl_Interp*);
    extern int Sqlitetest7_Init(Tcl_Interp*);
    extern int Sqlitetest8_Init(Tcl_Interp*);
    extern int Sqlitetest9_Init(Tcl_Interp*);
    extern int Sqlitetestasync_Init(Tcl_Interp*);
    extern int Sqlitetest_autoext_Init(Tcl_Interp*);
    extern int Sqlitetest_blob_Init(Tcl_Interp*);
    extern int Sqlitetest_cursorhint_Init(Tcl_Interp*);
    extern int Sqlitetest_demovfs_Init(Tcl_Interp *);
    extern int Sqlitetest_func_Init(Tcl_Interp*);
    extern int Sqlitetest_hexio_Init(Tcl_Interp*);
    extern int Sqlitetest_init_Init(Tcl_Interp*);
    extern int Sqlitetest_malloc_Init(Tcl_Interp*);
    extern int Sqlitetest_mutex_Init(Tcl_Interp*);
    extern int Sqlitetestschema_Init(Tcl_Interp*);








<








3735
3736
3737
3738
3739
3740
3741

3742
3743
3744
3745
3746
3747
3748

    extern int Sqlitetest6_Init(Tcl_Interp*);
    extern int Sqlitetest7_Init(Tcl_Interp*);
    extern int Sqlitetest8_Init(Tcl_Interp*);
    extern int Sqlitetest9_Init(Tcl_Interp*);
    extern int Sqlitetestasync_Init(Tcl_Interp*);
    extern int Sqlitetest_autoext_Init(Tcl_Interp*);
    extern int Sqlitetest_blob_Init(Tcl_Interp*);

    extern int Sqlitetest_demovfs_Init(Tcl_Interp *);
    extern int Sqlitetest_func_Init(Tcl_Interp*);
    extern int Sqlitetest_hexio_Init(Tcl_Interp*);
    extern int Sqlitetest_init_Init(Tcl_Interp*);
    extern int Sqlitetest_malloc_Init(Tcl_Interp*);
    extern int Sqlitetest_mutex_Init(Tcl_Interp*);
    extern int Sqlitetestschema_Init(Tcl_Interp*);
︙︙
3781
3782
3783
3784
3785
3786
3787
3788
3789
3790
3791
3792
3793
3794
3795
3796

    Sqlitetest6_Init(interp);
    Sqlitetest7_Init(interp);
    Sqlitetest8_Init(interp);
    Sqlitetest9_Init(interp);
    Sqlitetestasync_Init(interp);
    Sqlitetest_autoext_Init(interp);
    Sqlitetest_blob_Init(interp);
    Sqlitetest_cursorhint_Init(interp);
    Sqlitetest_demovfs_Init(interp);
    Sqlitetest_demovfs_Init(interp);
    Sqlitetest_func_Init(interp);
    Sqlitetest_hexio_Init(interp);
    Sqlitetest_init_Init(interp);
    Sqlitetest_malloc_Init(interp);
    Sqlitetest_mutex_Init(interp);
    Sqlitetestschema_Init(interp);








<
<








3780
3781
3782
3783
3784
3785
3786


3787
3788
3789
3790
3791
3792
3793

    Sqlitetest6_Init(interp);
    Sqlitetest7_Init(interp);
    Sqlitetest8_Init(interp);
    Sqlitetest9_Init(interp);
    Sqlitetestasync_Init(interp);
    Sqlitetest_autoext_Init(interp);
    Sqlitetest_blob_Init(interp);


    Sqlitetest_demovfs_Init(interp);
    Sqlitetest_func_Init(interp);
    Sqlitetest_hexio_Init(interp);
    Sqlitetest_init_Init(interp);
    Sqlitetest_malloc_Init(interp);
    Sqlitetest_mutex_Init(interp);
    Sqlitetestschema_Init(interp);
︙︙
Deleted src/test_cursorhint.c. 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
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
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
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
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162

/*
** 2008 March 19
**
** The author disclaims copyright to this source code.  In place of
** a legal notice, here is a blessing:
**
**    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.
**
*************************************************************************
** Code for testing all sorts of SQLite interfaces.  This code
** implements new SQL functions used by the test scripts.
*/
#include "sqlite3.h"
#include "tcl.h"
#include <stdlib.h>
#include <string.h>
#include <assert.h>

#include "sqliteInt.h"
#include "vdbeInt.h"

struct CursorHintGlobal {
  Tcl_Interp *interp;
  Tcl_Obj *pScript;
} cursorhintglobal;

static char *exprToString(Mem *aMem, Expr *pExpr){
  char *zRet = 0;
  char *zBinOp = 0;

  switch( pExpr->op ){
    case TK_STRING:
      zRet = sqlite3_mprintf("%Q", pExpr->u.zToken);
      break;

    case TK_INTEGER:
      zRet = sqlite3_mprintf("%d", pExpr->u.iValue);
      break;

    case TK_NULL:
      zRet = sqlite3_mprintf("%s", "NULL");
      break;

    case TK_REGISTER: {
      Mem *pMem = &aMem[pExpr->iTable];
      if( pMem->flags & MEM_Int ){
        zRet = sqlite3_mprintf("%lld", pMem->u.i);
      }
      else if( pMem->flags & MEM_Real ){
        zRet = sqlite3_mprintf("%f", pMem->u.r);
      }
      else if( pMem->flags & MEM_Str ){
        zRet = sqlite3_mprintf("%.*Q", pMem->n, pMem->z);
      }
      else if( pMem->flags & MEM_Blob ){
      }
      else{
        zRet = sqlite3_mprintf("%s", "NULL");
      }
      break;
    }

    case TK_COLUMN: {
      zRet = sqlite3_mprintf("col(%d)", (int)pExpr->iColumn);
      break;
    }

    case TK_LT:      zBinOp = "<";      break;
    case TK_LE:      zBinOp = "<=";     break;
    case TK_GT:      zBinOp = ">";      break;
    case TK_GE:      zBinOp = ">=";     break;
    case TK_NE:      zBinOp = "!=";     break;
    case TK_EQ:      zBinOp = "==";     break;
    case TK_IS:      zBinOp = "IS";     break;
    case TK_ISNOT:   zBinOp = "IS NOT"; break;
    case TK_AND:     zBinOp = "AND";    break;
    case TK_OR:      zBinOp = "OR";     break;
    case TK_PLUS:    zBinOp = "+";      break;
    case TK_STAR:    zBinOp = "*";      break;
    case TK_MINUS:   zBinOp = "-";      break;
    case TK_REM:     zBinOp = "%";      break;
    case TK_BITAND:  zBinOp = "&";      break;
    case TK_BITOR:   zBinOp = "|";      break;
    case TK_SLASH:   zBinOp = "/";      break;
    case TK_LSHIFT:  zBinOp = "<<";     break;
    case TK_RSHIFT:  zBinOp = ">>";     break;
    case TK_CONCAT:  zBinOp = "||";     break;

    default:
      zRet = sqlite3_mprintf("%s", "expr");
      break;
  }

  if( zBinOp ){
    zRet = sqlite3_mprintf("(%z %s %z)", 
        exprToString(aMem, pExpr->pLeft),
        zBinOp,
        exprToString(aMem, pExpr->pRight)
    );
  }

  return zRet;
}

void sqlite3BtreeCursorHintTest(Mem *aMem, Expr *pExpr){
  if( cursorhintglobal.pScript ){
    Tcl_Obj *pEval = Tcl_DuplicateObj(cursorhintglobal.pScript);
    char *zExpr;
    Tcl_Obj *pObj;
    Tcl_IncrRefCount(pEval);
    zExpr = exprToString(aMem, pExpr);
    pObj = Tcl_NewStringObj(zExpr, -1);
    sqlite3_free(zExpr);
    Tcl_ListObjAppendElement(cursorhintglobal.interp, pEval, pObj);
    Tcl_EvalObjEx(cursorhintglobal.interp, pEval, TCL_GLOBAL_ONLY);
    Tcl_DecrRefCount(pEval);
  }
}

/*
** Usage: cursorhint_hook SCRIPT
*/
static int install_cursorhint_hook(
  ClientData clientData, /* Pointer to sqlite3_enable_XXX function */
  Tcl_Interp *interp,    /* The TCL interpreter that invoked this command */
  int objc,              /* Number of arguments */
  Tcl_Obj *CONST objv[]  /* Command arguments */
){
  if( objc!=1 && objc!=2 ){
    Tcl_WrongNumArgs(interp, 1, objv, "?SCRIPT?");
    return TCL_ERROR;
  }
  if( cursorhintglobal.pScript ){
    Tcl_DecrRefCount(cursorhintglobal.pScript);
    memset(&cursorhintglobal, 0, sizeof(cursorhintglobal));
  }
  if( objc==2 ){
    cursorhintglobal.interp = interp;
    cursorhintglobal.pScript = Tcl_DuplicateObj(objv[1]);
  }
  return TCL_OK;
}

/*
** Register commands with the TCL interpreter.
*/
int Sqlitetest_cursorhint_Init(Tcl_Interp *interp){
  static struct {
     char *zName;
     Tcl_ObjCmdProc *xProc;
  } aObjCmd[] = {
     { "cursorhint_hook",    install_cursorhint_hook },
  };
  int i;
  for(i=0; i<sizeof(aObjCmd)/sizeof(aObjCmd[0]); i++){
    Tcl_CreateObjCommand(interp, aObjCmd[i].zName, aObjCmd[i].xProc, 0, 0);
  }
  sqlite3_initialize();
  return TCL_OK;
}

<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
Changes to src/vdbe.c.
︙︙
6566
6567
6568
6569
6570
6571
6572
6573
6574
6575
6576
6577
6578
6579
6580
6581
6582
6583

  VdbeCursor *pC;

  assert( pOp->p1>=0 && pOp->p1<p->nCursor );
  assert( pOp->p4type==P4_EXPR );
  pC = p->apCsr[pOp->p1];
  if( pC ){
    sqlite3BtreeCursorHint(pC->pCursor, BTREE_HINT_RANGE, pOp->p4.pExpr, aMem);
#ifdef SQLITE_TEST
    void sqlite3BtreeCursorHintTest(Mem*, Expr*);
    sqlite3BtreeCursorHintTest(p->aMem, pOp->p4.pExpr);
#endif
  }
  break;
}
#endif /* SQLITE_ENABLE_CURSOR_HINTS */

/* Opcode: Noop * * * * *
**








<
<
<
<








6566
6567
6568
6569
6570
6571
6572




6573
6574
6575
6576
6577
6578
6579

  VdbeCursor *pC;

  assert( pOp->p1>=0 && pOp->p1<p->nCursor );
  assert( pOp->p4type==P4_EXPR );
  pC = p->apCsr[pOp->p1];
  if( pC ){
    sqlite3BtreeCursorHint(pC->pCursor, BTREE_HINT_RANGE, pOp->p4.pExpr, aMem);




  }
  break;
}
#endif /* SQLITE_ENABLE_CURSOR_HINTS */

/* Opcode: Noop * * * * *
**
︙︙
Changes to test/cursorhint.test.
︙︙
12
13
14
15
16
17
18
19
20
21
22
23
24


25
26
27



28


29
30

31
32
33
34
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

#

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

do_execsql_test 1.0 {
  CREATE TABLE t1(a,b);
  CREATE TABLE t2(x,y);
  INSERT INTO t1 VALUES(10, 15);
  INSERT INTO t1 VALUES(20, 25);
  INSERT INTO t2 VALUES('ten', 'fifteen');
  INSERT INTO t2 VALUES('twenty', 'twentyfive');


  PRAGMA automatic_index = 0;
}




proc H {expr} {


  lappend ::cursorhint $expr
}


proc do_cursorhint_test {tn sql hints} {
  cursorhint_hook H
  set ::cursorhint [list]
  set testbody [subst {



    execsql {$sql}
    set ::cursorhint
  }]
  uplevel [list do_test $tn $testbody [list {*}$hints]]





  cursorhint_hook
}





do_cursorhint_test 1.1 {

  SELECT * FROM t1 CROSS JOIN t2 WHERE a=x;














} {
  {(10 == col(0))} 



  {(20 == col(0))}
}


do_cursorhint_test 1.2 {

  SELECT * FROM t2 CROSS JOIN t1 WHERE a=x;

} {
  {(col(0) == 'ten')} 
  {(col(0) == 'twenty')}


}




do_cursorhint_test 1.3 {
  SELECT * FROM t1 CROSS JOIN t2 WHERE b=15;
} {
  {(col(1) == 15)} 


}




do_cursorhint_test 1.3 {

  SELECT * FROM t1 CROSS JOIN t2 WHERE y=b+1;

} {




  {(col(1) == (15 + 1))} 
  {(col(1) == (25 + 1))} 



}



finish_test








|
|
|
|
|
|
>
>



>
>
>
|
>
>
|

>
|
|
<
<
<
>
>
>
|
|
<
<
>
>
>
>
>
|


>
>
>
|
|
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|
|
>
>
>
|
|
>
|
|
>
|
>
|
|
<
>
>

>

>
>
|
<
|
<
>
>

>
>
|
>
|
>
|
>
|
>
>
>
>
|
<
>
>
>

>




12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
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
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
112
113
114
115

116
117
118
119
120
121
122
123

#

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

do_execsql_test 1.0 {
  CREATE TABLE t1(a,b,c,d);
  CREATE TABLE t2(x,y,z);
  INSERT INTO t1(a,b) VALUES(10, 15);
  INSERT INTO t1(a,b) VALUES(20, 25);
  INSERT INTO t2(x,y) VALUES('ten', 'fifteen');
  INSERT INTO t2(x,y) VALUES('twenty', 'twentyfive');
  CREATE TABLE t3(id TEXT PRIMARY KEY, a, b, c, d) WITHOUT ROWID;
  INSERT INTO t3(id,a,b,c,d) SELECT rowid, a, b, c, d FROM t1;
  PRAGMA automatic_index = 0;
}

# Run EXPLAIN on $sql.  Return a list of P4 values for all $opcode
# opcodes.
#
proc p4_of_opcode {db opcode sql} {
  set res {}
  $db eval "EXPLAIN $sql" x {
    if {$x(opcode)==$opcode} {lappend res $x(p4)}
}
  return $res
}




# Run EXPLAIN on $sql.  Return a list of P5 values for all $opcode
# opcodes that contain regexp $comment in their comment
#
proc p5_of_opcode {db opcode comment sql} {
  set res {}


  $db eval "EXPLAIN $sql" x {
    if {$x(opcode)==$opcode && [regexp $comment $x(comment)]} {
      lappend res $x(p5)
    }
  }
  return $res
}

# Verify that when t1 is in the outer loop and t2 is in the inner loop,
# no cursor hints occur for t1 (since it is a full table scan) but that
# each t2 access has a cursor hint based on the current t1.a value.
#
do_test 1.1 {
  p4_of_opcode db CursorHint {
     SELECT * FROM t1 CROSS JOIN t2 WHERE a=x
  }
} {/(r*==c0)/}
do_test 1.2 {
  p5_of_opcode db OpenRead . {
     SELECT * FROM t1 CROSS JOIN t2 WHERE a=x
  }
} {00 00}

# Do the same test the other way around.
#
do_test 2.1 {
  p4_of_opcode db CursorHint {
     SELECT * FROM t2 CROSS JOIN t1 WHERE a=x
  }
} {/(c0==r*)/}
do_test 2.2 {
  p5_of_opcode db OpenRead . {
     SELECT * FROM t2 CROSS JOIN t1 WHERE a=x
  }
} {00 00}

# Various expressions captured by CursorHint
#
do_test 3.1 {
  p4_of_opcode db CursorHint {
    SELECT * FROM t1 WHERE a=15 AND c=22 AND rowid!=98
  }
} {/(c0==15).*(c2==22).*(rowid!=98)/}
do_test 3.2 {

  p4_of_opcode db CursorHint {
    SELECT * FROM t3 WHERE a<15 AND b>22 AND id!=98
}
} {/(c1<15).*(c2>22).*(c0!=98)/}

# Indexed queries
#
do_test 4.1 {

  db eval {

    CREATE INDEX t1bc ON t1(b,c);
    CREATE INDEX t2yz ON t2(y,z);
}
  p4_of_opcode db CursorHint {
    SELECT * FROM t1 WHERE b>11;
  }
} {/(c0>11)/}
do_test 4.2 {
  p5_of_opcode db OpenRead . {
    SELECT * FROM t1 WHERE b>11;
  }
} {02 00}
do_test 4.3 {
  p4_of_opcode db CursorHint {
    SELECT c FROM t1 WHERE b>11;
  }
} {/(c0>11)/}

do_test 4.4 {
  p5_of_opcode db OpenRead . {
    SELECT c FROM t1 WHERE b>11;
}
} {00}


finish_test