/ Check-in [bf383e66]
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 | SQL archive
Timelines: family | ancestors | descendants | both | cursor-hints
Files: files | file ages | folders
SHA1: bf383e665a191a4f33a540d1240960a922e22813
User & Date: drh 2015-08-14 20:08:13
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: 83a84435 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: bf383e66 user: drh tags: cursor-hints
18:50
Fix the cursor hint mechanism so that it does the right thing for indexed lookups. check-in: 581e3d49 user: drh tags: cursor-hints
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to main.mk.

   258    258     $(TOP)/src/test9.c \
   259    259     $(TOP)/src/test_autoext.c \
   260    260     $(TOP)/src/test_async.c \
   261    261     $(TOP)/src/test_backup.c \
   262    262     $(TOP)/src/test_blob.c \
   263    263     $(TOP)/src/test_btree.c \
   264    264     $(TOP)/src/test_config.c \
   265         -  $(TOP)/src/test_cursorhint.c \
   266    265     $(TOP)/src/test_demovfs.c \
   267    266     $(TOP)/src/test_devsym.c \
   268    267     $(TOP)/src/test_fs.c \
   269    268     $(TOP)/src/test_func.c \
   270    269     $(TOP)/src/test_hexio.c \
   271    270     $(TOP)/src/test_init.c \
   272    271     $(TOP)/src/test_intarray.c \

Changes to src/tclsqlite.c.

  3735   3735       extern int Sqlitetest6_Init(Tcl_Interp*);
  3736   3736       extern int Sqlitetest7_Init(Tcl_Interp*);
  3737   3737       extern int Sqlitetest8_Init(Tcl_Interp*);
  3738   3738       extern int Sqlitetest9_Init(Tcl_Interp*);
  3739   3739       extern int Sqlitetestasync_Init(Tcl_Interp*);
  3740   3740       extern int Sqlitetest_autoext_Init(Tcl_Interp*);
  3741   3741       extern int Sqlitetest_blob_Init(Tcl_Interp*);
  3742         -    extern int Sqlitetest_cursorhint_Init(Tcl_Interp*);
  3743   3742       extern int Sqlitetest_demovfs_Init(Tcl_Interp *);
  3744   3743       extern int Sqlitetest_func_Init(Tcl_Interp*);
  3745   3744       extern int Sqlitetest_hexio_Init(Tcl_Interp*);
  3746   3745       extern int Sqlitetest_init_Init(Tcl_Interp*);
  3747   3746       extern int Sqlitetest_malloc_Init(Tcl_Interp*);
  3748   3747       extern int Sqlitetest_mutex_Init(Tcl_Interp*);
  3749   3748       extern int Sqlitetestschema_Init(Tcl_Interp*);
................................................................................
  3781   3780       Sqlitetest6_Init(interp);
  3782   3781       Sqlitetest7_Init(interp);
  3783   3782       Sqlitetest8_Init(interp);
  3784   3783       Sqlitetest9_Init(interp);
  3785   3784       Sqlitetestasync_Init(interp);
  3786   3785       Sqlitetest_autoext_Init(interp);
  3787   3786       Sqlitetest_blob_Init(interp);
  3788         -    Sqlitetest_cursorhint_Init(interp);
  3789         -    Sqlitetest_demovfs_Init(interp);
  3790   3787       Sqlitetest_demovfs_Init(interp);
  3791   3788       Sqlitetest_func_Init(interp);
  3792   3789       Sqlitetest_hexio_Init(interp);
  3793   3790       Sqlitetest_init_Init(interp);
  3794   3791       Sqlitetest_malloc_Init(interp);
  3795   3792       Sqlitetest_mutex_Init(interp);
  3796   3793       Sqlitetestschema_Init(interp);

