/ Check-in [2438da79]
Login

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

Overview
Comment:Disable sorting by indices if there is a COLLATE subclause in the ORDER BY clause. (CVS 713)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:2438da791afb53b4f1c7a9ff9705d393c86f3cbd
User & Date: drh 2002-08-14 03:03:57
Context
2002-08-14
12:56
Implement probabilistic reader/writer locks under windows so that windows can have multiple simultaneous readers. (CVS 714) check-in: 2127de3f user: drh tags: trunk
03:03
Disable sorting by indices if there is a COLLATE subclause in the ORDER BY clause. (CVS 713) check-in: 2438da79 user: drh tags: trunk
00:10
Fix for ticket #132: make the working directory the last choice for where to write temporary files, not the first choice. (CVS 712) check-in: 26a4e7e7 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/where.c.

9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
...
176
177
178
179
180
181
182




183
184
185
186
187
188
189
**    May you share freely, never taking more than you give.
**
*************************************************************************
** 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 $
*/
#include "sqliteInt.h"

/*
** The query generator uses an array of instances of this structure to
** help it analyze the subexpressions of the WHERE clause.  Each WHERE
** clause subexpression is separated from the others by an AND operator.
................................................................................
  assert( pOrderBy!=0 );
  assert( pOrderBy->nExpr>0 );
  for(i=0; i<pOrderBy->nExpr; 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;




    }
    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 */
      return 0;
    }







|







 







>
>
>
>







9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
...
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
**    May you share freely, never taking more than you give.
**
*************************************************************************
** 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.62 2002/08/14 03:03:57 drh Exp $
*/
#include "sqliteInt.h"

/*
** The query generator uses an array of instances of this structure to
** help it analyze the subexpressions of the WHERE clause.  Each WHERE
** clause subexpression is separated from the others by an AND operator.
................................................................................
  assert( pOrderBy!=0 );
  assert( pOrderBy->nExpr>0 );
  for(i=0; i<pOrderBy->nExpr; 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 */
      return 0;
    }

Changes to test/where.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
438
439
440
441
442
443
444
















445
446
447
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# 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 $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Build some test data
#
do_test where-1.0 {
................................................................................
  }
} {1 0 2 1 3 1 nosort}
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}


















finish_test







|







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>



7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# 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.10 2002/08/14 03:03:58 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Build some test data
#
do_test where-1.0 {
................................................................................
  }
} {1 0 2 1 3 1 nosort}
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

Changes to www/datatypes.tcl.

1
2
3
4
5
6
7
8
9
10
11
...
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
#
# 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 $}

puts {<html>
<head>
  <title>Datatypes In SQLite</title>
</head>
<body bgcolor="white">
<h1 align="center">
................................................................................
sqlite&gt; SELECT typeof('abc'+123);
numeric
sqlite&gt; SELECT typeof('abc'||123);
text
</pre></blockquote>

<p>
For table columns, the datatype is determined by the datatype 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:
</p>

<blockquote>
BLOB<br>
CHAR<br>



|







 







|







1
2
3
4
5
6
7
8
9
10
11
...
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
#
# Run this script to generated a datatypes.html output file
#
set rcsid {$Id: datatypes.tcl,v 1.2 2002/08/14 03:03:58 drh Exp $}

puts {<html>
<head>
  <title>Datatypes In SQLite</title>
</head>
<body bgcolor="white">
<h1 align="center">
................................................................................
sqlite&gt; SELECT typeof('abc'+123);
numeric
sqlite&gt; SELECT typeof('abc'||123);
text
</pre></blockquote>

<p>
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:
</p>

<blockquote>
BLOB<br>
CHAR<br>