Index: ext/misc/spellfix.c ================================================================== --- ext/misc/spellfix.c +++ ext/misc/spellfix.c @@ -2670,11 +2670,11 @@ ** cause zWord to be NULL, so we look at the "command" column to see ** what special actions to take */ const char *zCmd = (const char*)sqlite3_value_text(argv[SPELLFIX_COL_COMMAND+2]); if( zCmd==0 ){ - pVTab->zErrMsg = sqlite3_mprintf("%s.word may not be NULL", + pVTab->zErrMsg = sqlite3_mprintf("NOT NULL constraint failed: %s.word", p->zTableName); return SQLITE_CONSTRAINT_NOTNULL; } if( strcmp(zCmd,"reset")==0 ){ /* Reset the edit cost table (if there is one). */ Index: ext/rtree/rtreeC.test ================================================================== --- ext/rtree/rtreeC.test +++ ext/rtree/rtreeC.test @@ -105,8 +105,58 @@ SELECT * FROM t, r_tree } { 0 0 1 {SCAN TABLE r_tree VIRTUAL TABLE INDEX 2:} 0 1 0 {SCAN TABLE t} } + +#------------------------------------------------------------------------- +# Test that the special CROSS JOIN handling works with rtree tables. +# +do_execsql_test 3.1 { + CREATE TABLE t1(x); + CREATE TABLE t2(y); + CREATE VIRTUAL TABLE t3 USING rtree(z, x1,x2, y1,y2); +} + +do_eqp_test 3.2.1 { SELECT * FROM t1 CROSS JOIN t2 } { + 0 0 0 {SCAN TABLE t1} + 0 1 1 {SCAN TABLE t2} +} +do_eqp_test 3.2.2 { SELECT * FROM t2 CROSS JOIN t1 } { + 0 0 0 {SCAN TABLE t2} 0 1 1 {SCAN TABLE t1} +} + +do_eqp_test 3.3.1 { SELECT * FROM t1 CROSS JOIN t3 } { + 0 0 0 {SCAN TABLE t1} + 0 1 1 {SCAN TABLE t3 VIRTUAL TABLE INDEX 2:} +} +do_eqp_test 3.3.2 { SELECT * FROM t3 CROSS JOIN t1 } { + 0 0 0 {SCAN TABLE t3 VIRTUAL TABLE INDEX 2:} + 0 1 1 {SCAN TABLE t1} +} + +#-------------------------------------------------------------------- +# Test that LEFT JOINs are not reordered if the right-hand-side is +# a virtual table. +# +reset_db +do_execsql_test 4.1 { + CREATE TABLE t1(a); + CREATE VIRTUAL TABLE t2 USING rtree(b, x1,x2); + + INSERT INTO t1 VALUES(1); + INSERT INTO t1 VALUES(2); + + INSERT INTO t2 VALUES(1, 0.0, 0.1); + INSERT INTO t2 VALUES(3, 0.0, 0.1); +} + +do_execsql_test 4.2 { + SELECT a, b FROM t1 LEFT JOIN t2 ON (+a = +b); +} {1 1 2 {}} + +do_execsql_test 4.3 { + SELECT b, a FROM t2 LEFT JOIN t1 ON (+a = +b); +} {1 1 3 {}} finish_test Index: src/select.c ================================================================== --- src/select.c +++ src/select.c @@ -4668,15 +4668,16 @@ /* Search for the index that has the lowest scan cost. ** ** (2011-04-15) Do not do a full scan of an unordered index. ** - ** (2013-10-03) Do not count the entires in a partial index. + ** (2013-10-03) Do not count the entries in a partial index. ** ** In practice the KeyInfo structure will not be used. It is only ** passed to keep OP_OpenRead happy. */ + if( !HasRowid(pTab) ) pBest = sqlite3PrimaryKeyIndex(pTab); for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){ if( pIdx->bUnordered==0 && pIdx->szIdxRowszTabRow && pIdx->pPartIdxWhere==0 && (!pBest || pIdx->szIdxRowszIdxRow) Index: src/sqlite.h.in ================================================================== --- src/sqlite.h.in +++ src/sqlite.h.in @@ -5329,10 +5329,11 @@ int idxNum; /* Number used to identify the index */ char *idxStr; /* String, possibly obtained from sqlite3_malloc */ int needToFreeIdxStr; /* Free idxStr using sqlite3_free() if true */ int orderByConsumed; /* True if output is already ordered */ double estimatedCost; /* Estimated cost of using this index */ + /* Fields below are only available in SQLite 3.8.2 and later */ sqlite3_int64 estimatedRows; /* Estimated number of rows returned */ }; /* ** CAPI3REF: Virtual Table Constraint Operator Codes Index: src/where.c ================================================================== --- src/where.c +++ src/where.c @@ -4700,11 +4700,12 @@ /* ** Add all WhereLoop objects for a table of the join identified by ** pBuilder->pNew->iTab. That table is guaranteed to be a virtual table. */ static int whereLoopAddVirtual( - WhereLoopBuilder *pBuilder /* WHERE clause information */ + WhereLoopBuilder *pBuilder, /* WHERE clause information */ + Bitmask mExtra ){ WhereInfo *pWInfo; /* WHERE analysis context */ Parse *pParse; /* The parsing context */ WhereClause *pWC; /* The WHERE clause */ struct SrcList_item *pSrc; /* The FROM clause term to search */ @@ -4790,11 +4791,11 @@ pIdxInfo->estimatedCost = SQLITE_BIG_DBL / (double)2; pIdxInfo->estimatedRows = 25; rc = vtabBestIndex(pParse, pTab, pIdxInfo); if( rc ) goto whereLoopAddVtab_exit; pIdxCons = *(struct sqlite3_index_constraint**)&pIdxInfo->aConstraint; - pNew->prereq = 0; + pNew->prereq = mExtra; mxTerm = -1; assert( pNew->nLSlot>=nConstraint ); for(i=0; iaLTerm[i] = 0; pNew->u.vtab.omitMask = 0; for(i=0; ipTab) ){ - rc = whereLoopAddVirtual(&sSubBuild); - for(i=0; ijointype|priorJoinType) & (JT_LEFT|JT_CROSS))!=0 ){ mExtra = mPrior; } priorJoinType = pItem->jointype; if( IsVirtual(pItem->pTab) ){ - rc = whereLoopAddVirtual(pBuilder); + rc = whereLoopAddVirtual(pBuilder, mExtra); }else{ rc = whereLoopAddBtree(pBuilder, mExtra); } if( rc==SQLITE_OK ){ rc = whereLoopAddOr(pBuilder, mExtra); Index: test/count.test ================================================================== --- test/count.test +++ test/count.test @@ -1,6 +1,6 @@ -# 2009 February 24 +# 2009-02-24 # # 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. @@ -9,11 +9,10 @@ # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing "SELECT count(*)" statements. # -# $Id: count.test,v 1.6 2009/06/05 17:09:12 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Test plan: @@ -22,12 +21,10 @@ # # count-1.*: Test that the OP_Count instruction appears to work on both # tables and indexes. Test both when they contain 0 entries, # when all entries are on the root page, and when the b-tree # forms a structure 2 and 3 levels deep. -# -# count-2.*: Test that # # do_test count-0.1 { db eval { @@ -186,7 +183,12 @@ CREATE INDEX t4i1 ON t4(b, a); SELECT count(*) FROM t4; } } {1} +do_execsql_test count-5.1 { + CREATE TABLE t5(a TEXT PRIMARY KEY, b VARCHAR(50)) WITHOUT ROWID; + INSERT INTO t5 VALUES('bison','jazz'); + SELECT count(*) FROM t5; +} {1} finish_test Index: test/tester.tcl ================================================================== --- test/tester.tcl +++ test/tester.tcl @@ -1045,11 +1045,11 @@ set G "\033\[32;1m" ;# Green fg set B "\033\[34;1m" ;# Red fg set D "\033\[39;0m" ;# Default fg foreach opcode { Seek SeekGe SeekGt SeekLe SeekLt NotFound Last Rewind - NoConflict Next Prev + NoConflict Next Prev VNext VPrev VFilter } { set color($opcode) $B } foreach opcode {ResultRow} { set color($opcode) $G @@ -1066,11 +1066,13 @@ if {$opcode == "Goto" && ($bSeenGoto==0 || ($p2 > $addr+10))} { set linebreak($p2) 1 set bSeenGoto 1 } - if {$opcode == "Next" || $opcode=="Prev"} { + if {$opcode=="Next" || $opcode=="Prev" + || $opcode=="VNext" || $opcode=="VPrev" + } { for {set i $p2} {$i<$addr} {incr i} { incr x($i) 2 } }