Index: src/where.c ================================================================== --- src/where.c +++ src/where.c @@ -11,11 +11,11 @@ ************************************************************************* ** This module contains C code that generates VDBE code used to process ** the WHERE clause of SQL statements. Also found here are subroutines ** to generate VDBE code to evaluate expressions. ** -** $Id: where.c,v 1.61 2002/08/13 23:02:58 drh Exp $ +** $Id: where.c,v 1.62 2002/08/14 03:03:57 drh Exp $ */ #include "sqliteInt.h" /* ** The query generator uses an array of instances of this structure to @@ -178,10 +178,14 @@ for(i=0; inExpr; i++){ Expr *p; if( (pOrderBy->a[i].sortOrder & SQLITE_SO_DIRMASK)!=SQLITE_SO_ASC ){ /* Indices can only be used for ascending sort order */ return 0; + } + if( (pOrderBy->a[i].sortOrder & SQLITE_SO_TYPEMASK)!=SQLITE_SO_UNK ){ + /* Do not sort by index if there is a COLLATE clause */ + return 0; } p = pOrderBy->a[i].pExpr; if( p->op!=TK_COLUMN || p->iTable!=base ){ /* Can not use an index sort on anything that is not a column in the ** left-most table of the FROM clause */ Index: test/where.test ================================================================== --- test/where.test +++ test/where.test @@ -9,11 +9,11 @@ # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing the use of indices in WHERE clases. # -# $Id: where.test,v 1.9 2002/06/19 14:27:06 drh Exp $ +# $Id: where.test,v 1.10 2002/08/14 03:03:58 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Build some test data @@ -440,8 +440,24 @@ do_test where-6.16 { cksort { SELECT t3.a, t1.x FROM t3, t1 WHERE t3.a=t1.w ORDER BY t1.x, t3.a LIMIT 3 } } {1 0 2 1 3 1 sort} +do_test where-6.17 { + cksort { + SELECT y FROM t1 ORDER BY w COLLATE text LIMIT 3; + } +} {4 121 10201 sort} +do_test where-6.18 { + cksort { + SELECT y FROM t1 ORDER BY w COLLATE numeric LIMIT 3; + } +} {4 9 16 sort} +do_test where-6.19 { + cksort { + SELECT y FROM t1 ORDER BY w LIMIT 3; + } +} {4 9 16 nosort} + finish_test Index: www/datatypes.tcl ================================================================== --- www/datatypes.tcl +++ www/datatypes.tcl @@ -1,9 +1,9 @@ # # Run this script to generated a datatypes.html output file # -set rcsid {$Id: datatypes.tcl,v 1.1 2002/08/14 00:08:13 drh Exp $} +set rcsid {$Id: datatypes.tcl,v 1.2 2002/08/14 03:03:58 drh Exp $} puts { Datatypes In SQLite @@ -167,11 +167,11 @@ sqlite> SELECT typeof('abc'||123); text

-For table columns, the datatype is determined by the datatype declaration +For table columns, the datatype is determined by the type declaration of the CREATE TABLE statement. The datatype is text if and only if the type declaration contains one or more of the following strings: