Index: src/global.c
==================================================================
--- src/global.c
+++ src/global.c
@@ -131,19 +131,24 @@
#ifndef SQLITE_USE_URI
# define SQLITE_USE_URI 0
#endif
+#ifndef SQLITE_ALLOW_COVERING_INDEX_SCAN
+# define SQLITE_ALLOW_COVERING_INDEX_SCAN 1
+#endif
+
/*
** The following singleton contains the global configuration for
** the SQLite library.
*/
SQLITE_WSD struct Sqlite3Config sqlite3Config = {
SQLITE_DEFAULT_MEMSTATUS, /* bMemstat */
1, /* bCoreMutex */
SQLITE_THREADSAFE==1, /* bFullMutex */
SQLITE_USE_URI, /* bOpenUri */
+ SQLITE_ALLOW_COVERING_INDEX_SCAN, /* bUseCis */
0x7ffffffe, /* mxStrlen */
128, /* szLookaside */
500, /* nLookaside */
{0,0,0,0,0,0,0,0}, /* m */
{0,0,0,0,0,0,0,0,0}, /* mutex */
Index: src/main.c
==================================================================
--- src/main.c
+++ src/main.c
@@ -472,10 +472,15 @@
case SQLITE_CONFIG_URI: {
sqlite3GlobalConfig.bOpenUri = va_arg(ap, int);
break;
}
+
+ case SQLITE_CONFIG_COVERING_INDEX_SCAN: {
+ sqlite3GlobalConfig.bUseCis = va_arg(ap, int);
+ break;
+ }
default: {
rc = SQLITE_ERROR;
break;
}
Index: src/sqlite.h.in
==================================================================
--- src/sqlite.h.in
+++ src/sqlite.h.in
@@ -1560,10 +1560,22 @@
** connection is opened. If it is globally disabled, filenames are
** only interpreted as URIs if the SQLITE_OPEN_URI flag is set when the
** database connection is opened. By default, URI handling is globally
** disabled. The default value may be changed by compiling with the
** [SQLITE_USE_URI] symbol defined.
+**
+** [[SQLITE_CONFIG_COVERING_INDEX_SCAN]] SQLITE_CONFIG_COVERING_INDEX_SCAN
+** This option taks a single integer argument which is interpreted as
+** a boolean in order to enable or disable the use of covering indices for
+** full table scans in the query optimizer. The default setting is determined
+** by the [SQLITE_ALLOW_COVERING_INDEX_SCAN] compile-time option, or is "on"
+** if that compile-time option is omitted.
+** The ability to disable the use of covering indices for full table scans
+** is because some incorrectly coded legacy applications might malfunction
+** malfunction when the optimization is enabled. Providing the ability to
+** disable the optimization allows the older, buggy application code to work
+** without change even with newer versions of SQLite.
**
** [[SQLITE_CONFIG_PCACHE]] [[SQLITE_CONFIG_GETPCACHE]]
** SQLITE_CONFIG_PCACHE and SQLITE_CONFIG_GETPCACHE
** These options are obsolete and should not be used by new code.
** They are retained for backwards compatibility but are now no-ops.
@@ -1586,10 +1598,11 @@
#define SQLITE_CONFIG_GETPCACHE 15 /* no-op */
#define SQLITE_CONFIG_LOG 16 /* xFunc, void* */
#define SQLITE_CONFIG_URI 17 /* int */
#define SQLITE_CONFIG_PCACHE2 18 /* sqlite3_pcache_methods2* */
#define SQLITE_CONFIG_GETPCACHE2 19 /* sqlite3_pcache_methods2* */
+#define SQLITE_CONFIG_COVERING_INDEX_SCAN 20 /* int */
/*
** CAPI3REF: Database Connection Configuration Options
**
** These constants are the available integer configuration options that
Index: src/sqliteInt.h
==================================================================
--- src/sqliteInt.h
+++ src/sqliteInt.h
@@ -966,10 +966,11 @@
#define SQLITE_ColumnCache 0x02 /* Disable the column cache */
#define SQLITE_GroupByOrder 0x04 /* Disable GROUPBY cover of ORDERBY */
#define SQLITE_FactorOutConst 0x08 /* Disable factoring out constants */
#define SQLITE_IdxRealAsInt 0x10 /* Store REAL as INT in indices */
#define SQLITE_DistinctOpt 0x20 /* DISTINCT using indexes */
+#define SQLITE_CoverIdxScan 0x40 /* Disable covering index scans */
#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
@@ -2459,10 +2460,11 @@
struct Sqlite3Config {
int bMemstat; /* True to enable memory status */
int bCoreMutex; /* True to enable core mutexing */
int bFullMutex; /* True to enable full mutexing */
int bOpenUri; /* True to interpret filenames as URIs */
+ int bUseCis; /* Use covering indices for full-scans */
int mxStrlen; /* Maximum string length */
int szLookaside; /* Default lookaside buffer size */
int nLookaside; /* Default lookaside buffer count */
sqlite3_mem_methods m; /* Low-level memory allocation interface */
sqlite3_mutex_methods mutex; /* Low-level mutex interface */
Index: src/test1.c
==================================================================
--- src/test1.c
+++ src/test1.c
@@ -5938,10 +5938,11 @@
{ "column-cache", SQLITE_ColumnCache },
{ "groupby-order", SQLITE_GroupByOrder },
{ "factor-constants", SQLITE_FactorOutConst },
{ "real-as-int", SQLITE_IdxRealAsInt },
{ "distinct-opt", SQLITE_DistinctOpt },
+ { "cover-idx-scan", SQLITE_CoverIdxScan },
};
if( objc!=4 ){
Tcl_WrongNumArgs(interp, 1, objv, "DB OPT BOOLEAN");
return TCL_ERROR;
Index: src/test_malloc.c
==================================================================
--- src/test_malloc.c
+++ src/test_malloc.c
@@ -1194,10 +1194,39 @@
rc = sqlite3_config(SQLITE_CONFIG_URI, bOpenUri);
Tcl_SetResult(interp, (char *)sqlite3TestErrorName(rc), TCL_VOLATILE);
return TCL_OK;
}
+
+/*
+** Usage: sqlite3_config_cis BOOLEAN
+**
+** Enables or disables the use of the covering-index scan optimization.
+** SQLITE_CONFIG_COVERING_INDEX_SCAN.
+*/
+static int test_config_cis(
+ void * clientData,
+ Tcl_Interp *interp,
+ int objc,
+ Tcl_Obj *CONST objv[]
+){
+ int rc;
+ int bUseCis;
+
+ if( objc!=2 ){
+ Tcl_WrongNumArgs(interp, 1, objv, "BOOL");
+ return TCL_ERROR;
+ }
+ if( Tcl_GetBooleanFromObj(interp, objv[1], &bUseCis) ){
+ return TCL_ERROR;
+ }
+
+ rc = sqlite3_config(SQLITE_CONFIG_COVERING_INDEX_SCAN, bUseCis);
+ Tcl_SetResult(interp, (char *)sqlite3TestErrorName(rc), TCL_VOLATILE);
+
+ return TCL_OK;
+}
/*
** Usage: sqlite3_dump_memsys3 FILENAME
** sqlite3_dump_memsys5 FILENAME
**
@@ -1445,10 +1474,11 @@
{ "sqlite3_config_heap", test_config_heap ,0 },
{ "sqlite3_config_memstatus", test_config_memstatus ,0 },
{ "sqlite3_config_lookaside", test_config_lookaside ,0 },
{ "sqlite3_config_error", test_config_error ,0 },
{ "sqlite3_config_uri", test_config_uri ,0 },
+ { "sqlite3_config_cis", test_config_cis ,0 },
{ "sqlite3_db_config_lookaside",test_db_config_lookaside ,0 },
{ "sqlite3_dump_memsys3", test_dump_memsys3 ,3 },
{ "sqlite3_dump_memsys5", test_dump_memsys3 ,5 },
{ "sqlite3_install_memsys3", test_install_memsys3 ,0 },
{ "sqlite3_memdebug_vfs_oom_test", test_vfs_oom_test ,0 },
Index: src/where.c
==================================================================
--- src/where.c
+++ src/where.c
@@ -262,10 +262,11 @@
#define WHERE_UNIQUE 0x04000000 /* Selects no more than one row */
#define WHERE_VIRTUALTABLE 0x08000000 /* Use virtual-table processing */
#define WHERE_MULTI_OR 0x10000000 /* OR using multiple indices */
#define WHERE_TEMP_INDEX 0x20000000 /* Uses an ephemeral index */
#define WHERE_DISTINCT 0x40000000 /* Correct order for DISTINCT */
+#define WHERE_COVER_SCAN 0x80000000 /* Full scan of a covering index */
/*
** Initialize a preallocated WhereClause structure.
*/
static void whereClauseInit(
@@ -3131,11 +3132,11 @@
/* If currently calculating the cost of using an index (not the IPK
** index), determine if all required column data may be obtained without
** using the main table (i.e. if the index is a covering
** index for this query). If it is, set the WHERE_IDX_ONLY flag in
** wsFlags. Otherwise, set the bLookup variable to true. */
- if( pIdx && wsFlags ){
+ if( pIdx ){
Bitmask m = pSrc->colUsed;
int j;
for(j=0; jnColumn; j++){
int x = pIdx->aiColumn[j];
if( xwctrlFlags & WHERE_ONEPASS_DESIRED)==0
+ && sqlite3GlobalConfig.bUseCis
+#ifndef SQLITE_OMIT_BUILTIN_TEST
+ && (pParse->db->flags & SQLITE_CoverIdxScan)==0
+#endif
+ ){
+ /* This index is not useful for indexing, but it is a covering index.
+ ** A full-scan of the index might be a little faster than a full-scan
+ ** of the table, so give this case a cost slightly less than a table
+ ** scan. */
+ cost = aiRowEst[0]*3 + pProbe->nColumn;
+ wsFlags |= WHERE_COVER_SCAN|WHERE_COLUMN_RANGE;
+ }else if( (wsFlags & WHERE_NOT_FULLSCAN)==0 ){
/* The cost of a full table scan is a number of move operations equal
** to the number of rows in the table.
**
** We add an additional 4x penalty to full table scans. This causes
** the cost function to err on the side of choosing an index over
@@ -3207,10 +3221,11 @@
** choosing a full scan. This 4x full-scan penalty is an arguable
** decision and one which we expect to revisit in the future. But
** it seems to be working well enough at the moment.
*/
cost = aiRowEst[0]*4;
+ wsFlags &= ~WHERE_IDX_ONLY;
}else{
log10N = estLog(aiRowEst[0]);
cost = nRow;
if( pIdx ){
if( bLookup ){
@@ -4250,10 +4265,15 @@
pLevel->op = OP_Prev;
}else{
pLevel->op = OP_Next;
}
pLevel->p1 = iIdxCur;
+ if( pLevel->plan.wsFlags & WHERE_COVER_SCAN ){
+ pLevel->p5 = SQLITE_STMTSTATUS_FULLSCAN_STEP;
+ }else{
+ assert( pLevel->p5==0 );
+ }
}else
#ifndef SQLITE_OMIT_OR_OPTIMIZATION
if( pLevel->plan.wsFlags & WHERE_MULTI_OR ){
/* Case 4: Two or more separately indexed terms connected by OR
@@ -5126,31 +5146,33 @@
** index name is '*'.
*/
for(i=0; ia[i];
+ w = pLevel->plan.wsFlags;
pTabItem = &pTabList->a[pLevel->iFrom];
z = pTabItem->zAlias;
if( z==0 ) z = pTabItem->pTab->zName;
n = sqlite3Strlen30(z);
if( n+nQPlan < sizeof(sqlite3_query_plan)-10 ){
- if( pLevel->plan.wsFlags & WHERE_IDX_ONLY ){
+ if( (w & WHERE_IDX_ONLY)!=0 && (w & WHERE_COVER_SCAN)==0 ){
memcpy(&sqlite3_query_plan[nQPlan], "{}", 2);
nQPlan += 2;
}else{
memcpy(&sqlite3_query_plan[nQPlan], z, n);
nQPlan += n;
}
sqlite3_query_plan[nQPlan++] = ' ';
}
- testcase( pLevel->plan.wsFlags & WHERE_ROWID_EQ );
- testcase( pLevel->plan.wsFlags & WHERE_ROWID_RANGE );
- if( pLevel->plan.wsFlags & (WHERE_ROWID_EQ|WHERE_ROWID_RANGE) ){
+ testcase( w & WHERE_ROWID_EQ );
+ testcase( w & WHERE_ROWID_RANGE );
+ if( w & (WHERE_ROWID_EQ|WHERE_ROWID_RANGE) ){
memcpy(&sqlite3_query_plan[nQPlan], "* ", 2);
nQPlan += 2;
- }else if( (pLevel->plan.wsFlags & WHERE_INDEXED)!=0 ){
+ }else if( (w & WHERE_INDEXED)!=0 && (w & WHERE_COVER_SCAN)==0 ){
n = sqlite3Strlen30(pLevel->plan.u.pIdx->zName);
if( n+nQPlan < sizeof(sqlite3_query_plan)-2 ){
memcpy(&sqlite3_query_plan[nQPlan], pLevel->plan.u.pIdx->zName, n);
nQPlan += n;
sqlite3_query_plan[nQPlan++] = ' ';
Index: test/analyze6.test
==================================================================
--- test/analyze6.test
+++ test/analyze6.test
@@ -59,18 +59,18 @@
# in EV. (Prior to the 2011-03-04 enhancement to where.c, this query would
# have used EV for the outer loop instead of CAT - which was about 3x slower.)
#
do_test analyze6-1.1 {
eqp {SELECT count(*) FROM ev, cat WHERE x=y}
-} {0 0 1 {SCAN TABLE cat (~16 rows)} 0 1 0 {SEARCH TABLE ev USING COVERING INDEX evy (y=?) (~32 rows)}}
+} {0 0 1 {SCAN TABLE cat USING COVERING INDEX catx (~16 rows)} 0 1 0 {SEARCH TABLE ev USING COVERING INDEX evy (y=?) (~32 rows)}}
# The same plan is chosen regardless of the order of the tables in the
# FROM clause.
#
do_test analyze6-1.2 {
eqp {SELECT count(*) FROM cat, ev WHERE x=y}
-} {0 0 0 {SCAN TABLE cat (~16 rows)} 0 1 1 {SEARCH TABLE ev USING COVERING INDEX evy (y=?) (~32 rows)}}
+} {0 0 0 {SCAN TABLE cat USING COVERING INDEX catx (~16 rows)} 0 1 1 {SEARCH TABLE ev USING COVERING INDEX evy (y=?) (~32 rows)}}
# Ticket [83ea97620bd3101645138b7b0e71c12c5498fe3d] 2011-03-30
# If ANALYZE is run on an empty table, make sure indices are used
# on the table.
Index: test/autovacuum.test
==================================================================
--- test/autovacuum.test
+++ test/autovacuum.test
@@ -112,11 +112,11 @@
set idx [lsearch $::tbl_data [make_str $d $ENTRY_LEN]]
set ::tbl_data [lreplace $::tbl_data $idx $idx]
}
do_test autovacuum-1.$tn.($delete).3 {
execsql {
- select a from av1
+ select a from av1 order by rowid
}
} $::tbl_data
}
# All rows have been deleted. Ensure the file has shrunk to 4 pages.
Index: test/collate4.test
==================================================================
--- test/collate4.test
+++ test/collate4.test
@@ -92,11 +92,11 @@
} {{} A B a b nosort}
do_test collate4-1.1.5 {
cksort {SELECT b FROM collate4t1 ORDER BY b COLLATE TEXT}
} {{} A B a b nosort}
do_test collate4-1.1.6 {
- cksort {SELECT b FROM collate4t1 ORDER BY b COLLATE NOCASE}
+ cksort {SELECT b FROM collate4t1 ORDER BY b COLLATE NOCASE, rowid}
} {{} a A b B sort}
do_test collate4-1.1.7 {
execsql {
CREATE TABLE collate4t2(
@@ -169,17 +169,17 @@
CREATE INDEX collate4i3 ON collate4t4(a COLLATE TEXT);
CREATE INDEX collate4i4 ON collate4t4(b COLLATE NOCASE);
}
} {}
do_test collate4-1.1.22 {
- cksort {SELECT a FROM collate4t4 ORDER BY a}
+ cksort {SELECT a FROM collate4t4 ORDER BY a, rowid}
} {{} a A b B sort}
do_test collate4-1.1.23 {
- cksort {SELECT a FROM collate4t4 ORDER BY a COLLATE NOCASE}
+ cksort {SELECT a FROM collate4t4 ORDER BY a COLLATE NOCASE, rowid}
} {{} a A b B sort}
do_test collate4-1.1.24 {
- cksort {SELECT a FROM collate4t4 ORDER BY a COLLATE TEXT}
+ cksort {SELECT a FROM collate4t4 ORDER BY a COLLATE TEXT, rowid}
} {{} A B a b nosort}
do_test collate4-1.1.25 {
cksort {SELECT b FROM collate4t4 ORDER BY b}
} {{} A B a b sort}
do_test collate4-1.1.26 {
@@ -220,11 +220,11 @@
} {{} A B a b sort}
do_test collate4-1.2.4 {
cksort {SELECT a FROM collate4t1 ORDER BY a, b}
} {{} A a B b nosort}
do_test collate4-1.2.5 {
- cksort {SELECT a FROM collate4t1 ORDER BY a, b COLLATE nocase}
+ cksort {SELECT a FROM collate4t1 ORDER BY a, b COLLATE nocase, rowid}
} {{} a A b B sort}
do_test collate4-1.2.6 {
cksort {SELECT a FROM collate4t1 ORDER BY a, b COLLATE text}
} {{} A a B b nosort}
@@ -269,14 +269,14 @@
INSERT INTO collate4t3 VALUES( 'A', 'A' );
CREATE INDEX collate4i2 ON collate4t3(a COLLATE TEXT, b COLLATE NOCASE);
}
} {}
do_test collate4-1.2.15 {
- cksort {SELECT a FROM collate4t3 ORDER BY a}
+ cksort {SELECT a FROM collate4t3 ORDER BY a, rowid}
} {{} a A b B sort}
do_test collate4-1.2.16 {
- cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE nocase}
+ cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE nocase, rowid}
} {{} a A b B sort}
do_test collate4-1.2.17 {
cksort {SELECT a FROM collate4t3 ORDER BY a COLLATE text}
} {{} A B a b nosort}
do_test collate4-1.2.18 {
@@ -362,11 +362,12 @@
execsql {
DROP INDEX collate4i1;
CREATE INDEX collate4i1 ON collate4t1(a COLLATE TEXT);
}
count {
- SELECT * FROM collate4t2, collate4t1 WHERE a = b;
+ SELECT * FROM collate4t2, collate4t1 WHERE a = b
+ ORDER BY collate4t2.rowid, collate4t1.rowid
}
} {A a A A 19}
do_test collate4-2.1.5 {
count {
SELECT * FROM collate4t2, collate4t1 WHERE b = a;
@@ -373,20 +374,22 @@
}
} {A A 4}
ifcapable subquery {
do_test collate4-2.1.6 {
count {
- SELECT a FROM collate4t1 WHERE a IN (SELECT * FROM collate4t2);
+ SELECT a FROM collate4t1 WHERE a IN (SELECT * FROM collate4t2)
+ ORDER BY rowid
}
} {a A 10}
do_test collate4-2.1.7 {
execsql {
DROP INDEX collate4i1;
CREATE INDEX collate4i1 ON collate4t1(a);
}
count {
- SELECT a FROM collate4t1 WHERE a IN (SELECT * FROM collate4t2);
+ SELECT a FROM collate4t1 WHERE a IN (SELECT * FROM collate4t2)
+ ORDER BY rowid
}
} {a A 6}
do_test collate4-2.1.8 {
count {
SELECT a FROM collate4t1 WHERE a IN ('z', 'a');
@@ -396,11 +399,11 @@
execsql {
DROP INDEX collate4i1;
CREATE INDEX collate4i1 ON collate4t1(a COLLATE TEXT);
}
count {
- SELECT a FROM collate4t1 WHERE a IN ('z', 'a');
+ SELECT a FROM collate4t1 WHERE a IN ('z', 'a') ORDER BY rowid;
}
} {a A 9}
}
do_test collate4-2.1.10 {
execsql {
Index: test/corruptD.test
==================================================================
--- test/corruptD.test
+++ test/corruptD.test
@@ -105,16 +105,16 @@
# containing the offset of the first free block in a page.
#
do_test corruptD-1.1.1 {
incr_change_counter
hexio_write test.db [expr 1024+1] FFFF
- catchsql { SELECT * FROM t1 }
+ catchsql { SELECT * FROM t1 ORDER BY rowid }
} {1 {database disk image is malformed}}
do_test corruptD-1.1.2 {
incr_change_counter
hexio_write test.db [expr 1024+1] [hexio_render_int32 1021]
- catchsql { SELECT * FROM t1 }
+ catchsql { SELECT * FROM t1 ORDER BY rowid }
} {1 {database disk image is malformed}}
#-------------------------------------------------------------------------
# The following tests, corruptD-1.2.*, focus on the offsets contained
# in the first 2 byte of each free-block on the free-list.
Index: test/corruptE.test
==================================================================
--- test/corruptE.test
+++ test/corruptE.test
@@ -47,11 +47,11 @@
INSERT OR IGNORE INTO t1 SELECT x*11,y FROM t1;
INSERT OR IGNORE INTO t1 SELECT x*13,y FROM t1;
INSERT OR IGNORE INTO t1 SELECT x*17,y FROM t1;
INSERT OR IGNORE INTO t1 SELECT x*19,y FROM t1;
CREATE INDEX t1i1 ON t1(x);
- CREATE TABLE t2 AS SELECT x,2 as y FROM t1 WHERE rowid%5!=0;
+ CREATE TABLE t2 AS SELECT x,2 as y FROM t1 WHERE rowid%5!=0 ORDER BY rowid;
COMMIT;
}
} {}
ifcapable {integrityck} {
ADDED test/coveridxscan.test
Index: test/coveridxscan.test
==================================================================
--- /dev/null
+++ test/coveridxscan.test
@@ -0,0 +1,93 @@
+# 2012 September 17
+#
+# 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.
+#
+#***********************************************************************
+#
+# Tests for the optimization which attempts to use a covering index
+# for a full-table scan (under the theory that the index will be smaller
+# and require less I/O and hence will run faster.)
+#
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+
+set testprefix coveridxscan
+
+do_test 1.1 {
+ db eval {
+ CREATE TABLE t1(a,b,c);
+ INSERT INTO t1 VALUES(5,4,3), (4,8,2), (3,2,1);
+ CREATE INDEX t1ab ON t1(a,b);
+ CREATE INDEX t1b ON t1(b);
+ SELECT a FROM t1;
+ }
+ # covering index used for the scan, hence values are increasing
+} {3 4 5}
+
+do_test 1.2 {
+ db eval {
+ SELECT a, c FROM t1;
+ }
+ # There is no covering index, hence the values are in rowid order
+} {5 3 4 2 3 1}
+
+do_test 1.3 {
+ db eval {
+ SELECT b FROM t1;
+ }
+ # Choice of two indices: use the one with fewest columns
+} {2 4 8}
+
+do_test 2.1 {
+ optimization_control db cover-idx-scan 0
+ db eval {SELECT a FROM t1}
+ # With the optimization turned off, output in rowid order
+} {5 4 3}
+do_test 2.2 {
+ db eval {SELECT a, c FROM t1}
+} {5 3 4 2 3 1}
+do_test 2.3 {
+ db eval {SELECT b FROM t1}
+} {4 8 2}
+
+db close
+sqlite3_shutdown
+sqlite3_config_cis 0
+sqlite3 db test.db
+
+do_test 3.1 {
+ db eval {SELECT a FROM t1}
+ # With the optimization configured off, output in rowid order
+} {5 4 3}
+do_test 3.2 {
+ db eval {SELECT a, c FROM t1}
+} {5 3 4 2 3 1}
+do_test 3.3 {
+ db eval {SELECT b FROM t1}
+} {4 8 2}
+
+db close
+sqlite3_shutdown
+sqlite3_config_cis 1
+sqlite3 db test.db
+
+# The CIS optimization is enabled again. Covering indices are once again
+# used for all table scans.
+do_test 4.1 {
+ db eval {SELECT a FROM t1}
+} {3 4 5}
+do_test 4.2 {
+ db eval {SELECT a, c FROM t1}
+} {5 3 4 2 3 1}
+do_test 4.3 {
+ db eval {SELECT b FROM t1}
+} {2 4 8}
+
+
+finish_test
Index: test/distinct.test
==================================================================
--- test/distinct.test
+++ test/distinct.test
@@ -173,11 +173,11 @@
do_execsql_test 2.$tn.1 "SELECT DISTINCT $sql" $res
do_temptables_test 2.$tn.2 "SELECT DISTINCT $sql" $temptables
}
do_execsql_test 2.A {
- SELECT (SELECT DISTINCT o.a FROM t1 AS i) FROM t1 AS o;
+ SELECT (SELECT DISTINCT o.a FROM t1 AS i) FROM t1 AS o ORDER BY rowid;
} {a A a A}
Index: test/e_createtable.test
==================================================================
--- test/e_createtable.test
+++ test/e_createtable.test
@@ -1589,11 +1589,11 @@
do_catchsql_test 4.17.$tn.2 "
INSERT INTO $tbl SELECT ((a%2)*a+3), 'three' FROM $tbl
" $res
do_test e_createtable-4.17.$tn.3 { sqlite3_get_autocommit db } $ac
- do_execsql_test 4.17.$tn.4 "SELECT * FROM $tbl" $data
+ do_execsql_test 4.17.$tn.4 "SELECT * FROM $tbl ORDER BY rowid" $data
}
catchsql COMMIT
# EVIDENCE-OF: R-12645-39772 Or, if a constraint definition does not
# include a conflict-clause or it is a CHECK constraint, the default
Index: test/e_fkey.test
==================================================================
--- test/e_fkey.test
+++ test/e_fkey.test
@@ -2058,20 +2058,20 @@
}
} {}
do_test e_fkey-45.2 {
execsql {
DELETE FROM pA WHERE rowid = 3;
- SELECT quote(x) FROM pA;
+ SELECT quote(x) FROM pA ORDER BY rowid;
}
} {X'0000' X'9999' X'1234'}
do_test e_fkey-45.3 {
execsql { SELECT quote(c) FROM cA }
} {X'0000'}
do_test e_fkey-45.4 {
execsql {
UPDATE pA SET x = X'8765' WHERE rowid = 4;
- SELECT quote(x) FROM pA;
+ SELECT quote(x) FROM pA ORDER BY rowid;
}
} {X'0000' X'9999' X'8765'}
do_test e_fkey-45.5 {
execsql { SELECT quote(c) FROM cB }
} {X'9999'}
@@ -2323,21 +2323,21 @@
}
} {}
do_test e_fkey-51.2 {
execsql {
UPDATE parent SET x = 22;
- SELECT * FROM parent ; SELECT 'xxx' ; SELECT a FROM child;
+ SELECT * FROM parent ORDER BY rowid; SELECT 'xxx' ; SELECT a FROM child;
}
} {22 21 23 xxx 22}
do_test e_fkey-51.3 {
execsql {
DELETE FROM child;
DELETE FROM parent;
INSERT INTO parent VALUES(-1);
INSERT INTO child VALUES(-1);
UPDATE parent SET x = 22;
- SELECT * FROM parent ; SELECT 'xxx' ; SELECT a FROM child;
+ SELECT * FROM parent ORDER BY rowid; SELECT 'xxx' ; SELECT a FROM child;
}
} {22 23 21 xxx 23}
#-------------------------------------------------------------------------
Index: test/e_select.test
==================================================================
--- test/e_select.test
+++ test/e_select.test
@@ -1024,11 +1024,11 @@
do_select_tests e_select-4.9 {
1 "SELECT group_concat(one), two FROM b1 GROUP BY two" {
4,5 f 1 o 7,6 s 3,2 t
}
2 "SELECT group_concat(one), sum(one) FROM b1 GROUP BY (one>4)" {
- 1,4,3,2 10 5,7,6 18
+ 1,2,3,4 10 5,6,7 18
}
3 "SELECT group_concat(one) FROM b1 GROUP BY (two>'o'), one%2" {
4 1,5 2,6 3,7
}
4 "SELECT group_concat(one) FROM b1 GROUP BY (one==2 OR two=='o')" {
Index: test/eqp.test
==================================================================
--- test/eqp.test
+++ test/eqp.test
@@ -60,11 +60,11 @@
0 0 0 {SCAN TABLE t1 USING COVERING INDEX i1 (~1000000 rows)}
}
do_eqp_test 1.4 {
SELECT a FROM t1 ORDER BY +a
} {
- 0 0 0 {SCAN TABLE t1 (~1000000 rows)}
+ 0 0 0 {SCAN TABLE t1 USING COVERING INDEX i1 (~1000000 rows)}
0 0 0 {USE TEMP B-TREE FOR ORDER BY}
}
do_eqp_test 1.5 {
SELECT a FROM t1 WHERE a=4
} {
@@ -164,11 +164,11 @@
}
det 2.3.2 "SELECT min(x) FROM t2" {
0 0 0 {SEARCH TABLE t2 USING COVERING INDEX t2i1 (~1 rows)}
}
det 2.3.3 "SELECT min(x), max(x) FROM t2" {
- 0 0 0 {SCAN TABLE t2 (~1000000 rows)}
+ 0 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1 (~1000000 rows)}
}
det 2.4.1 "SELECT * FROM t1 WHERE rowid=?" {
0 0 0 {SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}
}
@@ -337,19 +337,19 @@
do_eqp_test 4.3.1 {
SELECT x FROM t1 UNION SELECT x FROM t2
} {
1 0 0 {SCAN TABLE t1 (~1000000 rows)}
- 2 0 0 {SCAN TABLE t2 (~1000000 rows)}
+ 2 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1 (~1000000 rows)}
0 0 0 {COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION)}
}
do_eqp_test 4.3.2 {
SELECT x FROM t1 UNION SELECT x FROM t2 UNION SELECT x FROM t1
} {
2 0 0 {SCAN TABLE t1 (~1000000 rows)}
- 3 0 0 {SCAN TABLE t2 (~1000000 rows)}
+ 3 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1 (~1000000 rows)}
1 0 0 {COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)}
4 0 0 {SCAN TABLE t1 (~1000000 rows)}
0 0 0 {COMPOUND SUBQUERIES 1 AND 4 USING TEMP B-TREE (UNION)}
}
do_eqp_test 4.3.3 {
@@ -445,11 +445,11 @@
# 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2 2|0|0|SEARCH TABLE t1 USING
# INDEX i3 (b=?) (~10 rows)
det 5.9 {
SELECT (SELECT b FROM t1 WHERE a=0), (SELECT a FROM t1 WHERE b=t2.c) FROM t2
} {
- 0 0 0 {SCAN TABLE t2 (~1000000 rows)}
+ 0 0 0 {SCAN TABLE t2 USING COVERING INDEX i4 (~1000000 rows)}
0 0 0 {EXECUTE SCALAR SUBQUERY 1}
1 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) (~10 rows)}
0 0 0 {EXECUTE CORRELATED SCALAR SUBQUERY 2}
2 0 0 {SEARCH TABLE t1 USING INDEX i3 (b=?) (~10 rows)}
}
@@ -469,20 +469,20 @@
# EVIDENCE-OF: R-18544-33103 sqlite> EXPLAIN QUERY PLAN SELECT * FROM
# (SELECT * FROM t2 WHERE c=1), t1; 0|0|0|SEARCH TABLE t2 USING INDEX i4
# (c=?) (~10 rows) 0|1|1|SCAN TABLE t1 (~1000000 rows)
det 5.11 "SELECT * FROM (SELECT * FROM t2 WHERE c=1), t1" {
0 0 0 {SEARCH TABLE t2 USING INDEX i4 (c=?) (~10 rows)}
- 0 1 1 {SCAN TABLE t1 (~1000000 rows)}
+ 0 1 1 {SCAN TABLE t1 USING COVERING INDEX i2 (~1000000 rows)}
}
# EVIDENCE-OF: R-40701-42164 sqlite> EXPLAIN QUERY PLAN SELECT a FROM
# t1 UNION SELECT c FROM t2; 1|0|0|SCAN TABLE t1 (~1000000 rows)
# 2|0|0|SCAN TABLE t2 (~1000000 rows) 0|0|0|COMPOUND SUBQUERIES 1 AND 2
# USING TEMP B-TREE (UNION)
det 5.12 "SELECT a FROM t1 UNION SELECT c FROM t2" {
- 1 0 0 {SCAN TABLE t1 (~1000000 rows)}
- 2 0 0 {SCAN TABLE t2 (~1000000 rows)}
+ 1 0 0 {SCAN TABLE t1 USING COVERING INDEX i1 (~1000000 rows)}
+ 2 0 0 {SCAN TABLE t2 USING COVERING INDEX i4 (~1000000 rows)}
0 0 0 {COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION)}
}
# EVIDENCE-OF: R-61538-24748 sqlite> EXPLAIN QUERY PLAN SELECT a FROM
# t1 EXCEPT SELECT d FROM t2 ORDER BY 1; 1|0|0|SCAN TABLE t1 USING
Index: test/incrblob.test
==================================================================
--- test/incrblob.test
+++ test/incrblob.test
@@ -435,11 +435,11 @@
INSERT INTO blobs(k, v, i) VALUES('a', 'different', 'connection');
} db2
} {}
do_test incrblob-6.2 {
execsql {
- SELECT rowid FROM blobs
+ SELECT rowid FROM blobs ORDER BY rowid
}
} {1 2 3}
do_test incrblob-6.3 {
set rc [catch {
db incrblob blobs v 1
Index: test/intpkey.test
==================================================================
--- test/intpkey.test
+++ test/intpkey.test
@@ -374,11 +374,11 @@
SELECT * FROM t1 WHERE a=0;
}
} {0 zero entry 0}
do_test intpkey-5.2 {
execsql {
- SELECT rowid, a FROM t1
+ SELECT rowid, a FROM t1 ORDER BY rowid
}
} {-4 -4 0 0 5 5 6 6 11 11}
# Test the ability of the COPY command to put data into a
# table that contains an integer primary key.
Index: test/like.test
==================================================================
--- test/like.test
+++ test/like.test
@@ -404,11 +404,11 @@
set sqlite_like_count
} 12
do_test like-5.3 {
execsql {
CREATE TABLE t2(x TEXT COLLATE NOCASE);
- INSERT INTO t2 SELECT * FROM t1;
+ INSERT INTO t2 SELECT * FROM t1 ORDER BY rowid;
CREATE INDEX i2 ON t2(x COLLATE NOCASE);
}
set sqlite_like_count 0
queryplan {
SELECT x FROM t2 WHERE x LIKE 'abc%' ORDER BY 1
@@ -660,12 +660,12 @@
ifcapable explain {
do_test like-9.4.3 {
set res [sqlite3_exec_hex db {
EXPLAIN QUERY PLAN SELECT x FROM t2 WHERE x LIKE '%ff%25'
}]
- regexp {INDEX i2} $res
- } {0}
+ regexp {SCAN TABLE t2} $res
+ } {1}
}
do_test like-9.5.1 {
set res [sqlite3_exec_hex db {
SELECT x FROM t2 WHERE x LIKE '%fe%25'
}]
Index: test/stat.test
==================================================================
--- test/stat.test
+++ test/stat.test
@@ -74,15 +74,20 @@
} {}
do_execsql_test stat-2.1 {
CREATE TABLE t3(a PRIMARY KEY, b);
INSERT INTO t3(rowid, a, b) VALUES(2, a_string(111), a_string(222));
- INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3;
- INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3;
- INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3;
- INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3;
- INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3;
+ INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3
+ ORDER BY rowid;
+ INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3
+ ORDER BY rowid;
+ INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3
+ ORDER BY rowid;
+ INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3
+ ORDER BY rowid;
+ INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3
+ ORDER BY rowid;
SELECT name, path, pageno, pagetype, ncell, payload, unused, mx_payload
FROM stat WHERE name != 'sqlite_master';
} [list \
sqlite_autoindex_t3_1 / 3 internal 3 368 623 125 \
sqlite_autoindex_t3_1 /000/ 8 leaf 8 946 46 123 \
Index: test/tkt-385a5b56b9.test
==================================================================
--- test/tkt-385a5b56b9.test
+++ test/tkt-385a5b56b9.test
@@ -37,11 +37,11 @@
do_eqp_test 2.1 { SELECT DISTINCT x FROM t2 } {
0 0 0 {SCAN TABLE t2 USING COVERING INDEX t2x (~1000000 rows)}
}
do_eqp_test 2.2 { SELECT DISTINCT y FROM t2 } {
- 0 0 0 {SCAN TABLE t2 (~1000000 rows)}
+ 0 0 0 {SCAN TABLE t2 USING COVERING INDEX t2y (~1000000 rows)}
}
do_eqp_test 2.3 { SELECT DISTINCT x, y FROM t2 WHERE y=10 } {
0 0 0 {SEARCH TABLE t2 USING INDEX t2y (y=?) (~1 rows)}
}
@@ -49,6 +49,5 @@
do_eqp_test 2.4 { SELECT DISTINCT x, y FROM t2 WHERE x=10 } {
0 0 0 {SEARCH TABLE t2 USING INDEX t2x (x=?) (~1 rows)}
}
finish_test
-
Index: test/tkt-78e04e52ea.test
==================================================================
--- test/tkt-78e04e52ea.test
+++ test/tkt-78e04e52ea.test
@@ -42,11 +42,11 @@
} {}
do_test tkt-78e04-1.4 {
execsql {
EXPLAIN QUERY PLAN SELECT * FROM "" WHERE "" LIKE 'abc%';
}
-} {0 0 0 {SCAN TABLE (~500000 rows)}}
+} {0 0 0 {SCAN TABLE USING COVERING INDEX i1 (~500000 rows)}}
do_test tkt-78e04-1.5 {
execsql {
DROP TABLE "";
SELECT name FROM sqlite_master;
}
Index: test/triggerC.test
==================================================================
--- test/triggerC.test
+++ test/triggerC.test
@@ -220,11 +220,11 @@
catchsql { DROP TRIGGER t2_trig }
execsql { DELETE FROM t2 }
execsql $tdefn
catchsql {
INSERT INTO t2 VALUES(10);
- SELECT * FROM t2;
+ SELECT * FROM t2 ORDER BY rowid;
}
} $rc
}
do_test triggerC-2.2 {
@@ -545,11 +545,11 @@
} {
do_test triggerC-4.1.$n {
eval concat [execsql "
DELETE FROM log;
$insert ;
- SELECT * FROM log;
+ SELECT * FROM log ORDER BY rowid;
"]
} [join $log " "]
}
#-------------------------------------------------------------------------
@@ -582,12 +582,12 @@
} {
do_test triggerC-5.1.$n {
execsql "
BEGIN;
$dml ;
- SELECT * FROM t5g;
- SELECT * FROM t5;
+ SELECT * FROM t5g ORDER BY rowid;
+ SELECT * FROM t5 ORDER BY rowid;
ROLLBACK;
"
} [concat $t5g $t5]
}
do_test triggerC-5.2.0 {
@@ -609,12 +609,12 @@
} {
do_test triggerC-5.2.$n {
execsql "
BEGIN;
$dml ;
- SELECT * FROM t5g;
- SELECT * FROM t5;
+ SELECT * FROM t5g ORDER BY rowid;
+ SELECT * FROM t5 ORDER BY rowid;
ROLLBACK;
"
} [concat $t5g $t5]
}
do_test triggerC-5.3.0 {
@@ -631,12 +631,12 @@
} {
do_test triggerC-5.3.$n {
execsql "
BEGIN;
$dml ;
- SELECT * FROM t5g;
- SELECT * FROM t5;
+ SELECT * FROM t5g ORDER BY rowid;
+ SELECT * FROM t5 ORDER BY rowid;
ROLLBACK;
"
} [concat $t5g $t5]
}
do_test triggerC-5.3.8 {
Index: test/unordered.test
==================================================================
--- test/unordered.test
+++ test/unordered.test
@@ -49,11 +49,11 @@
{0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~1 rows)}}
{0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~1 rows)}
0 0 0 {USE TEMP B-TREE FOR ORDER BY}}
4 "SELECT max(a) FROM t1"
{0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (~1 rows)}}
- {0 0 0 {SEARCH TABLE t1 (~1 rows)}}
+ {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (~1 rows)}}
5 "SELECT group_concat(b) FROM t1 GROUP BY a"
{0 0 0 {SCAN TABLE t1 USING INDEX i1 (~128 rows)}}
{0 0 0 {SCAN TABLE t1 (~128 rows)} 0 0 0 {USE TEMP B-TREE FOR GROUP BY}}
6 "SELECT * FROM t1 WHERE a = ?"
Index: test/where.test
==================================================================
--- test/where.test
+++ test/where.test
@@ -1096,28 +1096,28 @@
} {1/1 1/4 4/1 4/4 sort}
do_test where-14.3 {
cksort {
SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, x.b
}
-} {1/1 1/4 4/1 4/4 nosort}
+} {1/4 1/1 4/4 4/1 nosort}
do_test where-14.4 {
cksort {
SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, x.b DESC
}
-} {1/1 1/4 4/1 4/4 nosort}
+} {1/4 1/1 4/4 4/1 nosort}
do_test where-14.5 {
# This test case changed from "nosort" to "sort". See ticket 2a5629202f.
cksort {
SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a||x.b
}
-} {4/1 4/4 1/1 1/4 sort}
+} {4/4 4/1 1/4 1/1 sort}
do_test where-14.6 {
# This test case changed from "nosort" to "sort". See ticket 2a5629202f.
cksort {
SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a||x.b DESC
}
-} {4/1 4/4 1/1 1/4 sort}
+} {4/4 4/1 1/4 1/1 sort}
do_test where-14.7 {
cksort {
SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, y.a||y.b
}
} {4/1 4/4 1/1 1/4 sort}
@@ -1128,11 +1128,11 @@
} {4/1 4/4 1/1 1/4 sort}
do_test where-14.7.2 {
cksort {
SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a, x.a||x.b
}
-} {4/1 4/4 1/1 1/4 nosort}
+} {4/4 4/1 1/4 1/1 nosort}
do_test where-14.8 {
cksort {
SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, y.a||y.b DESC
}
} {4/4 4/1 1/4 1/1 sort}
Index: test/where9.test
==================================================================
--- test/where9.test
+++ test/where9.test
@@ -690,11 +690,11 @@
}
} {scan 0 sort 0} ;# Add 100 to rowids 5 31 57 82 83 84 85 86 87
do_test where9-6.5.4 {
db eval {
SELECT count(*) FROM t1 UNION ALL
- SELECT a FROM t1 WHERE a%100 IN (5,31,57,82,83,84,85,86,87);
+ SELECT a FROM t1 WHERE a%100 IN (5,31,57,82,83,84,85,86,87) ORDER BY rowid;
ROLLBACK;
}
} {99 105 131 157 182 183 184 185 186 187}
do_test where9-6.6.1 {