Deleted src/test_cursorhint.c.

     1         -/*
     2         -** 2008 March 19
     3         -**
     4         -** The author disclaims copyright to this source code.  In place of
     5         -** a legal notice, here is a blessing:
     6         -**
     7         -**    May you do good and not evil.
     8         -**    May you find forgiveness for yourself and forgive others.
     9         -**    May you share freely, never taking more than you give.
    10         -**
    11         -*************************************************************************
    12         -** Code for testing all sorts of SQLite interfaces.  This code
    13         -** implements new SQL functions used by the test scripts.
    14         -*/
    15         -#include "sqlite3.h"
    16         -#include "tcl.h"
    17         -#include <stdlib.h>
    18         -#include <string.h>
    19         -#include <assert.h>
    20         -
    21         -#include "sqliteInt.h"
    22         -#include "vdbeInt.h"
    23         -
    24         -struct CursorHintGlobal {
    25         -  Tcl_Interp *interp;
    26         -  Tcl_Obj *pScript;
    27         -} cursorhintglobal;
    28         -
    29         -static char *exprToString(Mem *aMem, Expr *pExpr){
    30         -  char *zRet = 0;
    31         -  char *zBinOp = 0;
    32         -
    33         -  switch( pExpr->op ){
    34         -    case TK_STRING:
    35         -      zRet = sqlite3_mprintf("%Q", pExpr->u.zToken);
    36         -      break;
    37         -
    38         -    case TK_INTEGER:
    39         -      zRet = sqlite3_mprintf("%d", pExpr->u.iValue);
    40         -      break;
    41         -
    42         -    case TK_NULL:
    43         -      zRet = sqlite3_mprintf("%s", "NULL");
    44         -      break;
    45         -
    46         -    case TK_REGISTER: {
    47         -      Mem *pMem = &aMem[pExpr->iTable];
    48         -      if( pMem->flags & MEM_Int ){
    49         -        zRet = sqlite3_mprintf("%lld", pMem->u.i);
    50         -      }
    51         -      else if( pMem->flags & MEM_Real ){
    52         -        zRet = sqlite3_mprintf("%f", pMem->u.r);
    53         -      }
    54         -      else if( pMem->flags & MEM_Str ){
    55         -        zRet = sqlite3_mprintf("%.*Q", pMem->n, pMem->z);
    56         -      }
    57         -      else if( pMem->flags & MEM_Blob ){
    58         -      }
    59         -      else{
    60         -        zRet = sqlite3_mprintf("%s", "NULL");
    61         -      }
    62         -      break;
    63         -    }
    64         -
    65         -    case TK_COLUMN: {
    66         -      zRet = sqlite3_mprintf("col(%d)", (int)pExpr->iColumn);
    67         -      break;
    68         -    }
    69         -
    70         -    case TK_LT:      zBinOp = "<";      break;
    71         -    case TK_LE:      zBinOp = "<=";     break;
    72         -    case TK_GT:      zBinOp = ">";      break;
    73         -    case TK_GE:      zBinOp = ">=";     break;
    74         -    case TK_NE:      zBinOp = "!=";     break;
    75         -    case TK_EQ:      zBinOp = "==";     break;
    76         -    case TK_IS:      zBinOp = "IS";     break;
    77         -    case TK_ISNOT:   zBinOp = "IS NOT"; break;
    78         -    case TK_AND:     zBinOp = "AND";    break;
    79         -    case TK_OR:      zBinOp = "OR";     break;
    80         -    case TK_PLUS:    zBinOp = "+";      break;
    81         -    case TK_STAR:    zBinOp = "*";      break;
    82         -    case TK_MINUS:   zBinOp = "-";      break;
    83         -    case TK_REM:     zBinOp = "%";      break;
    84         -    case TK_BITAND:  zBinOp = "&";      break;
    85         -    case TK_BITOR:   zBinOp = "|";      break;
    86         -    case TK_SLASH:   zBinOp = "/";      break;
    87         -    case TK_LSHIFT:  zBinOp = "<<";     break;
    88         -    case TK_RSHIFT:  zBinOp = ">>";     break;
    89         -    case TK_CONCAT:  zBinOp = "||";     break;
    90         -
    91         -    default:
    92         -      zRet = sqlite3_mprintf("%s", "expr");
    93         -      break;
    94         -  }
    95         -
    96         -  if( zBinOp ){
    97         -    zRet = sqlite3_mprintf("(%z %s %z)", 
    98         -        exprToString(aMem, pExpr->pLeft),
    99         -        zBinOp,
   100         -        exprToString(aMem, pExpr->pRight)
   101         -    );
   102         -  }
   103         -
   104         -  return zRet;
   105         -}
   106         -
   107         -void sqlite3BtreeCursorHintTest(Mem *aMem, Expr *pExpr){
   108         -  if( cursorhintglobal.pScript ){
   109         -    Tcl_Obj *pEval = Tcl_DuplicateObj(cursorhintglobal.pScript);
   110         -    char *zExpr;
   111         -    Tcl_Obj *pObj;
   112         -    Tcl_IncrRefCount(pEval);
   113         -    zExpr = exprToString(aMem, pExpr);
   114         -    pObj = Tcl_NewStringObj(zExpr, -1);
   115         -    sqlite3_free(zExpr);
   116         -    Tcl_ListObjAppendElement(cursorhintglobal.interp, pEval, pObj);
   117         -    Tcl_EvalObjEx(cursorhintglobal.interp, pEval, TCL_GLOBAL_ONLY);
   118         -    Tcl_DecrRefCount(pEval);
   119         -  }
   120         -}
   121         -
   122         -/*
   123         -** Usage: cursorhint_hook SCRIPT
   124         -*/
   125         -static int install_cursorhint_hook(
   126         -  ClientData clientData, /* Pointer to sqlite3_enable_XXX function */
   127         -  Tcl_Interp *interp,    /* The TCL interpreter that invoked this command */
   128         -  int objc,              /* Number of arguments */
   129         -  Tcl_Obj *CONST objv[]  /* Command arguments */
   130         -){
   131         -  if( objc!=1 && objc!=2 ){
   132         -    Tcl_WrongNumArgs(interp, 1, objv, "?SCRIPT?");
   133         -    return TCL_ERROR;
   134         -  }
   135         -  if( cursorhintglobal.pScript ){
   136         -    Tcl_DecrRefCount(cursorhintglobal.pScript);
   137         -    memset(&cursorhintglobal, 0, sizeof(cursorhintglobal));
   138         -  }
   139         -  if( objc==2 ){
   140         -    cursorhintglobal.interp = interp;
   141         -    cursorhintglobal.pScript = Tcl_DuplicateObj(objv[1]);
   142         -  }
   143         -  return TCL_OK;
   144         -}
   145         -
   146         -/*
   147         -** Register commands with the TCL interpreter.
   148         -*/
   149         -int Sqlitetest_cursorhint_Init(Tcl_Interp *interp){
   150         -  static struct {
   151         -     char *zName;
   152         -     Tcl_ObjCmdProc *xProc;
   153         -  } aObjCmd[] = {
   154         -     { "cursorhint_hook",    install_cursorhint_hook },
   155         -  };
   156         -  int i;
   157         -  for(i=0; i<sizeof(aObjCmd)/sizeof(aObjCmd[0]); i++){
   158         -    Tcl_CreateObjCommand(interp, aObjCmd[i].zName, aObjCmd[i].xProc, 0, 0);
   159         -  }
   160         -  sqlite3_initialize();
   161         -  return TCL_OK;
   162         -}

