/ Check-in [bb0254ab]
Login

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

Overview
Comment:Modify test scripts to work when SQLITE_OMIT_SUBQUERY (along with other OMIT macros) is defined. (CVS 2251)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: bb0254ab14417f0ab40f10f37cb63a60507f070a
User & Date: danielk1977 2005-01-21 03:12:15
Context
2005-01-21
04:25
Modify test scripts to work when SQLITE_OMIT_SUBQUERY (but no other OMIT macros) is defined. (CVS 2252) check-in: d4e19efc user: danielk1977 tags: trunk
03:12
Modify test scripts to work when SQLITE_OMIT_SUBQUERY (along with other OMIT macros) is defined. (CVS 2251) check-in: bb0254ab user: danielk1977 tags: trunk
02:34
Add a new test file for subqueries. (CVS 2250) check-in: de8ee3a2 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/parse.y.

10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
...
701
702
703
704
705
706
707

708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
**
*************************************************************************
** This file contains SQLite's grammar for SQL.  Process this file
** using the lemon parser generator to generate C code that runs
** the parser.  Lemon will also generate a header file containing
** numeric codes for all of the tokens.
**
** @(#) $Id: parse.y,v 1.161 2005/01/20 13:36:20 drh Exp $
*/
%token_prefix TK_
%token_type {Token}
%default_type {Token}
%extra_argument {Parse *pParse}
%syntax_error {
  if( pParse->zErrMsg==0 ){
................................................................................
  ExprList *pList = sqlite3ExprListAppend(0, X, 0);
  pList = sqlite3ExprListAppend(pList, Y, 0);
  A = sqlite3Expr(TK_BETWEEN, W, 0, 0);
  if( A ) A->pList = pList;
  if( N ) A = sqlite3Expr(TK_NOT, A, 0, 0);
  sqlite3ExprSpan(A,&W->span,&Y->span);
}

%type in_op {int}
in_op(A) ::= IN.      {A = 0;}
in_op(A) ::= NOT IN.  {A = 1;}
expr(A) ::= expr(X) in_op(N) LP exprlist(Y) RP(E). [IN] {
  A = sqlite3Expr(TK_IN, X, 0, 0);
  if( A ) A->pList = Y;
  if( N ) A = sqlite3Expr(TK_NOT, A, 0, 0);
  sqlite3ExprSpan(A,&X->span,&E);
}
%ifndef SQLITE_OMIT_SUBQUERY
  expr(A) ::= LP(B) select(X) RP(E). {
    A = sqlite3Expr(TK_SELECT, 0, 0, 0);
    if( A ) A->pSelect = X;
    sqlite3ExprSpan(A,&B,&E);
  }
  expr(A) ::= expr(X) in_op(N) LP select(Y) RP(E).  [IN] {
    A = sqlite3Expr(TK_IN, X, 0, 0);







|







 







>
|
|
|
|
|
|
|
|
|
<







10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
...
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717

718
719
720
721
722
723
724
**
*************************************************************************
** This file contains SQLite's grammar for SQL.  Process this file
** using the lemon parser generator to generate C code that runs
** the parser.  Lemon will also generate a header file containing
** numeric codes for all of the tokens.
**
** @(#) $Id: parse.y,v 1.162 2005/01/21 03:12:15 danielk1977 Exp $
*/
%token_prefix TK_
%token_type {Token}
%default_type {Token}
%extra_argument {Parse *pParse}
%syntax_error {
  if( pParse->zErrMsg==0 ){
................................................................................
  ExprList *pList = sqlite3ExprListAppend(0, X, 0);
  pList = sqlite3ExprListAppend(pList, Y, 0);
  A = sqlite3Expr(TK_BETWEEN, W, 0, 0);
  if( A ) A->pList = pList;
  if( N ) A = sqlite3Expr(TK_NOT, A, 0, 0);
  sqlite3ExprSpan(A,&W->span,&Y->span);
}
%ifndef SQLITE_OMIT_SUBQUERY
  %type in_op {int}
  in_op(A) ::= IN.      {A = 0;}
  in_op(A) ::= NOT IN.  {A = 1;}
  expr(A) ::= expr(X) in_op(N) LP exprlist(Y) RP(E). [IN] {
    A = sqlite3Expr(TK_IN, X, 0, 0);
    if( A ) A->pList = Y;
    if( N ) A = sqlite3Expr(TK_NOT, A, 0, 0);
    sqlite3ExprSpan(A,&X->span,&E);
  }

  expr(A) ::= LP(B) select(X) RP(E). {
    A = sqlite3Expr(TK_SELECT, 0, 0, 0);
    if( A ) A->pSelect = X;
    sqlite3ExprSpan(A,&B,&E);
  }
  expr(A) ::= expr(X) in_op(N) LP select(Y) RP(E).  [IN] {
    A = sqlite3Expr(TK_IN, X, 0, 0);

Changes to src/sqliteInt.h.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
**    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.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.359 2005/01/20 13:36:20 drh Exp $
*/
#ifndef _SQLITEINT_H_
#define _SQLITEINT_H_

/*
** Cursor support is turned off unless the SQLITE_ENABLE_CURSOR option
** is defined.
................................................................................
/*
** When building SQLite for embedded systems where memory is scarce,
** you can define one or more of the following macros to omit extra
** features of the library and thus keep the size of the library to
** a minimum.
*/
/* #define SQLITE_OMIT_AUTHORIZATION  1 */
/* #define SQLITE_OMIT_INMEMORYDB     1 */
/* #define SQLITE_OMIT_VACUUM         1 */
/* #define SQLITE_OMIT_DATETIME_FUNCS 1 */
/* #define SQLITE_OMIT_PROGRESS_CALLBACK 1 */
/* #define SQLITE_OMIT_AUTOVACUUM */
/* #define SQLITE_OMIT_ALTERTABLE */

/*







|







 







|







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
**    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.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.360 2005/01/21 03:12:15 danielk1977 Exp $
*/
#ifndef _SQLITEINT_H_
#define _SQLITEINT_H_

/*
** Cursor support is turned off unless the SQLITE_ENABLE_CURSOR option
** is defined.
................................................................................
/*
** When building SQLite for embedded systems where memory is scarce,
** you can define one or more of the following macros to omit extra
** features of the library and thus keep the size of the library to
** a minimum.
*/
/* #define SQLITE_OMIT_AUTHORIZATION  1 */
/* #define SQLITE_OMIT_MEMORYDB     1 */
/* #define SQLITE_OMIT_VACUUM         1 */
/* #define SQLITE_OMIT_DATETIME_FUNCS 1 */
/* #define SQLITE_OMIT_PROGRESS_CALLBACK 1 */
/* #define SQLITE_OMIT_AUTOVACUUM */
/* #define SQLITE_OMIT_ALTERTABLE */

/*

Changes to src/test1.c.

9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
....
2729
2730
2731
2732
2733
2734
2735






2736
2737
2738
2739
2740
2741
2742
**    May you share freely, never taking more than you give.
**
*************************************************************************
** Code for testing the printf() interface to SQLite.  This code
** is not included in the SQLite library.  It is used for automated
** testing of the SQLite library.
**
** $Id: test1.c,v 1.124 2005/01/20 01:14:23 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include "tcl.h"
#include "os.h"
#include <stdlib.h>
#include <string.h>

................................................................................
#endif

#ifdef SQLITE_OMIT_SCHEMA_VERSION_PRAGMAS
  Tcl_SetVar2(interp, "sqlite_options", "schema_version", "0", TCL_GLOBAL_ONLY);
#else
  Tcl_SetVar2(interp, "sqlite_options", "schema_version", "1", TCL_GLOBAL_ONLY);
#endif







#ifdef SQLITE_OMIT_TCL_VARIABLE
  Tcl_SetVar2(interp, "sqlite_options", "tclvar", "0", TCL_GLOBAL_ONLY);
#else
  Tcl_SetVar2(interp, "sqlite_options", "tclvar", "1", TCL_GLOBAL_ONLY);
#endif








|







 







>
>
>
>
>
>







9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
....
2729
2730
2731
2732
2733
2734
2735
2736
2737
2738
2739
2740
2741
2742
2743
2744
2745
2746
2747
2748
**    May you share freely, never taking more than you give.
**
*************************************************************************
** Code for testing the printf() interface to SQLite.  This code
** is not included in the SQLite library.  It is used for automated
** testing of the SQLite library.
**
** $Id: test1.c,v 1.125 2005/01/21 03:12:16 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include "tcl.h"
#include "os.h"
#include <stdlib.h>
#include <string.h>

................................................................................
#endif

#ifdef SQLITE_OMIT_SCHEMA_VERSION_PRAGMAS
  Tcl_SetVar2(interp, "sqlite_options", "schema_version", "0", TCL_GLOBAL_ONLY);
#else
  Tcl_SetVar2(interp, "sqlite_options", "schema_version", "1", TCL_GLOBAL_ONLY);
#endif

#ifdef SQLITE_OMIT_SUBQUERY
  Tcl_SetVar2(interp, "sqlite_options", "subquery", "0", TCL_GLOBAL_ONLY);
#else
  Tcl_SetVar2(interp, "sqlite_options", "subquery", "1", TCL_GLOBAL_ONLY);
#endif

#ifdef SQLITE_OMIT_TCL_VARIABLE
  Tcl_SetVar2(interp, "sqlite_options", "tclvar", "0", TCL_GLOBAL_ONLY);
#else
  Tcl_SetVar2(interp, "sqlite_options", "tclvar", "1", TCL_GLOBAL_ONLY);
#endif

Changes to test/autovacuum.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
..
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
#    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 SELECT statement.
#
# $Id: autovacuum.test,v 1.14 2005/01/15 12:45:51 danielk1977 Exp $

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

# If this build of the library does not support auto-vacuum, omit this
# whole file.
ifcapable {!autovacuum} {
................................................................................
    } {ok}
  }

  foreach delete $delete_order {
    # Delete one set of rows from the table.
    do_test autovacuum-1.$tn.($delete).1 {
      execsql "
        DELETE FROM av1 WHERE oid IN ([join $delete ,])
      "
    } {}

    # Do the integrity check.
    ifcapable {integrityck} {
      do_test autovacuum-1.$tn.($delete).2 {
        execsql {







|







 







|







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
..
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
#    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 SELECT statement.
#
# $Id: autovacuum.test,v 1.15 2005/01/21 03:12:16 danielk1977 Exp $

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

# If this build of the library does not support auto-vacuum, omit this
# whole file.
ifcapable {!autovacuum} {
................................................................................
    } {ok}
  }

  foreach delete $delete_order {
    # Delete one set of rows from the table.
    do_test autovacuum-1.$tn.($delete).1 {
      execsql "
        DELETE FROM av1 WHERE oid = [join $delete "OR oid = "]
      "
    } {}

    # Do the integrity check.
    ifcapable {integrityck} {
      do_test autovacuum-1.$tn.($delete).2 {
        execsql {

Changes to test/collate2.test.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
200
201
202
203
204
205
206


207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239

240
241
242
243
244
245
246
...
339
340
341
342
343
344
345


346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378

379
380
381
382
383
384
385
...
478
479
480
481
482
483
484


485
486
487
488
489
490
491
492
493
494
495
496
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
522
523
524
#    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 script is page cache subsystem.
#
# $Id: collate2.test,v 1.3 2004/09/19 02:15:26 drh Exp $

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

#
# Tests are organised as follows:
#
................................................................................
do_test collate2-1.21 {
  execsql {
    SELECT c FROM collate2t1 WHERE 
      CASE c WHEN 'aa' THEN 1 ELSE 0 END
        ORDER BY 1, oid;
  }
} {aa}


do_test collate2-1.22 {
  execsql {
    SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb') ORDER BY 1, oid;
  }
} {aa bb}
do_test collate2-1.23 {
  execsql {
    SELECT b FROM collate2t1 WHERE b IN ('aa', 'bb') ORDER BY 1, oid;
  }
} {aa aA Aa AA bb bB Bb BB}
do_test collate2-1.24 {
  execsql {
    SELECT c FROM collate2t1 WHERE c IN ('aa', 'bb') ORDER BY 1, oid;
  }
} {aa bb}
do_test collate2-1.25 {
  execsql {
    SELECT a FROM collate2t1 
      WHERE a IN (SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb'));
  }
} {aa bb}
do_test collate2-1.26 {
  execsql {
    SELECT b FROM collate2t1 
      WHERE b IN (SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb'));
  }
} {aa bb aA bB Aa Bb AA BB}
do_test collate2-1.27 {
  execsql {
    SELECT c FROM collate2t1 
      WHERE c IN (SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb'));
  }
} {aa bb}


do_test collate2-2.1 {
  execsql {
    SELECT a FROM collate2t1 WHERE NOT a > 'aa' ORDER BY 1;
  }
} {AA AB Aa Ab BA BB Ba Bb aA aB aa}
do_test collate2-2.2 {
................................................................................
  }
} {{} ab ba bb aB bA bB Ab Ba Bb AB BA BB}
do_test collate2-2.21 {
  execsql {
    SELECT c FROM collate2t1 WHERE NOT CASE c WHEN 'aa' THEN 1 ELSE 0 END;
  }
} {{} ab ba bb aA aB bA bB Aa Ab Ba Bb AA AB BA BB}


do_test collate2-2.22 {
  execsql {
    SELECT a FROM collate2t1 WHERE NOT a IN ('aa', 'bb');
  }
} {ab ba aA aB bA bB Aa Ab Ba Bb AA AB BA BB}
do_test collate2-2.23 {
  execsql {
    SELECT b FROM collate2t1 WHERE NOT b IN ('aa', 'bb');
  }
} {ab ba aB bA Ab Ba AB BA}
do_test collate2-2.24 {
  execsql {
    SELECT c FROM collate2t1 WHERE NOT c IN ('aa', 'bb');
  }
} {ab ba aA aB bA bB Aa Ab Ba Bb AA AB BA BB}
do_test collate2-2.25 {
  execsql {
    SELECT a FROM collate2t1 
      WHERE NOT a IN (SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb'));
  }
} {ab ba aA aB bA bB Aa Ab Ba Bb AA AB BA BB}
do_test collate2-2.26 {
  execsql {
    SELECT b FROM collate2t1 
      WHERE NOT b IN (SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb'));
  }
} {ab ba aB bA Ab Ba AB BA}
do_test collate2-2.27 {
  execsql {
    SELECT c FROM collate2t1 
      WHERE NOT c IN (SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb'));
  }
} {ab ba aA aB bA bB Aa Ab Ba Bb AA AB BA BB}


do_test collate2-3.1 {
  execsql {
    SELECT a > 'aa' FROM collate2t1;
  }
} {{} 0 1 1 1 0 0 1 1 0 0 0 0 0 0 0 0}
do_test collate2-3.2 {
................................................................................
  }
} {0 1 0 0 0 1 0 0 0 1 0 0 0 1 0 0 0}
do_test collate2-3.21 {
  execsql {
    SELECT CASE c WHEN 'aa' THEN 1 ELSE 0 END FROM collate2t1;
  }
} {0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0}


do_test collate2-3.22 {
  execsql {
    SELECT a IN ('aa', 'bb') FROM collate2t1;
  }
} {{} 1 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0}
do_test collate2-3.23 {
  execsql {
    SELECT b IN ('aa', 'bb') FROM collate2t1;
  }
} {{} 1 0 0 1 1 0 0 1 1 0 0 1 1 0 0 1}
do_test collate2-3.24 {
  execsql {
    SELECT c IN ('aa', 'bb') FROM collate2t1;
  }
} {{} 1 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0}
do_test collate2-3.25 {
  execsql {
    SELECT a IN (SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb')) 
      FROM collate2t1;
  }
} {{} 1 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0}
do_test collate2-3.26 {
  execsql {
    SELECT b IN (SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb')) 
      FROM collate2t1;
  }
} {{} 1 0 0 1 1 0 0 1 1 0 0 1 1 0 0 1}
do_test collate2-3.27 {
  execsql {
    SELECT c IN (SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb')) 
      FROM collate2t1;
  }
} {{} 1 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0}


do_test collate2-4.0 {
  execsql {
    CREATE TABLE collate2t2(b COLLATE binary);
    CREATE TABLE collate2t3(b text);
    INSERT INTO collate2t2 VALUES('aa');
    INSERT INTO collate2t3 VALUES('aa');







|







 







>
>
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
>







 







>
>
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
>







 







>
>
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
>







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
...
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
...
484
485
486
487
488
489
490
491
492
493
494
495
496
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
522
523
524
525
526
527
528
529
530
531
532
533
#    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 script is page cache subsystem.
#
# $Id: collate2.test,v 1.4 2005/01/21 03:12:16 danielk1977 Exp $

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

#
# Tests are organised as follows:
#
................................................................................
do_test collate2-1.21 {
  execsql {
    SELECT c FROM collate2t1 WHERE 
      CASE c WHEN 'aa' THEN 1 ELSE 0 END
        ORDER BY 1, oid;
  }
} {aa}

ifcapable subquery {
  do_test collate2-1.22 {
    execsql {
      SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb') ORDER BY 1, oid;
    }
  } {aa bb}
  do_test collate2-1.23 {
    execsql {
      SELECT b FROM collate2t1 WHERE b IN ('aa', 'bb') ORDER BY 1, oid;
    }
  } {aa aA Aa AA bb bB Bb BB}
  do_test collate2-1.24 {
    execsql {
      SELECT c FROM collate2t1 WHERE c IN ('aa', 'bb') ORDER BY 1, oid;
    }
  } {aa bb}
  do_test collate2-1.25 {
    execsql {
      SELECT a FROM collate2t1 
        WHERE a IN (SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb'));
    }
  } {aa bb}
  do_test collate2-1.26 {
    execsql {
      SELECT b FROM collate2t1 
        WHERE b IN (SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb'));
    }
  } {aa bb aA bB Aa Bb AA BB}
  do_test collate2-1.27 {
    execsql {
      SELECT c FROM collate2t1 
        WHERE c IN (SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb'));
    }
  } {aa bb}
} ;# ifcapable subquery

do_test collate2-2.1 {
  execsql {
    SELECT a FROM collate2t1 WHERE NOT a > 'aa' ORDER BY 1;
  }
} {AA AB Aa Ab BA BB Ba Bb aA aB aa}
do_test collate2-2.2 {
................................................................................
  }
} {{} ab ba bb aB bA bB Ab Ba Bb AB BA BB}
do_test collate2-2.21 {
  execsql {
    SELECT c FROM collate2t1 WHERE NOT CASE c WHEN 'aa' THEN 1 ELSE 0 END;
  }
} {{} ab ba bb aA aB bA bB Aa Ab Ba Bb AA AB BA BB}

ifcapable subquery {
  do_test collate2-2.22 {
    execsql {
      SELECT a FROM collate2t1 WHERE NOT a IN ('aa', 'bb');
    }
  } {ab ba aA aB bA bB Aa Ab Ba Bb AA AB BA BB}
  do_test collate2-2.23 {
    execsql {
      SELECT b FROM collate2t1 WHERE NOT b IN ('aa', 'bb');
    }
  } {ab ba aB bA Ab Ba AB BA}
  do_test collate2-2.24 {
    execsql {
      SELECT c FROM collate2t1 WHERE NOT c IN ('aa', 'bb');
    }
  } {ab ba aA aB bA bB Aa Ab Ba Bb AA AB BA BB}
  do_test collate2-2.25 {
    execsql {
      SELECT a FROM collate2t1 
        WHERE NOT a IN (SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb'));
    }
  } {ab ba aA aB bA bB Aa Ab Ba Bb AA AB BA BB}
  do_test collate2-2.26 {
    execsql {
      SELECT b FROM collate2t1 
        WHERE NOT b IN (SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb'));
    }
  } {ab ba aB bA Ab Ba AB BA}
  do_test collate2-2.27 {
    execsql {
      SELECT c FROM collate2t1 
        WHERE NOT c IN (SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb'));
    }
  } {ab ba aA aB bA bB Aa Ab Ba Bb AA AB BA BB}
}

do_test collate2-3.1 {
  execsql {
    SELECT a > 'aa' FROM collate2t1;
  }
} {{} 0 1 1 1 0 0 1 1 0 0 0 0 0 0 0 0}
do_test collate2-3.2 {
................................................................................
  }
} {0 1 0 0 0 1 0 0 0 1 0 0 0 1 0 0 0}
do_test collate2-3.21 {
  execsql {
    SELECT CASE c WHEN 'aa' THEN 1 ELSE 0 END FROM collate2t1;
  }
} {0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0}

ifcapable subquery {
  do_test collate2-3.22 {
    execsql {
      SELECT a IN ('aa', 'bb') FROM collate2t1;
    }
  } {{} 1 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0}
  do_test collate2-3.23 {
    execsql {
      SELECT b IN ('aa', 'bb') FROM collate2t1;
    }
  } {{} 1 0 0 1 1 0 0 1 1 0 0 1 1 0 0 1}
  do_test collate2-3.24 {
    execsql {
      SELECT c IN ('aa', 'bb') FROM collate2t1;
    }
  } {{} 1 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0}
  do_test collate2-3.25 {
    execsql {
      SELECT a IN (SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb')) 
        FROM collate2t1;
    }
  } {{} 1 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0}
  do_test collate2-3.26 {
    execsql {
      SELECT b IN (SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb')) 
        FROM collate2t1;
    }
  } {{} 1 0 0 1 1 0 0 1 1 0 0 1 1 0 0 1}
  do_test collate2-3.27 {
    execsql {
      SELECT c IN (SELECT a FROM collate2t1 WHERE a IN ('aa', 'bb')) 
        FROM collate2t1;
    }
  } {{} 1 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0}
}

do_test collate2-4.0 {
  execsql {
    CREATE TABLE collate2t2(b COLLATE binary);
    CREATE TABLE collate2t3(b text);
    INSERT INTO collate2t2 VALUES('aa');
    INSERT INTO collate2t3 VALUES('aa');

Changes to test/collate4.test.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
367
368
369
370
371
372
373

374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401

402
403
404
405
406
407
408
#    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 script is page cache subsystem.
#
# $Id: collate4.test,v 1.6 2004/12/19 00:11:36 drh Exp $

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

db collate TEXT text_collate
proc text_collate {a b} {
  return [string compare $a $b]
................................................................................
  }
} {A a A A 19}
do_test collate4-2.1.5 {
  count {
    SELECT * FROM collate4t2, collate4t1 WHERE b = a;
  }
} {A A 4}

do_test collate4-2.1.6 {
  count {
    SELECT a FROM collate4t1 WHERE a IN (SELECT * FROM collate4t2);
  }
} {a A 10}
do_test collate4-2.1.7 {
  execsql {
    DROP INDEX collate4i1;
    CREATE INDEX collate4i1 ON collate4t1(a);
  }
  count {
    SELECT a FROM collate4t1 WHERE a IN (SELECT * FROM collate4t2);
  }
} {a A 6}
do_test collate4-2.1.8 {
  count {
    SELECT a FROM collate4t1 WHERE a IN ('z', 'a');
  }
} {a A 5}
do_test collate4-2.1.9 {
  execsql {
    DROP INDEX collate4i1;
    CREATE INDEX collate4i1 ON collate4t1(a COLLATE TEXT);
  }
  count {
    SELECT a FROM collate4t1 WHERE a IN ('z', 'a');
  }
} {a A 9}

do_test collate4-2.1.10 {
  execsql {
    DROP TABLE collate4t1;
    DROP TABLE collate4t2;
  }
} {}








|







 







>
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
>







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
#    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 script is page cache subsystem.
#
# $Id: collate4.test,v 1.7 2005/01/21 03:12:16 danielk1977 Exp $

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

db collate TEXT text_collate
proc text_collate {a b} {
  return [string compare $a $b]
................................................................................
  }
} {A a A A 19}
do_test collate4-2.1.5 {
  count {
    SELECT * FROM collate4t2, collate4t1 WHERE b = a;
  }
} {A A 4}
ifcapable subquery {
  do_test collate4-2.1.6 {
    count {
      SELECT a FROM collate4t1 WHERE a IN (SELECT * FROM collate4t2);
    }
  } {a A 10}
  do_test collate4-2.1.7 {
    execsql {
      DROP INDEX collate4i1;
      CREATE INDEX collate4i1 ON collate4t1(a);
    }
    count {
      SELECT a FROM collate4t1 WHERE a IN (SELECT * FROM collate4t2);
    }
  } {a A 6}
  do_test collate4-2.1.8 {
    count {
      SELECT a FROM collate4t1 WHERE a IN ('z', 'a');
    }
  } {a A 5}
  do_test collate4-2.1.9 {
    execsql {
      DROP INDEX collate4i1;
      CREATE INDEX collate4i1 ON collate4t1(a COLLATE TEXT);
    }
    count {
      SELECT a FROM collate4t1 WHERE a IN ('z', 'a');
    }
  } {a A 9}
}
do_test collate4-2.1.10 {
  execsql {
    DROP TABLE collate4t1;
    DROP TABLE collate4t2;
  }
} {}

Changes to test/expr.test.

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


503

504

505

506

507
508

509
510
511
512
513
514
515
#    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 expressions.
#
# $Id: expr.test,v 1.41 2005/01/11 17:46:42 drh Exp $

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

# Create a table to work with.
#
execsql {CREATE TABLE test1(i1 int, i2 int, r1 real, r2 real, t1 text, t2 text)}
................................................................................
test_expr2 expr-7.43 {(b+1234)||'this is a string that is at least 32 characters long' BETWEEN 1 AND 2} {}
test_expr2 expr-7.44 {123||'xabcdefghijklmnopqrstuvwyxz01234567890'||a BETWEEN '123a' AND '123b'} {}
test_expr2 expr-7.45 {((123||'xabcdefghijklmnopqrstuvwyxz01234567890'||a) BETWEEN '123a' AND '123b')<0} {}
test_expr2 expr-7.46 {((123||'xabcdefghijklmnopqrstuvwyxz01234567890'||a) BETWEEN '123a' AND '123z')>0} {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20}

test_expr2 expr-7.50 {((a between 1 and 2 OR 0) AND 1) OR 0} {1 2}
test_expr2 expr-7.51 {((a not between 3 and 100 OR 0) AND 1) OR 0} {1 2}


test_expr2 expr-7.52 {((a in (1,2) OR 0) AND 1) OR 0} {1 2}

test_expr2 expr-7.53 {((a not in (3,4,5,6,7,8,9,10) OR 0) AND a<11) OR 0} {1 2}

test_expr2 expr-7.54 {((a>0 OR 0) AND a<3) OR 0} {1 2}

test_expr2 expr-7.55 {((a in (1,2) OR 0) IS NULL AND 1) OR 0} {{}}

test_expr2 expr-7.56 {((a not in (3,4,5,6,7,8,9,10) IS NULL OR 0) AND 1) OR 0} \
   {{}}

test_expr2 expr-7.57 {((a>0 IS NULL OR 0) AND 1) OR 0} {{}}

test_expr2 expr-7.58  {(a||'')<='1'}                  {1}

test_expr2 expr-7.59 {LIKE('10%',b)}                  {10 20}
test_expr2 expr-7.60 {LIKE('_4',b)}                   {6}
test_expr2 expr-7.61 {GLOB('1?',a)}            {10 11 12 13 14 15 16 17 18 19}







|







 







>
>
|
>
|
>

>
|
>
|
<
>







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
496
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 expressions.
#
# $Id: expr.test,v 1.42 2005/01/21 03:12:16 danielk1977 Exp $

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

# Create a table to work with.
#
execsql {CREATE TABLE test1(i1 int, i2 int, r1 real, r2 real, t1 text, t2 text)}
................................................................................
test_expr2 expr-7.43 {(b+1234)||'this is a string that is at least 32 characters long' BETWEEN 1 AND 2} {}
test_expr2 expr-7.44 {123||'xabcdefghijklmnopqrstuvwyxz01234567890'||a BETWEEN '123a' AND '123b'} {}
test_expr2 expr-7.45 {((123||'xabcdefghijklmnopqrstuvwyxz01234567890'||a) BETWEEN '123a' AND '123b')<0} {}
test_expr2 expr-7.46 {((123||'xabcdefghijklmnopqrstuvwyxz01234567890'||a) BETWEEN '123a' AND '123z')>0} {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20}

test_expr2 expr-7.50 {((a between 1 and 2 OR 0) AND 1) OR 0} {1 2}
test_expr2 expr-7.51 {((a not between 3 and 100 OR 0) AND 1) OR 0} {1 2}

ifcapable subquery {
  test_expr2 expr-7.52 {((a in (1,2) OR 0) AND 1) OR 0} {1 2}
  test_expr2 expr-7.53 \
      {((a not in (3,4,5,6,7,8,9,10) OR 0) AND a<11) OR 0} {1 2}
}
test_expr2 expr-7.54 {((a>0 OR 0) AND a<3) OR 0} {1 2}
ifcapable subquery {
  test_expr2 expr-7.55 {((a in (1,2) OR 0) IS NULL AND 1) OR 0} {{}}
  test_expr2 expr-7.56 \
      {((a not in (3,4,5,6,7,8,9,10) IS NULL OR 0) AND 1) OR 0} {{}}

}
test_expr2 expr-7.57 {((a>0 IS NULL OR 0) AND 1) OR 0} {{}}

test_expr2 expr-7.58  {(a||'')<='1'}                  {1}

test_expr2 expr-7.59 {LIKE('10%',b)}                  {10 20}
test_expr2 expr-7.60 {LIKE('_4',b)}                   {6}
test_expr2 expr-7.61 {GLOB('1?',a)}            {10 11 12 13 14 15 16 17 18 19}

Changes to test/in.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
..
49
50
51
52
53
54
55








56
57
58
59
60
61
62
#    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 IN and BETWEEN operator.
#
# $Id: in.test,v 1.12 2004/05/27 17:22:56 drh Exp $

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

# Generate the test data we will need for the first squences of tests.
#
do_test in-1.0 {
................................................................................
do_test in-1.6 {
  execsql {SELECT a FROM t1 WHERE b BETWEEN a AND a*5 OR b=512 ORDER BY a}
} {1 2 3 4 9}
do_test in-1.7 {
  execsql {SELECT a+ 100*(a BETWEEN 1 and 3) FROM t1 ORDER BY b}
} {101 102 103 4 5 6 7 8 9 10}










# Testing of the IN operator using static lists on the right-hand side.
#
do_test in-2.1 {
  execsql {SELECT a FROM t1 WHERE b IN (8,12,16,24,32) ORDER BY a}
} {3 4 5}
do_test in-2.2 {







|







 







>
>
>
>
>
>
>
>







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
..
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
#    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 IN and BETWEEN operator.
#
# $Id: in.test,v 1.13 2005/01/21 03:12:16 danielk1977 Exp $

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

# Generate the test data we will need for the first squences of tests.
#
do_test in-1.0 {
................................................................................
do_test in-1.6 {
  execsql {SELECT a FROM t1 WHERE b BETWEEN a AND a*5 OR b=512 ORDER BY a}
} {1 2 3 4 9}
do_test in-1.7 {
  execsql {SELECT a+ 100*(a BETWEEN 1 and 3) FROM t1 ORDER BY b}
} {101 102 103 4 5 6 7 8 9 10}

# The rest of this file concentrates on testing the IN operator.
# Skip this if the library is compiled with SQLITE_OMIT_SUBQUERY 
# (because the IN operator is unavailable).
#
ifcapable !subquery {
  finish_test
  return
}

# Testing of the IN operator using static lists on the right-hand side.
#
do_test in-2.1 {
  execsql {SELECT a FROM t1 WHERE b IN (8,12,16,24,32) ORDER BY a}
} {3 4 5}
do_test in-2.2 {

Changes to test/index.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
300
301
302
303
304
305
306





307
308
309
310
311
312
313
314
315
#    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 INDEX statement.
#
# $Id: index.test,v 1.36 2004/11/22 08:43:32 danielk1977 Exp $

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

# Create a basic index and verify it is added to sqlite_master
#
do_test index-1.1 {
................................................................................
    INSERT INTO t1 VALUES (1,8);
    INSERT INTO t1 VALUES (1,9);
    INSERT INTO t1 VALUES (2,0);
    SELECT b FROM t1 WHERE a=1 ORDER BY b;
  }
} {1 2 3 4 5 6 7 8 9}
do_test index-10.5 {





  execsql {
    DELETE FROM t1 WHERE b IN (2, 4, 6, 8);
    SELECT b FROM t1 WHERE a=1 ORDER BY b;
  }
} {1 3 5 7 9}
do_test index-10.6 {
  execsql {
    DELETE FROM t1 WHERE b>2;
    SELECT b FROM t1 WHERE a=1 ORDER BY b;







|







 







>
>
>
>
>

<







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

313
314
315
316
317
318
319
#    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 INDEX statement.
#
# $Id: index.test,v 1.37 2005/01/21 03:12:16 danielk1977 Exp $

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

# Create a basic index and verify it is added to sqlite_master
#
do_test index-1.1 {
................................................................................
    INSERT INTO t1 VALUES (1,8);
    INSERT INTO t1 VALUES (1,9);
    INSERT INTO t1 VALUES (2,0);
    SELECT b FROM t1 WHERE a=1 ORDER BY b;
  }
} {1 2 3 4 5 6 7 8 9}
do_test index-10.5 {
  ifcapable subquery {
    execsql { DELETE FROM t1 WHERE b IN (2, 4, 6, 8); }
  } else {
    execsql { DELETE FROM t1 WHERE b = 2 OR b = 4 OR b = 6 OR b = 8; }
  }
  execsql {

    SELECT b FROM t1 WHERE a=1 ORDER BY b;
  }
} {1 3 5 7 9}
do_test index-10.6 {
  execsql {
    DELETE FROM t1 WHERE b>2;
    SELECT b FROM t1 WHERE a=1 ORDER BY b;

Changes to test/insert.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
173
174
175
176
177
178
179
180
181





182
183
184

185
186
187
188
189
190

191







192
193
194
195
196
197
198
199
200
#    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 INSERT statement.
#
# $Id: insert.test,v 1.22 2005/01/17 08:57:09 danielk1977 Exp $

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

# Try to insert into a non-existant table.
#
do_test insert-1.1 {
................................................................................
  execsql {
    CREATE TABLE t3(a,b,c);
    INSERT INTO t3 VALUES(1+2+3,4,5);
    SELECT * FROM t3;
  }
} {6 4 5}
do_test insert-4.2 {
  execsql {
    INSERT INTO t3 VALUES((SELECT max(a) FROM t3)+1,5,6);





    SELECT * FROM t3 ORDER BY a;
  }
} {6 4 5 7 5 6}

do_test insert-4.3 {
  catchsql {
    INSERT INTO t3 VALUES((SELECT max(a) FROM t3)+1,t3.a,6);
    SELECT * FROM t3 ORDER BY a;
  }
} {1 {no such column: t3.a}}

do_test insert-4.4 {







  execsql {
    INSERT INTO t3 VALUES((SELECT b FROM t3 WHERE a=0),6,7);
    SELECT * FROM t3 ORDER BY a;
  }
} {{} 6 7 6 4 5 7 5 6}
do_test insert-4.5 {
  execsql {
    SELECT b,c FROM t3 WHERE a IS NULL;
  }







|







 







|
|
>
>
>
>
>



>
|
|
|
|
|
|
>

>
>
>
>
>
>
>

<







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206

207
208
209
210
211
212
213
#    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 INSERT statement.
#
# $Id: insert.test,v 1.23 2005/01/21 03:12:16 danielk1977 Exp $

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

# Try to insert into a non-existant table.
#
do_test insert-1.1 {
................................................................................
  execsql {
    CREATE TABLE t3(a,b,c);
    INSERT INTO t3 VALUES(1+2+3,4,5);
    SELECT * FROM t3;
  }
} {6 4 5}
do_test insert-4.2 {
  ifcapable subquery {
    execsql {INSERT INTO t3 VALUES((SELECT max(a) FROM t3)+1,5,6);}
  } else {
    set maxa [execsql {SELECT max(a) FROM t3}]
    execsql "INSERT INTO t3 VALUES($maxa+1,5,6);"
  }
  execsql {
    SELECT * FROM t3 ORDER BY a;
  }
} {6 4 5 7 5 6}
ifcapable subquery {
  do_test insert-4.3 {
    catchsql {
      INSERT INTO t3 VALUES((SELECT max(a) FROM t3)+1,t3.a,6);
      SELECT * FROM t3 ORDER BY a;
    }
  } {1 {no such column: t3.a}}
}
do_test insert-4.4 {
  ifcapable subquery {
    execsql {INSERT INTO t3 VALUES((SELECT b FROM t3 WHERE a=0),6,7);}
  } else {
    set b [execsql {SELECT b FROM t3 WHERE a = 0}]
    if {$b==""} {set b NULL}
    execsql "INSERT INTO t3 VALUES($b,6,7);"
  }
  execsql {

    SELECT * FROM t3 ORDER BY a;
  }
} {{} 6 7 6 4 5 7 5 6}
do_test insert-4.5 {
  execsql {
    SELECT b,c FROM t3 WHERE a IS NULL;
  }

Changes to test/insert2.test.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
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
182
183
184
185
#    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 INSERT statement that takes is
# result from a SELECT.
#
# $Id: insert2.test,v 1.13 2004/11/22 13:35:42 danielk1977 Exp $

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

# Create some tables with data that we can select against
#
do_test insert2-1.0 {
................................................................................
} {9}
do_test insert2-3.2.1 {
  execsql {
    SELECT count(*) FROM t4;
  }
} {10}
do_test insert2-3.3 {

  execsql {
    BEGIN;
    INSERT INTO t4 SELECT x+(SELECT max(x) FROM t4),y FROM t4;
    INSERT INTO t4 SELECT x+(SELECT max(x) FROM t4),y FROM t4;
    INSERT INTO t4 SELECT x+(SELECT max(x) FROM t4),y FROM t4;
    INSERT INTO t4 SELECT x+(SELECT max(x) FROM t4),y FROM t4;
    COMMIT;
    SELECT count(*) FROM t4;












  }
} {160}
do_test insert2-3.4 {
  execsql {
    BEGIN;
    UPDATE t4 SET y='lots of data for the row where x=' || x
                     || ' and y=' || y || ' - even more data to fill space';
    COMMIT;
    SELECT count(*) FROM t4;
  }
} {160}
do_test insert2-3.5 {

  execsql {
    BEGIN;
    INSERT INTO t4 SELECT x+(SELECT max(x)+1 FROM t4),y FROM t4;
    SELECT count(*) from t4;
    ROLLBACK;








  }
} {320}
do_test insert2-3.6 {
  execsql {
    SELECT count(*) FROM t4;
  }
} {160}







|







 







>
|
|
|
|
|
|
|
|
>
>
>
>
>
>
>
>
>
>
>
>












>
|
|
|
|
|
>
>
>
>
>
>
>
>







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
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
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
#    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 INSERT statement that takes is
# result from a SELECT.
#
# $Id: insert2.test,v 1.14 2005/01/21 03:12:16 danielk1977 Exp $

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

# Create some tables with data that we can select against
#
do_test insert2-1.0 {
................................................................................
} {9}
do_test insert2-3.2.1 {
  execsql {
    SELECT count(*) FROM t4;
  }
} {10}
do_test insert2-3.3 {
  ifcapable subquery {
    execsql {
      BEGIN;
      INSERT INTO t4 SELECT x+(SELECT max(x) FROM t4),y FROM t4;
      INSERT INTO t4 SELECT x+(SELECT max(x) FROM t4),y FROM t4;
      INSERT INTO t4 SELECT x+(SELECT max(x) FROM t4),y FROM t4;
      INSERT INTO t4 SELECT x+(SELECT max(x) FROM t4),y FROM t4;
      COMMIT;
      SELECT count(*) FROM t4;
    }
  } else {
    db function max_x_t4 {execsql {SELECT max(x) FROM t4}}
    execsql {
      BEGIN;
      INSERT INTO t4 SELECT x+max_x_t4() ,y FROM t4;
      INSERT INTO t4 SELECT x+max_x_t4() ,y FROM t4;
      INSERT INTO t4 SELECT x+max_x_t4() ,y FROM t4;
      INSERT INTO t4 SELECT x+max_x_t4() ,y FROM t4;
      COMMIT;
      SELECT count(*) FROM t4;
    }
  }
} {160}
do_test insert2-3.4 {
  execsql {
    BEGIN;
    UPDATE t4 SET y='lots of data for the row where x=' || x
                     || ' and y=' || y || ' - even more data to fill space';
    COMMIT;
    SELECT count(*) FROM t4;
  }
} {160}
do_test insert2-3.5 {
  ifcapable subquery {
    execsql {
      BEGIN;
      INSERT INTO t4 SELECT x+(SELECT max(x)+1 FROM t4),y FROM t4;
      SELECT count(*) from t4;
      ROLLBACK;
    }
  } else {
    execsql {
      BEGIN;
      INSERT INTO t4 SELECT x+max_x_t4()+1,y FROM t4;
      SELECT count(*) from t4;
      ROLLBACK;
    }
  }
} {320}
do_test insert2-3.6 {
  execsql {
    SELECT count(*) FROM t4;
  }
} {160}

Changes to test/join.test.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
113
114
115
116
117
118
119


120
121
122
123
124
125
126
127
128
129
130
131

132
133
134
135
136
137
138
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for joins, including outer joins.
#
# $Id: join.test,v 1.15 2005/01/18 17:40:04 drh Exp $

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

do_test join-1.1 {
  execsql {
    CREATE TABLE t1(a,b,c);
................................................................................
  }
} {1 2 3 4 2 3 4 5}
do_test join-1.12 {
  execsql {
    SELECT * FROM t1 natural inner join t2;
  }
} {1 2 3 4 2 3 4 5}


do_test join-1.13 {
  execsql2 {
    SELECT * FROM t1 NATURAL JOIN 
      (SELECT b as 'c', c as 'd', d as 'e' FROM t2) as t3
  }
} {a 1 b 2 c 3 d 4 e 5}
do_test join-1.14 {
  execsql2 {
    SELECT * FROM (SELECT b as 'c', c as 'd', d as 'e' FROM t2) as 'tx'
        NATURAL JOIN t1
  }
} {c 3 d 4 e 5 a 1 b 2}


do_test join-1.15 {
  execsql {
    CREATE TABLE t3(c,d,e);
    INSERT INTO t3 VALUES(2,3,4);
    INSERT INTO t3 VALUES(3,4,5);
    INSERT INTO t3 VALUES(4,5,6);







|







 







>
>
|
|
|
|
|
|
|
|
|
|
|
|
>







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for joins, including outer joins.
#
# $Id: join.test,v 1.16 2005/01/21 03:12:16 danielk1977 Exp $

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

do_test join-1.1 {
  execsql {
    CREATE TABLE t1(a,b,c);
................................................................................
  }
} {1 2 3 4 2 3 4 5}
do_test join-1.12 {
  execsql {
    SELECT * FROM t1 natural inner join t2;
  }
} {1 2 3 4 2 3 4 5}

ifcapable subquery {
  do_test join-1.13 {
    execsql2 {
      SELECT * FROM t1 NATURAL JOIN 
        (SELECT b as 'c', c as 'd', d as 'e' FROM t2) as t3
    }
  } {a 1 b 2 c 3 d 4 e 5}
  do_test join-1.14 {
    execsql2 {
      SELECT * FROM (SELECT b as 'c', c as 'd', d as 'e' FROM t2) as 'tx'
          NATURAL JOIN t1
    }
  } {c 3 d 4 e 5 a 1 b 2}
}

do_test join-1.15 {
  execsql {
    CREATE TABLE t3(c,d,e);
    INSERT INTO t3 VALUES(2,3,4);
    INSERT INTO t3 VALUES(3,4,5);
    INSERT INTO t3 VALUES(4,5,6);

Changes to test/join2.test.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
..
59
60
61
62
63
64
65

66
67
68
69
70
71
72

73
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for joins, including outer joins.
#
# $Id: join2.test,v 1.1 2004/01/24 20:18:13 drh Exp $

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

do_test join2-1.1 {
  execsql {
    CREATE TABLE t1(a,b);
................................................................................
} {1 11 111 1111 3 33 333 {}}
do_test join2-1.6 {
  execsql {
    SELECT * FROM
      t1 NATURAL LEFT OUTER JOIN t2 NATURAL JOIN t3
  }
} {1 11 111 1111}

do_test join2-1.6 {
  execsql {
    SELECT * FROM
      t1 NATURAL LEFT OUTER JOIN (t2 NATURAL JOIN t3)
  }
} {1 11 111 1111 2 22 {} {} 3 33 {} {}}


finish_test







|







 







>
|
|
|
|
|
|
|
>

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
..
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for joins, including outer joins.
#
# $Id: join2.test,v 1.2 2005/01/21 03:12:16 danielk1977 Exp $

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

do_test join2-1.1 {
  execsql {
    CREATE TABLE t1(a,b);
................................................................................
} {1 11 111 1111 3 33 333 {}}
do_test join2-1.6 {
  execsql {
    SELECT * FROM
      t1 NATURAL LEFT OUTER JOIN t2 NATURAL JOIN t3
  }
} {1 11 111 1111}
ifcapable subquery {
  do_test join2-1.7 {
    execsql {
      SELECT * FROM
        t1 NATURAL LEFT OUTER JOIN (t2 NATURAL JOIN t3)
    }
  } {1 11 111 1111 2 22 {} {} 3 33 {} {}}
}

finish_test

Changes to test/join4.test.

9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
..
58
59
60
61
62
63
64

65
66
67
68
69
70
71
72
73
74

75
76
77
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for left outer joins containing WHERE
# clauses that restrict the scope of the left term of the join.
#
# $Id: join4.test,v 1.2 2004/07/19 19:28:44 drh Exp $

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

do_test join4-1.1 {
  execsql {
    create temp table t1(a integer, b varchar(10));
................................................................................
  }
} {2 two 2 niban ok}
do_test join4-1.4 {
  execsql {
    select * from t1 left outer join t2 on t1.a=t2.x and t2.z>='ok'
  }
} {1 one {} {} {} 2 two 2 niban ok 3 three {} {} {} 4 four {} {} {}}

do_test join4-1.6 {
  execsql {
    select * from t1 left outer join t2 on t1.a=t2.x where t2.z IN ('ok')
  }
} {2 two 2 niban ok}
do_test join4-1.7 {
  execsql {
    select * from t1 left outer join t2 on t1.a=t2.x and t2.z IN ('ok')
  }
} {1 one {} {} {} 2 two 2 niban ok 3 three {} {} {} 4 four {} {} {}}



finish_test







|







 







>
|
|
|
|
|
|
|
|
|
|
>



9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
..
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for left outer joins containing WHERE
# clauses that restrict the scope of the left term of the join.
#
# $Id: join4.test,v 1.3 2005/01/21 03:12:16 danielk1977 Exp $

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

do_test join4-1.1 {
  execsql {
    create temp table t1(a integer, b varchar(10));
................................................................................
  }
} {2 two 2 niban ok}
do_test join4-1.4 {
  execsql {
    select * from t1 left outer join t2 on t1.a=t2.x and t2.z>='ok'
  }
} {1 one {} {} {} 2 two 2 niban ok 3 three {} {} {} 4 four {} {} {}}
ifcapable subquery {
  do_test join4-1.6 {
    execsql {
      select * from t1 left outer join t2 on t1.a=t2.x where t2.z IN ('ok')
    }
  } {2 two 2 niban ok}
  do_test join4-1.7 {
    execsql {
      select * from t1 left outer join t2 on t1.a=t2.x and t2.z IN ('ok')
    }
  } {1 one {} {} {} 2 two 2 niban ok 3 three {} {} {} 4 four {} {} {}}
}


finish_test

Changes to test/limit.test.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
..
75
76
77
78
79
80
81

82
83
84
85
86
87


88
89
90
91
92
93
94

95

96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111















112
113
114
115
116
117
118
...
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
351

352
353
354
355
356
357
358
359
360
361
#    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 LIMIT ... OFFSET ... clause
#  of SELECT statements.
#
# $Id: limit.test,v 1.21 2005/01/20 02:17:02 danielk1977 Exp $

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

# Build some test data
#
execsql {
................................................................................
} ;# ifcapable view
do_test limit-2.2 {
  execsql {
    CREATE TABLE t2 AS SELECT * FROM t1 LIMIT 2;
    SELECT count(*) FROM t2;
  }
} 2

do_test limit-2.3 {
  execsql {
    SELECT count(*) FROM t1 WHERE rowid IN (SELECT rowid FROM t1 LIMIT 2);
  }
} 2



do_test limit-3.1 {
  execsql {
    SELECT z FROM (SELECT y*10+x AS z FROM t1 ORDER BY x LIMIT 10)
    ORDER BY z LIMIT 5;
  }
} {50 51 52 53 54}


do_test limit-4.1 {

  execsql {
    BEGIN;
    CREATE TABLE t3(x);
    INSERT INTO t3 SELECT x FROM t1 ORDER BY x LIMIT 10 OFFSET 1;
    INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
    INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
    INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
    INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
    INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
    INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
    INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
    INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
    INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
    INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
    END;
    SELECT count(*) FROM t3;















  }
} {10240}
do_test limit-4.2 {
  execsql {
    SELECT x FROM t3 LIMIT 2 OFFSET 10000
  }
} {10001 10002}
................................................................................
    SELECT DISTINCT round(x/100) FROM t3 LIMIT 5 OFFSET 25;
  }
} {25 26 27 28 29}

# Make sure limits on multiple subqueries work correctly.
# Ticket #1035
#

do_test limit-9.1 {
  execsql {
    SELECT * FROM (SELECT * FROM t6 LIMIT 3);
  }
} {1 2 3}

do_test limit-9.2 {
  execsql {
    CREATE TABLE t7 AS SELECT * FROM t6;





    SELECT * FROM (SELECT * FROM t7 LIMIT 3);
  }
} {1 2 3}

ifcapable compound {

  do_test limit-9.3 {
    execsql {
      SELECT * FROM (SELECT * FROM t6 LIMIT 3)
      UNION
      SELECT * FROM (SELECT * FROM t7 LIMIT 3)
      ORDER BY 1
    }
  } {1 2 3}
  do_test limit-9.4 {
    execsql {
      SELECT * FROM (SELECT * FROM t6 LIMIT 3)
      UNION
      SELECT * FROM (SELECT * FROM t7 LIMIT 3)
      ORDER BY 1
      LIMIT 2
    }
  } {1 2}

  do_test limit-9.5 {
    catchsql {
      SELECT * FROM t6 LIMIT 3
      UNION
      SELECT * FROM t7 LIMIT 3
    }
  } {1 {LIMIT clause should come after UNION not before}}
}

finish_test







|







 







>
|
|
|
|
|
|
>
>
|
|
|
|
|
|
|
>

>
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







>
|
|
|
|
|
>
|


>
>
>
>
>
|
|
|
>

>
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
>










8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
..
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
...
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
#    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 LIMIT ... OFFSET ... clause
#  of SELECT statements.
#
# $Id: limit.test,v 1.22 2005/01/21 03:12:16 danielk1977 Exp $

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

# Build some test data
#
execsql {
................................................................................
} ;# ifcapable view
do_test limit-2.2 {
  execsql {
    CREATE TABLE t2 AS SELECT * FROM t1 LIMIT 2;
    SELECT count(*) FROM t2;
  }
} 2
ifcapable subquery {
  do_test limit-2.3 {
    execsql {
      SELECT count(*) FROM t1 WHERE rowid IN (SELECT rowid FROM t1 LIMIT 2);
    }
  } 2
}

ifcapable subquery {
  do_test limit-3.1 {
    execsql {
      SELECT z FROM (SELECT y*10+x AS z FROM t1 ORDER BY x LIMIT 10)
      ORDER BY z LIMIT 5;
    }
  } {50 51 52 53 54}
}

do_test limit-4.1 {
  ifcapable subquery { 
    execsql {
      BEGIN;
      CREATE TABLE t3(x);
      INSERT INTO t3 SELECT x FROM t1 ORDER BY x LIMIT 10 OFFSET 1;
      INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
      INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
      INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
      INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
      INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
      INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
      INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
      INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
      INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
      INSERT INTO t3 SELECT x+(SELECT max(x) FROM t3) FROM t3;
      END;
      SELECT count(*) FROM t3;
    }
  } else {
    execsql {
      BEGIN;
      CREATE TABLE t3(x);
      INSERT INTO t3 SELECT x FROM t1 ORDER BY x LIMIT 10 OFFSET 1;
    }
    for {set i 0} {$i<10} {incr i} {
      set max_x_t3 [execsql {SELECT max(x) FROM t3}]
      execsql "INSERT INTO t3 SELECT x+$max_x_t3 FROM t3;"
    }
    execsql {
      END;
      SELECT count(*) FROM t3;
    }
  }
} {10240}
do_test limit-4.2 {
  execsql {
    SELECT x FROM t3 LIMIT 2 OFFSET 10000
  }
} {10001 10002}
................................................................................
    SELECT DISTINCT round(x/100) FROM t3 LIMIT 5 OFFSET 25;
  }
} {25 26 27 28 29}

# Make sure limits on multiple subqueries work correctly.
# Ticket #1035
#
ifcapable subquery {
  do_test limit-9.1 {
    execsql {
      SELECT * FROM (SELECT * FROM t6 LIMIT 3);
    }
  } {1 2 3}
}
do_test limit-9.2.1 {
  execsql {
    CREATE TABLE t7 AS SELECT * FROM t6;
  }
} {}
ifcapable subquery {
  do_test limit-9.2.2 {
    execsql {
      SELECT * FROM (SELECT * FROM t7 LIMIT 3);
    }
  } {1 2 3}
}
ifcapable compound {
  ifcapable subquery {
    do_test limit-9.3 {
      execsql {
        SELECT * FROM (SELECT * FROM t6 LIMIT 3)
        UNION
        SELECT * FROM (SELECT * FROM t7 LIMIT 3)
        ORDER BY 1
      }
    } {1 2 3}
    do_test limit-9.4 {
      execsql {
        SELECT * FROM (SELECT * FROM t6 LIMIT 3)
        UNION
        SELECT * FROM (SELECT * FROM t7 LIMIT 3)
        ORDER BY 1
        LIMIT 2
      }
    } {1 2}
  }
  do_test limit-9.5 {
    catchsql {
      SELECT * FROM t6 LIMIT 3
      UNION
      SELECT * FROM t7 LIMIT 3
    }
  } {1 {LIMIT clause should come after UNION not before}}
}

finish_test

Changes to test/minmax.test.

9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
...
100
101
102
103
104
105
106

107




108
109
110
111
112
113
114

115




116
117

118


119
120
121
122
123
124
125
...
218
219
220
221
222
223
224

225
226
227
228
229

230
231
232
233
234

235
236
237
238
239

240
241
242
243
244
245
246
...
315
316
317
318
319
320
321

322
323
324
325
326

327
328
329
330
331
332
333
...
345
346
347
348
349
350
351

352
353
354
355
356

357
358
359
360
361
362
363
364
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing SELECT statements that contain
# aggregate min() and max() functions and which are handled as
# as a special case.
#
# $Id: minmax.test,v 1.14 2004/11/22 13:35:42 danielk1977 Exp $

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

do_test minmax-1.0 {
  execsql {
    BEGIN;
................................................................................
  execsql {SELECT max(a) FROM t2}
} {20}
do_test minmax-2.3 {
  set sqlite_search_count
} {0}

do_test minmax-3.0 {

  execsql {INSERT INTO t2 VALUES((SELECT max(a) FROM t2)+1,999)}




  set sqlite_search_count 0
  execsql {SELECT max(a) FROM t2}
} {21}
do_test minmax-3.1 {
  set sqlite_search_count
} {0}
do_test minmax-3.2 {

  execsql {INSERT INTO t2 VALUES((SELECT max(a) FROM t2)+1,999)}




  set sqlite_search_count 0
  execsql {

    SELECT b FROM t2 WHERE a=(SELECT max(a) FROM t2)


  }
} {999}
do_test minmax-3.3 {
  set sqlite_search_count
} {0}

ifcapable compound {
................................................................................
# queries.  Ticket #587.
#
do_test minmax-7.1 {
  execsql {
    SELECT max(x) FROM t1;
  }
} 20

do_test minmax-7.2 {
  execsql {
    SELECT * FROM (SELECT max(x) FROM t1);
  }
} 20

do_test minmax-7.3 {
  execsql {
    SELECT min(x) FROM t1;
  }
} 1

do_test minmax-7.4 {
  execsql {
    SELECT * FROM (SELECT min(x) FROM t1);
  }
} 1


# Make sure min(x) and max(x) work correctly when the datatype is
# TEXT instead of NUMERIC.  Ticket #623.
#
do_test minmax-8.1 {
  execsql {
    CREATE TABLE t4(a TEXT);
................................................................................
  }
} 1
do_test minmax-10.6 {
  execsql {
    SELECT count(x) FROM t6;
  }
} 0

do_test minmax-10.7 {
  execsql {
    SELECT (SELECT min(x) FROM t6), (SELECT max(x) FROM t6);
  }
} {{} {}}

do_test minmax-10.8 {
  execsql {
    SELECT min(x), max(x) FROM t6;
  }
} {{} {}}
do_test minmax-10.9 {
  execsql {
................................................................................
  }
} 1024
do_test minmax-10.10 {
  execsql {
    SELECT count(x) FROM t6;
  }
} 0

do_test minmax-10.11 {
  execsql {
    SELECT (SELECT min(x) FROM t6), (SELECT max(x) FROM t6);
  }
} {{} {}}

do_test minmax-10.12 {
  execsql {
    SELECT min(x), max(x) FROM t6;
  }
} {{} {}}


finish_test







|







 







>
|
>
>
>
>







>
|
>
>
>
>

<
>
|
>
>







 







>
|
|
|
|
|
>





>
|
|
|
|
|
>







 







>
|
|
|
|
|
>







 







>
|
|
|
|
|
>








9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
...
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126

127
128
129
130
131
132
133
134
135
136
137
...
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
...
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
...
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing SELECT statements that contain
# aggregate min() and max() functions and which are handled as
# as a special case.
#
# $Id: minmax.test,v 1.15 2005/01/21 03:12:16 danielk1977 Exp $

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

do_test minmax-1.0 {
  execsql {
    BEGIN;
................................................................................
  execsql {SELECT max(a) FROM t2}
} {20}
do_test minmax-2.3 {
  set sqlite_search_count
} {0}

do_test minmax-3.0 {
  ifcapable subquery {
    execsql {INSERT INTO t2 VALUES((SELECT max(a) FROM t2)+1,999)}
  } else {
    db function max_a_t2 {execsql {SELECT max(a) FROM t2}}
    execsql {INSERT INTO t2 VALUES(max_a_t2()+1,999)}
  }
  set sqlite_search_count 0
  execsql {SELECT max(a) FROM t2}
} {21}
do_test minmax-3.1 {
  set sqlite_search_count
} {0}
do_test minmax-3.2 {
  ifcapable subquery {
    execsql {INSERT INTO t2 VALUES((SELECT max(a) FROM t2)+1,999)}
  } else {
    db function max_a_t2 {execsql {SELECT max(a) FROM t2}}
    execsql {INSERT INTO t2 VALUES(max_a_t2()+1,999)}
  }
  set sqlite_search_count 0

  ifcapable subquery {
    execsql { SELECT b FROM t2 WHERE a=(SELECT max(a) FROM t2) }
  } else {
    execsql { SELECT b FROM t2 WHERE a=max_a_t2() }
  }
} {999}
do_test minmax-3.3 {
  set sqlite_search_count
} {0}

ifcapable compound {
................................................................................
# queries.  Ticket #587.
#
do_test minmax-7.1 {
  execsql {
    SELECT max(x) FROM t1;
  }
} 20
ifcapable subquery {
  do_test minmax-7.2 {
    execsql {
      SELECT * FROM (SELECT max(x) FROM t1);
    }
  } 20
}
do_test minmax-7.3 {
  execsql {
    SELECT min(x) FROM t1;
  }
} 1
ifcapable subquery {
  do_test minmax-7.4 {
    execsql {
      SELECT * FROM (SELECT min(x) FROM t1);
    }
  } 1
}

# Make sure min(x) and max(x) work correctly when the datatype is
# TEXT instead of NUMERIC.  Ticket #623.
#
do_test minmax-8.1 {
  execsql {
    CREATE TABLE t4(a TEXT);
................................................................................
  }
} 1
do_test minmax-10.6 {
  execsql {
    SELECT count(x) FROM t6;
  }
} 0
ifcapable subquery {
  do_test minmax-10.7 {
    execsql {
      SELECT (SELECT min(x) FROM t6), (SELECT max(x) FROM t6);
    }
  } {{} {}}
}
do_test minmax-10.8 {
  execsql {
    SELECT min(x), max(x) FROM t6;
  }
} {{} {}}
do_test minmax-10.9 {
  execsql {
................................................................................
  }
} 1024
do_test minmax-10.10 {
  execsql {
    SELECT count(x) FROM t6;
  }
} 0
ifcapable subquery {
  do_test minmax-10.11 {
    execsql {
      SELECT (SELECT min(x) FROM t6), (SELECT max(x) FROM t6);
    }
  } {{} {}}
}
do_test minmax-10.12 {
  execsql {
    SELECT min(x), max(x) FROM t6;
  }
} {{} {}}


finish_test

Changes to test/misc1.test.

9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
...
427
428
429
430
431
432
433

434
435
436
437
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
464
465

466
467
468
469
470
471
472
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for miscellanous features that were
# left out of other test files.
#
# $Id: misc1.test,v 1.31 2004/11/04 04:42:28 drh Exp $

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

# Mimic the SQLite 2 collation type NUMERIC.
db collate numeric numeric_collate
proc numeric_collate {lhs rhs} {
................................................................................
} {1 4 4}

# There was a problem with realloc() in the OP_MemStore operation of
# the VDBE.  A buffer was being reallocated but some pointers into 
# the old copy of the buffer were not being moved over to the new copy.
# The following code tests for the problem.
#

do_test misc1-13.1 {
   execsql {
     CREATE TABLE t9(x,y);
     INSERT INTO t9 VALUES('one',1);
     INSERT INTO t9 VALUES('two',2);
     INSERT INTO t9 VALUES('three',3);
     INSERT INTO t9 VALUES('four',4);
     INSERT INTO t9 VALUES('five',5);
     INSERT INTO t9 VALUES('six',6);
     INSERT INTO t9 VALUES('seven',7);
     INSERT INTO t9 VALUES('eight',8);
     INSERT INTO t9 VALUES('nine',9);
     INSERT INTO t9 VALUES('ten',10);
     INSERT INTO t9 VALUES('eleven',11);
     SELECT y FROM t9
     WHERE x=(SELECT x FROM t9 WHERE y=1)
        OR x=(SELECT x FROM t9 WHERE y=2)
        OR x=(SELECT x FROM t9 WHERE y=3)
        OR x=(SELECT x FROM t9 WHERE y=4)
        OR x=(SELECT x FROM t9 WHERE y=5)
        OR x=(SELECT x FROM t9 WHERE y=6)
        OR x=(SELECT x FROM t9 WHERE y=7)
        OR x=(SELECT x FROM t9 WHERE y=8)
        OR x=(SELECT x FROM t9 WHERE y=9)
        OR x=(SELECT x FROM t9 WHERE y=10)
        OR x=(SELECT x FROM t9 WHERE y=11)
        OR x=(SELECT x FROM t9 WHERE y=12)
        OR x=(SELECT x FROM t9 WHERE y=13)
        OR x=(SELECT x FROM t9 WHERE y=14)
     ;
   }
} {1 2 3 4 5 6 7 8 9 10 11}


# Make sure a database connection still works after changing the
# working directory.
#
do_test misc1-14.1 {
  file mkdir tempdir
  cd tempdir







|







 







>
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
>







9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
...
427
428
429
430
431
432
433
434
435
436
437
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
464
465
466
467
468
469
470
471
472
473
474
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for miscellanous features that were
# left out of other test files.
#
# $Id: misc1.test,v 1.32 2005/01/21 03:12:16 danielk1977 Exp $

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

# Mimic the SQLite 2 collation type NUMERIC.
db collate numeric numeric_collate
proc numeric_collate {lhs rhs} {
................................................................................
} {1 4 4}

# There was a problem with realloc() in the OP_MemStore operation of
# the VDBE.  A buffer was being reallocated but some pointers into 
# the old copy of the buffer were not being moved over to the new copy.
# The following code tests for the problem.
#
ifcapable subquery {
  do_test misc1-13.1 {
     execsql {
       CREATE TABLE t9(x,y);
       INSERT INTO t9 VALUES('one',1);
       INSERT INTO t9 VALUES('two',2);
       INSERT INTO t9 VALUES('three',3);
       INSERT INTO t9 VALUES('four',4);
       INSERT INTO t9 VALUES('five',5);
       INSERT INTO t9 VALUES('six',6);
       INSERT INTO t9 VALUES('seven',7);
       INSERT INTO t9 VALUES('eight',8);
       INSERT INTO t9 VALUES('nine',9);
       INSERT INTO t9 VALUES('ten',10);
       INSERT INTO t9 VALUES('eleven',11);
       SELECT y FROM t9
       WHERE x=(SELECT x FROM t9 WHERE y=1)
          OR x=(SELECT x FROM t9 WHERE y=2)
          OR x=(SELECT x FROM t9 WHERE y=3)
          OR x=(SELECT x FROM t9 WHERE y=4)
          OR x=(SELECT x FROM t9 WHERE y=5)
          OR x=(SELECT x FROM t9 WHERE y=6)
          OR x=(SELECT x FROM t9 WHERE y=7)
          OR x=(SELECT x FROM t9 WHERE y=8)
          OR x=(SELECT x FROM t9 WHERE y=9)
          OR x=(SELECT x FROM t9 WHERE y=10)
          OR x=(SELECT x FROM t9 WHERE y=11)
          OR x=(SELECT x FROM t9 WHERE y=12)
          OR x=(SELECT x FROM t9 WHERE y=13)
          OR x=(SELECT x FROM t9 WHERE y=14)
       ;
     }
  } {1 2 3 4 5 6 7 8 9 10 11}
}

# Make sure a database connection still works after changing the
# working directory.
#
do_test misc1-14.1 {
  file mkdir tempdir
  cd tempdir

Changes to test/misc3.test.

9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
...
195
196
197
198
199
200
201

202
203
204
205
206
207
208
209
210
211
212

213
214
215

216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240

241
242
243
244
245
246
247
...
293
294
295
296
297
298
299

300
301
302
303
304
305
306
307
308
309
310

311
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for miscellanous features that were
# left out of other test files.
#
# $Id: misc3.test,v 1.15 2004/11/04 14:47:13 drh Exp $

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

ifcapable {integrityck} {
  # Ticket #529.  Make sure an ABORT does not damage the in-memory cache
  # that will be used by subsequent statements in the same transaction.
................................................................................
    INSERT INTO t3(b) SELECT b||'e' FROM t3;
    INSERT INTO t3(b) SELECT b||'f' FROM t3;
    INSERT INTO t3(b) SELECT b||'g' FROM t3;
    INSERT INTO t3(b) SELECT b||'h' FROM t3;
    SELECT count(a), count(b) FROM t3;
  }
} {128 64}

do_test misc3-4.2 {
  execsql {
    SELECT count(a) FROM t3 WHERE b IN (SELECT b FROM t3);
  }
} {64}
do_test misc3-4.3 {
  execsql {
    SELECT count(a) FROM t3 WHERE b IN (SELECT b FROM t3 ORDER BY a+1);
  }
} {64}


# Ticket #601:  Putting a left join inside "SELECT * FROM (<join-here>)"
# gives different results that if the outer "SELECT * FROM ..." is omitted.
#

do_test misc3-5.1 {
  execsql {
    CREATE TABLE x1 (b, c);
    INSERT INTO x1 VALUES('dog',3);
    INSERT INTO x1 VALUES('cat',1);
    INSERT INTO x1 VALUES('dog',4);
    CREATE TABLE x2 (c, e);
    INSERT INTO x2 VALUES(1,'one');
    INSERT INTO x2 VALUES(2,'two');
    INSERT INTO x2 VALUES(3,'three');
    INSERT INTO x2 VALUES(4,'four');
    SELECT x2.c AS c, e, b FROM x2 LEFT JOIN
       (SELECT b, max(c)+0 AS c FROM x1 GROUP BY b)
       USING(c);
  }
} {1 one cat 2 two {} 3 three {} 4 four dog}
do_test misc4-5.2 {
  execsql {
    SELECT * FROM (
      SELECT x2.c AS c, e, b FROM x2 LEFT JOIN
         (SELECT b, max(c)+0 AS c FROM x1 GROUP BY b)
         USING(c)
    );
  }
} {1 one cat 2 two {} 3 three {} 4 four dog}


ifcapable {explain} {
  # Ticket #626:  make sure EXPLAIN prevents BEGIN and COMMIT from working.
  #
  do_test misc3-6.1 {
    execsql {EXPLAIN BEGIN}
    catchsql {BEGIN}
................................................................................
} 32
} ;# endif trigger

# Ticket #668:  VDBE stack overflow occurs when the left-hand side
# of an IN expression is NULL and the result is used as an integer, not
# as a jump.
#

do_test misc-8.1 {
  execsql {
    SELECT count(CASE WHEN b IN ('abc','xyz') THEN 'x' END) FROM t3
  }
} {2}
do_test misc-8.2 {
  execsql {
    SELECT count(*) FROM t3 WHERE 1+(b IN ('abc','xyz'))==2
  }
} {2}


finish_test







|







 







>

|
|
|
|
|
|
|
|
|
|
>



>
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
>







 







>
|
|
|
|
|
|
|
|
|
|
|
>

9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
...
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
...
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for miscellanous features that were
# left out of other test files.
#
# $Id: misc3.test,v 1.16 2005/01/21 03:12:16 danielk1977 Exp $

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

ifcapable {integrityck} {
  # Ticket #529.  Make sure an ABORT does not damage the in-memory cache
  # that will be used by subsequent statements in the same transaction.
................................................................................
    INSERT INTO t3(b) SELECT b||'e' FROM t3;
    INSERT INTO t3(b) SELECT b||'f' FROM t3;
    INSERT INTO t3(b) SELECT b||'g' FROM t3;
    INSERT INTO t3(b) SELECT b||'h' FROM t3;
    SELECT count(a), count(b) FROM t3;
  }
} {128 64}
ifcapable subquery {
do_test misc3-4.2 {
    execsql {
      SELECT count(a) FROM t3 WHERE b IN (SELECT b FROM t3);
    }
  } {64}
  do_test misc3-4.3 {
    execsql {
      SELECT count(a) FROM t3 WHERE b IN (SELECT b FROM t3 ORDER BY a+1);
    }
  } {64}
}

# Ticket #601:  Putting a left join inside "SELECT * FROM (<join-here>)"
# gives different results that if the outer "SELECT * FROM ..." is omitted.
#
ifcapable subquery {
  do_test misc3-5.1 {
    execsql {
      CREATE TABLE x1 (b, c);
      INSERT INTO x1 VALUES('dog',3);
      INSERT INTO x1 VALUES('cat',1);
      INSERT INTO x1 VALUES('dog',4);
      CREATE TABLE x2 (c, e);
      INSERT INTO x2 VALUES(1,'one');
      INSERT INTO x2 VALUES(2,'two');
      INSERT INTO x2 VALUES(3,'three');
      INSERT INTO x2 VALUES(4,'four');
      SELECT x2.c AS c, e, b FROM x2 LEFT JOIN
         (SELECT b, max(c)+0 AS c FROM x1 GROUP BY b)
         USING(c);
    }
  } {1 one cat 2 two {} 3 three {} 4 four dog}
  do_test misc3-5.2 {
    execsql {
      SELECT * FROM (
        SELECT x2.c AS c, e, b FROM x2 LEFT JOIN
           (SELECT b, max(c)+0 AS c FROM x1 GROUP BY b)
           USING(c)
      );
    }
  } {1 one cat 2 two {} 3 three {} 4 four dog}
}

ifcapable {explain} {
  # Ticket #626:  make sure EXPLAIN prevents BEGIN and COMMIT from working.
  #
  do_test misc3-6.1 {
    execsql {EXPLAIN BEGIN}
    catchsql {BEGIN}
................................................................................
} 32
} ;# endif trigger

# Ticket #668:  VDBE stack overflow occurs when the left-hand side
# of an IN expression is NULL and the result is used as an integer, not
# as a jump.
#
ifcapable subquery {
  do_test misc-8.1 {
    execsql {
      SELECT count(CASE WHEN b IN ('abc','xyz') THEN 'x' END) FROM t3
    }
  } {2}
  do_test misc-8.2 {
    execsql {
      SELECT count(*) FROM t3 WHERE 1+(b IN ('abc','xyz'))==2
    }
  } {2}
}

finish_test

Changes to test/misc4.test.

9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
..
97
98
99
100
101
102
103

104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120

121
122
123
124
125
126
127
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for miscellanous features that were
# left out of other test files.
#
# $Id: misc4.test,v 1.12 2005/01/20 02:17:02 danielk1977 Exp $

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

# Prepare a statement that will create a temporary table.  Then do
# a rollback.  Then try to execute the prepared statement.
#
................................................................................
    ORDER BY 1, 2;
  }
} {{} {} 1 x 1 z}
} ;# ifcapable compound

# Ticket #1047.  Make sure column types are preserved in subqueries.
#

do_test misc4-4.1 {
  execsql {
    create table a(key varchar, data varchar);
    create table b(key varchar, period integer);
    insert into a values('01','data01');
    insert into a values('+1','data+1');
    
    insert into b values ('01',1);
    insert into b values ('01',2);
    insert into b values ('+1',3);
    insert into b values ('+1',4);
    
    select a.*, x.*
      from a, (select key,sum(period) from b group by key) as x
      where a.key=x.key;
  }
} {01 data01 01 3.0 +1 data+1 +1 7.0}


# Ticket #1036.  When creating tables from a SELECT on a view, use the
# short names of columns.
#
ifcapable view {
  do_test misc4-5.1 {
    execsql {







|







 







>
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
>







9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
..
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for miscellanous features that were
# left out of other test files.
#
# $Id: misc4.test,v 1.13 2005/01/21 03:12:16 danielk1977 Exp $

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

# Prepare a statement that will create a temporary table.  Then do
# a rollback.  Then try to execute the prepared statement.
#
................................................................................
    ORDER BY 1, 2;
  }
} {{} {} 1 x 1 z}
} ;# ifcapable compound

# Ticket #1047.  Make sure column types are preserved in subqueries.
#
ifcapable subquery {
  do_test misc4-4.1 {
    execsql {
      create table a(key varchar, data varchar);
      create table b(key varchar, period integer);
      insert into a values('01','data01');
      insert into a values('+1','data+1');
      
      insert into b values ('01',1);
      insert into b values ('01',2);
      insert into b values ('+1',3);
      insert into b values ('+1',4);
      
      select a.*, x.*
        from a, (select key,sum(period) from b group by key) as x
        where a.key=x.key;
    }
  } {01 data01 01 3.0 +1 data+1 +1 7.0}
}

# Ticket #1036.  When creating tables from a SELECT on a view, use the
# short names of columns.
#
ifcapable view {
  do_test misc4-5.1 {
    execsql {

Changes to test/null.test.

182
183
184
185
186
187
188

189
190
191
192
193

194
195
196
197
198
199
200
...
208
209
210
211
212
213
214

215
216
217
218
219

220
221
222
223
224
225
226
  execsql {
    CREATE TABLE t4(x,y);
    INSERT INTO t4 VALUES(1,11);
    INSERT INTO t4 VALUES(2,NULL);
    SELECT x FROM t4 WHERE y=NULL;
  }
} {}

do_test null-8.2 {
  execsql {
    SELECT x FROM t4 WHERE y IN (33,NULL);
  }
} {}

do_test null-8.3 {
  execsql {
    SELECT x FROM t4 WHERE y<33 ORDER BY x;
  }
} {1}
do_test null-8.4 {
  execsql {
................................................................................
} {1}
do_test null-8.11 {
  execsql {
    CREATE INDEX t4i1 ON t4(y);
    SELECT x FROM t4 WHERE y=NULL;
  }
} {}

do_test null-8.12 {
  execsql {
    SELECT x FROM t4 WHERE y IN (33,NULL);
  }
} {}

do_test null-8.13 {
  execsql {
    SELECT x FROM t4 WHERE y<33 ORDER BY x;
  }
} {1}
do_test null-8.14 {
  execsql {







>
|
|
|
|
|
>







 







>
|
|
|
|
|
>







182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
...
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
  execsql {
    CREATE TABLE t4(x,y);
    INSERT INTO t4 VALUES(1,11);
    INSERT INTO t4 VALUES(2,NULL);
    SELECT x FROM t4 WHERE y=NULL;
  }
} {}
ifcapable subquery {
  do_test null-8.2 {
    execsql {
      SELECT x FROM t4 WHERE y IN (33,NULL);
    }
  } {}
}
do_test null-8.3 {
  execsql {
    SELECT x FROM t4 WHERE y<33 ORDER BY x;
  }
} {1}
do_test null-8.4 {
  execsql {
................................................................................
} {1}
do_test null-8.11 {
  execsql {
    CREATE INDEX t4i1 ON t4(y);
    SELECT x FROM t4 WHERE y=NULL;
  }
} {}
ifcapable subquery {
  do_test null-8.12 {
    execsql {
      SELECT x FROM t4 WHERE y IN (33,NULL);
    }
  } {}
}
do_test null-8.13 {
  execsql {
    SELECT x FROM t4 WHERE y<33 ORDER BY x;
  }
} {1}
do_test null-8.14 {
  execsql {

Changes to test/rowid.test.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
269
270
271
272
273
274
275
276

277







278
279
280
281
282
283
284
...
342
343
344
345
346
347
348

349
350
351
352
353
354
355
356
357
358
359
360

361
362

363
364
365
366
367
368
369
#    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 magic ROWID column that is
# found on all tables.
#
# $Id: rowid.test,v 1.17 2004/11/04 04:42:28 drh Exp $

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

# Basic ROWID functionality tests.
#
do_test rowid-1.1 {
................................................................................
do_test rowid-4.6 {
  execsql {
    SELECT t1.x FROM t1, t2
    WHERE t2.y==256 AND t1.rowid==t2.rowid
  }
} {4}

do_test rowid-5.1 {

  execsql {DELETE FROM t1 WHERE _rowid_ IN (SELECT oid FROM t1 WHERE x>8)}







  execsql {SELECT max(x) FROM t1}
} {8}

# Make sure a "WHERE rowid=X" clause works when there is no ROWID of X.
#
do_test rowid-6.1 {
  execsql {
................................................................................
do_test rowid-7.5 {
  execsql {
    INSERT INTO t2(a,b) VALUES(2147483647,99);
    INSERT INTO t2(b) VALUES(11);
    SELECT b FROM t2 ORDER BY b;
  }
} {11 55 66 77 88 99}

do_test rowid-7.6 {
  execsql {
    SELECT b FROM t2 WHERE a NOT IN(1,2,1000000,1000001,2147483647);
  }
} {11}
do_test rowid-7.7 {
  execsql {
    INSERT INTO t2(b) VALUES(22);
    INSERT INTO t2(b) VALUES(33);
    INSERT INTO t2(b) VALUES(44);
    INSERT INTO t2(b) VALUES(55);
    SELECT b FROM t2 WHERE a NOT IN(1,2,1000000,1000001,2147483647) ORDER BY b;

  }
} {11 22 33 44 55}

do_test rowid-7.8 {
  execsql {
    DELETE FROM t2 WHERE a!=2;
    INSERT INTO t2(b) VALUES(111);
    SELECT * FROM t2;
  }
} {2 66 3 111}







|







 







|
>
|
>
>
>
>
>
>
>







 







>
|
|
|
|
|
|
|
|
|
|
|
|
>
|
|
>







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
...
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
#    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 magic ROWID column that is
# found on all tables.
#
# $Id: rowid.test,v 1.18 2005/01/21 03:12:16 danielk1977 Exp $

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

# Basic ROWID functionality tests.
#
do_test rowid-1.1 {
................................................................................
do_test rowid-4.6 {
  execsql {
    SELECT t1.x FROM t1, t2
    WHERE t2.y==256 AND t1.rowid==t2.rowid
  }
} {4}

do_test rowid-5.1.1 {
  ifcapable subquery {
    execsql {DELETE FROM t1 WHERE _rowid_ IN (SELECT oid FROM t1 WHERE x>8)}
  } else {
    set oids [execsql {SELECT oid FROM t1 WHERE x>8}]
    set where "_rowid_ = [join $oids { OR _rowid_ = }]"
    execsql "DELETE FROM t1 WHERE $where"
  }
} {}
do_test rowid-5.1.2 {
  execsql {SELECT max(x) FROM t1}
} {8}

# Make sure a "WHERE rowid=X" clause works when there is no ROWID of X.
#
do_test rowid-6.1 {
  execsql {
................................................................................
do_test rowid-7.5 {
  execsql {
    INSERT INTO t2(a,b) VALUES(2147483647,99);
    INSERT INTO t2(b) VALUES(11);
    SELECT b FROM t2 ORDER BY b;
  }
} {11 55 66 77 88 99}
ifcapable subquery {
  do_test rowid-7.6 {
    execsql {
      SELECT b FROM t2 WHERE a NOT IN(1,2,1000000,1000001,2147483647);
    }
  } {11}
  do_test rowid-7.7 {
    execsql {
      INSERT INTO t2(b) VALUES(22);
      INSERT INTO t2(b) VALUES(33);
      INSERT INTO t2(b) VALUES(44);
      INSERT INTO t2(b) VALUES(55);
      SELECT b FROM t2 WHERE a NOT IN(1,2,1000000,1000001,2147483647) 
          ORDER BY b;
    }
  } {11 22 33 44 55}
}
do_test rowid-7.8 {
  execsql {
    DELETE FROM t2 WHERE a!=2;
    INSERT INTO t2(b) VALUES(111);
    SELECT * FROM t2;
  }
} {2 66 3 111}

Changes to test/select1.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
581
582
583
584
585
586
587

588
589
590
591
592

593
594
595
596
597
598
599
...
697
698
699
700
701
702
703

704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723

724
725
726
727
728
729
730
...
761
762
763
764
765
766
767

768
769
770
771
772
773
774
775
776
777

778
779
780
781
782
783
784
#    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 SELECT statement.
#
# $Id: select1.test,v 1.40 2005/01/18 17:20:10 drh Exp $

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

# Try to select on a non-existant table.
#
do_test select1-1.1 {
................................................................................
do_test select1-9.2 {
  execsql {PRAGMA empty_result_callbacks=on}
  catch {unset r}
  set r(*) {}
  db eval {SELECT * FROM test1 WHERE f1<0} r {}
  set r(*)
} {f1 f2}

do_test select1-9.3 {
  set r(*) {}
  db eval {SELECT * FROM test1 WHERE f1<(select count(*) from test2)} r {}
  set r(*)
} {f1 f2}

do_test select1-9.4 {
  set r(*) {}
  db eval {SELECT * FROM test1 ORDER BY f1} r {}
  set r(*)
} {f1 f2}
do_test select1-9.5 {
  set r(*) {}
................................................................................
  }
} {1 {no such table: t5}}
do_test select1-11.11 {
  catchsql {
    SELECT t3.* FROM t3 AS x, t4;
  }
} {1 {no such table: t3}}

do_test select1-11.12 {
  execsql2 {
    SELECT t3.* FROM t3, (SELECT max(a), max(b) FROM t4)
  }
} {a 1 b 2}
do_test select1-11.13 {
  execsql2 {
    SELECT t3.* FROM (SELECT max(a), max(b) FROM t4), t3
  }
} {a 1 b 2}
do_test select1-11.14 {
  execsql2 {
    SELECT * FROM t3, (SELECT max(a), max(b) FROM t4) AS 'tx'
  }
} {a 1 b 2 max(a) 3 max(b) 4}
do_test select1-11.15 {
  execsql2 {
    SELECT y.*, t3.* FROM t3, (SELECT max(a), max(b) FROM t4) AS y
  }
} {max(a) 3 max(b) 4 a 1 b 2}

do_test select1-11.16 {
  execsql2 {
    SELECT y.* FROM t3 as y, t4 as z
  }
} {a 1 b 2}

# Tests of SELECT statements without a FROM clause.
................................................................................
do_test select1-12.6 {
  execsql {
    SELECT 3, 4 UNION SELECT * FROM t3;
  }
} {1 2 3 4}
} ;# ifcapable compound


do_test select1-12.7 {
  execsql {
    SELECT * FROM t3 WHERE a=(SELECT 1);
  }
} {1 2}
do_test select1-12.8 {
  execsql {
    SELECT * FROM t3 WHERE a=(SELECT 2);
  }
} {}


ifcapable compound {
do_test select1-12.9 {
  execsql2 {
    SELECT x FROM (
      SELECT a,b FROM t3 UNION SELECT a AS 'x', b AS 'y' FROM t4 ORDER BY a,b
    ) ORDER BY x;







|







 







>
|
|
|
|
|
>







 







>
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
>







 







>
|
|
|
|
|
|
|
|
|
|
>







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
...
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
...
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
#    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 SELECT statement.
#
# $Id: select1.test,v 1.41 2005/01/21 03:12:16 danielk1977 Exp $

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

# Try to select on a non-existant table.
#
do_test select1-1.1 {
................................................................................
do_test select1-9.2 {
  execsql {PRAGMA empty_result_callbacks=on}
  catch {unset r}
  set r(*) {}
  db eval {SELECT * FROM test1 WHERE f1<0} r {}
  set r(*)
} {f1 f2}
ifcapable subquery {
  do_test select1-9.3 {
    set r(*) {}
    db eval {SELECT * FROM test1 WHERE f1<(select count(*) from test2)} r {}
    set r(*)
  } {f1 f2}
}
do_test select1-9.4 {
  set r(*) {}
  db eval {SELECT * FROM test1 ORDER BY f1} r {}
  set r(*)
} {f1 f2}
do_test select1-9.5 {
  set r(*) {}
................................................................................
  }
} {1 {no such table: t5}}
do_test select1-11.11 {
  catchsql {
    SELECT t3.* FROM t3 AS x, t4;
  }
} {1 {no such table: t3}}
ifcapable subquery {
  do_test select1-11.12 {
    execsql2 {
      SELECT t3.* FROM t3, (SELECT max(a), max(b) FROM t4)
    }
  } {a 1 b 2}
  do_test select1-11.13 {
    execsql2 {
      SELECT t3.* FROM (SELECT max(a), max(b) FROM t4), t3
    }
  } {a 1 b 2}
  do_test select1-11.14 {
    execsql2 {
      SELECT * FROM t3, (SELECT max(a), max(b) FROM t4) AS 'tx'
    }
  } {a 1 b 2 max(a) 3 max(b) 4}
  do_test select1-11.15 {
    execsql2 {
      SELECT y.*, t3.* FROM t3, (SELECT max(a), max(b) FROM t4) AS y
    }
  } {max(a) 3 max(b) 4 a 1 b 2}
}
do_test select1-11.16 {
  execsql2 {
    SELECT y.* FROM t3 as y, t4 as z
  }
} {a 1 b 2}

# Tests of SELECT statements without a FROM clause.
................................................................................
do_test select1-12.6 {
  execsql {
    SELECT 3, 4 UNION SELECT * FROM t3;
  }
} {1 2 3 4}
} ;# ifcapable compound

ifcapable subquery {
  do_test select1-12.7 {
    execsql {
      SELECT * FROM t3 WHERE a=(SELECT 1);
    }
  } {1 2}
  do_test select1-12.8 {
    execsql {
      SELECT * FROM t3 WHERE a=(SELECT 2);
    }
  } {}
}

ifcapable compound {
do_test select1-12.9 {
  execsql2 {
    SELECT x FROM (
      SELECT a,b FROM t3 UNION SELECT a AS 'x', b AS 'y' FROM t4 ORDER BY a,b
    ) ORDER BY x;

Changes to test/select6.test.

8
9
10
11
12
13
14
15
16
17
18






19
20
21
22
23
24
25
#    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 SELECT statements that contain
# subqueries in their FROM clause.
#
# $Id: select6.test,v 1.15 2004/11/22 15:05:59 danielk1977 Exp $

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







do_test select6-1.0 {
  execsql {
    BEGIN;
    CREATE TABLE t1(x, y);
    INSERT INTO t1 VALUES(1,1);
    INSERT INTO t1 VALUES(2,2);







|



>
>
>
>
>
>







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
#    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 SELECT statements that contain
# subqueries in their FROM clause.
#
# $Id: select6.test,v 1.16 2005/01/21 03:12:16 danielk1977 Exp $

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

# Omit this whole file if the library is build without subquery support.
ifcapable !subquery {
  finish_test
  return
}

do_test select6-1.0 {
  execsql {
    BEGIN;
    CREATE TABLE t1(x, y);
    INSERT INTO t1 VALUES(1,1);
    INSERT INTO t1 VALUES(2,2);

Changes to test/select7.test.

6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
..
50
51
52
53
54
55
56

57
58
59
60
61

62
#    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 compute SELECT statements and nested
# views.
#
# $Id: select7.test,v 1.4 2005/01/03 02:26:55 drh Exp $


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

ifcapable compound {

................................................................................
} {1 1}
} ;# ifcapable view

} ;# ifcapable compound

# Do not allow GROUP BY without an aggregate. Ticket #1039.
#

do_test select7-3.1 {
  catchsql {
    SELECT * FROM (SELECT * FROM sqlite_master) GROUP BY name
  }
} {1 {GROUP BY may only be used on aggregate queries}}

finish_test







|







 







>
|
|
|
|
|
>

6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
..
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
#    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 compute SELECT statements and nested
# views.
#
# $Id: select7.test,v 1.5 2005/01/21 03:12:16 danielk1977 Exp $


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

ifcapable compound {

................................................................................
} {1 1}
} ;# ifcapable view

} ;# ifcapable compound

# Do not allow GROUP BY without an aggregate. Ticket #1039.
#
ifcapable subquery {
  do_test select7-3.1 {
    catchsql {
      SELECT * FROM (SELECT * FROM sqlite_master) GROUP BY name
    }
  } {1 {GROUP BY may only be used on aggregate queries}}
}
finish_test

Changes to test/subselect.test.

8
9
10
11
12
13
14
15
16
17
18






19
20
21
22
23
24
25
#    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 SELECT statements that are part of
# expressions.
#
# $Id: subselect.test,v 1.11 2004/11/22 13:35:42 danielk1977 Exp $

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







# Basic sanity checking.  Try a simple subselect.
#
do_test subselect-1.1 {
  execsql {
    CREATE TABLE t1(a int, b int);
    INSERT INTO t1 VALUES(1,2);







|



>
>
>
>
>
>







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
#    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 SELECT statements that are part of
# expressions.
#
# $Id: subselect.test,v 1.12 2005/01/21 03:12:16 danielk1977 Exp $

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

# Omit this whole file if the library is build without subquery support.
ifcapable !subquery {
  finish_test
  return
}

# Basic sanity checking.  Try a simple subselect.
#
do_test subselect-1.1 {
  execsql {
    CREATE TABLE t1(a int, b int);
    INSERT INTO t1 VALUES(1,2);

Changes to test/table.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
510
511
512
513
514
515
516

517
518





519
520
521
522
523
524
525
#    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: table.test,v 1.35 2004/11/23 22:16:40 drh Exp $

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

# Create a basic table and verify it is added to sqlite_master
#
do_test table-1.1 {
................................................................................
    FROM t7 LIMIT 1;
  }
} {null null null null}

# Test that when creating a table using CREATE TABLE AS, column types are
# assigned correctly for (SELECT ...) and 'x AS y' expressions.
do_test table-12.1 {

  execsql {
    CREATE TABLE t8 AS SELECT b, h, a as i, (SELECT f FROM t7) as j FROM t7;





  }
} {}
do_test table-12.2 {
  execsql {
    SELECT sql FROM sqlite_master WHERE tbl_name = 't8'
  }
} {{CREATE TABLE t8(b number(5,10),h,i integer,j BLOB)}}







|







 







>
|
|
>
>
>
>
>







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
#    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: table.test,v 1.36 2005/01/21 03:12:16 danielk1977 Exp $

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

# Create a basic table and verify it is added to sqlite_master
#
do_test table-1.1 {
................................................................................
    FROM t7 LIMIT 1;
  }
} {null null null null}

# Test that when creating a table using CREATE TABLE AS, column types are
# assigned correctly for (SELECT ...) and 'x AS y' expressions.
do_test table-12.1 {
  ifcapable subquery {
    execsql {
      CREATE TABLE t8 AS SELECT b, h, a as i, (SELECT f FROM t7) as j FROM t7;
    }
  } else {
    execsql {
      CREATE TABLE t8 AS SELECT b, h, a as i, f as j FROM t7;
    }
  }
} {}
do_test table-12.2 {
  execsql {
    SELECT sql FROM sqlite_master WHERE tbl_name = 't8'
  }
} {{CREATE TABLE t8(b number(5,10),h,i integer,j BLOB)}}

Changes to test/tableapi.test.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
..
79
80
81
82
83
84
85

86
87
88





89
90
91
92
93
94
95
...
136
137
138
139
140
141
142

143
144
145





146
147
148
149
150
151
152
#    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 sqlite_exec_printf() and
# sqlite_get_table_printf() APIs.
#
# $Id: tableapi.test,v 1.9 2004/06/19 03:33:57 danielk1977 Exp $

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

do_test tableapi-1.0 {
  set ::dbx [sqlite3_open test.db]
  catch {sqlite_exec_printf $::dbx {DROP TABLE xyz} {}}
................................................................................
    SELECT * FROM xyz WHERE a>49 ORDER BY a;
  } {}
} "0 2 2 a b 50 (50) 51 \173$::big_str\175"
do_test tableapi-2.6 {
  sqlite3_get_table_printf $::dbx {
    INSERT INTO xyz VALUES(52,NULL)
  } {}

  sqlite3_get_table_printf $::dbx {
    SELECT * FROM xyz WHERE a IN (42,50,52) ORDER BY a DESC
  } {}





} {0 3 2 a b 52 NULL 50 (50) 42 (42)}
do_test tableapi-2.7 {
  sqlite3_get_table_printf $::dbx {
    SELECT * FROM xyz WHERE a>1000
  } {}
} {0 0 0}

................................................................................
    SELECT * FROM xyz WHERE a>49 ORDER BY a;
  } {}
} "0 2 2 a b 50 (50) 51 \173$::big_str\175"
do_test tableapi-3.6 {
  sqlite3_get_table_printf $::dbx {
    INSERT INTO xyz VALUES(52,NULL)
  } {}

  sqlite3_get_table_printf $::dbx {
    SELECT * FROM xyz WHERE a IN (42,50,52) ORDER BY a DESC
  } {}





} {0 3 2 a b 52 NULL 50 (50) 42 (42)}
do_test tableapi-3.7 {
  sqlite3_get_table_printf $::dbx {
    SELECT * FROM xyz WHERE a>1000
  } {}
} {0 0 2 a b}








|







 







>
|
|
|
>
>
>
>
>







 







>
|
|
|
>
>
>
>
>







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
..
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
...
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
#    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 sqlite_exec_printf() and
# sqlite_get_table_printf() APIs.
#
# $Id: tableapi.test,v 1.10 2005/01/21 03:12:16 danielk1977 Exp $

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

do_test tableapi-1.0 {
  set ::dbx [sqlite3_open test.db]
  catch {sqlite_exec_printf $::dbx {DROP TABLE xyz} {}}
................................................................................
    SELECT * FROM xyz WHERE a>49 ORDER BY a;
  } {}
} "0 2 2 a b 50 (50) 51 \173$::big_str\175"
do_test tableapi-2.6 {
  sqlite3_get_table_printf $::dbx {
    INSERT INTO xyz VALUES(52,NULL)
  } {}
  ifcapable subquery {
    sqlite3_get_table_printf $::dbx {
      SELECT * FROM xyz WHERE a IN (42,50,52) ORDER BY a DESC
    } {}
  } else {
    sqlite3_get_table_printf $::dbx {
      SELECT * FROM xyz WHERE a=42 OR a=50 OR a=52 ORDER BY a DESC
    } {}
  }
} {0 3 2 a b 52 NULL 50 (50) 42 (42)}
do_test tableapi-2.7 {
  sqlite3_get_table_printf $::dbx {
    SELECT * FROM xyz WHERE a>1000
  } {}
} {0 0 0}

................................................................................
    SELECT * FROM xyz WHERE a>49 ORDER BY a;
  } {}
} "0 2 2 a b 50 (50) 51 \173$::big_str\175"
do_test tableapi-3.6 {
  sqlite3_get_table_printf $::dbx {
    INSERT INTO xyz VALUES(52,NULL)
  } {}
  ifcapable subquery {
    sqlite3_get_table_printf $::dbx {
      SELECT * FROM xyz WHERE a IN (42,50,52) ORDER BY a DESC
    } {}
  } else {
    sqlite3_get_table_printf $::dbx {
      SELECT * FROM xyz WHERE a=42 OR a=50 OR a=52 ORDER BY a DESC
    } {}
  }
} {0 3 2 a b 52 NULL 50 (50) 42 (42)}
do_test tableapi-3.7 {
  sqlite3_get_table_printf $::dbx {
    SELECT * FROM xyz WHERE a>1000
  } {}
} {0 0 2 a b}

Changes to test/tester.tcl.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
236
237
238
239
240
241
242
243
244
245
246




247
248
249
250
251
252
253
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements some common TCL routines used for regression
# testing the SQLite library
#
# $Id: tester.tcl,v 1.44 2005/01/13 11:07:54 danielk1977 Exp $

# Make sure tclsqlite3 was compiled correctly.  Abort now with an
# error message if not.
#
if {[sqlite3 -tcl-uses-utf]} {
  if {"\u1234"=="u1234"} {
    puts stderr "***** BUILD PROBLEM *****"
................................................................................
    } {ok}
  }
}

# Evaluate a boolean expression of capabilities.  If true, execute the
# code.  Omit the code if false.
#
proc ifcapable {expr code} {
  regsub -all {[a-z_0-9]+} $expr {$::sqlite_options(&)} e2
  if !($e2) return
  return -code [catch {uplevel 1 $code}]




}

# This proc execs a seperate process that crashes midway through executing
# the SQL script $sql on database test.db.
#
# The crash occurs during a sync() of file $crashfile. When the crash
# occurs a random subset of all unsynced writes made by the process are







|







 







|

|
|
>
>
>
>







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements some common TCL routines used for regression
# testing the SQLite library
#
# $Id: tester.tcl,v 1.45 2005/01/21 03:12:16 danielk1977 Exp $

# Make sure tclsqlite3 was compiled correctly.  Abort now with an
# error message if not.
#
if {[sqlite3 -tcl-uses-utf]} {
  if {"\u1234"=="u1234"} {
    puts stderr "***** BUILD PROBLEM *****"
................................................................................
    } {ok}
  }
}

# Evaluate a boolean expression of capabilities.  If true, execute the
# code.  Omit the code if false.
#
proc ifcapable {expr code {else ""} {elsecode ""}} {
  regsub -all {[a-z_0-9]+} $expr {$::sqlite_options(&)} e2
  if ($e2) {
    set c [catch {uplevel 1 $code} r]
  } else {
    set c [catch {uplevel 1 $elsecode} r]
  }
  return -code $c $r
}

# This proc execs a seperate process that crashes midway through executing
# the SQL script $sql on database test.db.
#
# The crash occurs during a sync() of file $crashfile. When the crash
# occurs a random subset of all unsynced writes made by the process are

Changes to test/types2.test.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
192
193
194
195
196
197
198

199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231

232
233
234
235
236
237
238

239
240
241
242
243
244
245
246
247
248
249

250
251
252
253
254
255
256
257
258
259
260
261

262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285

286
287
288
289
290
291

292
293
294
295
296
297
298
299
300
301
302
303
304

305
#    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 interaction of manifest types, type affinity
# and comparison expressions.
#
# $Id: types2.test,v 1.4 2004/07/19 00:39:46 drh Exp $

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

# Tests in this file are organized roughly as follows:
#
# types2-1.*: The '=' operator in the absence of an index.
................................................................................
test_bool types2-4.23 {o1=500} {500.0 > o1} 0
test_bool types2-4.24 {o1=500} {'500.0' > o1} 1
test_bool types2-4.25 {o1='500'} {500 > o1} 0
test_bool types2-4.26 {o1='500'} {'500' > o1} 0
test_bool types2-4.27 {o1='500'} {500.0 > o1} 0
test_bool types2-4.28 {o1='500'} {'500.0' > o1} 1


# types2-5.* - The 'IN (x, y....)' operator with no index.
# 
# Compare literals against literals (always a numeric comparison).
test_bool types2-5.1 {} {(NULL IN ('10.0', 20)) ISNULL} 1
test_bool types2-5.2 {} {10 IN ('10.0', 20)} 1
test_bool types2-5.3 {} {'10' IN ('10.0', 20)} 1
test_bool types2-5.4 {} {10 IN (10.0, 20)} 1
test_bool types2-5.5 {} {'10.0' IN (10, 20)} 1

# Compare literals against a column with TEXT affinity
test_bool types2-5.6 {t1='10.0'} {t1 IN (10.0, 20)} 1
test_bool types2-5.7 {t1='10.0'} {t1 IN (10, 20)} 0
test_bool types2-5.8 {t1='10'} {t1 IN (10.0, 20)} 0
test_bool types2-5.9 {t1='10'} {t1 IN (20, '10.0')} 0
test_bool types2-5.10 {t1=10} {t1 IN (20, '10')} 1

# Compare literals against a column with NUMERIC affinity
test_bool types2-5.11 {n1='10.0'} {n1 IN (10.0, 20)} 1
test_bool types2-5.12 {n1='10.0'} {n1 IN (10, 20)} 1
test_bool types2-5.13 {n1='10'} {n1 IN (10.0, 20)} 1
test_bool types2-5.14 {n1='10'} {n1 IN (20, '10.0')} 1
test_bool types2-5.15 {n1=10} {n1 IN (20, '10')} 1

# Compare literals against a column with affinity NONE
test_bool types2-5.16 {o1='10.0'} {o1 IN (10.0, 20)} 0
test_bool types2-5.17 {o1='10.0'} {o1 IN (10, 20)} 0
test_bool types2-5.18 {o1='10'} {o1 IN (10.0, 20)} 0
test_bool types2-5.19 {o1='10'} {o1 IN (20, '10.0')} 0
test_bool types2-5.20 {o1=10} {o1 IN (20, '10')} 0
test_bool types2-5.21 {o1='10.0'} {o1 IN (10, 20, '10.0')} 1
test_bool types2-5.22 {o1='10'} {o1 IN (10.0, 20, '10')} 1
test_bool types2-5.23 {o1=10} {n1 IN (20, '10', 10)} 1


# Tests named types2-6.* use the same infrastructure as the types2-2.*
# tests. The contents of the vals array is repeated here for easy 
# reference.
# 
# set vals [list 10 10.0 '10' '10.0' 20 20.0 '20' '20.0' 30 30.0 '30' '30.0']
#                1  2    3    4      5  6    7    8      9  10   11   12


test_boolset types2-6.1 {o IN ('10', 30)} {3 9 10}
test_boolset types2-6.2 {o IN (20.0, 30.0)} {5 6 9 10}
test_boolset types2-6.3 {t IN ('10', 30)} {1 3 9 11}
test_boolset types2-6.4 {t IN (20.0, 30.0)} {6 8 10 12}
test_boolset types2-6.5 {n IN ('10', 30)} {1 2 3 4 9 10 11 12}
test_boolset types2-6.6 {n IN (20.0, 30.0)} {5 6 7 8 9 10 11 12}
test_boolset types2-6.7 {i IN ('10', 30)} {1 2 3 4 9 10 11 12}
test_boolset types2-6.8 {i IN (20.0, 30.0)} {5 6 7 8 9 10 11 12}

# Also test than IN(x, y, z) works on a rowid:
test_boolset types2-6.9 {rowid IN (1, 6, 10)} {1 6 10}


# Tests types2-7.* concentrate on expressions of the form 
# "x IN (SELECT...)" with no index.
execsql {
  CREATE TABLE t3(i INTEGER, n NUMERIC, t TEXT, o BLOB);
  INSERT INTO t3 VALUES(1, 1, 1, 1);
  INSERT INTO t3 VALUES(2, 2, 2, 2);
  INSERT INTO t3 VALUES(3, 3, 3, 3);
  INSERT INTO t3 VALUES('1', '1', '1', '1');
  INSERT INTO t3 VALUES('1.0', '1.0', '1.0', '1.0');
}


test_bool types2-7.1 {i1=1} {i1 IN (SELECT i FROM t3)} 1
test_bool types2-7.2 {i1='2.0'} {i1 IN (SELECT i FROM t3)} 1
test_bool types2-7.3 {i1='2.0'} {i1 IN (SELECT n FROM t3)} 1
test_bool types2-7.4 {i1='2.0'} {i1 IN (SELECT t FROM t3)} 1
test_bool types2-7.5 {i1='2.0'} {i1 IN (SELECT o FROM t3)} 1

test_bool types2-7.6 {n1=1} {n1 IN (SELECT n FROM t3)} 1
test_bool types2-7.7 {n1='2.0'} {n1 IN (SELECT i FROM t3)} 1
test_bool types2-7.8 {n1='2.0'} {n1 IN (SELECT n FROM t3)} 1
test_bool types2-7.9 {n1='2.0'} {n1 IN (SELECT t FROM t3)} 1
test_bool types2-7.10 {n1='2.0'} {n1 IN (SELECT o FROM t3)} 1

test_bool types2-7.6 {t1=1} {t1 IN (SELECT t FROM t3)} 1
test_bool types2-7.7 {t1='2.0'} {t1 IN (SELECT t FROM t3)} 0
test_bool types2-7.8 {t1='2.0'} {t1 IN (SELECT n FROM t3)} 1
test_bool types2-7.9 {t1='2.0'} {t1 IN (SELECT i FROM t3)} 1
test_bool types2-7.10 {t1='2.0'} {t1 IN (SELECT o FROM t3)} 0
test_bool types2-7.11 {t1='1.0'} {t1 IN (SELECT t FROM t3)} 1
test_bool types2-7.12 {t1='1.0'} {t1 IN (SELECT o FROM t3)} 1

test_bool types2-7.13 {o1=2} {o1 IN (SELECT o FROM t3)} 1
test_bool types2-7.14 {o1='2'} {o1 IN (SELECT o FROM t3)} 0
test_bool types2-7.15 {o1='2'} {o1 IN (SELECT o||'' FROM t3)} 1


# set vals [list 10 10.0 '10' '10.0' 20 20.0 '20' '20.0' 30 30.0 '30' '30.0']
#                1  2    3    4      5  6    7    8      9  10   11   12
execsql {
  CREATE TABLE t4(i INTEGER, n NUMERIC, t VARCHAR(20), o LARGE BLOB);
  INSERT INTO t4 VALUES(10, 20, 20, 30);
}

test_boolset types2-8.1 {i IN (SELECT i FROM t4)} {1 2 3 4}
test_boolset types2-8.2 {n IN (SELECT i FROM t4)} {1 2 3 4}
test_boolset types2-8.3 {t IN (SELECT i FROM t4)} {1 2 3 4}
test_boolset types2-8.4 {o IN (SELECT i FROM t4)} {1 2 3 4}
test_boolset types2-8.5 {i IN (SELECT t FROM t4)} {5 6 7 8}
test_boolset types2-8.6 {n IN (SELECT t FROM t4)} {5 6 7 8}
test_boolset types2-8.7 {t IN (SELECT t FROM t4)} {5 7}
test_boolset types2-8.8 {o IN (SELECT t FROM t4)} {7}
test_boolset types2-8.9 {i IN (SELECT o FROM t4)} {9 10 11 12}
test_boolset types2-8.6 {n IN (SELECT o FROM t4)} {9 10 11 12}
test_boolset types2-8.7 {t IN (SELECT o FROM t4)} {9 11}
test_boolset types2-8.8 {o IN (SELECT o FROM t4)} {9 10}


finish_test







|







 







>
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
>







>
|
|
|
|
|
|
|
|

|
|
>












>
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
>






>
|
|
|
|
|
|
|
|
|
|
|
|
|
>

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
#    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 interaction of manifest types, type affinity
# and comparison expressions.
#
# $Id: types2.test,v 1.5 2005/01/21 03:12:16 danielk1977 Exp $

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

# Tests in this file are organized roughly as follows:
#
# types2-1.*: The '=' operator in the absence of an index.
................................................................................
test_bool types2-4.23 {o1=500} {500.0 > o1} 0
test_bool types2-4.24 {o1=500} {'500.0' > o1} 1
test_bool types2-4.25 {o1='500'} {500 > o1} 0
test_bool types2-4.26 {o1='500'} {'500' > o1} 0
test_bool types2-4.27 {o1='500'} {500.0 > o1} 0
test_bool types2-4.28 {o1='500'} {'500.0' > o1} 1

ifcapable subquery {
  # types2-5.* - The 'IN (x, y....)' operator with no index.
  # 
  # Compare literals against literals (always a numeric comparison).
  test_bool types2-5.1 {} {(NULL IN ('10.0', 20)) ISNULL} 1
  test_bool types2-5.2 {} {10 IN ('10.0', 20)} 1
  test_bool types2-5.3 {} {'10' IN ('10.0', 20)} 1
  test_bool types2-5.4 {} {10 IN (10.0, 20)} 1
  test_bool types2-5.5 {} {'10.0' IN (10, 20)} 1
  
  # Compare literals against a column with TEXT affinity
  test_bool types2-5.6 {t1='10.0'} {t1 IN (10.0, 20)} 1
  test_bool types2-5.7 {t1='10.0'} {t1 IN (10, 20)} 0
  test_bool types2-5.8 {t1='10'} {t1 IN (10.0, 20)} 0
  test_bool types2-5.9 {t1='10'} {t1 IN (20, '10.0')} 0
  test_bool types2-5.10 {t1=10} {t1 IN (20, '10')} 1
  
  # Compare literals against a column with NUMERIC affinity
  test_bool types2-5.11 {n1='10.0'} {n1 IN (10.0, 20)} 1
  test_bool types2-5.12 {n1='10.0'} {n1 IN (10, 20)} 1
  test_bool types2-5.13 {n1='10'} {n1 IN (10.0, 20)} 1
  test_bool types2-5.14 {n1='10'} {n1 IN (20, '10.0')} 1
  test_bool types2-5.15 {n1=10} {n1 IN (20, '10')} 1
  
  # Compare literals against a column with affinity NONE
  test_bool types2-5.16 {o1='10.0'} {o1 IN (10.0, 20)} 0
  test_bool types2-5.17 {o1='10.0'} {o1 IN (10, 20)} 0
  test_bool types2-5.18 {o1='10'} {o1 IN (10.0, 20)} 0
  test_bool types2-5.19 {o1='10'} {o1 IN (20, '10.0')} 0
  test_bool types2-5.20 {o1=10} {o1 IN (20, '10')} 0
  test_bool types2-5.21 {o1='10.0'} {o1 IN (10, 20, '10.0')} 1
  test_bool types2-5.22 {o1='10'} {o1 IN (10.0, 20, '10')} 1
  test_bool types2-5.23 {o1=10} {n1 IN (20, '10', 10)} 1
}

# Tests named types2-6.* use the same infrastructure as the types2-2.*
# tests. The contents of the vals array is repeated here for easy 
# reference.
# 
# set vals [list 10 10.0 '10' '10.0' 20 20.0 '20' '20.0' 30 30.0 '30' '30.0']
#                1  2    3    4      5  6    7    8      9  10   11   12

ifcapable subquery {
  test_boolset types2-6.1 {o IN ('10', 30)} {3 9 10}
  test_boolset types2-6.2 {o IN (20.0, 30.0)} {5 6 9 10}
  test_boolset types2-6.3 {t IN ('10', 30)} {1 3 9 11}
  test_boolset types2-6.4 {t IN (20.0, 30.0)} {6 8 10 12}
  test_boolset types2-6.5 {n IN ('10', 30)} {1 2 3 4 9 10 11 12}
  test_boolset types2-6.6 {n IN (20.0, 30.0)} {5 6 7 8 9 10 11 12}
  test_boolset types2-6.7 {i IN ('10', 30)} {1 2 3 4 9 10 11 12}
  test_boolset types2-6.8 {i IN (20.0, 30.0)} {5 6 7 8 9 10 11 12}

  # Also test than IN(x, y, z) works on a rowid:
  test_boolset types2-6.9 {rowid IN (1, 6, 10)} {1 6 10}
}

# Tests types2-7.* concentrate on expressions of the form 
# "x IN (SELECT...)" with no index.
execsql {
  CREATE TABLE t3(i INTEGER, n NUMERIC, t TEXT, o BLOB);
  INSERT INTO t3 VALUES(1, 1, 1, 1);
  INSERT INTO t3 VALUES(2, 2, 2, 2);
  INSERT INTO t3 VALUES(3, 3, 3, 3);
  INSERT INTO t3 VALUES('1', '1', '1', '1');
  INSERT INTO t3 VALUES('1.0', '1.0', '1.0', '1.0');
}

ifcapable subquery {
  test_bool types2-7.1 {i1=1} {i1 IN (SELECT i FROM t3)} 1
  test_bool types2-7.2 {i1='2.0'} {i1 IN (SELECT i FROM t3)} 1
  test_bool types2-7.3 {i1='2.0'} {i1 IN (SELECT n FROM t3)} 1
  test_bool types2-7.4 {i1='2.0'} {i1 IN (SELECT t FROM t3)} 1
  test_bool types2-7.5 {i1='2.0'} {i1 IN (SELECT o FROM t3)} 1
  
  test_bool types2-7.6 {n1=1} {n1 IN (SELECT n FROM t3)} 1
  test_bool types2-7.7 {n1='2.0'} {n1 IN (SELECT i FROM t3)} 1
  test_bool types2-7.8 {n1='2.0'} {n1 IN (SELECT n FROM t3)} 1
  test_bool types2-7.9 {n1='2.0'} {n1 IN (SELECT t FROM t3)} 1
  test_bool types2-7.10 {n1='2.0'} {n1 IN (SELECT o FROM t3)} 1
  
  test_bool types2-7.6 {t1=1} {t1 IN (SELECT t FROM t3)} 1
  test_bool types2-7.7 {t1='2.0'} {t1 IN (SELECT t FROM t3)} 0
  test_bool types2-7.8 {t1='2.0'} {t1 IN (SELECT n FROM t3)} 1
  test_bool types2-7.9 {t1='2.0'} {t1 IN (SELECT i FROM t3)} 1
  test_bool types2-7.10 {t1='2.0'} {t1 IN (SELECT o FROM t3)} 0
  test_bool types2-7.11 {t1='1.0'} {t1 IN (SELECT t FROM t3)} 1
  test_bool types2-7.12 {t1='1.0'} {t1 IN (SELECT o FROM t3)} 1
  
  test_bool types2-7.13 {o1=2} {o1 IN (SELECT o FROM t3)} 1
  test_bool types2-7.14 {o1='2'} {o1 IN (SELECT o FROM t3)} 0
  test_bool types2-7.15 {o1='2'} {o1 IN (SELECT o||'' FROM t3)} 1
}

# set vals [list 10 10.0 '10' '10.0' 20 20.0 '20' '20.0' 30 30.0 '30' '30.0']
#                1  2    3    4      5  6    7    8      9  10   11   12
execsql {
  CREATE TABLE t4(i INTEGER, n NUMERIC, t VARCHAR(20), o LARGE BLOB);
  INSERT INTO t4 VALUES(10, 20, 20, 30);
}
ifcapable subquery {
  test_boolset types2-8.1 {i IN (SELECT i FROM t4)} {1 2 3 4}
  test_boolset types2-8.2 {n IN (SELECT i FROM t4)} {1 2 3 4}
  test_boolset types2-8.3 {t IN (SELECT i FROM t4)} {1 2 3 4}
  test_boolset types2-8.4 {o IN (SELECT i FROM t4)} {1 2 3 4}
  test_boolset types2-8.5 {i IN (SELECT t FROM t4)} {5 6 7 8}
  test_boolset types2-8.6 {n IN (SELECT t FROM t4)} {5 6 7 8}
  test_boolset types2-8.7 {t IN (SELECT t FROM t4)} {5 7}
  test_boolset types2-8.8 {o IN (SELECT t FROM t4)} {7}
  test_boolset types2-8.9 {i IN (SELECT o FROM t4)} {9 10 11 12}
  test_boolset types2-8.6 {n IN (SELECT o FROM t4)} {9 10 11 12}
  test_boolset types2-8.7 {t IN (SELECT o FROM t4)} {9 11}
  test_boolset types2-8.8 {o IN (SELECT o FROM t4)} {9 10}
}

finish_test

Changes to test/update.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
479
480
481
482
483
484
485

486
487
488
489
490
491
492
493
494
495
496
497

498
499
500
501
502
503
504
#    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 UPDATE statement.
#
# $Id: update.test,v 1.16 2005/01/15 00:40:43 drh Exp $

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

# Try to update an non-existent table
#
do_test update-1.1 {
................................................................................
  catchsql {
    SELECT * FROM t1;
  }
} {0 {1 2 3 4 13 6 2 3 4 4 6 7}}

# Make sure we can handle a subquery in the where clause.
#

do_test update-11.1 {
  execsql {
    UPDATE t1 SET e=e+1 WHERE b IN (SELECT b FROM t1);
    SELECT b,e FROM t1;
  }
} {2 14 3 7}
do_test update-11.2 {
  execsql {
    UPDATE t1 SET e=e+1 WHERE a IN (SELECT a FROM t1);
    SELECT a,e FROM t1;
  }
} {1 15 2 8}


integrity_check update-12.1

# Ticket 602.  Updates should occur in the same order as the records
# were discovered in the WHERE clause.
#
do_test update-13.1 {







|







 







>
|
|
|
|
|
|
|
|
|
|
|
|
>







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
#    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 UPDATE statement.
#
# $Id: update.test,v 1.17 2005/01/21 03:12:16 danielk1977 Exp $

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

# Try to update an non-existent table
#
do_test update-1.1 {
................................................................................
  catchsql {
    SELECT * FROM t1;
  }
} {0 {1 2 3 4 13 6 2 3 4 4 6 7}}

# Make sure we can handle a subquery in the where clause.
#
ifcapable subquery {
  do_test update-11.1 {
    execsql {
      UPDATE t1 SET e=e+1 WHERE b IN (SELECT b FROM t1);
      SELECT b,e FROM t1;
    }
  } {2 14 3 7}
  do_test update-11.2 {
    execsql {
      UPDATE t1 SET e=e+1 WHERE a IN (SELECT a FROM t1);
      SELECT a,e FROM t1;
    }
  } {1 15 2 8}
}

integrity_check update-12.1

# Ticket 602.  Updates should occur in the same order as the records
# were discovered in the WHERE clause.
#
do_test update-13.1 {

Changes to test/where.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
..
25
26
27
28
29
30
31


32
33









34
35
36
37
38
39
40
...
287
288
289
290
291
292
293

294

295
296
297
298
299
300
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
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372

373
374
375
376
377
378
379
...
424
425
426
427
428
429
430

431
432
433
434
435

436
437
438
439
440
441
442
#    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.27 2005/01/11 18:13:57 drh Exp $

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

# Build some test data
#
do_test where-1.0 {
................................................................................
  }
  for {set i 1} {$i<=100} {incr i} {
    set w $i
    set x [expr {int(log($i)/log(2))}]
    set y [expr {$i*$i + 2*$i + 1}]
    execsql "INSERT INTO t1 VALUES($w,$x,$y)"
  }


  execsql {
    INSERT INTO t2 SELECT 101-w, x, (SELECT max(y) FROM t1)+1-y, y FROM t1;









    CREATE INDEX i1w ON t1(w);
    CREATE INDEX i1xy ON t1(x,y);
    CREATE INDEX i2p ON t2(p);
    CREATE INDEX i2r ON t2(r);
    CREATE INDEX i2qs ON t2(q, s);
  }
} {}
................................................................................
do_test where-4.4 {
  execsql {
    SELECT 99 WHERE 1
  }
} {99}

# Verify that IN operators in a WHERE clause are handled correctly.

#

do_test where-5.1 {
  count {
    SELECT * FROM t1 WHERE rowid IN (1,2,3,1234) order by 1;
  }
} {1 0 4 2 1 9 3 1 16 3}
do_test where-5.2 {
  count {
    SELECT * FROM t1 WHERE rowid+0 IN (1,2,3,1234) order by 1;
  }
} {1 0 4 2 1 9 3 1 16 199}
do_test where-5.3 {
  count {
    SELECT * FROM t1 WHERE w IN (-1,1,2,3) order by 1;
  }
} {1 0 4 2 1 9 3 1 16 13}
do_test where-5.4 {
  count {
    SELECT * FROM t1 WHERE w+0 IN (-1,1,2,3) order by 1;
  }
} {1 0 4 2 1 9 3 1 16 199}
do_test where-5.5 {
  count {
    SELECT * FROM t1 WHERE rowid IN 
       (select rowid from t1 where rowid IN (-1,2,4))
    ORDER BY 1;
  }
} {2 1 9 4 2 25 3}
do_test where-5.6 {
  count {
    SELECT * FROM t1 WHERE rowid+0 IN 
       (select rowid from t1 where rowid IN (-1,2,4))
    ORDER BY 1;
  }
} {2 1 9 4 2 25 201}
do_test where-5.7 {
  count {
    SELECT * FROM t1 WHERE w IN 
       (select rowid from t1 where rowid IN (-1,2,4))
    ORDER BY 1;
  }
} {2 1 9 4 2 25 9}
do_test where-5.8 {
  count {
    SELECT * FROM t1 WHERE w+0 IN 
       (select rowid from t1 where rowid IN (-1,2,4))
    ORDER BY 1;
  }
} {2 1 9 4 2 25 201}
do_test where-5.9 {
  count {
    SELECT * FROM t1 WHERE x IN (1,7) ORDER BY 1;
  }
} {2 1 9 3 1 16 7}
do_test where-5.10 {
  count {
    SELECT * FROM t1 WHERE x+0 IN (1,7) ORDER BY 1;
  }
} {2 1 9 3 1 16 199}
do_test where-5.11 {
  count {
    SELECT * FROM t1 WHERE y IN (6400,8100) ORDER BY 1;
  }
} {79 6 6400 89 6 8100 199}
do_test where-5.12 {
  count {
    SELECT * FROM t1 WHERE x=6 AND y IN (6400,8100) ORDER BY 1;
  }
} {79 6 6400 89 6 8100 74}
do_test where-5.13 {
  count {
    SELECT * FROM t1 WHERE x IN (1,7) AND y NOT IN (6400,8100) ORDER BY 1;
  }
} {2 1 9 3 1 16 7}
do_test where-5.14 {
  count {
    SELECT * FROM t1 WHERE x IN (1,7) AND y IN (9,10) ORDER BY 1;
  }
} {2 1 9 7}


# This procedure executes the SQL.  Then it checks to see if the OP_Sort
# opcode was executed.  If an OP_Sort did occur, then "sort" is appended
# to the result.  If no OP_Sort happened, then "nosort" is appended.
#
# This procedure is used to check to make sure sorting is or is not
# occurring as expected.
................................................................................
  }
} {1 100 4 2 99 9 3 98 16 nosort}
do_test where-6.7 {
  cksort {
    SELECT * FROM t3 WHERE b>0 ORDER BY a LIMIT 3
  }
} {1 100 4 2 99 9 3 98 16 sort}

do_test where-6.8 {
  cksort {
    SELECT * FROM t3 WHERE a IN (3,5,7,1,9,4,2) ORDER BY a LIMIT 3
  }
} {1 100 4 2 99 9 3 98 16 sort}

do_test where-6.9.1 {
  cksort {
    SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a LIMIT 3
  }
} {1 100 4 nosort}
do_test where-6.9.1.1 {
  cksort {







|







 







>
>
|
|
>
>
>
>
>
>
>
>
>







 







>

>
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
>







 







>
|
|
|
|
|
>







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
..
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
...
298
299
300
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
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
...
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
#    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.28 2005/01/21 03:12:16 danielk1977 Exp $

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

# Build some test data
#
do_test where-1.0 {
................................................................................
  }
  for {set i 1} {$i<=100} {incr i} {
    set w $i
    set x [expr {int(log($i)/log(2))}]
    set y [expr {$i*$i + 2*$i + 1}]
    execsql "INSERT INTO t1 VALUES($w,$x,$y)"
  }

  ifcapable subquery {
    execsql {
      INSERT INTO t2 SELECT 101-w, x, (SELECT max(y) FROM t1)+1-y, y FROM t1;
    }
  } else {
    set maxy [execsql {select max(y) from t1}]
    execsql "
      INSERT INTO t2 SELECT 101-w, x, $maxy+1-y, y FROM t1;
    "
  }

  execsql {
    CREATE INDEX i1w ON t1(w);
    CREATE INDEX i1xy ON t1(x,y);
    CREATE INDEX i2p ON t2(p);
    CREATE INDEX i2r ON t2(r);
    CREATE INDEX i2qs ON t2(q, s);
  }
} {}
................................................................................
do_test where-4.4 {
  execsql {
    SELECT 99 WHERE 1
  }
} {99}

# Verify that IN operators in a WHERE clause are handled correctly.
# Omit these tests if the build is not capable of sub-queries.
#
ifcapable subquery {
  do_test where-5.1 {
    count {
      SELECT * FROM t1 WHERE rowid IN (1,2,3,1234) order by 1;
    }
  } {1 0 4 2 1 9 3 1 16 3}
  do_test where-5.2 {
    count {
      SELECT * FROM t1 WHERE rowid+0 IN (1,2,3,1234) order by 1;
    }
  } {1 0 4 2 1 9 3 1 16 199}
  do_test where-5.3 {
    count {
      SELECT * FROM t1 WHERE w IN (-1,1,2,3) order by 1;
    }
  } {1 0 4 2 1 9 3 1 16 13}
  do_test where-5.4 {
    count {
      SELECT * FROM t1 WHERE w+0 IN (-1,1,2,3) order by 1;
    }
  } {1 0 4 2 1 9 3 1 16 199}
  do_test where-5.5 {
    count {
      SELECT * FROM t1 WHERE rowid IN 
         (select rowid from t1 where rowid IN (-1,2,4))
      ORDER BY 1;
    }
  } {2 1 9 4 2 25 3}
  do_test where-5.6 {
    count {
      SELECT * FROM t1 WHERE rowid+0 IN 
         (select rowid from t1 where rowid IN (-1,2,4))
      ORDER BY 1;
    }
  } {2 1 9 4 2 25 201}
  do_test where-5.7 {
    count {
      SELECT * FROM t1 WHERE w IN 
         (select rowid from t1 where rowid IN (-1,2,4))
      ORDER BY 1;
    }
  } {2 1 9 4 2 25 9}
  do_test where-5.8 {
    count {
      SELECT * FROM t1 WHERE w+0 IN 
         (select rowid from t1 where rowid IN (-1,2,4))
      ORDER BY 1;
    }
  } {2 1 9 4 2 25 201}
  do_test where-5.9 {
    count {
      SELECT * FROM t1 WHERE x IN (1,7) ORDER BY 1;
    }
  } {2 1 9 3 1 16 7}
  do_test where-5.10 {
    count {
      SELECT * FROM t1 WHERE x+0 IN (1,7) ORDER BY 1;
    }
  } {2 1 9 3 1 16 199}
  do_test where-5.11 {
    count {
      SELECT * FROM t1 WHERE y IN (6400,8100) ORDER BY 1;
    }
  } {79 6 6400 89 6 8100 199}
  do_test where-5.12 {
    count {
      SELECT * FROM t1 WHERE x=6 AND y IN (6400,8100) ORDER BY 1;
    }
  } {79 6 6400 89 6 8100 74}
  do_test where-5.13 {
    count {
      SELECT * FROM t1 WHERE x IN (1,7) AND y NOT IN (6400,8100) ORDER BY 1;
    }
  } {2 1 9 3 1 16 7}
  do_test where-5.14 {
    count {
      SELECT * FROM t1 WHERE x IN (1,7) AND y IN (9,10) ORDER BY 1;
    }
  } {2 1 9 7}
}

# This procedure executes the SQL.  Then it checks to see if the OP_Sort
# opcode was executed.  If an OP_Sort did occur, then "sort" is appended
# to the result.  If no OP_Sort happened, then "nosort" is appended.
#
# This procedure is used to check to make sure sorting is or is not
# occurring as expected.
................................................................................
  }
} {1 100 4 2 99 9 3 98 16 nosort}
do_test where-6.7 {
  cksort {
    SELECT * FROM t3 WHERE b>0 ORDER BY a LIMIT 3
  }
} {1 100 4 2 99 9 3 98 16 sort}
ifcapable subquery {
  do_test where-6.8 {
    cksort {
      SELECT * FROM t3 WHERE a IN (3,5,7,1,9,4,2) ORDER BY a LIMIT 3
    }
  } {1 100 4 2 99 9 3 98 16 sort}
}
do_test where-6.9.1 {
  cksort {
    SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a LIMIT 3
  }
} {1 100 4 nosort}
do_test where-6.9.1.1 {
  cksort {