SQLite

Check-in [6bc71dfcf0]
Login

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

Overview
Comment:Pull in recent trunk changes. Fix the ORDER BY optimizer so that it is better able to deal with COLLATE clauses. Clean up ambiguities in the descidx1.test script.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | nextgen-query-plan-exp
Files: files | file ages | folders
SHA1: 6bc71dfcf0ef757c5c2b426dd8fddc1e5ae0f598
User & Date: drh 2013-06-03 20:46:35.011
Context
2013-06-03
21:25
Adjust the xBestIndex methods on both the fuzzer and transitive_closure virtual tables so that an unused MATCH operator gets a really large cost. Remove ambiguities from the fuzzer test cases. (check-in: e2c1af78b6 user: drh tags: nextgen-query-plan-exp)
20:46
Pull in recent trunk changes. Fix the ORDER BY optimizer so that it is better able to deal with COLLATE clauses. Clean up ambiguities in the descidx1.test script. (check-in: 6bc71dfcf0 user: drh tags: nextgen-query-plan-exp)
20:39
Fix a typo in a collating function inside the e_reindex.test script. (check-in: 4d74fccf02 user: drh tags: trunk)
19:17
Do not use an index fullscan for an UPDATE or DELETE or if disabled by sqlite3_test_control() or sqlite3_config(). (check-in: fabb21854e user: drh tags: nextgen-query-plan-exp)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/tclsqlite.c.
︙︙
3046
3047
3048
3049
3050
3051
3052
3053
3054
3055
3056
3057
3058
3059
3060


/*
** Provide a dummy Tcl_InitStubs if we are using this as a static
** library.
*/
#ifndef USE_TCL_STUBS
# undef  Tcl_InitStubs
# define Tcl_InitStubs(a,b,c)
#endif

/*
** Make sure we have a PACKAGE_VERSION macro defined.  This will be
** defined automatically by the TEA makefile.  But other makefiles
** do not define it.
*/








|








3046
3047
3048
3049
3050
3051
3052
3053
3054
3055
3056
3057
3058
3059
3060


/*
** Provide a dummy Tcl_InitStubs if we are using this as a static
** library.
*/
#ifndef USE_TCL_STUBS
# undef  Tcl_InitStubs
# define Tcl_InitStubs(a,b,c) TCL_VERSION
#endif

/*
** Make sure we have a PACKAGE_VERSION macro defined.  This will be
** defined automatically by the TEA makefile.  But other makefiles
** do not define it.
*/
︙︙
3070
3071
3072
3073
3074
3075
3076
3077

3078
3079
3080
3081
3082
3083
3084
3085
3086
3087

3088
3089
3090
3091
3092
3093
3094
3095
3096

** if the extension only supplies one new name!)  The "sqlite" command is
** used to open a new SQLite database.  See the DbMain() routine above
** for additional information.
**
** The EXTERN macros are required by TCL in order to work on windows.
*/
EXTERN int Sqlite3_Init(Tcl_Interp *interp){
  Tcl_InitStubs(interp, "8.4", 0);

  Tcl_CreateObjCommand(interp, "sqlite3", (Tcl_ObjCmdProc*)DbMain, 0, 0);
  Tcl_PkgProvide(interp, "sqlite3", PACKAGE_VERSION);

#ifndef SQLITE_3_SUFFIX_ONLY
  /* The "sqlite" alias is undocumented.  It is here only to support
  ** legacy scripts.  All new scripts should use only the "sqlite3"
  ** command.
  */
  Tcl_CreateObjCommand(interp, "sqlite", (Tcl_ObjCmdProc*)DbMain, 0, 0);
#endif


  return TCL_OK;
}
EXTERN int Tclsqlite3_Init(Tcl_Interp *interp){ return Sqlite3_Init(interp); }
EXTERN int Sqlite3_Unload(Tcl_Interp *interp, int flags){ return TCL_OK; }
EXTERN int Tclsqlite3_Unload(Tcl_Interp *interp, int flags){ return TCL_OK; }

/* Because it accesses the file-system and uses persistent state, SQLite
** is not considered appropriate for safe interpreters.  Hence, we deliberately








|
>
|
<
<

|
|
|
<
|

>
|
|








3070
3071
3072
3073
3074
3075
3076
3077
3078
3079


3080
3081
3082
3083

3084
3085
3086
3087
3088
3089
3090
3091
3092
3093
3094
3095

** if the extension only supplies one new name!)  The "sqlite" command is
** used to open a new SQLite database.  See the DbMain() routine above
** for additional information.
**
** The EXTERN macros are required by TCL in order to work on windows.
*/
EXTERN int Sqlite3_Init(Tcl_Interp *interp){
  int rc = Tcl_InitStubs(interp, "8.4", 0)==0 ? TCL_ERROR : TCL_OK;
  if( rc==TCL_OK ){
    Tcl_CreateObjCommand(interp, "sqlite3", (Tcl_ObjCmdProc*)DbMain, 0, 0);


#ifndef SQLITE_3_SUFFIX_ONLY
    /* The "sqlite" alias is undocumented.  It is here only to support
    ** legacy scripts.  All new scripts should use only the "sqlite3"
    ** command. */

    Tcl_CreateObjCommand(interp, "sqlite", (Tcl_ObjCmdProc*)DbMain, 0, 0);
#endif
    rc = Tcl_PkgProvide(interp, "sqlite3", PACKAGE_VERSION);
  }
  return rc;
}
EXTERN int Tclsqlite3_Init(Tcl_Interp *interp){ return Sqlite3_Init(interp); }
EXTERN int Sqlite3_Unload(Tcl_Interp *interp, int flags){ return TCL_OK; }
EXTERN int Tclsqlite3_Unload(Tcl_Interp *interp, int flags){ return TCL_OK; }

