Index: src/shell_indexes.c ================================================================== --- src/shell_indexes.c +++ src/shell_indexes.c @@ -32,10 +32,11 @@ char *zColl; /* Collation sequence */ int bRange; /* True for range, false for eq */ int iCol; /* Constrained table column */ i64 depmask; /* Dependency mask */ int bFlag; /* Used by idxFindCompatible() */ + int bDesc; /* True if ORDER BY DESC */ IdxConstraint *pNext; /* Next constraint in pEq or pRange list */ IdxConstraint *pLink; /* See above */ }; /* @@ -196,15 +197,21 @@ break; } case SQLITE_WHEREINFO_ORDERBY: { IdxConstraint *pNew = idxNewConstraint(&p->rc, zVal); - IdxConstraint **pp; if( pNew==0 ) return; pNew->iCol = iVal; - for(pp=&p->pScan->pOrder; *pp; pp=&(*pp)->pNext); - *pp = pNew; + pNew->bDesc = (int)mask; + if( p->pScan->pOrder==0 ){ + p->pScan->pOrder = pNew; + }else{ + IdxConstraint *pIter; + for(pIter=p->pScan->pOrder; pIter->pNext; pIter=pIter->pNext); + pIter->pNext = pNew; + pIter->pLink = pNew; + } break; } case SQLITE_WHEREINFO_EQUALS: case SQLITE_WHEREINFO_RANGE: { @@ -506,10 +513,14 @@ zRet = idxAppendText(pRc, zRet, " COLLATE %Q", pCons->zColl); }else{ zRet = idxAppendText(pRc, zRet, " COLLATE %s", pCons->zColl); } } + + if( pCons->bDesc ){ + zRet = idxAppendText(pRc, zRet, " DESC"); + } return zRet; } /* ** Search database dbm for an index compatible with the one idxCreateFromCons() @@ -810,11 +821,13 @@ }else if( memcmp(&zDetail[i], " USING COVERING INDEX ", 22)==0 ){ zIdx = &zDetail[i+22]; } if( zIdx ){ int nIdx = 0; - while( zIdx[nIdx]!='\0' && zIdx[nIdx]!=' ' ) nIdx++; + while( zIdx[nIdx]!='\0' && (zIdx[nIdx]!=' ' || zIdx[nIdx+1]!='(') ){ + nIdx++; + } sqlite3_bind_text(pSelect, 1, zIdx, nIdx, SQLITE_STATIC); if( SQLITE_ROW==sqlite3_step(pSelect) ){ i64 iRowid = sqlite3_column_int64(pSelect, 0); const char *zSql = (const char*)sqlite3_column_text(pSelect, 1); if( iRowid>=pCtx->iIdxRowid ){ Index: src/where.c ================================================================== --- src/where.c +++ src/where.c @@ -3988,15 +3988,17 @@ int i; int bFirst = 1; for(i=0; ipOrderBy->nExpr; i++){ Expr *pExpr = p->pOrderBy->a[i].pExpr; CollSeq *pColl = sqlite3ExprCollSeq(pParse, pExpr); + assert( pColl || pParse->rc ); pExpr = sqlite3ExprSkipCollate(pExpr); if( pExpr->op==TK_COLUMN && pExpr->iTable==pItem->iCursor ){ int iCol = pExpr->iColumn; - if( iCol>=0 ){ - x(pCtx, SQLITE_WHEREINFO_ORDERBY, pColl->zName, iCol, 0); + if( pColl && iCol>=0 ){ + int bDesc = p->pOrderBy->a[i].sortOrder; + x(pCtx, SQLITE_WHEREINFO_ORDERBY, pColl->zName, iCol, bDesc); } } } } ADDED test/shell6.test Index: test/shell6.test ================================================================== --- /dev/null +++ test/shell6.test @@ -0,0 +1,191 @@ +# 2009 Nov 11 +# +# 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. +# +#*********************************************************************** +# +# The focus of this file is testing the CLI shell tool. Specifically, +# the ".recommend" command. +# +# + +# Test plan: +# +# shell1-1.*: Basic command line option handling. +# shell1-2.*: Basic "dot" command token parsing. +# shell1-3.*: Basic test that "dot" command can be called. +# +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set testprefix shell6 + +if {$tcl_platform(platform)=="windows"} { + set CLI "sqlite3.exe" +} else { + set CLI "./sqlite3" +} +if {![file executable $CLI]} { + finish_test + return +} + + +proc squish {txt} { + regsub -all {[[:space:]]+} $txt { } +} + +proc do_rec_test {tn sql res} { + set res [squish [string trim $res]] + set tst [subst -nocommands { + squish [lindex [catchcmd [list -rec test.db {$sql;}]] 1] + }] + uplevel [list do_test $tn $tst $res] +} + +proc do_setup_rec_test {tn setup sql res} { + reset_db + db eval $setup + uplevel [list do_rec_test $tn $sql $res] +} + + +do_setup_rec_test 1.1 { CREATE TABLE t1(a, b, c) } { + SELECT * FROM t1 +} { + (no new indexes) + 0|0|0|SCAN TABLE t1 +} + +do_setup_rec_test 1.2 { + CREATE TABLE t1(a, b, c); +} { + SELECT * FROM t1 WHERE b>?; +} { + CREATE INDEX t1_idx_00000062 ON t1(b) + 0|0|0|SEARCH TABLE t1 USING INDEX t1_idx_00000062 (b>?) +} + +do_setup_rec_test 1.3 { + CREATE TABLE t1(a, b, c); +} { + SELECT * FROM t1 WHERE b COLLATE nocase BETWEEN ? AND ? +} { + CREATE INDEX t1_idx_3e094c27 ON t1(b COLLATE NOCASE) + 0|0|0|SEARCH TABLE t1 USING INDEX t1_idx_3e094c27 (b>? AND b? AND b