Changes to src/vdbe.c.

  6566   6566     VdbeCursor *pC;
  6567   6567   
  6568   6568     assert( pOp->p1>=0 && pOp->p1<p->nCursor );
  6569   6569     assert( pOp->p4type==P4_EXPR );
  6570   6570     pC = p->apCsr[pOp->p1];
  6571   6571     if( pC ){
  6572   6572       sqlite3BtreeCursorHint(pC->pCursor, BTREE_HINT_RANGE, pOp->p4.pExpr, aMem);
  6573         -#ifdef SQLITE_TEST
  6574         -    void sqlite3BtreeCursorHintTest(Mem*, Expr*);
  6575         -    sqlite3BtreeCursorHintTest(p->aMem, pOp->p4.pExpr);
  6576         -#endif
  6577   6573     }
  6578   6574     break;
  6579   6575   }
  6580   6576   #endif /* SQLITE_ENABLE_CURSOR_HINTS */
  6581   6577   
  6582   6578   /* Opcode: Noop * * * * *
  6583   6579   **

Changes to test/cursorhint.test.

    12     12   #
    13     13   
    14     14   set testdir [file dirname $argv0]
    15     15   source $testdir/tester.tcl
    16     16   set testprefix cursorhint
    17     17   
    18     18   do_execsql_test 1.0 {
    19         -  CREATE TABLE t1(a,b);
    20         -  CREATE TABLE t2(x,y);
    21         -  INSERT INTO t1 VALUES(10, 15);
    22         -  INSERT INTO t1 VALUES(20, 25);
    23         -  INSERT INTO t2 VALUES('ten', 'fifteen');
    24         -  INSERT INTO t2 VALUES('twenty', 'twentyfive');
           19  +  CREATE TABLE t1(a,b,c,d);
           20  +  CREATE TABLE t2(x,y,z);
           21  +  INSERT INTO t1(a,b) VALUES(10, 15);
           22  +  INSERT INTO t1(a,b) VALUES(20, 25);
           23  +  INSERT INTO t2(x,y) VALUES('ten', 'fifteen');
           24  +  INSERT INTO t2(x,y) VALUES('twenty', 'twentyfive');
           25  +  CREATE TABLE t3(id TEXT PRIMARY KEY, a, b, c, d) WITHOUT ROWID;
           26  +  INSERT INTO t3(id,a,b,c,d) SELECT rowid, a, b, c, d FROM t1;
    25     27     PRAGMA automatic_index = 0;
    26     28   }
    27     29   
    28         -proc H {expr} {
    29         -  lappend ::cursorhint $expr
    30         -}
    31         -
    32         -proc do_cursorhint_test {tn sql hints} {
    33         -  cursorhint_hook H
    34         -  set ::cursorhint [list]
    35         -  set testbody [subst {
    36         -    execsql {$sql}
    37         -    set ::cursorhint
    38         -  }]
    39         -  uplevel [list do_test $tn $testbody [list {*}$hints]]
    40         -  cursorhint_hook
           30  +# Run EXPLAIN on $sql.  Return a list of P4 values for all $opcode
           31  +# opcodes.
           32  +#
           33  +proc p4_of_opcode {db opcode sql} {
           34  +  set res {}
           35  +  $db eval "EXPLAIN $sql" x {
           36  +    if {$x(opcode)==$opcode} {lappend res $x(p4)}
           37  +  }
           38  +  return $res
    41     39   }
    42     40   
    43         -
    44         -do_cursorhint_test 1.1 {
    45         -  SELECT * FROM t1 CROSS JOIN t2 WHERE a=x;
    46         -} {
    47         -  {(10 == col(0))} 
    48         -  {(20 == col(0))}
           41  +# Run EXPLAIN on $sql.  Return a list of P5 values for all $opcode
           42  +# opcodes that contain regexp $comment in their comment
           43  +#
           44  +proc p5_of_opcode {db opcode comment sql} {
           45  +  set res {}
           46  +  $db eval "EXPLAIN $sql" x {
           47  +    if {$x(opcode)==$opcode && [regexp $comment $x(comment)]} {
           48  +      lappend res $x(p5)
           49  +    }
           50  +  }
           51  +  return $res
    49     52   }
    50     53   
    51         -do_cursorhint_test 1.2 {
    52         -  SELECT * FROM t2 CROSS JOIN t1 WHERE a=x;
    53         -} {
    54         -  {(col(0) == 'ten')} 
    55         -  {(col(0) == 'twenty')}
    56         -}
           54  +# Verify that when t1 is in the outer loop and t2 is in the inner loop,
           55  +# no cursor hints occur for t1 (since it is a full table scan) but that
           56  +# each t2 access has a cursor hint based on the current t1.a value.
           57  +#
           58  +do_test 1.1 {
           59  +  p4_of_opcode db CursorHint {
           60  +     SELECT * FROM t1 CROSS JOIN t2 WHERE a=x
           61  +  }
           62  +} {/(r*==c0)/}
           63  +do_test 1.2 {
           64  +  p5_of_opcode db OpenRead . {
           65  +     SELECT * FROM t1 CROSS JOIN t2 WHERE a=x
           66  +  }
           67  +} {00 00}
           68  +
           69  +# Do the same test the other way around.
           70  +#
           71  +do_test 2.1 {
           72  +  p4_of_opcode db CursorHint {
           73  +     SELECT * FROM t2 CROSS JOIN t1 WHERE a=x
           74  +  }
           75  +} {/(c0==r*)/}
           76  +do_test 2.2 {
           77  +  p5_of_opcode db OpenRead . {
           78  +     SELECT * FROM t2 CROSS JOIN t1 WHERE a=x
           79  +  }
           80  +} {00 00}
    57     81   
    58         -do_cursorhint_test 1.3 {
    59         -  SELECT * FROM t1 CROSS JOIN t2 WHERE b=15;
    60         -} {
    61         -  {(col(1) == 15)} 
    62         -}
           82  +# Various expressions captured by CursorHint
           83  +#
           84  +do_test 3.1 {
           85  +  p4_of_opcode db CursorHint {
           86  +    SELECT * FROM t1 WHERE a=15 AND c=22 AND rowid!=98
           87  +  }
           88  +} {/(c0==15).*(c2==22).*(rowid!=98)/}
           89  +do_test 3.2 {
           90  +  p4_of_opcode db CursorHint {
           91  +    SELECT * FROM t3 WHERE a<15 AND b>22 AND id!=98
           92  +  }
           93  +} {/(c1<15).*(c2>22).*(c0!=98)/}
    63     94   
    64         -do_cursorhint_test 1.3 {
    65         -  SELECT * FROM t1 CROSS JOIN t2 WHERE y=b+1;
    66         -} {
    67         -  {(col(1) == (15 + 1))} 
    68         -  {(col(1) == (25 + 1))} 
    69         -}
           95  +# Indexed queries
           96  +#
           97  +do_test 4.1 {
           98  +  db eval {
           99  +    CREATE INDEX t1bc ON t1(b,c);
          100  +    CREATE INDEX t2yz ON t2(y,z);
          101  +  }
          102  +  p4_of_opcode db CursorHint {
          103  +    SELECT * FROM t1 WHERE b>11;
          104  +  }
          105  +} {/(c0>11)/}
          106  +do_test 4.2 {
          107  +  p5_of_opcode db OpenRead . {
          108  +    SELECT * FROM t1 WHERE b>11;
          109  +  }
          110  +} {02 00}
          111  +do_test 4.3 {
          112  +  p4_of_opcode db CursorHint {
          113  +    SELECT c FROM t1 WHERE b>11;
          114  +  }
          115  +} {/(c0>11)/}
          116  +do_test 4.4 {
          117  +  p5_of_opcode db OpenRead . {
          118  +    SELECT c FROM t1 WHERE b>11;
          119  +  }
          120  +} {00}
    70    121   
    71    122   
    72    123   finish_test