/* Because it accesses the file-system and uses persistent state, SQLite
** is not considered appropriate for safe interpreters.  Hence, we deliberately
︙︙
Changes to src/where.c.
︙︙
4097
4098
4099
4100
4101
4102
4103
4104
4105
4106
4107
4108
4109
4110
4111

  ExprList *pOB;
  int iCol;
  int ii;

  if( (pOB = pBuilder->pOrderBy)==0 ) return 0;
  iCol = pIndex->aiColumn[0];
  for(ii=0; ii<pOB->nExpr; ii++){
    Expr *pExpr = pOB->a[ii].pExpr;
    if( pExpr->op!=TK_COLUMN ) return 0;
    if( pExpr->iTable==iCursor ){
      if( pExpr->iColumn==iCol ) return 1;
      return 0;
    }
  }
  return 0;








|








4097
4098
4099
4100
4101
4102
4103
4104
4105
4106
4107
4108
4109
4110
4111

  ExprList *pOB;
  int iCol;
  int ii;

  if( (pOB = pBuilder->pOrderBy)==0 ) return 0;
  iCol = pIndex->aiColumn[0];
  for(ii=0; ii<pOB->nExpr; ii++){
    Expr *pExpr = sqlite3ExprSkipCollate(pOB->a[ii].pExpr);
    if( pExpr->op!=TK_COLUMN ) return 0;
    if( pExpr->iTable==iCursor ){
      if( pExpr->iColumn==iCol ) return 1;
      return 0;
    }
  }
  return 0;
︙︙
Changes to test/descidx1.test.
︙︙
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212

  do_test descidx1-4.2 {
    execsql {
      SELECT d FROM t2 ORDER BY a;
    }
  } {1.0 2.2 2.0 2.1 2.3 3.0 4.0 5.0 6.0}
  do_test descidx1-4.3 {
    execsql {
      SELECT d FROM t2 WHERE a>=2;
    }
  } {2.2 2.0 2.1 2.3 3.0 4.0 5.0 6.0}
  do_test descidx1-4.4 {
    execsql {
      SELECT d FROM t2 WHERE a>2;
    }
  } {3.0 4.0 5.0 6.0}
  do_test descidx1-4.5 {
    execsql {
      SELECT d FROM t2 WHERE a=2 AND b>'two';
    }
  } {2.2}








|




|








193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212

  do_test descidx1-4.2 {
    execsql {
      SELECT d FROM t2 ORDER BY a;
    }
  } {1.0 2.2 2.0 2.1 2.3 3.0 4.0 5.0 6.0}
  do_test descidx1-4.3 {
    execsql {
      SELECT d FROM t2 WHERE a>=2 ORDER BY a;
    }
  } {2.2 2.0 2.1 2.3 3.0 4.0 5.0 6.0}
  do_test descidx1-4.4 {
    execsql {
      SELECT d FROM t2 WHERE a>2 ORDER BY a;
    }
  } {3.0 4.0 5.0 6.0}
  do_test descidx1-4.5 {
    execsql {
      SELECT d FROM t2 WHERE a=2 AND b>'two';
    }
  } {2.2}
︙︙
Changes to test/e_reindex.test.
︙︙
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108

  set res [expr {[string length $lhs] - [string length $rhs]}]
  if {$res!=0} {return $res}
  return [string compare $lhs $rhs]
}
array set V {one 1 two 2 three 3 four 4 five 5 six 6 seven 7 eight 8}
proc sort_by_value {lhs rhs} {
  global V
  set res [expr {$V($lhs) - $V(rhs)}]
  if {$res!=0} {return $res}
  return [string compare $lhs $rhs]
}

db collate collA sort_by_length
db collate collB sort_by_value









|








94
95
96
97
98
99
100
101
102
103
104
105
106
107
108

  set res [expr {[string length $lhs] - [string length $rhs]}]
  if {$res!=0} {return $res}
  return [string compare $lhs $rhs]
}
array set V {one 1 two 2 three 3 four 4 five 5 six 6 seven 7 eight 8}
proc sort_by_value {lhs rhs} {
  global V
  set res [expr {$V($lhs) - $V($rhs)}]
  if {$res!=0} {return $res}
  return [string compare $lhs $rhs]
}

db collate collA sort_by_length
db collate collB sort_by_value
︙︙