/ Check-in [3b55095e]
Login

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

Overview
Comment:Most sorting problems are fixed. Dead code has been removed. 3 test failures remain but will be fixed by the new function API once it gets implemented. (CVS 1425)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:3b55095e036d68886d007239333bbf90acd15692
User & Date: drh 2004-05-21 02:14:25
Context
2004-05-21
03:01
Eliminate the OP_SortMakeRec and OP_SortCallback opcodes. Sort using the standard record format. (CVS 1426) check-in: 25643a01 user: drh tags: trunk
02:14
Most sorting problems are fixed. Dead code has been removed. 3 test failures remain but will be fixed by the new function API once it gets implemented. (CVS 1425) check-in: 3b55095e user: drh tags: trunk
02:11
Fix typo in bind.test that was causing a seg-fault. (CVS 1424) check-in: d1af1a4a user: danielk1977 tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/expr.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
....
1035
1036
1037
1038
1039
1040
1041

1042
1043
1044
1045
1046
1047

1048
1049
1050
1051
1052
1053
1054
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains routines used for analyzing expressions and
** for generating VDBE code that evaluates expressions in SQLite.
**
** $Id: expr.c,v 1.125 2004/05/20 22:16:29 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

char const *sqlite3AffinityString(char affinity){
  switch( affinity ){
    case SQLITE_AFF_INTEGER: return "i";
................................................................................
** The sqlite3ExprResolveIds() and sqlite3ExprCheck() routines must have
** both been called on the expression before it is passed to this routine.
*/
int sqlite3ExprType(Expr *p){
  if( p==0 ) return SQLITE_AFF_NONE;
  while( p ) switch( p->op ){
    case TK_CONCAT:

      return SQLITE_AFF_TEXT;

    case TK_AS:
      p = p->pLeft;
      break;


    case TK_NULL:
      return SQLITE_AFF_NONE;

    case TK_SELECT:   /*** FIX ME ****/
    case TK_COLUMN:   /*** FIX ME ****/
    case TK_CASE:     /*** FIX ME ****/








|







 







>






>







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
....
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains routines used for analyzing expressions and
** for generating VDBE code that evaluates expressions in SQLite.
**
** $Id: expr.c,v 1.126 2004/05/21 02:14:25 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

char const *sqlite3AffinityString(char affinity){
  switch( affinity ){
    case SQLITE_AFF_INTEGER: return "i";
................................................................................
** The sqlite3ExprResolveIds() and sqlite3ExprCheck() routines must have
** both been called on the expression before it is passed to this routine.
*/
int sqlite3ExprType(Expr *p){
  if( p==0 ) return SQLITE_AFF_NONE;
  while( p ) switch( p->op ){
    case TK_CONCAT:
    case TK_STRING:
      return SQLITE_AFF_TEXT;

    case TK_AS:
      p = p->pLeft;
      break;

    case TK_VARIABLE:
    case TK_NULL:
      return SQLITE_AFF_NONE;

    case TK_SELECT:   /*** FIX ME ****/
    case TK_COLUMN:   /*** FIX ME ****/
    case TK_CASE:     /*** FIX ME ****/

Changes to src/select.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
...
671
672
673
674
675
676
677
678
679
680


681
682
683
684
685
686
687
....
1222
1223
1224
1225
1226
1227
1228
1229
1230
1231
1232
1233




1234
1235
1236
1237
1238
1239
1240
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle SELECT statements in SQLite.
**
** $Id: select.c,v 1.172 2004/05/21 01:29:06 drh Exp $
*/
#include "sqliteInt.h"


/*
** Allocate a new Select structure and return a pointer to that
** structure.
................................................................................
** instead of OP_SortMakeKey.  Delete the OP_SortMakeKey opcode.
** All columns should have affinity NONE.  Handle ASC versus
** DESC sort order by defining a list of comparison functions to
** be used by the OP_Sort opcode.
*/
static void pushOntoSorter(Parse *pParse, Vdbe *v, ExprList *pOrderBy){
  int i;
#if 0
  char *zSortOrder;
  zSortOrder = sqliteMalloc( pOrderBy->nExpr + 1 );
  if( zSortOrder==0 ) return;
#endif
  for(i=0; i<pOrderBy->nExpr; i++){
#if 0
    int order = pOrderBy->a[i].sortOrder;
    int c;
    if( order==SQLITE_SO_ASC ){
      c = 'A';
    }else{
      c = 'D';
    }
    zSortOrder[i] = c;
#endif
    sqlite3ExprCode(pParse, pOrderBy->a[i].pExpr);
  }
#if 0
  zSortOrder[pOrderBy->nExpr] = 0;
  sqlite3VdbeOp3(v, OP_SortMakeKey, pOrderBy->nExpr, 0, zSortOrder, P3_DYNAMIC);
#endif
  sqlite3VdbeAddOp(v, OP_MakeKey, pOrderBy->nExpr, 0);
  sqlite3VdbeAddOp(v, OP_SortPut, 0, 0);
}

/*
** This routine generates the code for the inside of the inner loop
** of a SELECT.
................................................................................
      assert( iCol==-1 || (iCol>=0 && iCol<pTab->nCol) );
      if( iCol<0 ){
        zType = "INTEGER";
      }else{
        zType = pTab->aCol[iCol].zType;
      }
    }else{
      zType = "ANY";
      /** TODO:  Perhaps something related to the affinity of the 
      ** exprsssion? */


    }
    sqlite3VdbeOp3(v, OP_ColumnName, i + pEList->nExpr, 0, zType, 0);
  }
}

/*
** Generate code that will tell the VDBE the names of columns
................................................................................
** KeyInfo structure.  The number of columns in the KeyInfo is determined
** by the result set of the SELECT statement in the second argument.
**
** Make the new table a KeyAsData table if keyAsData is true.
*/
static void openTempIndex(Parse *pParse, Select *p, int iTab, int keyAsData){
  KeyInfo *pKeyInfo;
  int nColumn = p->pEList->nExpr;
  sqlite *db = pParse->db;
  int i;
  Vdbe *v = pParse->pVdbe;





  pKeyInfo = sqliteMalloc( sizeof(*pKeyInfo)+nColumn*sizeof(CollSeq*) );
  if( pKeyInfo==0 ) return;
  pKeyInfo->nField = nColumn;
  for(i=0; i<nColumn; i++){
    pKeyInfo->aColl[i] = db->pDfltColl;
  }
  sqlite3VdbeOp3(v, OP_OpenTemp, iTab, 0, (char*)pKeyInfo, P3_KEYINFO_HANDOFF);







|







 







<
<
<
<
<

<
<
<
<
<
<
<
<
<
<


<
<
<
<







 







|
|
|
>
>







 







|




>
>
>
>







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
315
316
317
318
319
320
321





322










323
324




325
326
327
328
329
330
331
...
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
....
1205
1206
1207
1208
1209
1210
1211
1212
1213
1214
1215
1216
1217
1218
1219
1220
1221
1222
1223
1224
1225
1226
1227
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle SELECT statements in SQLite.
**
** $Id: select.c,v 1.173 2004/05/21 02:14:25 drh Exp $
*/
#include "sqliteInt.h"


/*
** Allocate a new Select structure and return a pointer to that
** structure.
................................................................................
** instead of OP_SortMakeKey.  Delete the OP_SortMakeKey opcode.
** All columns should have affinity NONE.  Handle ASC versus
** DESC sort order by defining a list of comparison functions to
** be used by the OP_Sort opcode.
*/
static void pushOntoSorter(Parse *pParse, Vdbe *v, ExprList *pOrderBy){
  int i;





  for(i=0; i<pOrderBy->nExpr; i++){










    sqlite3ExprCode(pParse, pOrderBy->a[i].pExpr);
  }




  sqlite3VdbeAddOp(v, OP_MakeKey, pOrderBy->nExpr, 0);
  sqlite3VdbeAddOp(v, OP_SortPut, 0, 0);
}

/*
** This routine generates the code for the inside of the inner loop
** of a SELECT.
................................................................................
      assert( iCol==-1 || (iCol>=0 && iCol<pTab->nCol) );
      if( iCol<0 ){
        zType = "INTEGER";
      }else{
        zType = pTab->aCol[iCol].zType;
      }
    }else{
      switch( sqlite3ExprType(p) ){
        case SQLITE_AFF_TEXT:     zType = "TEXT";    break;
        case SQLITE_AFF_NUMERIC:  zType = "NUMERIC"; break;
        default:                  zType = "ANY";     break;
      }
    }
    sqlite3VdbeOp3(v, OP_ColumnName, i + pEList->nExpr, 0, zType, 0);
  }
}

/*
** Generate code that will tell the VDBE the names of columns
................................................................................
** KeyInfo structure.  The number of columns in the KeyInfo is determined
** by the result set of the SELECT statement in the second argument.
**
** Make the new table a KeyAsData table if keyAsData is true.
*/
static void openTempIndex(Parse *pParse, Select *p, int iTab, int keyAsData){
  KeyInfo *pKeyInfo;
  int nColumn;
  sqlite *db = pParse->db;
  int i;
  Vdbe *v = pParse->pVdbe;

  if( fillInColumnList(pParse, p) ){
    return;
  }
  nColumn = p->pEList->nExpr;
  pKeyInfo = sqliteMalloc( sizeof(*pKeyInfo)+nColumn*sizeof(CollSeq*) );
  if( pKeyInfo==0 ) return;
  pKeyInfo->nField = nColumn;
  for(i=0; i<nColumn; i++){
    pKeyInfo->aColl[i] = db->pDfltColl;
  }
  sqlite3VdbeOp3(v, OP_OpenTemp, iTab, 0, (char*)pKeyInfo, P3_KEYINFO_HANDOFF);

Changes to src/vdbe.c.

39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
....
4233
4234
4235
4236
4237
4238
4239
4240
4241
4242
4243
4244
4245
4246
4247
4248
4249
4250
4251
4252
4253
4254
4255
4256
4257
4258
4259
4260
4261
4262
4263
4264
4265
4266
4267
4268
4269
4270
4271
4272
4273
4274
4275
4276
4277
4278
4279
4280
4281
4282
4283
4284
4285
4286
4287
4288
4289
4290
4291
4292
4293
4294
4295
4296
4297
4298
4299
4300
4301
4302
4303
4304
4305
**
** Various scripts scan this source file in order to generate HTML
** documentation, headers files, or other derived files.  The formatting
** of the code in this file is, therefore, important.  See other comments
** in this file for details.  If in doubt, do not deviate from existing
** commenting and indentation practices when changing or adding code.
**
** $Id: vdbe.c,v 1.310 2004/05/21 01:29:06 drh Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>
#include "vdbeInt.h"

/*
................................................................................
  pTos++;
  pTos->n = nByte;
  pTos->z = (char*)azArg;
  pTos->flags = MEM_Str | MEM_Dyn;
  break;
}

/* Opcode: SortMakeKey * * P3
**
** Convert the top few entries of the stack into a sort key.  The
** number of stack entries consumed is the number of characters in 
** the string P3.  One character from P3 is prepended to each entry.
** The first character of P3 is prepended to the element lowest in
** the stack and the last character of P3 is prepended to the top of
** the stack.  All stack entries are separated by a \000 character
** in the result.  The whole key is terminated by two \000 characters
** in a row.
**
** "N" is substituted in place of the P3 character for NULL values.
**
** See also the MakeKey and MakeIdxKey opcodes.
*/
case OP_SortMakeKey: {
  char *zNewKey;
  int nByte;
  int nField;
  int i, j, k;
  Mem *pRec;

  nField = strlen(pOp->p3);
  pRec = &pTos[1-nField];
  nByte = 1;
  for(i=0; i<nField; i++, pRec++){
    if( pRec->flags & MEM_Null ){
      nByte += 2;
    }else{
      Stringify(pRec);
      nByte += pRec->n+2;
    }
  }
  zNewKey = sqliteMallocRaw( nByte );
  if( zNewKey==0 ) goto no_mem;
  j = 0;
  k = 0;
  for(pRec=&pTos[1-nField], i=0; i<nField; i++, pRec++){
    if( pRec->flags & MEM_Null ){
      zNewKey[j++] = 'N';
      zNewKey[j++] = 0;
      k++;
    }else{
      zNewKey[j++] = pOp->p3[k++];
      memcpy(&zNewKey[j], pRec->z, pRec->n-1);
      j += pRec->n-1;
      zNewKey[j++] = 0;
    }
  }
  zNewKey[j] = 0;
  assert( j<nByte );
  popStack(&pTos, nField);
  pTos++;
  pTos->n = nByte;
  pTos->flags = MEM_Str|MEM_Dyn;
  pTos->z = zNewKey;
  break;
}

/* Opcode: Sort * * P3
**
** Sort all elements on the sorter.  The algorithm is a
** mergesort.  The P3 argument is a pointer to a KeyInfo structure
** that describes the keys to be sorted.
*/
case OP_Sort: {







|







 







<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<







39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
....
4233
4234
4235
4236
4237
4238
4239



























































4240
4241
4242
4243
4244
4245
4246
**
** Various scripts scan this source file in order to generate HTML
** documentation, headers files, or other derived files.  The formatting
** of the code in this file is, therefore, important.  See other comments
** in this file for details.  If in doubt, do not deviate from existing
** commenting and indentation practices when changing or adding code.
**
** $Id: vdbe.c,v 1.311 2004/05/21 02:14:25 drh Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>
#include "vdbeInt.h"

/*
................................................................................
  pTos++;
  pTos->n = nByte;
  pTos->z = (char*)azArg;
  pTos->flags = MEM_Str | MEM_Dyn;
  break;
}




























































/* Opcode: Sort * * P3
**
** Sort all elements on the sorter.  The algorithm is a
** mergesort.  The P3 argument is a pointer to a KeyInfo structure
** that describes the keys to be sorted.
*/
case OP_Sort: {

Changes to test/pragma.test.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for the PRAGMA command.
#
# $Id: pragma.test,v 1.10 2004/05/13 11:34:17 danielk1977 Exp $

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

# Delete the preexisting database to avoid the special setup
# that the "all.test" script does.
#
................................................................................
  }
  sqlite_datatypes $::DB {SELECT * FROM t1}
} {INTEGER TEXT WHATEVER CLOB BLOB VARCHAR(123) nVaRcHaR(432)}
do_test pragma-2.4 {
  sqlite_datatypes $::DB {
     SELECT 1, 'hello', NULL
  }
} {NUMERIC TEXT TEXT}
do_test pragma-2.5 {
  sqlite_datatypes $::DB {
     SELECT 1+2 AS X, 'hello' || 5 AS Y, NULL AS Z
  }
} {NUMERIC TEXT TEXT}
do_test pragma-2.6 {
  execsql {
    CREATE VIEW v1 AS SELECT a+b, b||c, * FROM t1;
  }
  sqlite_datatypes $::DB {SELECT * FROM v1}
} {NUMERIC TEXT INTEGER TEXT WHATEVER CLOB BLOB VARCHAR(123) nVaRcHaR(432)}
do_test pragma-2.7 {







|







 







|




|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for the PRAGMA command.
#
# $Id: pragma.test,v 1.11 2004/05/21 02:14:25 drh Exp $

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

# Delete the preexisting database to avoid the special setup
# that the "all.test" script does.
#
................................................................................
  }
  sqlite_datatypes $::DB {SELECT * FROM t1}
} {INTEGER TEXT WHATEVER CLOB BLOB VARCHAR(123) nVaRcHaR(432)}
do_test pragma-2.4 {
  sqlite_datatypes $::DB {
     SELECT 1, 'hello', NULL
  }
} {NUMERIC TEXT ANY}
do_test pragma-2.5 {
  sqlite_datatypes $::DB {
     SELECT 1+2 AS X, 'hello' || 5 AS Y, NULL AS Z
  }
} {NUMERIC TEXT ANY}
do_test pragma-2.6 {
  execsql {
    CREATE VIEW v1 AS SELECT a+b, b||c, * FROM t1;
  }
  sqlite_datatypes $::DB {SELECT * FROM v1}
} {NUMERIC TEXT INTEGER TEXT WHATEVER CLOB BLOB VARCHAR(123) nVaRcHaR(432)}
do_test pragma-2.7 {

Changes to test/select3.test.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
#    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 aggregate functions and the
# GROUP BY and HAVING clauses of SELECT statements.
#
# $Id: select3.test,v 1.8 2003/01/31 17:16:37 drh Exp $

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

# Build some test data
#
do_test select3-1.0 {
................................................................................
  }
} {3 4 4 8 5 15}
do_test select3-4.3 {
  execsql {
    SELECT log, count(*) FROM t1 
    GROUP BY log 
    HAVING count(*)>=4 
    ORDER BY max(n)
  }
} {3 4 4 8 5 15}
do_test select3-4.4 {
  execsql {
    SELECT log AS x, count(*) AS y FROM t1 
    GROUP BY x
    HAVING y>=4 
    ORDER BY max(n)
  }
} {3 4 4 8 5 15}
do_test select3-4.5 {
  execsql {
    SELECT log AS x FROM t1 
    GROUP BY x
    HAVING count(*)>=4 
    ORDER BY max(n)
  }
} {3 4 5}

do_test select3-5.1 {
  execsql {
    SELECT log, count(*), avg(n), max(n+log*2) FROM t1 
    GROUP BY log 
    ORDER BY max(n+log*2), avg(n)
  }
} {0 1 1 1 1 1 2 4 2 2 3.5 8 3 4 6.5 14 4 8 12.5 24 5 15 24 41}
do_test select3-5.2 {
  execsql {
    SELECT log, count(*), avg(n), max(n+log*2) FROM t1 
    GROUP BY log 
    ORDER BY max(n+log*2), min(log,avg(n))
  }
} {0 1 1 1 1 1 2 4 2 2 3.5 8 3 4 6.5 14 4 8 12.5 24 5 15 24 41}

# Test sorting of GROUP BY results in the presence of an index
# on the GROUP BY column.
#
do_test select3-6.1 {







|







 







|







|







|







|






|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
#    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 aggregate functions and the
# GROUP BY and HAVING clauses of SELECT statements.
#
# $Id: select3.test,v 1.9 2004/05/21 02:14:25 drh Exp $

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

# Build some test data
#
do_test select3-1.0 {
................................................................................
  }
} {3 4 4 8 5 15}
do_test select3-4.3 {
  execsql {
    SELECT log, count(*) FROM t1 
    GROUP BY log 
    HAVING count(*)>=4 
    ORDER BY max(n)+0
  }
} {3 4 4 8 5 15}
do_test select3-4.4 {
  execsql {
    SELECT log AS x, count(*) AS y FROM t1 
    GROUP BY x
    HAVING y>=4 
    ORDER BY max(n)+0
  }
} {3 4 4 8 5 15}
do_test select3-4.5 {
  execsql {
    SELECT log AS x FROM t1 
    GROUP BY x
    HAVING count(*)>=4 
    ORDER BY max(n)+0
  }
} {3 4 5}

do_test select3-5.1 {
  execsql {
    SELECT log, count(*), avg(n), max(n+log*2) FROM t1 
    GROUP BY log 
    ORDER BY max(n+log*2)+0, avg(n)+0
  }
} {0 1 1 1 1 1 2 4 2 2 3.5 8 3 4 6.5 14 4 8 12.5 24 5 15 24 41}
do_test select3-5.2 {
  execsql {
    SELECT log, count(*), avg(n), max(n+log*2) FROM t1 
    GROUP BY log 
    ORDER BY max(n+log*2)+0, min(log,avg(n))+0
  }
} {0 1 1 1 1 1 2 4 2 2 3.5 8 3 4 6.5 14 4 8 12.5 24 5 15 24 41}

# Test sorting of GROUP BY results in the presence of an index
# on the GROUP BY column.
#
do_test select3-6.1 {

Changes to test/sort.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318

319
320
321
322

323
324
325
326
327

328
329
330
331
332
333
334
335
#    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 CREATE TABLE statement.
#
# $Id: sort.test,v 1.10 2004/05/14 11:00:53 danielk1977 Exp $

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

# Create a bunch of data to sort against
#
do_test sort-1.0 {
................................................................................
    SELECT a FROM t4 UNION SELECT a FROM v4 ORDER BY 1;
  }
} {1 2 11 12}
do_test sort-7.6 {
  execsql {
    SELECT b FROM t4 UNION SELECT a FROM v4 ORDER BY 1;
  }
} {1 2 11 12}
do_test sort-7.7 {
  execsql {
    SELECT a FROM t4 UNION SELECT b FROM v4 ORDER BY 1;
  }
} {1 2 11 12}
do_test sort-7.8 {
  execsql {
    SELECT b FROM t4 UNION SELECT b FROM v4 ORDER BY 1;
  }
} {1 11 12 2}

do_test sort-7.9 {
  execsql {
    SELECT b FROM t4 UNION SELECT b FROM v4 ORDER BY 1 COLLATE numeric;
  }

} {1 2 11 12}
do_test sort-7.10 {
  execsql {
    SELECT b FROM t4 UNION SELECT b FROM v4 ORDER BY 1 COLLATE integer;
  }

} {1 2 11 12}
do_test sort-7.11 {
  execsql {
    SELECT b FROM t4 UNION SELECT b FROM v4 ORDER BY 1 COLLATE text;
  }
} {1 11 12 2}
do_test sort-7.12 {
  execsql {







|







 







|




|





>
|
|
|
<
>
|
|
|
|
<
>
|







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322

323
324
325
326
327

328
329
330
331
332
333
334
335
336
#    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 CREATE TABLE statement.
#
# $Id: sort.test,v 1.11 2004/05/21 02:14:25 drh Exp $

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

# Create a bunch of data to sort against
#
do_test sort-1.0 {
................................................................................
    SELECT a FROM t4 UNION SELECT a FROM v4 ORDER BY 1;
  }
} {1 2 11 12}
do_test sort-7.6 {
  execsql {
    SELECT b FROM t4 UNION SELECT a FROM v4 ORDER BY 1;
  }
} {1 2 11 12 1 11 12 2}  ;# text from t4.b and numeric from v4.a
do_test sort-7.7 {
  execsql {
    SELECT a FROM t4 UNION SELECT b FROM v4 ORDER BY 1;
  }
} {1 2 11 12 1 11 12 2} ;# numeric from t4.a and text from v4.b
do_test sort-7.8 {
  execsql {
    SELECT b FROM t4 UNION SELECT b FROM v4 ORDER BY 1;
  }
} {1 11 12 2}
#### Version 3 works differently here:
#do_test sort-7.9 {
#  execsql {
#    SELECT b FROM t4 UNION SELECT b FROM v4 ORDER BY 1 COLLATE numeric;

#  }
#} {1 2 11 12}
#do_test sort-7.10 {
#  execsql {
#    SELECT b FROM t4 UNION SELECT b FROM v4 ORDER BY 1 COLLATE integer;

#  }
#} {1 2 11 12}
do_test sort-7.11 {
  execsql {
    SELECT b FROM t4 UNION SELECT b FROM v4 ORDER BY 1 COLLATE text;
  }
} {1 11 12 2}
do_test sort-7.12 {
  execsql {

Changes to test/where.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
497
498
499
500
501
502
503

504
505
506
507

508
509
510
511
512

513
514
515
516
517
518
519
520
#    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.19 2004/05/19 14:56:57 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}

# Tests for reverse-order sorting.







|







 







>
|
|
|
<
>
|
|
|
|
<
>
|







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
497
498
499
500
501
502
503
504
505
506
507

508
509
510
511
512

513
514
515
516
517
518
519
520
521
#    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.20 2004/05/21 02:14:25 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}
#### Version 3 does not work this way:
#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}

# Tests for reverse-order sorting.